Bringing SQL data from a variety of applications and standalone databases into SharePoint is a hot topic. With SharePoint Designer 2010 you can easily create an external content type and expose your external data as list; however, this does not provide sound disaster recovery story. Here are some tips&tricks on exporting definition work you have done with SharePoint Designer into Visual Studio and packaging it for reusable deployment.
We start by creating an External Content Source which in my case will be a SQL Adventure Works database and the table of my interest will be Customers.
In one of the earlier SSWUG articles we looked at the details on how to achieve that (Managing external SQL data in SharePoint 2010).
We end up with a connection and the content source defined that will look like this:
Now that we have our External Content Type defined with a list of Read/Wring/Delete operations we can export the Application Model to an XML file using an Exportfeature:
Here it`s important to remember the name you give to your exported application model:
Next we open Visual Studio 2010 and assuming you have SharePoint extensions installed along with your Visual Studio, we open a new project with Business Data Connectivity Model template. In here you can accept all of the suggested debugging and packaging options in your wizard.
Visual Studio will open a sample BDC model project which contains sample Identifiers, operations, and parameters. The sample template represents a .NET Type data source. You`ve seen this data source available when creating a connection to your SQL database in SharePoint Designer external content type.
This means that Visual Studio will create an Entity class as well as operations class to define how are we going to retrieve and manage data. This template is applicable to more complex scenarios where you use custom authentication or other custom mechanisms to retrieve or manage your data. In our case, all we want is to package our existing Application Model connecting to SQL into a Visual Studio package. We won`t be defining our operations in C#.
In your Solution Explorer, double click on your BDC Model:
You will notice that Visual Studio does not provide any options to import your Application Model, but there is an option to open an existing model as an XML file as shown below:
Now you can overwrite Application Model saved from SharePoint Designer with the Visual Studio sample model.
To ensure there is no namespace misfit, make sure the name of your data model exported from SharePoint Designer XML file is replaced with the name of the model you defined when created a Visual Studio project.
You will also need to delete sample classes Visual Studio created for your project. Those classes were referenced by sample data model which your replaced.
All you need now is to build your project and deployment package will be generated for you. If you`d like to test your model you can use a deploy feature in Visual Studio. Once a deployment is complete you can create a new External List based on your model and it will access and manage data just as you specified in your SharePoint Designer wizard.
That`s about it ... if you found this article useful be sure to let me know by casting a vote!
Yaroslav Pentsarskyy, Microsoft SharePoint MVP
Blog: www.sharemuch.com
