public void SimpleSelect()
{
Net4.MySql.DataBase db = Connect();
var generator = new MySql.SqlGenerator();
db.LoadSchema();
Table historicoVentas = db.Schema.Tables.Where(t => t.Name == "historicoventa").Single();
Select select = new Select();
select.Table = historicoVentas;
select.Columns.Add(new SelectColumn(historicoVentas["fecha"]));
select.Columns.Add(new SelectColumn(historicoVentas["tipocambio"]));
select.Columns.Add(new SelectColumn(historicoVentas["precio"]));
Table cliente = db.Schema.Tables.Where(t => t.Name == "cliente").Single();
SelectJoin joinCliente = new SelectJoin();
joinCliente.Table = cliente;
joinCliente.Columns.Add(new SelectColumn(cliente["RasonSocial"]));
joinCliente.On.Add(new ColumnCompareFilter() { Column = historicoVentas["cliente"], ColumnToCompare = cliente["oid"] });
select.Where.Add(new RangeFilter() { Column = historicoVentas["fecha"], MinValue = DateTime.Now.AddYears(-1), MaxValue = DateTime.Now });
Command command = generator.Select(select);
var result = db.GetDataTable(command);
}
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 MultiJoinTest()
{
//Open Connect to DataBase
DataBase db = Connect();
var generator = new OKHOSTING.Sql.MySql.SqlGenerator();
// define table Customer
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 });
// define table Product
Table product = new Table("product");
product.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = product });
product.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = product });
product.Columns.Add(new Column() { Name = "Price", DbType = DbType.Decimal, IsNullable = false, Table = product });
// define table Tax
Table tax = new Table("tax");
tax.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = tax });
tax.Columns.Add(new Column() { Name = "Name", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = tax });
tax.Columns.Add(new Column() { Name = "Rate", DbType = DbType.Decimal, IsNullable = false, Table = tax });
// define table Sale
Table sale = new Table("sale");
sale.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = sale });
sale.Columns.Add(new Column() { Name = "Date", DbType = DbType.Date, Table = sale });
sale.Columns.Add(new Column() { Name = "Customer", DbType = DbType.Int32, IsNullable = false, Table = sale });
sale.Columns.Add(new Column() { Name = "Product", DbType = DbType.Int32, IsNullable = false, Table = sale });
sale.Columns.Add(new Column() { Name = "Tax", DbType = DbType.Int32, IsNullable = false, Table = sale });
//define Foreign Key Sale to Customer
ForeignKey customerFK = new ForeignKey();
customerFK.Table = sale;
customerFK.RemoteTable = customer;
customerFK.Name = "FK_sale_customer";
customerFK.Columns.Add(new Tuple<Column, Column>(sale["Customer"], customer["Id"]));
customerFK.DeleteAction = customerFK.UpdateAction = ConstraintAction.Restrict;
//define Foreign Key Sale to Product
ForeignKey productFK = new ForeignKey();
productFK.Table = sale;
productFK.RemoteTable = product;
productFK.Name = "FK_sale_product";
productFK.Columns.Add(new Tuple<Column, Column>(sale["Product"], product["Id"]));
productFK.DeleteAction = productFK.UpdateAction = ConstraintAction.Restrict;
//define Foreign Key Sale to Tax
ForeignKey taxFK = new ForeignKey();
taxFK.Table = sale;
taxFK.RemoteTable = tax;
taxFK.Name = "FK_sale_tax";
taxFK.Columns.Add(new Tuple<Column, Column>(sale["Tax"], tax["Id"]));
taxFK.DeleteAction = taxFK.UpdateAction = ConstraintAction.Restrict;
//Create table Customer
var sql = generator.Create(customer);
db.Execute(sql);
//Create table Product
sql = generator.Create(product);
db.Execute(sql);
//Create table Tax
sql = generator.Create(tax);
db.Execute(sql);
//Create table Sale
sql = generator.Create(sale);
db.Execute(sql);
//Create foreign Key Sale to Customer
sql = generator.Create(customerFK);
db.Execute(sql);
//Create foreign Key Sale to Product
sql = generator.Create(productFK);
db.Execute(sql);
//Create foreign Key Sale to Tax
sql = generator.Create(taxFK);
db.Execute(sql);
//insert values into customer
Insert insert = new Insert();
insert.Table = customer;
insert.Values.Add(new ColumnValue(customer["Id"], 1));
insert.Values.Add(new ColumnValue(customer["Name"], "Joyas Loyane SA de CV"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into customer
insert = new Insert();
insert.Table = customer;
insert.Values.Add(new ColumnValue(customer["Id"], 2));
insert.Values.Add(new ColumnValue(customer["Name"], "Cartie Joyerias SC de CV"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into product
insert = new Insert();
insert.Table = product;
insert.Values.Add(new ColumnValue(product["Id"], 1));
insert.Values.Add(new ColumnValue(product["Name"], "Hosting"));
insert.Values.Add(new ColumnValue(product["Price"], 125.50));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into product
insert = new Insert();
insert.Table = product;
insert.Values.Add(new ColumnValue(product["Id"], 2));
insert.Values.Add(new ColumnValue(product["Name"], "Web Page"));
insert.Values.Add(new ColumnValue(product["Price"], 1300.75));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into tax
insert = new Insert();
insert.Table = tax;
insert.Values.Add(new ColumnValue(tax["Id"], 1));
insert.Values.Add(new ColumnValue(tax["Name"], "IVA"));
insert.Values.Add(new ColumnValue(tax["Rate"], 16.00));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into sale
insert = new Insert();
insert.Table = sale;
insert.Values.Add(new ColumnValue(sale["Id"], 1));
insert.Values.Add(new ColumnValue(sale["Date"], DateTime.Today));
insert.Values.Add(new ColumnValue(sale["Customer"], 1));
insert.Values.Add(new ColumnValue(sale["Product"], 1));
insert.Values.Add(new ColumnValue(sale["Tax"], 1));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into sale
insert = new Insert();
insert.Table = sale;
insert.Values.Add(new ColumnValue(sale["Id"], 2));
insert.Values.Add(new ColumnValue(sale["Date"], DateTime.Today));
insert.Values.Add(new ColumnValue(sale["Customer"], 2));
insert.Values.Add(new ColumnValue(sale["Product"], 2));
insert.Values.Add(new ColumnValue(sale["Tax"], 1));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//select sale for customer 'Joyas Loyane SA de CV'
Select select = new Select();
select.Table = sale;
select.Columns.Add(new SelectColumn(sale["id"]));
select.Columns.Add(new SelectColumn(sale["Date"]));
//join for get name Customer
SelectJoin join = new SelectJoin();
join.Table = customer;
join.On.Add(new ColumnCompareFilter() { Column = sale["Customer"], ColumnToCompare = customer["Id"], Operator = Data.CompareOperator.Equal });
join.Columns.Add(new SelectColumn(customer["Name"], "customerName"));
join.JoinType = SelectJoinType.Inner;
//Add Join in select
select.Joins.Add(join);
//join for get name Product
SelectJoin join2 = new SelectJoin();
join2.Table = product;
join2.On.Add(new ColumnCompareFilter() { Column = sale["Product"], ColumnToCompare = product["Id"], Operator = Data.CompareOperator.Equal });
join2.Columns.Add(new SelectColumn(product["Name"], "productName"));
join2.JoinType = SelectJoinType.Inner;
//Add Join in select
select.Joins.Add(join2);
//join for get name Tax
SelectJoin join3 = new SelectJoin();
join3.Table = tax;
join3.On.Add(new ColumnCompareFilter() { Column = sale["Tax"], ColumnToCompare = tax["Id"], Operator = Data.CompareOperator.Equal });
join3.Columns.Add(new SelectColumn(tax["Name"], "taxName"));
join3.JoinType = SelectJoinType.Inner;
//Add Join in select
select.Joins.Add(join3);
//Where Customer = Joyas Loyane SA de CV.
select.Where.Add(new ValueCompareFilter() { Column = customer["Name"], ValueToCompare = "Joyas Loyane SA de CV", Operator = Data.CompareOperator.Equal });
//Execute Select
sql = generator.Select(select);
var result = db.GetDataTable(sql);
//Show result in Command Line
foreach (IDataRow row in result)
{
foreach (object obj in row)
{
Console.Write(obj);
}
}
Assert.AreEqual(result.Count, 1);
//Drop Table Sale (Firts drop the table contains foreign keys)
sql = generator.Drop(sale);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(sale.Name));
//Drop Table Customer
sql = generator.Drop(customer);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(sale.Name));
//Drop Table Product
sql = generator.Drop(product);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(sale.Name));
//Drop Table Tax
sql = generator.Drop(tax);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(sale.Name));
}
