Manipulating databases with Command objects

1.Command Object Query Database

 protected voidButton1_Click (Objectsender, EventArgs e) {        //read the Web. config node configuration        stringStrcon = configurationmanager.connectionstrings["TESTJM"].        ConnectionString; //instantiating a SqlConnection objectSqlConnection con =NewSqlConnection (Strcon); //Database Establishment Connection opencon.           Open (); stringstrSQL ="SELECT * from userinfo where [email protected]";//Query StatementsSqlCommand myCMD =NewSqlCommand (strSQL, con); myCMD. Parameters.Add ("@name", SqlDbType.VarChar, -). Value =TextBox1.Text.Trim (); SqlDataAdapter Myda=NewSqlDataAdapter (myCMD);//instantiate the SqlDataAdapter and pass the strSQL query statement through con to the databaseDataSet myds =NewDataSet ();//instantiate a dataset as myDSMyda. Fill (myDS,"UserInfo");//Populating data setsGridview1.datasource = myds;//The returned data set is displayed on the interface, specifying the data sourceGridview1.databind ();//Binding DatabaseMyda.            Dispose (); myDS.            Dispose (); Con. Close ();//Close Connection}

2.Command Object Add Data

/// <summary>    ///Package Query UserInfo table information/// </summary>    protected voidbind () {SqlConnection con=Getcon (); //Database Establishment Connection opencon.        Open (); stringstrSQL ="SELECT * from UserInfo";//Query StatementsSqlDataAdapter Myda =NewSqlDataAdapter (Strsql,con); DataSet myds=NewDataSet (); Myda.        Fill (myds); Gridview1.datasource= myDS;//The returned data set is displayed on the interface, specifying the data sourceGridview1.datakeynames =New string[] {"ID" }; Gridview1.databind ();//Binding DatabaseMyda.        Dispose (); myDS.        Dispose (); Con.    Close (); }    /// <summary>    ///Encapsulating database Connections/// </summary>    /// <returns></returns>    protectedSqlConnection Getcon () {//read the Web. config node configuration        stringStrcon = configurationmanager.connectionstrings["TESTJM"].        ConnectionString; //instantiating a SqlConnection objectSqlConnection Con1 =NewSqlConnection (Strcon); returnCon1; }

/// <summary>    ///Add Data/// </summary>    /// <param name= "Sender" ></param>    /// <param name= "E" ></param>    protected voidBtsumbit_click (Objectsender, EventArgs e) {SqlConnection con=Getcon (); //Database Establishment Connection opencon.        Open (); stringStrinsert ="INSERT INTO UserInfo (id,name,password,age) VALUES ("+ This. Tbid. Text.trim () +", '"+ This. Tbname. Text.trim () +"', '"+ This. tbpwd. Text.trim () +"',"+ This. Tbage. Text.trim () +")"; SqlCommand myCMD=NewSqlCommand (Strinsert, con); myCMD.        ExecuteNonQuery (); myCMD.        Dispose (); Con. Close ();//Close Connection         This. bind (); }    /// <summary>    ///to add a reset in the data/// </summary>    /// <param name= "Sender" ></param>    /// <param name= "E" ></param>    protected voidBtreset_click (Objectsender, EventArgs e) {tbid. Text=""; Tbname. Text=""; Tbpwd. Text=""; Tbage. Text=""; }

3.Command Object Modification Data

 /// <summary>    ///Click the Edit button to trigger the Rowediting event/// </summary>    /// <param name= "Sender" ></param>    /// <param name= "E" ></param>    protected voidGridview1_rowediting (Objectsender, GridViewEditEventArgs e) {Gridview1.editindex=E.neweditindex;  This. bind (); }    /// <summary>    ///Update data, rowupdating Pre-update events, rowupdated updated events/// </summary>    /// <param name= "Sender" ></param>    /// <param name= "E" ></param>    protected voidGridview1_rowupdating (Objectsender, Gridviewupdateeventargs e) {        intCID =Convert.ToInt32 (Gridview1.datakeys[e.rowindex].        Value.tostring ()); stringCName = ((TextBox) (Gridview1.rows[e.rowindex]. cells[2]. controls[0])).        Text.tostring (); stringCPWD = ((TextBox) (Gridview1.rows[e.rowindex]. cells[3]. controls[0])).        Text.tostring (); stringStrupdate ="Update userinfo set name= '"+ CName +"', password= '"+ CPWD +"' where id="+CID; SqlConnection Con=Getcon (); Con.        Open (); SqlCommand myCMD=NewSqlCommand (strupdate, con); myCMD.        ExecuteNonQuery (); myCMD.        Dispose (); Con. Close ();//Close Connection         This. bind (); }    /// <summary>    ///Click the Cancel button in the update to trigger the Rowcancelingedit event/// </summary>    /// <param name= "Sender" ></param>    /// <param name= "E" ></param>    protected voidGridview1_rowcancelingedit (Objectsender, Gridviewcancelediteventargs e) {Gridview1.editindex= -1;  This. bind (); }

4.Command Object Delete Data

Manipulating databases with Command objects