Exemplo n.º 1
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 UPDATE query for a single dataobject's Table
		/// </summary>
		/// <param name="table">
		/// Table considered in the Update
		/// </param>
		/// <param name="dobj">
		/// object instance for perform the update
		/// </param>
		/// <returns>
		/// Sql string for update the specified object
		/// </returns>
		public virtual Command Update(Update update)
		{
			//Validating if table argument is null
			if (update == null)
			{
				throw new ArgumentNullException("update");
			}

			//Creating Update sentence begin
			Command command = "UPDATE " + EncloseName(update.Table.Name) + " SET ";

			//Crossing DataMembers that aren't primary key
			foreach (var value in update.Set)
			{
				CommandParameter param = new CommandParameter(value.Value, "@" + value.Column.Name, value.Column.DbType);
				command.Parameters.Add(param);

				//Building the sql sentence...
				command.Script += 
					EncloseName(value.Column.Name) + 
					" = " + param.Name +
					", ";

			}

			//Remove the ", " if there is at least one updated property
			command.Script = command.Script.TrimEnd(',', ' ');

			//filters
			command += WhereClause(update.Where);
			command.Script += ScriptSeparator;

			//Returning the sentence
			return command;
		}