Online Data Access Layer Code Engine
Blocks

The benefits

C# Crash Course for generated code

Until I have a complete guide available for you, this Crash Course will get you on the way.
I haven't checked for typo's, so there could be some in code samples!

Populating a record

You can populate a single record by creating an object and then calling a populate method: Customer customer = new Customer(); customer.PopulateByCustomerID(123);

or, by using a static method of the Customer object:

Customer customer = Customer.FindByCustomerID(123);

Populating a record collection

The following examples show how to get all records from the Customers table. Customers customers = new Customers(); customers.GetAllCustomers();

or, using a static method of the Customer object:

Customers customers = Customer.GetAllCustomers();

Using custom queries to get data

You can use custom queries to populate your objects using the SelectQueryBuilder class. Any custom query you create will be added to the default query that is built internally. Customers customers = new Customers(); SelectQueryBuilder query = new SelectQueryBuilder(); query.AddWhere("City", Comparison.Equals, "Amsterdam"); query.AddWhere("CompanyName", Comparison.Like, "a%"); query.AddOrderBy("CompanyName", Sorting.Ascending); customers.GetAllCustomers(query); or Customer customer = Customer.FindByCustomerID(123); SelectQueryBuilder query = new SelectQueryBuilder(); // Only get some columns from the Orders table query.SelectColumns("OrderID", "OrderDate", "ShippedDate"); // Executes query: SELECT OrderID,OrderDate,ShippedDate FROM Orders WHERE CustomerID=123 customer.Orders.AutoPopulate(query);

Using stored procedures to get data

Populating a collection from a stored procedure: Customers customers = new Customers(); customers.GetByStoredProcedure("sp_SelectCustomersWithOrders"); or, a single record: Customer customer = new Customer(); customer.PopulateByStoredProcedure("sp_SelectBestCustomer"); or, if you need to pass parameters on to a procedure: DbCommand command = DataAccess.GetUsedProviderFactory(ProjectGlobals.ProjectName).CreateCommand(); command.CommandText = "sp_SelectCustomer"; command.CommandType = CommandTypes.StoredProcedure; command.Parameters.AddWithValue("@CustomerID", 123); customer.PopulateByCommand(command);

Iterating through a collection

Customers customers = Customer.GetAllCustomers(); foreach (Customer customer in customers) { Console.WriteLine("Customer {0}: {1}", customer.CustomerID, customer.CompanyName); }

Binding to other objects

You can bind the CodeEngine objects as a datasource directly: myGridView.DataSource = customers; Or through an exported DataTable: myGridView.DataSource = customers.GetDataTable();

Related records

foreach (Order order in customer.Orders) { ... } or customer.Orders.AutoPopulate(); myDropDown.DataSource = customer.Orders; myDropDown.ValueMember = "OrderID"; myDropDown.DisplayMember = "Description"

Linked records

Imagine a many-to-many relationship from Teacher to Student, through linktable Teacher_Student. Teacher teacher = Teacher.FindByTeacherID(123); Student student = Student.FindByStudentID(321); // Add the student to the Students collection teacher.Students.Add(student); // Call Update to create the record in the Teacher_Student linktable, // with TeacherID=123, StudentID=321 teacher.Students.Update(); // Delete all records from Teacher_Student where TeacherID=123 teacher.Students.Delete();

Deleting a single record

You can delete a record by setting its PK fields to the right values and calling the Delete method: Order order = new Order(); order.OrderID = orderToBeDeletedID; order.Delete(); or, by populating the object first: Order order = Order.FindByOrderID(orderToBeDeletedID); order.Delete(); or, from any collection, using its index: currentCustomer.Orders[orderToBeDeletedIndex].Delete(); or, from any collection, using the object's PK value(s): currentCustomer.Orders.ItemByOrderID(orderToBeDeletedID).Delete();

Deleting multiple records

To delete all customers in the database: Customers customers = Customer.GetAllCustomers(); customers.Delete(); To delete all orders belonging to a customer: currentCustomer.Orders.Delete(); // DELETE FROM Orders WHERE CustomerID=..

Counting records

The generated code performs counts in different ways. If a collection has been populated with data from the database, the Count property simply returns the number of records in that collection. If it hasn't been populated yet, it performs a Count query on the database once. Sequential calls to the Count property simply return a cached number. Customer customer = Customer.FindByID(123); // First count call executes a SELECT count() query to get the amount of records Response.Write("Number of orders for this customer: " + customer.Orders.Count); // Sequential counts return a cached count number Response.Write("Number of orders for this customer: " + customer.Orders.Count);

or, on a populated collection:

Customer customer = Customer.FindByID(123); // Populates the related Orders collection from database and loops through it foreach (Order order in customer.Orders) { ... } // No SQL count now: simply returns number of records in populated collection MessageBox.Show("Orders: " + customer.Orders.Count);

DataTables and CodeEngine classes

CodeEngine classes can interact with DataTables. To export your data to a DataTable, use: myGridView.DataSource = customers.GetDataTable(); And, to import from a DataTable: customers.FillFromDataTable( (DataTable)myGridView.DataSource );

Executing miscellanious SQL commands

You can use te DataAccess class to do miscellanious database tasks. In combination with the QueryBuilder classes (SelectQueryBuilder, UpdateQueryBuilder, InsertQueryBuilder and DeleteQueryBuilder) it is extermely easy to perform those tasks. // Get the appropriate connection info for your project from the app/web.config DataAccess dataAccess = new DataAccess(ProjectGlobals.ProjectName); DeleteQueryBuilder deleteQuery = new DeleteQueryBuilder(dataAccess.GetUsedProviderFactory); deleteQuery.Table = "Customers"; deleteQuery.AddWhere("CustomerID", Comparison.GreaterThan, 123); // Build a DbCommand object containing the DELETE query and Parameters DbCommand command = deleteQuery.BuildCommand(); // Executes the command and returns the number of records deleted long rowsAffected = dataAccess.ExecuteSqlReturnRowsAffected(command); or, execute some select statement and fill a DataSet with the returned records DataSet myDataSet = dataAccess.ExecuteSqlReturnDataSet(command); or, execute some select statement and get a DataReader DbDataReader myReader = dataAccess.ExecuteSqlReturnReader(command); or, execute some insert statement and return the inserted ID: DataAccess dataAccess = new DataAccess(ProjectGlobals.ProjectName); InsertQueryBuilder insertQuery = new InsertQueryBuilder(dataAccess.GetUsedProviderFactory); insertQuery.Table = "Customer"; insertQuery.SetField("CompanyName", "Some Company"); insertQuery.SelectIdentity = true; int newID = (int)dataAccess.ExecuteSqlReturnScalar(insertQuery.BuildCommand()); or, call a Stored Procedure with your own built DbCommand: DbCommand command = DataAccess.GetUsedProviderFactory(ProjectGlobals.ProjectName).CreateCommand(); command.CommandText = "sp_UpdateCustomer"; command.CommandType = CommandTypes.StoredProcedure; command.Parameters.Add(..); long rowsAffected = dataAccess.ExecuteSqlReturnRowsAffected(command);