C# (CSharp) Dictionary.ToSqlParametersの例

コード例 #1

ファイル: IDictionaryExtensionsTests.cs プロジェクト: jashelb90/DBConfirm

        public void IDictionaryExtensions_EmptyDictionary_ReturnEmptyList()
        {
            IDictionary <string, object> dictionary = new Dictionary <string, object>();
            IList <SqlParameter>         result     = dictionary.ToSqlParameters();

            Assert.IsNotNull(result);
            Assert.AreEqual(0, result.Count);
        }

コード例 #2

ファイル: SqlConnection.ExecuteNonQuery.cs プロジェクト: fqybzhangji/Z.ExtensionMethods

        public void ExecuteNonQuery()
        {
            string sql = "DECLARE @FizzBuzz VARCHAR(MAX) = @Fizz";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                conn.ExecuteNonQuery(sql, dict.ToSqlParameters());
            }
        }

コード例 #3

ファイル: IDictionaryExtensionsTests.cs プロジェクト: jashelb90/DBConfirm

        public void IDictionaryExtensions_DictionaryWithItems_ReturnListWithItems()
        {
            IDictionary <string, object> dictionary = new Dictionary <string, object>
            {
                { "ColumnA", 123 }
            };
            IList <SqlParameter> result = dictionary.ToSqlParameters();

            Assert.IsNotNull(result);
            Assert.AreEqual(1, result.Count);
            Assert.AreEqual("@ColumnA", result[0].ParameterName);
            Assert.AreEqual(123, result[0].Value);
        }

コード例 #4

        public void ExecuteNonQuery()
        {
            string sql  = "DECLARE @FizzBuzz VARCHAR(MAX) = @Fizz";
            var    dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                conn.ExecuteNonQuery(sql, dict.ToSqlParameters());
            }
        }

コード例 #5

ファイル: SqlConnection.ExecuteScalar.cs プロジェクト: fqybzhangji/Z.ExtensionMethods

        public void ExecuteScalar()
        {
            const string sql = @"SELECT 1 As IntColumn WHERE @Fizz = 1";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                object result = conn.ExecuteScalar(sql, dict.ToSqlParameters());

                // UnitTest
                Assert.AreEqual(1, result);
            }
        }

コード例 #6

ファイル: SqlConnection.ExecuteExpandoObject.cs プロジェクト: ChuangYang/Z.ExtensionMethods

        public void ExecuteExpandoObject()
        {
            string sql = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn WHERE @Fizz = 1";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                dynamic entity = conn.ExecuteExpandoObject(sql, dict.ToSqlParameters());

                // Unit Test
                Assert.AreEqual(1, entity.IntColumn);
                Assert.AreEqual("FizzBuzz", entity.StringColumn);
            }
        }

コード例 #7

ファイル: SqlConnection.ExecuteScalar.cs プロジェクト: BackOffice-Solutions/Extensions.NET

        public void ExecuteScalar()
        {
            const string sql  = @"SELECT 1 As IntColumn WHERE @Fizz = 1";
            var          dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                object result = conn.ExecuteScalar(sql, dict.ToSqlParameters());

                // UnitTest
                Assert.AreEqual(1, result);
            }
        }

コード例 #8

ファイル: SqlConnection.ExecuteExpandoObject.cs プロジェクト: zqlovejyc/Z.ExtensionMethods

        public void ExecuteExpandoObject()
        {
            string sql  = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn WHERE @Fizz = 1";
            var    dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                dynamic entity = conn.ExecuteExpandoObject(sql, dict.ToSqlParameters());

                // Unit Test
                Assert.AreEqual(1, entity.IntColumn);
                Assert.AreEqual("FizzBuzz", entity.StringColumn);
            }
        }

コード例 #9

ファイル: SqlConnection.ExecuteExpandoObjects.cs プロジェクト: fqybzhangji/Z.ExtensionMethods

        public void ExecuteExpandoObjects()
        {
            string sql = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn UNION SELECT 2, 'BuzzBuzz' WHERE @Fizz = 1";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                List<dynamic> list = conn.ExecuteExpandoObjects(sql, dict.ToSqlParameters()).ToList();

                // Unit Test
                Assert.AreEqual(1, list[0].IntColumn);
                Assert.AreEqual("FizzBuzz", list[0].StringColumn);
                Assert.AreEqual(2, list[1].IntColumn);
                Assert.AreEqual("BuzzBuzz", list[1].StringColumn);
            }
        }

コード例 #10

ファイル: SqlConnection.ExecuteXmlReader.cs プロジェクト: ChuangYang/Z.ExtensionMethods

        public void ExecuteReader()
        {
            string sql = "SELECT '1' AS A WHERE @Fizz = 1 FOR XML RAW";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                using (XmlReader reader = conn.ExecuteXmlReader(sql, dict.ToSqlParameters()))
                {
                    reader.Read();

                    object result1 = reader[0];

                    // Unit Test
                    Assert.AreEqual("1", result1);
                }
            }
        }

コード例 #11

        public void ExecuteExpandoObjects()
        {
            string sql  = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn UNION SELECT 2, 'BuzzBuzz' WHERE @Fizz = 1";
            var    dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                List <dynamic> list = conn.ExecuteExpandoObjects(sql, dict.ToSqlParameters()).ToList();

                // Unit Test
                Assert.AreEqual(1, list[0].IntColumn);
                Assert.AreEqual("FizzBuzz", list[0].StringColumn);
                Assert.AreEqual(2, list[1].IntColumn);
                Assert.AreEqual("BuzzBuzz", list[1].StringColumn);
            }
        }

コード例 #12

ファイル: SqlConnection.ExecuteDataSet.cs プロジェクト: ChuangYang/Z.ExtensionMethods

        public void ExecuteDataSet()
        {
            DataSet result;
            const string sql = @"
SELECT 1
SELECT 2
SELECT 3 WHERE @Fizz = 1
";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                result = conn.ExecuteDataSet(sql, dict.ToSqlParameters()); // return DataSet (3 tables).
            }

            // Unit Test
            Assert.AreEqual(3, result.Tables.Count);
        }

コード例 #13

ファイル: SqlConnection.ExecuteReader.cs プロジェクト: ChuangYang/Z.ExtensionMethods

        public void ExecuteReader()
        {
            string sql = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn WHERE @Fizz = 1";
            var dict = new Dictionary<string, object> {{"@Fizz", 1}};

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                using (IDataReader reader = conn.ExecuteReader(sql, dict.ToSqlParameters()))
                {
                    reader.Read();
                    object result1 = reader[0];
                    object result2 = reader[1];

                    // Unit Test
                    Assert.AreEqual(1, result1);
                    Assert.AreEqual("FizzBuzz", result2);
                }
            }
        }

コード例 #14

ファイル: SqlConnection.ExecuteXmlReader.cs プロジェクト: BackOffice-Solutions/Extensions.NET

        public void ExecuteReader()
        {
            string sql  = "SELECT '1' AS A WHERE @Fizz = 1 FOR XML RAW";
            var    dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                using (XmlReader reader = conn.ExecuteXmlReader(sql, dict.ToSqlParameters()))
                {
                    reader.Read();

                    object result1 = reader[0];

                    // Unit Test
                    Assert.AreEqual("1", result1);
                }
            }
        }

コード例 #15

        public void ExecuteDataSet()
        {
            DataSet      result;
            const string sql  = @"
SELECT 1
SELECT 2
SELECT 3 WHERE @Fizz = 1
";
            var          dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                result = conn.ExecuteDataSet(sql, dict.ToSqlParameters()); // return DataSet (3 tables).
            }

            // Unit Test
            Assert.AreEqual(3, result.Tables.Count);
        }

コード例 #16

ファイル: SqlConnection.ExecuteReader.cs プロジェクト: BackOffice-Solutions/Extensions.NET

        public void ExecuteReader()
        {
            string sql  = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn WHERE @Fizz = 1";
            var    dict = new Dictionary <string, object> {
                { "@Fizz", 1 }
            };

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                conn.Open();
                using (IDataReader reader = conn.ExecuteReader(sql, dict.ToSqlParameters()))
                {
                    reader.Read();
                    object result1 = reader[0];
                    object result2 = reader[1];

                    // Unit Test
                    Assert.AreEqual(1, result1);
                    Assert.AreEqual("FizzBuzz", result2);
                }
            }
        }

コード例 #17

ファイル: IDictionary[System.String,System.Object].ToSqlParameters.cs プロジェクト: ChuangYang/Z.ExtensionMethods

        public void ToSqlParameters()
        {
            const string sql = @"SELECT 1 WHERE @Fizz = 1 AND @Buzz = 2";
            var dict = new Dictionary<string, object>();
            dict.Add("@Fizz", 1);
            dict.Add("@Buzz", 2);

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(dict.ToSqlParameters());
                    conn.Open();

                    object result = command.ExecuteScalar();

                    // UnitTest
                    Assert.AreEqual(1, result);
                }
            }
        }

コード例 #18

        public void ToSqlParameters()
        {
            const string sql  = @"SELECT 1 WHERE @Fizz = 1 AND @Buzz = 2";
            var          dict = new Dictionary <string, object>();

            dict.Add("@Fizz", 1);
            dict.Add("@Buzz", 2);

            // Examples
            using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
            {
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(dict.ToSqlParameters());
                    conn.Open();

                    object result = command.ExecuteScalar();

                    // UnitTest
                    Assert.AreEqual(1, result);
                }
            }
        }

コード例 #19

ファイル: IDictionaryExtensionsTests.cs プロジェクト: jashelb90/DBConfirm

        public void IDictionaryExtensions_DictionaryWithMultipleRequiredPlaceholder_ThrowFirstError()
        {
            IDictionary <string, object> dictionary = new Dictionary <string, object>
            {
                { "ColumnA", Placeholders.IsRequired() },
                { "ColumnB", Placeholders.IsRequired() }
            };
            RequiredPlaceholderIsNullException exception = Assert.Throws <RequiredPlaceholderIsNullException>(() => dictionary.ToSqlParameters());

            Assert.AreEqual("ColumnA", exception.ColumnName);
            Assert.AreEqual("The value for ColumnA is required but has not been set", exception.Message);
        }