ADO. NET calls the stored procedure and ado.net Stored Procedure

ADO. NET calls the stored procedure and ado.net Stored Procedure

Create a table

CREATE TABLE [tab_cJ] ([id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[name] [varchar] (50) ,[age] [int] NULL ,[info] [varchar] (200))

Create storage process

CREATE PROCEDURE QueryInfoByName@name varchar(50),@age intASselect info from tab_cj where [name]=@name and age=@ageGO

Call

Using (SqlConnection conn = new SqlConnection ("Data Source = .; initial Catalog = test; Integrated Security = True ") {SqlCommand cmd = conn. createCommand (); cmd. commandType = CommandType. storedProcedure; // specifies the execution of Stored Procedure operations cmd. commandText = "QueryInfoByName"; // stored procedure name // The first parameter of the corresponding Stored Procedure QueryInfoByName @ name SqlParameter parName = new SqlParameter ("@ name", SqlDbType. varChar, 50); // specify the value of @ name to be transferred to parName. value = "aa"; // The second parameter corresponding to the stored procedure QueryInfoByName @ age SqlParameter parAge = new SqlParameter ("@ age", SqlDbType. int); // specify the value of @ age to be transferred to parAge. value = 12; // this step is very important. You must add the two parameter types you have set to the cmd parameter set of the Command object. parameters. add (parName); cmd. parameters. add (parAge); // method 1. the query result must be displayed on a control such as DataGrid. DataSet ds = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter (cmd ); adapter. fill (ds); // method 2. Read the conn by a single value. open (); SqlDataReader reader = cmd. executeReader (); if (reader. hasRows) {while (reader. read () {Response. write (reader. getString (0 ));}}}

Call a stored procedure with output parameters

Create storage process

create procedure getAge(@name varchar(50),@age int output)asselect @age=age from tab_cJ where [name]=@name

Call the Stored Procedure

Using (SqlConnection conn = new SqlConnection ("Data Source = .; initial Catalog = test; Integrated Security = True ") {SqlCommand cmd = conn. createCommand (); cmd. commandType = CommandType. storedProcedure; // specifies the execution of Stored Procedure operations cmd. commandText = "getAge"; // name of the stored procedure SqlParameter parName = new SqlParameter ("@ name", SqlDbType. varChar, 50); parName. value = "aa"; // The output parameter @ age SqlParameter parAge = new SqlParameter ("@ age", SqlDbType. int); parAge. direction = ParameterDirection. output; cmd. parameters. add (parName); cmd. parameters. add (parAge); try {conn. open (); cmd. executeNonQuery (); int I = int. parse (parAge. value. toString (); Response. write (I. toString (); conn. close ();} catch (Exception ex) {Response. write (ex. toString ());}}

Extract ------

Chunhua qiushi

Such as intrusion and Deletion