C#参数化SQL查询

浏览数：19 / 时间：2015年06月12日

//写一个存储过程

ALTER PROCEDURE dbo.Infosearch
    
    (
    @bmid smallint = null,
    @xm varchar(10)=null,
    @xb varchar(10)=null,
    @strage smallint=null,
    @endage smallint=null,
    @zzmm varchar(10)=null,
    @xl varchar(10)=null,
    @zw varchar(10)=null
    )
    
AS
    /* SET NOCOUNT ON */ 
    declare @sql varchar(100)
    if @bmid is not null
    begin 
        set @sql=‘ where 部门ID=‘+Convert(varchar(10),@bmid)
    end
    
    if @xm is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 姓名like‘+@xm
        else set @sql=‘ where 姓名like‘+@xm
    end
    
    if @xb is not null
    begin 
        if    @sql is not null
            set @sql=@sql+‘ and 性别=‘+@xb
        else set @sql=‘ where 性别=‘+@xb
    end
    
    if @strage is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 年龄between ‘+Convert(varchar(10),@strage)
        else set @sql=‘ where 年龄between ‘+Convert(varchar(10),@strage)
    end
    
    if @endage is not null
    begin 
        set @sql=@sql+‘ and ‘+Convert(varchar(10),@endage)
    end
    
    if @zzmm is not null
    begin
        if @sql is not null
             set @sql=@sql+‘ and 政治面貌=‘+@zzmm
        else set @sql=‘ where 政治面貌=‘+@zzmm
    end
    
    if @xl is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 学历=‘+@xl
        else set @sql=‘ where 学历=‘+@xl
    end
    
    if @zw is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 职位=‘+@zw
        else set @sql=‘ where 职位=‘+@zw
    end
    
    exec(‘select 职工号,姓名,性别,年龄,学历,婚姻状况,政治面貌from yuangong‘+@sql)
    RETURN
 

ALTER PROCEDURE dbo.Infosearch
    
    (
    @bmid smallint = null,
    @xm varchar(10)=null,
    @xb varchar(10)=null,
    @strage smallint=null,
    @endage smallint=null,
    @zzmm varchar(10)=null,
    @xl varchar(10)=null,
    @zw varchar(10)=null
    )
    
AS
    /* SET NOCOUNT ON */ 
    declare @sql varchar(100)
    if @bmid is not null
    begin 
        set @sql=‘ where 部门ID=‘+Convert(varchar(10),@bmid)
    end
    
    if @xm is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 姓名like‘+@xm
        else set @sql=‘ where 姓名like‘+@xm
    end
    
    if @xb is not null
    begin 
        if    @sql is not null
            set @sql=@sql+‘ and 性别=‘+@xb
        else set @sql=‘ where 性别=‘+@xb
    end
    
    if @strage is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 年龄between ‘+Convert(varchar(10),@strage)
        else set @sql=‘ where 年龄between ‘+Convert(varchar(10),@strage)
    end
    
    if @endage is not null
    begin 
        set @sql=@sql+‘ and ‘+Convert(varchar(10),@endage)
    end
    
    if @zzmm is not null
    begin
        if @sql is not null
             set @sql=@sql+‘ and 政治面貌=‘+@zzmm
        else set @sql=‘ where 政治面貌=‘+@zzmm
    end
    
    if @xl is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 学历=‘+@xl
        else set @sql=‘ where 学历=‘+@xl
    end
    
    if @zw is not null
    begin
        if @sql is not null
            set @sql=@sql+‘ and 职位=‘+@zw
        else set @sql=‘ where 职位=‘+@zw
    end
    
    exec(‘select 职工号,姓名,性别,年龄,学历,婚姻状况,政治面貌from yuangong‘+@sql)
    RETURN
 //判断参数是否为空来决定怎样拼接查询语句

如果是多条件查询的话
存储过程里面就一个参数就够了

这个参数是不定条件查询语句
多条件之中判断那个是否为空如果为空填充1=1 不为空就为条件

 public static IDataReader ExecuteReader(DbCommand comm, string sql, params object[] value)
        {
            comm.CommandText = sql;
            if (value != null && value.Length >= 0)
            {
                if (comm.CommandText.IndexOf("?") == -1)
                {
                    string[] temp = sql.Split(‘@‘);
                    for (int i = 0; i < value.Length; i++)
                    {
                        string pName;
                        if (temp[i + 1].IndexOf(" ") > -1)
                        {
                            pName = "@" + temp[i + 1].Substring(0, temp[i + 1].IndexOf(" "));
                        }
                        else
                        {
                            pName = "@" + temp[i + 1];
                        }
                        //pName = "@p" + (i + 1).ToString();
                        DbParameter p = comm.CreateParameter();
                        p.DbType = DbType.String;
                        p.ParameterName = pName;
                        p.Value = value[i];
                        comm.Parameters.Add(p);
                    }
                }
                else
                {
                    string[] temp = sql.Split(‘?‘);
                    for (int i = 0; i < value.Length; i++)
                    {
                        temp[i] = temp[i] + "@p" + (i + 1).ToString();
                        string pName = "@p" + (i + 1).ToString();
                        DbParameter p = comm.CreateParameter();
                        p.DbType = DbType.String;
                        p.ParameterName = pName;
                        p.Value = value[i];
                        comm.Parameters.Add(p);
                    }
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < temp.Length; i++)
                    {
                        sb.Append(temp[i]);
                    }
                    comm.CommandText = sb.ToString();
                }
            }
            if (comm.Connection.State != ConnectionState.Open)
            {
                comm.Connection.Open();
            }
            return comm.ExecuteReader(CommandBehavior.CloseConnection);
        }


调用的时候类似：ExecuteReaderParams(comm, "select * from xx where id=? and name=?",id,name);