locked
Create a good dal RRS feed

  • Question

  • Hi,

    Iam looking for a good DAL. At the moment we got a little internal application but the DAL is not correct.

    This is the get section.

    // #################
    // Customer.
    // #################
    public static Customer getCustomer(Guid customerID)
    {
    	SqlConnection MyConnection = new SqlConnection(GetConnectionString);
    	SqlCommand MyCommand = new SqlCommand("sp_selectCustomerByID", MyConnection);
    
    	// Mark the command as storedprocedure.
    	MyCommand.CommandType = CommandType.StoredProcedure;
    
    	// Add parameters to storedprocedure.
    	SqlParameter ParameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.UniqueIdentifier);
    	ParameterCustomerID.Value = customerID;
    	MyCommand.Parameters.Add(ParameterCustomerID);
    
    	// Open Connection.
    	MyConnection.Open();
    
    	Customer objCustomer = null;
    	using (SqlDataReader dr = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)) {
    		while (dr.Read) {
    			objCustomer = new Customer();
    			objCustomer.ID = (Guid)dr("CustomerID");
    			objCustomer.FirstName = (string)dr("FirstName");
    			objCustomer.MiddleName = (string)object.ReferenceEquals(dr("MiddleName"), DBNull.Value) ? string.Empty : dr("MiddleName");
    			objCustomer.LastName = (string)object.ReferenceEquals(dr("LastName"), DBNull.Value) ? string.Empty : dr("LastName");
    			objCustomer.Orders = getOrders(objCustomer);
    			objCustomer.Created = (DateTime)dr("CreatedDate");
    		}
    	}
    
    	// House Cleaning.
    	MyConnection.Dispose();
    	MyCommand.Dispose();
    
    	return objCustomer;
    }
    
    // #################
    // Orders.
    // #################
    private static Orders getOrders(Customer objCustomer)
    {
    	SqlConnection MyConnection = new SqlConnection(GetConnectionString);
    	SqlCommand MyCommand = new SqlCommand("sp_selectOrdersByCustomer", MyConnection);
    
    	// Mark the command as storedprocedure.
    	MyCommand.CommandType = CommandType.StoredProcedure;
    
    	// Add parameters to storedprocedure.
    	SqlParameter ParameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.UniqueIdentifier);
    	ParameterCustomerID.Value = objCustomer.ID;
    	MyCommand.Parameters.Add(ParameterCustomerID);
    
    	// Open Connection.
    	MyConnection.Open();
    
    	Orders lOrders = new Orders();
    	Order objOrder = null;
    
    	using (SqlDataReader dr = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)) {
    		while (dr.Read) {
    			objOrder = new Order();
    			objOrder.ID = dr("OrderID");
    			objOrder.CustomerID = dr("CustomerID");
    			objOrder.OrderNumber = dr("Number").ToString();
    			objOrder.InvoiceAddress = getAddress(dr("InvoiceAddressID"));
    			objOrder.OrderDetails = getOrderDetails(objOrder, objCustomer);
    			objOrder.Note = dr("Note").ToString;
    			objOrder.Created = (DateTime)dr("CreatedDate");
    
    			lOrders.Add(objOrder);
    		}
    	}
    
    	// House Cleaning.
    	MyConnection.Dispose();
    	MyCommand.Dispose();
    
    	return lOrders;
    }
    
    // #################
    // Order Details.
    // #################
    private static OrderDetails getOrderDetails(Order objOrder, Customer objCustomer)
    {
    	SqlConnection MyConnection = new SqlConnection(GetConnectionString);
    
    	SqlCommand MyCommand = new SqlCommand("sp_selectOrderDetailsByOrder", MyConnection);
    
    	// Mark the command as storedprocedure.
    	MyCommand.CommandType = CommandType.StoredProcedure;
    
    	// Add parameters to storedprocedure.
    	SqlParameter ParameterOrderID = new SqlParameter("@OrderID", SqlDbType.UniqueIdentifier);
    	ParameterOrderID.Value = objOrder.ID;
    	MyCommand.Parameters.Add(ParameterOrderID);
    	
    	// Open Connection.
    	MyConnection.Open();
    
    	OrderDetails lOrderDetails = new OrderDetails();
    	OrderDetail objOrderDetail = null;
    
    	using (SqlDataReader dr = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)) {
    		while (dr.Read) {
    			objOrderDetail = new OrderDetail();
    			objOrderDetail.ID = dr("OrderDetailID");
    			objOrderDetail.OrderID = dr("OrderID");
    			objOrderDetail.Product = getProduct(dr("ProductID"), objCustomer);
    			objOrderDetail.Note = dr("Note").ToString;
    			objOrderDetail.Status = dr("Status");
    			objOrderDetail.Created = dr("CreatedDate");
    
    			lOrderDetails.Add(objOrderDetail);
    		}
    	}
    	
    	// House Cleaning.
    	MyConnection.Dispose();
    	MyCommand.Dispose();
    
    	return lOrderDetails;
    }

    This is the save section

    // #################
    // Save Customer.
    // #################
    public static void setCustomer(Customer objCustomer)
    {
    	SqlConnection MyConnection = new SqlConnection(GetConnectionString);
    	SqlCommand MyCommand = new SqlCommand("sp_insertCustomer", MyConnection);
    
    	// Mark the command as storedprocedure.
    	MyCommand.CommandType = CommandType.StoredProcedure;
    
    	// Add parameters to storedprocedure.
    	SqlParameter ParameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.UniqueIdentifier);
    	ParameterCustomerID.Value = objCustomer.ID;
    	MyCommand.Parameters.Add(ParameterCustomerID);
    	
    	SqlParameter ParameterFirstName = new SqlParameter("@FirstName", SqlDbType.NVarChar);
    	ParameterFirstName.Value = objCustomer.FirstName;
    	MyCommand.Parameters.Add(ParameterFirstName);
    	
    	SqlParameter ParameterMiddleName = new SqlParameter("@MiddleName", SqlDbType.NVarChar);
    	if (string.IsNullOrWhiteSpace(objCustomer.MiddleName)) {
    		ParameterMiddleName.Value = DBNull.Value;
    	} else {
    		ParameterMiddleName.Value = objCustomer.MiddleName;
    	}
    	MyCommand.Parameters.Add(ParameterMiddleName);
    
    	SqlParameter ParameterLastName = new SqlParameter("@LastName", SqlDbType.NVarChar);
    	ParameterLastName.Value = objCustomer.LastName;
    	MyCommand.Parameters.Add(ParameterLastName);
    
    	// Open Connection.
    	MyConnection.Open();
    
    	// Execute Update/Insert Query.
    	MyCommand.ExecuteNonQuery();
    
    	// Close Connection.
    	MyConnection.Close();
    
    	// Save Orders.
    	setOrders(objCustomer.Orders);
    
    	// House Cleaning.
    	MyConnection.Dispose();
    	MyCommand.Dispose();
    }
    
    // #################
    // Save Order.
    // #################
    public static void setOrders(Orders lOrders)
    {
    	SqlConnection MyConnection = new SqlConnection(GetConnectionString);
    	SqlCommand MyCommand = new SqlCommand("sp_insertOrder", MyConnection);
    
    	// Mark the command as storedprocedure.
    	MyCommand.CommandType = CommandType.StoredProcedure;
    
    	// Add parameters to storedprocedure.
    	SqlParameter ParameterOrderID = default(SqlParameter);
    	SqlParameter ParameterCustomerID = default(SqlParameter);
    	SqlParameter ParameterOrderNumber = default(SqlParameter);
    
    	// Open Connection.
    	MyConnection.Open();
    
    	foreach (Order objOrder in lOrders) {
    		// Save Invoice Address.
    		setAddress(objOrder.InvoiceAddress);
    
    		ParameterOrderID = new SqlParameter("@OrderID", SqlDbType.UniqueIdentifier);
    		ParameterOrderID.Value = objOrder.ID;
    		MyCommand.Parameters.Add(ParameterOrderID);
    
    		ParameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.UniqueIdentifier);
    		ParameterCustomerID.Value = objOrder.CustomerID;
    		MyCommand.Parameters.Add(ParameterCustomerID);
    
    		ParameterOrderNumber = new SqlParameter("@Number", SqlDbType.Int);
    		ParameterOrderNumber.Value = objOrder.OrderNumber;
    		MyCommand.Parameters.Add(ParameterOrderNumber);
    
    		// Execute Update/Insert Query.
    		MyCommand.ExecuteNonQuery();
    
    		// Clear Parameters.
    		MyCommand.Parameters.Clear();
    
    		// Save OrderDetails.
    		setOrderDetails(objOrder.OrderDetails);
    	}
    
    	// Close Connection.
    	MyConnection.Close();
    
    	// House Cleaning.
    	MyConnection.Dispose();
    	MyCommand.Dispose();
    
    }

    How can we correct this?

    Thanks,

    Kind regards,

    Flaminio

    • Edited by Flaminio Thursday, October 28, 2010 4:01 PM Layout Issues
    Thursday, October 28, 2010 3:50 PM

All replies

  • Hello there,

    This is very important section of the database operation for your entire project. Some points I would like highlight here is that are given below.

    •      Point One : For all your database operation , please do create stored procedure for your application.
    •      Point Two : There should not be no hard coded in the c# code in the dal layer.
    •      Point Three: There should be separate class inside the dal for each kind of DML operation performed against the database.
    •      Point Four: All the custom class in the DAL layer, IDisposable interface needs inherited from all the custom class.
    •      Point  Five : Please do optimize your DAL code . It is very important.
    •      Point Six : Please do format c# code , code should be very represent able to senior people in your organization for code review.
    • Point Seven : Please business objects in the bll layer for getting and transmitting data from and out of the DAL Layer .

    Additional Point : Humble request , please do not show the code publically , the code that you have written  cost money , other wise know intellectual property for the software project.

    Hope this helps,

    Regards,

    Phijo Mathew Philip.

     

     


    PHIJO MP
    Thursday, October 28, 2010 5:59 PM
  • Hello there,

     

    What is a Data Access Layer?

    A Data Access Layer is a set of classes and functions for reading from and writing to a database or other data store. It does not contain any of the business logic for the application nor User Interface elements.

     

    It is part of a multi-layer application design that usually includes:

     

    • User Interface layer (UI) which contains screens and User Interface components
    • Business Logic layer (BLL) which contains the business rules for the application

     To return strongly-typed objects, developers can either create their own custom business objects or use Typed DataSets. A business object is implemented by the developer as a class whose properties typically reflect the columns of the underlying database table the business object represents. A Typed DataSet is a class generated for you by Visual Studio based on a database schema and whose members are strongly-typed according to this schema. The Typed DataSet itself consists of classes that extend the ADO.NET DataSet, DataTable, and DataRow classes. In addition to strongly-typed DataTables, Typed DataSets now also include TableAdapters, which are classes with methods for populating the DataSet's DataTables and propagating modifications within the DataTables back to the database.

     

    Regards,

    Phijo Mathew Philip.

    Thursday, October 28, 2010 6:25 PM
  • Hello there,

    Design principals in the data access layer

    The objective of the DAL is to provide data to your business objects without using database specific code. You accomplish this by exposing a series of data access methods from the DAL that operate on data in the data-tier using database specific code but do not expose any database specific method parameters or return types to the business tier. Any time a business object needs to access the data tier, you use the method calls in the DAL instead of calling directly down to the data tier. This pushes database-specific code into the DAL and makes your business object database independent.

    Now wait, you say, all you've accomplished is making the business objects dependent on the DAL. And since the DAL uses database-specific code, what's the benefit? The benefit is that the DAL resides in its own assembly and exposes database-independent method signatures. You can easily create another DAL with the same assembly name and an identical set of method signatures that supports a different database. Since the method signatures are the same, your code can interface with either one, effectively giving you two interchangeable assemblies. And since the assembly is a physical file referenced by your application and the assembly names are the same, interchanging the two is simply a matter of placing one or the other into your application's bin folder.

    The business tier

    And of course, this brings us to the topic of business objects and the Data Access Layer (also known as the DAL), two sub-layers within the business tier. A business object is a component that encapsulates the data and business processing logic for a particular business entity. It is not, however, a persistent storage mechanism. Since business objects cannot store data indefinitely, the business tier relies on the data tier for long term data storage and retrieval. Thus, your business tier contains logic for retrieving persistent data from the data-tier and placing it into business objects and, conversely, logic that persists data from business objects into the data tier. This is called data access logic.

    Some developers choose to put the data access logic for their business objects directly in the business objects themselves, tightly binding the two together. This may seem like a logical choice at first because from the business object perspective it seems to keep everything nicely packaged. You will begin noticing problems, however, if you ever need to support multiple databases, change databases, or even overhaul your current database significantly. Let's say, for example, that your boss comes to you and says that you will be moving your application's database from Oracle to SQL Server and that you have four months to do it. In the meantime, however, you have to continue supporting whatever business logic changes come up. Your only real option is to make a complete copy of the business object code so you can update the data access logic in it to support SQL Server.

    Design principals in the data access layer

    The objective of the DAL is to provide data to your business objects without using database specific code. You accomplish this by exposing a series of data access methods from the DAL that operate on data in the data-tier using database specific code but do not expose any database specific method parameters or return types to the business tier. Any time a business object needs to access the data tier, you use the method calls in the DAL instead of calling directly down to the data tier. This pushes database-specific code into the DAL and makes your business object database independent.

    Now wait, you say, all you've accomplished is making the business objects dependent on the DAL. And since the DAL uses database-specific code, what's the benefit? The benefit is that the DAL resides in its own assembly and exposes database-independent method signatures. You can easily create another DAL with the same assembly name and an identical set of method signatures that supports a different database. Since the method signatures are the same, your code can interface with either one, effectively giving you two interchangeable assemblies. And since the assembly is a physical file referenced by your application and the assembly names are the same, interchanging the two is simply a matter of placing one or the other into your application's bin folder.

    Note: You can also implement a DAL without placing it in a separate assembly if you build it against a DAL interface definition, but we will leave that to another article.

    Regards,

    Phijo Mathew Philip.

     


    PHIJO MP
    Thursday, October 28, 2010 6:30 PM
  • Hello there,

    In the sample c# code 

    SqlCommand MyCommand = new SqlCommand("sp_selectOrdersByCustomer", MyConnection)

     Please do remove the "sp_selectOrdersByCustomer" the hard coded string and put the stored procedure inside the resource file.

    Additional Information regarding the Resource file.

    Strings can be added to a resources file via the resources tab on the project properties. These files can be localized. You can "Add New Item" - "Resource File" and name it with culture string  before the resx extension. For example Welsh is cy-GB, so name the file Resources.cy-GB.resx. You can then access the resource in code  via My.Resources

     

    Resources and Localization

    http://msdn.microsoft.com/en-us/magazine/cc163609.aspx

     Regards,

    Phijo Mathew Philip.


    PHIJO MP
    Thursday, October 28, 2010 6:44 PM
  • Hello there,

    Dispose interface inside the DAL Layer :

    Inside your class constructor, you can instantiate your DB connections.

    Then inside your IDisposable.Dispose Method, you write your tear down code for closing your DB connections.

    class TestProgram : IDisposable

    {

      public void Dispose()

        {

            if (Connection1 != null)

            {

                Connection1.Dispose();

            }

        }

    }

    Regards,

    Phijo Mathew Philip.

     


    PHIJO MP
    Thursday, October 28, 2010 7:02 PM
  • Hello there,

    In the sample c# code which is given below

    using (SqlDataReader dr = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)) {

                                    while (dr.Read) {

                                                    objOrderDetail = new OrderDetail();

                                                    objOrderDetail.ID = dr("OrderDetailID");

                                                    objOrderDetail.OrderID = dr("OrderID");

                                                    objOrderDetail.Product = getProduct(dr("ProductID"), objCustomer);

                                                    objOrderDetail.Note = dr("Note").ToString;

                                                    objOrderDetail.Status = dr("Status");

                                                    objOrderDetail.Created = dr("CreatedDate");

     

                                                    lOrderDetails.Add(objOrderDetail);

                                    }

                    }

                    Please do use a stored procedure for the database operation.

    Note 1:

    Why use stored procedures?

    Stored procedures offer several distinct advantages over embedding queries in your Graphical User Interface (GUI).

    Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.

    Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.

    Advantage 3: Stored procedures abstract or separate server-side functions from the client-side. It is much easier to code a GUI application to call a procedure than to build a query through the GUI code.

    Advantage 4: Stored procedures are usually written by database developers/administrator

    Advantage 5:Stored procedure are the precompiled statements and one of its biggest advantage is that it speed up the  performance of your application.

    Note 2:

    What is the advantage of using stored procedure over the SQL queries?

    Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements. A stored procedure is a precompiled executable object that contains one or more SQL statements. Hence you can replace your complex SQL statements with a single stored procedure. Since, stored procedures are precompiled objects they execute faster at the database server. Most of the time, stored procedures contain more than one command; in this case, the time to pass the individual commands to the database server from the program is saved. The database is issued just one command (to execute the stored procedure) and the DB server executes all the commands and returns the result in the end. Hence, the overall interaction time with the DB server reduces in a great deal. This can result in a huge optimization in case where the DB server is accessed via a slow network.

     

     

    Note 3:

    SQL SERVER – Stored Procedures Advantages and Best Advantage

    ·         Execution plan retention and reuse

    ·         Query auto-parameterization

    ·         Encapsulation of business rules and policies

    ·         Application modularization

    ·         Sharing of application logic between applications

    ·         Access to database objects that is both secure and uniform

    ·         Consistent, safe data modification

    ·         Network bandwidth conservation

    ·         Support for automatic execution at system start-up

    ·         Enhanced hardware and software capabilities

    ·         Improved security

    ·         Reduced development cost and increased reliability

    ·         Centralized security, administration, and maintenance for common routines.

    Note 4 :

    Benefits of using stored procedures

    Applications that use stored procedures have the following advantages:

    Reduced network usage between clients and servers

    A client application passes control to a stored procedure on the database server. The stored procedure performs intermediate processing on the database server, without transmitting unnecessary data across the network. Only the records that are actually required by the client application are transmitted. Using a stored procedure can result in reduced network usage and better overall performance.

    Applications that execute SQL statements one at a time typically cross the network twice for each SQL statement. A stored procedure can group SQL statements together, making it necessary to only cross the network twice for each group of SQL statements. The more SQL statements that you group together in a stored procedure, the more you reduce network usage and the time that database locks are held. Reducing network usage and the length of database locks improves overall network performance and reduces lock contention problems.

    Applications that process large amounts of SQL-generated data, but present only a subset of the data to the user, can generate excessive network usage because all of the data is returned to the client before final processing. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.

     

    Enhanced hardware and software capabilities

    Applications that use stored procedures have access to increased memory and disk space on the server computer. These applications also have access to software that is installed only on the database server. You can distribute the executable business logic across machines that have sufficient memory and processors.

    Improved security

    By including database privileges with stored procedures that use static SQL, the database administrator (DBA) can improve security. The DBA or developer who builds the stored procedure must have the database privileges that the stored procedure requires. Users of the client applications that call the stored procedure do not need such privileges. This can reduce the number of users who require privileges.

    Reduced development cost and increased reliability

    In a database application environment, many tasks are repeated. Repeated tasks might include returning a fixed set of data, or performing the same set of multiple requests to a database. By reusing one common procedure, a stored procedure can provide a highly efficient way to address these recurrent situations.

    Centralized security, administration, and maintenance for common routines

    By managing shared logic in one place at the server, you can simplify security, administration, and maintenance. Client applications can call stored procedures that run SQL queries with little or no additional processing.

    Regards,

    Phijo Mathew Philip.


    PHIJO MP
    Thursday, October 28, 2010 7:21 PM
  • I was pretty sure that the OP is using stored procedures...
    Thursday, October 28, 2010 7:37 PM
  • Hello there,

    the content is taken from using Statement

    http://msdn.microsoft.com/en-us/library/yh598w02.aspx

     As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

    The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler. The code example earlier expands to the following code at compile time (note the extra curly braces to create the limited scope for the object):

    You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.

     

    Regards,

    Phijo Mathew Philip.


    PHIJO MP
    Thursday, October 28, 2010 7:43 PM
  • Hi,

    Wow, that is a lot of information very usefull. Is there any sample DAL class where all of the comments mentioned above are in it?

    Kind regards,

    Flaminio

    Thursday, October 28, 2010 8:33 PM
  • Is it too late to substitute Entity Framework 4 into your app?

     

    Friday, October 29, 2010 9:58 AM