public void Connection_SimpleSelect()
{
string sqlQuery = "SELECT top 10 * from titles";
using (IDbConnection connection = new TdsConnection(connectionString, ConsoleLogger.LoggerFactory))
{
var command = connection.CreateCommand();
command.CommandText = sqlQuery;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write($" {reader.GetName(i)} : {reader[i]}");
}
Console.WriteLine();
}
reader.Close();
}
}
public void Connection_QueryWithParamTest_2()
{
string sqlQuery = "SELECT @par";
string paramValue = "test123";
using (IDbConnection connection = new TdsConnection(connectionString, ConsoleLogger.LoggerFactory))
{
var command = connection.CreateCommand();
command.CommandText = sqlQuery;
var param = command.CreateParameter();
param.ParameterName = "@par";
param.Value = paramValue;
param.DbType = DbType.String;
command.Parameters.Add(param);
connection.Open();
var reader = command.ExecuteReader();
while (reader != null && reader.Read())
{
Console.WriteLine("\t{0}", reader[0]);
}
reader.Close();
}
}
public void Connection_QueryWithParamTest()
{
string sqlQuery =
"SELECT @string as c1, @char as c2, @int as c3, @double as c4, @decimal as c5, @datetime as c6";
string strParam = "test";
char charParam = 't';
int intParam = 123141;
double doubleParam = 5.55;
decimal decimalParam = 3.02m;
var dt = DateTime.Now.AddSeconds(123000);
DateTime dtParam = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Day, dt.Second);
using (IDbConnection connection = new TdsConnection(connectionString, ConsoleLogger.LoggerFactory))
{
var command = connection.CreateCommand();
command.CommandText = sqlQuery;
var param = command.CreateParameter();
param.ParameterName = "@string";
param.Value = strParam;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@char";
param.Value = charParam;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@int";
param.Value = intParam;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@double";
param.Value = doubleParam;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@decimal";
param.Value = decimalParam;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@datetime";
param.Value = dtParam;
command.Parameters.Add(param);
connection.Open();
var reader = command.ExecuteReader();
while (reader != null && reader.Read())
{
Console.WriteLine($"\"{reader[0]}\", \"{reader[1]}\", \"{reader[2]}\", \"{reader[3]}\", \"{reader[4]}\", \"{reader[5]}\"");
Assert.AreEqual(strParam, reader[0]);
Assert.AreEqual(charParam.ToString(), reader[1]);
Assert.AreEqual(intParam, reader[2]);
Assert.AreEqual(doubleParam, reader[3]);
Assert.AreEqual(decimalParam, reader[4]);
Assert.AreEqual(dtParam, reader[5]);
}
reader.Close();
}
}