DBLayer Wizard V 1.0

//#***Header//#***
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DBLayer
{
	/// <summary>
	/// Summary description for DBTableData.
	/// </summary>
	public class DBTable[@TableName@]:DB
	{
		private DBTable[@TableName@]()
		{
			 
		}
		//#***HeaderEnd//#***
		//#***SelectByPK//#***
		public static DataSet SelectByPK([@FunctionParameters@])
		{
			string SQLQuery="SELECT * FROM [[@TableName@]] WHERE [@Where@]";

			SqlCommand command=new SqlCommand(SQLQuery,Connection);
			[@CommandParameters@]

			return Execute(command);
		}
	//#***SelectByPKEnd//#***
	
	//#***SelectByPKReader//#***
		public static SqlDataReader SelectByPKReader([@FunctionParameters@])
		{
				string SQLQuery="SELECT * FROM [[@TableName@]] WHERE [@Where@]";

				SqlCommand command=new SqlCommand(SQLQuery,Connection);
				[@CommandParameters@]

				return ExecuteReader(command);	
		}
	//#***SelectByPKReaderEnd//#***
	
	//#***SelectByFK//#***
		public static DataSet SelectByFK([@FunctionParameters@])
		{
			string SQLQuery="SELECT * FROM [[@TableName@]] WHERE [@Where@]";

			SqlCommand command=new SqlCommand(SQLQuery,Connection);
			[@CommandParameters@]

			return Execute(command);
		}
	//#***SelectByFKEnd//#***
	
	//#***SelectByFKReader//#***
		public static SqlDataReader SelectByFKReader([@FunctionParameters@])
		{
				string SQLQuery="SELECT * FROM [[@TableName@]] WHERE [@Where@]";

				SqlCommand command=new SqlCommand(SQLQuery,Connection);
				[@CommandParameters@]

				return ExecuteReader(command);	
		}
	//#***SelectByFKReaderEnd//#***
	
	//#***SelectAll//#***
		public static DataSet SelectAll()
		{
			return Execute("SELECT * FROM [[@TableName@]]");
		}
	//#***SelectAllEnd//#***
	//#***SelectAll_Columns//#***
		public static DataSet SelectAll(params string []Columns)
		{
			string col="";
			foreach(string str in Columns)
				col+="["+str+"],";
			col=col.Remove(col.Length-1,1);

			return Execute("SELECT "+col+" FROM [[@TableName@]]");
		}
	//#***SelectAll_ColumnsEnd//#***

//#***SelectAllReader//#***
	public static SqlDataReader SelectAllReader()
		{
	return ExecuteReader("SELECT * FROM [[@TableName@]]");
		}
//#***SelectAllReaderEnd//#***

//#***SelectAllReader_Columns//#***
	public static SqlDataReader SelectAllReader(params string []Columns)
		{
	string col="";
	foreach(string str in Columns)
	col+="["+str+"],";
	col=col.Remove(col.Length-1,1);

	return ExecuteReader("SELECT "+col+" FROM [[@TableName@]]");
		}
//#***SelectAllReader_ColumnsEnd//#***

	//#***Select//#***
		public static DataSet Select(string Sql)
		{
			return Execute(Sql);
		}
	//#***SelectEnd//#***

	//#***Insert_DataRow//#***
		public static bool Insert(DataRow Row)
		{
			SqlCommand command=new SqlCommand("",Connection);

			string Columns="",Values="";
			for(int i=0;i<Row.Table.Columns.Count;i++)
			{
				if(Row[i].ToString().Length!=0 && 
					!Row.Table.Columns[i].AutoIncrement &&
					Row.Table.Columns[i].Expression.Length==0)
				{
					Columns+="["+Row.Table.Columns[i].ColumnName+"],";
					Values+="@"+Row.Table.Columns[i].ColumnName+",";
					command.Parameters.Add("@"+Row.Table.Columns[i].ColumnName,Row[i]);
				}
			}
			
			Columns=Columns.Remove(Columns.Length-1,1);
			Values=Values.Remove(Values.Length-1,1);

			command.CommandText="INSERT INTO [[@TableName@]] ( "+Columns+" ) VALUES	( "+Values+" )";
			
			int RowsAffected=ExecuteNonQuery(command);
			if(RowsAffected==1)
				return true;
			else return false;
		}
//#***Insert_DataRowEnd//#***

//#***Insert_DataTable//#***
		public static bool Insert(DataTable Table)
		{
			for(int i=0;i<Table.Rows.Count;i++)
			{
				try
				{
					Insert(Table.Rows[i]);
				}
				catch(Exception ex)
				{
					throw ex;
				}
			}
			return true;
		}
//#***Insert_DataTableEnd//#***
//#***Insert_Columns//#***
		public static bool Insert([@FunctionParameters@])
		{
			string SQLQuery="INSERT INTO [[@TableName@]] ( [@Columns@] ) VALUES	([@ColumnsParameters@])";

			SqlCommand command=new SqlCommand(SQLQuery,Connection);
			[@CommandParameters@]
			
			int RowsAffected=ExecuteNonQuery(command);
			if(RowsAffected==1)
			return true;
			else return false;
		}
//#***Insert_ColumnsEnd//#***

//#***Update_DataRow//#***
		public static bool Update(DataRow Row)
		{
			SqlCommand command=new SqlCommand("",Connection);
			string ColumnsAndValues="",Where="";
			for(int i=0;i<Row.Table.Columns.Count;i++)
			{
				if(Row[i].ToString().Length!=0)
				{
					bool IsColumnPartOfPK=false;
					for(int x=0;x<Row.Table.PrimaryKey.Length;x++)
					{
						if(Row.Table.Columns[i].Caption==Row.Table.PrimaryKey[x].Caption)
						{
							IsColumnPartOfPK=true;
							break;
						}
					}
					if(IsColumnPartOfPK)
					{
						Where+="["+Row.Table.Columns[i].ColumnName+"]=@"+Row.Table.Columns[i].ColumnName+",";
					}
					else
					{
						ColumnsAndValues+="["+Row.Table.Columns[i].ColumnName+"]=@"+Row.Table.Columns[i].ColumnName+",";
					}
					command.Parameters.Add("@"+Row.Table.Columns[i].ColumnName,Row[i]);
				}
			}
			if(ColumnsAndValues.Length>0)
			ColumnsAndValues=ColumnsAndValues.Remove(ColumnsAndValues.Length-1,1);
			if(Where.Length>0)
			Where=Where.Remove(Where.Length-1,1);

			command.CommandText="UPDATE [[@TableName@]] SET "+ColumnsAndValues+" WHERE "+Where;

			int RowsAffected=ExecuteNonQuery(command);
			if(RowsAffected==1)
				return true;
			else return false;
		}
//#***Update_DataRowEnd//#***
//#***Update_DataTable//#***
		public static bool Update(DataTable Table)
		{
			for(int i=0;i<Table.Rows.Count;i++)
			{
				try
				{
					Update(Table.Rows[i]);
				}
				catch(Exception ex)
				{
					throw ex;
				}
			}
			return true;
		}
//#***Update_DataTableEnd//#***
//#***Update_Columns//#***
		public static bool Update([@FunctionParameters@])
		{
			string SQLQuery="UPDATE [[@TableName@]] SET [@Update@] WHERE [@Where@]";

			SqlCommand command=new SqlCommand(SQLQuery,Connection);
			[@CommandParameters@]
			
			int RowsAffected=ExecuteNonQuery(command);
			if(RowsAffected==1)
				return true;
			else return false;
		}
//#***Update_ColumnsEnd//#***

//#***DeleteByPK//#***
		public static bool DeleteByPK([@FunctionParameters@])
		{
			string SQLQuery="DELETE FROM [[@TableName@]] WHERE [@Where@]";

			SqlCommand command=new SqlCommand(SQLQuery,Connection);
			[@CommandParameters@]

			int RowsAffected=ExecuteNonQuery(command);
			if(RowsAffected==1)
				return true;
			else return false;
		}
//#***DeleteByPKEnd//#***
//#***DeleteByFK//#***
		public static bool DeleteByFK([@FunctionParameters@])
		{
			string SQLQuery="DELETE FROM [[@TableName@]] WHERE [@Where@]";

			SqlCommand command=new SqlCommand(SQLQuery,Connection);
			[@CommandParameters@]

			int RowsAffected=ExecuteNonQuery(command);
			if(RowsAffected==1)
				return true;
			else return false;
		}
//#***DeleteByFKEnd//#***
//#***DeleteAll//#***
		public static int DeleteAll()
		{
			return ExecuteNonQuery("DELETE FROM [[@TableName@]]");
		}
//#***DeleteAllEnd//#***

		//#***GetSchema//#***
		public static DataRow GetSchema()
		{
			string SQLQuery="SELECT TOP 0 * FROM [@TableName@]";
			SqlCommand Command=new SqlCommand(SQLQuery,Connection);
			SqlDataAdapter adapter=new SqlDataAdapter(Command);
			DataTable TableSchema=new DataTable("[@TableName@]");
			adapter.FillSchema(TableSchema,SchemaType.Mapped);
			return TableSchema.NewRow();
		}
//#***GetSchemaEnd//#***
//#***GetSchemaTable//#***
		public static DataTable GetSchemaTable()
		{
			string SQLQuery="SELECT TOP 0 * FROM [@TableName@]";
			SqlCommand Command=new SqlCommand(SQLQuery,Connection);
			SqlDataAdapter adapter=new SqlDataAdapter(Command);
			DataTable TableSchema=new DataTable("[@TableName@]");
			adapter.FillSchema(TableSchema,SchemaType.Mapped);
			return TableSchema.Clone();
		}
//#***GetSchemaTableEnd//#***
//#***Footer//#***
	}
}
//#***FooterEnd//#***