Customizing the data access layer

We have to do the same thing to the data access layer, that is, manually add the code to access the databases. In addition, we have to add the connection strings to the configuration file, and add references to the business entities project.

Adding the connection strings

For this service, we will still use the Northwind sample database. So, we first need to specify the connection strings to this database.

With Service Factory, the host application is under the MyWCF.EasyNorthwindTestsMyWCF.EasyNorthwind.Host folder. It will use the ASP.NET Development Server to host the WCF services, and the configuration file is called web.config. We need to modify this file to specify the connection string.

  • Open the web.config file in the host folder, and find the following node:
<connectionStrings/>
  • Change it to:
<connectionStrings configSource="connections.config"/>

This means that we will have a separate file to hold all of the connection strings, and this file is called connections.config.

The content of the file connections.config is: 

<?xml version="1.0" encoding="utf-8" ?>
<connectionStrings>
<add name="NorthwindConnectionString"
providerName="System.Data.SqlProvider"
connectionString="server=your_db_serveryour_db_instance;
uid=your_user_name; pwd=your_password;
database=Northwind;" />
</connectionStrings>

Just add a new item (XML file) to the host application project, name it connections.config, and put the above content inside the file and save it.

Or you can just replace the line<connectionStrings/> with the following lines in the web.config file, and not create a new file for this example:

<connectionStrings>
<add name="NorthwindConnectionString"
providerName="System.Data.SqlProvider"
connectionString="server=your_db_serveryour_db_instance;
uid=your_user_name; pwd=your_password;
database=Northwind;" />
</connectionStrings>

But remember to replace the database server name, db instance name, your user name, and your password, according to your database setup.

You can also use the following connectionString if you are using windows authentication:

connectionString="server=your_db_serveryour_db_instance; database=Northwind;Trusted_Connection=yes"

And use the following connectionString if you are using an integrated security connection:

connectionString="server=your_db_serveryour_db_instance; database=Northwind;Integrated Security=SSPI"

Adding a reference to the BusinessEntities project

Next, add a reference to the MyWCF.EasyNorthwind.BusinessEntities project in the data access project.

The data access layer needs to reference the BusinessEntities project, because this layer communicates with the business logic layer through business entities. It will retrieve information from the database, store the information in business entities, and pass the business entities back to the business logic layer. When saving the data back to the database, it will get the business entities from the business logic layer, connect to the database, and commit the changes back to the database.

Adding the data access class

Now, add a new class file to the data access project, name it ProductDAL.cs, and customize it as follows:

  1. Add three using statements for the BusinessEntities, SqlClient and Configuration namespaces.
  2. Change the file to be a public class.
  3. Add a new method, GetProduct, to retrieve a product from the database.

The final ProductDAL class file should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyWCF.EasyNorthwind.BusinessEntities;
using System.Data.SqlClient;
using System.Configuration;
namespace MyWCF.EasyNorthwind.DataAccess
{
public class ProductDAL
{
string connectionString = ConfigurationManager.ConnectionStrin gs["NorthwindConnectionString"].ConnectionString;
public ProductEntity GetProduct(int id)
{
ProductEntity p = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand comm = new SqlCommand();
comm.CommandText = "select * from Products where ProductID=" + id;
comm.Connection = conn;
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
p = new ProductEntity();
p.ProductID = id;
p.ProductName =
(string)reader["ProductName"];
p.QuantityPerUnit =
(string)reader["QuantityPerUnit"];
p.UnitPrice =
(decimal)reader["UnitPrice"];
p.UnitsInStock =
(short)reader["UnitsInStock"];
p.UnitsOnOrder =
(short)reader["UnitsOnOrder"];
p.ReorderLevel =
(short)reader["ReorderLevel"];
p.Discontinued =
(bool)reader["Discontinued"];
}
}
return p;
}
}
}

Again, build the data access project, to make sure that there is no build error.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset