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); }