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"));
}
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" }));
}
}
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()));
}
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()));
}
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);
}
}
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()));
}
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()));
}
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);
}
}