数据库操作必备

using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Drawing;
namespace DAL
{
    public static  class DbOperation
    {
        private static string connStr = "Data Source=127.0.0.1;Initial Catalog=Toy;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, ref ComboBox cb)
        {


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

            cb.Items.Clear();

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

            cb.SelectedIndex = 0;

            myAdap.Dispose();
        }

        
        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;
        }

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



            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlDataAdapter myAdap = new SqlDataAdapter(cmdStr, conn);
            DataSet myDatset = new DataSet();
            myAdap.Fill(myDatset);
            dv.DataSource = myDatset.Tables[0].DefaultView;
            dv.AllowUserToAddRows = false;
            dv.AllowUserToDeleteRows = false;
            dv.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dv.ScrollBars = ScrollBars.Both;
            dv.RowsDefaultCellStyle.BackColor = Color.FromArgb(224,224,224) ;
            dv.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(255, 255, 255);
           // dv.AlternatingRowsDefaultCellStyle = 
        }
        //用于查询并绑定到datagridview


        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 void DbEditImage(string editStr,string myImage)
        {

            //SqlConnection conn = new SqlConnection(connStr);
            //conn.Open();


            //SqlParameter para = new SqlParameter(myImage, SqlDbType.Image, bytes.Length);
            //para.Value = bytes;
            //SqlCommand ins_cmd = new SqlCommand(editStr + myImage + ")", conn);
            //ins_cmd.Parameters.Add(para);
            //ins_cmd.ExecuteNonQuery();


            //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;
            }
        }


        //过滤非法字符
        public static bool FilterIsSpecial(string str)
        {
            string flag = str;
            if (str == "")
            {
                return false ;
            }
            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(" ", "");
                if (str == flag)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }
    }
}