C # Using SQL stored procedure complete process

A stored procedure is a SQL statement that is cured inside a SQL database system, and the benefit is that it can improve execution efficiency, improve database security, and reduce network traffic. The next step is to set up a stored procedure in the database.

Open the SQL2055 database, expand the Databases node, locate the database you are using (the destination database), expand the Database node, find the "programmability" node and expand, you can see the "stored procedure", right-click on the "Stored procedure" and create a new stored procedure. Then the query parser pops up, where you can enter the creation code.

    Create proc Myinsert-- creates a stored procedure named Myinsert      --write the arguments here, if any;             here to write a specific statement, you can write n      go--can be added without adding, go means another page, equivalent to the next function block. If the bottom does not write the statement, can not add!  

  10 ),-- Note the comma here, multiple arguments separated by commas @password varchar ( 10   10   10  ), @cr Eatepeople varchar ( 10  )  as   insert INTO systemusers (username,password,name,usertype,creatpeople) Valu Es (@username, @password, @name, @usertype, @createpeople) go  

Use     jf_charging_system      go      create proc myinsert          @username varchar,          @ Password varchar(ten),          @name varchar(),          @usertype varchar (   ),          @createpeoplevarchar (                 INSERT INTO systemusers (username,password,name,usertype,creatpeople) VALUES (@username, @password, @name, @ usertype, @createpeople)      go  

Here's how to call a stored procedure in C #. Nonsense, but say, a complete code + comments let you know everything! This C # code is completely corresponding to the stored procedure above.

            stringstrSQL ="Data source=localhost;initial catalog=######;integrated security=true";//Database Link String            stringsql ="Selectusername";//the name of the stored procedure to invokeSqlConnection Constr =NewSqlConnection (strSQL);//SQL database Connection object, with database link string as parameterSqlCommand Comstr =NewSqlCommand (SQL, CONSTR);//The SQL statement executes the object, the first argument is the statement to execute, and the second is the database connection objectComstr.commandtype = CommandType.StoredProcedure;//because you want to use a stored procedure, set the execution type to the stored procedure//set parameters for the stored procedure in turnCOMSTR.PARAMETERS.ADD ("@Param1", Sqldbtype.text). Value ="####"; Constr.open ();//Open a database connection//MessageBox.Show (Comstr.executenonquery (). ToString ());//Executing stored proceduresSqlDataAdapter SqlDataAdapter1 =NewSqlDataAdapter (COMSTR); DataTable DT=NewDataTable ();            SqlDataAdapter1.Fill (DT); Datagridview1.datasource=DT; Constr.close ();//Close Connection

C # Using SQL stored procedure complete process