. NET using stored procedures to implement additions and deletions to the database

First, the overall thinking

The stored procedure is established first, and then the stored procedure is called by the. NET to make additions and deletions to the table.

II. new database and stored procedures

Open SqlServer2008, New database Orm1, and table student.

Database and tables Well, let's build a stored procedure.

Stored procedure ad: New Student information

CREATE PROCEDURE ad     Char (ten),     Char (ten),     int as  insertinto0

The first sentence of the ad represents the name of the stored procedure, as before the @sid, @sname, @sage as input or output parameters, the default is the input parameters, if you need to set the output parameters, you need to add the outputs after the parameter, that is:

    Char (ten) OUTPUT,

There is a closer look you will find that the last parameter is the end is no need to add a comma!

So now we're going to execute this stored procedure, is that correct.

In SqlServer2008, selecting the stored procedure, right-executing the stored procedure, will pop up the input window, enter the corresponding student information, click OK to run.

After execution, look at the database and discover that the new information has been inserted into the database, representing the success of the new stored procedure!

Three. NET calling stored procedures

Create a new WebForm1.aspx file first

<%@ Page language="C #"autoeventwireup="true"Codebehind="WebForm1.aspx.cs"inherits="Webapplication3.webform1"%><! DOCTYPE html>"http://www.w3.org/1999/xhtml">"Server"><meta http-equiv="Content-type"Content="text/html; Charset=utf-8"/> <title></title>"Form1"runat="Server"> <div>School Number:<asp:textbox id="Sid"runat="Server"></asp:TextBox> </div> <div>Name:<asp:textbox id="sname"runat="Server"></asp:TextBox> </div> <div>Age:<asp:textbox id="Sage"runat="Server"></asp:TextBox> </div> <div> <asp:button id="ADD"runat="Server"text="New"onclick="Add_click"/> <asp:button id="Select"runat="Server"text="Enquiry"onclick="Select_click"/> <asp:button id="Delete"runat="Server"text="Delete"onclick="Delete_click"/> <asp:button id="Update"runat="Server"text="Modify"onclick="Update_click"/> <asp:button id="ShowAll"runat="Server"text="Show All"onclick="Showall_click"/> </div> <div> <asp:gridview id="GridView1"runat="Server"></asp:GridView> </div> </form></body>New Student Information
Then, open the corresponding CS file and write the new method Add_click
        protected voidAdd_click (Objectsender, EventArgs e) {String Constr="Data source=.; database=orm1;integrated security=true"; SqlConnection Con=NewSqlConnection (CONSTR);//New ConnectionCon. Open ();//Open ConnectionSqlCommand cmd = con. CreateCommand ();//Create a SqlCommand objectCmd.commandtext ="AD";//call the stored procedure adCmd.commandtype = CommandType.StoredProcedure;//set the execution type of cmd as a stored procedureCmd. Parameters.addwithvalue ("@sid"Sid. Text);//setting parameters and assigning valuesCmd. Parameters.addwithvalue ("@sname", Sname. Text);//setting parameters and assigning valuesCmd. Parameters.addwithvalue ("@sage",int. Parse (Sage. Text));//setting parameters and assigning values            if(CMD. ExecuteNonQuery () >0)//Executing stored procedures{Response.Write ("Add success! "); }            Else{Response.Write ("Add failed"); } con.                                                Close (); //Close Connection}
Run this code to execute the stored procedure.
Query Student Information
The new feature is complete, now let's look at how to implement the function of query learning information.
The same is the first build of the stored procedure SS.
CREATE PROCEDURE SS     Char (ten) asbegin     * from Student WHERE @sid = student.sidend
Calling the stored procedure SS
        protected voidSelect_click (Objectsender, EventArgs e) {String Constr="Data Source=.;i Nitial catalog=orm1;integrated security=true"; SqlConnection Con=NewSqlConnection (CONSTR); Con.                                                 Open (); //Open ConnectionSqlCommand cmd =NewSqlCommand ("SS", con);//Using Stored ProceduresCmd.commandtype = CommandType.StoredProcedure;//set the type of the Command objectSqlParameter SPR;//represents executing a stored procedureSPR = cmd. Parameters.Add ("@sid", SqlDbType.NChar,Ten);//Add parameter IDCmd. parameters["@sid"]. Value = SID. Text;//Initialization for parametersGridview1.datasource = cmd. ExecuteReader ();//executes the stored procedure and binds the data to the GridViewGridview1.databind (); Con.                                                Close (); //To close a stored procedure}
Let's run the program this time and see how it works.
We enter the newly created SID 200 in front of us, click Query
Here, I believe that you should be able to understand how to use. NET to call stored procedures, so, modify, delete the function I directly paste the code.
Modify Student Information
CREATE PROCEDURE ud    @sid nchar,    @sname nchar (ten),     int as     Set where student.sid=0
        protected voidUpdate_click (Objectsender, EventArgs e) {String Constr="data source=.; database=orm1;integrated security=true"; SqlConnection Con=NewSqlConnection (CONSTR); Con.            Open (); SqlCommand cmd=con.            CreateCommand (); Cmd.commandtext="UD"; Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.addwithvalue ("@sid", sid.            Text); Cmd. Parameters.addwithvalue ("@sname", sname.            Text); Cmd. Parameters.addwithvalue ("@sage",int. Parse (Sage.            Text)); if(CMD. ExecuteNonQuery () >0) {Response.Write ("Modification succeeded"); }            Else{Response.Write ("Modification Failed"); } con.                    Close (); }
Delete Student Information
CREATE PROCEDURE de     Char (ten    ) as = Student.sid
        protected voidDelete_click (Objectsender, EventArgs e) {String Constr="Data source=.; database=orm1;integrated security=true"; SqlConnection SqlConnection=NewSqlConnection (CONSTR);            Sqlconnection.open (); SqlCommand cmd=Sqlconnection.createcommand (); Cmd.commandtext="de"; Cmd.commandtype=CommandType.StoredProcedure;            SqlParameter SPR; SPR= cmd. Parameters.Add ("@sid", SqlDbType.NChar,Ten); Cmd. parameters["@sid"]. Value =SID.            Text; if(CMD. ExecuteNonQuery () >0) {Response.Write ("Delete Success! "); }            Else{Response.Write ("Delete Failed");        } sqlconnection.close (); }
. NET using stored procedures to implement additions and deletions to the database