怎么在VS中调用存储过程来实现操作（增删改查）功能

[rainJune001]

求大神指导一下，最好有源码，本人刚工作没多久，近期做一个项目，在SQL Server中写好了存储过程，在VS要怎么调用？

[wanhao110]

http://www.cnblogs.com/Earloye/archive/2010/11/02/1867522.html 传送门

[newtee]

commandtype改成StoredProcedure就行了，其他差不多。只要你平时不是拼字符串的（这种有安全性问题）。

[rainJune001]

我是这样写的，但是报错 public object Select() { SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=BookShop;Integrated Security=True"); SqlParameter[] para = new SqlParameter[] { new SqlParameter("@Title",SqlDbType.NVarChar,200) }; para[0].Value = "Books"; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "book_tab"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(para); con.Open(); object result = cmd.ExecuteReader(); return result; } } 但是报错，错误代码System.Data.SqlClient.SqlDataReader

[rainJune001]

按照上面给的方法写了一遍还是不行啊...能不能给一个model啊，拜谢.....

[newtee]

http://www.51cto.com/art/200806/76139.htm http://www.cnblogs.com/herbert/archive/2010/07/12/1775889.html 你翻翻VS帮助文档也有例子啊！

[liu_lxx]

comd.CommandType = CommandType.StoredProcedure; comd.CommandText = "PRO_YHDL";//存储过程名.. comd.Parameters.AddWithValue("@YHYX", Txt_Yhyx.Value);参数.

[hu_hujun]

SQL语句 --创建数据库 create database DemoInfo go --创建表 go create table UserInfo( UserId int not null primary key identity(1,1), UserName nvarchar(50) not null, UserPwd nvarchar(50) not null ) --添加 go create procedure proc_UserInfoInsert @UserId int output, @UserName nvarchar(50), @UserPwd nvarchar(50) as insert into UserInfo values(@UserName,@UserPwd) set @UserId=SCOPE_IDENTITY() go --删除 create procedure proc_UserInfoDelete @UserId int as delete UserInfo where UserId=@UserId go --修改 create procedure proc_UserInfoUpdate @UserId int, @UserName nvarchar(50), @UserPwd nvarchar(50) as update UserInfo set UserName=@UserName,UserPwd=@UserPwd where UserId=@UserId go --查询 create procedure proc_UserInfoSelect @Page int as select top 5 * from UserInfo where UserId not in(select top(5*@Page-5) UserId from UserInfo) go --求出数据库的总数 create procedure proc_UserINfoCount as select COUNT(*) from UserInfo go --判断输入的用户是否一样 create procedure proc_RepeatUserName @UserName nvarchar(50) as select COUNT(*) from UserInfo where UserName=@UserName go --根据id查询信息 create procedure proc_UserInfoSelectId @UserId int as select * from UserInfo where UserId=@UserId go select * from UserInfo

[hu_hujun]

DAL层 SqlHelper.cs类 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Reflection; using System.Data; namespace DAL { public class SqlHelper { // private const string CONNECTION_SQL = @"Data Source=.\sql2008;Initial Catalog=JDDB;User ID=sa"; private const string CONNECTION_SQL = @"Data Source=MICROSO-OIQ0VNG;Initial Catalog=DemoInfo;Integrated Security=True"; public static List<T> ExecuteList<T>(string sql) { List<T> lists = new List<T>(); using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(sql, con)) { using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { T obj = ExecuteReader<T>(dr); if (obj != null) { lists.Add(obj); } } } } } return lists; } private static T ExecuteReader<T>(SqlDataReader dr) { T obj = default(T); Type type = typeof(T); obj = Activator.CreateInstance<T>(); PropertyInfo[] propertyInfos = type.GetProperties(); int columnCount = dr.FieldCount; foreach (PropertyInfo propertyInfo in propertyInfos) { for (int i = 0; i < columnCount; i++) { string columnName = dr.GetName(i); string propertyName = propertyInfo.Name; if (string.Compare(columnName, propertyName, true) == 0) { object value = dr[i]; if (value != null && value != DBNull.Value) { propertyInfo.SetValue(obj, value, null); break; } } } } return obj; } /// <summary> /// 根据实体类查询数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="commandText"></param> /// <param name="commandType"></param> /// <param name="values"></param> /// <returns></returns> public static T ExecuteEntity<T>(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { T obj = default(T); using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { obj = ExecuteReader<T>(dr); break; } } } } return obj; } /// <summary> /// 执行不查询的操作 /// </summary> /// <param name="commandText"></param> /// <param name="commandType"></param> /// <param name="values"></param> /// <returns></returns> public static int ExecuteNonQuery(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { int count = 0; using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); count = cmd.ExecuteNonQuery(); } } return count; } /// <summary> /// 执行返回一行一列的操作（聚合函数的操作） /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExecuteScalar(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { int count = 0; using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); count = (int)cmd.ExecuteScalar(); } } return count; } /// <summary> /// 查询所有的数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="commandText"></param> /// <param name="commandType"></param> /// <param name="values"></param> /// <returns></returns> public static List<T> ExecuteList<T>(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { List<T> list = new List<T>(); using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { T obj = ExecuteReader<T>(dr); list.Add(obj); } } } } return list; } } }

[hu_hujun]

UserInfoDAL.cs类 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace DAL { public static class UserInfoDAL { /// <summary> /// 添加信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoInsert(Entity.UserInfo user) { int count = 0; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Direction = System.Data.ParameterDirection.Output; user.UserId = parMentUserId.Value == DBNull.Value ? 0 : Convert.ToInt32(parMentUserId.Value); SqlParameter parMentUserName = new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 50); parMentUserName.Value = user.UserName; SqlParameter parMentUserPwd = new SqlParameter("@UserPwd", System.Data.SqlDbType.NVarChar, 50); parMentUserPwd.Value = user.UserPwd; count = SqlHelper.ExecuteNonQuery("proc_UserInfoInsert", System.Data.CommandType.StoredProcedure, parMentUserId, parMentUserName, parMentUserPwd); return count; } /// <summary> /// 删除信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static int UserInfoDelete(int id) { int count = 0; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Value = id; count = SqlHelper.ExecuteNonQuery("proc_UserInfoDelete", System.Data.CommandType.StoredProcedure, parMentUserId); return count; } /// <summary> /// 修改信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoUpdate(Entity.UserInfo user) { int count = 0; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Value = user.UserId; SqlParameter parMentUserName = new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 50); parMentUserName.Value = user.UserName; SqlParameter parMentUserPwd = new SqlParameter("@UserPwd", System.Data.SqlDbType.NVarChar, 50); parMentUserPwd.Value = user.UserPwd; count = SqlHelper.ExecuteNonQuery("proc_UserInfoUpdate", System.Data.CommandType.StoredProcedure, parMentUserId,parMentUserName,parMentUserPwd); return count; } /// <summary> /// 查询信息 /// </summary> /// <param name="page"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelect(int page) { List<Entity.UserInfo> list = null; SqlParameter parMentPage = new SqlParameter("@Page", System.Data.SqlDbType.Int); parMentPage.Value = page; list = SqlHelper.ExecuteList<Entity.UserInfo>("proc_UserInfoSelect", System.Data.CommandType.StoredProcedure, parMentPage); return list; } /// <summary> /// 求出总数 /// </summary> /// <returns></returns> public static int UserInfoCount() { int count = 0; count = SqlHelper.ExecuteScalar("proc_UserINfoCount", System.Data.CommandType.StoredProcedure); return count; } /// <summary> /// 判断用户是否相同 /// </summary> /// <param name="userName"></param> /// <returns></returns> public static int UserRepeatUserName(string userName) { int count = 0; SqlParameter parMentRepeatUserName = new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 50); parMentRepeatUserName.Value = userName; count = SqlHelper.ExecuteScalar("proc_RepeatUserName", System.Data.CommandType.StoredProcedure, parMentRepeatUserName); return count; } /// <summary> /// 根据id查询信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelectId(int id) { List<Entity.UserInfo> list = null; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Value = id; list = SqlHelper.ExecuteList<Entity.UserInfo>("proc_UserInfoSelectId", System.Data.CommandType.StoredProcedure, parMentUserId); return list; } } } BLL层 UserInfoBLL.cs类 using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BLL { public static class UserInfoBLL { /// <summary> /// 添加信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoInsert(Entity.UserInfo user) { return DAL.UserInfoDAL.UserInfoInsert(user); } /// 删除信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static int UserInfoDelete(int id) { return DAL.UserInfoDAL.UserInfoDelete(id); } /// <summary> /// 修改信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoUpdate(Entity.UserInfo user) { return DAL.UserInfoDAL.UserInfoUpdate(user); } /// <summary> /// 查询信息 /// </summary> /// <param name="page"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelect(int page) { return DAL.UserInfoDAL.UserInfoSelect(page); } /// <summary> /// 求出总数 /// </summary> /// <returns></returns> public static int UserInfoCount() { return DAL.UserInfoDAL.UserInfoCount(); } /// <summary> /// 判断用户是否相同 /// </summary> /// <param name="userName"></param> /// <returns></returns> public static int UserRepeatUserName(string userName) { return DAL.UserInfoDAL.UserRepeatUserName(userName); } /// <summary> /// 根据id查询信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelectId(int id) { return DAL.UserInfoDAL.UserInfoSelectId(id); } } }

[insus]

Refer: 下载源程序来看， http://www.cnblogs.com/insus/archive/2013/04/21/3029483.html http://www.cnblogs.com/insus/archive/2013/04/21/3033467.html