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 : CultureInfoTest.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('{TableName1}', 'U') IS NOT NULL DROP TABLE [{TableName1}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName2}', 'U') IS NOT NULL DROP TABLE [{TableName2}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : DataAnnotationNotMappedTest2.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('DataAnnotationNotMappedTest2Model', 'U') IS NOT NULL DROP TABLE [DataAnnotationNotMappedTest2Model];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : CancellationTokenTest.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();
                }
            }
        }

19 View Source File : BigIntDecimalAndFloatTypesTest.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<BigIntDecimalAndFloatTypesTestSqlServerModel, BigIntDecimalAndFloatTypesTestSqlServerModel>(new BigIntDecimalAndFloatTypesTestSqlServerModel { BigintColumn = 123, Decimal18Column = 987654321, Decimal54Column = null, FloatColumn = null }, new BigIntDecimalAndFloatTypesTestSqlServerModel()));
            _checkValues.Add(ChangeType.Update.ToString(), new Tuple<BigIntDecimalAndFloatTypesTestSqlServerModel, BigIntDecimalAndFloatTypesTestSqlServerModel>(new BigIntDecimalAndFloatTypesTestSqlServerModel { BigintColumn = null, Decimal18Column = null, Decimal54Column = 6.77M, FloatColumn = 7.55F }, new BigIntDecimalAndFloatTypesTestSqlServerModel()));
            _checkValues.Add(ChangeType.Delete.ToString(), new Tuple<BigIntDecimalAndFloatTypesTestSqlServerModel, BigIntDecimalAndFloatTypesTestSqlServerModel>(new BigIntDecimalAndFloatTypesTestSqlServerModel { BigintColumn = null, Decimal18Column = null, Decimal54Column = 6.77M, FloatColumn = 7.55F }, new BigIntDecimalAndFloatTypesTestSqlServerModel()));

            _checkValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<BigIntDecimalAndFloatTypesTestSqlServerModel, BigIntDecimalAndFloatTypesTestSqlServerModel>(new BigIntDecimalAndFloatTypesTestSqlServerModel { BigintColumn = 123, Decimal18Column = 987654321, Decimal54Column = null, FloatColumn = null }, new BigIntDecimalAndFloatTypesTestSqlServerModel()));
            _checkValuesOld.Add(ChangeType.Update.ToString(), new Tuple<BigIntDecimalAndFloatTypesTestSqlServerModel, BigIntDecimalAndFloatTypesTestSqlServerModel>(new BigIntDecimalAndFloatTypesTestSqlServerModel { BigintColumn = null, Decimal18Column = null, Decimal54Column = 6.77M, FloatColumn = 7.55F }, new BigIntDecimalAndFloatTypesTestSqlServerModel()));
            _checkValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<BigIntDecimalAndFloatTypesTestSqlServerModel, BigIntDecimalAndFloatTypesTestSqlServerModel>(new BigIntDecimalAndFloatTypesTestSqlServerModel { BigintColumn = null, Decimal18Column = null, Decimal54Column = 6.77M, FloatColumn = 7.55F }, new BigIntDecimalAndFloatTypesTestSqlServerModel()));
        }

19 View Source File : DataAnnotationTest01.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.CommandText = $"INSERT INTO [{TableName}] ([Name], [Long Description]) VALUES ('{_checkValues[ChangeType.Insert.ToString()].Item1.Name}', '{_checkValues[ChangeType.Insert.ToString()].Item1.Description}')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [Name] = '{_checkValues[ChangeType.Update.ToString()].Item1.Name}', [Long Description] = '{_checkValues[ChangeType.Update.ToString()].Item1.Description}'";
                    sqlCommand.ExecuteNonQuery();
                }

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

19 View Source File : MsSqlDbHelper.cs
License : MIT License
Project Creator : chi8708

public override DataTable GetDataTable(string commandText, params IDataParameter[] parms)
        {
            using (SqlConnection connection = new SqlConnection(Config.ConnectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                command.Parameters.AddRange(parms);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
        }

19 View Source File : DataAnnotationNotMappedTest1.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('DataAnnotationNotMappedTest1Model', 'U') IS NOT NULL DROP TABLE [DataAnnotationNotMappedTest1Model];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : MsSqlDataStoreBase.cs
License : MIT License
Project Creator : azist

public void TestConnection()
    {
      try
      {
        using (var cnn = GetConnection().GetAwaiter().GetResult())
        {
          var cmd = cnn.CreateCommand();
          cmd.CommandType = System.Data.CommandType.Text;
          cmd.CommandText = "SELECT 1+1 from DUAL";
          if (cmd.ExecuteScalar().ToString() != "2")
            throw new MsSqlDataAccessException(StringConsts.SQL_STATEMENT_FAILED_ERROR);
        }
      }
      catch (Exception error)
      {
        throw new MsSqlDataAccessException(string.Format(StringConsts.CONNECTION_TEST_FAILED_ERROR, error.Message), error);
      }
    }

19 View Source File : DataAnnotationTest01.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<DataAnnotationTestSqlServer1Model, DataAnnotationTestSqlServer1Model>(new DataAnnotationTestSqlServer1Model { Name = "Christian", Description = "Del Bianco" }, new DataAnnotationTestSqlServer1Model()));
            _checkValues.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationTestSqlServer1Model, DataAnnotationTestSqlServer1Model>(new DataAnnotationTestSqlServer1Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer1Model()));
            _checkValues.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationTestSqlServer1Model, DataAnnotationTestSqlServer1Model>(new DataAnnotationTestSqlServer1Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer1Model()));

            _checkValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<DataAnnotationTestSqlServer1Model, DataAnnotationTestSqlServer1Model>(new DataAnnotationTestSqlServer1Model { Name = "Christian", Description = "Del Bianco" }, new DataAnnotationTestSqlServer1Model()));
            _checkValuesOld.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationTestSqlServer1Model, DataAnnotationTestSqlServer1Model>(new DataAnnotationTestSqlServer1Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer1Model()));
            _checkValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationTestSqlServer1Model, DataAnnotationTestSqlServer1Model>(new DataAnnotationTestSqlServer1Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer1Model()));

        }

19 View Source File : CRUDGenerator.cs
License : MIT License
Project Creator : azist

private static async Task<int> crudUpsert(MsSqlDataStoreBase store, SqlConnection cnn, SqlTransaction trans, Doc doc, FieldFilterFunc filter)
    {
      var target = store.TargetName;
      var cnames = new StringBuilder();
      var values = new StringBuilder();
      var upserts = new StringBuilder();
      var vparams = new List<SqlParameter>();
      var vpidx = 0;
      foreach (var fld in doc.Schema.FieldDefs)
      {
        var fattr = fld[target];
        if (fattr==null) continue;

        if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore) continue;


        if (filter!=null)//20160210 Dkh+SPol
        {
          if (!filter(doc, null, fld)) continue;
        }

        var fname = fld.GetBackendNameForTarget(target);

        fname = store.AdjustObjectNameCasing( fname );

        var converted = getDbFieldValue(doc, fld, fattr, store);


        cnames.AppendFormat(" [{0}],", fname);

        if ( converted.value != null)
        {
                var pname = string.Format("@VAL{0}", vpidx);

                values.AppendFormat(" {0},", pname);

                if (!fattr.Key)
                    upserts.AppendFormat(" [{0}] = {1},", fname, pname);

                var par = new SqlParameter();
                par.ParameterName = pname;
                par.Value = converted;
                if (converted.dbType.HasValue) par.SqlDbType = converted.dbType.Value;
                vparams.Add(par);

                vpidx++;
        }
        else
        {
                values.Append(" NULL,");
                upserts.AppendFormat(" [{0}] = NULL,", fname);
        }
      }//foreach

      if (cnames.Length > 0 && upserts.Length > 0)
      {
        cnames.Remove(cnames.Length - 1, 1);// remove ","
        upserts.Remove(upserts.Length - 1, 1);// remove ","
        values.Remove(values.Length - 1, 1);// remove ","
      }
      else
        return 0;//nothing to do


      string tableName = store.AdjustObjectNameCasing( getTableName(doc.Schema, target) );

      using(var cmd = cnn.CreateCommand())
      {
        var sql =
        @"INSERT INTO [{0}] ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE {3}".Args( tableName, cnames, values, upserts);

        cmd.Transaction = trans;
        cmd.CommandText = sql;
        cmd.Parameters.AddRange(vparams.ToArray());
     //   ConvertParameters(store, cmd.Parameters);

        try
        {
            var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            GeneratorUtils.LogCommand(store, "upsert-ok", cmd, null);
            return affected;
        }
        catch(Exception error)
        {
            GeneratorUtils.LogCommand(store, "upsert-error", cmd, error);
            throw;
        }
      }//using command
    }

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

protected int CountConversationEndpoints(string naming = null)
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForSa))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = "select COUNT(*) from sys.conversation_endpoints WITH (NOLOCK)" + (string.IsNullOrWhiteSpace(naming) ? ";" : $" WHERE [far_service] = '{naming}_Receiver';");
                    return (int)sqlCommand.ExecuteScalar();
                }
            }
        }

19 View Source File : DataAnnotationTest02.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<DataAnnotationTestSqlServer2Model, DataAnnotationTestSqlServer2Model>(new DataAnnotationTestSqlServer2Model { Name = "Christian", Description = "Del Bianco" }, new DataAnnotationTestSqlServer2Model()));
            _checkValues.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationTestSqlServer2Model, DataAnnotationTestSqlServer2Model>(new DataAnnotationTestSqlServer2Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer2Model()));
            _checkValues.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationTestSqlServer2Model, DataAnnotationTestSqlServer2Model>(new DataAnnotationTestSqlServer2Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer2Model()));

            _checkValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<DataAnnotationTestSqlServer2Model, DataAnnotationTestSqlServer2Model>(new DataAnnotationTestSqlServer2Model { Name = "Christian", Description = "Del Bianco" }, new DataAnnotationTestSqlServer2Model()));
            _checkValuesOld.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationTestSqlServer2Model, DataAnnotationTestSqlServer2Model>(new DataAnnotationTestSqlServer2Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer2Model()));
            _checkValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationTestSqlServer2Model, DataAnnotationTestSqlServer2Model>(new DataAnnotationTestSqlServer2Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer2Model()));
        }

19 View Source File : DbLoggingTest.cs
License : MIT License
Project Creator : capslocky

private static int LogRequest(string origin, string operation, string request, string location)
      {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
          connection.Open();

          using (var command = connection.CreateCommand())
          {
            command.CommandText = "insert into [Requests] (Origin, Operation, Location, Request) values (@origin, @operation, @location, @request); select @@IDENreplacedY;";
            command.Parameters.AddWithValue("origin", origin);
            command.Parameters.AddWithValue("operation", operation);
            command.Parameters.AddWithValue("location", location);
            command.Parameters.AddWithValue("request", request);
            var result = command.ExecuteScalar();
            return Convert.ToInt32(result);
          }
        }
      }

19 View Source File : ComputedColumnTest.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.CommandText = $"INSERT INTO [{TableName}] ([Name], [BirthDate]) VALUES (@name, @birth)";
                    sqlCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar) { Value = CheckValues[ChangeType.Insert.ToString()].Item1.Name });
                    sqlCommand.Parameters.Add(new SqlParameter("@birth", SqlDbType.Date) { Value = CheckValues[ChangeType.Insert.ToString()].Item1.BirthDate });
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [Name] = @name, [BirthDate] = @birth";
                    sqlCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar) { Value = CheckValues[ChangeType.Update.ToString()].Item1.Name });
                    sqlCommand.Parameters.Add(new SqlParameter("@birth", SqlDbType.Date) { Value = CheckValues[ChangeType.Update.ToString()].Item1.BirthDate });
                    sqlCommand.ExecuteNonQuery();
                }

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

19 View Source File : CRUDGenerator.cs
License : MIT License
Project Creator : azist

private static async Task<int> crudDelete(MsSqlDataStoreBase store, SqlConnection cnn, SqlTransaction trans, Doc doc, IDataStoreKey key)
    {
      var target = store.TargetName;
      string tableName = store.AdjustObjectNameCasing( getTableName(doc.Schema, target) );

      using (var cmd = cnn.CreateCommand())
      {
        var pk = key ?? doc.GetDataStoreKey(target);

        if (pk == null)
            throw new MsSqlDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);

        var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);

        cmd.Transaction = trans;
        if (!string.IsNullOrEmpty(where))
            cmd.CommandText = string.Format("DELETE FROM [{0}] T1 WHERE {1}",tableName, where);
        else
            cmd.CommandText = string.Format("DELETE FROM [{0}] T1", tableName);

        ConvertParameters(store, cmd.Parameters);

//dbg(cmd);

        try
        {
            var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            GeneratorUtils.LogCommand(store, "delete-ok", cmd, null);
            return affected;
        }
        catch(Exception error)
        {
            GeneratorUtils.LogCommand(store, "delete-error", cmd, error);
            throw;
        }


      }//using command
    }

19 View Source File : DataAnnotationNotMappedTest1.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('DataAnnotationNotMappedTest1Model', 'U') IS NOT NULL DROP TABLE [DataAnnotationNotMappedTest1Model];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "CREATE TABLE [DataAnnotationNotMappedTest1Model]([Number] [NVARCHAR](50) NOT NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

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

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

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([binary50Column], [bitColumn], [bit2Column], [bit3Column], [char10Column], varbinary50Column, varbinaryMAXColumn) VALUES (@binary50Column, @bitColumn, 0, 0, null, @varbinary50Column, null)";
                    sqlCommand.Parameters.Add(new SqlParameter("@binary50Column", SqlDbType.Binary) { Size = 50, Value = _checkValues[ChangeType.Insert.ToString()].Item1.Binary50Column });
                    sqlCommand.Parameters.Add(new SqlParameter("@bitColumn", SqlDbType.Bit) { Value = _checkValues[ChangeType.Insert.ToString()].Item1.BitColumn.GetValueOrDefault() });
                    sqlCommand.Parameters.Add(new SqlParameter("@varbinary50Column", SqlDbType.VarBinary) { Size = 50, Value = _checkValues[ChangeType.Insert.ToString()].Item1.Varbinary50Column });
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [binary50Column] = @binary50Column, [bitColumn] = @bitColumn, [bit2Column] = 1, [bit3Column] = 1 ,[char10Column] = @char10Column, varbinary50Column = null, varbinaryMAXColumn = @varbinaryMAXColumn";
                    sqlCommand.Parameters.Add(new SqlParameter("@binary50Column", SqlDbType.Binary) { Value = _checkValues[ChangeType.Update.ToString()].Item1.Binary50Column });
                    sqlCommand.Parameters.Add(new SqlParameter("@bitColumn", SqlDbType.Bit) { Value = _checkValues[ChangeType.Update.ToString()].Item1.BitColumn.GetValueOrDefault() });
                    sqlCommand.Parameters.Add(new SqlParameter("@char10Column", SqlDbType.Char) { Size = 10, Value = _checkValues[ChangeType.Update.ToString()].Item1.Char10Column });
                    sqlCommand.Parameters.Add(new SqlParameter("@varbinaryMAXColumn", SqlDbType.VarBinary) { Value = _checkValues[ChangeType.Update.ToString()].Item1.VarbinaryMaxColumn });
                    sqlCommand.ExecuteNonQuery();
                }

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

19 View Source File : DataAnnotationNotMappedTest2.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('DataAnnotationNotMappedTest2Model', 'U') IS NOT NULL DROP TABLE [DataAnnotationNotMappedTest2Model];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "CREATE TABLE [DataAnnotationNotMappedTest2Model]([Id] [int] NOT NULL, [Name] [NVARCHAR](50) NULL, [Long Description] [NVARCHAR](255) NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : Database.cs
License : GNU General Public License v3.0
Project Creator : andysal

public Image GetCategoryThumbnail(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection)this.Context.Database.Connection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }
            }
            return byteArrayToImage(imageRawData, true);
        }

19 View Source File : DataAnnotationTest01.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] IDENreplacedY(1, 1) NOT NULL, [Name] [NVARCHAR](50) NULL, [Long Description] [NVARCHAR](50) NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : ComputedColumnTest.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<ComputedColumnModel, ComputedColumnModel>(new ComputedColumnModel { Name = "Christian", BirthDate = DateTime.Now.AddYears(-46).Date, CalculatedAge = 46 }, new ComputedColumnModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<ComputedColumnModel, ComputedColumnModel>(new ComputedColumnModel { Name = "Nonna Velia", BirthDate = DateTime.Now.AddYears(-95).Date, CalculatedAge = 95 }, new ComputedColumnModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<ComputedColumnModel, ComputedColumnModel>(new ComputedColumnModel { Name = "Nonna Velia", BirthDate = DateTime.Now.AddYears(-95).Date, CalculatedAge = 95 }, new ComputedColumnModel()));

            CheckValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<ComputedColumnModel, ComputedColumnModel>(new ComputedColumnModel { Name = "Christian", BirthDate = DateTime.Now.AddYears(-46).Date, CalculatedAge = 46 }, new ComputedColumnModel()));
            CheckValuesOld.Add(ChangeType.Update.ToString(), new Tuple<ComputedColumnModel, ComputedColumnModel>(new ComputedColumnModel { Name = "Nonna Velia", BirthDate = DateTime.Now.AddYears(-95).Date, CalculatedAge = 95 }, new ComputedColumnModel()));
            CheckValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<ComputedColumnModel, ComputedColumnModel>(new ComputedColumnModel { Name = "Nonna Velia", BirthDate = DateTime.Now.AddYears(-95).Date, CalculatedAge = 95 }, new ComputedColumnModel()));
        }

19 View Source File : CultureInfoTest.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 [{TableName1}] ([Name], [BirthDate]) VALUES ('Christian', '2009-08-05')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName1}] SET [Name] = 'Valentina', [BirthDate] = '2009-05-08'";
                    sqlCommand.ExecuteNonQuery();
                }

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

19 View Source File : NskReadModelFacadeImpl.cs
License : GNU General Public License v3.0
Project Creator : andysal

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection)this.Database.Connection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }    
            }
            return byteArrayToImage(imageRawData, true);
        }

19 View Source File : DataAnnotationTest02.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] IDENreplacedY(1, 1) NOT NULL, [Name] [NVARCHAR](50) NULL, [Long Description] [NVARCHAR](MAX) NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : MsSqlTests.cs
License : MIT License
Project Creator : azist

private void clearAllTables()
        {
          using(var cnn = new SqlConnection(CONNECT_STRING))
          {
              cnn.Open();
              using(var cmd = cnn.CreateCommand())
              {
                cmd.CommandText = "delete from TBL_TUPLE; delete from TBL_PATIENT; delete from TBL_DOCTOR; delete from TBL_TYPES; delete from TBL_FULLGDID;";
                cmd.ExecuteNonQuery();
              }
          }

        }

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

protected bool AreAllDbObjectDisposed(string naming)
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForSa))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.objects WITH (NOLOCK) WHERE name = N'tr_{naming}_Sender'";
                    var triggerExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.service_contracts WITH (NOLOCK) WHERE name = N'{naming}'";
                    var contectExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.service_message_types WITH (NOLOCK) WHERE name = N'{naming}_Updated'";
                    var messageExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.service_queues WHERE name = N'{naming}_Receiver'";
                    var receiverQueueExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.service_queues WHERE name = N'{naming}_Sender'";
                    var senderQueueExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.services WHERE name = N'{naming}_Receiver'";
                    var serviceExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    sqlCommand.CommandText = $"SELECT COUNT(*) FROM sys.objects WITH (NOLOCK) WHERE name = N'{naming}_QueueActivationSender'";
                    var procedureExists = Convert.ToInt32(sqlCommand.ExecuteScalar());

                    return serviceExists == 0 && senderQueueExists == 0 && receiverQueueExists == 0 & triggerExists == 0 && messageExists == 0 && procedureExists == 0 && contectExists == 0;
                }
            }
        }

19 View Source File : DataAnnotationTest02.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('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : VaultMsSqlBackup.cs
License : MIT License
Project Creator : burki169

public bool Backup(string databaseName, string physicalPath)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("BACKUP DATABASE " + databaseName + " ");
                sb.AppendLine("TO DISK='" + physicalPath + "'");
                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();
            }

            return true;
        }

19 View Source File : BigIntDecimalAndFloatTypesTest.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 OBJECT_ID('{TableName}', 'U') IS NOT NULL DROP TABLE [{TableName}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"CREATE TABLE {TableName}(" +
                        "BigintColumn BIGINT NULL," +
                        "Decimal18Column DECIMAL(18, 0) NULL, " +
                        "Decimal54Column DECIMAL(5, 4) NULL, " +
                        "FloatColumn FLOAT NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : CRUDGenerator.cs
License : MIT License
Project Creator : azist

private static async Task<int> crudInsert(MsSqlDataStoreBase store, SqlConnection cnn, SqlTransaction trans, Doc doc, FieldFilterFunc filter)
    {
      var target = store.TargetName;
      var cnames = new StringBuilder();
      var values = new StringBuilder();
      var vparams = new List<SqlParameter>();
      var vpidx = 0;
      foreach (var fld in doc.Schema.FieldDefs)
      {
        var fattr = fld[target];
        if (fattr==null) continue;

        if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore) continue;

        if (filter!=null)
        {
          if (!filter(doc, null, fld)) continue;
        }

        var fname = store.AdjustObjectNameCasing( fld.GetBackendNameForTarget(target) );

        var converted = getDbFieldValue(doc, fld, fattr, store);


        cnames.AppendFormat(" [{0}],", fname);

        if ( converted.value != null)
        {
          var pname = string.Format("@VAL{0}", vpidx);

          values.AppendFormat(" {0},", pname);

          var par = new SqlParameter();
//Console.WriteLine(doc.Schema.ToJson());
//Console.WriteLine("{0}|{1}: OrclDbTyp.{2} = ({3}){4}".Args(fld.NonNullableType.FullName, pname, converted.dbType, converted.value.GetType().FullName, converted.value));
          par.ParameterName = pname;
          par.Value = converted.value;
          if (converted.dbType.HasValue) par.SqlDbType = converted.dbType.Value;
          vparams.Add(par);

          vpidx++;
        }
        else
        {
          values.Append(" NULL,");
        }
      }//foreach

      if (cnames.Length > 0)
      {
        cnames.Remove(cnames.Length - 1, 1);// remove ","
        values.Remove(values.Length - 1, 1);// remove ","
      }
      else
        return 0;//nothing to do


      string tableName = store.AdjustObjectNameCasing( getTableName(doc.Schema, target) );

      using(var cmd = cnn.CreateCommand())
      {
        var sql = "INSERT INTO [{0}] ({1}) VALUES ({2})".Args( tableName, cnames, values);

        cmd.Transaction = trans;
        cmd.CommandText = sql;
        cmd.Parameters.AddRange(vparams.ToArray());
      //  ConvertParameters(store, cmd.Parameters);
        try
        {
            var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            GeneratorUtils.LogCommand(store, "insert-ok", cmd, null);
            return affected;
        }
        catch(Exception error)
        {
            GeneratorUtils.LogCommand(store, "insert-error", cmd, error);
            throw;
        }
      }//using command
    }

19 View Source File : ComputedColumnTest.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}]([Name] [NVARCHAR](50) NULL, [BirthDate] [DATETIME] NULL)";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"ALTER TABLE [{TableName}] ADD [Age] AS DATEDIFF(YEAR, [BirthDate], GETDATE())";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : DbLoggingTest.cs
License : MIT License
Project Creator : capslocky

private static void LogResponse(string response, int id)
      {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
          connection.Open();

          using (var command = connection.CreateCommand())
          {
            command.CommandText = "update [Requests] set Response = @response where ID = @id";
            command.Parameters.AddWithValue("response", response);
            command.Parameters.AddWithValue("id", id);
            command.ExecuteNonQuery();
          }
        }
      }

19 View Source File : CultureInfoTest.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('{TableName1}', 'U') IS NOT NULL DROP TABLE [{TableName1}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"CREATE TABLE [{TableName1}]([Name] [NVARCHAR](50) NULL, [BirthDate] [DATETIME] NULL)";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"IF OBJECT_ID('{TableName2}', 'U') IS NOT NULL DROP TABLE [{TableName2}];";
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = $"CREATE TABLE [{TableName2}]([Name] [NVARCHAR](50) NULL, [BirthDate] [DATETIME] NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : Database.cs
License : GNU General Public License v3.0
Project Creator : andysal

public Image<Rgba32> GetProductThumbnail(int productId)
        {
            var connectionString = this.ConnectionString;
            using (var cn = new SqlConnection(connectionString))
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = $"SELECT CategoryID FROM Products WHERE ProductID={productId}";
                cmd.Connection = cn;
                cn.Open();
                var categoryId = (int) cmd.ExecuteScalar();
                return GetCategoryThumbnail(categoryId);
            }
        }

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

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

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName2}] ([Name], [BirthDate]) VALUES ('Christian', '2009-08-05')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName2}] SET [Name] = 'Valentina', [BirthDate] = '2009-05-08'";
                    sqlCommand.ExecuteNonQuery();
                }

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

19 View Source File : WeatherForecastService.cs
License : Apache License 2.0
Project Creator : christiandelbianco

public IList<WeatherForecast> GetForecast()
        {
            var result = new List<WeatherForecast>();

            using (var sqlConnection = new SqlConnection(_configuration["ConnectionString"]))
            {
                sqlConnection.Open();

                using (var command = sqlConnection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM " + TableName;
                    command.CommandType = CommandType.Text;

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                result.Add(new WeatherForecast
                                {
                                    City = reader.GetString(reader.GetOrdinal("City")),
                                    Temperature = reader.GetInt32(reader.GetOrdinal("Temperature"))
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

19 View Source File : DataAnnotationNotMappedTest1.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 [DataAnnotationNotMappedTest1Model];";
                    sqlCommand.ExecuteNonQuery();
                }
            }

            _checkValuesTest1.Clear();

            _counter = 0;

            _checkValuesTest1.Add(ChangeType.Insert.ToString(), new Tuple<DataAnnotationNotMappedTest1Model, DataAnnotationNotMappedTest1Model>(new DataAnnotationNotMappedTest1Model { StringNumberInDatabase = "100" }, new DataAnnotationNotMappedTest1Model()));
            _checkValuesTest1.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationNotMappedTest1Model, DataAnnotationNotMappedTest1Model>(new DataAnnotationNotMappedTest1Model { StringNumberInDatabase = "990" }, new DataAnnotationNotMappedTest1Model()));
            _checkValuesTest1.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationNotMappedTest1Model, DataAnnotationNotMappedTest1Model>(new DataAnnotationNotMappedTest1Model { StringNumberInDatabase = "990" }, new DataAnnotationNotMappedTest1Model()));
        }

19 View Source File : MsSqlRpcHandler.cs
License : MIT License
Project Creator : azist

private async Task<Rowset> readAsync(SqlConnection connection, Command command, bool isSql)
    {
      Rowset result = null;

      using(var cmd = connection.CreateCommand())
      {
        cmd.CommandType = isSql ? System.Data.CommandType.Text : System.Data.CommandType.StoredProcedure;
        cmd.CommandText = command.Text;

        bindParams(cmd, command);

        using(var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
        {
          var schema = inferSchema(reader, command);
          result = new Rowset(schema);
          result.LogChanges = false;
          while(await reader.ReadAsync().ConfigureAwait(false))
          {
            var doc = Doc.MakeDoc(result.Schema);
            populateDoc(doc, reader, command);
            result.Add(doc);

            if (result.Count > FETCH_LIMIT) break;
          }
        }
      }

      return result;
    }

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

private static void ModifyTableContentTest1()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [DataAnnotationNotMappedTest1Model] ([Number]) VALUES ('{_checkValuesTest1[ChangeType.Insert.ToString()].Item1.StringNumberInDatabase}')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [DataAnnotationNotMappedTest1Model] SET [Number] = '{_checkValuesTest1[ChangeType.Update.ToString()].Item1.StringNumberInDatabase}'";
                    sqlCommand.ExecuteNonQuery();
                }

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

19 View Source File : CancellationTokenTest.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] IDENreplacedY(1, 1) NOT NULL, " +
                        "[First Name] [nvarchar](50) NOT NULL, " +
                        "[Second Name] [nvarchar](50) NOT NULL, " +
                        "[Born] [datetime] NULL)";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

19 View Source File : DataAnnotationNotMappedTest2.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 [DataAnnotationNotMappedTest2Model];";
                    sqlCommand.ExecuteNonQuery();
                }
            }

            _checkValuesTest2.Clear();

            _counter = 0;

            _checkValuesTest2.Add(ChangeType.Insert.ToString(), new Tuple<DataAnnotationNotMappedTest2Model, DataAnnotationNotMappedTest2Model>(new DataAnnotationNotMappedTest2Model { Id = 1, Name = "Christian", Description = "Del Bianco" }, new DataAnnotationNotMappedTest2Model()));
            _checkValuesTest2.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationNotMappedTest2Model, DataAnnotationNotMappedTest2Model>(new DataAnnotationNotMappedTest2Model { Id = 3, Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationNotMappedTest2Model()));
            _checkValuesTest2.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationNotMappedTest2Model, DataAnnotationNotMappedTest2Model>(new DataAnnotationNotMappedTest2Model { Id = 3, Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationNotMappedTest2Model()));
        }

19 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : agebullhu

public SqlCommand CreateCommand(string sql, IEnumerable<DbParameter> args = null)
        {
            var cmd = Connection.CreateCommand();

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            if (sql != null)
            {
                cmd.CommandText = sql;
            }
            if (args != null)
            {
                var sqlParameters = args as SqlParameter[] ?? args.Cast<SqlParameter>().ToArray();
                if (sqlParameters.Any(p => p != null))
                {
                    cmd.Parameters.AddRange(
                        sqlParameters.Where(p => p != null)
                            .Select(
                                p => new SqlParameter(p.ParameterName, p.SqlDbType, p.Size, p.Direction, p.IsNullable, p.Precision, p.Scale,
                                        p.SourceColumn, p.SourceVersion, p.Value)).ToArray());
                }
            }
            TraceSql(cmd);
            return cmd;
        }

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

private static void ModifyTableContentTest2()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [DataAnnotationNotMappedTest2Model] ([Id], [Name], [Long Description]) VALUES ({_checkValuesTest2[ChangeType.Insert.ToString()].Item1.Id}, '{_checkValuesTest2[ChangeType.Insert.ToString()].Item1.Name}', '{_checkValuesTest2[ChangeType.Insert.ToString()].Item1.Description}')";
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [DataAnnotationNotMappedTest2Model] SET [Id] = {_checkValuesTest2[ChangeType.Update.ToString()].Item1.Id}, [Name] = '{_checkValuesTest2[ChangeType.Update.ToString()].Item1.Name}', [Long Description] = '{_checkValuesTest2[ChangeType.Update.ToString()].Item1.Description}'";
                    sqlCommand.ExecuteNonQuery();
                }

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

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

[TestCategory("SqlServer")]
        [TestMethod]
        public void Test()
        {
            var cts = new CancellationTokenSource(TimeSpan.FromMinutes(1));
            var token = cts.Token;

            var listenerSlq = new ListenerSlq(TableName);
            var objectNaming = listenerSlq.ObjectNaming;
            Task.Factory.StartNew(() => listenerSlq.Run(token), token);
            Thread.Sleep(1000 * 15 * 1);

            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    while (token.IsCancellationRequested == false)
                    {
                        sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([First Name], [Second Name]) VALUES ('{DateTime.Now.Ticks}', '{DateTime.Now.Ticks}')";
                        sqlCommand.ExecuteNonQuery();

                        Thread.Sleep(1000 * 1 * 1);
                    }
                }
            }

            listenerSlq.Dispose();
            listenerSlq = null;

            Thread.Sleep(1000 * 15 * 1);
            replacedert.IsTrue(base.AreAllDbObjectDisposed(objectNaming));
            replacedert.IsTrue(base.CountConversationEndpoints(objectNaming) == 0);
        }

19 View Source File : CRUDGenerator.cs
License : MIT License
Project Creator : azist

private static async Task<int> crudUpdate(MsSqlDataStoreBase store, SqlConnection cnn, SqlTransaction trans, Doc doc, IDataStoreKey key, FieldFilterFunc filter)
    {
      var target = store.TargetName;
      var values = new StringBuilder();
      var vparams = new List<SqlParameter>();
      var vpidx = 0;
      foreach (var fld in doc.Schema.FieldDefs)
      {
        var fattr = fld[target];
        if (fattr==null) continue;

        var fname =  fld.GetBackendNameForTarget(target);

        //20141008 DKh Skip update of key fields
        //20160124 DKh add update of keys if IDataStoreKey is present
        if (fattr.Key && !GeneratorUtils.HasFieldInNamedKey(fname, key)) continue;

        fname = store.AdjustObjectNameCasing(fname);

        if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore) continue;

        if (filter!=null)
        {
          if (!filter(doc, key, fld)) continue;
        }


        var converted = getDbFieldValue(doc, fld, fattr, store);


        if ( converted.value != null)
        {
          var pname = string.Format("@VAL{0}", vpidx);

          values.AppendFormat(" [{0}] = {1},", fname, pname);

          var par = new SqlParameter();
          par.ParameterName = pname;
          par.Value = converted.value;
          if (converted.dbType.HasValue) par.SqlDbType = converted.dbType.Value;
          vparams.Add(par);

          vpidx++;
        }
        else
        {
         values.AppendFormat(" [{0}] = NULL,", fname);
        }
      }//foreach

      if (values.Length > 0)
      {
        values.Remove(values.Length - 1, 1);// remove ","
      }
      else
        return 0;//nothing to do


      string tableName = store.AdjustObjectNameCasing( getTableName(doc.Schema, target) );

      using(var cmd = cnn.CreateCommand())
      {
        var sql = string.Empty;

        var pk = key ?? doc.GetDataStoreKey(target);

        if (pk == null)
            throw new MsSqlDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);

        var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);

        if (!string.IsNullOrEmpty(where))
            sql = "UPDATE [{0}] T1  SET {1} WHERE {2}".Args( tableName, values, where);
        else
            throw new MsSqlDataAccessException(StringConsts.BROAD_UPDATE_ERROR);//20141008 DKh BROAD update

        cmd.Transaction = trans;
        cmd.CommandText = sql;
        cmd.Parameters.AddRange(vparams.ToArray());
        //  ConvertParameters(store, cmd.Parameters);

//dbg(cmd);

        try
        {
            var affected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            GeneratorUtils.LogCommand(store, "update-ok", cmd, null);
            return affected;
        }
        catch(Exception error)
        {
            GeneratorUtils.LogCommand(store, "update-error", cmd, error);
            throw;
        }
      }//using command
    }

19 View Source File : SqlServerDataBase.cs
License : Mozilla Public License 2.0
Project Creator : agebullhu

public SqlCommand CreateCommand(string sql, IEnumerable<SqlParameter> args = null)
        {
            var cmd = Connection.CreateCommand();

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            if (sql != null)
            {
                cmd.CommandText = sql;
            }
            if (args != null)
            {
                var sqlParameters = args as SqlParameter[] ?? args.ToArray();
                if (sqlParameters.Any(p => p != null))
                {
                    cmd.Parameters.AddRange(
                        sqlParameters.Where(p => p != null)
                            .Select(
                                p =>
                                    new SqlParameter(p.ParameterName, p.SqlDbType, p.Size, p.Direction, p.Precision, p.Scale,
                                        p.SourceColumn, p.SourceVersion, p.SourceColumnNullMapping, p.Value,
                                        p.XmlSchemaCollectionDatabase, p.XmlSchemaCollectionOwningSchema,
                                        p.XmlSchemaCollectionName)).ToArray());
                }
            }
            TraceSql(cmd);
            return cmd;
        }

19 View Source File : Database.cs
License : GNU General Public License v3.0
Project Creator : andysal

public Image<Rgba32> GetCategoryThumbnail(int categoryId)
        {
            byte[] imageRawData = null;
            var connectionString = this.ConnectionString;
            using(var cn = new SqlConnection(connectionString))
            using(IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = $"SELECT Picture FROM Categories WHERE CategoryID={categoryId}";
                cmd.Connection = cn;
                cn.Open();
                using IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (myReader.Read())
                {
                    imageRawData = (byte[])myReader.GetValue(0);
                }
            }
            return ByteArrayToImage(imageRawData, true);
        }

19 View Source File : MsSqlRpcHandler.cs
License : MIT License
Project Creator : azist

private async Task<ChangeResult> txAsync(SqlConnection connection, SqlTransaction tx, Command command, bool isSql)
    {
      using (var cmd = connection.CreateCommand())
      {
        cmd.CommandType = isSql ? System.Data.CommandType.Text : System.Data.CommandType.StoredProcedure;
        cmd.CommandText = command.Text;
        cmd.Transaction = tx;

        bindParams(cmd, command);

        var time = Time.Timeter.StartNew();
        var got = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
        time.Stop();

        return new ChangeResult(ChangeResult.ChangeType.Processed, got, $"Done in {time.ElapsedMs:n0} ms", null);
      }
    }

19 View Source File : VaultMsSqlBackup.cs
License : MIT License
Project Creator : burki169

public bool Restore(string databaseName, string physicalPath)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                var sb = new StringBuilder();
                sb.AppendLine("USE [master]");
                sb.AppendLine("ALTER DATABASE " + databaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
                sb.AppendLine("RESTORE DATABASE " + databaseName + " FROM DISK='" + physicalPath + "'");
                sb.AppendLine("WAITFOR DELAY '00:00:02'");
                sb.AppendLine("ALTER DATABASE " + databaseName + " SET MULTI_USER");
                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();
            }

            return true;
        }

19 View Source File : DataAnnotationTest03.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<DataAnnotationTestSqlServer3Model, DataAnnotationTestSqlServer3Model>(new DataAnnotationTestSqlServer3Model { Name = "Christian", Description = "Del Bianco" }, new DataAnnotationTestSqlServer3Model()));
            _checkValues.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationTestSqlServer3Model, DataAnnotationTestSqlServer3Model>(new DataAnnotationTestSqlServer3Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer3Model()));
            _checkValues.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationTestSqlServer3Model, DataAnnotationTestSqlServer3Model>(new DataAnnotationTestSqlServer3Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer3Model()));

            _checkValuesOld.Add(ChangeType.Insert.ToString(), new Tuple<DataAnnotationTestSqlServer3Model, DataAnnotationTestSqlServer3Model>(new DataAnnotationTestSqlServer3Model { Name = "Christian", Description = "Del Bianco" }, new DataAnnotationTestSqlServer3Model()));
            _checkValuesOld.Add(ChangeType.Update.ToString(), new Tuple<DataAnnotationTestSqlServer3Model, DataAnnotationTestSqlServer3Model>(new DataAnnotationTestSqlServer3Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer3Model()));
            _checkValuesOld.Add(ChangeType.Delete.ToString(), new Tuple<DataAnnotationTestSqlServer3Model, DataAnnotationTestSqlServer3Model>(new DataAnnotationTestSqlServer3Model { Name = "Velia", Description = "Ceccarelli" }, new DataAnnotationTestSqlServer3Model()));
        }

See More Examples