. NET Basics Operations Review _ use ADO to operate the classes used by SQL Server

Some tools used for a long time or have new tools appear, slowly forgotten a lot, they become unfamiliar from the familiar, of course, it is not good for us.

Today, let's review the basic class library of the MS that is used by ADO, first on the code (standard SQL Server Operations)

1) A basic approach to insert encapsulation  

        Using System;
Using System.Collections;
Using System.Text;

Using System.Data

Using System.Data.SqlClient;

 Public Static BOOLADD (Hashtable model) {StringBuilder strSQL=NewStringBuilder (); Strsql.append ("INSERT INTO Sysuser ("); Strsql.append ("userid,effectivetime)"); Strsql.append ("VALUES ("); Strsql.append ("@[email protected])"); sqlparameter[] Parameters= {NewSqlParameter ("@UserID", SqlDbType.VarChar, -),NewSqlParameter ("@EffectiveTime", SqlDbType.VarChar, -)};parameters[0]. Value = model["UserID"];parameters[1]. Value = model["Effectivetime"]; returnSqlhelper.executenonquery (connstring, strsql.tostring (), parameters) >0; }

The above method uses four basic classes: Hashtable is used to store incoming parameters (key/value combination); StringBuilder is used to stitch strings, because StringBuilder allocates storage space, which is much more efficient than string ; SqlParameter, passing arguments to SqlCommand; string acts as the final SQL string parameter (translated by StringBuilder).

2) Method of execution
 Public Static intExecuteNonQuery (stringConnString,stringCmdtext,paramssqlparameter[] cmdparms) {SqlCommand cmd=NewSqlCommand (); using(SqlConnection conn =NewSqlConnection (connstring)) {PrepareCommand (CMD, conn,NULL, CommandType.Text, Cmdtext, cmdparms); intval =cmd.                ExecuteNonQuery (); Cmd.                Parameters.clear (); returnVal; }        }

The above method also uses four classes: string (skip ); SqlParameter, passing arguments to the SqlCommand, SqlCommand class, a Transact-SQL statement or stored procedure that executes against the database Server (cannot be inherited), SqlConnection class, representing the Open connections to SQL Server databases (cannot be inherited).

3) How to query
 Public StaticDataSet Adapter (stringConnString,stringCmdtext,paramssqlparameter[] cmdparms) {SqlCommand cmd=NewSqlCommand (); DataSet DS=NewDataSet (); using(SqlConnection conn =NewSqlConnection (connstring)) {PrepareCommand (CMD, conn,NULL, CommandType.Text, Cmdtext, cmdparms); SqlDataAdapter Ada=NewSqlDataAdapter (CMD); Ada.                Fill (DS); Cmd.                Parameters.clear (); returnds; }        }

The above method also uses six classes: string (skip ); SqlParameter, passing arguments to SqlCommand;SqlCommand class, a Transact-SQL statement or stored procedure that executes against a SQL Server database (cannot be inherited) The SqlConnection class, which represents an open connection to a SQL Server database (cannot be inherited);D Ataset, a data cache class stored in memory; SqlDataAdapter, a set of data commands for populating a dataset and updating a SQL Server database, and a database connection with at least one DataTable (one in-memory data table Class) in a dataset.

4) The execution class of the transaction

 Public Static intExecuteNonQuery (stringConnString, list<string> Listcmdtext, list<sqlparameter[]>listcmdparms) {            if(Listcmdtext.count! =listcmdparms.count) {return-1; }            intval =0; SqlCommand cmd=NewSqlCommand (); using(SqlConnection conn =NewSqlConnection (connstring)) {                if(Conn. State! =ConnectionState.Open) {conn.                Open (); } sqltransaction Trans=Conn.                BeginTransaction (); Try                {                     for(inti =0; i < Listcmdtext.count; i++) {PrepareCommand (CMD, conn, trans, CommandType.Text, Listcmdtext[i], LISTC                        Mdparms[i]); Val+=cmd.                        ExecuteNonQuery (); Cmd.                    Parameters.clear ();                } trans.commit (); }                Catch(Exception ex) {trans.                    Rollback (); if(Conn. state = =ConnectionState.Open) {conn.                    Close (); }                    Throwex; }                finally                {                    if(Conn. state = =ConnectionState.Open) {conn.                    Close (); }                }            }            returnVal; }

Class not described above: SqlTransaction, the class of Transact-SQL transactions generated in a SQL Server database (for multiple SQL executions); List of strongly typed lists of objects accessed by the index.

Summary:

1) Splicing SQL string: String to be used, StringBuilder

2) Universal parameter transfer: List, HashTable

3) Special class (link other databases with different approximate class libraries): Parameter passing class, SqlParameter; Database connection class, SqlConnection; Transaction class, SqlTransaction;SqlCommand, command execution class; SqlDataAdapter, command execution and link classes.

. NET Basics Operations Review _ Use ADO to manipulate SQL Server classes