通用的增删改操作
- /// <summary>
- /// 增删改操作函数,返回值为受影响的行数
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <param name="para">参数</param>
- /// <returns></returns>
- public int RunSql(string procName, SqlParameter[] para)
- {
- int count = 0;
- try
- {
- //得到SqlCommand对象
- cmd = CreateCmd(procName, para);
- conn.Open();
- count = cmd.ExecuteNonQuery();
- }
- catch (SqlException ex)
- {
- throw ex;
- }
- finally
- {
- conn.Close();
- }
- return count;
- }
- /// <summary>
- /// 创建SqlCommand来执行存储过程(带参数)
- /// </summary>
- /// <param name="procName"></param>
- /// <param name="para"></param>
- /// <returns></returns>
- public SqlCommand CreateCmd(string procName, SqlParameter[] para)
- {
- try
- {
- conn = CreateConn(); //得到数据库连接
- cmd = new SqlCommand(); //设置Command对象
- cmd.CommandType = CommandType.StoredProcedure; //创建存储过程
- cmd.CommandText = procName; //调用存储过程名称
- cmd.Connection = conn; //创建数据库连接对象
- if (para != null)
- {
- //添加存储过程参数
- foreach (SqlParameter sp in para)
- {
- cmd.Parameters.Add(sp);
- }
- }
- }
- catch (SqlException ex)
- {
- throw ex;
- }
- return cmd;
- }
- /// <summary>
- /// 创建数据库连接
- /// </summary>
- public SqlConnection CreateConn()
- {
- SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlConn"]);
- return conn;
- }
- //举例说明,Adduser()增加用户
- private void Adduser()
- {
- string CompaneyName = Request.Form["CompaneyName"];
- string TelPhone = Request.Form["TelPhone"];
- string FaxID = Request.Form["FaxID"];
- string CustName = Request.Form["CustName"];
- string MobileNo = Request.Form["MobileNo"];
- string zip = Request.Form["zip"];
- string provine = Request.Form["provine"];
- string city = Request.Form["city"];
- string Address = Request.Form["Address"];
- string sex = Request.Form["sex"];
- string Email = Request.Form["Email"];
- string remark = Request.Form["remark"];
- int x=0;
- try
- {
- SqlParameter[] sp ={
- para = new SqlParameter("@CompaneyName",CompaneyName),
- para = new SqlParameter("@TelPhone",TelPhone),
- para = new SqlParameter("@FaxID",FaxID),
- para = new SqlParameter("@CustName",CustName),
- para = new SqlParameter("@MobileNo",MobileNo),
- para = new SqlParameter("@zip",zip),
- para = new SqlParameter("@provine",provine),
- para = new SqlParameter("@city",city),
- para = new SqlParameter("@Address",Address),
- para = new SqlParameter("@sex",sex),
- para = new SqlParameter("@Email",Email),
- para=new SqlParameter("@remark",remark)
- };
- x = sq.RunSql("E_adduser", sp);//E_adduser存储过程
- if (x > 0)
- {
- Response.Write("<script>alert('增加成功') </script>");
- }
- else
- {
- Response.Write("<script>alert('失败') </script>");
- }
- }
- catch (Exception ex)
- {
- Response.Write(ex.Message);
- }
- }
- //删除用户
- public void deleteuser()
- {
- int custID =Convert.ToInt32(Request["custID"].ToString());
- int count = 0;
- try
- {
- SqlParameter[] sp ={
- para = new SqlParameter("@custID",custID)
- };
- count = sh.RunSql("E_Deluser", sp);
- if (x > 0)
- {
- Response.Write("<script>alert('删除成功') </script>");
- }
- else
- {
- Response.Write("<script>alert('失败') </script>");
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- //存储过程
- //执行增加操作的存储过程
- create proc E_adduser
- (
- @CompaneyName varchar(40),
- @TelPhone varchar(30),
- @FaxID varchar(30),
- @CustName varchar(30),
- @MobileNo varchar(20),
- @zip varchar(15),
- @provine varchar(15),
- @city varchar(15),
- @Address varchar(100),
- @sex varchar(10),
- @Email varchar(50),
- @remark varchar(255)
- )
- as
- insert into Customers(CompanyName, PhoneNo,FaxNo,CustName,MobileNo,Zip,Province,City,Address,Gender,Email,Remark,LastCallTime) values(@CompaneyName,@TelPhone,@FaxID,@CustName,@MobileNo,@zip,@provine,@city,@Address,@sex,@Email,@remark,GetDate())
- GO
- //执行删除操作的存储过程
- create proc E_Deluser(@custID int)
- as
- delete from Customers where CustId = @custID
- GO