C# SqlConnection.CreateCommand方法代码示例

        public static void TestMain()
        {
            string connstr = (new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr) { MultipleActiveResultSets = true }).ConnectionString;
            string cmdText1 = "select * from Orders; select count(*) from Customers";
            string cmdText2 = "select * from Customers; select count(*) from Orders";

            using (var conn = new SqlConnection(connstr))
            {
                conn.Open();

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    SqlCommand cmd1 = conn.CreateCommand();
                    cmd1.Transaction = tran;
                    cmd1.CommandText = cmdText1;

                    using (SqlDataReader reader1 = cmd1.ExecuteReader())
                    {

                        SqlCommand cmd2 = conn.CreateCommand();
                        cmd2.Transaction = tran;
                        cmd2.CommandText = cmdText2;

                        using (SqlDataReader reader2 = cmd2.ExecuteReader())
                        {
                            int actualOrderCount = 0;
                            while (reader1.Read())
                            {
                                Assert.True(actualOrderCount <= expectedOrders.Length, "FAILED: Received more results than expected");
                                DataTestUtility.AssertEqualsWithDescription(expectedOrders[actualOrderCount], reader1.GetValue(0), "FAILED: Received wrong value in order query at row " + actualOrderCount);
                                actualOrderCount++;
                            }
                            reader1.NextResult();
                            reader1.Read();
                            int customerCount = (int)reader1.GetValue(0);

                            int actualCustomerCount = 0;
                            while (reader2.Read())
                            {
                                Assert.True(actualCustomerCount <= expectedCustomers.Length, "FAILED: Received more results than expected");
                                DataTestUtility.AssertEqualsWithDescription(expectedCustomers[actualCustomerCount], reader2.GetValue(0), "FAILED: Received wrong value in customer query at row " + actualCustomerCount);
                                actualCustomerCount++;
                            }
                            reader2.NextResult();
                            reader2.Read();
                            int orderCount = (int)reader2.GetValue(0);

                            DataTestUtility.AssertEqualsWithDescription(expectedCustomers.Length, customerCount, "FAILED: Count query returned incorrect Customer count");
                            DataTestUtility.AssertEqualsWithDescription(expectedOrders.Length, orderCount, "FAILED: Count query returned incorrect Order count");
                        }
                    }

                    cmd1.CommandText = "select @@trancount";
                    int tranCount = (int)cmd1.ExecuteScalar();
                    Assert.True(tranCount == 1, "FAILED: Expected a transaction count of 1, but actually received " + tranCount);

                    tran.Commit();
                }
            }
        }

        public static void XmlTest()
        {
            string tempTable = "xml_" + Guid.NewGuid().ToString().Replace('-', '_');
            string initStr = "create table " + tempTable + " (xml_col XML)";
            string insertNormStr = "INSERT " + tempTable + " VALUES('<doc>Hello World</doc>')";
            string insertParamStr = "INSERT " + tempTable + " VALUES(@x)";
            string queryStr = "select * from " + tempTable;

            using (SqlConnection conn = new SqlConnection(DataTestUtility.TcpConnStr))
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = initStr;
                cmd.ExecuteNonQuery();

                try
                {
                    cmd.CommandText = insertNormStr;
                    cmd.ExecuteNonQuery();

                    SqlCommand cmd2 = new SqlCommand(insertParamStr, conn);

                    cmd2.Parameters.Add("@x", SqlDbType.Xml);
                    XmlReader xr = XmlReader.Create("data.xml");
                    cmd2.Parameters[0].Value = new SqlXml(xr);
                    cmd2.ExecuteNonQuery();

                    cmd.CommandText = queryStr;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int currentValue = 0;
                        string[] expectedValues =
                        {
                            "<doc>Hello World</doc>",
                            "<NewDataSet><builtinCLRtypes><colsbyte>1</colsbyte><colbyte>2</colbyte><colint16>-20</colint16><coluint16>40</coluint16><colint32>-300</colint32><coluint32>300</coluint32><colint64>-4000</colint64><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colstring>string variable</colstring><colboolean>true</colboolean><coltimespan>P10675199DT2H48M5.4775807S</coltimespan><coldatetime>9999-12-30T23:59:59.9999999-08:00</coldatetime><colGuid>00000001-0002-0003-0405-060708010101</colGuid><colbyteArray>AQIDBAUGBwgJCgsMDQ4PEA==</colbyteArray><colUri>http://www.abc.com/</colUri><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">1</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">2</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-20</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">40</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-300</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">300</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-4000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">4000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">50000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">600000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">70000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">true</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">P10675199DT2H48M5.4775807S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">9999-12-30T23:59:59.9999999-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000001-0002-0003-0405-060708010101</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">AQIDBAUGBwgJCgsMDQ4PEA==</colobjectbytearray><colobjectUri xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"anyURI\">http://www.abc.com/</colobjectUri></builtinCLRtypes><builtinCLRtypes><colbyte>2</colbyte><colint16>-20</colint16><colint32>-300</colint32><coluint32>300</coluint32><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colboolean>true</colboolean><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">11</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">22</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-200</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">400</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-3000</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">3000</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-40000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">40000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">500000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">6000000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">700000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable 2</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">false</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">-P10675199DT2H48M5.4775808S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">0001-01-01T00:00:00.0000000-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000002-0001-0001-0807-060504030201</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">EA8ODQwLCgkIBwYFBAMCAQ==</colobjectbytearray></builtinCLRtypes></NewDataSet>"
                        };

                        while (reader.Read())
                        {
                            Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");

                            SqlXml sx = reader.GetSqlXml(0);
                            xr = sx.CreateReader();
                            xr.Read();

                            DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], xr.ReadOuterXml(), "FAILED: Did not receive expected data");
                        }
                    }
                }
                finally
                {
                    cmd.CommandText = "drop table " + tempTable;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private static void TimeoutCancel(string constr)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = 1;
                cmd.CommandText = "WAITFOR DELAY '00:00:30';select * from Customers";

                string errorMessage = SystemDataResourceManager.Instance.SQL_Timeout;
                DataTestUtility.ExpectFailure<SqlException>(() => cmd.ExecuteReader(), errorMessage);

                VerifyConnection(cmd);
            }
        }

        public static void SQLBU503290Test()
        {
            using (SqlConnection conn = new SqlConnection(DataTestUtility.TcpConnStr))
            {
                conn.Open();
                SqlParameter p = new SqlParameter("@p", SqlDbType.DateTimeOffset);
                p.Value = DBNull.Value;
                p.Size = 27;
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT @p";
                cmd.Parameters.Add(p);

                Assert.True(cmd.ExecuteScalar() is DBNull, "FAILED: ExecuteScalar did not return a result of type DBNull");
            }
        }

        private static void MultiThreadedCancel(string constr, bool async)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                var command = con.CreateCommand();
                command.CommandText = "select * from orders; waitfor delay '00:00:08'; select * from customers";

                Thread rThread1 = new Thread(ExecuteCommandCancelExpected);
                Thread rThread2 = new Thread(CancelSharedCommand);
                Barrier threadsReady = new Barrier(2);
                object state = new Tuple<bool, SqlCommand, Barrier>(async, command, threadsReady);

                rThread1.Start(state);
                rThread2.Start(state);
                rThread1.Join();
                rThread2.Join();

                CommandCancelTest.VerifyConnection(command);
            }
        }

        public void GenerateTableOnServer(SqlConnection con, string tableName)
        {
            // create table
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = GenerateCreateTableTSql(tableName);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                StringWriter output = new StringWriter();
                output.WriteLine("(START)");
                output.WriteLine(cmd.CommandText);
                output.WriteLine("- Columns:");
                DumpColumnsInfo(output);
                output.WriteLine("(END)");
                output.WriteLine(e.Message);
                output.WriteLine("---------");

                lock (Console.Out)
                    Console.WriteLine(output);

                throw;
            }

            InsertRows(con, tableName, 0, _rows.Count);
        }

        public static void TestUdtSchemaMetadata()
        {
            using (SqlConnection connection = new SqlConnection(DataTestUtility.TcpConnStr))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select hierarchyid::Parse('/1/1/3/') as col0, geometry::Parse('LINESTRING (100 100, 20 180, 180 180)') as col1, geography::Parse('LINESTRING(-122.360 47.656, -122.343 47.656)') as col2";
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                    ReadOnlyCollection<DbColumn> columns = reader.GetColumnSchema();

                    DbColumn column = null;

                    // Validate Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
                    column = columns[0];
                    Assert.Equal(column.ColumnName, "col0");
                    Assert.Equal(typeof(byte[]), column.DataType);
                    Assert.NotNull(column.UdtAssemblyQualifiedName);
                    AssertSqlUdtAssemblyQualifiedName(column.UdtAssemblyQualifiedName, "Microsoft.SqlServer.Types.SqlHierarchyId");

                    // Validate Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version = 11.0.0.0, Culture = neutral, PublicKeyToken = 89845dcd8080cc91
                    column = columns[1];
                    Assert.Equal(column.ColumnName, "col1");
                    Assert.Equal(typeof(byte[]), column.DataType);
                    Assert.NotNull(column.UdtAssemblyQualifiedName);
                    AssertSqlUdtAssemblyQualifiedName(column.UdtAssemblyQualifiedName, "Microsoft.SqlServer.Types.SqlGeometry");

                    // Validate Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version = 11.0.0.0, Culture = neutral, PublicKeyToken = 89845dcd8080cc91
                    column = columns[2];
                    Assert.Equal(column.ColumnName, "col2");
                    Assert.Equal(typeof(byte[]), column.DataType);
                    Assert.NotNull(column.UdtAssemblyQualifiedName);
                    AssertSqlUdtAssemblyQualifiedName(column.UdtAssemblyQualifiedName, "Microsoft.SqlServer.Types.SqlGeography");
                }
            }
        }

        private static void TestUdtSqlDataReaderGetBytes(CommandBehavior behavior)
        {
            using (SqlConnection connection = new SqlConnection(DataTestUtility.TcpConnStr))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select hierarchyid::Parse('/1/1/3/') as col0, geometry::Parse('LINESTRING (100 100, 20 180, 180 180)') as col1, geography::Parse('LINESTRING(-122.360 47.656, -122.343 47.656)') as col2";
                using (SqlDataReader reader = command.ExecuteReader(behavior))
                {
                    Assert.True(reader.Read());

                    int byteCount = 0;
                    byte[] bytes = null;

                    byteCount = (int) reader.GetBytes(0, 0, null, 0, 0);
                    Assert.True(byteCount > 0);
                    bytes = new byte[byteCount];
                    reader.GetBytes(0, 0, bytes, 0, bytes.Length);
                    Assert.Equal("5ade", ToHexString(bytes));

                    byteCount = (int)reader.GetBytes(1, 0, null, 0, 0);
                    Assert.True(byteCount > 0);
                    bytes = new byte[byteCount];
                    reader.GetBytes(1, 0, bytes, 0, bytes.Length);
                    Assert.Equal("0000000001040300000000000000000059400000000000005940000000000000344000000000008066400000000000806640000000000080664001000000010000000001000000ffffffff0000000002", ToHexString(bytes));

                    byteCount = (int)reader.GetBytes(2, 0, null, 0, 0);
                    Assert.True(byteCount > 0);
                    bytes = new byte[byteCount];
                    reader.GetBytes(2, 0, bytes, 0, bytes.Length);
                    Assert.Equal("e610000001148716d9cef7d34740d7a3703d0a975ec08716d9cef7d34740cba145b6f3955ec0", ToHexString(bytes));

                    if (behavior == CommandBehavior.Default)
                    {
                        byteCount = (int)reader.GetBytes(0, 0, null, 0, 0);
                        Assert.True(byteCount > 0);
                        bytes = new byte[byteCount];
                        reader.GetBytes(0, 0, bytes, 0, bytes.Length);
                        Assert.Equal("5ade", ToHexString(bytes));
                    }
                }
            }
        }

        public static void TestUdtSqlCommandExecuteScalarThrowsPlatformNotSupportedException()
        {
            using (SqlConnection connection = new SqlConnection(DataTestUtility.TcpConnStr))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select hierarchyid::Parse('/') as col0";
                Assert.Throws<PlatformNotSupportedException>(() =>
                {
                    object value = command.ExecuteScalar();
                });


                SqlCommand asyncCommand = connection.CreateCommand();
                asyncCommand.CommandText = "select hierarchyid::Parse('/') as col0";
                AggregateException exception = Assert.Throws<AggregateException>(() =>
                {
                    asyncCommand.ExecuteScalarAsync().Wait();
                });
                Assert.IsType(typeof(PlatformNotSupportedException), exception.InnerException);
            }
        }

 private void StartTest()
 {
     try
         {
         using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
             {
             conn.Open();
             using ( SqlCommand cmd = conn.CreateCommand() )
                 {
                 cmd.CommandText = "StartNewSMSDeliveryServiceTest";
                 cmd.CommandType = System.Data.CommandType.StoredProcedure;
                 cmd.Parameters.AddWithValue("@PhoneNumber", Settings.Default.RemoutePhoneNumber);
                 cmd.Parameters.AddWithValue("@NativePhoneNumber", Settings.Default.NativePhoneNumber);
                 object result = cmd.ExecuteScalar();
                 testId = Convert.ToInt64(result);
                 testStarted = true;
                 lastChecked = DateTime.Now.Ticks;
                 if ( OnTestStarted != null )
                     {
                     OnTestStarted();
                     }
                 }
             }
         }
     catch ( Exception exp )
         {
         NotifyOnError(exp);
         }
 }

 private void SendMessageToAdministrator(string messageText)
 {
     Message message = new Message(Settings.Default.AdminPhoneNumber, messageText);
     try
         {
         using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
             {
             conn.Open();
             using ( SqlCommand cmd = conn.CreateCommand() )
                 {
                 cmd.CommandText = "select Count(*) from SMSServiceTroublesLog where cast([Date] as date) = @Date and Processed = 0 and ServiceNumber = @ServicePhoneNumber";
                 cmd.Parameters.AddWithValue("@Date", DateTime.Now.Date);
                 cmd.Parameters.AddWithValue("@ServicePhoneNumber", Settings.Default.RemoutePhoneNumber);
                 object result = cmd.ExecuteScalar();
                 if ( ( int ) result == 0 && SMSHelper.SmsHelper.SendMessage(message) )
                     {
                     cmd.CommandText = "AddSMSDeliveryServiceTroubleInformation";
                     cmd.Parameters.Clear();
                     cmd.CommandType = System.Data.CommandType.StoredProcedure;
                     cmd.Parameters.AddWithValue("@AdminPhoneNumber", Settings.Default.AdminPhoneNumber);
                     cmd.Parameters.AddWithValue("@ServicePhoneNumber", Settings.Default.RemoutePhoneNumber);
                     cmd.Parameters.AddWithValue("@ErrorData", messageText);
                     cmd.ExecuteNonQuery();
                     testStarted = false;
                     testId = 0;
                     }
                 }
             }
         }
     catch ( Exception exp )
         {
         NotifyOnError(exp);
         }
 }

 private void PerformTest(TestResults result = TestResults.Ok)
 {
     if ( testStarted )
         {
         try
             {
             using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
                 {
                 conn.Open();
                 using ( SqlCommand cmd = conn.CreateCommand() )
                     {
                     cmd.CommandText = "update SMSDeliverServiceTest set DateEnd = GETDATE(), Result = @Status where Id = @Id";
                     cmd.Parameters.AddWithValue("@Id", testId);
                     cmd.Parameters.AddWithValue("@Status", ( int ) result);
                     cmd.ExecuteNonQuery();
                     testId = 0;
                     testStarted = false;
                     lastChecked = DateTime.Now.Ticks;
                     if ( OnTestEnded != null )
                         {
                         OnTestEnded(result);
                         }
                     }
                 }
             }
         catch ( Exception exp )
             {
             NotifyOnError(exp);
             }
         }
 }

 private bool CheckRemouteSMSServiceStatus()
 {
     bool result = false;
     try
         {
         using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
             {
             conn.Open();
             using ( SqlCommand cmd = conn.CreateCommand() )
                 {
                 cmd.CommandText = "GetSMSServiceStatus";
                 cmd.CommandType = System.Data.CommandType.StoredProcedure;
                 cmd.Parameters.AddWithValue("@PhoneNumber", Settings.Default.RemoutePhoneNumber);
                 cmd.Parameters.AddWithValue("@OfflineStatusDelay", Settings.Default.DelayBeforeTestErrorCalled);
                 result = Convert.ToBoolean(cmd.ExecuteScalar());
                 if ( !result && remouteServiceIsOnline )
                     {
                     remouteServiceIsOnline = false;
                     SendMessageToAdministrator(String.Format("Удаленный сервис {0} Offline",
                         Settings.Default.BaseHelperClassName == "GSMTerminalSMSHelper" ? "Смартфон" : "GSM терминал"));
                     if ( OnRemouteSMSServiceStatusChanged != null )
                         {
                         OnRemouteSMSServiceStatusChanged(false);
                         }
                     }
                 else if ( result && !remouteServiceIsOnline )
                     {
                     remouteServiceIsOnline = true;
                     if ( OnRemouteSMSServiceStatusChanged != null )
                         {
                         OnRemouteSMSServiceStatusChanged(true);
                         }
                     }
                 }
             }
         }
     catch ( Exception exp )
         {
         NotifyOnError(exp);
         }
     return result;
 }

 private void SetNewPhoneNumberAsMain()
 {
     try
         {
         using (SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString))
             {
             conn.Open();
             using (SqlCommand cmd = conn.CreateCommand())
                 {
                 cmd.CommandText = "SetNewPhoneNumberAsMain";
                 cmd.CommandType = System.Data.CommandType.StoredProcedure;
                 cmd.Parameters.AddWithValue("@DevicePhoneNumber", Settings.Default.NativePhoneNumber);
                 object result = cmd.ExecuteScalar();
                 }
             }
         }
     catch (Exception exp)
         {
         NotifyOnError(exp);
         }
 }

        private void InsertRowInternal(SqlConnection con, ref SqlCommand cmd, ref SqlParameter[] parameters, string tableName, int row)
        {
            // cannot use DataTable: it does not handle well char[] values and variant and also does not support sparse/column set ones
            StringBuilder columnsText = new StringBuilder();
            StringBuilder valuesText = new StringBuilder();

            // create the command and parameters on first call, reuse afterwards (to reduces table creation overhead)
            if (cmd == null)
            {
                cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                // need to unbind existing parameters and re-add the next set of values
                cmd.Parameters.Clear();
            }

            if (parameters == null)
            {
                parameters = new SqlParameter[_columns.Length];
            }

            object[] rowValues = _rows[row];

            // there is a limit of parameters to be sent (2010)
            for (int ci = 0; ci < _columns.Length; ci++)
            {
                if (cmd.Parameters.Count >= MaxParameterCount)
                {
                    // reached the limit of max parameters, cannot continue
                    // theoretically, we could do INSERT + UPDATE. practically, chances for this to happen are almost none since nulls are skipped
                    rowValues[ci] = DBNull.Value;
                    continue;
                }

                if (SkipOnInsert(ci))
                {
                    // cannot insert timestamp
                    // insert of values into columnset columns are also not supported (use sparse columns themselves)
                    continue;
                }

                bool isNull = (rowValues[ci] == DBNull.Value || rowValues[ci] == null);

                if (isNull)
                {
                    // columns such as sparse cannot have DEFAULT constraint, thus it is safe to ignore the value of the column when inserting new row
                    // this also significantly reduces number of columns updated during insert, to prevent "The number of target 
                    // columns that are specified in an INSERT, UPDATE, or MERGE statement exceeds the maximum of 4096."
                    continue;
                }

                SqlParameter p = parameters[ci];

                // construct column list
                if (columnsText.Length > 0)
                {
                    columnsText.Append(", ");
                    valuesText.Append(", ");
                }

                columnsText.AppendFormat("[{0}]", _columnNames[ci]);

                if (p == null)
                {
                    p = cmd.CreateParameter();
                    p.ParameterName = "@p" + ci;
                    p.SqlDbType = _columns[ci].Type;

                    parameters[ci] = p;
                }

                p.Value = rowValues[ci] ?? DBNull.Value;

                cmd.Parameters.Add(p);

                valuesText.Append(p.ParameterName);
            }

            Debug.Assert(columnsText.Length > 0, "Table that have only TIMESTAMP, ColumnSet or Sparse columns are not allowed - use primary key in this case");

            cmd.CommandText = string.Format("INSERT INTO {0} ( {1} ) VALUES ( {2} )", tableName, columnsText, valuesText);

            cmd.ExecuteNonQuery();
        }