.net 利用三层 调用 sql存储过程 实现增删查改

注：本篇文章涉及的代码 是本人第一次 实现三层 调用存储过程，如有问题，请多指教。【仅供参考】首先 进行数据库操作1、建立一个数据库【User】，建立一张数据表【UserInfo】包含四个字段 UserID int, UserName varchar(50), UserSex varchar(10), UserDesc varchar(50) 其中UserID为自动增长列2、创建存储过程（1）、查找表中所有数据 create procedure dbo.uInfo_select as select * from userInfo（2）、根据ID查找表中数据 create procedure dbo.uInfo_select_uid uID int as select * from UserInfo where UserID = uID（3）、向表中插入数据 create procedure dbo.uInfo_inSert uName varchar(50), uSex varchar(10), uDesc varchar(100) as insert into userInfo(UserName,UserSex,UserDesc) values (uName,uSex,uDesc)（4）、更新表中数据 create procedure dbo.uInfo_updata uID int, uName varchar(50), uSex varchar(10), uDesc varchar(100) as update userInfo setUserName=uName,UserSex=uSex,UserDesc=uDescwhere UserID = uID（5）、删除表中某条记录 create procedure dbo.uInfo_delete uID int as delete userInfo where UserID = uID再次 建立三层1、在 web.config 中添加 数据连接字节 2.创建接口层 IDAL类库using System;using System.Collections.Generic;using System.Text;using System.Configuration;using System.Data;namespace Test.IDAL public interface IDAL_uInfo / / 查询表中所有的数据 / / DataSet uinfo_select(); / / 查询表中所有的数据 / / 根据ID / DataSet uinfo_select(int ID); / / 向表中插入数据 / / 用户名称 / 性别 / 信息 void uinfo_insert(string uName, string uSex, string uDesc); / / 更新表中数据 / / 用户ID / 名称 / 性别 / 信息 void uinfo_updata(int uID, string uName, string uSex, string uDesc); / / 删除表中数据 / / 用户ID void uinfo_delete(int uID); 3、创建数据访问层 DAL类库using System;using System.Collections.Generic;using System.Text;using Test.IDAL;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace Test.DAL public class DAL_uInfo : IDAL_uInfo /获取web.config中的链接字符串 static string connStr = ConfigurationSettings.AppSettingsconnStr; / / 查询表中所有的数据 / / public DataSet uinfo_select() DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connStr) try conn.Open(); SqlCommand comm = new SqlCommand(uInfo_select, conn); comm.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(comm); da.Fill(ds); return ds; catch (SqlException ex) throw new Exception(ex.Message); / / 查询表中所有的数据 / / 根据ID / public DataSet uinfo_select(int ID) using (SqlConnection conn = new SqlConnection(connStr) DataSet ds = new DataSet(); try conn.Open(); SqlCommand comm = new SqlCommand(uInfo_select_uid, conn); comm.CommandType = CommandType.StoredProcedure; SqlParameter puID = new SqlParameter(uID, SqlDbType.Int); puID.Value = ID; comm.Parameters.Add(puID); SqlDataAdapter da = new SqlDataAdapter(comm); da.Fill(ds); return ds; catch (SqlException ex) throw new Exception(ex.Message); / / 向表中插入数据 / / 用户名称 / 性别 / 信息 public void uinfo_insert(string uName, string uSex, string uDesc) using (SqlConnection conn = new SqlConnection(connStr) try conn.Open(); SqlCommand comm = new SqlCommand(uInfo_inSert, conn); comm.CommandType = CommandType.StoredProcedure; SqlParameter puName = new SqlParameter(uName, SqlDbType.VarChar, 50); SqlParameter puSex = new SqlParameter(uSex, SqlDbType.VarChar, 10); SqlParameter puDesc = new SqlParameter(uDesc, SqlDbType.VarChar, 50); puName.Value = uName; puSex.Value = uSex; puDesc.Value = uDesc; comm.Parameters.Add(puName); comm.Parameters.Add(puSex); comm.Parameters.Add(puDesc); comm.ExecuteNonQuery(); catch (SqlException ex) throw new Exception(ex.Message); / / 更新表中数据 / / 用户ID / 名称 / 性别 / 信息 public void uinfo_updata(int uID, string uName, string uSex, string uDesc) using (SqlConnection conn = new SqlConnection(connStr) try conn.Open(); SqlCommand comm = new SqlCommand(uInfo_updata, conn); comm.CommandType = CommandType.StoredProcedure; SqlParameter puName = new SqlParameter(uName, SqlDbType.VarChar, 50); SqlParameter puSex = new SqlParameter(uSex, SqlDbType.VarChar, 10); SqlParameter puDesc = new SqlParameter(uDesc, SqlDbType.VarChar, 50); SqlParameter puID = new SqlParameter(uID, SqlDbType.Int); puName.Value = uName; puSex.Value = uSex; puDesc.Value = uDesc; puID.Value = uID; comm.Parameters.Add(puName); comm.Parameters.Add(puID); comm.Parameters.Add(puDesc); comm.Parameters.Add(puSex); comm.ExecuteNonQuery(); catch (SqlException ex) throw new Exception(ex.Message); / / 删除表中数据 / / 用户ID public void uinfo_delete(int uID) using (SqlConnection conn = new SqlConnection(connStr) try conn.Open(); SqlCommand comm = new SqlCommand(uInfo_delete, conn); comm.CommandType = CommandType.StoredProcedure; SqlParameter puID = new SqlParameter(uID, SqlDbType.Int); puID.Value = uID; comm.Parameters.Add(puID); comm.ExecuteNonQuery(); catch (SqlException ex) throw new Exception(ex.Message); 4、创建业务逻辑层 BLL类库using System;using System.Collections.Generic;using System.Text;using Test;using System.Data;namespace Test.BLL public class BLL_uInfo Test.IDAL.IDAL_uInfo dal = new Test.DAL.DAL_uInfo(); / / 查询表中所有的数据 / / public DataSet uinfo_select() return dal.uinfo_select(); / / 查询表中所有的数据 / / 根据ID / public DataSet uinfo_select_id(int ID) return dal.uinfo_select(ID); / / 向表中插入数据 / / 用户名称 / 性别 / 信息 public void uinfo_insert(string uName, string uSex, string uDesc) dal.ui