Witam.
Zainspirowany uwagą @abrakadaber w wątku http://4programmers.net/Forum/1205587 zacząłem testy z przekazywaniem daty przez parametr i pojawił się problem.
W ramach prób naskrobałem coś takiego:
string host = @"SERWER\SQL";
string user = "sa";
string password = "XXX";
string database = "TEST";
SqlConnectionStringBuilder sqlConnectionstringBuilder = new SqlConnectionStringBuilder();
sqlConnectionstringBuilder.DataSource = host;
sqlConnectionstringBuilder.InitialCatalog = database;
sqlConnectionstringBuilder.UserID = user;
sqlConnectionstringBuilder.Password = password;
DateTime from = DateTime.Parse("2010-01-01");
DateTime to = DateTime.Parse("2016-01-01");
using (SqlConnection sqlConnection = new SqlConnection())
{
sqlConnection.ConnectionString = sqlConnectionstringBuilder.ConnectionString;
try
{
sqlConnection.Open();
Console.WriteLine("Connected.\n");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("(1)");
string sqlCommandString = string.Format("SELECT TOP 10 * FROM dokument WHERE Data BETWEEN '{0}' AND '{1}'", from, to);
try
{
using (SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
Console.WriteLine(sqlDataReader[0].ToString());
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("\n(2)");
try
{
sqlCommandString = "SELECT TOP 10 * FROM dokument WHERE Data BETWEEN '@from' AND '@to'";
using (SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.Parameters.AddWithValue("@from", from);
sqlCommand.Parameters.AddWithValue("@to", to);
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
Console.WriteLine(sqlDataReader[0].ToString());
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.ReadKey();
Wynikiem działania jest:
Connected.
(1)
1
2
3
4
5
6
7
8
9
10
(2)
Conversion failed when converting date and/or time from character string.
Jak jak widać przy zapytaniu z parametrem serwer zwraca błąd.
Podejrzałem profilerem przekazane zapytanie i wygląda tak:
exec sp_executesql N'SELECT TOP 10 * FROM dokument WHERE Data BETWEEN ''@from'' AND ''@to''',N'@from datetime,@to datetime',@from='2010-01-01 00:00:00',@to='2016-01-01 00:00:00'
Jak poprawnie przekazać datę przez parametr do zapytania?