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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.