SqlParameter[] parameters = new SqlParameter[4];
parameters[0] = new SqlParameter("@FileNam", model.FileNam);
parameters[1] = new SqlParameter("@Decription", model.Decription);
parameters[2] = new SqlParameter("@stardate", createdate);
parameters[3] = new SqlParameter("@enddate", endate);
return DbHelperSQL.RunProcedure("P_UserSerch", parameters, "t_File");
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
string strSql = "select * from t_File where 1 =1";
string strWhere = "";
if (!string.IsNullOrEmpty(model.FileNam))
{
strWhere += " and FileNam like @FileNam";
}
if (!string.IsNullOrEmpty(model.Decription))
{
strWhere += " and Decription like @Decription";
}
if (!string.IsNullOrEmpty(createdate) || !string.IsNullOrEmpty(endate))
{
strWhere += " and CreateOn between @createdate and @endate";
}
strSql += strWhere;
SqlParameter[] parameters = {
new SqlParameter("@FileNam",SqlDbType.NVarChar,256),
new SqlParameter("@Decription",SqlDbType.NVarChar,256),
new SqlParameter("@createdate",SqlDbType.NVarChar),
new SqlParameter("@endate",SqlDbType.NVarChar)
};
parameters[0].Value = "%" + model.FileNam + "%";
parameters[1].Value = "%" + model.Decription + "%";
parameters[2].Value = createdate;
parameters[3].Value = endate;
return DbHelperSQL.Query(strSql, parameters);
parameters[0].Value = "'%" + model.FileNam + "%'";
parameters[1].Value = "'%" + model.Decription + "%'";
CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null,
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS
BEGIN
select * from [t_File]
where FileNam like case when @FileNam is null then FileNam else '%'+@FileNam+'%' end
and Decription like case when @Decription is null then Decription else '%'+@Decription+'%' end
and (@stardate is null or CreateOn>=@stardate) and (@enddate is null or CreateOn<=@enddate)
END
CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null,
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS
BEGIN
declare @sql nvarchar(4000)
set @sql = 'select * from t_file where 1= 1'
if len(@filenam) >0
set @sql = @sql + ' and fileNam like '''+@FileNam+''''
if len(@decription)>0
set @sql = @sql +' and decription like '''+@decription+''''
if len(@startdate)>0 or len(@enddate)>0)
set @sql = @sql +' and createOn between '''+@startdate +''' and '''+@enddate+''''
exec @sql
END
CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null,
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS
BEGIN
declare @sql nvarchar(4000)
set @sql = 'select * from t_file where 1= 1'
if len(@filenam) >0
set @sql = @sql + ' and fileNam like '''+@FileNam+''''
if len(@decription)>0
set @sql = @sql +' and decription like '''+@decription+''''
if len(@startdate)>0 or len(@enddate)>0
set @sql = @sql +' and createOn between '''+@startdate +''' and '''+@enddate+''''
exec @sql
END
