1. Connecting Model to Data Source
To begin with, create a new BDC
Model project and give it a proper name. Here it is named
BdcCustomersModel. To make it easy to use the data obtained from the Customers
database, the best way is to add a LINQ to SQL model. LINQ is not the
fastest implementation method performance-wise, so if performance is
critical, you might prefer to implement a dedicated data adaptor
instead.
Select the project by left-clicking it.
Click Add New Item on the projects menu to open the Add New Item dialog.
Select Data templates from the Installed Templates panel.
Choose the LINQ to SQL Classes project type from the Templates panel.
Give the project a proper name—here it is called "Customer"—and then click Add.
Open the Server Explorer, and add a connection to the Customers database.
Drag Customers tableand drop it on the Customer.dbml design surface.
At this point, a designer class named CustomerDataContext
is automatically added. To allow a connection to be created using a
custom connection string, a new class should be added. Name the class CustomerDataContext.cs. Make this class a partial class. Then pass the connection string to the base class through the constructor, as in Listing 1.
Example 1. Data Context for Connecting to a Database with a Custom Connection String
public partial class CustomerDataContext
{
private const string ConnectionString = @"Data Source=localhost\SQLEXPRESS;Initial
Catalog=CustomersDatabase;Integrated Security=True;Pooling=False";
public CustomerDataContext() :
base(ConnectionString, mappingSource)
{
OnCreated();
}
}
|
NOTE
We made the connection
string a constant in the code for exemplifying it. In a production
environment, it should be added to the Web.Config
file in encrypted format. When using your own database, the connection
string should be modified to match your database and credential
requirements.
2. Mapping BDC Model to Data Source
At this point, the BDC model
should be mapped to the data source. This involves making a number of
entities and specifying appropriate methods. The purpose is to create
the interpretation layer between the database and the BDC model. First
an entity with an identifier key needs to be created:
An entity named "Entity1" is automatically created. Delete it.
Create a new entity. This can be done using "Drag and Drop" on the Entity from Toolbox and dropping it on the design surface.
Change the default name of the entity to "Customer."
Create
a new identifier named, CustomerKey, on the entity "Customer." This is
most easily done by right-clicking the entity and selecting the Add
Identifier option. This adds a new identifier to the entity.
Give the identifier the name "CustomerKey".
To add functionality, a
number of method instances must be added to the entity. This is most
easily done by selecting the entity and clicking the <Add a
Method> button that appears in the Method Details panel. Create a
Specific Finder method on the entity. This will add the ReadItem, as shown in Figure 1.
As shown in the Method Details panel, the ReadItem method has two parameters, namely an In parameter, which takes the identifier key, and a Return
parameter, which is an object instance of the identity type. VS2010
offers some functionality for making it easy to create new methods by
copying type descriptors automatically when possible. Therefore it is a
good idea to configure those for the Specific Finder method before
adding the other methods.
To complete the identifier key configuration, the type descriptor for the return parameter named CustomerKey should be added.
Open the Method Details panel.
Choose the <Edit> command from the type descriptor menu named CustomerKey.
In the BDC Explorer, add a type descriptor by right-clicking the CustomerKey, as shown in Figure 2, and choose the Add Type Descriptor option. This will create a new type descriptor.
Rename the just-created type descriptor to "CustomerKey", using the Properties panel.
Change the Identifier property to CustomerKey, as in Figure 3. This is how the BCS runtime knows that this type descriptor maps to the CustomerKey identifier.
Change the Type Name property to match the type from the LINQ model. In this example, it is not required to change it.
Repeat steps 1–6 for all required type descriptors.
When all type descriptors are added as in steps 1 through 7, the type descriptors shown in Figure 4 should be visible. It is always a good idea to check the spelling and Type Name properties at this point, as updating them later on can be a pain. Refactoring does not currently support this.
At this point, the other methods available need to be created the same as the ReadItem
(Specific Finder) method. These are the methods that support the BDC
operations that are the CRUD operations. To do this, repeat the steps in
this section for each of the following methods: ReadList, Create, Update, and Delete. Also counting the ReadItem
method, a total of five methods should be defined for the entity named
Customer. It is, however, much easier to create the last four methods,
as the type descriptors of the return parameters are automatically
defined the same way as with the ReadItem
method. The BDC Designer automatically applies type descriptors defined
in the other methods of an entity and copies them to the newly created
methods.
With the required type
descriptors in place for the methods, the LOB system–qualified type name
of the type descriptor Customer should be defined. This is done by
selecting the Customer type descriptor in the BDC Explorer panel. In the
Properties panel, its value should be changed from System.String to BdcCustomer.Customer, BdcModel1. This is now the underlying data type of the data structure that the Customer type descriptor returns.
3. Adding Code-Behind to Access External Data Source
To implement the logic required for performing the actual CRUD operations, the method body of the methods in the CustomerService.cs code file should be changed to match the code in Listing 2.
Example 2. Implementation of CRUD Operations in the BDC Method Instances
public static Customer ReadItem(string customersKey)
{
CustomerDataContext context = new CustomerDataContext();
Customer cust = context.Customers.Single(c => c.CustomerKey == customersKey);
return cust;
}
public static Customer Create(Customer newCustomer)
{
CustomerDataContext context = new CustomerDataContext();
context.Customers.InsertOnSubmit(newCustomer); context.SubmitChanges();
Customer cust= context.Customers.Single(c => c.CustomerKey ==newCustomer.CustomerKey);
return cust;
}
public static void Delete(string customersKey)
{
CustomerDataContext context = new CustomerDataContext();
Customer cust = context.Customers.Single(c => c.CustomerKey == customersKey);
context.Customers.DeleteOnSubmit(cust);
context.SubmitChanges();
}
public static IEnumerable<Customer> ReadList()
{
CustomerDataContext context = new CustomerDataContext();
IEnumerable<Customer> custList = context.Customers;
return custList;
}
public static void Update(Customer customer)
{
CustomerDataContext context = new CustomerDataContext();
Customer cust = context.Customers.Single(c => c.CustomerKey == customer.CustomerKey);
cust.CustomerKey = customer.CustomerKey;
cust.CompanyName = customer.CompanyName;
cust.ContactPerson = customer.ContactPerson;
cust.EmailAddress = customer.EmailAddress;
cust.PhoneNumber = customer.PhoneNumber;
context.SubmitChanges();
} 
