//写一个存储过程
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);
转载于:https://www.cnblogs.com/zhanghongqiang/p/4081703.html
相关资源：C# 查询参数化例子
