ADO.NET调用存储过程
建表
CREATE TABLE [tab_cJ] ( [id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY, [name] [varchar] (50) , [age] [int] NULL , [info] [varchar] (200) )
创建储存过程
CREATE PROCEDURE QueryInfoByName @name varchar(50), @age int AS select info from tab_cj where [name]=@name and age=@age GO
调用
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True")) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作 cmd.CommandText = "QueryInfoByName"; //存储过程名称 //对应存储过程QueryInfoByName的第一个参数@name SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50); //指定参数@name要转入的值 parName.Value = "aa"; //对应存储过程QueryInfoByName的第二个参数@age SqlParameter parAge = new SqlParameter("@age", SqlDbType.Int); //指定参数@age要转入的值 parAge.Value = 12; //这一步非常重要,一定将设置好的两个参数类型添加到Command对象的参数集合里 cmd.Parameters.Add(parName); cmd.Parameters.Add(parAge); //方式一,查询回来的结果需要显示在DataGrid之类的控件上 DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); //方式二,按单个值读取 conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Response.Write(reader.GetString(0)); } } }
调用具有输出参数的存储过程
创建储存过程
create procedure getAge ( @name varchar(50), @age int output ) as select @age=age from tab_cJ where [name]=@name
调用储存过程
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True")) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作 cmd.CommandText = "getAge"; //存储过程名称 SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50); parName.Value = "aa"; //对应存储过程getAge的输出参数@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()); } }
摘取至——————
春华秋实
如侵自删
posted on 2017-08-21 11:29 YellowCool 阅读(565) 评论(4) 编辑 收藏 举报