插入 修改 删除 查询 存储过程

 sql 创建 存储过程

 

 

---查询

create PROCEDURE [dbo].[SP_SelectByWhere]
(
  @strTableName nvarchar(200),
  @strWhere nvarchar(200)='',
  @strSelect nvarchar(300)= '*',
  @strOrder nvarchar(50)=''
)
AS
begin
   declare @sql varchar(4000)
   begin
      set @sql= 'select '+ @strSelect+ ' from '+ @strTableName + ' ' +@strWhere + ' ' +@strOrder
      execute(@sql)
   end
end

---插入

create PROCEDURE [dbo].[SP_insertinto]
(
  @strTableName nvarchar(200),
  @strinsert nvarchar(200)
)
as
begin
   declare @sql varchar(4000)
   begin
      set @sql= 'insert into '+ @strTableName+ ' values '+ @strinsert
      execute(@sql)
   end
end

 

--删除

create PROCEDURE [dbo].[SP_deleteByWhere]
(
  @strTableName nvarchar(200),
  @strWhere nvarchar(200)=''
)
AS
begin
   declare @sql varchar(4000)
   begin
      set @sql= 'delete from '+ @strTableName + ' ' +@strWhere
      execute(@sql)
   end
end

---修改

create PROCEDURE [dbo].[SP_updateByWhere]
(
  @strTableName nvarchar(200),
  @strWhere nvarchar(200)='',
  @strupdate nvarchar(300)
)
AS
begin
   declare @sql varchar(4000)
   begin
      set @sql= 'update '+ @strTableName+ ' set '+ @strupdate + ' ' +@strWhere
      execute(@sql)
   end
end

 

 

 

 

 

 

 

 

 

 

 

  //查询
    public DataTable List()
    {
        string strSelect = "*";
        string strWhere = "";
        string strTableName = "users";
        string strOrder = "order by sid desc";
        SqlParameter[] param = new SqlParameter[]
        { 
                new SqlParameter("@StrSelect",strSelect),
                new SqlParameter("@strWhere",strWhere),
                new SqlParameter("@strTableName", strTableName),
                new SqlParameter("@strOrder", strOrder)
        };
        SqlCommand cmd = new SqlCommand("SP_SelectByWhere", MyConn);
        cmd.CommandType = CommandType.StoredProcedure;
        if (param != null)
        {
            foreach (SqlParameter Parameter in param)
                cmd.Parameters.Add(Parameter);
        }
        return ExeDt(cmd);
    }
    //插入
    public int Add() {

        string strTableName = "users(username,userpwd)";
        string strinsert = "('aaa','bbb')";
        SqlParameter[] param = new SqlParameter[]
        { 
                new SqlParameter("@strinsert",strinsert),    
                new SqlParameter("@strTableName", strTableName),
        };
        SqlCommand cmd = new SqlCommand("SP_insertinto", MyConn);
        cmd.CommandType = CommandType.StoredProcedure;
        if (param != null)
        {
            foreach (SqlParameter Parameter in param)
                cmd.Parameters.Add(Parameter);
        }
        return ExeNo(cmd);
    }
    //删除
    public int Del()
    {

        string strTableName = "users";
        string strWhere = "where sid=6";
        SqlParameter[] param = new SqlParameter[]
        { 
                new SqlParameter("@strWhere",strWhere),    
                new SqlParameter("@strTableName", strTableName),
        };
        SqlCommand cmd = new SqlCommand("SP_deleteByWhere", MyConn);
        cmd.CommandType = CommandType.StoredProcedure;
        if (param != null)
        {
            foreach (SqlParameter Parameter in param)
                cmd.Parameters.Add(Parameter);
        }
        return ExeNo(cmd);
    }
    //修改
    public int Up() {
        string strTableName = "users";
        string strWhere = "where sid=3";
        string strupdate = "username=4,userpwd=4";
        SqlParameter[] param = new SqlParameter[]
        { 
                new SqlParameter("@strWhere",strWhere),    
                new SqlParameter("@strTableName", strTableName),
                new SqlParameter("@strupdate", strupdate),
        };
        SqlCommand cmd = new SqlCommand("SP_updateByWhere", MyConn);
        cmd.CommandType = CommandType.StoredProcedure;
        if (param != null)
        {
            foreach (SqlParameter Parameter in param)
                cmd.Parameters.Add(Parameter);
        }
        return ExeNo(cmd);
    }