ADO Comprehensive Finishing

A simple introduction under the ADO

Learn about some of the classes we commonly use under the System.Data namespace:

1①system.data  →datatable,dataset,datarow,datacolumn,datarelation,constraint,datacolumnmapping, DataTableMapping 2②system.data.coummon     → base classes and interfaces for various data access classes 3③system.data.sqlclient   → data access classes that operate on SQL Server 4   main:   a) SqlConnection            → database connector 5             b) SqlCommand               → database naming object 6             c) SqlCommandBuilder        → Survival SQL command 7             d) SqlDataReader            → data reader 8             e) SqlDataAdapter           → data adapter, populating DataSet 9             F) SqlParameter             → defining parameters for stored procedures (             g) SqlTransaction           → database Things

Ii. SqlConnection (Connection object)

　　1. Connection string

Basic syntax: DataSource (data source) + database name (Initial Catalog) + username (user ID) + password (Password) (this way is more secure)!

Recommended article: SQL Server connection string and authentication, you must know the ADO (c) connection string, you underestimated it? , SQL Server 2008 connection string notation, the connection string has a lot of writing, the safest way to use the "SqlConnectionStringBuilder" class, it provides a comprehensive connection string properties, So that the error rate is reduced (related properties check MSDN), and most of the connection strings are written in the config file!

　　2. Create a Connection object

1 SqlConnectionStringBuilder connectionstringbuilder = new SqlConnectionStringBuilder () 2  {3      DataSource = "", 4      InitialCatalog = "", 5      UserID = "", 6      Password = "" 7  };8 SqlConnection connection = new SqlConnection (connectionstringbuilder.tostring ());

　　3. Open and close the Connection object (use using to close the connection)

1 using (SqlConnection connection = new SqlConnection (connectionstringbuilder.tostring ())) 2 {3     connection. Open (); 4     connection. Close (); 5}

Articles about connection pooling: you have to know. ADO (v) Detail database connection pool (it's really nice to write), top ...

Three, SqlCommand (Command object)

　　1. Four properties initialized by default when instantiating

　　2. Create a Command object

Use the CreateCommand () method of the Connection object to create the command object, or you can use new to instantiate the Object!

1 SqlCommand command = connection. CreateCommand (); This way is better, you can also instantiate an object yourself!

　3. Several important attributes

①commandtext: Gets or sets the Transact-SQL statement, table name, or stored procedure to be executed against the data source!

②commandtype: Sets whether the SQL statement you are executing is a stored procedure or T-SQL (an enumeration)!

　　　　

③parameters: Set the parameters you need to use in T-SQL (described later), is a "sqlparameterscollection" type, this property is very important, is that you pass the code to the SQL statement the path of parameters, so remember the syntax, Remember that some usage rules are very helpful for coding!

　　4. A few important methods (I believe you are familiar with the no longer familiar with)

①executenonquery: Returns the number of rows (int) that are affected, primarily performing updates, additions, deletions, and so on!

②executereader: Execute SQL or stored procedure, return is the type of SqlDataReader, mainly used to query!

★ This way note the overloaded Commandbehaviour enumeration of this method, with the following members:

　　　　

1 command. ExecuteReader (commandbehavior.closeconnection); The connection object is closed automatically after the read is performed

③executescalar: Returns the first column of the first row in the execution result set, or null! if there is no data

Note: Because a "Null" value may be returned, the result needs to be judged as follows:

   Object my = cmd. ExecuteScalar ();                 if (object. Equals (My,null))  //can use equals to determine the null value, legibility strong                     Console.WriteLine ("Not Data");                 Else                     Console.WriteLine ("Yes");

④createparameter: Creating an SqlParameter instance

1 SqlParameter para = cmd. CreateParameter ()//This method is suitable for cases where there is only one parameter in the SQL statement!

Recommended article: You need to know. ADO (vi) talk about command objects and data retrieval

You have to know. ADO (vii) wow! Command Object Advanced Application

Iv. SqlParameter (SQL parameter)

　　1. A few important attributes

ParameterName: Setting parameter names

Value: Setting values for parameters

Size: Sets the maximum size of the parameter byte, in bytes but

SqlDbType: The type of the parameter in SQL

View Code

　　2. Several ways to add a parameter collection to a Command object

①addwithvalue

②add

③addrange

Recommended article: The function and usage of SqlParameter, the code is as follows:

 1 using (SqlConnection connection = new SqlConnection ("")) 2 {3 SqlCommand command = connection. CreateCommand (); 4 Command.commandtext = ""; 5 6//You can add multiple parameters in this way, but not good enough 7 command. Parameters.Add ("@name", SqlDbType.NVarChar). Value = "Yang"; The first way 8 command. Parameters.Add ("@age", SqlDbType.Int). Value = 888; 9 command. Parameters.Add ("@address", SqlDbType.NVarChar, 100). Value = "Jiang Su"; 10 11//This method directly given the parameter name and parameters can be, the operability of the poor command. Parameters.addwithvalue ("@name", "Yang"), command. Parameters.addwithvalue ("@age", 888). SqlDbType = sqldbtype.int;14 command. Parameters.addwithvalue ("@address", "Jiang su"). SqlDbType = sqldbtype.nvarchar;15 16//Use parameter collection directly to add the parameters you need, recommend this notation sqlparameter[] Parameters = new Sqlparameter[]1 8 {New SqlParameter ("@name", sqldbtype.nvarchar,100) {Value = "yang"},20 new SqlParameter ("@age", S qldbtype.int,2) {Value = 888},21 new SqlParameter ("@address", sqldbtype.nvarchar,20) {Value = "Jiang Su"},};23 command.  Parameters.addrange (Parameters); The parameter can also be an array, if the readability and extensibility of the array parameter code is not so good. 24 25//When we add the parameters, a "sqlparametercollection" collection type is generated, which is equivalent to the set of parameters 26//That We can modify and remove these parameters. 27//The "sqlparametercollection" inside is actually a collection of list<sqlparameter>, but it is relatively high in complexity, considering the overall comm and. Parameters[0]. Value = "Hot girl"; command. Parameters[0]. Size = 200;30}

　 3. Say "SqlParameterCollection", Parameter set

The "SqlParameter" parameter added above has been added to the "SqlParameterCollection" collection so that we can read and modify it!

　　4. Define the appropriate parameter properties gets the return value of the stored procedure (return) →direction = ParameterDirection.Output

The code is as follows:

View Code

V. SqlDataReader (data stream reader)

To tell the truth, if only know how to use the reader, it is very studious, if in-depth understanding, its knowledge will be very attractive, then on my side of the experience to say, you do not take offense Ah!

1. Basic Usage

View Code

2. Common Methods

①getordinal: Gets the column ordinal (index number) of the specified column name, using this method to fix the frequently changed columns

1 int name = Dr. GetOrdinal ("name"); Get the index number of the column by its name so that it doesn't matter if your column name changes next time.

②getname: Gets the column name, the sequence number of the specified column name, and returns a string

1 string columnName = Dr. GetName (name); Get the column name name by the index number where the column name is located

③isdbnull: Determines whether the currently read data is Null, the return type is Bool

1   Dr. IsDBNull (Cocontactid)? "NULL": Dr. GetInt32 (Cocontactid). ToString ()//I believe everyone will use it.

④nextresult: When the query is a batch query, use this method to read the removal of a result set, the return value is bool, or true if there are multiple result sets ; false otherwise

1//select * from Employee;select * from county, so that you can use this way 2 Dr. NextResult (); Remember to put this in while (Dr. Read ()), the next data set cannot be read until a data set is read

⑤read: Reading data

The most important way to read data is not to mention it!

　　3. Common Properties

①hasrow: determines whether to include one or more rows, that is, to determine if there is no data, the return type is Bool

②FieldCount: Gets the number of columns read, the return type is Int

③isclosed: determine if the read stream is closed

So the flexible use of the above attributes to enhance the readability and robustness of the code, a comprehensive example:

View Code

　　　　Note: When SqlDataReader is closed, only the IsClosed and RecordsAffected properties can be called, and if other methods or properties are called, an error will be added!

　　4. Performance in-depth analysis

There are many ways to read data, such as dr[0]. ToString (), dr["Name"]. ToString (), Dr. GetString (0), Dr. GetSqlString (0) and so on the reading method of writing, if you go online to check the data will be easy to find that there are some differences in these ways!

The following is a summary of read data performance:

1     SqlDataReader Read method: 2     1. DataReader     Index   + based on [serial number]→dr[0]. ToString        | index-based ACCESS3     2. DataReader     Index   + based on [column name]  →dr["name"]. ToString     | Worst Performance 4     3. Get            starts with the + based on [serial number]→DR. GetString (0)      | type-access5     4. GetSQL         starts with the + based on the [serial number]→DR. GetSqlString (0)   | Provider-specific typed Accessor6     5. GetOrdinal ()   Gets the serial number of this column by column name                      | This method has a role in improving performance 7     6. Performance (4)--(3)--(1)--(2)

Note: So when reading data to have to use some high performance methods, nor to pursue performance, but it is a habit, for most of the methods of reading the database using the index to read is undoubtedly the fastest, remember a word, "when performance has not become a problem, Do not over-optimize it ", efficient and graceful use of these methods, is the kingly!"

Recommended article: SqlDataReader performance issues with early termination

　　　　PS: My summary here is actually before in the garden to see a person wrote, find for half an hour have not found, if someone saw, send a link to me, I make up!

　　Discussion of 5.SqlDataReader and datasets

Recommended article: Talk about the optimization of asp: SqlDataReader and DataSet selection

Vi. sqltransaction (Business)

1. Transactions in the Code　　

Now the code basically uses the stored procedure to control the transaction processing, the control transaction through the code is also one of the tasks that we learn ADO!

A transaction is created after the connection object, and it is associated with a command object, using the Try ... Catch catches the exception, and then calls the rollback method to roll back the transaction!

Commit: Submit

RollBack: Rolling back

View Code

2. Named storage points in a transaction　　　　　

Once you have defined a named storage point, you can only roll back the operation after the named storage point, which is used if the situation!

This is the case when you call the Rollback method and reload the parameters of a named storage point, as shown in the following code:

1     using (SqlConnection conn = new SqlConnection (str)) 2     {3          conn. Open (); 4          sqltransaction transaction = conn. BeginTransaction (); 5          SqlCommand cmd = conn. CreateCommand (); 6          Cmd.commandtext = ""; 7          cmd. Transaction = Transaction; 8          //use named Storage point 9          transaction. Save ("This was point");  Define a named storage point, use the Save method to save the storage point, and define the starting position of the rollback data.          This is the operation code you want to roll back.          //Roll back the operation from the named storage point to the          transaction. Rollback ("This was point");  Rollback named Storage point (*)      

　　Transactions in 3.SQL statements (transactions in SQL Server are actually very complex)

1 BEGIN TRANSACTION2 3     -you need to perform an update, delete, insert the statement 4     5 IF (@ @ERROR > 0)//This is a system variable that stores the record number of the error that occurred when you performed the update, delete, insert operation 6     ROLLBACK7 ELSE8     COMMIT

Recommended article: Talking about the acid of transactions in SQL Server

Drill down into a transaction in SQL Server

　　4. Talk about "TransactionScope" to make matters easier

1 using (TransactionScope TransactionScope = new TransactionScope ()) 2 {3     try 4     {5         using (SqlConnection conn ection = new SqlConnection ()) 6         {7             //To do 8             //COMMIT TRANSACTION, if there is an exception, he will automatically rollback the 9             transactionscope.complete (); 10         }11     }12     catch (Exception)-     {         //catch exception-         throw;16     }17}

Recommended article: C # Comprehensive Disclosure--detail affairs

Seven, SqlDataAdapter (data adapter)

　　1. Constructors

14 Overloads:    2   1. No parameter 3   2. SqlDataAdapter (SqlCommand)  → Execute Command object instance 4   3. SqlDataAdapter (String, SqlConnection) →① can only specify query statements ② Connection object instance 5   4. SqlDataAdapter (String, ConnectionString) → Initializes a new instance of the SqlDataAdapter class with SelectCommand and a connection string 6   Note: The fourth overload includes both the connection object and the Command Object!

　　2. Filling data (fill)

The simplest fill data

1 DataSet DataSet = new DataSet (), 2 using (SqlConnection conn = new SqlConnection ("")) 3 {4     conn. Open (); 5     SqlCommand command = conn. CreateCommand (); 6     command. CommandText = "Select name,age,address from Myinformation"; 7     SqlDataAdapter dataAdapter = new SqlDataAdapter ( command); 8     DataAdapter.Fill (dataSet);  Populating Data 9}

3. Use "SqlCommandBuilder" to make the data

① Adding data

1  using (SqlConnection conn = new SqlConnection (ConnectionString ())) 2  {3      conn. Open (); 4      //Build a query statement, you can also specify SqlCommand, where the transformation method has many 5      SqlDataAdapter da = new SqlDataAdapter ("Select Lastname,firstname FROM dbo. Employees ", conn); 6      DataSet ds = new DataSet (); 7      da. Fill (DS); 8      //This is important, it will convert the data you added in the dataset into SQL statements to update the database 9      sqlcommandbuilder cmdbuilder = new SqlCommandBuilder (DA); 10      //Add a row, instantiate a row object, and note that the row      is created with NewRow DataRow row = ds. Tables[0]. NewRow ();      row[0] = "Yang";      row[1] = "Ghost head";      ds. Tables[0]. Rows.Add (row);  Added to the table of the      da. Update (DS);             Compare the table in the dataset to the database and update the  

② Modifying data

1 using (SqlConnection conn = new SqlConnection ("")) 2 {3     SqlDataAdapter da = new SqlDataAdapter ("SQL statement or Command object of your own definition", conn); 4     DataSet ds = new DataSet (); 5     da. Fill (DS); 6     //Very important sentence 7     sqlcommandbuilder cmdbuilder = new SqlCommandBuilder (DA); 8     ds. Tables[0]. ROWS[12][1] = ""; Modify Data 9     da. Update (DS);     call the Update method implicitly call the AcceptChanges method, update the data in the dataset one     //If you continue to use this data set without calling this method, an exception will occur in the subsequent use 12     ds. AcceptChanges ();  This sentence can not be written 13}

③ Deleting data

1 using (SqlConnection conn = new SqlConnection (""))

2  {3      SqlDataAdapter da = new SqlDataAdapter ("SQL statement or Command object of your own definition", conn); 4      DataSet ds = new DataSet (); 5
   da. Fill (DS); 6      SqlCommandBuilder cmdbuilder = new SqlCommandBuilder (DA); 7      //delete data 8      ds. Tables[0]. ROWS[12]. Delete (); 9      da. Update (DS);  This way, the AcceptChanges method of the DataTable is implicitly called.  

Note: It is importantto note that the Update method has implicitly helped me to invoke the AcceptChanges, no more than worrying about the state to change the deletion data will be error, Microsoft has helped us all do, In fact, the table.acceptchanges () this thing, if what happens in a normal DataTable, commits all the changes made to the row since the last call to AcceptChanges , after calling this method, All changes in the table will be committed, all row state (RowState) states will become unchanged, and in the dataset I will talk about them!

　　4. Some discussions on the Fill method in "SqlDataAdapter"

Simply write it on MSDN, check it out!

1★ Specifies the number of padding data, such as: 2//Fill dataset with data from line fifth to line tenth   

Viii. Dataset,datatable,datarow,datacolumn

Indicates that the data is stored in the cache, and the dataset can contain multiple datatable,datatable in multiple DataColumn and multiple DataRow, including operations on the DataTable, and operations on columns and rows, in a dataset, When working with a DataTable, you should first determine if they are null, which is the most basic!

　　1.datatable,datarow,datacolumn

① to create your own DataTable

View Code

② simplifies code with object collection initializers and uses "DataColumnCollection" and "DataRowCollection" to manipulate rows and columns that have been added

The constructor's access modifier is internal, which allows you to "add, delete, change, check" the column and row, and see MSDN in detail, such as Remove,add,removeat etc!

1 dt. Columns.Add (New DataColumn ("Age", typeof (Int32)); 2 dt. Columns.Add (New DataColumn () 3 {4     ColumnName = "Address", 5     DataType = typeof (String), 6     defaultvalue = "Haian, Jiangsu "7}"); 8//Our side uses the Add method for the second overload of 9 dt. Rows.Add (new object[] {"One", "222", "Yang Cao Gui"}); 10//We can also read and modify the added rows and columns by DT. Columns[0]. ColumnName = "Wang Wei"; dt. rows[0]["Wang Wei"] = "I changed the value of this row, haha";

③ Use the expression tree to quickly build your own columns, or you can check MSDN in detail

View Code

　　2.DataRowState (line status), DataRowVersion (row version)

①datarowstate (line state) is a very important state in "DataRow", there are five main aspects:

1     Added          → add 2     Deleted        → Delete 3     Detached       → Detach 4     Modified       → Modify 5     unchanged      → for change

Note : If a row is instantiated, but not added to any table, then its state is always detached, there is no add,modified of the points, this is to be noted!

②datarowversion (row version), there are four version states, as follows:

1     current → most recent row, mainly for deleted before the operation of the row, row state of the Deleted2     default → row defaults state 3     Original    → Row of the original value Added,modified,unchanged    4     proposed    

PS: If you want to understand them, you should write some sample code to emulate them so that you can get to know them well!

Strongly recommend this article: → deep into the. NET DataTable(very carefully written, good, sure to see), and this article → deep. NET DataTable (Addendum).

　　3.dataset,datatable

More important methods: Select,merge,copy,clone,getchanges and so on.

About the GetChanges method: To get the data changes, it gets the information that you modified the data after you last called the AcceptChanges method, so be careful!

　　4. The following is a good collection of articles about datasets and DataTable　　

DataTable.Select use of small experience

Advanced analysis of data source binding principle in DataTable

Compare two DataTable data (same structure)-50,000 data in seconds

　　5. Knowledge of "DataRelation" and "DataView" is also important

　　　　DataView → Custom view that represents the bindable data for sorting, filtering, searching, editing, and navigating a DataTable, see article: Dataset,datatable,dataview

　　　　DataRelation → Represents a parent/child relationship between two DataTable objects, see article: an example of a DataRelation (parent-child table in a dataset)

Nine, package database operation class (this is the essence)

Only fully understand the above knowledge, in order to better package, to write a robust object-oriented database operation class Library, because I have limited knowledge, simple encapsulation can also, but not enough to see people, so collected some good articles for us to learn:

1.sqlhelper--double-edged sword (really good ~ ~) (Refining more robust code)

2. My DBHelper data manipulation Class (Custom action class library)

3.JSM SqlHelper 2.0 Source Download (more detailed packaging, the opportunity to study)

4. Microsoft original SqlHelper Class (code too many, there are many we will not use, there are too many overloaded too!) ）

ADO Comprehensive Finishing