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 dropRow()
{
//Open connect to database;
DataBase db = Connect();
var generator = new OKHOSTING.Sql.MySql.SqlGenerator();
//define table customer
Table table = new Table("Customer");
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 = 100, 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 = 50, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Telephone", DbType = DbType.AnsiString, Length = 12, IsNullable = false, Table = table });
//create table customer
var sql = generator.Create(table);
db.Execute(sql);
Assert.IsTrue(db.ExistsTable(table.Name));
//insert values into customer
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Id"], 1));
insert.Values.Add(new ColumnValue(table["Company"], "Software Create Inc."));
insert.Values.Add(new ColumnValue(table["Address"], "San Angel #123-A Col. Metropolis Mexico. D.F."));
insert.Values.Add(new ColumnValue(table["Email"], "*****@*****.**"));
insert.Values.Add(new ColumnValue(table["Telephone"], "013318592634"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into customer
insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Id"], 2));
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);
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 DataType(Type innerType, Table table)
{
if (innerType == null)
{
throw new ArgumentNullException("innerType");
}
InnerType = innerType;
if (table == null)
{
CreateTable();
}
else
{
Table = table;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="OKHOSTING.ORM.DataType"/> class.
/// <para xml:lang="es">
/// Inicializa una nueva instancia de la clase OKHOSTING.ORM.DataTipe
/// </para>
/// </summary>
/// <param name="innerType">Inner type.
/// <para xml:lang="es">Tipo de entrada.</para>
/// </param>
/// <param name="table">Table.
/// <para xml:lang="es">La tabla.</para>
/// </param>
public DataType(Type innerType, Table table)
{
//if type innerType is null, it sends an exception
if (innerType == null)
{
throw new ArgumentNullException("innerType");
}
//Assigns the value received at Inner Type
InnerType = innerType;
//If the table is null, send call the metod CreateTable()
if (table == null)
{
CreateTable();
}
//Else, assign the table received to Table.
else
{
Table = table;
}
}
/// <summary>
/// Returns the name of the function used to retrieve the latest
/// auto-generated primary key on the session
/// </summary>
/// <param name="table">Table which table will be scanned for the last autogenerated primary key</param>
/// <returns>Sql function that returns a single value containing the last auto generated primary key on a table</returns>
protected override Command AutoIncrementalFunction(Table table)
{
return "LAST_INSERT_ID()";
}
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 DropTable()
{
//Open Connect to DataBase
DataBase db = Connect();
var generator = new OKHOSTING.Sql.MySql.SqlGenerator();
//define table song
Table table = new Table("Song");
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 = "Sing", DbType = DbType.AnsiString, Length = 120, IsNullable = false, Table = table });
//create table song
var sql = generator.Create(table);
db.Execute(sql);
Assert.IsTrue(db.ExistsTable(table.Name));
//insert values into song
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Id"], 1));
insert.Values.Add(new ColumnValue(table["Name"], "More than words"));
insert.Values.Add(new ColumnValue(table["Sing"], "Extreme"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//drop table song
sql = generator.Drop(table);
db.Execute(sql);
Assert.IsFalse(db.ExistsTable(table.Name));
}
public void selectPerson()
{
//open connect to database
DataBase db = Connect();
var generator = new OKHOSTING.Sql.MySql.SqlGenerator();
//define table person
Table table = new Table("Person");
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["Id"], 1));
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);
//SELECT id, name, age FROM person WHERE name='Angel'
Select select = new Select();
select.Table = table;
select.Columns.Add(table["Id"]);
select.Columns.Add(table["Name"]);
select.Columns.Add(table["Age"]);
select.Where.Add(new ValueCompareFilter() { Column = table["Name"], ValueToCompare = "Angel", Operator = Data.CompareOperator.Equal });
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);
}
/// <summary>
/// Returns the name of the function used to retrieve the latest
/// auto-generated primary key on the session
/// </summary>
/// <param name="dtype">TypeMap<T> which table will be scanned for the last autogenerated primary key</param>
/// <returns>Sql function that returns a single value containing the last auto generated primary key on a table</returns>
protected override Command AutoIncrementalFunction(Table table)
{
return string.Format("IDENT_CURRENT({0})", EncloseName(table.Name));
}
public void selectLike()
{
//Open Connect to DataBase
DataBase db = Connect();
var generator = new OKHOSTING.Sql.MySql.SqlGenerator();
//define table Address
Table table = new Table("Address");
table.Columns.Add(new Column() { Name = "Id", DbType = DbType.Int32, IsPrimaryKey = true, IsAutoNumber = true, Table = table });
table.Columns.Add(new Column() { Name = "Street", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Number", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Suburb", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "State", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
table.Columns.Add(new Column() { Name = "Country", DbType = DbType.AnsiString, Length = 100, IsNullable = false, Table = table });
//create tanle Address
var sql = generator.Create(table);
db.Execute(sql);
Assert.IsTrue(db.ExistsTable(table.Name));
//insert values into Address
Insert insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Id"], 1));
insert.Values.Add(new ColumnValue(table["Street"], "First Avenue"));
insert.Values.Add(new ColumnValue(table["Number"], "12-B"));
insert.Values.Add(new ColumnValue(table["Suburb"], "The Roses"));
insert.Values.Add(new ColumnValue(table["State"], "California"));
insert.Values.Add(new ColumnValue(table["Country"], "United States"));
sql = generator.Insert(insert);
int affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//insert values into Address
insert = new Insert();
insert.Table = table;
insert.Values.Add(new ColumnValue(table["Id"], 2));
insert.Values.Add(new ColumnValue(table["Street"], "Second Life"));
insert.Values.Add(new ColumnValue(table["Number"], "1120"));
insert.Values.Add(new ColumnValue(table["Suburb"], "Park Avenue"));
insert.Values.Add(new ColumnValue(table["State"], "New York"));
insert.Values.Add(new ColumnValue(table["Country"], "United States"));
sql = generator.Insert(insert);
affectedRows = db.Execute(sql);
Assert.AreEqual(affectedRows, 1);
//SELECT street and suburb FROM address WHERE street LIKE 'Second'
Select select = new Select();
select.Table = table;
select.Columns.Add(table["Id"]);
select.Columns.Add(table["Street"]);
select.Columns.Add(table["Suburb"]);
select.Where.Add(new ValueCompareFilter() { Column = table["Street"], ValueToCompare = "Second", Operator = Data.CompareOperator.Like });
sql = generator.Select(select);
var result = db.GetDataTable(sql);
//Show result from query
foreach (IDataRow row in result)
{
foreach (object obj in row)
{
Console.Write(obj);
}
}
}
protected override Command AutoIncrementalFunction(Table table)
{
throw new NotImplementedException();
}
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 Create Table sentence for a single dataobject's Table
/// </summary>
/// <param name="table">
/// Table for Create Table sentence creation
/// </param>
/// <returns>
/// Create Table Sql Sentence
/// </returns>
public virtual Command Create(Table table)
{
//Begin the Create Table Creation
Command sql = "CREATE TABLE " + EncloseName(table.Name) + " (";
//Add columns
foreach (var m in table.Columns)
{
//Processing column definition
sql += CreateColumnClause(m) + ", ";
}
//Adding primary key
sql += PrimaryKeyDefinition(table) + ", ";
//Creating indexes
//foreach (Index index in table.InnerType.GetCustomAttributes(typeof(Index), false))
//{
// sql += GetIndexDefinition(table, index) + ", ";
//}
//Removing the last ", "
sql.Script = sql.Script.TrimEnd(',', ' ');
//Enclosing the create table
sql += ")";
//Returning the sql Sentence
return sql;
}
/// <summary>
/// Creates a DROP TABLE sentence for a single dataobject's Table
/// </summary>
/// <param name="table">
/// Table for create te Drop sentence
/// </param>
/// <returns>
/// Sql DROP TABLE sentence
/// </returns>
public virtual Command Drop(Table table)
{
return "DROP TABLE " + EncloseName(table.Name);
}
/// <summary> /// Returns the name of the function used to retrieve the latest /// auto-generated primary key on the session /// </summary> /// <param name="table">Table which table will be scanned for the last autogenerated primary key</param> /// <returns>Sql function that returns a single value containing the last auto generated primary key on a table</returns> protected abstract Command AutoIncrementalFunction(Table table);
/// <summary>
/// Returns a query for returning the auto-generated id of the last insert operation on a Table
/// </summary>
/// <param name="table">Table which table will be scanned for the last autogenerated primary key</param>
/// <returns>Sql script that returns a single value containing the last auto generated primary key on a table</returns>
public virtual Command LastAutogeneratedId(Table table)
{
return "SELECT " + AutoIncrementalFunction(table) + " AS ID" + ScriptSeparator;
}
/// <summary>
/// Returns the DML for define the primary key
/// constraint of the specified Table
/// </summary>
/// <param name="table">
/// Table for primary key creation
/// </param>
/// <returns>
/// DML for define the primary key
/// constraint of the specified Table
/// </returns>
protected virtual Command PrimaryKeyDefinition(Table table)
{
//Local Vars
string primaryKeyConstraint;
//Begin the primary key definition
primaryKeyConstraint = "CONSTRAINT " + EncloseName("PK_" + table.Name) + " PRIMARY KEY (";
//Crossing the DataMembers for the primary key
foreach (Column pk in table.PrimaryKey)
{
primaryKeyConstraint += EncloseName(pk.Name) + ", ";
}
//Removing the last ", " and enclosing the primary key Clause
primaryKeyConstraint = primaryKeyConstraint.Remove(primaryKeyConstraint.Length - 2, 2);
primaryKeyConstraint += ")";
//Retuning the DML
return primaryKeyConstraint;
}
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);
}
/// <summary>
/// Creates (in memory, not in DB) a new table for this DataType and creates columns for it's datamembers
/// <para xml:lang="es">
/// Crea (en memoria no en BD) una nueva tabla para este tipo de datos y crea columnas de datos para sus miembros.
/// </para>
/// </summary>
public void CreateTable()
{
Table = new Table(FullName.Replace('.', '_').Replace('<', '_').Replace('>', '_'));
//If the table contains a column for each type of data, create.
foreach (DataMember dm in DataMembers)
{
if (dm.Column == null)
{
dm.CreateColumn();
}
}
}
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 ManualMapWithTables()
{
Table personTable = new Table("personTable");
personTable.Columns.Add(new Column() { IsPrimaryKey = true, Name = "key", DbType = Sql.DbType.Int32 });
personTable.Columns.Add(new Column() { Name = "name", DbType = Sql.DbType.String });
personTable.Columns.Add(new Column() { Name = "birth", DbType = Sql.DbType.DateTime });
DataType<Person> dtype = new DataType<Person>(personTable);
DataType.AllDataTypes.Add(dtype);
dtype.AddMember(m => m.Id, personTable["key"]);
dtype.AddMember(m => m.Firstname, personTable["name"]);
dtype.AddMember(x => x.BirthDate, personTable["birth"]);
DataType<CustomerContact> dtype2 = new DataType<CustomerContact>();
DataType.AllDataTypes.Add(dtype2);
dtype2.AddMember(m => m.Id);
dtype2.AddMember(m => m.Customer.Id);
dtype2.AddMember(m => m.Customer.LegalName);
dtype2.AddMember(m => m.Customer.Phone);
DataBase.Create<CustomerContact>();
for (int i = 0; i < 30; i++)
{
CustomerContact cc = new CustomerContact();
cc.Firstname = "Maria " + i;
cc.Customer = new Customer();
cc.Customer.LegalName = "Empresa " + i;
cc.Customer.Phone = "Telefono " + i;
DataBase.Table<int, CustomerContact>().Add(0, cc);
}
foreach (var cc in DataBase.Table<int, CustomerContact>())
{
Console.WriteLine(cc.Value.Firstname + " " + cc.Value.Customer.LegalName);
}
DataBase.Drop<CustomerContact>();
}
/// <summary>
/// Returns the full schema of the database including tables, indexes, foreign keys, etc.
/// </summary>
/// <remarks>
/// It's very slow for large databases
/// </remarks>
public static DataBaseSchema Load(DataBase database, string schemaProvider)
{
DataBaseSchema schema = new DataBaseSchema();
DatabaseSchema schemaReader;
using (var dbReader = new DatabaseSchemaReader.DatabaseReader(database.ConnectionString, schemaProvider))
{
dbReader.AllTables();
dbReader.AllViews();
try
{
dbReader.AllStoredProcedures();
}
catch { }
try
{
dbReader.AllUsers();
}
catch { }
schemaReader = dbReader.DatabaseSchema;
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
dbt.PrimaryKeyColumn.AddIdentity();
var table = new Table()
{
Name = dbt.Name,
DataBase = schema,
IdentityIncrement = dbt.PrimaryKeyColumn.IdentityDefinition.IdentityIncrement,
IdentitySeed = dbt.PrimaryKeyColumn.IdentityDefinition.IdentitySeed,
};
schema.Tables.Add(table);
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
var table = schema[dbt.Name];
foreach (DatabaseColumn dbc in dbt.Columns)
{
Column column = new Column()
{
Name = dbc.Name,
Table = table,
Description = dbc.Description,
IsNullable = dbc.Nullable,
IsAutoNumber = dbc.IsAutoNumber,
ComputedDefinition = dbc.ComputedDefinition,
DefaultValue = dbc.DefaultValue,
IsPrimaryKey = dbc.IsPrimaryKey,
Length = (uint?) dbc.Length,
Ordinal = dbc.Ordinal,
Precision = dbc.Precision,
Scale = dbc.Scale,
};
if (dbc.DataType != null)
{
column.DbType = DbTypeMapper.Parse(dbc.DataType.GetNetType());
}
else
{
if(dbc.DbDataType.StartsWith("varchar"))
{
column.DbType = DbType.AnsiString;
}
else if (dbc.DbDataType.StartsWith("int"))
{
column.DbType = DbType.Int32;
}
else if (dbc.DbDataType.StartsWith("decimal"))
{
column.DbType = DbType.Decimal;
}
else if (dbc.DbDataType.StartsWith("datetime"))
{
column.DbType = DbType.DateTime;
}
else if (dbc.DbDataType.StartsWith("money"))
{
column.DbType = DbType.Currency;
}
else if (dbc.DbDataType.StartsWith("char"))
{
column.DbType = DbType.AnsiStringFixedLength;
}
else if (dbc.DbDataType.StartsWith("text"))
{
column.DbType = DbType.AnsiString;
}
}
table.Columns.Add(column);
}
foreach (DatabaseIndex dbi in dbt.Indexes)
{
Index index = new Index()
{
Name = dbi.Name,
Table = table,
Direction = SortDirection.Ascending,
Unique = dbi.IsUnique,
};
foreach (DatabaseColumn dbc in dbi.Columns)
{
index.Columns.Add(table[dbc.Name]);
}
table.Indexes.Add(index);
}
foreach (DatabaseTrigger dbtr in dbt.Triggers)
{
DataBaseOperation operation = DataBaseOperation.Insert;
Enum.TryParse<DataBaseOperation>(dbtr.TriggerEvent, true, out operation);
Trigger trigger = new Trigger()
{
TriggerBody = dbtr.TriggerBody,
TriggerEvent = operation,
Table = table,
};
table.Triggers.Add(trigger);
}
foreach (DatabaseConstraint dbcons in dbt.CheckConstraints)
{
if (dbcons.ConstraintType == ConstraintType.Check)
{
CheckConstraint constraint = new CheckConstraint()
{
Expression = dbcons.Expression,
Table = table,
};
table.CheckConstraints.Add(constraint);
}
else if (dbcons.ConstraintType == ConstraintType.ForeignKey)
{
ForeignKey foreignKey = new ForeignKey()
{
Name= dbcons.Name,
DeleteAction = schema.ParseConstraintAction(dbcons.DeleteRule),
UpdateAction =schema.ParseConstraintAction(dbcons.UpdateRule),
RemoteTable = schema[dbcons.RefersToTable],
Table = table,
};
var referencedColumns = dbcons.ReferencedColumns(schemaReader).ToArray();
for (int i = 0; i < dbcons.Columns.Count; i++)
{
foreignKey.Columns.Add(new Tuple<Column,Column>(table[dbcons.Columns[i]], foreignKey.RemoteTable[referencedColumns[i]]));
}
table.ForeignKeys.Add(foreignKey);
}
}
}
foreach (DatabaseView dbv in schemaReader.Views)
{
View view = new View()
{
Name = dbv.Name,
Command = dbv.Sql,
Description = dbv.Description,
DataBase = schema,
};
schema.Views.Add(view);
}
foreach (DatabaseUser dbu in schemaReader.Users)
{
User user = new User()
{
Name = dbu.Name,
DataBase = schema,
};
schema.Users.Add(user);
}
return schema;
}
/// <summary>
/// Creates a new DataType based on an existing Table, matching only members that have a column with the same name
/// </summary>
public static DataType DefaultMap(Type type, Table table)
{
if (type == null)
{
throw new ArgumentNullException("type");
}
if (table == null)
{
throw new ArgumentNullException("table");
}
if (IsMapped(type))
{
throw new ArgumentOutOfRangeException("type", "This Types is already mapped");
}
DataType dtype = new DataType(type, table);
foreach (var memberInfo in GetMappableMembers(type))
{
if (dtype.Table.Columns.Where(c => c.Name == memberInfo.Name).Count() > 0)
{
dtype.AddMember(memberInfo.Name, dtype.Table[memberInfo.Name]);
}
}
return dtype;
}
/// <summary>
/// Creates (in memory, not in DB) a new table for this DataType and creates columns for it's datamembers
/// </summary>
public void CreateTable()
{
Table = new Table(FullName.Replace('.', '_').Replace('<', '_').Replace('>', '_'));
foreach (DataMember dm in DataMembers)
{
if (dm.Column == null)
{
dm.CreateColumn();
}
}
}
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));
}
/// <summary>
/// Creates a new data type based on the table that receives as argument, only members who have a column with the same name
/// <para xml:lang="es">
/// Crea un nuevo tipo de datos basado en la tabla que reciba como argumento, solo miembros que tienen una columna con el mismo nombre
/// </para>
/// </summary>
public static DataType DefaultMap(Type type, Table table)
{
if (type == null)
{
throw new ArgumentNullException("type");
}
if (table == null)
{
throw new ArgumentNullException("table");
}
if (IsMapped(type))
{
throw new ArgumentOutOfRangeException("type", "This Types is already mapped");
}
DataType dtype = new DataType(type, table);
foreach (MemberInfo memberInfo in GetMappableMembers(type))
{
if (dtype.Table.Columns.Where(c => c.Name == memberInfo.Name).Count() > 0)
{
dtype.AddMember(memberInfo.Name, dtype.Table[memberInfo.Name]);
TypeInfo returnType = MemberExpression.GetReturnType(memberInfo).GetTypeInfo();
if (returnType.Equals(typeof(DataType).GetTypeInfo()) || returnType.IsSubclassOf(typeof(DataType)))
{
dtype[memberInfo.Name].Converter = new Conversions.DataTypeConverter();
}
else if (returnType.Equals(typeof(Type).GetTypeInfo()))
{
dtype[memberInfo.Name].Converter = new Conversions.TypeConverter();
}
else if (returnType.Equals(typeof(object).GetTypeInfo()) && IsMapped(returnType.AsType()))
{
dtype[memberInfo.Name].Converter = new Conversions.PersistentObjectConverter();
}
}
}
return dtype;
}
/// <summary>
/// Returns the name of the function used to retrieve the latest
/// auto-generated primary key on the session
/// </summary>
/// <param name="dtype">TypeMap<T> which table will be scanned for the last autogenerated primary key</param>
/// <returns>Sql function that returns a single value containing the last auto generated primary key on a table</returns>
protected override Command AutoIncrementalFunction(Table table)
{
return "@@IDENTITY";
}