For this recipe, you need to have access to the AdventureWorks database. You can download it from: http://msftdbprodsamples.codeplex.com/.

Visual Studio refers to external content types as an entity because BDC schema refers to this as entities. BDC Designer is built on top of ADO.Net Entity Designer and hence that reference. If you are familiar with ADO.Net Entity Designer, this will work the same way. In here, we can create entities and associate them with some relationship.

For our external content type to work, we need to create at least two methods of type Finder and SpecificFinder method. In our example, we created GetDepartmentList and GetDepartment that corresponds to these types. This is important because SharePoint calls these methods to render the list and display the selected item when the item is selected. There are other types of methods that can be used in external content types. Refer to MSDN at: http://msdn.microsoft.com/en-us/library/ee557363(office.14).aspx for more information on different method types that can be developed in the external content types.

The finder method, GetDepartmentList does not take any input parameters, but has one return parameter. This is defined in the Method Details window. In here, we provided information about the type of our parameter and direction of the parameter. The direction can be "In", "Out", "InOut", and "Return". The "In" direction is used on the parameters that are used to pass in the values to the methods. The "Out" direction, as the name refers to, is the output parameter and "InOut" is similar to "ref" parameter in C#. We also provided the type of our parameter. For this, we used the Department object that was created through LINQ to SQL. We set the type of the parameter through type descriptors. Type descriptors are metadata information that is used during the runtime to determine the type of the parameter passed or returned. There are other properties in type descriptors that tell SharePoint how to use this parameter. For example, the Creator Field property in a type descriptor tells that it is used in the form for user input. the collection property tells that the parameter is a collection. Not all of the type descriptors make sense for each of the parameters that pass in the method. It comes in handy, depending upon the type we want to retrieve or pass.

Each of the methods that we defined also has a method instance. This is because, in BCS, when we define a method, it is defined as an abstract piece of code. An instance is created during run time and executed.

If you receive Access denied by Business Data Connectivity error as shown here, follow these steps:

Create, update, and delete methods

For our preceding example, to add other CRUD methods like create, update, and delete methods, follow these steps:

1. From the BDC Method Details Window, create Creator Method for creating the department. Name this method CreateDepartment. For updating, the method type should be Updater Method and for delete Deleter Method. Also name the updater method as UpdateDepartment and the deleter method as DeleteDepartment.
2. Visual Studio automatically wires up the necessary parameters and its type descriptors and adds method signatures in the DepartmentService.cs. Add the code in the DepartmentService.cs for create, update, and delete. Your code should look as follows:

   public static Department CreateDepartment(Department newDepartment)
   {
   DepartmentDataContext dx = new DepartmentDataContext("Data Source=intsql;Initial Catalog=AdventureWorks;Integrated Security=True");
   newDepartment.ModifiedDate = DateTime.Today;
   dx.Departments.InsertOnSubmit(newDepartment);
   dx.SubmitChanges();
   return dx.Departments.Single(d => d.DepartmentID == newDepartment.DepartmentID);
   }
   public static void UpdateDepartment(Department department)
   {
   DepartmentDataContext dx = new DepartmentDataContext("Data Source=intsql;Initial Catalog=AdventureWorks;Integrated Security=True");
   Department dept = dx.Departments.Single(d => d.DepartmentID == department.DepartmentID);
   dept.GroupName = department.GroupName;
   dept.Name = department.Name;
   dept.ModifiedDate = DateTime.Today;
   dx.SubmitChanges();
   }
   public static void DeleteDepartment(short departmentID)
   {
   DepartmentDataContext dx = new DepartmentDataContext("Data Source=intsql;Initial Catalog=AdventureWorks;Integrated Security=True");
   external content typeCRUD methodsdx.Departments.DeleteOnSubmit(dx.Departments.Single(d => d.DepartmentID == departmentID));
   dx.SubmitChanges();
   }
   

Connection strings

In our example, we have hard coded the connection string. There are several ways you can access the connection string data without hard coding it like getting the connection string from web.config or from a custom SharePoint list that is used to store the configuration information. The other approach is to use the BCS LobSystemInstance object. For our example:

1. Navigate from BCS Explorer window Model | DepartmentsModel | DepartmentsModel | LobSystemInstances | DepartmentsModel.
2. From the properties windows, add a custom property in the custom property collection and add your connection string as shown:
   
3. Add a reference to Microsoft.BusinessData.dll found in Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPI.
4. This connection string now can be accessed from the code by making DepartmentService implement the IContextProperty interface. The following is the code for getting the DB connection string.

   public string GetDBCon()
   {
   Microsoft.BusinessData.MetadataModel.Collections.INamedPropertyDictionary dic = this.LobSystemInstance.GetProperties();
   if (dic.ContainsKey("DBCon"))
   return dic["DBCon"].ToString();
   else
   return "";
   }
   public Microsoft.BusinessData.Runtime.IExecutionContext ExecutionContext
   public Microsoft.BusinessData.Runtime.IExecutionContext ExecutionContext
   {
   get;
   set;
   }
   public Microsoft.BusinessData.MetadataModel.ILobSystemInstance LobSystemInstance
   {
   get;
   set;
   }
   public Microsoft.BusinessData.MetadataModel.IMethodInstance MethodInstance
   {
   get;
   set;
   }
   

The other method is to use the secure store service to store the connection string. Although it is used to store the user credentials for logging into the external system, it can be used to store the connection string. Information on configuring the secure store service can be found on MSDN at http://technet.microsoft.com/en-us/library/ee806866.aspx.

For our example, create a new secure store application called AdventureWorks with one field of type Generic. Call that field Connection String as shown here:

Follow the next steps to get credentials from the secure store programmatically.

1. Add a new class to the project and call it SecureStoreUtilities.cs.
2. Add a reference to Microsoft.BusinessData.dll from the ISAPI folder.
3. Add a reference to Microsoft.Office.SecureStoreService.dll from WindowsassemblyGAC_MSILMicrosoft.Office.SecureStoreService14.0.0.0__71e9bce111e9429c.
4. Add the following code to SecureStoreUtilities.cs to retrieve and decrypt the string:

   public static Dictionary<string, string> GetSSCreds(string applicationID)
   {
   var credKVP = new Dictionary<string, string>(); //Key Value pair
   using (SPSite site = new SPSite("http://intsp1/"))
   {
   // Console.WriteLine(site.RootWeb.CurrentUser.Name);
   SPServiceContext serviceContext = SPServiceContext.GetContext(site);
   var secureStoreProvider = new SecureStoreProvider { Context = serviceContext };
   using (var creds = secureStoreProvider. GetCredentials(applicationID))
   {
   var taFields = secureStoreProvider.GetTarget ApplicationFields(applicationID);
   for (var i = 0; i < taFields.Count; i++)
   {
   var field = taFields[i];
   var credential = creds[i];
   var decryptedCredential = DecryptCredential(credential.Credential);
   external content typeconnection stringscredKVP.Add(field.Name, decryptedCredential);
   }
   }
   }
   return credKVP;
   }
   public static string DecryptCredential(this SecureString encryptedString)
   {
   var ssBSTR = Marshal.SecureStringToBSTR (encryptedString);
   try
   {
   return Marshal.PtrToStringBSTR(ssBSTR);
   }
   finally
   {
   Marshal.FreeBSTR(ssBSTR);
   }
   }
   

5. Create a following utility method in your DepartmentService.cs called GetDBConnectionString and pass the return value from this method to the DepartmentDataContext constructor:

   public static string GetDBConnectionString()
   {
   string sDbCon = "";
   Dictionary<string, string> ssList = SecureStoreUtilities.GetSSCreds("AdventureWorks");
   foreach (KeyValuePair<string, string> kvp in ssList)
   {
   if (kvp.Key == "Connection String")
   {
   sDbCon = kvp.Value;
   break;
   }
   }
   return sDbCon;
   }
   

You can use this approach to dynamically construct a LOB connection string by storing the credentials in the secure store.