SQL Server database operations public class Dboperate

Using System; Using System.Collections.Generic; Using System.Linq; Using System.Text;

Using System.Data.SqlClient; Using System.Data; Using System.Windows.Forms; Using windowsformsapplication1.dbtools;//to provide a database connection

Namespace Liuxw_mps.     DBTools {//<summary>///database Operation public class. Improved: Combined with Sqlparamstool class, it is better to support dynamic SQL statement operation///</summary> class Newdboperate {

        Private SqlConnection conn=null;//Database Connection object          private sqltransaction  trans=null;//transaction        //< Summary>        ///Building a database connection in a constructor method         </summary>         public  newdboperate ()          {          conn=dbconnection.getconnection ();        }        //<summary>         //Open database connection        //</summary>         public  void  openconnection ()          {           if (conn. State!=coNnectionstate.open)            {               Conn. Open ();           }              }        //<summary>         //Close database connection        //</summary>          public  void CloseConnection ()         {           if (Conn. state!=connectionstate.closed)           {             Conn. Close ();          }        }                //<summary>        ///  Execute the SQL statement that needs to return the dataset;        //</summary>         //<param name= "SQL" ></param>        //< param name= "sp" ></param>        //<returns></returns>         Public DataSet GetDataSet (String sql, sqlparameter[] sp)          {            SqlDataAdapter SDA = new SqlDataAdapter (SQL, conn);             SqlCommand cmd = SDA. SelectCommand;             if (sp! = NULL)//sql statement has dynamic parameters, set parameters              {

               //>>> Mode 1. Set Parameters                //foreach ( SqlParameter param in SP)                 //{               //SDA. SELECTCOMMAND.PARAMETERS.ADD (param);

               //}                 //>>> mode 2. Setting parameters                  SDA. SelectCommand.Parameters.AddRange (SP);//Parameter SP cannot be a null value             }                           SDA. Selectcommand.transaction = trans;//transaction.             DataSet ds = new DataSet ();             SDA. Fill (DS);             return ds;          }        //<summary>        ///  executes the SQL statement that needs to return the DataTable table;        //</ Summary>        //<param name= "SQL" ></param>         //<param name= "sp" ></param>         ///<returns></returns>         public DataTable GetTable ( String sql, sqlparameter[] sp)         {             return this. GetDataSet (SQL,SP). Tables[0];        }        //<summary>         ///  Execute SQL statements that need to return SqlDataReader objects;         //</summary>        //<param name= "SQL" ></param>        //<param name= "SP" ></param>        //<returns> </returns>         Public SqlDataReader ExecuteReader (String sql, Sqlparameter[] sp)         {             SqlCommand cmd = new SqlCommand (SQL, conn);             if (sp! = null)              {                 cmd. Parameters.addrange (SP);//set SQL parameter; parameter SP cannot be null value             }             cmd. Transaction = trans;//use transactions; processing of database Things must assign a value of Transaction to cmd, default to null value              return cmd. ExecuteReader ();        }        ///<summary>         ///Returns the first column of the first row in the result set returned by the query. Other columns or rows are ignored.        ///can be used for user name login verification.        //</summary>        //< Returns> returns a single value of </returns>         public Object ExecuteScalar (String sql, Sqlparameter[] sp)         {             Object returnvalue = 0;             SqlCommand cmd = new SqlCommand (SQL, conn);             if (sp! = null)              {                 cmd. Parameters.addrange (SP);            }              cmd. Transaction = trans;//Use transaction             return CMD. ExecuteScalar ();

       }        //<summary>         //binding DataGridView;        //</ Summary>        //<param name= "DGV" ></param>         //<param name= "SQL" ></param>         //<param name= "SP" ></param>         public void Binddatagridview (DataGridView DGV, String sql, sqlparameter[] sp)         { & nbsp;          DGV. DataSource = this. GetTable (SQL,SP). defaultview;//setting up data sources              }         //<summary>        //Binding drop-down list, parameters "control", "Table name", "column"        //</summary>        //<param Name= "CB" ></param>        //<param name= "SQL" ></param>        //<param name= "sp" ></param>          public void Bindcombobox (ComboBox cb,string tablename,int column)      & nbsp;  {            sqldatareader  reader = this. ExecuteReader ("SELECT * from " +tablename,null);             while (reader. Read ())             {                 CB. Items.Add (Reader[column]. ToString ());            }             Reader. Close ();        }        //<summary>         ///  operation data (increase and deletion);        //</ Summary>        //<param name= "SQL" ></param>         //<param name= "sp" ></param>         ///<returns> Returns the number of rows affected </returns>         public int Operatedata (String sql,sqlparameter[] sp)         {             SqlCommand cmd = new SqlCommand (SQL, conn);             cmd. Transaction = trans;//Transaction             if (sp! = NULL)//sql parameter is not empty             {                               cmd. Parameters.addrange (SP);//set parameter; parameter SP cannot be null            }             int i = cmd. ExecuteNonQuery ();//Gets the number of rows affected by the operation             return i;        }

       //>>> transactions:        //< Summary>        //Open database transaction        // </summary>         public void BeginTransaction ()          {            trans = conn. BeginTransaction ();        }        //<summary>         //Submit transactions        //</summary>          public void Commit ()         {             Trans.commit ();        }        //<summary>         ///ROLLBACK TRANSACTION        ///</summary>          public void RollBack ()         {             Trans. Rollback ();        }        //<summary>         //Destruction Transactions        //</summary>          public void Disposetransaction ()         {             if (trans! = null)              {                 Trans. Dispose ();                 traNS = null;            }        }

} }

SQL Server database operations public class Dboperate