Business Connectivity services using Visual Studio 2010

Business Connectivity services using Visual Studio 2010

Using SharePoint Designer you can connect and communicate to external Database or Data. But you don’t have much control using SharePoint 2010 designer.

Other option is you can use Visual studio to write custom code to achieve External Content type. Using Custom code you have full control over the business logic to external Data Sources.
How it Works

BDC Runtime API which manipulate the External data, External content type behave similar like a SharePoint content types, you can manage security on external Lists, Default farms for add, edit, and delete are created , List are available for BI solution, Data in Lists are available for Search.

How to Create BCS Solution in Visual Studio 2010

1.       First we need to identify the Data Store/Database which we want to use as external Content type, in this example I am using very simple table with the name of Customers which contain only 3 columns. Database Table script is

Create TABLE [dbo].[Customers](

   [CustomerID] [int] IDENTITY(1,1) NOT NULL,

   [CustomerName] [nvarchar](50) NULL,

   [CusAddress] [nvarchar](50) NULL,

 CONSTRAINT [PK_Customers1] PRIMARY KEY CLUSTERED

(

   [CustomerID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

2.       Create new project using Visual studio 2010

3.       Select SharePoint project templates

4.       Select Business connectivity as Project type, Project name to solution and project and Press Ok

5.       Business connectivity Solution can only deployed as Farm solution, select the Site URL and Press finish button.

6.       After Finish button Visual Studio 2010 will Create Some structure for you

7.       Rename the File Entity1.cs to Customers.cs

This is entity Class; here we need to define entity like in my case entity is Customer

8.       Open the Code View of Customer.cs

9.       Remove the existing properties And write new properties based on your entity.

 private int _ID;

        public int ID

        {

            get { return _ID; }

            set { _ID = value; }

        }

        private string _CustomerName;

        public string CustomerName

        {

            get { return _CustomerName; }

            set { _CustomerName = value; }

        }

        private string _CusAddress;

        public string CusAddress

        {

            get { return _CusAddress; }

            set { _CusAddress = value; }

        }

10.    Now open the designer mode of BDC Model ,

11.    Rename the Model name. right click on Model -> select properties -> Change name to CustomersModel (can choose any name)

12.    Select the identifier -> open properties and rename to ID and change Type Name to System.Int32. As an Example ID is identifier

13.    Visual studio will Create 2 methods by default ReadList and Readitem, Select ReadItem method and in below properties select Type Descriptor (its Dropdown) select and Press Edit.

14.    Rename the type descriptor to ID. As in below screen shot. Change Name to ID instead of identifier1 and Change Type Name to System.Int32.

15.    Now look at readitem Method again and select the second parameter (Return Parameter), Select the type Descriptor and Press Edit from the Dropdown. As we done for previous parameter name id.

16.    Rename Entity1 to Customer, change (Rename) Identifer1 to ID and rename its Type Name to System.Int32.

17.    Now we have to add Additional type descriptors. Rename Message to Our Second Field (CustomerName) in Entity and type name to system.string.

18.    Right Click on Customer and add New Descriptor as we have one more field

19. As third property or field is CusAddress and type is String.

That’s we done with ReadItem method.

20.    Now next for ReadList Method. Expand ReadList Method  -> Return parameter -> Entity, Right Click and delete this Entity.

21.    Expand the ReadItem Method and Expand the Copy the Customer Entity for Return parameter (as we define it earlier so no need to do this effort again ). 

22.    Copy to ReadList Method Return Parameter and Rename EntityList1 to Customers and Paste entity to Customers.

Till we have done to get specific Item Method (readItem) and get All items (ReadList), Now we are looking to create Creator method.

23.    Select the model Open the Propertied and select the Creator Method from Dropdown. And click on it. It will setup everything (in previous method we need to define entity it will setup automatically).

No do same step for Deleter and Updater Methods. Just select Model Diagram and open the BDC Model Detail Pain and select Deleter and Updater, it will configure everything in default based on our previous settings. And your Detail View look like Below screenshot.

This is all that we need to do using designer, now open the Code view (CustomerModelService.cs Class). And see it there are 5 methods now we have to write the code for Create, update, delete, readList and ReadItem methods.

Here you can write code with any logic, I am using very simple ADO.Net Method to perform these actions.

Here is My Code … Again – (here you can use your own way to write the Code. And you own logic)

Create Method

public static Customer Create(Customer newCustomerModel)

        {

            SqlConnection cnn;

            cnn = new SqlConnection(connetionString);

            try

            {

                cnn.Open();

                SqlCommand myCommand = new SqlCommand("INSERT INTO [Customers]([CustomerName],[CusAddress])VALUES('" + newCustomerModel.CustomerName + "','" + newCustomerModel.CusAddress + "')", cnn);

                myCommand.ExecuteNonQuery();

                cnn.Close();

                return newCustomerModel;

            }

            catch (Exception ex)

            {

                return newCustomerModel;

            }

        }

 Delete Method      

public static void Delete(int iD)

{

    SqlConnection cnn;

    cnn = new SqlConnection(connetionString);

    try

    {

        cnn.Open();

 SqlCommand myCommand = new SqlCommand("Delete [Customers] Where CustomerID='" + 

 iD + "'", cnn);

        myCommand.ExecuteNonQuery();

        cnn.Close();

    }

    catch (Exception ex)

    {

        // return newCustomerEntity;

    }

}

 Update Method

public static void Update(Customer customerModel)

        {

            SqlConnection cnn;

            cnn = new SqlConnection(connetionString);

            try

            {

                cnn.Open();

                SqlCommand myCommand = new SqlCommand("Update [Customers] Set [CustomerName]='" + customerModel.CustomerName + "',[CusAddress]='" + customerModel.CusAddress + "' Where CustomerID=" + customerModel.ID + "", cnn);

                myCommand.ExecuteNonQuery();

                cnn.Close();

            }

            catch (Exception ex)

            {

                // return newCustomerEntity;

            }

        }

ReadList Method

public static IEnumerable<Customer> ReadList()

        {

            int count = ItemCounter();

            Customer[] CustomersList = new Customer[count];

            Customer ObjCustomer = new Customer();

            //CustomersList[0] = ObjCustomer;

            SqlConnection cnn;

            cnn = new SqlConnection(connetionString);

            try

            {

                cnn.Open();

                SqlDataReader myReader = null;

                SqlCommand myCommand = new SqlCommand("SELECT [CustomerID],[CustomerName],[CusAddress] FROM [Customers] ", cnn);

                myReader = myCommand.ExecuteReader();

                int i = 0;

                while (myReader.Read())

                {

                    ObjCustomer.ID = Convert.ToInt32(myReader["CustomerID"]);

                    ObjCustomer.CustomerName = Convert.ToString(myReader["CustomerName"].ToString());

                    ObjCustomer.CusAddress = Convert.ToString(myReader["CusAddress"].ToString());

                    CustomersList[i] = ObjCustomer;

                    i++;

                    ObjCustomer = new Customer();

                }

                cnn.Close();

                return CustomersList;

            }

            catch (Exception ex)

            {

                CustomersList = new Customer[1];

                ObjCustomer.ID = 0;//Convert.ToInt32(myReader["CustomerID"]);

                ObjCustomer.CustomerName = "Not Found";//Convert.ToString(myReader["CustomerName"].ToString());

                ObjCustomer.CusAddress = "Not Found";

                CustomersList[0] = ObjCustomer;

                return CustomersList;

            }

        }

Read Item Method

public static Customer ReadItem(int id)

        {

            //TODO: This is just a sample. Replace this simple sample with valid code.

            Customer ObjCustomer = new Customer();

            SqlConnection cnn;

            cnn = new SqlConnection(connetionString);

            try

            {

                cnn.Open();

                SqlDataReader myReader = null;

                SqlCommand myCommand = new SqlCommand("SELECT [CustomerID],[CustomerName],[CusAddress] FROM [Customers] Where [CustomerID]=" + id + "", cnn);

                myReader = myCommand.ExecuteReader();

                while (myReader.Read())

                {

                    ObjCustomer.ID = Convert.ToInt32(myReader["CustomerID"]);

                    ObjCustomer.CustomerName = Convert.ToString(myReader["CustomerName"].ToString());

                    ObjCustomer.CusAddress = Convert.ToString(myReader["CusAddress"].ToString());

                }

                cnn.Close();

                return ObjCustomer;

            }

            catch (Exception ex)

            {

                return ObjCustomer;

            }

         

        }

After This Deploy your solution either using Visual studio or Powershell.

Open the particular site - > site action -> Select More Option  -> Select External List from the Templates and Click Create.

Provide name and select External Content type.

Now see that your items from Database are in SharePoint List, we Implement Create, Update delete, readitem and readList Methods so it will work for all solutions..

Working Example - Click Here to Download