Esempio n. 1
0
        public void SqlServerCmdKeyColumns()
        {
            var cmd = new SqlServerCmd("dbo.Table1", "Id")
            {
                ["#KeyValue"]    = "hello",
                ["AnotherValue"] = "whatever"
            };

            Assert.IsTrue(cmd["#KeyValue"].Equals(cmd["KeyValue"]));
            Assert.IsTrue(cmd["KeyValue"].Equals("hello"));
            Assert.IsTrue(cmd["#KeyValue"].Equals("hello"));
        }
Esempio n. 2
0
        public void SqlServerCmdFromQueryWithId()
        {
            CmdDictionaryInsert();

            using (var cn = GetConnection())
            {
                int id      = cn.Query <int>("SELECT [Id] FROM [dbo].[Employee]").First();
                var cmd     = SqlServerCmd.FromQueryAsync(cn, "SELECT * FROM [dbo].[Employee] WHERE [Id]=@id", new { id }).Result;
                var columns = cmd.Select(kp => kp.Key).ToArray();
                Assert.IsTrue(columns.SequenceEqual(new string[] { "FirstName", "LastName", "HireDate", "TermDate", "IsExempt", "Timestamp", "Status", "Value", "Comments", "Id" }));
            }
        }
Esempio n. 3
0
        public void SqlServerUpdateCommandWithKeyColumns()
        {
            var upd = new SqlServerCmd("dbo.Table1", "Id")
            {
                { "FirstName", "Adam" },
                { "LastName", "O'Neil" }
            };

            var cmd = upd.GetUpdateStatement();

            Assert.IsTrue(cmd.ReplaceWhitespace().Equals(
                              @"UPDATE [dbo].[Table1] SET
                    [FirstName]=@FirstName, [LastName]=@LastName
                WHERE [Id]=@Id".ReplaceWhitespace()));
        }
Esempio n. 4
0
        public void SqlServerUpdateWithExpression()
        {
            var upd = new SqlServerCmd("dbo.Table1", "Id")
            {
                { "FirstName", "Adam" },
                { "LastName", "O'Neil" },
                { "Weight", new SqlExpression("[Weight]-10") } // lost 10 lbs, yay!!!
            };

            var cmd = upd.GetUpdateStatement();

            Assert.IsTrue(cmd.ReplaceWhitespace().Equals(
                              @"UPDATE [dbo].[Table1] SET
                    [FirstName]=@FirstName, [LastName]=@LastName, [Weight]=[Weight]-10
                WHERE [Id]=@Id".ReplaceWhitespace()));
        }
Esempio n. 5
0
        public void CmdDictionaryInsert()
        {
            using (var cn = GetConnection())
            {
                var cmd = SqlServerCmd.FromTableSchemaAsync(cn, "dbo", "Employee").Result;
                cmd["FirstName"] = "Wilbur";
                cmd["LastName"]  = "Wainright";
                cmd["IsExempt"]  = true;
                cmd["Timestamp"] = new SqlExpression("getdate()");
                cmd["Status"]    = Status.Active;

                var sql = cmd.GetInsertStatement();
                var id  = cmd.InsertAsync <int>(cn).Result;
                Assert.IsTrue(cn.RowExistsAsync("[dbo].[Employee] WHERE [LastName]='Wainright'").Result);
            }
        }
Esempio n. 6
0
        public void SqlServerInsertCommand()
        {
            var ins = new SqlServerCmd("dbo.Table1", "Id")
            {
                { "FirstName", "Adam" },
                { "LastName", "O'Neil" }
            };

            var cmd = ins.GetInsertStatement();

            Assert.IsTrue(cmd.ReplaceWhitespace().Equals(
                              @"INSERT INTO [dbo].[Table1] (
                    [FirstName], [LastName]
                ) VALUES (
                    @FirstName, @LastName
                ); SELECT SCOPE_IDENTITY();".ReplaceWhitespace()));
        }
Esempio n. 7
0
        public void SqlServerInsertWithExpression()
        {
            var ins = new SqlServerCmd("dbo.Table1", "Id")
            {
                { "FirstName", "Adam" },
                { "LastName", "O'Neil" },
                { "CurrentDate", new SqlExpression("getdate()") }
            };

            var cmd = ins.GetInsertStatement();

            Assert.IsTrue(cmd.ReplaceWhitespace().Equals(
                              @"INSERT INTO [dbo].[Table1] (
                    [FirstName], [LastName], [CurrentDate]
                ) VALUES (
                    @FirstName, @LastName, getdate()
                ); SELECT SCOPE_IDENTITY();".ReplaceWhitespace()));
        }
Esempio n. 8
0
        public void CmdDictionaryUpdate()
        {
            // create our sample row
            CmdDictionaryInsert();

            using (var cn = GetConnection())
            {
                var cmd = SqlServerCmd.FromTableSchemaAsync(cn, "dbo", "Employee").Result;
                cmd["FirstName"] = "Wilbur";
                cmd["LastName"]  = "Wainright2";
                cmd["IsExempt"]  = true;
                cmd["Timestamp"] = new SqlExpression("getdate()");
                cmd["Status"]    = Status.Inactive;
                cmd["Value"]     = OtherEnum.That;

                cmd.UpdateAsync(cn, 1).Wait();
                Assert.IsTrue(cn.RowExistsAsync("[dbo].[Employee] WHERE [LastName]='Wainright2'").Result);
            }
        }