Ado. NET Development Tips (3)-Execute command Preliminary

Very good, recruit, we have successfully connected to the database, then we can let the database execute our command, that is, we can start a big game. Oh, of course, you still have to know how to get the database to execute commands. If you do not have the skills to be self-defeating, the abnormal army will immediately come and kill you.

Once the SqlConnection class uses the Open method, then, it means that we have connected to the database, oh, and so on, this is ... extraordinary armies! Idiot, you misspelled the connection string! What to do? Don't panic, Corporal, the toolkit Microsoft gave us is useless, let me open it, oh, this is try-catch-finally. The other is using, and it's more than enough to deal with them.

Cry-catch-finally is a syntax that can catch exceptions and handle them. Not much to say, first look at the Microsoft User Manual on how to use it:

              try  {                    conn. Open ();                     // do some things                 }                catch(Exception) {                    throw;                }                 finally {                    Conn. Close ();                    Conn. Dispose ();                }

We first open the connection within the try block, so that if there is an exception thrown, then we can catch it into the catch block, and we can determine what the exception is and write the processing code. In the final finally statement block, the object is executed regardless of the result above, and here we choose to have it close the database and release the resources.

You will probably wonder why we have to manually release resources, we do not have a Microsoft-provided GC artifact. I regret to tell you that the GC artifact cannot automatically reclaim this SqlConnection object, but Microsoft has provided another mechanism for automatic release, which is the IDispose interface. As long as the object that implements this interface can be automatically freed with the Using keyword:

using New SqlConnection ()) {                    // execute some command                     Co. Open ();                }

Yes, classes that implement the IDispose interface within a using statement can be automatically freed.

Well, then let's start executing the order!

Our first step is to create a command:

using New SqlConnection ()) {                    // execute some command                     Co. Open ();                     using (SqlCommand cmd = Co.) CreateCommand ()) {                        "insert INTO Task (Tid,ttitle) VALUES (546, ' Meeting ')" ;                         = commandtype.text;                                            }                }

Where type types are the SQL command (Text) or stored procedure that we want to execute. It is text by default, so we don't need to specify it.

Okay, here's the function that executes the command, we have four functions to execute the command

Cmd. ExecuteNonQuery (); cmd. ExecuteScalar (); cmd. ExecuteXmlReader (); cmd. ExecuteReader ();

The first is a function that executes a non-query command, which returns an int type, representing several rows affected, such as (Insert,update,delete)

The second is a function that executes a single query, which returns the first column of the first row in the query results, such as:

" Select Tname from Task where TID = 1 " ;                         string  as string;

The third one returns a Sqlxmlreader, and we can process the data in a way that handles the DOM, but this involves more knowledge, so it's generally not practical.

The fourth returns a SqlDataReader object, which is suitable for arbitrary query commands. It relies on the read function for reading data. Every time you call read, he will point to the next line of data.

Here's how to use it:

using (var reader = cmd.) ExecuteReader ()) {                            while(reader. Read ()) {                                var tN = (string) reader["tname"];                                 var tid = (int) reader["tID"];                            }                        }

By the way, these things have an asynchronous version, that is, we just add the Await/async keyword can be executed asynchronously command, it is not too much to force Ah!

ADO. NET Development Tips (3)-Execute command preliminary