/// <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 list of new DataTypes, creating as well a list of new Tables with all members of type as columns /// <para xml:lang="es"> /// Crea una lista de tipos de datos nuevos, creando así una lista de nuevas tablas con todos los miembros de tipo como columnas /// </para> /// </summary> public static IEnumerable<DataType> DefaultMap(IEnumerable<Type> types) { //we will store here all types that are actually persistent List<DataType> persistentTypes = new List<DataType>(); Random random = new Random(); //map primary keys first, so we allow to foreign keys and inheritance to be correctly mapped foreach (Type type in types) { //skip enums and interfaces if (type.GetTypeInfo().IsEnum || type.GetTypeInfo().IsInterface) { continue; } //ignore types with no primary key var pk = GetMappableMembers(type).Where(m => DataMember.IsPrimaryKey(m)); if (pk.Count() == 0) { continue; } DataType dtype = new DataType(type); AllDataTypes.Add(dtype); foreach (var memberInfo in pk) { //create datamember dtype.AddMember(memberInfo.Name); } persistentTypes.Add(dtype); } foreach (DataType dtype in persistentTypes) { //create inheritance foreign keys if (dtype.BaseDataType != null) { ForeignKey foreignKey = new ForeignKey(); foreignKey.Table = dtype.Table; foreignKey.RemoteTable = dtype.BaseDataType.Table; foreignKey.Name = "FK_" + dtype.Name + "_" + dtype.BaseDataType.Name + "_" + random.Next(); //we asume that primary keys on parent and child tables have the same number and order of related columns for (int i = 0; i < dtype.PrimaryKey.Count(); i++) { DataMember pk = dtype.PrimaryKey.ToArray()[i]; DataMember basePk = dtype.BaseDataType.PrimaryKey.ToArray()[i]; foreignKey.Columns.Add(new Tuple<Column, Column>(pk.Column, basePk.Column)); } dtype.Table.ForeignKeys.Add(foreignKey); } //map non primary key members now foreach (var memberInfo in GetMappableMembers(dtype.InnerType).Where(m => !DataMember.IsPrimaryKey(m))) { Type returnType = MemberExpression.GetReturnType(memberInfo); //is this a collection of a mapped type? if so, ignore since this must be a 1-1, 1-many or many-many relationship and must be mapped somewhere else if (DataType.IsCollection(returnType) && IsMapped(returnType.GetCollectionItemType())) { continue; } //its a persistent type, with it's own table, map as a foreign key with one or more columns for the primary key if (IsMapped(returnType)) { //we asume this datatype is already mapped along with it's primery key DataType returnDataType = returnType; ForeignKey foreignKey = new ForeignKey(); foreignKey.Table = dtype.Table; foreignKey.RemoteTable = returnDataType.Table; foreignKey.Name = "FK_" + dtype.Name + "_" + memberInfo.Name + "_" + random.Next(); foreach (DataMember pk in returnDataType.PrimaryKey.ToList()) { DataMember dmember = dtype.AddMember(memberInfo.Name + "." + pk.Expression); dmember.Column.Name = memberInfo.Name + "_" + pk.Expression.Expression.Replace('.', '_'); dmember.Column.IsPrimaryKey = false; dmember.Column.IsNullable = !RequiredValidator.IsRequired(memberInfo); foreignKey.Columns.Add(new Tuple<Column, Column>(dmember.Column, pk.Column)); } dtype.Table.ForeignKeys.Add(foreignKey); } //just map as a atomic value else { //create datamember dtype.AddMember(memberInfo.Name); } } foreach (var memberInfo in GetMappableMembers(dtype.InnerType)) { foreach (var att in memberInfo.GetCustomAttributes()) { var validator = att as ValidatorBase; if (validator == null) { continue; } MemberValidator memberValidator = new MemberValidator(new MemberExpression(memberInfo.DeclaringType, memberInfo.Name), validator); dtype.Validators.Add(memberValidator); } } yield return dtype; } }
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); }
/// <summary> /// Creates a list of new DataTypes, creating as well a list of new Tables with all members of type as columns /// </summary> public static IEnumerable<DataType> DefaultMap(IEnumerable<Type> types) { //we will store here all types that are actually persistent List<DataType> persistentTypes = new List<DataType>(); Random random = new Random(); //map primary keys first, so we allow to foreign keys and inheritance to be correctly mapped foreach (Type type in types) { //skip enums and interfaces if (type.GetTypeInfo().IsEnum || type.GetTypeInfo().IsInterface) { continue; } //ignore types with no primary key var pk = GetMappableMembers(type).Where(m => DataMember.IsPrimaryKey(m)); if (pk.Count() == 0) { continue; } DataType dtype = new DataType(type); AllDataTypes.Add(dtype); foreach (var memberInfo in pk) { //create datamember dtype.AddMember(memberInfo.Name); } persistentTypes.Add(dtype); } foreach (DataType dtype in persistentTypes) { //create inheritance foreign keys if (dtype.BaseDataType != null) { ForeignKey foreignKey = new ForeignKey(); foreignKey.Table = dtype.Table; foreignKey.RemoteTable = dtype.BaseDataType.Table; foreignKey.Name = "FK_" + dtype.Name + "_" + dtype.BaseDataType.Name + "_" + random.Next(); //we asume that primary keys on parent and child tables have the same number and order of related columns for (int i = 0; i < dtype.PrimaryKey.Count(); i++) { DataMember pk = dtype.PrimaryKey.ToArray()[i]; DataMember basePk = dtype.BaseDataType.PrimaryKey.ToArray()[i]; foreignKey.Columns.Add(new Tuple<Column, Column>(pk.Column, basePk.Column)); } dtype.Table.ForeignKeys.Add(foreignKey); } //map non primary key members now foreach (var memberInfo in GetMappableMembers(dtype.InnerType).Where(m => !DataMember.IsPrimaryKey(m))) { Type returnType = MemberExpression.GetReturnType(memberInfo); //is this a collection of a mapped type? if so, ignore since this must be a 1-1, 1-many or many-many relationship and must be mapped somewhere else if (DataType.IsCollection(returnType) && IsMapped(returnType.GetCollectionItemType())) { continue; } //its a persistent type, with it's own table, map as a foreign key with one or more columns for the primary key if (IsMapped(returnType)) { //we asume this datatype is already mapped along with it's primery key DataType returnDataType = returnType; ForeignKey foreignKey = new ForeignKey(); foreignKey.Table = dtype.Table; foreignKey.RemoteTable = returnDataType.Table; foreignKey.Name = "FK_" + dtype.Name + "_" + memberInfo.Name + "_" + random.Next(); foreach (DataMember pk in returnDataType.PrimaryKey.ToList()) { Column column = new Column(); column.Name = memberInfo.Name + "_" + pk.Member.Expression.Replace('.', '_'); column.Table = dtype.Table; column.IsPrimaryKey = false; column.IsNullable = !RequiredValidator.IsRequired(memberInfo); column.DbType = DbTypeMapper.Parse(pk.Member.ReturnType); if (column.IsString) { column.Length = StringLengthValidator.GetMaxLength(pk.Member.FinalMemberInfo); } dtype.Table.Columns.Add(column); foreignKey.Columns.Add(new Tuple<Column, Column>(column, pk.Column)); //create datamember dtype.AddMember(memberInfo.Name + "." + pk.Member, column); } dtype.Table.ForeignKeys.Add(foreignKey); } //just map as a atomic value else { Column column = new Column(); column.Name = memberInfo.Name; column.Table = dtype.Table; column.IsNullable = !RequiredValidator.IsRequired(memberInfo); column.IsPrimaryKey = false; //create datamember DataMember dmember = dtype.AddMember(memberInfo.Name, column); //is this a regular atomic value? if (DbTypeMapper.DbTypeMap.ContainsValue(returnType) && returnType != typeof(object)) { column.DbType = DbTypeMapper.Parse(returnType); } else if (returnType.GetTypeInfo().IsEnum) { column.DbType = DbType.Int32; } //this is an non-atomic object, but its not mapped as a DataType, so we serialize it as json else { column.DbType = DbType.String; dmember.Converter = new Conversions.Json<object>(); } if (column.IsString) { column.Length = Data.Validation.StringLengthValidator.GetMaxLength(memberInfo); } dtype.Table.Columns.Add(column); } } yield return dtype; } }
/// <summary> /// Returns the Sql sentence for dropping a foreign key in the specified Table /// </summary> /// <param name="index">Foreign key that will be dropped</param> /// <returns>Sql query for dropping the foreign key</returns> public virtual Command Drop(ForeignKey foreigkKey) { return "ALTER TABLE " + EncloseName(foreigkKey.Table.Name) + " DROP FOREIGN KEY " + EncloseName(foreigkKey.Name); }
/// <summary> /// Returns the Sql sentence for adding a foreign key in the specified Table /// </summary> /// <param name="index">Doreign key that will be added</param> /// <returns>Sql query for adding the foreign key</returns> public virtual Command Create(ForeignKey foreigkKey) { return "ALTER TABLE " + EncloseName(foreigkKey.Table.Name) + " ADD " + ForeignKeyDefinition(foreigkKey); }
/// <summary> /// Creates a Sql sentence for creating a foreign key /// </summary> /// <param name="column">Column that represents a foreign key</param> /// <returns>Sql sentence for creating a foreign key in the database</returns> protected virtual Command ForeignKeyDefinition(ForeignKey foreigkKey) { //Validate if column is a foreign key if (foreigkKey == null) throw new ArgumentNullException("foreigkKey"); //sql script that wil be generated string sql = "CONSTRAINT " + EncloseName(foreigkKey.Name) + " FOREIGN KEY ("; //add local columns foreach (Tuple<Column, Column> columnTuple in foreigkKey.Columns) { sql += EncloseName(columnTuple.Item1.Name) + ", "; } //remove last comma and close parenthesis sql = sql.TrimEnd(',', ' '); sql += ") REFERENCES " + EncloseName(foreigkKey.RemoteTable.Name) + "("; //add list of local columns foreach (Tuple<Column, Column> columnTuple in foreigkKey.Columns) { sql += EncloseName(columnTuple.Item2.Name) + ", "; } //Removing the last ", " sql = sql.TrimEnd(',', ' '); sql += ")"; //Returning the sentence return sql; }
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)); }