public void CreateTable()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//define table schema
Table table = new Table("test1");
table.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, Table = table, IsAutoNumber = true });
table.Columns.Add(new Column() { Name = "TextField", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "NumberField", DbType = DbType.Int32, IsNullable = false, Table = table });
table.Indexes.Add(new Index() { Name = "IX_TextField", Unique = false, Table = table });
table.Indexes[0].Columns.Add(table["TextField"]);
//create
var sql = generator.Create(table);
db.Execute(sql);
Assert.IsTrue(db.ExistsTable(table.Name));
//add index
sql = generator.Create(table.Indexes[0]);
db.Execute(sql);
//insert
Insert insert = new Insert();
insert.Table = table;
//insert.Values.Add(new ColumnValue(table["Id"], 1));
insert.Values.Add(new ColumnValue(table["TextField"], "test11"));
insert.Values.Add(new ColumnValue(table["NumberField"], 100));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert
insert = new Insert();
insert.Table = table;
//insert.Values.Add(new ColumnValue(table["Id"], 2));
insert.Values.Add(new ColumnValue(table["TextField"], "test15"));
insert.Values.Add(new ColumnValue(table["NumberField"], 110));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select
Select select = new Select();
select.Table = table;
select.Columns.Add(table["id"]);
select.Columns.Add(table["TextField"]);
select.Where.Add(new ValueCompareFilter() { Column = table["TextField"], ValueToCompare = "test11", Operator = Data.CompareOperator.Equal });
sql = generator.Select(select);
var result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 1);
//update
Update update = new Update();
update.Table = table;
update.Where.Add(new ValueCompareFilter() { Column = table["TextField"], ValueToCompare = "test11" });
//delete
Delete delete = new Delete();
delete.Table = table;
delete.Where.Add(new ValueCompareFilter() { Column = table["TextField"], ValueToCompare = "test11" });
sql = generator.Delete(delete);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//drop
sql = generator.Drop(table);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(table.Name));
}
/// <summary>
/// Creates a UPDATE query for a single dataobject's Table
/// </summary>
/// <param name="table">
/// Table considered in the Update
/// </param>
/// <param name="dobj">
/// object instance for perform the update
/// </param>
/// <returns>
/// Sql string for update the specified object
/// </returns>
public virtual Command Update(Update update)
{
//Validating if table argument is null
if (update == null)
{
throw new ArgumentNullException("update");
}
//Creating Update sentence begin
Command command = "UPDATE " + EncloseName(update.Table.Name) + " SET ";
//Crossing DataMembers that aren't primary key
foreach (var value in update.Set)
{
CommandParameter param = new CommandParameter(value.Value, "@" + value.Column.Name, value.Column.DbType);
command.Parameters.Add(param);
//Building the sql sentence...
command.Script +=
EncloseName(value.Column.Name) +
" = " + param.Name +
", ";
}
//Remove the ", " if there is at least one updated property
command.Script = command.Script.TrimEnd(',', ' ');
//filters
command += WhereClause(update.Where);
command.Script += ScriptSeparator;
//Returning the sentence
return command;
}