public void InserInTable()
{
//open connect to database
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//define table person
Table table = new Table("test2");
table.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = table });
table.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Age", DbType = DbType.Int32, IsNullable = false, Table = table });
//create table person
var sql = generator.Create(table);
db.Execute(sql);
Assert.IsTrue(db.ExistsTable(table.Name));
//insert values into person
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Name"], "Angel"));
insert.Values.Add(new ColumnValue(table["Age"], 25));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
}
public void dropRow()
{
//Open connect to database;
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//define table customer
Table table = new Table("test4");
table.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = table });
table.Columns.Add(new Column() { Name = "Company", DbType = DbType.AnsiString, Length = 200, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Address", DbType = DbType.AnsiString, Length = 500, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Email", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Telephone", DbType = DbType.AnsiString, Length = 20, IsNullable = false, Table = table });
table.Indexes.Add(new Index() { Name = "IX_Company", Unique = true, Table = table });
table.Indexes[0].Columns.Add(table["Company"]);
//create table customer
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 values into test3
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Company"], "Monsters Inc. Corporate"));
insert.Values.Add(new ColumnValue(table["Address"], "First Street #12 Blv. Flowers San Diego. C.A."));
insert.Values.Add(new ColumnValue(table["Email"], "*****@*****.**"));
insert.Values.Add(new ColumnValue(table["Telephone"], "0122389456278"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into test3
insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Company"], "Baby tunes SA de CV"));
insert.Values.Add(new ColumnValue(table["Address"], "Paid Call #202 Blv. Saint, James Tucson. Arizona"));
insert.Values.Add(new ColumnValue(table["Email"], "*****@*****.**"));
insert.Values.Add(new ColumnValue(table["Telephone"], "012235656178"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//delete row from customer.company = Monsters Inc. Corporate
Delete delete = new Delete();
delete.Table = table;
delete.Where.Add(new ValueCompareFilter() { Column = table["Company"], ValueToCompare = "Monsters Inc. Corporate", Operator = Data.CompareOperator.Equal });
sql = generator.Delete(delete);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
}
public void InserInTable()
{
//open connect to database
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//define table person
Table table = new Table("test2");
table.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = table
});
table.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table
});
table.Columns.Add(new Column()
{
Name = "Age", DbType = DbType.Int32, IsNullable = false, Table = table
});
//create table person
var sql = generator.Create(table);
db.Execute(sql);
Assert.IsTrue(db.ExistsTable(table.Name));
//insert values into person
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Name"], "Angel"));
insert.Values.Add(new ColumnValue(table["Age"], 25));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
}
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));
}
public void InventoryTest()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//Create table store
Table store = new Table("store");
store.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = store });
store.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 50, IsNullable = false, Table = store });
store.Columns.Add(new Column() { Name = "Inventory", DbType = DbType.AnsiString, Length = 50, IsNullable = false, Table = store });
store.Columns.Add(new Column() { Name = "Employee", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = store });
Command sql = generator.Create(store);
db.Execute(sql);
//First inserts store
Insert insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 1));
insert.Values.Add(new ColumnValue(store["Name"], "Abarrotes Torrez"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Torreon"));
insert.Values.Add(new ColumnValue(store["Employee"], "Juan Rocha Gomez"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//Second inserts store
insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 2));
insert.Values.Add(new ColumnValue(store["Name"], "La Furiosa"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Zacatecas"));
insert.Values.Add(new ColumnValue(store["Employee"], "Martin Torrez"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//third inserts store
insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 3));
insert.Values.Add(new ColumnValue(store["Name"], "Los dos amigos"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Durango"));
insert.Values.Add(new ColumnValue(store["Employee"], "Luis Martinez"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//Fourth inserts store
insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 4));
insert.Values.Add(new ColumnValue(store["Name"], "La Pasada"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Nayarit"));
insert.Values.Add(new ColumnValue(store["Employee"], "Raul Gomez"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select whit OrFilter
Select select = new Select();
select.Table = store;
select.Columns.Add(store["id"]);
select.Columns.Add(store["Name"]);
select.Columns.Add(store["Employee"]);
OrFilter filter = new OrFilter();
filter.InnerFilters.Add(new ValueCompareFilter() { Column = store["id"], ValueToCompare = 1, Operator = Data.CompareOperator.Equal });
filter.InnerFilters.Add(new ValueCompareFilter() { Column = store["id"], ValueToCompare = 2, Operator = Data.CompareOperator.Equal });
select.Where.Add(filter);
sql = generator.Select(select);
var result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 2);
//select whit like
select = new Select();
select.Table = store;
select.Columns.Add(store["Name"]);
select.Columns.Add(store["Employee"]);
select.Where.Add(new ValueCompareFilter() { Column = store["Name"], ValueToCompare = '%' + "la" + '%', Operator = Data.CompareOperator.Like });
sql = generator.Select(select);
result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 2);
Assert.AreEqual(result.Count, 0);
//select whit AndFilter
select = new Select();
select.Table = store;
select.Columns.Add(store["id"]);
select.Columns.Add(store["Name"]);
select.Columns.Add(store["Inventory"]);
AndFilter and = new AndFilter();
and.InnerFilters.Add(new ValueCompareFilter() { Column = store["id"], ValueToCompare = 1, Operator = Data.CompareOperator.Equal });
and.InnerFilters.Add(new ValueCompareFilter() { Column = store["Name"], ValueToCompare = "Abarrotes Torrez", Operator = Data.CompareOperator.Equal });
select.Where.Add(and);
sql = generator.Select(select);
result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 0);
//Drop table
sql = generator.Drop(store);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(store.Name));
}
public void InnerJoinTest()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
// define table schema
Table customer = new Table("customer");
customer.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = customer });
customer.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = customer });
customer.Columns.Add(new Column() { Name = "Country", DbType = DbType.Int32, IsNullable = false, Table = customer });
Table country = new Table("country");
country.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = country });
country.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = country });
ForeignKey countryFK = new ForeignKey();
countryFK.Table = customer;
countryFK.RemoteTable = country;
countryFK.Name = "FK_customer_country";
countryFK.Columns.Add(new Tuple<Column, Column>(customer["Country"], country["id"]));
countryFK.DeleteAction = countryFK.UpdateAction = ConstraintAction.Restrict;
var sql = generator.Create(customer);
db.Execute(sql);
sql = generator.Create(country);
db.Execute(sql);
sql = generator.Create(countryFK);
db.Execute(sql);
//insert
Insert insert2 = new Insert();
insert2.Table = country;
insert2.Values.Add(new ColumnValue(country["Id"], 1));
insert2.Values.Add(new ColumnValue(country["Name"], "Mexico"));
sql = generator.Insert(insert2);
int affectedRows2 = db.Execute(sql);
Assert.AreEqual(affectedRows2, 1);
Insert insert = new Insert();
insert.Table = customer;
insert.Values.Add(new ColumnValue(customer["Id"], 1));
insert.Values.Add(new ColumnValue(customer["Name"], "Angel"));
insert.Values.Add(new ColumnValue(customer["Country"], 1));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select
Select select = new Select();
select.Table = customer;
select.Columns.Add(new SelectColumn(customer["id"]));
select.Columns.Add(new SelectColumn(customer["Name"]));
SelectJoin join = new SelectJoin();
join.Table = country;
join.On.Add(new ColumnCompareFilter() { Column = customer["country"], ColumnToCompare = country["id"], Operator = Data.CompareOperator.Equal });
join.Columns.Add(new SelectColumn(country["name"], "countryName"));
join.JoinType = SelectJoinType.Inner;
select.Joins.Add(join);
sql = generator.Select(select);
var result = db.GetDataTable(sql);
foreach (IDataRow row in result)
{
foreach (object obj in row)
{
Console.Write(obj);
}
}
Assert.AreEqual(result.Count, 1);
}
public void TablesTest()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//Create table team
Table team = new Table("team");
team.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = team });
team.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 50, IsNullable = false, Table = team });
team.Columns.Add(new Column() { Name = "Leage", DbType = DbType.Int32, IsNullable = false, Table = team });
team.Columns.Add(new Column() { Name = "Country", DbType = DbType.Int32, IsNullable = false, Table = team });
//Create table leage
Table leage = new Table("leage");
leage.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = leage });
leage.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, IsNullable = false, Table = leage });
//Create table country
Table country = new Table("country");
country.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = country });
country.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, IsNullable = false, Table = country });
//Create ForeignKey FK_team_country
ForeignKey countryFK = new ForeignKey();
countryFK.Table = team;
countryFK.RemoteTable = country;
countryFK.Name = "FK_team_country";
countryFK.Columns.Add(new Tuple<Column, Column>(team["Country"], country["Id"]));
countryFK.DeleteAction = countryFK.UpdateAction = ConstraintAction.Restrict;
//Create ForeignKey FK_team_leage
ForeignKey leageFK = new ForeignKey();
leageFK.Table = team;
leageFK.RemoteTable = leage;
leageFK.Name = "FK_team_leage";
leageFK.Columns.Add(new Tuple<Column, Column>(team["Leage"], leage["Id"]));
leageFK.DeleteAction = countryFK.UpdateAction = ConstraintAction.Restrict;
Command sql = generator.Create(team);
db.Execute(sql);
sql = generator.Create(leage);
db.Execute(sql);
sql = generator.Create(country);
db.Execute(sql);
//insert Country
Insert insert = new Insert();
insert.Table = country;
insert.Values.Add(new ColumnValue(country["Id"], 15));
insert.Values.Add(new ColumnValue(country["Name"], "Argentina"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
insert = new Insert();
insert.Table = country;
insert.Values.Add(new ColumnValue(country["Id"], 10));
insert.Values.Add(new ColumnValue(country["Name"], "Brasil"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert leage
insert = new Insert();
insert.Table = leage;
insert.Values.Add(new ColumnValue(leage["Id"], 100));
insert.Values.Add(new ColumnValue(leage["Name"], "Champions"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
insert = new Insert();
insert.Table = leage;
insert.Values.Add(new ColumnValue(leage["Id"], 110));
insert.Values.Add(new ColumnValue(leage["Name"], "Concacaff"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert team
insert = new Insert();
insert.Table = team;
insert.Values.Add(new ColumnValue(team["Id"], 1));
insert.Values.Add(new ColumnValue(team["Name"], "Barza"));
insert.Values.Add(new ColumnValue(team["Leage"], 100));
insert.Values.Add(new ColumnValue(team["Country"], 10));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
insert = new Insert();
insert.Table = team;
insert.Values.Add(new ColumnValue(team["Id"], 2));
insert.Values.Add(new ColumnValue(team["Name"], "Pumas"));
insert.Values.Add(new ColumnValue(team["Leage"], 110));
insert.Values.Add(new ColumnValue(team["Country"], 15));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select
Select select = new Select();
select.Table = team;
select.Columns.Add(new SelectColumn(team["id"]));
select.Columns.Add(new SelectColumn(team["Name"]));
//Create inner join to country
SelectJoin join = new SelectJoin();
join.Table = country;
join.On.Add(new ColumnCompareFilter() { Column = team["country"], ColumnToCompare = country["id"], Operator = Data.CompareOperator.Equal });
join.Columns.Add(new SelectColumn(country["name"], "countryName"));
join.JoinType = SelectJoinType.Inner;
select.Joins.Add(join);
//Create inner join to leage
SelectJoin join2 = new SelectJoin();
join2.Table = leage;
join2.On.Add(new ColumnCompareFilter() { Column = team["leage"], ColumnToCompare = leage["id"], Operator = Data.CompareOperator.Equal });
join2.Columns.Add(new SelectColumn(leage["name"], "leageName"));
join2.JoinType = SelectJoinType.Inner;
select.Joins.Add(join2);
select.Where.Add(new ValueCompareFilter() { Column = team["Name"], ValueToCompare = "Pumas", Operator = Data.CompareOperator.Equal });
sql = generator.Select(select);
var result = db.GetDataTable(sql);
//delete column id in table leage where id = 110
Delete delete = new Delete();
delete.Table = leage;
delete.Where.Add(new ValueCompareFilter() { Column = leage["id"], ValueToCompare = 110, Operator = Data.CompareOperator.Equal });
sql = generator.Delete(delete);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//drop table leage
sql = generator.Drop(leage);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(leage.Name));
//drop table country
sql = generator.Drop(country);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(country.Name));
//drop table team
sql = generator.Drop(team);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(team.Name));
}
public void dropRow()
{
//Open connect to database;
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//define table customer
Table table = new Table("test4");
table.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = table
});
table.Columns.Add(new Column()
{
Name = "Company", DbType = DbType.AnsiString, Length = 200, IsNullable = false, Table = table
});
table.Columns.Add(new Column()
{
Name = "Address", DbType = DbType.AnsiString, Length = 500, IsNullable = false, Table = table
});
table.Columns.Add(new Column()
{
Name = "Email", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table
});
table.Columns.Add(new Column()
{
Name = "Telephone", DbType = DbType.AnsiString, Length = 20, IsNullable = false, Table = table
});
table.Indexes.Add(new Index()
{
Name = "IX_Company", Unique = true, Table = table
});
table.Indexes[0].Columns.Add(table["Company"]);
//create table customer
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 values into test3
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Company"], "Monsters Inc. Corporate"));
insert.Values.Add(new ColumnValue(table["Address"], "First Street #12 Blv. Flowers San Diego. C.A."));
insert.Values.Add(new ColumnValue(table["Email"], "*****@*****.**"));
insert.Values.Add(new ColumnValue(table["Telephone"], "0122389456278"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into test3
insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Company"], "Baby tunes SA de CV"));
insert.Values.Add(new ColumnValue(table["Address"], "Paid Call #202 Blv. Saint, James Tucson. Arizona"));
insert.Values.Add(new ColumnValue(table["Email"], "*****@*****.**"));
insert.Values.Add(new ColumnValue(table["Telephone"], "012235656178"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//delete row from customer.company = Monsters Inc. Corporate
Delete delete = new Delete();
delete.Table = table;
delete.Where.Add(new ValueCompareFilter()
{
Column = table["Company"], ValueToCompare = "Monsters Inc. Corporate", Operator = Data.CompareOperator.Equal
});
sql = generator.Delete(delete);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
}
public void InventoryTest()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//Create table store
Table store = new Table("store");
store.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = store
});
store.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, Length = 50, IsNullable = false, Table = store
});
store.Columns.Add(new Column()
{
Name = "Inventory", DbType = DbType.AnsiString, Length = 50, IsNullable = false, Table = store
});
store.Columns.Add(new Column()
{
Name = "Employee", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = store
});
Command sql = generator.Create(store);
db.Execute(sql);
//First inserts store
Insert insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 1));
insert.Values.Add(new ColumnValue(store["Name"], "Abarrotes Torrez"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Torreon"));
insert.Values.Add(new ColumnValue(store["Employee"], "Juan Rocha Gomez"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//Second inserts store
insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 2));
insert.Values.Add(new ColumnValue(store["Name"], "La Furiosa"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Zacatecas"));
insert.Values.Add(new ColumnValue(store["Employee"], "Martin Torrez"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//third inserts store
insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 3));
insert.Values.Add(new ColumnValue(store["Name"], "Los dos amigos"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Durango"));
insert.Values.Add(new ColumnValue(store["Employee"], "Luis Martinez"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//Fourth inserts store
insert = new Insert();
insert.Table = store;
insert.Values.Add(new ColumnValue(store["Id"], 4));
insert.Values.Add(new ColumnValue(store["Name"], "La Pasada"));
insert.Values.Add(new ColumnValue(store["Inventory"], "Nayarit"));
insert.Values.Add(new ColumnValue(store["Employee"], "Raul Gomez"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select whit OrFilter
Select select = new Select();
select.Table = store;
select.Columns.Add(store["id"]);
select.Columns.Add(store["Name"]);
select.Columns.Add(store["Employee"]);
OrFilter filter = new OrFilter();
filter.InnerFilters.Add(new ValueCompareFilter()
{
Column = store["id"], ValueToCompare = 1, Operator = Data.CompareOperator.Equal
});
filter.InnerFilters.Add(new ValueCompareFilter()
{
Column = store["id"], ValueToCompare = 2, Operator = Data.CompareOperator.Equal
});
select.Where.Add(filter);
sql = generator.Select(select);
var result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 2);
//select whit like
select = new Select();
select.Table = store;
select.Columns.Add(store["Name"]);
select.Columns.Add(store["Employee"]);
select.Where.Add(new ValueCompareFilter()
{
Column = store["Name"], ValueToCompare = '%' + "la" + '%', Operator = Data.CompareOperator.Like
});
sql = generator.Select(select);
result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 2);
Assert.AreEqual(result.Count, 0);
//select whit AndFilter
select = new Select();
select.Table = store;
select.Columns.Add(store["id"]);
select.Columns.Add(store["Name"]);
select.Columns.Add(store["Inventory"]);
AndFilter and = new AndFilter();
and.InnerFilters.Add(new ValueCompareFilter()
{
Column = store["id"], ValueToCompare = 1, Operator = Data.CompareOperator.Equal
});
and.InnerFilters.Add(new ValueCompareFilter()
{
Column = store["Name"], ValueToCompare = "Abarrotes Torrez", Operator = Data.CompareOperator.Equal
});
select.Where.Add(and);
sql = generator.Select(select);
result = db.GetDataTable(sql);
Assert.AreEqual(result.Count, 0);
//Drop table
sql = generator.Drop(store);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(store.Name));
}
public void TablesTest()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
//Create table team
Table team = new Table("team");
team.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = team
});
team.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, Length = 50, IsNullable = false, Table = team
});
team.Columns.Add(new Column()
{
Name = "Leage", DbType = DbType.Int32, IsNullable = false, Table = team
});
team.Columns.Add(new Column()
{
Name = "Country", DbType = DbType.Int32, IsNullable = false, Table = team
});
//Create table leage
Table leage = new Table("leage");
leage.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = leage
});
leage.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, IsNullable = false, Table = leage
});
//Create table country
Table country = new Table("country");
country.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = country
});
country.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, IsNullable = false, Table = country
});
//Create ForeignKey FK_team_country
ForeignKey countryFK = new ForeignKey();
countryFK.Table = team;
countryFK.RemoteTable = country;
countryFK.Name = "FK_team_country";
countryFK.Columns.Add(new Tuple <Column, Column>(team["Country"], country["Id"]));
countryFK.DeleteAction = countryFK.UpdateAction = ConstraintAction.Restrict;
//Create ForeignKey FK_team_leage
ForeignKey leageFK = new ForeignKey();
leageFK.Table = team;
leageFK.RemoteTable = leage;
leageFK.Name = "FK_team_leage";
leageFK.Columns.Add(new Tuple <Column, Column>(team["Leage"], leage["Id"]));
leageFK.DeleteAction = countryFK.UpdateAction = ConstraintAction.Restrict;
Command sql = generator.Create(team);
db.Execute(sql);
sql = generator.Create(leage);
db.Execute(sql);
sql = generator.Create(country);
db.Execute(sql);
//insert Country
Insert insert = new Insert();
insert.Table = country;
insert.Values.Add(new ColumnValue(country["Id"], 15));
insert.Values.Add(new ColumnValue(country["Name"], "Argentina"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
insert = new Insert();
insert.Table = country;
insert.Values.Add(new ColumnValue(country["Id"], 10));
insert.Values.Add(new ColumnValue(country["Name"], "Brasil"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert leage
insert = new Insert();
insert.Table = leage;
insert.Values.Add(new ColumnValue(leage["Id"], 100));
insert.Values.Add(new ColumnValue(leage["Name"], "Champions"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
insert = new Insert();
insert.Table = leage;
insert.Values.Add(new ColumnValue(leage["Id"], 110));
insert.Values.Add(new ColumnValue(leage["Name"], "Concacaff"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert team
insert = new Insert();
insert.Table = team;
insert.Values.Add(new ColumnValue(team["Id"], 1));
insert.Values.Add(new ColumnValue(team["Name"], "Barza"));
insert.Values.Add(new ColumnValue(team["Leage"], 100));
insert.Values.Add(new ColumnValue(team["Country"], 10));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
insert = new Insert();
insert.Table = team;
insert.Values.Add(new ColumnValue(team["Id"], 2));
insert.Values.Add(new ColumnValue(team["Name"], "Pumas"));
insert.Values.Add(new ColumnValue(team["Leage"], 110));
insert.Values.Add(new ColumnValue(team["Country"], 15));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select
Select select = new Select();
select.Table = team;
select.Columns.Add(new SelectColumn(team["id"]));
select.Columns.Add(new SelectColumn(team["Name"]));
//Create inner join to country
SelectJoin join = new SelectJoin();
join.Table = country;
join.On.Add(new ColumnCompareFilter()
{
Column = team["country"], ColumnToCompare = country["id"], Operator = Data.CompareOperator.Equal
});
join.Columns.Add(new SelectColumn(country["name"], "countryName"));
join.JoinType = SelectJoinType.Inner;
select.Joins.Add(join);
//Create inner join to leage
SelectJoin join2 = new SelectJoin();
join2.Table = leage;
join2.On.Add(new ColumnCompareFilter()
{
Column = team["leage"], ColumnToCompare = leage["id"], Operator = Data.CompareOperator.Equal
});
join2.Columns.Add(new SelectColumn(leage["name"], "leageName"));
join2.JoinType = SelectJoinType.Inner;
select.Joins.Add(join2);
select.Where.Add(new ValueCompareFilter()
{
Column = team["Name"], ValueToCompare = "Pumas", Operator = Data.CompareOperator.Equal
});
sql = generator.Select(select);
var result = db.GetDataTable(sql);
//delete column id in table leage where id = 110
Delete delete = new Delete();
delete.Table = leage;
delete.Where.Add(new ValueCompareFilter()
{
Column = leage["id"], ValueToCompare = 110, Operator = Data.CompareOperator.Equal
});
sql = generator.Delete(delete);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//drop table leage
sql = generator.Drop(leage);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(leage.Name));
//drop table country
sql = generator.Drop(country);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(country.Name));
//drop table team
sql = generator.Drop(team);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(team.Name));
}
public void InnerJoinTest()
{
DataBase db = Connect();
var generator = new OKHOSTING.Sql.Net4.SqlServer.SqlGenerator();
// define table schema
Table customer = new Table("customer");
customer.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = customer
});
customer.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = customer
});
customer.Columns.Add(new Column()
{
Name = "Country", DbType = DbType.Int32, IsNullable = false, Table = customer
});
Table country = new Table("country");
country.Columns.Add(new Column()
{
Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = country
});
country.Columns.Add(new Column()
{
Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = country
});
ForeignKey countryFK = new ForeignKey();
countryFK.Table = customer;
countryFK.RemoteTable = country;
countryFK.Name = "FK_customer_country";
countryFK.Columns.Add(new Tuple <Column, Column>(customer["Country"], country["id"]));
countryFK.DeleteAction = countryFK.UpdateAction = ConstraintAction.Restrict;
var sql = generator.Create(customer);
db.Execute(sql);
sql = generator.Create(country);
db.Execute(sql);
sql = generator.Create(countryFK);
db.Execute(sql);
//insert
Insert insert2 = new Insert();
insert2.Table = country;
insert2.Values.Add(new ColumnValue(country["Id"], 1));
insert2.Values.Add(new ColumnValue(country["Name"], "Mexico"));
sql = generator.Insert(insert2);
int affectedRows2 = db.Execute(sql);
Assert.AreEqual(affectedRows2, 1);
Insert insert = new Insert();
insert.Table = customer;
insert.Values.Add(new ColumnValue(customer["Id"], 1));
insert.Values.Add(new ColumnValue(customer["Name"], "Angel"));
insert.Values.Add(new ColumnValue(customer["Country"], 1));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select
Select select = new Select();
select.Table = customer;
select.Columns.Add(new SelectColumn(customer["id"]));
select.Columns.Add(new SelectColumn(customer["Name"]));
SelectJoin join = new SelectJoin();
join.Table = country;
join.On.Add(new ColumnCompareFilter()
{
Column = customer["country"], ColumnToCompare = country["id"], Operator = Data.CompareOperator.Equal
});
join.Columns.Add(new SelectColumn(country["name"], "countryName"));
join.JoinType = SelectJoinType.Inner;
select.Joins.Add(join);
sql = generator.Select(select);
var result = db.GetDataTable(sql);
foreach (IDataRow row in result)
{
foreach (object obj in row)
{
Console.Write(obj);
}
}
Assert.AreEqual(result.Count, 1);
}
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));
}
