Common SqlCommand attributes and methods for Winform development, winformsqlcommand

Common SqlCommand attributes and methods for Winform development, winformsqlcommand

The SqlCommand class indicates a Transact-SQL statement or stored procedure to be executed on the SQL Server database. There are several attributes and several methods. The specific methods can be found on msdn.

Here we will introduce several common things

I. Attributes

CommandText gets or sets the Transact-SQL statement, table name, or stored procedure to be executed on the data source.
CommandTimeout gets or sets the waiting time before terminating the Command Execution Attempt and generating an error.
CommandType gets or sets a value that indicates whether 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 gets SqlParameterCollection.
Obtain or set the SqlTransaction in which the SqlCommand is executed.

Static void Main (string [] args) {string str = "server = .; database = JunTest; uid = sa; pwd = 123 "; SqlConnection conn = new SqlConnection (str); SqlCommand cmd = conn. createCommand (); cmd. commandText = "Update Person Set Name = 'ado. net modify 'where Id = @ Id "; // set the operation statement // check the default error wait time (seconds) Console. writeLine (cmd. commandTimeout); // 30 // specifies whether CommandText is an SQL statement, a stored procedure, or a table operation. Enumeration type. Go to the definition to see the Console. writeLine (cmd. commandType); // The output Text indicates that this is an SQL statement cmd. commandText = CommandType. storedProcedure/CommandType. the SqlConnection Instance Object Console of Text // SqlCommand. writeLine (cmd. connection. connectionString); // "server = .; database = JunTest; uid = sa; pwd = 123 "; // set the parameter value cmd. parameters. add ("@ Id", SqlDbType. int); cmd. parameters ["@ Id"]. value = 1; Console. writeLine (cmd. parameters ["@ Id"]. value); // output 1 conn. open (); conn. close (); Console. readKey ();}

Ii. Method

1. SqlCommand. ExecuteNonQuery Method

This operation is used to add, delete, modify, and delete databases, but the SQL statements called inside are different from the stored procedure.

Static void Main (string [] args) {string str = "server = .; database = JunTest; uid = sa; pwd = 123; "; SqlConnection conn = new SqlConnection (str); // create a connection SqlCommand cmd = conn. createCommand (); // CREATE Command cmd. commandText = "Update Person Set PersonName = 'ado. net modify 'where PersonId = @ Id "; // set the operation statement cmd. parameters. add ("@ Id", SqlDbType. int); // Add a parameter, indicating the type of cmd. parameters ["@ Id"]. value = 1; // set the parameter Value conn. open (); // Open the connection int I = cmd. executeNonQuery (); // execute the command. ExecuteNonQuery can only be used in non-query statement conn. close (); // Close the connection Console. writeLine (I); // The number of affected lines in the output Console. readKey ();}

2,ExecuteScalar ()

Create a SqlCommand and run it using ExecuteScalar. Two strings are passed to this example. One String indicates the new value to be inserted into the table, and the other string is used to connect to the data source. If a new row has been inserted, this function returns a new"Identity"Column value. If it fails, 0 is returned.

static public int AddProductCategory(string newName, string connString){    Int32 newProdID = 0;    string sql =        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "        + "SELECT CAST(scope_identity() AS int)";    using (SqlConnection conn = new SqlConnection(connString))    {        SqlCommand cmd = new SqlCommand(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 void Main (string [] args) {string str = "server = .; database = JunTest; uid = sa; pwd = 123; "; SqlConnection conn = new SqlConnection (str); // create a connection SqlCommand cmd = conn. createCommand (); // CREATE Command cmd. commandText = "SELECT * FROM Person"; // sets the operation statement conn. open (); // Open the connection // SqlDataReader reads data using (SqlDataReader reader = cmd. executeReader () {while (reader. read () {Console. writeLine (reader [0] + ":" + reader [1]); // output the first and second columns of the current row.} cmd. commandText = "SELECT Count (*) FROM Person"; object obj = cmd. executeScalar (); // only query the Console in the first column of the first row. writeLine (int) obj); conn. close (); // Close the connection Console. readKey ();}

4,ExecuteXmlReader () returns the System. XmlReader instance, which is used to read the XML field value in SQLServer.

Database fields are stored in xml

<Article> <author age = "30"> Zhang San </author> <length> 12000 </length> <price> 42 </price> </Article>

Static void Main (string [] args) {string str = "server = .; database = JunTest; uid = sa; pwd = 123; "; SqlConnection conn = new SqlConnection (str); // create a connection SqlCommand cmd = conn. createCommand (); // CREATE Command cmd. commandText = "SELECT * FROM Person"; // sets the operation statement conn. open (); // Open the connection cmd. commandText = "SELECT Top 1 ArticleInfo FROM Article"; // query the XML field using (XmlReader reader = cmd. executeXmlReader () // The XmlReader instance {while (reader. read () {if (reader. name = "author") {Console. writeLine (reader. readInnerXml (); // output Part 3 }}conn. close (); Console. readKey ();}

There is also the asynchronous version of the above methods. This example uses the following two methods:

5. inexecutenonquery () asynchronous version of ExecuteNonQuery ()

6. EndExecuteNonQuery () asynchronous version of ExecuteNonQuery ()

Class Program {static void Main (string [] args) {string str = "server = .; database = JunTest; uid = sa; pwd = 123; Asynchronous Processing = true "; SqlConnection conn = new SqlConnection (str); // create a connection SqlCommand cmd = conn. createCommand (); // CREATE Command cmd. commandText = "insert into Person VALUES (11, 'Guo jia')"; // you can specify the conn statement. open (); // Open the connection cmd. beginExecuteNonQuery (BeginCallback, cmd); // asynchronous execution statement Console. writeLine ("no matter whether you finish executing, I will continue to do my work! "); Console. ReadKey ();} public static void BeginCallback (IAsyncResult result) {Console. WriteLine (" the SQL command is being executed! "); SqlCommand cmd = result. asyncState as SqlCommand; // obtain the Console of the asynchronous input parameter. writeLine ("successfully executed command:" + cmd. commandText); Console. writeLine ("the number of lines affected by this execution is:" + cmd. endExecuteNonQuery (result); Console. writeLine ("close connection! "); Cmd. Connection. Close (); // Close the Connection officially }}

Part of this article is from msdn, and part of this article is from reverse blogs.