无法从c#代码向存储过程传递sql Date类型的参数
本文关键字:sql Date 类型 参数 存储过程 代码 | 更新日期: 2023-09-27 18:14:51
我有一个通过比较两个日期来选择记录的存储过程。从c#代码,我传递参数到存储过程,但得到错误
将参数值从字符串转换为日期时间失败。
我的SQL Server存储过程是:
ALTER PROCEDURE Select
@fromDt date = null,
@toDt date = null,
@flag int
AS
BEGIN
SET NOCOUNT ON;
if @flag = 1
begin
SELECT xxx,xxx,xxx from xxx where Status <> 'Complete'
end
else
begin
SELECT xxx,x,xxx from xxx where Status <> 'Complete' and Date >= @fromDt and Date < @toDt
end
END
GO
我的代码查询表是:
protected void btnFilter_Click(object sender, EventArgs e)
{
DoProcess(Convert.ToString(dtFrom.SelectedDate.ToString("yyyyMMdd")), Convert.ToString(dtTo.SelectedDate.ToString("yyyyMMdd")), 0);
}
private void DoProcess(string fromDt,string toDt, int flag)
{
SqlConnection connection = GeneralMethods.GetConnection();
SqlCommand selectCommand = new SqlCommand();
DataTable dtVendors = new DataTable();
try
{
// set command object properties
selectCommand.Connection = connection;
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.CommandText = "Select";
// check if from date and to dates are not null then set parameters to stored procedure.
if (!string.IsNullOrEmpty(fromDt))
{
selectCommand.Parameters.Add("@fromDt", SqlDbType.Date).Value = fromDt;
selectCommand.Parameters.Add("@toDt", SqlDbType.Date).Value = toDt;
}
// @flag is 1 then do not compare dates if 0 then compare to and from dates.
selectCommand.Parameters.Add("@flag", SqlDbType.Int).Value = flag;
// open the connection execute the select command.
connection.Open();
// load data into data table.
dtVendors.Load(selectCommand.ExecuteReader());
connection.Close();
if (dtVendors.Rows.Count > 0)
{
grdVendors.DataSource = dtVendors;
grdVendors.DataBind();
}
}
catch (Exception ex)
{ }
finally
{
// finally close the connection if open and release the resources
if (connection.State == System.Data.ConnectionState.Open)
connection.Close();
connection.Dispose();
selectCommand.Dispose();
dtVendors.Dispose();
}
}
我该如何处理这个错误?
为什么将日期参数作为字符串传递?
selectCommand.Parameters.Add("@fromDt", SqlDbType.Date).Value = DateTime.Parse(fromDt);
或更好
selectCommand.Parameters.Add("@fromDt", SqlDbType.Date, DateTime.Parse(fromDt));