SqlCommand common Properties and methods of WinForm development

The SqlCommand class represents a Transact-SQL statement or stored procedure to be executed against a SQL Server database, with several attributes and several methods that can be found on MSDN using a variety of methods.

Here are a few common things

First, the attribute

CommandText Gets or sets the Transact-SQL statement, table name, or stored procedure to be executed against the data source.
CommandTimeout Gets or sets the wait time before terminating an attempt to execute a command and generating an error.
CommandType Gets or sets a value that indicates whether the release CommandText is an SQL statement, a stored procedure, or a table operation.
Connection Gets or sets the SqlConnection used by this instance of SqlCommand.
Parameters get sqlparametercollection.
Transaction Gets or sets the sqltransaction in which the SqlCommand will be executed.

Static voidMain (string[] args) {            stringstr ="server=.; Database=juntest;uid=sa;pwd=123"; SqlConnection Conn=NewSqlConnection (str); SqlCommand cmd=Conn.            CreateCommand (); Cmd.commandtext="Update person Set Name = ' ado Modify ' WHERE Id = @Id";//Set ACTION Statement//take a look at the default error wait time (seconds)Console.WriteLine (Cmd.commandtimeout);// -//Indicates whether CommandText is an SQL statement, a stored procedure, or a table operation. Enumeration type, go to definition to seeConsole.WriteLine (Cmd.commandtype);//The output Text indicates that this is an SQL statement Cmd.commandtext=commandtype.storedprocedure/commandtype.text//SqlConnection instance object for SqlCommandConsole.WriteLine (cmd. connection.connectionstring);//"server=.;            Database=juntest;uid=sa;pwd=123 "; //Setting parameter valuesCmd. Parameters.Add ("@Id", SqlDbType.Int); Cmd. parameters["@Id"]. Value =1; Console.WriteLine (cmd. parameters["@Id"]. Value);//Output 1Conn.            Open (); Conn.            Close ();        Console.readkey (); }

Second, the method

1. SqlCommand.ExecuteNonQuery method

The deletion of the database is used for this operation, except that the SQL statements and stored procedures inside the call are different

Static voidMain (string[] args) {            stringstr ="server=.; database=juntest;uid=sa;pwd=123;"; SqlConnection Conn=NewSqlConnection (str);//Create a connectionSqlCommand cmd = conn. CreateCommand ();//Create commandCmd.commandtext ="Update person Set personname = ' ado Modify ' WHERE PersonId = @Id";//Set ACTION StatementCmd. Parameters.Add ("@Id", SqlDbType.Int);//Add parameter, description typeCmd. parameters["@Id"]. Value =1;//Setting parameter valuesConn. Open ();//Open Connection            inti = cmd. ExecuteNonQuery ();//execute command, ExecuteNonQuery by name, only for non-query statementsConn. Close ();//Close ConnectionConsole.WriteLine (i);//output affects the number of rowsConsole.readkey (); }

2,executescalar ()

Create a SqlCommand, and then use executescalar to execute it. pass two strings to the example, one string representing the new value to insert into the table, and another string to connect to the data source. If a new row has been inserted, this function returns the new Identity column value, or 0 if it fails.

Static  Public intAddproductcategory (stringNewName,stringconnstring) {Int32 Newprodid=0; stringsql ="INSERT into Production.productcategory (Name) VALUES (@Name);"+"SELECT CAST (scope_identity () as int)"; using(SqlConnection conn =NewSqlConnection (connstring)) {SqlCommand cmd=NewSqlCommand (SQL, conn); Cmd. Parameters.Add ("@Name", SqlDbType.VarChar); Cmd. parameters["@name"]. Value =NewName; Try{Conn.            Open (); Newprodid=(Int32) cmd.        ExecuteScalar (); }        Catch(Exception ex) {Console.WriteLine (ex).        Message); }    }    return(int) Newprodid;}

3,ExecuteReader ()

Static voidMain (string[] args) {            stringstr ="server=.; database=juntest;uid=sa;pwd=123;"; SqlConnection Conn=NewSqlConnection (str);//Create a connectionSqlCommand cmd = conn. CreateCommand ();//Create commandCmd.commandtext ="SELECT * from person";//Set ACTION StatementConn. Open ();//Open Connection//SqlDataReader reading Data            using(SqlDataReader reader =cmd. ExecuteReader ()) { while(reader. Read ()) {Console.WriteLine (reader[0] +":"+ reader[1]);//outputs the first column of the current row, and the second column of data}} cmd.commandtext="SELECT Count (*) from person"; Objectobj = cmd. ExecuteScalar ();//query only the first column of the first rowConsole.WriteLine ((int) (obj); Conn.                                       Close (); //Close ConnectionConsole.readkey (); }

4.ExecuteXmlReader () returns the System.xmlreader instance for reading the value of an XML field in SQL Server

The database word Gencun is XML

<article>  <author Age= "+">Tom</author>  <length>12000</length>  < Price>42</ Price></article>

Static voidMain (string[] args) {            stringstr ="server=.; database=juntest;uid=sa;pwd=123;"; SqlConnection Conn=NewSqlConnection (str);//Create a connectionSqlCommand cmd = conn. CreateCommand ();//Create commandCmd.commandtext ="SELECT * from person";//Set ACTION StatementConn. Open ();//Open ConnectionCmd.commandtext="SELECT Top 1 articleinfo from article";//querying XML Fields            using(XmlReader reader = cmd.) ExecuteXmlReader ())//returns an instance of XmlReader from a command instance            {                 while(reader. Read ()) {if(Reader. Name = ="author"{Console.WriteLine (reader).       ReadInnerXml ()); //output Zhang San}}} conn.                   Close ();        Console.readkey (); }

There are asynchronous versions of the above methods, which are only examples of the following two methods:

5, Beginexecutenonquery () asynchronous version ExecuteNonQuery ()

6, Endexecutenonquery () asynchronous version ExecuteNonQuery ()

classProgram {Static voidMain (string[] args) {            stringstr ="server=.; database=juntest;uid=sa;pwd=123; Asynchronous Processing=true"; SqlConnection Conn=NewSqlConnection (str);//Create a connectionSqlCommand cmd = conn. CreateCommand ();//Create commandCmd.commandtext ="INSERT into Person VALUES (11, ' Guo Jia ')";//Set ACTION StatementConn. Open ();//Open ConnectionCmd. Beginexecutenonquery (Begincallback, CMD);//Execute statements asynchronouslyConsole.WriteLine ("I'm going to do my job, whether you're done or not.");        Console.readkey (); }         Public Static voidbegincallback (IAsyncResult result) {Console.WriteLine ("Executing SQL command!"); SqlCommand cmd= result. AsyncState asSqlCommand;//get an asynchronous passed in parameterConsole.WriteLine ("Execute command successfully:"+cmd.commandtext); Console.WriteLine ("This execution affects the number of rows:"+cmd.            Endexecutenonquery (result)); Console.WriteLine ("Close the connection!"); Cmd.     Connection.close (); //formally close the connection        }    }

Part of this article comes from MSDN, a part of the blog from the inverse Heart

SqlCommand common Properties and methods of WinForm development