System.Data.SqlClient.SqlConnection.CreateCommand()

Here are the examples of the csharp api System.Data.SqlClient.SqlConnection.CreateCommand() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

532 Examples 7

19 View Source File : WhereWithMapperTest.cs
License : MIT License
Project Creator : christiandelbianco

[TestInitialize]
        public void TestInitialize()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }

            CheckValues.Clear();
            CheckValuesOld.Clear();

            _counter = 0;

            CheckValues.Add(ChangeType.Insert.ToString(), new Tuple<WithMapperTestModel, WithMapperTestModel>(new WithMapperTestModel { Identificator = _id, Surname = "Del Bianco", Name = "Christian" }, new WithMapperTestModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<WithMapperTestModel, WithMapperTestModel>(new WithMapperTestModel { Identificator = _id, Surname = "Nonna", Name = "Velia" }, new WithMapperTestModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<WithMapperTestModel, WithMapperTestModel>(new WithMapperTestModel { Identificator = _id, Surname = "Nonna", Name = "Velia" }, new WithMapperTestModel()));

            CheckValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<WithMapperTestModel, WithMapperTestModel>(new WithMapperTestModel { Identificator = _id, Surname = "Del Bianco", Name = "Christian" }, new WithMapperTestModel()));
            CheckValuesOld.Add(ChangeType.Update.ToString(), new Tuple<WithMapperTestModel, WithMapperTestModel>(new WithMapperTestModel { Identificator = _id, Surname = "Nonna", Name = "Velia" }, new WithMapperTestModel()));
            CheckValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<WithMapperTestModel, WithMapperTestModel>(new WithMapperTestModel { Identificator = _id, Surname = "Nonna", Name = "Velia" }, new WithMapperTestModel()));
        }

19 View Source File : WhereWithMapperTest.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedCleanup]
        public static void ClreplacedCleanup()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF OBJECT_ID(N'{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : WhereWithMapperTest.cs
License : MIT License
Project Creator : christiandelbianco

private static void ModifyTableContent()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([Id], [Name], [Second Name]) VALUES (999, N'Iron', N'Man')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([Id], [Name], [Second Name]) VALUES ({CheckValues[ChangeType.Insert.ToString()].Item1.Identificator}, N'{CheckValues[ChangeType.Insert.ToString()].Item1.Name}', N'{CheckValues[ChangeType.Insert.ToString()].Item1.Surname}')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [Name] = N'Spider', [Second Name] = 'Man' WHERE [Id] = 999";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [Name] = N'{CheckValues[ChangeType.Update.ToString()].Item1.Name}', [Second Name] =  N'{CheckValues[ChangeType.Update.ToString()].Item1.Surname}' WHERE [Id] = {CheckValues[ChangeType.Update.ToString()].Item1.Identificator}";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}] WHERE [Id] = 999";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}] WHERE [Id] = {CheckValues[ChangeType.Delete.ToString()].Item1.Identificator}";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : XmlAsStringSqlServerTest.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedInitialize]
        public static void ClreplacedInitialize(TestContext testContext)
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"CREATE TABLE {TableName}(VarcharMAXColumn VARCHAR(MAX) NULL, NvarcharMAXColumn NVARCHAR(MAX) NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : WhereCustomFilterTest.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedInitialize]
        public static void ClreplacedInitialize(TestContext testContext)
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText =
                        $"CREATE TABLE [{TableName}]( " +
                        "[Id] [int] NOT NULL, " +
                        "[Name] [nvarchar](50) NOT NULL, " +
                        "[Second Name] [nvarchar](50) NULL, " +
                        "[Born] [datetime] NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : WhereEqualToTest.cs
License : MIT License
Project Creator : christiandelbianco

[TestInitialize]
        public void TestInitialize()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }

            CheckValues.Clear();
            CheckValuesOld.Clear();

            _counter = 0;

            CheckValues.Add(ChangeType.Insert.ToString(), new Tuple<EqualToTestSqlServerModel, EqualToTestSqlServerModel>(new EqualToTestSqlServerModel { Id = _id, Name = "Christian" }, new EqualToTestSqlServerModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<EqualToTestSqlServerModel, EqualToTestSqlServerModel>(new EqualToTestSqlServerModel { Id = _id, Name = "Velia" }, new EqualToTestSqlServerModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<EqualToTestSqlServerModel, EqualToTestSqlServerModel>(new EqualToTestSqlServerModel { Id = _id, Name = "Velia" }, new EqualToTestSqlServerModel()));

            CheckValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<EqualToTestSqlServerModel, EqualToTestSqlServerModel>(new EqualToTestSqlServerModel { Id = _id, Name = "Christian" }, new EqualToTestSqlServerModel()));
            CheckValuesOld.Add(ChangeType.Update.ToString(), new Tuple<EqualToTestSqlServerModel, EqualToTestSqlServerModel>(new EqualToTestSqlServerModel { Id = _id, Name = "Velia" }, new EqualToTestSqlServerModel()));
            CheckValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<EqualToTestSqlServerModel, EqualToTestSqlServerModel>(new EqualToTestSqlServerModel { Id = _id, Name = "Velia" }, new EqualToTestSqlServerModel()));
        }

19 View Source File : WhereEqualToTest.cs
License : MIT License
Project Creator : christiandelbianco

private static void ModifyTableContent()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([Id], [Name]) VALUES (999, N'Iron Man')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([Id], [Name]) VALUES ({CheckValues[ChangeType.Insert.ToString()].Item1.Id}, N'{CheckValues[ChangeType.Insert.ToString()].Item1.Name}')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [Name] = N'Spider Man' WHERE [Id] = 999";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [Name] = N'{CheckValues[ChangeType.Update.ToString()].Item1.Name}' WHERE [Id] = {CheckValues[ChangeType.Update.ToString()].Item1.Id}";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}] WHERE [Id]= 999";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}] WHERE [Id] = {CheckValues[ChangeType.Delete.ToString()].Item1.Id}";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : WhereFilterUsingSchemaTest.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedInitialize]
        public static void ClreplacedInitialize(TestContext testContext)
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForSa))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF NOT EXISTS(SELECT schema_name FROM information_schema.schemata WHERE schema_name = '{SchemaName}') BEGIN EXEC sp_executesql N'CREATE SCHEMA [{SchemaName}];'; END;";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{TableName}' AND TABLE_SCHEMA = '{SchemaName}'";
                    var exists = (int)sqlCommand.ExecuteScalar();
                    if (exists > 0)
                    {
                        sqlCommand.CommandText = $"DROP TABLE [{SchemaName}].[{TableName}]";
                        sqlCommand.ExecuteNonQuery();
                    }

                    sqlCommand.CommandText = $"CREATE TABLE [{SchemaName}].[{TableName}] (Id INT NULL, NAME nvarchar(50) NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : WhereFilterUsingSchemaTest.cs
License : MIT License
Project Creator : christiandelbianco

[TestInitialize]
        public void TestInitialize()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForSa))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{SchemaName}].[{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }

            CheckValues.Clear();
            CheckValuesOld.Clear();

            _counter = 0;

            CheckValues.Add(ChangeType.Insert.ToString(), new Tuple<FilterUsingSchemaTestModel, FilterUsingSchemaTestModel>(new FilterUsingSchemaTestModel { Name = "Christian" }, new FilterUsingSchemaTestModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<FilterUsingSchemaTestModel, FilterUsingSchemaTestModel>(new FilterUsingSchemaTestModel { Name = "Velia" }, new FilterUsingSchemaTestModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<FilterUsingSchemaTestModel, FilterUsingSchemaTestModel>(new FilterUsingSchemaTestModel { Name = "Velia" }, new FilterUsingSchemaTestModel()));

            CheckValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<FilterUsingSchemaTestModel, FilterUsingSchemaTestModel>(new FilterUsingSchemaTestModel { Name = "Christian" }, new FilterUsingSchemaTestModel()));
            CheckValuesOld.Add(ChangeType.Update.ToString(), new Tuple<FilterUsingSchemaTestModel, FilterUsingSchemaTestModel>(new FilterUsingSchemaTestModel { Name = "Velia" }, new FilterUsingSchemaTestModel()));
            CheckValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<FilterUsingSchemaTestModel, FilterUsingSchemaTestModel>(new FilterUsingSchemaTestModel { Name = "Velia" }, new FilterUsingSchemaTestModel()));
        }

19 View Source File : WhereFilterUsingSchemaTest.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedCleanup]
        public static void ClreplacedCleanup()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForSa))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{TableName}' AND TABLE_SCHEMA = '{SchemaName}'";
                    var exists = (int)sqlCommand.ExecuteScalar();
                    if (exists > 0)
                    {
                        sqlCommand.CommandText = $"DROP TABLE [{SchemaName}].[{TableName}];";
                        sqlCommand.ExecuteNonQuery();
                    }

                    sqlCommand.CommandText = $"DROP SCHEMA [{SchemaName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : WhereMultipleConditionsTest.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedInitialize]
        public static void ClreplacedInitialize(TestContext testContext)
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"CREATE TABLE [{TableName}]([Id] [int] NOT NULL, [CategoryId] [int] NOT NULL, [Quanreplacedy] [int] NOT NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : XmlAsStringSqlServerTest.cs
License : MIT License
Project Creator : christiandelbianco

[TestInitialize]
        public void TestInitialize()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }

            _checkValues.Clear();
            _checkValuesOld.Clear();

            _checkValues.Add(ChangeType.Insert.ToString(), new Tuple<XmlreplacedtringSqlServerTestModel, XmlreplacedtringSqlServerTestModel>(new XmlreplacedtringSqlServerTestModel { VarcharMaxColumn = XmlForInsert, NvarcharMaxColumn = XmlForInsert }, new XmlreplacedtringSqlServerTestModel()));
            _checkValues.Add(ChangeType.Update.ToString(), new Tuple<XmlreplacedtringSqlServerTestModel, XmlreplacedtringSqlServerTestModel>(new XmlreplacedtringSqlServerTestModel { VarcharMaxColumn = XmlForUpdate, NvarcharMaxColumn = XmlForUpdate }, new XmlreplacedtringSqlServerTestModel()));
            _checkValues.Add(ChangeType.Delete.ToString(), new Tuple<XmlreplacedtringSqlServerTestModel, XmlreplacedtringSqlServerTestModel>(new XmlreplacedtringSqlServerTestModel { VarcharMaxColumn = XmlForUpdate, NvarcharMaxColumn = XmlForUpdate }, new XmlreplacedtringSqlServerTestModel()));

            _checkValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<XmlreplacedtringSqlServerTestModel, XmlreplacedtringSqlServerTestModel>(new XmlreplacedtringSqlServerTestModel { VarcharMaxColumn = XmlForInsert, NvarcharMaxColumn = XmlForInsert }, new XmlreplacedtringSqlServerTestModel()));
            _checkValuesOld.Add(ChangeType.Update.ToString(), new Tuple<XmlreplacedtringSqlServerTestModel, XmlreplacedtringSqlServerTestModel>(new XmlreplacedtringSqlServerTestModel { VarcharMaxColumn = XmlForUpdate, NvarcharMaxColumn = XmlForUpdate }, new XmlreplacedtringSqlServerTestModel()));
            _checkValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<XmlreplacedtringSqlServerTestModel, XmlreplacedtringSqlServerTestModel>(new XmlreplacedtringSqlServerTestModel { VarcharMaxColumn = XmlForUpdate, NvarcharMaxColumn = XmlForUpdate }, new XmlreplacedtringSqlServerTestModel()));
        }

19 View Source File : XmlAsStringSqlServerTest.cs
License : MIT License
Project Creator : christiandelbianco

private static void ModifyTableContent1()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([VarcharMAXColumn], [NvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", _checkValues[ChangeType.Insert.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", _checkValues[ChangeType.Insert.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.Parameters.Clear();
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [VarcharMAXColumn] = @varcharMAXColumn, [NvarcharMAXColumn] = @nvarcharMAXColumn";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", _checkValues[ChangeType.Update.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", _checkValues[ChangeType.Update.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.Parameters.Clear();
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}]";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : XmlNVarcharMaxAndVarcharMaxType2.cs
License : MIT License
Project Creator : christiandelbianco

private static void ModifyTableContent1()
        {
            CheckValues.Add(ChangeType.Insert.ToString(),
                new Tuple<NVarcharMaxAndVarcharMaxModel2, NVarcharMaxAndVarcharMaxModel2>(new NVarcharMaxAndVarcharMaxModel2
                { varcharMAXColumn = new string('¢', 6000), nvarcharMAXColumn = "мы фантастические" }, new NVarcharMaxAndVarcharMaxModel2()));

            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([varcharMAXColumn], [nvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.varcharMAXColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.nvarcharMAXColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);
            }
        }

19 View Source File : Program.cs
License : MIT License
Project Creator : christiandelbianco

public static void Main(string[] args)
        {
            int deletedCnt = 0;
            int insertedCnt = 0;
            int updatedCnt = 0;
            int total = 999999;
            int index = 1;
            int i = 1;
            var connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

            Console.replacedle = new string('*', 10) + " SQL ServerDB Writer 1 " + new string('*', 10);
            System.Threading.Thread.Sleep(4000);
            
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    while (index < total)
                    {
                        switch (i)
                        {
                            case 1:
                                sqlCommand.CommandText = "INSERT INTO [LoadTest] ([Id], [FirstName], [SecondName]) VALUES (1, 'mannaggia', 'alla puttana')";
                                if (sqlCommand.ExecuteNonQuery() > 0) insertedCnt++;
                                i++;
                                break;

                            case 2:
                                sqlCommand.CommandText = "UPDATE [LoadTest] SET [FirstName] = 'cazzarola', [SecondName] = '" + Guid.NewGuid() + "' WHERE [Id] = 1";
                                if (sqlCommand.ExecuteNonQuery() > 0) updatedCnt++;
                                i++;
                                break;

                            case 3:
                                sqlCommand.CommandText = "DELETE FROM [LoadTest] WHERE [Id] = 1";
                                if (sqlCommand.ExecuteNonQuery() > 0) deletedCnt++;
                                i = 1;
                                break;
                        }

                        Console.WriteLine("Writer 1 executed: " + Environment.NewLine + sqlCommand.CommandText);
                        System.Threading.Thread.Sleep(50);
                        index++;
                    }
                }
            }

            Console.WriteLine("INSERT counter: " + insertedCnt);
            Console.WriteLine("UPDATE counter: " + updatedCnt);
            Console.WriteLine("DELETE counter: " + deletedCnt);

            Console.WriteLine(Environment.NewLine + "Press a key to exit");
            Console.ReadKey();
        }

19 View Source File : Program.cs
License : MIT License
Project Creator : christiandelbianco

public static void Main(string[] args)
        {
            int deletedCnt = 0;
            int insertedCnt = 0;
            int updatedCnt = 0;
            int total = 7777;
            int index = 1;
            int i = 1;

            var connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

            Console.replacedle = new string('*', 10) + " SQL Server DB Writer 2 " + new string('*', 10);
            System.Threading.Thread.Sleep(6000);
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    while (index < total)
                    {
                        switch (i)
                        {
                            case 1:
                                sqlCommand.CommandText = "INSERT INTO [LoadTest] ([Id], [FirstName], [SecondName]) VALUES (2, 'sono cazzi acidi', 'AAAAS')";                                
                                if (sqlCommand.ExecuteNonQuery() > 0) insertedCnt++;
                                i++;
                                break;

                            case 2:
                                sqlCommand.CommandText = "UPDATE [LoadTest] SET [FirstName] = 'Guai grossi', [SecondName] = '" + Guid.NewGuid() + "' WHERE [Id] = 2";
                                if (sqlCommand.ExecuteNonQuery() > 0) updatedCnt++;
                                i++;
                                break;

                            case 3:
                                sqlCommand.CommandText = "DELETE FROM [LoadTest] WHERE [Id] = 2";
                                if (sqlCommand.ExecuteNonQuery() > 0) deletedCnt++;
                                i = 1;
                                break;
                        }

                        Console.WriteLine("Writer 2 executed: " + Environment.NewLine + sqlCommand.CommandText);
                        System.Threading.Thread.Sleep(50);
                        index++;
                    }
                }
            }

            Console.WriteLine("INSERT counter: " + insertedCnt);
            Console.WriteLine("UPDATE counter: " + updatedCnt);
            Console.WriteLine("DELETE counter: " + deletedCnt);

            Console.WriteLine(Environment.NewLine + "Press a key to exit");
            Console.ReadKey();
        }

19 View Source File : XmlNVarcharMaxAndVarcharMaxType.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedInitialize()]
        public static void ClreplacedInitialize(TestContext testContext)
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"CREATE TABLE {TableName}(varcharMAXColumn VARCHAR(MAX) NULL, NvarcharMAXColumn NVARCHAR(MAX) NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : XmlNVarcharMaxAndVarcharMaxType.cs
License : MIT License
Project Creator : christiandelbianco

[ClreplacedCleanup()]
        public static void ClreplacedCleanup()
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : XmlNVarcharMaxAndVarcharMaxType.cs
License : MIT License
Project Creator : christiandelbianco

private static void ModifyTableContent1()
        {
            CheckValues.Add(ChangeType.Insert.ToString(), new Tuple<XmlNVarcharMaxAndVarcharMaxModel, XmlNVarcharMaxAndVarcharMaxModel>(new XmlNVarcharMaxAndVarcharMaxModel { varcharMAXColumn = new string('*', 6000), nvarcharMAXColumn = new string('*', 8000) }, new XmlNVarcharMaxAndVarcharMaxModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<XmlNVarcharMaxAndVarcharMaxModel, XmlNVarcharMaxAndVarcharMaxModel>(new XmlNVarcharMaxAndVarcharMaxModel { varcharMAXColumn = "111", nvarcharMAXColumn = "new byte[] { 1, 2, 3, 4, 5, 6 }" }, new XmlNVarcharMaxAndVarcharMaxModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<XmlNVarcharMaxAndVarcharMaxModel, XmlNVarcharMaxAndVarcharMaxModel>(new XmlNVarcharMaxAndVarcharMaxModel { varcharMAXColumn = "111", nvarcharMAXColumn = "new byte[] { 1, 2, 3, 4, 5, 6 }" }, new XmlNVarcharMaxAndVarcharMaxModel()));

            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([varcharMAXColumn], [nvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.varcharMAXColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.nvarcharMAXColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [varcharMAXColumn] = @varcharMAXColumn, [nvarcharMAXColumn] = @nvarcharMAXColumn";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Update.ToString()].Item1.varcharMAXColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Update.ToString()].Item1.nvarcharMAXColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}]";
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);
            }
        }

19 View Source File : Program.cs
License : MIT License
Project Creator : christiandelbianco

private static void DropAndCreateTable(string connectionString)
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = "DROP TABLE [dbo].[LoadTest]";
                    sqlCommand.ExecuteNonQuery();
                    sqlCommand.CommandText = "CREATE TABLE [LoadTest] ([Id] [int], [FirstName] nvarchar(50), [SecondName] nvarchar(50))";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : SqlCatalog.cs
License : MIT License
Project Creator : CslaGenFork

private DataTable GetConstraintInformation()
        {
            var table = new DataTable();
            var sb = new System.Text.StringBuilder();
            sb.Append("	select object_name(constid) CONSTRAINT_NAME, ");
            sb.Append(
                "	db_name() TABLE_CATALOG, SCHEMA_NAME(o1.uid) TABLE_SCHEMA, o1.name TABLE_NAME, c1.name COLUMN_NAME,");
            sb.Append(
                "	db_name() REF_TABLE_CATALOG, SCHEMA_NAME(o2.uid) REF_TABLE_SCHEMA, o2.name REF_TABLE_NAME, c2.name  REF_COLUMN_NAME");
            sb.Append("	from sysforeignkeys a");
            sb.Append("	INNER JOIN syscolumns c1");
            sb.Append("		ON a.fkeyid = c1.id");
            sb.Append("		AND a.fkey = c1.colid");
            sb.Append("	INNER JOIN syscolumns c2");
            sb.Append("		ON a.rkeyid = c2.id");
            sb.Append("		AND a.rkey = c2.colid");
            sb.Append("	INNER JOIN sysobjects o1");
            sb.Append("		ON c1.id = o1.id");
            sb.Append("	INNER JOIN sysobjects o2");
            sb.Append("		ON c2.id = o2.id");
            //sb.Append(" WHERE constid in (");
            //sb.Append("         SELECT object_id(CONSTRAINT_NAME)");
            //sb.Append("         FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS)");
            OpenConnection();
            try
            {
                var cn = (SqlConnection)Connection;
                using (var cmd = cn.CreateCommand())
                {
                    cmd.CommandText = sb.ToString();
                    cmd.CommandType = CommandType.Text;
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(table);
                    }
                }
            }
            finally
            {
                CloseConnection();
            }
            return table;
        }

19 View Source File : SqlCatalog.cs
License : MIT License
Project Creator : CslaGenFork

private DataTable GetColumnsDescriptionInformation()
        {
            var table = new DataTable();
            var sb = new System.Text.StringBuilder();
            sb.Append(" SELECT   s.name AS SchemaOwner,");
            sb.Append("          o.Name AS ObjectName,");
            sb.Append("          o.type AS ObjectType,");
            sb.Append("          c.name AS ColumnName,");
            sb.Append("          ep.value AS Description");
            sb.Append(" FROM     sys.objects o INNER JOIN sys.extended_properties ep");
            sb.Append("          ON o.object_id = ep.major_id");
            sb.Append("          INNER JOIN sys.schemas s");
            sb.Append("          ON o.schema_id = s.schema_id");
            sb.Append("          LEFT JOIN syscolumns c");
            sb.Append("          ON ep.minor_id = c.colid");
            sb.Append("          AND ep.major_id = c.id");
            sb.Append(" WHERE    (o.type ='U' AND c.name IS NOT NULL) OR ");
            sb.Append("          ((o.type ='U' OR o.type ='V' OR o.type ='P') AND c.name IS NULL AND ep.name LIKE '%Description')");
            sb.Append(" ORDER BY SchemaOwner, ObjectName, ObjectType, ColumnName");
            OpenConnection();
            try
            {
                var cn = (SqlConnection)Connection;
                using (var cmd = cn.CreateCommand())
                {
                    cmd.CommandText = sb.ToString();
                    cmd.CommandType = CommandType.Text;
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(table);
                    }
                }
            }
            finally
            {
                CloseConnection();
            }
            return table;
        }

19 View Source File : SqlCatalog.cs
License : MIT License
Project Creator : CslaGenFork

protected override DBStructure GetProcedureSchema(IDbConnection cn)
        {
            var ds = new DBStructure();
            var scn = (SqlConnection)cn;
            //.GetSchema("Procedures", new string[] { Catalog });
            var sb = new System.Text.StringBuilder();
            if (scn.ServerVersion.StartsWith("08"))
            {
                sb.AppendLine(Properties.Resources.SqlServerGetProcedures2000);
            }
            else
            {
                sb.AppendLine(Properties.Resources.SqlServerGetProcedures2005);
            }

            sb.AppendLine();
            sb.AppendLine(Properties.Resources.SqlServerGetParameters);
            using (SqlCommand cmd = scn.CreateCommand())
            {
                cmd.CommandText = sb.ToString();
                cmd.CommandType = CommandType.Text;
                using (var da = new SqlDataAdapter(cmd))
                {
                    da.TableMappings.Add("Table", "INFORMATION_SCHEMA_ROUTINES");
                    da.TableMappings.Add("Table1", "INFORMATION_SCHEMA_PARAMETERS");
                    ds.EnforceConstraints = false;
                    da.Fill(ds);
                }
            }
            return ds;
        }

19 View Source File : SqlStaticObjectBase.cs
License : MIT License
Project Creator : CslaGenFork

public override void Reload(bool throwOnError)
        {
            using (var cn = (SqlConnection)Catalog.CreateConnection())
            {
                using (var cmd = cn.CreateCommand())
                {
                    var sb = new StringBuilder();
                    sb.AppendLine(Properties.Resources.SqlServerGetColumn);
                    sb.AppendLine("WHERE");
                    if (!string.IsNullOrEmpty(ObjectCatalog))
                    {
                        sb.AppendLine("A.TABLE_CATALOG = @Catalog AND");
                        cmd.Parameters.AddWithValue("@Catalog", ObjectCatalog);
                    }
                    if (!string.IsNullOrEmpty(ObjectSchema))
                    {
                        sb.AppendLine("A.TABLE_SCHEMA = @Schema AND");
                        cmd.Parameters.AddWithValue("@Schema", ObjectSchema);
                    }
                    sb.Append("A.TABLE_NAME = @Name");
                    cmd.Parameters.AddWithValue("@Name", ObjectName);
                    cmd.CommandText = sb.ToString();
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var table = new DBStructure.INFORMATION_SCHEMA_COLUMNSDataTable();
                        try
                        {
                            da.Fill(table);
                        }
                        catch (Exception)
                        {
                            if (throwOnError)
                                throw;
                        }
                        LoadColumnInfo(table);
                    }
                }
            }
        }

19 View Source File : SqlStaticObjectBase.cs
License : MIT License
Project Creator : CslaGenFork

public override void Reload(bool throwOnError)
        {
            using (SqlConnection cn = (SqlConnection)Catalog.CreateConnection())
            {
                using (SqlCommand cmd = cn.CreateCommand())
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine(DBSchemaInfo.Properties.Resources.SqlServerGetColumn);
                    sb.AppendLine("WHERE");
                    if (!string.IsNullOrEmpty(this.ObjectCatalog))
                    {
                        sb.AppendLine("A.TABLE_CATALOG = @Catalog AND");
                        cmd.Parameters.AddWithValue("@Catalog", this.ObjectCatalog);
                    }
                    if (!string.IsNullOrEmpty(this.ObjectSchema))
                    {
                        sb.AppendLine("A.TABLE_SCHEMA = @Schema AND");
                        cmd.Parameters.AddWithValue("@Schema", this.ObjectSchema);
                    }
                    sb.Append("A.TABLE_NAME = @Name");
                    cmd.Parameters.AddWithValue("@Name", this.ObjectName);
                    cmd.CommandText = sb.ToString();
                    using (SqlDataAdapter da = new SqlDataAdapter (cmd))
                    {
                        DBStructure.INFORMATION_SCHEMA_COLUMNSDataTable table = new DBStructure.INFORMATION_SCHEMA_COLUMNSDataTable ();
                        try
                        {
                            da.Fill(table);
                        }
                        catch (Exception ex)
                        {
                            if (throwOnError)
                                throw ex;
                        }
                        LoadColumnInfo(table);
                    }
                }
            	
            }
        }

19 View Source File : SqlCatalog.cs
License : MIT License
Project Creator : CslaGenFork

protected override DBStructure GetProcedureSchema(IDbConnection cn)
        {
            DBStructure ds = new DBStructure();
            SqlConnection scn = (SqlConnection)cn;
            //.GetSchema("Procedures", new string[] { Catalog });
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            if (scn.ServerVersion.StartsWith("08"))
            {
                sb.AppendLine(DBSchemaInfo.Properties.Resources.SqlServerGetProcedures2000);
            }
            else
            {
                sb.AppendLine(DBSchemaInfo.Properties.Resources.SqlServerGetProcedures2005);
            }

            sb.AppendLine();
            sb.AppendLine(DBSchemaInfo.Properties.Resources.SqlServerGetParameters);
            using (SqlCommand cmd = scn.CreateCommand())
            {
                cmd.CommandText = sb.ToString();
                cmd.CommandType = CommandType.Text;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.TableMappings.Add("Table", "INFORMATION_SCHEMA_ROUTINES");
                    da.TableMappings.Add("Table1", "INFORMATION_SCHEMA_PARAMETERS");
                    ds.EnforceConstraints = false;
                    da.Fill(ds);
                }
            }
            return ds;
        }

19 View Source File : SqlCatalog.cs
License : MIT License
Project Creator : CslaGenFork

private DataTable GetConstraintInformation()
        {
            DataTable table = new DataTable();
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("	select object_name(constid) CONSTRAINT_NAME, ");
            sb.Append("	db_name() TABLE_CATALOG, user_name(o1.uid) TABLE_SCHEMA, o1.name TABLE_NAME, c1.name COLUMN_NAME,");
            sb.Append("	db_name() REF_TABLE_CATALOG, user_name(o2.uid) REF_TABLE_SCHEMA, o2.name REF_TABLE_NAME, c2.name  REF_COLUMN_NAME");
            sb.Append("	from sysforeignkeys a");
            sb.Append("	INNER JOIN syscolumns c1");
            sb.Append("		ON a.fkeyid = c1.id");
            sb.Append("		AND a.fkey = c1.colid");
            sb.Append("	INNER JOIN syscolumns c2");
            sb.Append("		ON a.rkeyid = c2.id");
            sb.Append("		AND a.rkey = c2.colid");
            sb.Append("	INNER JOIN sysobjects o1");
            sb.Append("		ON c1.id = o1.id");
            sb.Append("	INNER JOIN sysobjects o2");
            sb.Append("		ON c2.id = o2.id");
            //sb.Append(" WHERE constid in (");
            //sb.Append("         SELECT object_id(CONSTRAINT_NAME)");
            //sb.Append("         FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS)");
            base.OpenConnection();
            try
            {
                SqlConnection cn = (SqlConnection)Connection;
                using (SqlCommand cmd = cn.CreateCommand())
                {
                    cmd.CommandText = sb.ToString();
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {

                        da.Fill(table);
                    }
                }
            }
            finally
            {
                base.CloseConnection();
            }
            return table;
        }

19 View Source File : HealthCheckBuilderDataExtensions.cs
License : MIT License
Project Creator : dotnet-architecture

public static HealthCheckBuilder AddSqlCheck(this HealthCheckBuilder builder, string name, string connectionString)
        {
            builder.AddCheck($"SqlCheck({name})", async () =>
            {
                try
                {
                    //TODO: There is probably a much better way to do this.
                    using (var connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandType = CommandType.Text;
                            command.CommandText = "SELECT 1";
                            var result = (int)await command.ExecuteScalarAsync().ConfigureAwait(false);
                            if (result == 1)
                            {
                                return HealthCheckResult.Healthy($"SqlCheck({name}): Healthy");
                            }

                            return HealthCheckResult.Unhealthy($"SqlCheck({name}): Unhealthy");
                        }
                    }
                }
                catch(Exception ex)
                {
                    return HealthCheckResult.Unhealthy($"SqlCheck({name}): Exception during check: {ex.GetType().FullName}");
                }
            });

            return builder;
        }

19 View Source File : SqlDBFactory.cs
License : Apache License 2.0
Project Creator : Epi-Info

public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
        {
            try
            {
                SqlConnectionStringBuilder masterBuilder = new SqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
                masterBuilder.InitialCatalog = "Master";
                //masterBuilder.IntegratedSecurity = true;
                SqlConnection masterConnection = new SqlConnection(masterBuilder.ToString());
                IDbCommand command = masterConnection.CreateCommand();
                command.CommandText = string.Format("SELECT Count(*) FROM sysdatabases WHERE name='{0}'", dbInfo.DBName);
                masterConnection.Open();
                object result = command.ExecuteScalar();
                if ((int)result == 0)
                {
                    command.CommandText = "create database [" + dbInfo.DBName + "]";
                    //Logger.Log(command.CommandText);
                    command.ExecuteNonQuery();
                }
                masterConnection.Close();
                
            }
            catch (ApplicationException)
            {
                    throw new System.ApplicationException("Could not create new SQL database. Please contact your SQL server administrator.");
            }
            finally
            {
            }
        }

19 View Source File : SqlGateway.cs
License : MIT License
Project Creator : GoEddie

public void RunQuery(string query)
        {
            using (var con = new SqlConnection(_connectionString))
            {
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
            }
        }

19 View Source File : SqlGateway.cs
License : MIT License
Project Creator : GoEddie

public int GetInt(string query)
        {
            using (var con = new SqlConnection(_connectionString))
            {
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = query;
                    var result = cmd.ExecuteScalar();
                    return (int)result;
                }
            }
        }

19 View Source File : SqlConnectionExtensions.cs
License : MIT License
Project Creator : gordon-matt

public static IEnumerable<string> GetDatabaseNames(this SqlConnection connection)
        {
            const string CMD_SELECT_DATABASE_NAMES = "SELECT NAME FROM SYS.DATABASES ORDER BY NAME";
            var databaseNames = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = CMD_SELECT_DATABASE_NAMES;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        databaseNames.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return databaseNames;
        }

19 View Source File : SqlConnectionExtensions.cs
License : MIT License
Project Creator : gordon-matt

public static IEnumerable<string> GetTableNames(this SqlConnection connection, string databaseName, bool includeViews = false, string schema = "dbo")
        {
            var commandBuilder = new SqlCommandBuilder();

            string query;
            if (includeViews)
            {
                query =
$@"USE {commandBuilder.QuoteIdentifier(databaseName)};
SELECT [name]
FROM sys.Tables
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
UNION
SELECT [name]
FROM sys.Views
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
ORDER BY [name]";
            }
            else
            {
                query =
$@"USE {commandBuilder.QuoteIdentifier(databaseName)};
SELECT [name]
FROM sys.Tables
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
ORDER BY [name]";
            }

            var tables = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = query;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tables.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return tables;
        }

19 View Source File : SqlConnectionExtensions.cs
License : MIT License
Project Creator : gordon-matt

public static IEnumerable<string> GetViewNames(this SqlConnection connection, string databaseName, string schema = "dbo")
        {
            var commandBuilder = new SqlCommandBuilder();

            string query =
$@"USE {commandBuilder.QuoteIdentifier(databaseName)};
SELECT [name]
FROM sys.Views
WHERE [name] <> 'sysdiagrams'
AND SCHEMA_NAME([schema_id]) = @SchemaName
ORDER BY [name]";

            var views = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = query;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        views.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return views;
        }

19 View Source File : SqlConnectionExtensions.cs
License : MIT License
Project Creator : gordon-matt

public static ColumnInfoCollection GetColumnData(this SqlConnection connection, string tableName, string schema = "dbo")
        {
            const string CMD_COLUMN_INFO_FORMAT =
@"SELECT
    COLUMN_NAME,
    COLUMN_DEFAULT,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    ORDINAL_POSITION,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdenreplacedy') AS 'IsIdenreplacedy'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName";

            const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
WHERE CU.CONSTRAINT_NAME = T.Constraint_Name
AND CU.TABLE_NAME = T.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND CU.TABLE_NAME = @TableName
AND T.CONSTRAINT_SCHEMA = @SchemaName";

            var list = new ColumnInfoCollection();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            try
            {
                var foreignKeyColumns = connection.GetForeignKeyData(tableName, schema);

                if (!alreadyOpen)
                {
                    connection.Open();
                }

                using (var command = new SqlCommand(CMD_COLUMN_INFO_FORMAT, connection))
                {
                    command.CommandType = CommandType.Text;

                    command.Parameters.Add(new SqlParameter
                    {
                        Direction = ParameterDirection.Input,
                        DbType = DbType.String,
                        ParameterName = "@TableName",
                        Value = tableName
                    });

                    command.Parameters.Add(new SqlParameter
                    {
                        Direction = ParameterDirection.Input,
                        DbType = DbType.String,
                        ParameterName = "@SchemaName",
                        Value = schema
                    });

                    using (var reader = command.ExecuteReader())
                    {
                        ColumnInfo columnInfo = null;

                        while (reader.Read())
                        {
                            columnInfo = new ColumnInfo();

                            if (!reader.IsDBNull(0))
                            { columnInfo.ColumnName = reader.GetString(0); }

                            if (!reader.IsDBNull(1))
                            { columnInfo.DefaultValue = reader.GetString(1); }
                            else
                            { columnInfo.DefaultValue = string.Empty; }

                            if (foreignKeyColumns.Contains(columnInfo.ColumnName))
                            {
                                columnInfo.KeyType = KeyType.ForeignKey;
                            }

                            //else
                            //{
                            try
                            {
                                string type = reader.GetString(2);
                                columnInfo.DataTypeNative = type;
                                columnInfo.DataType = DataTypeConvertor.GetDbType(type.ToEnum<SqlDbType>(true));
                            }
                            catch (ArgumentNullException)
                            {
                                columnInfo.DataType = DbType.Object;
                            }
                            catch (ArgumentException)
                            {
                                columnInfo.DataType = DbType.Object;
                            }

                            //}

                            if (!reader.IsDBNull(3))
                            { columnInfo.MaximumLength = reader.GetInt32(3); }

                            if (!reader.IsDBNull(4))
                            {
                                if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
                                { columnInfo.IsNullable = false; }
                                else
                                { columnInfo.IsNullable = true; }
                            }

                            if (!reader.IsDBNull(5))
                            { columnInfo.OrdinalPosition = reader.GetInt32(5); }

                            if (!reader.IsDBNull(6))
                            { columnInfo.Precision = reader.GetByte(6); }

                            if (!reader.IsDBNull(7))
                            { columnInfo.Scale = reader.GetInt32(7); }

                            if (!reader.IsDBNull(8))
                            { columnInfo.IsAutoIncremented = reader.GetInt32(8) == 1 ? true : false; }

                            list.Add(columnInfo);
                        }
                    }
                }
            }
            finally
            {
                if (!alreadyOpen && connection.State != ConnectionState.Closed)
                { connection.Close(); }
            }

            #region Primary Keys

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = CMD_IS_PRIMARY_KEY_FORMAT;

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@TableName",
                    Value = tableName
                });

                command.Parameters.Add(new SqlParameter
                {
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    ParameterName = "@SchemaName",
                    Value = schema
                });

                alreadyOpen = (connection.State != ConnectionState.Closed);

                if (!alreadyOpen)
                {
                    connection.Open();
                }

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string pkColumn = reader.GetString(0);
                        ColumnInfo match = list[pkColumn];
                        if (match != null)
                        {
                            match.KeyType = KeyType.PrimaryKey;
                        }
                    }
                }

                if (!alreadyOpen)
                {
                    connection.Close();
                }
            }

            #endregion Primary Keys

            return list;
        }

19 View Source File : SqlConnectionExtensions.cs
License : MIT License
Project Creator : gordon-matt

public static IEnumerable<string> GetSchemaNames(this SqlConnection connection)
        {
            const string CMD_SELECT_SCHEMA_NAMES =
@"SELECT s.[name] as [Name]
FROM sys.schemas S
WHERE [schema_id] < 1000
ORDER BY S.[name]";

            var schemaNames = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = CMD_SELECT_SCHEMA_NAMES;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        schemaNames.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return schemaNames;
        }

19 View Source File : DbLockedCriticalSection.cs
License : MIT License
Project Creator : havit

private SqlCommand ReleaseLock_PrepareCommand(string lockValue, SqlConnection sqlConnection, out SqlParameter resultCodeSqlParameter)
        {
            SqlParameter lockedResourceSqlParameter = new SqlParameter("Resource", lockValue);
            SqlParameter lockOwnerSqlParameter = new SqlParameter("LockOwner", "Session");
            resultCodeSqlParameter = new SqlParameter("@ResultCode", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };

            SqlCommand command = sqlConnection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "EXEC @ResultCode = sp_releaseapplock @Resource, @LockOwner";
            command.Parameters.Add(lockedResourceSqlParameter);
            command.Parameters.Add(lockOwnerSqlParameter);
            command.Parameters.Add(resultCodeSqlParameter);
            command.CommandTimeout = options.SqlCommandTimeoutSeconds;

            return command;
        }

19 View Source File : DbLockedCriticalSection.cs
License : MIT License
Project Creator : havit

private SqlCommand GetLock_PrepareCommand(string lockValue, SqlConnection sqlConnection, out SqlParameter resultCodeSqlParameter)
        {
            SqlParameter lockedResourceSqlParameter = new SqlParameter("@Resource", lockValue);
            SqlParameter lockModeSqlParameter = new SqlParameter("@LockMode", "Exclusive"); // Exclusive - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
            SqlParameter lockOwnerSqlParameter = new SqlParameter("@LockOwner", "Session");
            SqlParameter lockTimeoutSqlParameter = new SqlParameter("@LockTimeout", options.LockTimeoutMs);            
            resultCodeSqlParameter = new SqlParameter("@ResultCode", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };

            SqlCommand command = sqlConnection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "EXEC @ResultCode = sp_getapplock @Resource, @LockMode, @LockOwner, @LockTimeout";
            command.Parameters.Add(lockedResourceSqlParameter);
            command.Parameters.Add(lockModeSqlParameter);
            command.Parameters.Add(lockOwnerSqlParameter);
            command.Parameters.Add(lockTimeoutSqlParameter);
            command.Parameters.Add(resultCodeSqlParameter);
            command.CommandTimeout = options.SqlCommandTimeoutSeconds;

            return command;
        }

19 View Source File : ReportingServiceTest.cs
License : MIT License
Project Creator : HotcakesCommerce

private void ExecuteSql(string sql)
        {
            var conString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
            var conStringBuilder = new SqlConnectionStringBuilder(conString);
            conStringBuilder.InitialCatalog = string.Empty;
            using (var connection = new SqlConnection(conStringBuilder.ConnectionString))
            {
                connection.Open();

                var sqlCommand = connection.CreateCommand();
                sqlCommand.CommandText = sql;
                sqlCommand.ExecuteNonQuery();
            }
        }

19 View Source File : RegisterMappingTypeDemo.cs
License : MIT License
Project Creator : JackQChen

public IDbCommand CreateCommand()
        {
            return new SqlServerCommand(this._dbConnection.CreateCommand());
        }

19 View Source File : ResultColumns.cs
License : MIT License
Project Creator : kiler398

override internal void LoadAll()
		{
			try
			{
				string schema = "";

				if(-1 == this.Procedure.Schema.IndexOf("."))
				{
					schema = this.Procedure.Schema + ".";
				}

                //SET FMTONLY ON 
				string select = "EXEC [" + this.Procedure.Database.Name + "]." + schema + "[" +
					this.Procedure.Name + "] ";

				int paramCount = this.Procedure.Parameters.Count;

				if(paramCount > 0)
				{
					IParameters parameters = this.Procedure.Parameters;
					IParameter param = null;

					int c = parameters.Count;

					for(int i = 0; i < c; i++)
					{
						param = parameters[i];

						if(param.Direction == ParamDirection.ReturnValue)
						{
							paramCount--;
						}
					}
				}

				for(int i = 0; i < paramCount; i++)
				{
					if(i > 0) 
					{
						select += ",";
					}

					select += "null";
				}

				DataTable metaData = new DataTable();

				try
				{
                    //Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=localhost
                    //Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Preplacedword=myPreplacedword;
                    //Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPreplacedword;
                    //Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Preplacedword=myPreplacedword;

					string[] pairs = dbRoot.ConnectionString.Split(';');
                    Hashtable conn = new Hashtable();
                    int idx;
                    string name, val;
                    foreach (string pairstr in pairs)
                    {
                        idx = pairstr.IndexOf('=');
                        if (idx > 0)
                        {
                            name = pairstr.Substring(0, idx);
                            val = pairstr.Substring(idx + 1);
                            conn[name.Trim()] = val.Trim();
                        }
                    }

					string cn = "", tmp;
					foreach(string key in conn.Keys)
                    {
                        tmp = conn[key] as string;
						switch(key.ToLower())
						{
							case "provider":
								break;
							case "extended properties":
                                break;
                            case "server":
                            case "data source":
                                cn += "Data Source=" + tmp + ";";
                                break;
                            case "user id":
                            case "uid":
                                cn += "User ID=" + tmp + ";";
                                break;
                            case "preplacedword":
                            case "pwd":
                                cn += "Preplacedword=" + tmp + ";"; 
                                break;
                            case "initial catalog":
                            case "database":
                                cn += "Initial Catalog=" + tmp + ";";
                                break;
                            case "marsconn":
                                if (tmp.ToLower() == "yes")
                                {
                                    cn += "MultipleActiveResultSets=" + ((tmp.ToLower() == "yes") ? "true" : "false") + ";";
                                }
                                break;
							default:
                                cn += key + "=" + tmp + ";"; 
								break;
						}
					}
                    SqlConnection sqlconn = new SqlConnection(cn);
                    sqlconn.Open();
                    SqlCommand sqlcmd = sqlconn.CreateCommand(); 
                    sqlcmd.CommandText = select;
                    sqlcmd.CommandType = CommandType.Text;
                    SqlDataReader reader = sqlcmd.ExecuteReader(CommandBehavior.SchemaOnly);

                    metaData = reader.GetSchemaTable();
                    SqlResultColumn resultColumn;
                    foreach (DataRow row in metaData.Rows)
                    {
                        resultColumn = this.dbRoot.ClreplacedFactory.CreateResultColumn() as Sql.SqlResultColumn;
                        resultColumn.dbRoot = this.dbRoot;
                        resultColumn.ResultColumns = this;
                        resultColumn._row = row;
                        this._array.Add(resultColumn);
                    }
				}
				catch
                {
                   //
				}

			}
			catch {}
		}

19 View Source File : ResultColumns.cs
License : MIT License
Project Creator : kiler398

override internal void LoadAll()
		{
			try
			{
				string schema = "";

				if(-1 == this.Procedure.Schema.IndexOf("."))
				{
					schema = this.Procedure.Schema + ".";
				}

                //SET FMTONLY ON 
				string select = "EXEC [" + this.Procedure.Database.Name + "]." + schema + "[" +
					this.Procedure.Name + "] ";

				int paramCount = this.Procedure.Parameters.Count;

				if(paramCount > 0)
				{
					IParameters parameters = this.Procedure.Parameters;
					IParameter param = null;

					int c = parameters.Count;

					for(int i = 0; i < c; i++)
					{
						param = parameters[i];

						if(param.Direction == ParamDirection.ReturnValue)
						{
							paramCount--;
						}
					}
				}

				for(int i = 0; i < paramCount; i++)
				{
					if(i > 0) 
					{
						select += ",";
					}

					select += "null";
				}

				DataTable metaData = new DataTable();

				try
				{
                    //Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=localhost
                    //Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Preplacedword=myPreplacedword;
                    //Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPreplacedword;
                    //Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Preplacedword=myPreplacedword;

					string[] pairs = dbRoot.ConnectionString.Split(';');
                    Hashtable conn = new Hashtable();
                    int idx;
                    string name, val;
                    foreach (string pairstr in pairs)
                    {
                        idx = pairstr.IndexOf('=');
                        if (idx > 0)
                        {
                            name = pairstr.Substring(0, idx);
                            val = pairstr.Substring(idx + 1);
                            conn[name.Trim()] = val.Trim();
                        }
                    }

					string cn = "", tmp;
					foreach(string key in conn.Keys)
                    {
                        tmp = conn[key] as string;
						switch(key.ToLower())
						{
							case "provider":
								break;
							case "extended properties":
                                break;
                            case "server":
                            case "data source":
                                cn += "Data Source=" + tmp + ";";
                                break;
                            case "user id":
                            case "uid":
                                cn += "User ID=" + tmp + ";";
                                break;
                            case "preplacedword":
                            case "pwd":
                                cn += "Preplacedword=" + tmp + ";"; 
                                break;
                            case "initial catalog":
                            case "database":
                                cn += "Initial Catalog=" + tmp + ";";
                                break;
                            case "marsconn":
                                if (tmp.ToLower() == "yes")
                                {
                                    cn += "MultipleActiveResultSets=" + ((tmp.ToLower() == "yes") ? "true" : "false") + ";";
                                }
                                break;
							default:
                                cn += key + "=" + tmp + ";"; 
								break;
						}
					}
                    SqlConnection sqlconn = new SqlConnection(cn);
                    sqlconn.Open();
                    SqlCommand sqlcmd = sqlconn.CreateCommand(); 
                    sqlcmd.CommandText = select;
                    sqlcmd.CommandType = CommandType.Text;
                    SqlDataReader reader = sqlcmd.ExecuteReader(CommandBehavior.SchemaOnly);

                    metaData = reader.GetSchemaTable();
                    SqlResultColumn resultColumn;
                    foreach (DataRow row in metaData.Rows)
                    {
                        resultColumn = this.dbRoot.ClreplacedFactory.CreateResultColumn() as Sql.SqlResultColumn;
                        resultColumn.dbRoot = this.dbRoot;
                        resultColumn.ResultColumns = this;
                        resultColumn._row = row;
                        this._array.Add(resultColumn);
                    }
				}
				catch
                {
                   //
				}

			}
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
        }

19 View Source File : TranslationStorageManager.cs
License : GNU General Public License v2.0
Project Creator : lbonifacius

private static void ExecuteSQLFile(SqlTransaction transaction, string manifestName)
        {
            string sql;

            using (Stream strm = replacedembly.GetExecutingreplacedembly().GetManifestResourceStream(manifestName))
            {
                var reader = new StreamReader(strm);
                sql = reader.ReadToEnd();
            }

            var regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            string[] lines = regex.Split(sql);

            foreach (string line in lines)
            {
                if (!String.IsNullOrEmpty(line))
                {
                    var command = transaction.Connection.CreateCommand();
                    command.CommandText = line;
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                }
            }
        }

19 View Source File : SqlNode.cs
License : MIT License
Project Creator : MarkMpn

public DataTable Execute(IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues)
        {
            _executionCount++;
            var startTime = DateTime.Now;

            try
            {
                if (!dataSources.TryGetValue(DataSource, out var dataSource))
                    throw new QueryExecutionException("Missing datasource " + DataSource);

                if (options.UseLocalTimeZone)
                    throw new QueryExecutionException("Cannot use automatic local time zone conversion with the TDS Endpoint");

                if (!(dataSource.Connection is CrmServiceClient svc))
                    throw new QueryExecutionException($"IOrganizationService implementation needs to be CrmServiceClient for use with the TDS Endpoint, got {dataSource.Connection.GetType()}");

                if (svc.CallerId != Guid.Empty)
                    throw new QueryExecutionException("Cannot use impersonation with the TDS Endpoint");

                using (var con = new SqlConnection("server=" + svc.CrmConnectOrgUriActual.Host))
                {
                    con.AccessToken = svc.CurrentAccessToken;
                    con.Open();

                    using (var cmd = con.CreateCommand())
                    {
                        cmd.CommandTimeout = (int)TimeSpan.FromMinutes(2).TotalSeconds;
                        cmd.CommandText = Sql;
                        var result = new DataTable();

                        using (var adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(result);
                        }

                        var columnSqlTypes = result.Columns.Cast<DataColumn>().Select(col => SqlTypeConverter.NetToSqlType(col.DataType)).ToArray();
                        var columnNullValues = columnSqlTypes.Select(type => SqlTypeConverter.GetNullValue(type)).ToArray();

                        // Values will be stored as BCL types, convert them to SqlXxx types for consistency with IDataExecutionPlanNodes
                        var sqlTable = new DataTable();

                        for (var i = 0; i < result.Columns.Count; i++)
                            sqlTable.Columns.Add(result.Columns[i].ColumnName, columnSqlTypes[i]);

                        foreach (DataRow row in result.Rows)
                        {
                            var sqlRow = sqlTable.Rows.Add();

                            for (var i = 0; i < result.Columns.Count; i++)
                            {
                                var sqlValue = DBNull.Value.Equals(row[i]) ? columnNullValues[i] : SqlTypeConverter.NetToSqlType(row[i]);
                                sqlRow[i] = sqlValue;
                            }
                        }

                        return sqlTable;
                    }
                }
            }
            catch (QueryExecutionException ex)
            {
                if (ex.Node == null)
                    ex.Node = this;

                throw;
            }
            catch (Exception ex)
            {
                throw new QueryExecutionException(ex.Message, ex)
                {
                    Node = this
                };
            }
            finally
            {
                var endTime = DateTime.Now;
                _duration += (endTime - startTime);
            }
        }

19 View Source File : SampleTests.cs
License : MIT License
Project Creator : MarkPflug

[Fact(Skip = "Usage example.")]
		public void SqlTVPSimple2()
		{
			using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset

			var conn = GetConnection();
			conn.Open();

			var data =
				Enumerable
				.Range(0, 10)
				.Select(i => new { Id = i, Name = "name " + i, Code = (i % 2 == 1) ? "" : "OR" });

			var dataReader = ObjectDataReader.Create(data);

			using var cmd = conn.CreateCommand();
			cmd.CommandText = "InsertSimple2";
			cmd.CommandType = CommandType.StoredProcedure;
			var param = new SqlParameter()
			{
				ParameterName = "data",
				SqlDbType = SqlDbType.Structured
			};

			var paramData = dataReader;
			param.Value = paramData;
			cmd.Parameters.Add(param);
			cmd.ExecuteNonQuery();
		}

19 View Source File : SampleTests.cs
License : MIT License
Project Creator : MarkPflug

static Schema GetSchema(SqlConnection conn, string table)
		{
			using var cmd = conn.CreateCommand();
			cmd.CommandText = "select top 0 * from " + table;
			using var reader = cmd.ExecuteReader();
			var tableSchema = reader.GetColumnSchema();
			return new Schema(tableSchema);
		}

19 View Source File : SampleTests.cs
License : MIT License
Project Creator : MarkPflug

[Fact(Skip = "Usage example.")]
		public void SqlTVPSimple2Csv()
		{
			using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset

			var conn = GetConnection();
			conn.Open();

			var schema = GetSchema(conn, "Simple2");
			var reader = File.OpenText("Data/Simple2Data.csv");
			var dataReader = CsvDataReader.Create(reader, new CsvDataReaderOptions { Schema = schema });

			using var cmd = conn.CreateCommand();
			cmd.CommandText = "InsertSimple2";
			cmd.CommandType = CommandType.StoredProcedure;
			var param = new SqlParameter()
			{
				ParameterName = "data",
				SqlDbType = SqlDbType.Structured
			};

			var paramData = dataReader;
			param.Value = paramData;
			cmd.Parameters.Add(param);
			cmd.ExecuteNonQuery();
		}

19 View Source File : SampleTests.cs
License : MIT License
Project Creator : MarkPflug

[Fact(Skip = "Usage example.")]
		public void SqlTVPSimple1()
		{
			using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset

			var conn = GetConnection();
			conn.Open();

			var data =
				Enumerable
				.Range(0, 10)
				.Select(i => new { Id = i, Name = "name " + i });

			var dataReader = ObjectDataReader.Create(data);

			using var cmd = conn.CreateCommand();
			cmd.CommandText = "InsertSimple1";
			cmd.CommandType = CommandType.StoredProcedure;
			var param = new SqlParameter()
			{
				ParameterName = "data",
				SqlDbType = SqlDbType.Structured
			};

			var paramData = dataReader;
			param.Value = paramData;
			cmd.Parameters.Add(param);
			cmd.ExecuteNonQuery();
		}

19 View Source File : Common.cs
License : GNU Lesser General Public License v3.0
Project Creator : NaverCloudPlatform

public static bool QueryExecuter(string connectionString, string listStringQuerys, int commandTimeout = 5)
        {
            bool bReturn = false;
            List<string> querys = ReadQuery(listStringQuerys);
            foreach (var query in querys)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = query;
                            cmd.CommandTimeout = commandTimeout;
                            cmd.ExecuteNonQuery();
                        }
                        conn.Close();
                        bReturn = true;
                    }
                }
                catch(Exception) { throw; }
            }
            return bReturn;
        }

19 View Source File : MainWorker.cs
License : GNU Lesser General Public License v3.0
Project Creator : NaverCloudPlatform

private bool QueryExecuter(string listStringQuery, int commandTimeout = 30)
        {
            bool bReturn = false;
            sbResultAll.Clear();
            List<string> querys = TranString.ReadQuery(listStringQuery);
            startTime = DateTime.Now;
            
            foreach (var query in querys)
            {
                try
                {
                    if (CancelRequested)
                        break;

                    sbResultAll.Append("-->>---------------------------------");
                    sbResultAll.Append(query + Environment.NewLine);
                    sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);
                    if (query.Trim().ToUpper().StartsWith("USE"))
                    {
                        string[] database = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None);
                        ConnectionString = SetConnectionString(database[1]);

                    }
                    string result = string.Empty;


                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        conn.Open();
                        conn.InfoMessage += Conn_InfoMessage; // message hook (like backup message) 


                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.StatementCompleted += Cmd_StatementCompleted; // retrive row count

                            cmd.CommandType = CommandType.Text;

                            //// execute only estimation plan text
                            //cmd.CommandText = "SET SHOWPLAN_ALL ON";
                            //cmd.ExecuteNonQuery();

                            //// execute only estimation plan xml
                            //cmd.CommandText = "SET SHOWPLAN_XML ON";
                            //cmd.ExecuteNonQuery();
                                                       
                            cmd.CommandText = query;
                            SqlDataReader reader = cmd.ExecuteReader();

                            do
                            {
                                StringBuilder sb = new StringBuilder();
                                string Header = string.Empty;
                                string Line = string.Empty;
                                DataTable schemaTable = reader.GetSchemaTable();
                                if (schemaTable != null)
                                {
                                    try
                                    {
                                        foreach (DataRow row in schemaTable.Rows)
                                        {
                                            foreach (DataColumn column in schemaTable.Columns)
                                            {
                                                if (column.ColumnName == "ColumnName")
                                                {
                                                    Header = Header + row[column] + ColumnDelimiter;
                                                }
                                            }
                                        }
                                        Header = Header + Environment.NewLine;
//                                        Line = Line + Environment.NewLine;
                                        sb.Append(Header);
                                        sb.Append(Line);

                                        while (reader.Read())
                                        {
                                            for (int i = 0; i < reader.FieldCount; i++)
                                            {
                                                if (reader.GetValue(i).ToString() == "System.Byte[]")
                                                    sb.Append("0x" + BitConverter.ToString((byte[])reader.GetValue(i)).Replace("-", ""));
                                                else
                                                    sb.Append(reader.GetValue(i).ToString());
                                                sb.Append(ColumnDelimiter);
                                            }
                                            sb.Append(Environment.NewLine);
                                        }

                                    }
                                    catch (SqlException ex)
                                    {
                                        ErrorCnt++;
                                        sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                                        sbResultAll.Append("--SQL Exception" + Environment.NewLine);
                                        sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                                        for (int i = 0; i < ex.Errors.Count; i++)
                                        {
                                            sbResultAll.Append("Inner SqlException No #" + i + Environment.NewLine +
                                            "Message: " + ex.Errors[i].Message + Environment.NewLine +
                                            "Source: " + ex.Errors[i].Source + Environment.NewLine +
                                            "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine);
                                        }
                                    }
                                    finally
                                    {
                                        sb.Append(Environment.NewLine);
                                        sbResultAll.Append(sb);
                                        sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows"));
                                    }
                                }
                                else
                                {
                                    string[] Query = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None);
                                    if (
                                        Query[0].Equals("update", StringComparison.OrdinalIgnoreCase)
                                        || Query[0].Equals("insert", StringComparison.OrdinalIgnoreCase)
                                        || Query[0].Equals("delete", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("update", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("insert", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("delete", StringComparison.OrdinalIgnoreCase)
                                        )
                                        sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows"));
                                    else
                                        sbResultAll.Append(string.Format("Commands completed successfully." + Environment.NewLine + Environment.NewLine));
                                }

                            } while (reader.NextResult());
                        }
                        conn.Close();
                        bReturn = true;
                    }
                }

                catch (SqlException ex)
                {
                    ErrorCnt++;
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append("--SQL Exception" + Environment.NewLine);
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);

                    for (int i = 0; i < ex.Errors.Count; i++)
                    {
                        sbResultAll.Append("SqlException No #" + i + Environment.NewLine +
                        "Message: " + ex.Errors[i].Message + Environment.NewLine +
                        "Source: " + ex.Errors[i].Source + Environment.NewLine +
                        "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine);
                    }

                    sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);

                    bReturn = false;
                }
                catch (Exception ex)
                {
                    ErrorCnt++;
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append("--Exception" + Environment.NewLine);
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append(ex.Message);
                    sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);
                    bReturn = false;
                }
            }

            //sbResultAll.Append(Environment.NewLine + "-->>---------------------------------" + Environment.NewLine);
            endTime = DateTime.Now;
            //sbResultAll.Append("EndTime : " + endTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + Environment.NewLine);
            sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);
            sbResultAll.Append(Environment.NewLine + "-->>---------------------------------" + Environment.NewLine);
            sbResultAll.Append("StartTime : " + startTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + Environment.NewLine);
            sbResultAll.Append("EndTime : " + endTime.ToString("yyyy-MM-dd HH:mm:ss.fff") + Environment.NewLine);
            TimeSpan diff = endTime - startTime;
            string formatted = string.Format(
                                   "TotalExecutionTime : {0} days, {1} hours, {2} minutes, {3} seconds, {4} miliseconds",
                                   diff.Days,
                                   diff.Hours,
                                   diff.Minutes,
                                   diff.Seconds,
                                   diff.Milliseconds
                                   );
            sbResultAll.Append(formatted + Environment.NewLine);
            sbResultAll.Append("---------------------------------<<--" + Environment.NewLine + Environment.NewLine);

            return bReturn;
        }

See More Examples