C # and database Access technology Summary (vi) Command object creation SQL statement code example

CommandObject creationSQlStatement code example

Description: The Command object's methods and some properties are described earlier, and the command object is primarily used to execute SQL statements. With the command object, you can query data and modify data.

In the following code, you first create a Sqlconnecdon connection object from the connection string and use this object to connect to the data source: Then create a SqlCommand object and use the object's The ExecuteNonQuery method executes an SQL statement with no return result set.

　　

1 // Connection String 2 3 Private Static string strconnect="  data source=localhost;  database=logindb; uid=sa;pwd=aspent; "

6 7 //Create a SqlConnection connection handle based on the connection string8 9Sqlconnetion objconnection =NewSqlConnection (strconnect);Ten  One //Database Commands A  -SqlCommand objcommand =NewSqlCommand (" ", objconnection); -  the //setting up SQL statements -  -objcommand.commandtext="INSERT into USERS"+"(USERNAME, nickname, UserPassword, UserEmail, Userrole, Creatdate, lastmodifydate)"+"VALUES"+"(@USERNAME, @NICKNAME, @USERPASSWORD, @USEREMAIL, @USERROLE, @CREATDATE, @LASTMODIFYDATE)"; -  + //The following omit statements that set each value -  + ... A  at Try -  - { -  - //Open a database connection -  in if(Objconnection.state = =ConnectionState. Closed) -  to { +  - Objconnection.open (); the  * } $ Panax Notoginseng //get run results, insert data -  the objcommand.executenonquery (); +  A //Omit subsequent actions the  + ... -  $ } $  - Catch(SqlException e) -  the { - Wuyi Response.Write (E.message.tostring ()); the  - } Wu  - finally About  $ { -  - //To close a database connection -  A if(Objconnection.state = =ConnectionState.Open) +  the { -  $ objconnection.close (); the  the } the  the}

　　This code is a typical code for connecting to a database and performing operations.

where the code that operates the database is Try ... catch ... finally structure, the code can not only operate the database properly, but also throw exceptions in the event of an exception.

In addition, regardless of whether an exception occurred or not, regardless of the database operation exception, finally code in the block will be executed,

Therefore, the code must be guaranteed to close the connection after accessing the database.

In the following code, the Command object is used to execute the SQL Statement of the query class and assign the result set to the Dataread object.

Private Static stringstrconnect="data source=localhost;UID=sa;pwd=aspent;database=logindb"sqlconnetion objconnection=NewSqlConnection (strconnect); SqlCommand objcommand=NewSqlCommand (" ", objconnection);//setting up SQL statementsObjcommand.commandtext="SELECT * from USERS";Try{//Open a database connectionif(Objconnection.state = =ConnectionState. Closed) Objconnection.open ();//Get Run Results SqlDataReader result=Objcommand.executereader ();//Omit subsequent actions...}Catch(SqlException e) {Response.Write (e.message.tostring ());}finally{//To close a database connectionif(Objconnection.state = =ConnectionState.Open) {Objconnection.close ();}}

here to use DataReader object to get the result set, if you just want to return the value of the first column of the first row of the query result set , you can SqlDataReader Result=objcommand.executereader (); Change into objcommand.executescalar (). ToString ();

C # and database Access technology Summary (vi) Command object creation SQL statement code example