예제 #1
0
		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);
		}
예제 #2
0
		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);
		}
예제 #3
0
		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);
		}
예제 #4
0
		public DataType(Type innerType, Table table)
		{
			if (innerType == null)
			{
				throw new ArgumentNullException("innerType");
			}

			InnerType = innerType;

			if (table == null)
			{
				CreateTable();
			}
			else
			{
				Table = table;
			}
		}
예제 #5
0
		/// <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;
			}
		}
예제 #6
0
		/// <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()";
		}
예제 #7
0
		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));
		}
예제 #8
0
		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));
		}
예제 #9
0
		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);
		}
예제 #10
0
		/// <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));
		}
예제 #11
0
		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);
				}
			}

		}
예제 #12
0
		protected override Command AutoIncrementalFunction(Table table)
		{
			throw new NotImplementedException();
		}
예제 #13
0
		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;
		}
예제 #19
0
		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);
		}
예제 #20
0
		/// <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();
				}
			}
		}
예제 #21
0
		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));
		}
예제 #22
0
		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>();
		}
예제 #23
0
		/// <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;
		}
예제 #24
0
		/// <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;
		}
예제 #25
0
		/// <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();
				}
			}
		}
예제 #26
0
		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));
		}
예제 #27
0
		/// <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";
		}