Talking about using SQL Server stored procedures in C #

As long as the basic SQL statements will be written, then learn the stored procedure is very cool!

Stored Procedures (Stored Procedure) are in a large database system, a set of SQL statements to complete a specific function , stored in the database, after the first compilation after the call does not need to compile again, The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

Basic syntax: CREATE proc mysoredname----Stored procedure name

@UserName varchar,----have parameters to write on, do not write

@UserPwd varchar (a)

As

Begin

-----Write Plain SQL statements (complex statistics or federated queries)----

End

Before my classmates went to a company, do OA system, Project manager additions and deletions to change, is to use stored procedures to achieve,

(Individuals think to do basic additions and deletions to use the general SQL statement implementation can be, stored procedures left to the complex SQL, it will be more reasonable it )

Now that we've met, we'll create it.

It's SQL2008, and the other versions are the same.

CREATE proc Insertuser----added

@UserName varchar (50),

@UserPwd varchar (50),

@RoleId varchar (50)

As

Begin

-----Core----is actually a normal SQL statement

Insert into T_user (Username,userpwd,roleid) VALUES (@UserName, @UserPwd, @RoleId)

End

Go----Create the next stored procedure consecutively, if you no longer create it.

CREATE proc deleteuser----Delete

@Id int

As

Begin

Delete T_user where [email protected]

End

Go

CREATE proc UpdateUser----Modified

@Id int,

@UserName varchar (50),

@UserPwd varchar (50),

@RoleId varchar (50)

As

Begin

Update T_user Set [email protected],[email protected],[email protected] where [email protected]

End

Go

CREATE proc selectuser----Query statement (no parameters)

As

Begin

SELECT * FROM T_user A

Left join T_role B on A.roleid=b.id

End

Using stored procedures in C #

Add call (same as Delete):

String connstr = "Data source=.;i Nitial catalog=mvcdb; User Id=sa; Password=123 ";

String storedname = "Insertuser";//Stored Procedure name

SqlConnection conn = new SqlConnection (CONNSTR);//Instantiate Connection object

Conn. Open ();

The command is to execute the inserted data, the Insertuser stored procedure that is written

SqlCommand cmd = new SqlCommand (STOREDNAME, conn);

Cmd.commandtype = commandtype.storedprocedure;//The type of the declaration command is a stored procedure

Set the data parameter to insert

Cmd. Parameters.Add ("@UserName", SqlDbType.VarChar, 50). Value = "007";

Cmd. Parameters.Add ("@UserPwd", SqlDbType.VarChar, 50). Value = "007";

Cmd. Parameters.Add ("@RoleId", SqlDbType.VarChar, 50). Value = "1";

MessageBox.Show (cmd. ExecuteNonQuery (). ToString ());//Execute stored Procedure

Conn. Close ();

Query invocation:

String connstr = "Data source=.;i Nitial catalog=mvcdb; User Id=sa; Password=123 ";

String storedname = "Selectuser";//Stored Procedure name

SqlConnection conn = new SqlConnection (CONNSTR);//Instantiate Connection object

Conn. Open ();

The command is the data to execute the query, the selectuser stored procedure that is written

SqlCommand cmd = new SqlCommand (STOREDNAME, conn);

Cmd.commandtype = commandtype.storedprocedure;//The type of the declaration command is a stored procedure

SqlDataAdapter Adapter=new SqlDataAdapter (CMD);

DataSet ds=new DataSet ();

Adapter. Fill (DS);//Executes the statement through the adapter and fills the result into the DS

Conn. Close ();

Datagridview1.datasource = ds. Tables[0];

stored procedures used to be very cool, but can not be abused, to pay attention to yin and yang harmony, moderate use, will bring you the project unprecedented efficiency effect.

General (complex statistics or joint queries) better use

Talking about using SQL Server stored procedures in C #