public static List <T> ExecuteQuery <T>(this TdsConnection cnn, string text) where T : class, new()
{
var writer = cnn.TdsPackage.Writer;
var reader = cnn.TdsPackage.Reader;
var parser = cnn.StreamParser;
writer.SendExecuteBatch(text, cnn.SqlTransactionId);
parser.ParseInput();
if (parser.Status == ParseStatus.Done)
{
return(null);
}
var result = new List <T>();
var rowReader = GetRowReader <T>(reader, text);
var columnReader = new TdsColumnReader(cnn.TdsPackage.Reader);
while (parser.Status != ParseStatus.Done)
{
var row = rowReader(columnReader);
parser.ParseInput();
result.Add(row);
}
return(result);
}
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 static List <T> ExecuteParameterQuery <T>(this TdsConnection cnn, FormattableString text) where T : class, new()
{
var writer = cnn.TdsPackage.Writer;
var reader = cnn.TdsPackage.Reader;
var parser = cnn.StreamParser;
writer.SendRpc(reader.CurrentSession.DefaultCollation, text, cnn.SqlTransactionId);
parser.ParseInput();
if (parser.Status == ParseStatus.Done)
{
return(null);
}
var r = new List <T>();
var rowReader = GetRowReader <T>(reader, text.Format);
var columnReader = new TdsColumnReader(reader);
while (parser.Status != ParseStatus.Done)
{
var result = rowReader(columnReader);
parser.ParseInput();
r.Add(result);
}
return(r);
}
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_SelectAll_Tables_Pubs3()
{
var sqlQuery = new[]
{
"SELECT TOP 10 * FROM publishers",
"SELECT TOP 10 * FROM authors",
"SELECT TOP 10 * FROM titles",
"SELECT TOP 10 * FROM titleauthor",
"SELECT TOP 10 * FROM salesdetail",
"SELECT TOP 10 * FROM sales",
"SELECT TOP 10 * FROM stores",
"SELECT TOP 10 * FROM store_employees",
"SELECT TOP 10 * FROM roysched",
"SELECT TOP 10 * FROM discounts",
"SELECT TOP 10 * FROM blurbs",
};
using (IDbConnection connection = new TdsConnection(connectionString, ConsoleLogger.LoggerFactory))
{
connection.Open();
connection.ChangeDatabase("pubs3");
foreach (var q in sqlQuery)
{
var result = connection.Query(q);
foreach (var r in result)
{
Console.WriteLine($" {r}");
}
}
}
}
public MedellaOrm()
{
var connections = new TdsConnection[conc];
_tds = TdsConnectionPools.GetConnectionPool(_connectionString);
var task = Parallel.For(0, conc, (x) => connections[x] = _tds.GetConnection());
//OrmTester.EnsureDbSetup(_connectionString);
task = Parallel.For(0, conc, (x) => _tds.Return(connections[x]));
}
public void Connection_Dapper()
{
string sqlQuery = "exec sp_help 'titles'";
var result = new TdsConnection(connectionString, ConsoleLogger.LoggerFactory).Query(sqlQuery);
foreach (var r in result)
{
Console.WriteLine($" {r}");
}
}
public TdsConnection GetConnection()
{
if (_freepool.TryDequeue(out var tdsController))
{
return(tdsController);
}
var options = _options;
var tdsStream = TdsStreamProxy.CreatedStream(options.DataSource, options.ConnectTimeout);
var cnn = new TdsConnection(tdsStream, options);
return(cnn);
}
public void Connection_Dapper_WithParams()
{
using (IDbConnection connection = new TdsConnection(connectionString, ConsoleLogger.LoggerFactory))
{
connection.Open();
connection.ChangeDatabase("pubs3");
var result = connection.Query("SELECT * FROM titles where title_id = @Id", new { Id = "PS3333" });
foreach (var r in result)
{
Console.WriteLine($" {r}");
}
}
}
public void Get_50_connections_return_to_the_pool_and_use_one()
{
var x = new TdsConnection[50];
var tds = TdsConnectionPools.GetConnectionPool(ConnectionString);
for (var j = 0; j < 50; j++)
{
x[j] = tds.GetConnection();
}
//OrmTester.EnsureDbSetup(_connectionString);
for (var j = 0; j < 50; j++)
{
tds.Return(x[j]);
}
}
public static void BulkInsert <T>(this TdsConnection cnn, IEnumerable <T> objects, string tableName, Dictionary <string, PropertyInfo> columnMapping)
{
var writer = cnn.TdsPackage.Writer;
var reader = cnn.TdsPackage.Reader;
var parser = cnn.StreamParser;
MetadataBulkCopy[] metaDataAllColumns = null;
writer.SendExecuteBatch($"SET FMTONLY ON select * from {tableName} SET FMTONLY OFF", cnn.SqlTransactionId);
parser.ParseInput(count => { metaDataAllColumns = reader.ColMetaDataBulkCopy(count); });
writer.ColumnsMetadata = columnMapping != null?GetUsedColumns(metaDataAllColumns, columnMapping) : GetUsedColumns(metaDataAllColumns);
var bulkInsert = CreateBulkInsertStatement(tableName, writer.ColumnsMetadata);
writer.SendExecuteBatch(bulkInsert, cnn.SqlTransactionId);
parser.ParseInput();
writer.NewPackage(TdsEnums.MT_BULK);
var columnWriter = new TdsColumnWriter(writer);
var rowWriter = RowWriter.GetComplexWriter <T>(columnWriter);
WriteBulkInsertColMetaData(writer);
foreach (var o in objects)
{
writer.WriteByte(TdsEnums.SQLROW);
rowWriter(columnWriter, o);
}
writer.WriteByteArray(Done);
writer.SendLastMessage();
parser.ParseInput();
if (parser.Status != ParseStatus.Done)
{
parser.ParseInput();
}
}
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();
}
}
public static void ExecuteNonQuery(this TdsConnection cnn, string text)
{
cnn.TdsPackage.Writer.SendExecuteBatch(text, cnn.SqlTransactionId);
cnn.StreamParser.ParseInput();
}
public static void Main(string[] args)
{
Console.WriteLine("Start TestSqlConnection.");
if (args.Length != 6 && args.Length != 7)
{
Console.WriteLine(
"\nUsage: mono TestSqlConnection.exe Client Table Column Server Database UserID [Password]\n\n" +
#if IncludeSybaseAndTdsClient
"\tClient is one of the following: SqlClient, TdsClient, or SybaseClient\n" +
#else
"\tClient is: SqlClient. No support for TdsClient nor SybaseClient\n" +
#endif // IncludeSybaseAndTdsClient
"\tTable is the name of the database table to select from\n" +
"\tColumn is the name of the column in the Table to select from\n" +
"\tServer is the SQL Server to connect. Use one of the following forms:\n" +
"\t\tHOSTNAME Ex: MYHOST\n" +
"\t\tHOSTNAME,port Ex: MYHOST,1433\n" +
"\t\tHOSTNAME\\\\instance Ex: MYHOST\\\\NETSDK Note: only works with SqlClient\n" +
"\tDatabase is the name of the database to use\n" +
"\tUser ID is the user's User ID\n" +
"\tPassword is the user's Password Note: if ommitted, a blank password is used\n" +
"Exampes:\n" +
"\tEx 1: SqlClient employee lname MYHOST pubs myuserid mypassword\n" +
"\tEx 3: SqlClient employee lname MYHOST,1443 pubs myuserid mypassword\n" +
"\tEx 2: SqlClient Products ProductName MYHOST\\\\NETSDK myuserid mypassword\n" +
"\tEx 4: SqlClient employee lname MYHOST pubs myuserid\n" +
"\tEx 5: TdsClient sometable somecolumn MYHOST test myuserid mypassword\n" +
"\tEx 6: SybaseClient sometable somecolumn MYHOST test myuserid mypassword\n");
return;
}
string client = args[0];
string tableName = args[1];
string columnName = args[2];
string server = args[3];
string database = args[4];
string userid = args[5];
string password = "";
if (args.Length == 7)
{
password = args[6];
}
string constr;
string sql;
Console.WriteLine("\nClient: " + client);
Console.WriteLine("Table Name: " + tableName);
Console.WriteLine("Column Name: " + columnName);
Console.WriteLine("Server: " + server);
Console.WriteLine("Database: " + database);
Console.WriteLine("User ID: " + userid);
Console.WriteLine("Password: "******"SELECT " + columnName + " FROM " + tableName;
constr =
"Server=" + server + ";" +
"Database=" + database + ";" +
"User ID=" + userid + ";" +
"Password="******";";
Console.WriteLine("\nConnectionString: " + constr);
Console.WriteLine("SQL: " + sql);
Console.WriteLine("\nCreating Connection...");
IDbConnection con = null;
switch (client.ToUpper())
{
case "SQLCLIENT":
con = new SqlConnection();
break;
#if IncludeSybaseAndTdsClient
case "TDSCLIENT":
con = new TdsConnection();
break;
case "SYBASECLIENT":
con = new SybaseConnection();
break;
default:
Console.WriteLine("Invalid client: " + client + "\nUse SqlClient, TdsClient, or SybaseClient");
return;
#else
default:
Console.WriteLine("Invalid client: " + client + "\nUse SqlClient. No support for TdsClient nor SybaseClient.");
return;
#endif
}
Console.WriteLine("set connection string...");
con.ConnectionString = constr;
Console.WriteLine("open connection...");
try {
con.Open();
}
catch (SqlException se) {
Console.WriteLine("SqlException caught");
Console.WriteLine("Message: " + se.Message);
Console.WriteLine("Procedure: " + se.Procedure);
Console.WriteLine("Class: " + se.Class);
Console.WriteLine("Number: " + se.Number);
Console.WriteLine("Source: " + se.Source);
Console.WriteLine("State: " + se.State);
Console.WriteLine("Errors:");
foreach (SqlError error in se.Errors)
{
Console.WriteLine(" SqlError:");
Console.WriteLine(" Message: " + se.Message);
Console.WriteLine(" Line Number: " + se.LineNumber);
Console.WriteLine(" Procedure: " + se.Procedure);
Console.WriteLine(" Class: " + se.Class);
Console.WriteLine(" Number: " + se.Number);
Console.WriteLine(" Server: " + se.Server);
Console.WriteLine(" Source: " + se.Source);
Console.WriteLine(" State: " + se.State);
}
Console.WriteLine("StackTrace: " + se.StackTrace);
Console.WriteLine("TargetSite: " + se.TargetSite);
Exception ie = se.InnerException;
if (ie != null)
{
Console.WriteLine("InnerException:");
Console.WriteLine(" Message: " + se.Message);
Console.WriteLine(" Class: " + se.Class);
Console.WriteLine(" Number: " + se.Number);
Console.WriteLine(" Source: " + se.Source);
Console.WriteLine(" State: " + se.State);
Console.WriteLine(" StackTrace: " + se.StackTrace);
Console.WriteLine(" TargetSite: " + se.TargetSite);
}
return;
}
Console.WriteLine("Creating command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("set SQL...");
cmd.CommandText = sql;
Console.WriteLine("execute reader...");
IDataReader reader = cmd.ExecuteReader();
Console.WriteLine("read first row...");
if (reader.Read())
{
Console.WriteLine(" Value: " + reader[columnName].ToString());
}
else
{
Console.WriteLine(" No data returned. Or either, no permission to read data.");
}
Console.WriteLine("Clean up...");
// clean up
reader.Close();
reader = null;
cmd.Dispose();
cmd = null;
con.Close();
con = null;
Console.WriteLine("Done.");
}
public void Return(TdsConnection cnn)
{
Debug.WriteLine("connection returned to the pool");
cnn.ResetToInitialState();
_freepool.Enqueue(cnn);
}
public static void Return(string connectionString, TdsConnection tdsConnection)
{
var freePool = FreePool.GetOrAdd(connectionString, x => new TdsConnectionPool(new SqlConnectionString(connectionString)));
freePool.Return(tdsConnection);
}
public static void BulkInsert <T>(this TdsConnection cnn, IEnumerable <T> objects, string tableName)
{
cnn.BulkInsert(objects, tableName, null);
}
