Introduction
This is the first version of DBLayer Wizard. It's a tool to generate the Data Layer in N-tier architectures in CS file format. This helps developers to save their time from writing the same code every time they work in a new project. We tried to provide the best ways in coding and data access patterns to satisfy speed, ease of use, reliability, robustness, and portability.
As we stated above, our main goal for this tool was to save developer time in N-tier applications using .NET languages. We also tried very hard to cover many methods for data access; for that we studied many data layer frameworks, so we provide here many ways and options for database operations.
Features
- Creates a class that is called
DB
which includes common data access functionality in any application like executing in-line SQL, executing a Stored Procedure and executing a transaction.
- Creates a class for each table that performs the most common operations like select by primary key, select by foreign key, select all records, insert, update and delete.
- Creates a class to execute Stored Procedures. For each Stored Procedure, gives the ability to return (
DataSet
, DataReader
, or a return value).
- Generated code is based on templates so developer can customize with save and open customized templates.
- All generated CS files saved to folder that you choose.
Using the DataLayer wizard code generator
- Define SQL Server information (server name, authentication method, database name).
- Select tables for which you want to generate a .NET class for.
- Select Stored Procedures for which you want to generate methods for.
- Select the return data types for tables [
DataSet
or DataReader
or both] and for Stored Procedures.
- Define the template which will be used to generate the .NET code.
- Define the folder which the code will be generated in.
Using the generated code from your application
Here we will see some examples of how to use the generated code. We are working on the NorthWind as the sample DB. This should act as a guide for the class developer.
- How to execute in-line SQL:
DataSet ds=DBLayer.DB.Execute("SELECT * FROM Customers");
object result=DBLayer.DB.ExecuteScalar("SELECT COUNT(*) FROM Customers");
int AffectedRows=DBLayer.DB.ExecuteNonQuery(insert sql statment);
As you see above, all methods are static
and the base namespace is DBlayer
.
- How to insert into a table:
As you will see, in the insert operation, we have three different ways for inserting.
bool result= DBLayer.DBTableShippers.Insert("New Tech","123456");
DataRow row=DBLayer.DBTableShippers.GetSchema();
row["CompanyName"]="New Tech";
row["Phone"]="123456";
bool result= DBLayer.DBTableShippers.Insert(row);
DataTable table=DBLayer.DBTableShippers.GetSchemaTable();
DataRow row1=table.NewRow();
row1["CompanyName"]="New Tech";
row1["Phone"]="123456";
table.Rows.Add(row1);
DataRow row2=table.NewRow();
row2["CompanyName"]="New Tech";
row2["Phone"]="123456";
table.Rows.Add(row2);
bool result= DBLayer.DBTableShippers.Insert(table);
- How to update data in a table:
bool result=DBLayer.DBTableShippers.Update(1,"Home","12365");
DataRow row=DBLayer.DBTableShippers.GetSchema();
row["ShipperID"]=1;
row["CompanyName"]="New Tech";
row["Phone"]="123456";
bool result=DBLayer.DBTableShippers.Update(row);
DataTable table=DBLayer.DBTableShippers.GetSchemaTable();
DataRow row1=table.NewRow();
row1["ShipperID"]=1;
row1["CompanyName"]="New Tech";
row1["Phone"]="123456";
table.Rows.Add(row1);
DataRow row2=table.NewRow();
row2["ShipperID"]=2;
row2["CompanyName"]="Ok";
row2["Phone"]="123456";
table.Rows.Add(row2);
bool result=DBLayer.DBTableShippers.Update(table);
- How to delete data from a table:
int DeletedRows=DBLayer.DBTableOrders.DeleteAll();
bool result=DBLayer.DBTableOrders.DeleteByPK(int.Parse(txtPK.Text));
bool result=DBLayer.DBTableOrders.DeleteByFK(txtFK.Text);
- How to select from a table:
DataSet ds= DBLayer.DBTableOrders.SelectAll();
DataSet ds= DBLayer.DBTableOrders.SelectByPK(int.Parse(txtPKds.Text));
DataSet ds= DBLayer.DBTableOrders.SelectByFK(txtFKds.Text);
- How to execute a transaction:
bool result=DBLayer.DB.ExecuteTransaction(txtSQL.Lines);
UML class diagram for the data layer framework classes and methods
The common base class:
Table class: