如何在两个日期之间进行搜索接受null而不是强制搜索问题 [英] How to make search between two dates accept null not obligatory search proplem
问题描述
大家好，当我在两个日期之间搜索记录时，它确实成功但你必须输入日期和日期到第一个才能进行搜索
i将显示我需要的东西从这个例子中
我需要通过4个文本框搜索动态
1-datefrom
2-dateto
3-EmployeeNo
4-EmployeeName
但我需要的搜索必须是动态的意思
如果我输入员工不仅仅给予我的员工在数据库中找不到
如果我输入员工姓名给我使用此名称的员工使用类似
如果我输入所有4个文本框null并输入按钮搜索获取所有数据
但是当我需要通过点击搜索按钮搜索时，我在此查询中有问题
i必须先写日期和日期，然后再编写员工编号或员工姓名，如果我需要搜索
所以帽子我需要员工搜索没有单独或员工姓名，而不使用日期和日期
如果我搜索不使用datefrom和dateto它给我消息错误'字符串未被识别为有效的日期时间
我的存储过程和代码如下：
Hi guys when i search record between two dates it works ok success but you must enter date from and dateto first to to make search
i will show what i need from this example
I need to search dynamic by 4 textbox
1-datefrom
2-dateto
3-EmployeeNo
4-EmployeeName
but search i need must be dynamic meaning
if i enter employee no only give me employee no found in database
if i enter employee name give me employees found with this name using like
if i enter all 4 text box null and enter button search get all data
but i have proplem in this query when i need to search by click search button
i must write date from and date to firstly then write employee no or employee name if i need to search
so that i need to search by employee no alone or employee name alone without using date from and date to
And if i search without using datefrom and dateto it give me message error 'string wasnot recognized as valid datetime"
my stored procedure and code as following :
ALTER proc [dbo].[CollectsearchData]
@StartDate datetime,
@EndDate datetime,
@EmployeeID NVARCHAR(50),
@EmployeeName nvarchar(50)
as
Begin
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery ='SELECT * from ViewEmployeeTest Where (1=1)'
If (@StartDate is not NULL)
Set @SQLQuery = @SQLQuery + ' And (joindate >= '''+ Cast(@StartDate as varchar(100))+''')'
If (@EndDate is not NULL)
Set @SQLQuery = @SQLQuery + ' And (joindate <= '''+ Cast(@EndDate as varchar(100))+''')'
If @EmployeeID <>''
Set @SQLQuery = @SQLQuery + 'And (EmployeeID = '+ @EmployeeID+') '
If @EmployeeName Is Not Null
Set @SQLQuery = @SQLQuery + ' AND (DriverName LIKE ''%'+@EmployeeName+'%'') '
Print @sqlQuery
Exec (@SQLQuery)
End
Function using
public DataTable SearchDataA(string ConnectionString,string EmployeeNo,string EmployeeName, DateTime StartDate, DateTime EndDate)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "CollectsearchData";//work
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50);
cmd.Parameters["@StartDate"].Value = StartDate;
cmd.Parameters["@EndDate"].Value = EndDate;
cmd.Parameters["@EmployeeID"].Value = EmployeeNo;
cmd.Parameters["@EmployeeName"].Value = EmployeeName;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
interface button search
try
{
CultureInfo ukCulture = new CultureInfo("en-GB");
FleetManagment.Fleet fleet = new FleetManagment.Fleet();
DataTable Table = fleet.SearchDataA("Data Source=" + value1 + ";Initial Catalog=" + value2 + ";User ID=" + value3 + ";Password=" + value4 + "",textBox3.Text,textBox4.Text, DateTime.Parse(textBox1.Text, ukCulture.DateTimeFormat), Convert.ToDateTime(textBox2.Text, ukCulture.DateTimeFormat));
dataGridView1.DataSource = Table;
dataGridView1.Refresh();
}
catch (Exception ex)
{
MessageBox.Show(ex + "error");
}
}
推荐答案
您的存储过程足够聪明，可以判断日期（或员工姓名）是空的，但你的C＃不是。
Your stored procedure is smart enough to tell if the dates (or employee name) are null, but your C# is not.
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50);
cmd.Parameters["@StartDate"].Value = StartDate ?? DBNull.Value;
cmd.Parameters["@EndDate"].Value = EndDate ?? DBNull.Value;
cmd.Parameters["@EmployeeID"].Value = EmployeeNo;
cmd.Parameters["@EmployeeName"].Value = EmployeeName ?? DBNull.Value;
或者，为简洁起见：
or, for brevity:
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate ?? DBNull.Value;
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate ?? DBNull.Value;
cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50).Value = EmployeeNo;
cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50).Value = EmployeeName ?? DBNull.Value;
存储过程无法保护您的原因的完美示例 SQL注入 [ ^ ]！
使用< a href =http://msdn.microsoft.com/en-gb/library/ms188001.aspx>sp_executesql[ ^ ]调用您的动态查询;这样，您可以正确传递参数，而无需使用字符串连接。
A perfect example of why stored procedures don't protect you from SQL Injection[^]!
Usesp_executesql[^] to call your dynamic query; that way, you can pass the parameters properly, without using string concatenation.
DECLARE @SQLQuery as nvarchar(max);
SET @SQLQuery = N'SELECT * from ViewEmployeeTest Where (1=1)';
If @StartDate Is Not Null
SET @SQLQuery = @SQLQuery + N' And (joindate >= @StartDate)';
If @EndDate Is Not Null
SET @SQLQuery = @SQLQuery + N' And (joindate <= @EndDate)';
If @EmployeeID Is Not Null And @EmployeeID != ''
SET @SQLQuery = @SQLQuery + N' And (EmployeeID = @EmployeeID)';
If @EmployeeName Is Not Null And @EmployeeName != ''
SET @SQLQuery = @SQLQuery + N' And (DriverName Like ''%'' + @EmployeeName + ''%'')';
Print @SQLQuery;
Exec sp_executesql @SQLQuery,
N'@StartDate datetime, @EndDate datetime, @EmployeeID nvarchar(50), @EmployeeName nvarchar(50)',
@StartDate, @EndDate, @EmployeeID, @EmployeeName;
检查下面的每个参数是否为NULL，如果是返回true，从而有效地绕过过滤器。如果参数为NOT NULL，则该参数用作过滤器。
Each of the parameters below is checked for NULL and if so returns true, thereby effectively bypassing the filter. If the parameter is NOT NULL, the parameter is used as a filter.
ALTER proc [dbo].[CollectsearchData]
@StartDate datetime,
@EndDate datetime,
@EmployeeID NVARCHAR(50),
@EmployeeName nvarchar(50)
AS
BEGIN
SELECT * from ViewEmployeeTest
WHERE
(@StartDate IS NULL OR joindate >= @StartDate)
AND
(@EndDate IS NULL OR joindate <= @EndDate)
AND
(@EmployeeID IS NULL OR EmployeeID = @EmployeID)
AND
(@EmployeeName IS NULL OR DriverName LIKE '%' + @EmployeeName + '%'
END
传递给C＃函数的值保证你永远不会有NULL它是DateTime而不是可以为空的DateTime（由DateTime表示？）。功能定义的更正如下：
The value being passed into your C# function guarantees that you will never have a NULL as it is a DateTime rather than a nullable DateTime (represented by DateTime?). Correction to function definition is below:
public DataTable SearchDataA(string ConnectionString,string EmployeeNo,string EmployeeName, DateTime? StartDate, DateTime? EndDate)
如果C＃为null，则所有参数都需要转换为DBNull.Value。
解决方案＃1对于字符串参数是正确的：
All of the parameters need to be converted to DBNull.Value, if they are C# null.
Solution #1 is correct for the string parameters:
cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50).Value = EmployeeNo ?? DBNull.Value;
cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50).Value = EmployeeName ?? DBNull.Value;
不幸的是，DateTime to DBNull.Value转换不兼容，必须使用if语句完成。
Unfortunately, DateTime to DBNull.Value conversions are not compatible and must done with an if statement.
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
if(StartDate.HasValue)
{
cmd.Parameters["@StartDate"].Value = StartDate.Value;
}
else
{
cmd.Parameters["@StartDate"].Value = DBNull.Value;
}
if(EndDate.HasValue)
{
cmd.Parameters["@EndDate"].Value = EndDate.Value;
}
else
{
cmd.Parameters["@EndDate"].Value = DBNull.Value;
}
希望这会有所帮助！
Hope this helps!
这篇关于如何在两个日期之间进行搜索接受null而不是强制搜索问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
