(5) C # 's ADO handwriting SqlHelp.cs and improvements

In order to more quickly ask the database, and more in line with the principle of object-oriented encapsulation, we should have a database operation class, SqlHelper.cs so that we do not have to access the database every time the connection, assignment, execution, can be more convenient step into place.

First write a relatively simple SqlHelp.cs, separate write ExecuteNonQuery, ExecuteScalar, ExecuteReader. ◇ Configure the connection variables first (mentioned in the previous section)

Private Static string connstr = configurationmanager.connectionstrings["connstr"]. ConnectionString;

◇sqlhelper.cs in ExecuteNonQuery ():

         Public Static int ExecuteNonQuery (string  sql)        {            usingnew  SqlConnection (CONNSTR))            {                Conn. Open ();                 using (SqlCommand cmd = conn.) CreateCommand ())                {                    = sql;                     return cmd. ExecuteNonQuery ();         }}}

◇ Where: ① for other files convenient to call, here all the self-write method is set to static method (can not instantiate direct calls, the same as) the ② system comes with the ExecuteNonQuery () method return value is an int type, which returns the number of rows affected in the database.    ◇sqlhelper.cs in ExecuteScalar ():

           Public Static Object ExecuteScalar (string  sql)        {            usingnew  SqlConnection (CONNSTR))            {                Conn. Open ();                 using (SqlCommand cmd = conn.) CreateCommand ())                {                    = sql;                     return cmd. ExecuteScalar ();         }}}

◇ this and the above ExecuteNonQuery () there is not much difference, the only difference is the function type, the system comes with the ExecuteScalar () is the range of a row of query results, there may be a number of variable types, so we here to define its type as object,  Wait until you need to use the type conversion. ◇sqlhelper.cs in Executedatatable (): (That is, the return collection table of the Executeread () method)

         Public StaticDataTable executedatatable (stringSQL) {            using(SqlConnection conn =NewSqlConnection (CONNSTR)) {Conn.                Open (); using(SqlCommand cmd =Conn. CreateCommand ()) {Cmd.commandtext=SQL; SqlDataAdapter da=NewSqlDataAdapter (CMD); DataSet DS=NewDataSet (); Da.                    Fill (DS); DataTable DT= ds. tables[0]; returnDT; }            }        }

◇ try to write a button click event to test executedatatable (), Proof handwriting simple sqlhelper error.

Private void button2_click (object  sender, RoutedEventArgs e)        {           = Sqlhelper.executedatatable ("select * from Student");            foreach inch dt. Rows)           {               string name = (string) dr[0];               MessageBox.Show (name);           }        }

The next step is to improve the sqlhelper of the simple and easy-to-read: ◇ the improvement is mainly in the input of some key parameters in the SQL statement. Avoid stitching strings causing injection exploits. ◇ The key string "variable length parameter" is introduced first.params"This keyword. This keyword can only be placed before the last parameter of the function is defined. ◇ then the ExecuteNonQuery () method is improved:

         Public Static intExecuteNonQuery (stringSqlparamssqlparameter[] Parameters) {            using(SqlConnection conn =NewSqlConnection (CONNSTR)) {Conn.                Open (); using(SqlCommand cmd =Conn. CreateCommand ()) {Cmd.commandtext=sql; cmd.                    Parameters.addrange (Parameters); returncmd.                ExecuteNonQuery (); }            }        }          Public Static ObjectExecuteScalar (stringSqlparamssqlparameter[] Parameters) {            using(SqlConnection conn =NewSqlConnection (CONNSTR)) {Conn.                Open (); using(SqlCommand cmd =Conn. CreateCommand ()) {Cmd.commandtext=SQL; Cmd.                    Parameters.addrange (Parameters); returncmd.                ExecuteScalar (); }            }        }          Public StaticDataTable executedatatable (stringSqlparamssqlparameter[] Parameters) {            using(SqlConnection conn =NewSqlConnection (CONNSTR)) {Conn.                Open (); using(SqlCommand cmd =Conn. CreateCommand ()) {Cmd.commandtext=SQL; Cmd.                     Parameters.addrange (Parameters); SqlDataAdapter Adapter=NewSqlDataAdapter (CMD); DataSet DataSet=NewDataSet (); Adapter.                    Fill (DataSet); returnDataSet. tables[0]; }            }        }

◇ Description: cmd.   Parameters.addrange (Parameters);  This sentence is equivalent to the sqlparameter[] in this array of parameters all added to the cmd. Below try to write a code to prove that the above code is correct.

        Private voidButton3_Click (Objectsender, RoutedEventArgs e) {DataTable dt= Sqlhelper.executedatatable ("SELECT * from Student where name = @name",NewSqlParameter ("@name","Xuweixi")); foreach(DataRow Drinchdt. Rows) {stringName = (string) dr[0];           MessageBox.Show (name); }        }

◇ This completes the SqlHelper.cs improved version of the writing.

(5) C # 's ADO handwriting SqlHelp.cs and improvements