Using SharePoint Designer 2010 is easy way to create BCS connection because this method required no code at all. In this blog post i am going to connect MS- SQL DB and bring data into SharePoint. To do this first we need to create External Content Type (ECT). So let us Fire up SPD 2010.
1) Open a site ( You want to work with) in SharePoint Designer 2010. and wait for site to load.
Now Select External Content Types in left pane and click Top Menu External Content Type to create .
Now Name the ECT & Click to Discover External data sources and Define Operations as shown below .
Now Click on Add Connection and select the Data Source Type Selection from Given Drown down List.
Edit SQL Server Connection Details .
Once connection is Successfully established to external Data source you will able to see the Tables in Data Source Explorer as shown below.
Now right click on the table and select the operation which you wanted to generate and follow the wizard .
Now save the work. This time SPD will write ECT to DBC Metadata store DB.
Now Create Profile page.
Profile page for an instance of a Business Data Connectivity service application site are stored in a site in the farm that hosts the Business Data Connectivity service. You can specify the site where profile pages are hosted.
Now we need to create External list . go to the site you ware working on.
Now Select ECT Type from the list. here we are going to pick ASSET DETAILS.
Name the External List and select ECT for this list and Click Create .
Once the above step is completed you will see the SP List populated with our External SQL DB Tables Records as shown below.
Now you are able to add , View the records with in SharePoint environment. basically you can do all (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.