Passing parameter of C# DateTime to SQL Server 2005?

Question

How do I pass C# DateTime values (FromCreateDate and ToCreateDate) to SQL Server 2005 in order to select from a view ?

The result set CreateDate column must be between FromDateDate and ToCreateDate.

Answer 1

Exactly the same as you would any other parameter... of course it depends on how you are doing your data-access, but if we assume a SqlCommand you'd just refer to a named parameter (@fromDate / @toDate) in the TSQL, and add named SqlParameters (with .Value = theDate) to the command:

DateTime end = DateTime.Today, start = end.AddDays(-7); // the last week
using (var conn = new SqlConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.Parameters.AddWithValue("@from", start); // fine for DateTime; for strings, use more explicit param
    cmd.Parameters.AddWithValue("@to", end); // construction to avoid lots of different-length plans
    cmd.CommandText = "SELECT COUNT(1) FROM [Users] WHERE LastSeen >= @from AND LastSeen < @to";
    int count = (int) cmd.ExecuteScalar();
}

With LINQ etc you'd just use it in the query, i.e.

int count = db.Users.Count(user => user.LastSeen>=start && user.LastSeen<end);

I'm using a simple count in the examples just to keep it simple; obviously you can SELECT blah from a VIEW etc too...

Answer 2

Just set the parameter type to SqlDbType.DateTime

e.g

SqlCommand cmd = new SqlCommand("Select * From dbo.MyView Where createDate = @FromDate", SqlCon);
cmd.Parameters.Add(new SqlParameter(@FromDate, SqlDbType.DateTime));
cmd.Parameters["@FromDate"].Value = fromDate;

Answer 3

If you are using Inline-Query :

private DataTable GetData(string StartDate,string EndDate)
{
string Cmd = SELECT * FROM DATE_SAMPLE WHERE createDate >= '"+StartDate+"' AND createDate <= '"+EndDate"'

SqlCommand SqlCmd - new SqlCommand (Cmd,SqlConnection);
SqlDataAdpater SqlAdpt = new SqlDataAdpater(SqlCmd);
DataTable Records = new DataTable();
SqlAdpt.Fill(Records);
return Records;
}

//Calling Function
GetData(dateTimePicker_StartDate.Value.ToString("MM/dd/yyyy"),dateTimePicker_End.Value.ToString("MM/dd/yyyy"));