Module 6 - Code Snippets: Accessing External Data with Business Connectivity Services in SharePoint 2010

The following code snippets are taken from a demonstration video included in Module 6 of the Getting Started Course for SharePoint 2010 Developers (https://www.mssharepointdeveloper.com)

This first snippet defines a simple class that was used in the demo. This class is used to represent the Customer entity, and contains public properties that match the columns in a SQL Server table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace CustomerBCS.BdcModel1
{
public partial class Customer
{
public Int32 CustomerID { get; set; }
public string CustomerName { get; set; }
public string City { get; set; }
}
}

The following code implements the methods used in the Business Data Catalog Model for working with the data represented by the Customer class shown above. The code includes a helper function called getSQLConnection, and three methods for working with the data in the external data store.
NOTE: The ReadItem method is the implementation of the SpecificFinder-type method, the ReadList method is the implementation of the Finder-type method, and the Delete method is the implementation of the Deleter-type method

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace CustomerBCS.BdcModel1
{
public class CustomerEntityService
{
static SqlConnection getSqlConnection()
{
SqlConnection sqlConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Customers;Data Source=MOSS");
return (sqlConn);
}
public static Customer ReadItem(int id)
{
Customer cust = new Customer();
SqlConnection sqlConn = getSqlConnection();
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT CustomerID, Customer, City"
+ " FROM Customer"
+ " WHERE CustomerID = " + id.ToString();
cmd.Connection = sqlConn;
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (rdr.Read())
{
cust.CustomerID = int.Parse(rdr[0].ToString());
cust.CustomerName = rdr[1].ToString();
cust.City = rdr[2].ToString();
}
else
{
cust.CustomerID = -1;
cust.CustomerName = "Customer Not Found";
cust.City = "";
}
sqlConn.Dispose();
return cust;
}
public static IEnumerable<Customer> ReadList()
{
SqlConnection sqlConn = getSqlConnection();
try
{
List<Customer> allCustomers = new List<Customer>();

                sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandText = "SELECT CustomerID, Customer, City FROM Customer";
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read())
{
Customer cust = new Customer();
cust.CustomerID = int.Parse(rdr[0].ToString());
cust.CustomerName = rdr[1].ToString();
cust.City = rdr[2].ToString();
allCustomers.Add(cust);
}
Customer[] customerList = new Customer[allCustomers.Count];
for (int custCounter = 0; custCounter <= allCustomers.Count - 1; custCounter++)
{
customerList[custCounter] = allCustomers[custCounter];
}
return (customerList);
}
catch (Exception ex)
{
string TellMe = ex.Message;
Customer[] customerList = new Customer[0];
Customer cust = new Customer();
cust.CustomerID = -1;
cust.CustomerName = "Unable to retrieve data";
cust.City = "";
customerList[0] = cust;
return (customerList);
}
finally
{
sqlConn.Dispose();
}
}
public static void Delete(int customerID)
{
SqlConnection sqlConn = getSqlConnection();
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandText = "DELETE Customer WHERE CustomerID = "
+ customerID.ToString();
cmd.ExecuteNonQuery();

            sqlConn.Dispose();
}
}
}