using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.UI.WebControls;
using System.Windows.Forms;
using System.Collections;
//using System.Windows.Forms;
namespace Test.Utility
{
    public static class DbOperation
    {
        private static string connStr = "Data Source=58.57.32.11,1347;Initial Catalog=CRM;Persist Security Info=True;User ID=sa;Password=521777yesu!#%@$^";



        public static string DbQueryCount(string cmdStr)
        {



            string result = "";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn);
            DataSet myDatset = new DataSet();
            myAdap.Fill(myDatset);

            myAdap.Dispose();

            int a = 0;
            int b = 0;
            a = myDatset.Tables[0].Rows.Count;
            b = myDatset.Tables[0].Columns.Count;

            for (int i = 0; i < a; i++)
            {
                for (int j = 0; j < b; j++)
                {
                    result = result + myDatset.Tables[0].Rows[i][j].ToString();
                    result = result + "   ";
                }
                result = result + "\n";
            }



            return result;

        }

        //public static void checkListBind(string cmdStr, ref CheckedListBox clb)
        //{


        //    SqlConnection conn = new SqlConnection(connStr);
        //    conn.Open();
        //    SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn);
        //    DataSet myDatset = new DataSet();
        //    myAdap.Fill(myDatset);

        //    for (int i = 0; i < myDatset.Tables[0].Rows.Count; i++)
        //        clb.Items.Add(myDatset.Tables[0].Rows[i][0].ToString());

        //    //clb.SelectedIndex = 0;

        //    myAdap.Dispose();
        //}

        public static void ComboxBind(string cmdStr,string ViewName,string UserName, ref DropDownList dl)
        {

            int myCount = 0;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn);
            myAdap.SelectCommand.CommandType = CommandType.StoredProcedure;


            SqlParameter spt1 = new SqlParameter("@MyCount", SqlDbType.VarChar, 20);

            spt1.Direction = ParameterDirection.Output;

            spt1.Value = myCount;

            myAdap.SelectCommand.Parameters.Add(spt1);



            SqlParameter spt2 = new SqlParameter("@ViewName", SqlDbType.VarChar, 20);

            spt2.Direction = ParameterDirection.Input;

            spt2.Value = ViewName;

            myAdap.SelectCommand.Parameters.Add(spt2);


            SqlParameter spt3 = new SqlParameter("@UserName", SqlDbType.VarChar, 20);

            spt3.Direction = ParameterDirection.Input;

            if (UserName == "")
            {
                dl.Visible = false;
                return;
            }
          
            spt3.Value = UserName;
     
            myAdap.SelectCommand.Parameters.Add(spt3);


            DataSet myDatset = new DataSet();
            myAdap.Fill(myDatset);

            dl.Items.Clear();
            for (int i = 0; i < myDatset.Tables[0].Rows.Count; i++)
                dl.Items.Add(myDatset.Tables[0].Rows[i][0].ToString());

            dl.SelectedIndex = 0;

            myAdap.Dispose();
        }

        //用于查询并绑定到datagridview
        public static void DbQuery(string cmdStr, string myParameter, ref GridView dv)
        {



            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn);
            myAdap.SelectCommand.CommandType = CommandType.StoredProcedure;


            if (myParameter != "")
            {

                SqlDataAdapter myAdap1 = new SqlDataAdapter("GetPara", conn);
                int myCount = 0;
                myAdap1.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter spt1 = new SqlParameter("@Para_Count", SqlDbType.VarChar, 20);

                spt1.Direction = ParameterDirection.Output;

                spt1.Value = myCount;

                myAdap1.SelectCommand.Parameters.Add(spt1);

                SqlParameter spt2 = new SqlParameter("@Sp_Name", SqlDbType.VarChar, 20);

                spt2.Direction = ParameterDirection.Input;

                spt2.Value = cmdStr;

                myAdap1.SelectCommand.Parameters.Add(spt2);

                DataSet myDataSet1 = new DataSet();
                myAdap1.Fill(myDataSet1);

                string[] myArray = myParameter.Split(‘@‘);




                SqlParameter[] myList = new SqlParameter[myDataSet1.Tables[0].Rows.Count];
                for (int i = 0; i < myDataSet1.Tables[0].Rows.Count; i++)
                {

                    //myList[i].ParameterName = myDataSet1.Tables[0].Rows[i][0].ToString();
                    //MessageBox.Show("" + myDataSet1.Tables[0].Rows[i][0].ToString());
                    myList[i] = new SqlParameter();
                    myList[i].ParameterName = myDataSet1.Tables[0].Rows[i][0].ToString();
                    myList[i].SqlDbType = SqlDbType.NVarChar;
                    myList[i].Size = 256;
                    myList[i].Direction = ParameterDirection.Input;
                    myList[i].Value = myArray[i];

                    myAdap.SelectCommand.Parameters.Add(myList[i]);
                }
            }
            DataSet myDatset = new DataSet();
            myAdap.Fill(myDatset);

            if (myDatset.Tables.Count == 0)
            {
                return;
            }
            dv.DataSource = myDatset.Tables[0].DefaultView;






            dv.DataBind();
            //dv.Columns[1].HeaderStyle.Width = new Unit("124"); 
            myAdap.Dispose();



        }
        //用于查询并绑定到datagridview

        public static string DbReturn(string cmdStr)
        {
            string returnStr = "";


            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand myCmd = new SqlCommand(cmdStr, conn);
            if (myCmd.ExecuteScalar() != null)
                returnStr = myCmd.ExecuteScalar().ToString();
            else
                returnStr = "";


            return returnStr;
        }

    

        public static DataTable DbQueryTable(string cmdStr)
        {



            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn);
            DataSet myDatset = new DataSet();
            myAdap.Fill(myDatset);


            myAdap.Dispose();
            return myDatset.Tables[0];


        }





        public static long DbCount(string cmdStr)
        {
            int myCount = 0;


            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand mycmd = new SqlCommand(cmdStr, conn);
            myCount = (int)mycmd.ExecuteScalar();
            conn.Close();



            return myCount;
        }

        public static void DbEdit(string editStr)
        {

            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand mycmd = new SqlCommand(editStr, conn);
            mycmd.ExecuteScalar();
            conn.Close();
        }
        //过滤非法字符
        public static string FilterSpecial(string str)
        {
            if (str == "")
            {
                return str;
            }
            else
            {
                str = str.Replace("‘", "");
                str = str.Replace("<", "");
                str = str.Replace(">", "");
                str = str.Replace("%", "");
                str = str.Replace("‘delete", "");
                str = str.Replace("‘drop", "");
                str = str.Replace("‘alter", "");
                str = str.Replace("‘add", "");
                str = str.Replace("‘‘", "");
                str = str.Replace("\"\"", "");
                str = str.Replace(",", "");
                str = str.Replace(".", "");
                str = str.Replace(">=", "");
                str = str.Replace("=<", "");
                str = str.Replace("-", "");
                str = str.Replace("_", "");
                str = str.Replace(";", "");
                str = str.Replace("||", "");
                str = str.Replace("[", "");
                str = str.Replace("]", "");
                str = str.Replace("&", "");
                str = str.Replace("#", "");
                str = str.Replace("/", "");
                str = str.Replace("-", "");
                str = str.Replace("|", "");
                str = str.Replace("?", "");
                str = str.Replace(">?", "");
                str = str.Replace("?<", "");
                //str = str.Replace(" ", "");
                return str;
            }
        }
    }
}