Creating a .NET Connectivity Assembly for Business Connectivity Services by Using Visual Studio 2010

  • Article

Summary: Learn how to create a Microsoft .NET Framework connectivity assembly for Microsoft Business Connectivity Services (BCS) by using Microsoft Visual Studio 2010.

Applies to: Business Connectivity Services | Office 2010 | Open XML | SharePoint Designer 2010 | SharePoint Foundation 2010 | SharePoint Online | SharePoint Server 2010 | Visual Studio

Provided by: Ben Hedges

Contents

  • Overview of Creating a Connectivity Assembly

  • Creating the HRTrainingManagement Database

  • Creating a .NET Framework Connectivity Assembly by Using Visual Studio 2010

  • Deploying and Using the Solution

  • Additional Resources

Download the sample code

Watch the video

Overview of Creating a Connectivity Assembly

Visual Studio 2010 provides a project type that developers can use to build .NET Framework connectivity assemblies for Business Connectivity Services.

This article shows how to create a .NET Framework connectivity assembly for Business Connectivity Services by using the following steps:

  1. Create the Business Data Connectivity model.

  2. Define type descriptors for the ReadItem and ReadList Methods.

  3. Create additional data operation methods.

  4. Implement the data operation methods with code.

  5. Add permission on the new entity.

  6. Set up and use external lists in Microsoft SharePoint 2010.

  7. Review changes to the back-end data repository.

In this example, you create a BDC model that uses BCS entities in Visual Studio to access a "Training" matrix in Microsoft SQL Server. The matrix consists of a many-to-many relationship between People and the Courses that those people have attended or that they are scheduled to attend.

The solution includes adding, updating, and deleting entities that comprise several database tables.

Creating the HRTrainingManagement Database

To create the HRTrainingManagement database

  1. Open SQL Server Management Studio.

  2. In SQL Server create a database named HRTrainingManagement.

  3. Use the following SQL script to create the tables that this article requires.

    USE [HRTrainingManagement]
GO
/****** Object:  Table [dbo].[Student ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[LoginName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
[StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TrainingObjects ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TrainingObjects](
[TrainingID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
[EventType] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_TrainingObjects] PRIMARY KEY CLUSTERED 
(
[TrainingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TrainingEvent ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TrainingEvent](
[TrainingEventID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[TrainingID] [int] NOT NULL,
[EventDate] [smalldatetime] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TrainingEvent] PRIMARY KEY CLUSTERED 
(
[StudentID] ASC,
[TrainingID] ASC
)WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_TrainingEvent_Student ******/
ALTER TABLE [dbo].[TrainingEvent]  
WITH CHECK ADD  CONSTRAINT [FK_TrainingEvent_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Student] ([StudentID])
GO
ALTER TABLE [dbo].[TrainingEvent] CHECK CONSTRAINT [FK_TrainingEvent_Student]
GO
/****** Object:  ForeignKey [FK_TrainingEvent_TrainingObjects]    ******/
ALTER TABLE [dbo].[TrainingEvent]  
WITH CHECK ADD  CONSTRAINT [FK_TrainingEvent_TrainingObjects] FOREIGN KEY([TrainingID])
REFERENCES [dbo].[TrainingObjects] ([TrainingID])
GO
ALTER TABLE [dbo].[TrainingEvent] CHECK CONSTRAINT [FK_TrainingEvent_TrainingObjects]
GO

Creating a .NET Framework Connectivity Assembly by Using Visual Studio 2010

To create an empty SharePoint 2010 Business Data Connectivity Model in Visual Studio 2010

  1. Start Visual Studio 2010.

  2. On the File menu, click New, and then click Project.

  3. In the Installed Templates section, expand Visual Basic or C#, expand SharePoint, and then click 2010.

  4. In the template pane, click Business Data Connectivity Model.

  5. In the Name textbox, type BDCModel.

  6. Leave the other fields with their default values and click OK.

  7. In the What local site do you want to use for debugging? combo box, select your site.

  8. Click the Deploy as a farm solution option box, and then click Finish.

To configure the Events Business Data Connectivity Model

  1. In the Solution Explorer window, right-click Entity1.vb or Entity1.cs, and then click Rename.

  2. Type TrainingEvent.vb or TrainingEvent.cs, and then press Enter.

  3. Double-click the TrainingEvent.vb or TrainingEvent.cs file.

  4. Replace the existing property declarations with the following code.

    Private _TrainingEventID As Int32
Private _EventDate As DateTime
Private _Status As String
Private _LoginName As String
Private _Title As String
Private _EventType As String
Private _Description As String
Public Property TrainingEventID() As Int32
    Get
        Return _TrainingEventID
    End Get
    Set(ByVal value As Int32)
        _TrainingEventID = value
    End Set
End Property
Public Property EventDate() As DateTime
    Get
        Return _EventDate
    End Get
    Set(ByVal value As DateTime)
        _EventDate = value
    End Set
End Property
Public Property Status() As String
    Get
        Return _Status
    End Get
    Set(ByVal value As String)
        _Status = value
    End Set
End Property
Public Property LoginName() As String
    Get
        Return _LoginName
    End Get
    Set(ByVal value As String)
        _LoginName = value
    End Set
End Property
Public Property Title() As String
    Get
        Return _Title
    End Get
    Set(ByVal value As String)
        _Title = value
    End Set
End Property
Public Property EventType() As String
    Get
        Return _EventType
    End Get
    Set(ByVal value As String)
        _EventType = value
    End Set
End Property
Public Property Description() As String
    Get
        Return _Description
    End Get
    Set(ByVal value As String)
        _Description = value
    End Set
End Property

    public Int32 TrainingEventID { get; set; } 
public DateTime EventDate { get; set; } 
public string Status { get; set; } 
public string LoginName { get; set; } 
public string Title { get; set; } 
public string EventType { get; set; } 
public string Description { get; set; }

To define type descriptors for the ReadItem method

  1. On the design surface, click ReadItem.

  2. In the BDC Method Details pane, in the Parameters section below ReadItem, click Identifier1 (in the id row). Then, click the arrow that appears and click <Edit>.

  3. In the Properties window, click the Name property, type TrainingEventID, and then press Enter.

  4. In the Properties window, click Type Name.

  5. In the list for the Type Name property, click Int32.

  6. In the BDC Method Details pane, in the Parameters section below ReadItem, click Entity1 (in the returnParameter row). Then, click the arrow that appears and click <Edit>.

  7. In the Properties window, click the Name property, type TrainingEvent, and then press Enter.

  8. In the BDC Explorer window, expand the currently selected TrainingEvent node.

  9. Beneath the TrainingEvent node, click Identifier1.

  10. In the Properties window, click the Name property, type TrainingEventID, and then press Enter.

  11. In the Properties window, click Type Name.

  12. In the list for the Type Name property, click Int32.

  13. In the Properties window, click Read-only.

  14. In the list for the Read-only property, click True.

  15. In the BDC Explorer window, click Message.

  16. In the Properties window, click the Name property, type EventDate, and then press Enter.

  17. In the Properties window, click Type Name.

  18. In the list for the Type Name property, click DateTime.

  19. In the BDC Explorer window, right-click TrainingEvent, and then click Add Type Descriptor.

  20. In the Properties window, click the Name property, type Status, and then press Enter.

  21. In the BDC Explorer window, right-click TrainingEvent, and then click Add Type Descriptor.

  22. In the Properties window, click the Name property, type LoginName, and then press Enter.

  23. In the BDC Explorer window, right-click TrainingEvent, and then click Add Type Descriptor.

  24. In the Properties window, click the Name property, type Title, and then press Enter.

  25. In the BDC Explorer window, right-click TrainingEvent, and then click Add Type Descriptor.

  26. In the Properties window, click the Name property, type EventType, and then press Enter.

  27. In the BDC Explorer window, right-click TrainingEvent, and then click Add Type Descriptor.

  28. In the Properties window, click the Name property, type Description, and then press Enter.

  29. On the File menu, click Save All.

To define type descriptors for the ReadList method

  1. In the BDC Method Details pane, in the Parameters section below ReadList, click Entity1List (in the returnParameter row). Then click the arrow that appears and click <Edit>.

  2. In the Properties window, click the Name property, type TrainingEventList, and then press Enter.

  3. In the BDC Explorer window, expand the TrainingEventList node.

  4. Right-click Entity1 and then click Delete.

  5. In the BDC Explorer window, below the returnParameter for the ReadItem node, right-click TrainingEvent, and then click Copy.

  6. In the BDC Explorer window, below the returnParameter for the ReadList node, right-click TrainingEventList, and then click Paste.

  7. On the File menu, click Save All.

To create data operation methods

  1. In the BDC Method Details pane, collapse the ReadList and ReadItem nodes.

  2. Click <Add a Method>, and then click Create Creator Method in the list.

  3. In the Parameters section below Create, click NewTrainingEventEntity in the Type Descriptor column for the newTrainingEventEntity row.

  4. Click the arrow, and the click <Edit>.

  5. In the BDC Explorer window, expand the selected NewTrainingEventEntity node, and then click TrainingEventID.

  6. In the Properties window, click Read-only, click the arrow, and then click False.

  7. Collapse the Create node.

  8. Click <Add a Method>, and then click Create Deleter Method in the list.

  9. Collapse the Delete node.

  10. Click <Add a Method>, and then click Create Updater Method in the list.

  11. In the Parameters section below Update, click TrainingEventEntity in the Type Descriptor column for the trainingEventEntity row.

  12. Click the arrow, and the click <Edit>.

  13. In the BDC Explorer window, expand the selected TrainingEventEntity node, and then click TrainingEventID.

  14. In the Properties window, click Read-only, click the arrow, and then click False.

  15. On the File menu, click Save All.

To implement methods for the TrainingEventEntity entity

  1. On the design surface, double-click ReadItem to display the code file for the TrainingEventEntityService.cs class.

  2. Add the following using statements above the namespace declaration.

    Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

    using System.Data; 
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

  3. Place the cursor after the class definition, public class TrainingEventEntityService, and then press Enter.

  4. Add the following function to the space that you just created.

    Note

    Edit the connection string for your server name and connection information.

    Private Shared Function getSqlConnection() As SqlConnection
        Dim sqlConn As New SqlConnection _
     ("Integrated Security=SSPI;Persist Security Info=False; _Initial Catalog=HRTrainingManagement; " _
      + "Data Source=Demo2010a")
        Return (sqlConn)
    End Function

    static SqlConnection getSqlConnection()
{
  SqlConnection sqlConn = new SqlConnection(    "Integrated Security=SSPI;Persist Security Info=False;"
    + "Initial Catalog=HRTrainingManagement;"
    +  @"Data Source= Demo2010a");
  return (sqlConn);
}

  5. Replace the contents of the ReadItem method with the following code.

    Dim thisConn As SqlConnection = Nothing
Dim evt As TrainingEvent = Nothing
    Try
        evt = New TrainingEvent()
        thisConn = getSqlConnection()
        thisConn.Open()
Dim thisCmd As New SqlCommand()
        thisCmd.CommandText = "SELECT e.TrainingEventID, s.LoginName, t.Title, t.EventType, " _
  + "t.Description, e.EventDate, e.Status FROM Student s INNER JOIN TrainingEvent e " _
  + "ON s.StudentID = e.StudentID " _
  + "INNER JOIN TrainingObjects t ON e.TrainingID = t.TrainingID WHERE e.TrainingEventID = " _
  & id.ToString()
        thisCmd.Connection = thisConn
Dim thisReader As SqlDataReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection)
        If thisReader.Read() Then
            evt.TrainingEventID = id
            evt.LoginName = thisReader(1).ToString()
            evt.Title = thisReader(2).ToString()
            evt.EventType = thisReader(3).ToString()
            evt.Description = thisReader(4).ToString()
            evt.EventDate = DateTime.Parse(thisReader(5).ToString())
            evt.Status = thisReader(6).ToString()
        Else
            evt.TrainingEventID = -1
            evt.LoginName = "Data Not Found"
            evt.Title = "Data Not Found"
            evt.EventType = "Data Not Found"
            evt.Description = "Data Not Found"
            evt.EventDate = DateTime.MinValue
            evt.Status = "Data Not Found"
        End If
        thisReader.Close()
        Return (evt)
    Catch ex As Exception
        evt.TrainingEventID = -1
        evt.LoginName = "Data Not Found"
        evt.Title = "Data Not Found"
        evt.EventType = "Data Not Found"
        evt.Description = "Data Not Found"
        evt.EventDate = DateTime.MinValue
        evt.Status = "Data Not Found"
        Return (evt)
    Finally
        thisConn.Dispose()
    End Try

    SqlConnection thisConn = null;
TrainingEvent evt = null;
try
{
  evt = new TrainingEvent();
  thisConn = getSqlConnection();
  thisConn.Open();
  SqlCommand thisCmd = new SqlCommand();
  thisCmd.CommandText = "SELECT e.TrainingEventID, s.LoginName,"
  + " t.Title, t.EventType, t.Description, e.EventDate, e.Status"
  + " FROM Student s"
  + " INNER JOIN TrainingEvent e ON s.StudentID = e.StudentID"
  + " INNER JOIN TrainingObjects t ON e.TrainingID = t.TrainingID"
  + " WHERE e.TrainingEventID = " + id.ToString();
  thisCmd.Connection = thisConn;
  SqlDataReader thisReader = 
    thisCmd.ExecuteReader(CommandBehavior.CloseConnection);
  if (thisReader.Read())
  {
    evt.TrainingEventID = id;
    evt.LoginName = thisReader[1].ToString();
    evt.Title = thisReader[2].ToString();
    evt.EventType = thisReader[3].ToString();
    evt.Description = thisReader[4].ToString();
    evt.EventDate = DateTime.Parse(thisReader[5].ToString());
    evt.Status = thisReader[6].ToString();
  }
  else
  {
    evt.TrainingEventID = -1;
    evt.LoginName = "Data Not Found";
    evt.Title = "Data Not Found";
    evt.EventType = "Data Not Found";
    evt.Description = "Data Not Found";
    evt.EventDate = DateTime.MinValue;
    evt.Status = "Data Not Found";
  }
  thisReader.Close();
  return (evt);
}
catch
{
  evt.TrainingEventID = -1;
  evt.LoginName = "Data Not Found";
  evt.Title = "Data Not Found";
  evt.EventType = "Data Not Found";
  evt.Description = "Data Not Found";
  evt.EventDate = DateTime.MinValue;
  evt.Status = "Data Not Found";
  return (evt);
}
finally
{
    thisConn.Dispose();
}

  6. Replace the contents of the ReadList method with the following code.

    Dim thisConn As SqlConnection = Nothing
Dim allEvents As List(Of TrainingEvent)
Try
thisConn = getSqlConnection()
allEvents = New List(Of TrainingEvent)()
thisConn.Open()
Dim thisCommand As New SqlCommand()
thisCommand.Connection = thisConn
thisCommand.CommandText = "SELECT e.TrainingEventID, LoginName, t.Title, t.EventType, " _
    + "t.Description, e.EventDate, e.Status FROM Student s INNER JOIN TrainingEvent e " _
    + "ON s.StudentID = e.StudentID INNER JOIN TrainingObjects t ON e.TrainingID = t.TrainingID"
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader(CommandBehavior.CloseConnection)
While thisReader.Read()
Dim evt As New TrainingEvent()
    evt.TrainingEventID = Integer.Parse(thisReader(0).ToString())
    evt.LoginName = thisReader(1).ToString()
    evt.Title = thisReader(2).ToString()
    evt.EventType = thisReader(3).ToString()
    evt.Description = thisReader(4).ToString()
    evt.EventDate = DateTime.Parse(thisReader(5).ToString())
    evt.Status = thisReader(6).ToString()
    allEvents.Add(evt)
End While
thisReader.Close()
Dim eventList As TrainingEvent() = New TrainingEvent(allEvents.Count - 1) {}
For evtCounter As Integer = 0 To allEvents.Count - 1
    eventList(evtCounter) = allEvents(evtCounter)
Next
Return (eventList)
Catch ex As Exception
Dim errEventList As TrainingEvent() = New TrainingEvent(0) {}
Dim errEvt As New TrainingEvent()
errEvt.TrainingEventID = -1
errEvt.LoginName = ex.Message
errEvt.Title = ex.Message
errEvt.EventType = ex.Message
errEvt.Description = ex.Message
errEvt.EventDate = DateTime.MinValue
errEvt.Status = ex.Message
errEventList(0) = errEvt
Return (errEventList)
Finally
thisConn.Dispose()
End Try

    SqlConnection thisConn = null;
List<TrainingEvent> allEvents;
try
{
  thisConn = getSqlConnection();
  allEvents = new List<TrainingEvent>();
  thisConn.Open();
  SqlCommand thisCommand = new SqlCommand();
  thisCommand.Connection = thisConn;
  thisCommand.CommandText = "SELECT e.TrainingEventID, LoginName," 
  + " t.Title, t.EventType, t.Description, e.EventDate, e.Status"
  + " FROM Student s"
  + " INNER JOIN TrainingEvent e ON s.StudentID = e.StudentID"
  + " INNER JOIN TrainingObjects t ON e.TrainingID = t.TrainingID";
  SqlDataReader thisReader = 
    thisCommand.ExecuteReader(CommandBehavior.CloseConnection);
  while (thisReader.Read())
  {
    TrainingEvent evt = new TrainingEvent();
    evt.TrainingEventID = int.Parse(thisReader[0].ToString());
    evt.LoginName = thisReader[1].ToString();
    evt.Title = thisReader[2].ToString();
    evt.EventType = thisReader[3].ToString();
    evt.Description = thisReader[4].ToString();
    evt.EventDate = DateTime.Parse(thisReader[5].ToString());
    evt.Status = thisReader[6].ToString();
    allEvents.Add(evt);
  }
  thisReader.Close();
  TrainingEvent[] eventList = new TrainingEvent[allEvents.Count];
  for (int evtCounter = 0; 
    evtCounter <= allEvents.Count - 1; 
    evtCounter++)
  {
    eventList[evtCounter] = allEvents[evtCounter];
  }
  return (eventList);
}
catch (Exception ex)
{
  TrainingEvent[] errEventList = new TrainingEvent[1]; 
  TrainingEvent errEvt = new TrainingEvent();
  errEvt.TrainingEventID = -1;
  errEvt.LoginName = ex.Message;
  errEvt.Title = ex.Message;
  errEvt.EventType = ex.Message;
  errEvt.Description = ex.Message;
  errEvt.EventDate = DateTime.MinValue;
  errEvt.Status = ex.Message;
  errEventList[0] = errEvt;
  return (errEventList);
}
finally
{
    thisConn.Dispose();
}

  7. Replace the contents of the Create method with the following code.

    Dim thisConn As SqlConnection = Nothing
Try
thisConn = getSqlConnection()
thisConn.Open()
Dim studentName As String = newTrainingEventEntity.LoginName
Dim trainingTitle As String = newTrainingEventEntity.Title
Dim trainingType As String = newTrainingEventEntity.EventType
Dim trainingDescription As String = newTrainingEventEntity.Description
Dim trainingDate As DateTime = newTrainingEventEntity.EventDate
Dim trainingStatus As String = newTrainingEventEntity.Status
Dim studentID As Integer = 0
Dim studentCmd As New SqlCommand()
studentCmd.Connection = thisConn
studentCmd.CommandText = "SELECT StudentID FROM Student WHERE LoginName='" & studentName & "'"
Dim studentReader As SqlDataReader = studentCmd.ExecuteReader(CommandBehavior.[Default])
If studentReader.Read() Then
    studentID = Integer.Parse(studentReader(0).ToString())
    studentReader.Close()
Else
    studentReader.Close()
Dim addStudentCommand As New SqlCommand()
    addStudentCommand.Connection = thisConn
    addStudentCommand.CommandText = "INSERT Student(LoginName) VALUES('" & studentName & "')"
    addStudentCommand.ExecuteNonQuery()
Dim getNewStudentCmd As New SqlCommand()
    getNewStudentCmd.Connection = thisConn
    getNewStudentCmd.CommandText = _
        "SELECT StudentID FROM Student WHERE LoginName = '" & studentName & "'"
Dim getNewStudentReader As SqlDataReader = _
getNewStudentCmd.ExecuteReader(CommandBehavior.[Default])
    getNewStudentReader.Read()
    studentID = Integer.Parse(getNewStudentReader(0).ToString())
    getNewStudentReader.Close()
End If
Dim trainingID As Integer = 0
Dim trainingCmd As New SqlCommand()
trainingCmd.Connection = thisConn
trainingCmd.CommandText = _
    "SELECT TrainingID" & " FROM TrainingObjects" & " WHERE Title = '" & trainingTitle & "'" & _
    " AND EventType = '" & trainingType & "'" & " AND Description = '" & trainingDescription & "'"
Dim trainingReader As SqlDataReader = trainingCmd.ExecuteReader(CommandBehavior.[Default])
If trainingReader.Read() Then
    trainingID = Integer.Parse(trainingReader(0).ToString())
    trainingReader.Close()
Else
    trainingReader.Close()
Dim addTrainingCommand As New SqlCommand()
    addTrainingCommand.Connection = thisConn
    addTrainingCommand.CommandText = _
        "INSERT TrainingObjects(Title, EventType, Description) VALUES('" & trainingTitle & _
        "','" & trainingType & "','" & trainingDescription & "')"
    addTrainingCommand.ExecuteNonQuery()
Dim getNewTrainingCmd As New SqlCommand()
    getNewTrainingCmd.Connection = thisConn
    getNewTrainingCmd.CommandText = _
    "SELECT TrainingID FROM TrainingObjects WHERE Title = '" + trainingTitle & _
    "' AND EventType = '" & trainingType & "' AND Description = '" & trainingDescription & "'"
Dim getNewTrainingReader As SqlDataReader = _
getNewTrainingCmd.ExecuteReader(CommandBehavior.[Default])
    getNewTrainingReader.Read()
    trainingID = Integer.Parse(getNewTrainingReader(0).ToString())
    getNewTrainingReader.Close()
End If
Dim insertEventCommand As New SqlCommand()
insertEventCommand.Connection = thisConn
insertEventCommand.CommandText = _
"INSERT TrainingEvent (StudentID, TrainingID, EventDate, Status) VALUES(" & studentID & _
", " & trainingID & ", '" + trainingDate.ToShortDateString() & "', '" & trainingStatus & "')"
insertEventCommand.ExecuteNonQuery()
Return (newTrainingEventEntity)
Finally
thisConn.Dispose()
End Try

    SqlConnection thisConn = null;
try
{
  thisConn = getSqlConnection();
  thisConn.Open();
  string studentName = newTrainingEventEntity.LoginName;
  string trainingTitle = newTrainingEventEntity.Title;
  string trainingType = newTrainingEventEntity.EventType;
  string trainingDescription = newTrainingEventEntity.Description;
  DateTime trainingDate = newTrainingEventEntity.EventDate;
  string trainingStatus = newTrainingEventEntity.Status;
  int studentID = 0;
  SqlCommand studentCmd = new SqlCommand();
  studentCmd.Connection = thisConn;
  studentCmd.CommandText = "SELECT StudentID"
    + " FROM Student"
    + " WHERE LoginName='" + studentName + "'";
  SqlDataReader studentReader = 
    studentCmd.ExecuteReader(CommandBehavior.Default);
  if (studentReader.Read())
  {
    studentID = int.Parse(studentReader[0].ToString());
    studentReader.Close();
  }
  else
  {
    studentReader.Close();
    SqlCommand addStudentCommand = new SqlCommand();
    addStudentCommand.Connection = thisConn;
    addStudentCommand.CommandText = 
      "INSERT Student(LoginName) VALUES('" + studentName + "')";
    addStudentCommand.ExecuteNonQuery();
    SqlCommand getNewStudentCmd = new SqlCommand();
    getNewStudentCmd.Connection = thisConn;
    getNewStudentCmd.CommandText = "SELECT StudentID"
      + " FROM Student"
      + " WHERE LoginName = '" + studentName + "'";
    SqlDataReader getNewStudentReader = 
      getNewStudentCmd.ExecuteReader(CommandBehavior.Default);
    getNewStudentReader.Read();
    studentID = int.Parse(getNewStudentReader[0].ToString());
    getNewStudentReader.Close();
  }
  int trainingID = 0;
  SqlCommand trainingCmd = new SqlCommand();
  trainingCmd.Connection = thisConn;
  trainingCmd.CommandText = "SELECT TrainingID"
    + " FROM TrainingObjects"
    + " WHERE Title = '" + trainingTitle + "'"
    + " AND EventType = '" + trainingType + "'"
    + " AND Description = '" + trainingDescription + "'";
  SqlDataReader trainingReader = 
    trainingCmd.ExecuteReader(CommandBehavior.Default);
  if (trainingReader.Read())
  {
    trainingID = int.Parse(trainingReader[0].ToString());
    trainingReader.Close();
  }
  else
  {
    trainingReader.Close();
    SqlCommand addTrainingCommand = new SqlCommand();
    addTrainingCommand.Connection = thisConn;
    addTrainingCommand.CommandText = 
      "INSERT TrainingObjects(Title, EventType, Description)"
      + " VALUES('" + trainingTitle + "','"
      + trainingType + "','"
      + trainingDescription + "')";
    addTrainingCommand.ExecuteNonQuery();
    SqlCommand getNewTrainingCmd = new SqlCommand();
    getNewTrainingCmd.Connection = thisConn;
    getNewTrainingCmd.CommandText = "SELECT TrainingID"
      + " FROM TrainingObjects"
      + " WHERE Title = '" + trainingTitle + "'"
      + " AND EventType = '" + trainingType + "'"
      + " AND Description = '" + trainingDescription + "'";
    SqlDataReader getNewTrainingReader = 
      getNewTrainingCmd.ExecuteReader(CommandBehavior.Default);
    getNewTrainingReader.Read();
    trainingID = int.Parse(getNewTrainingReader[0].ToString());
    getNewTrainingReader.Close();
  }
  SqlCommand insertEventCommand = new SqlCommand();
  insertEventCommand.Connection = thisConn;
  insertEventCommand.CommandText = "INSERT TrainingEvent" 
    + "(StudentID, TrainingID, EventDate, Status) VALUES("
    + studentID
    + ", " + trainingID
    + ", '" + trainingDate.ToShortDateString() + "'"
    + ", '" + trainingStatus + "')";
  insertEventCommand.ExecuteNonQuery();
  return (newTrainingEventEntity);
}
finally
{
  thisConn.Dispose();
}

  8. Replace the contents of the Delete method with the following code.

    Dim thisConn As SqlConnection = Nothing
Try
    thisConn = getSqlConnection()
    thisConn.Open()

Dim thisCommand As New SqlCommand()
    thisCommand.Connection = thisConn
    thisCommand.CommandText = _
    "DELETE TrainingEvent WHERE TrainingEventID = " & trainingEventID.ToString()
    thisCommand.ExecuteNonQuery()
Finally
    thisConn.Dispose()
End Try

    SqlConnection thisConn = null;
try
{
  thisConn = getSqlConnection();
  thisConn.Open();
  SqlCommand thisCommand = new SqlCommand();
  thisCommand.Connection = thisConn;
  thisCommand.CommandText =     "DELETE TrainingEvent WHERE TrainingEventID = "
    + trainingEventID.ToString();
  thisCommand.ExecuteNonQuery();
}
finally
{
  thisConn.Dispose();
}

  9. Replace the contents of the Update method with the following code.

    Dim thisConn As SqlConnection = Nothing
Try
thisConn = getSqlConnection()
thisConn.Open()
Dim trainingEventID As Integer = trainingEventEntity.TrainingEventID
Dim studentName As String = trainingEventEntity.LoginName
Dim trainingTitle As String = trainingEventEntity.Title
Dim trainingType As String = trainingEventEntity.EventType
Dim trainingDescription As String = trainingEventEntity.Description
Dim trainingDate As DateTime = trainingEventEntity.EventDate
Dim trainingStatus As String = trainingEventEntity.Status
Dim studentID As Integer = 0
Dim studentCmd As New SqlCommand()
studentCmd.Connection = thisConn
studentCmd.CommandText = _
    "SELECT s.StudentID, s.LoginName FROM Student s INNER JOIN TrainingEvent e " _
    + "ON s.StudentID = e.StudentID WHERE e.TrainingEventID = " & trainingEventID.ToString()
Dim studentReader As SqlDataReader = studentCmd.ExecuteReader(CommandBehavior.[Default])
studentReader.Read()
If studentReader(1).ToString() = studentName Then
    studentID = Integer.Parse(studentReader(0).ToString())
    studentReader.Close()
Else
    studentReader.Close()
Dim changeStudentCmd As New SqlCommand()
    changeStudentCmd.Connection = thisConn
    changeStudentCmd.CommandText = "SELECT StudentID FROM Student WHERE LoginName = '" _
        + studentName & "'"
Dim changeStudentReader As SqlDataReader = _
changeStudentCmd.ExecuteReader(CommandBehavior.[Default])
    If changeStudentReader.Read() Then
        studentID = Integer.Parse(changeStudentReader(0).ToString())
        changeStudentReader.Close()
    Else
        changeStudentReader.Close()
Dim addStudentCommand As New SqlCommand()
        addStudentCommand.Connection = thisConn
        addStudentCommand.CommandText = "INSERT Student(LoginName) VALUES('" & studentName & "')"
        addStudentCommand.ExecuteNonQuery()
Dim getNewStudentCmd As New SqlCommand()
        getNewStudentCmd.Connection = thisConn
        getNewStudentCmd.CommandText = _
        "SELECT StudentID FROM Student WHERE LoginName = '" + studentName & "'"
Dim getNewStudentReader As SqlDataReader = _
getNewStudentCmd.ExecuteReader(CommandBehavior.[Default])
        getNewStudentReader.Read()
        studentID = Integer.Parse(getNewStudentReader(0).ToString())
        getNewStudentReader.Close()
    End If
End If
Dim trainingID As Integer = 0
Dim trainingCmd As New SqlCommand()
studentCmd.Connection = thisConn
studentCmd.CommandText = _
"SELECT t.TrainingID, t.Title, t.EventType, t.Description FROM TrainingObjects t " _
+ "INNER JOIN TrainingEvent e ON t.TrainingID = e.TrainingID" _
+ "WHERE e.TrainingEventID = " & trainingEventID.ToString()
Dim trainingReader As SqlDataReader = studentCmd.ExecuteReader(CommandBehavior.[Default])
trainingReader.Read()
If (trainingReader(1).ToString() = trainingTitle) _
    AndAlso (trainingReader(2).ToString() = trainingType) _
    AndAlso (trainingReader(3).ToString() = trainingDescription) Then
    trainingID = Integer.Parse(trainingReader(0).ToString())
    trainingReader.Close()
Else
    trainingReader.Close()
Dim changeTrainingCmd As New SqlCommand()
    changeTrainingCmd.Connection = thisConn
    changeTrainingCmd.CommandText = _
    "SELECT TrainingID FROM TrainingObjects WHERE Title = '" & trainingTitle & _
    "' AND EventType = '" + trainingType & "' AND Description = '" & trainingDescription & "'"
Dim changeTrainingReader As SqlDataReader = _
changeTrainingCmd.ExecuteReader(CommandBehavior.[Default])
    If changeTrainingReader.Read() Then
        trainingID = Integer.Parse(changeTrainingReader(0).ToString())
        changeTrainingReader.Close()
    Else
        changeTrainingReader.Close()
Dim addTrainingCommand As New SqlCommand()
        addTrainingCommand.Connection = thisConn
        addTrainingCommand.CommandText = _
            "INSERT TrainingObjects(Title, EventType, Description) VALUES('" & _
            trainingTitle & "','" & trainingType & "','" & trainingDescription & "')"
        addTrainingCommand.ExecuteNonQuery()
Dim getNewTrainingCmd As New SqlCommand()
        getNewTrainingCmd.Connection = thisConn
        getNewTrainingCmd.CommandText = _
            "SELECT TrainingID FROM TrainingObjects WHERE Title = '" & trainingTitle & _
            "' AND EventType = '" & trainingType & "' AND Description = '" & _
            trainingDescription & "'"
Dim getNewTrainingReader As SqlDataReader = _
getNewTrainingCmd.ExecuteReader(CommandBehavior.[Default])
        getNewTrainingReader.Read()
        trainingID = Integer.Parse(getNewTrainingReader(0).ToString())
        getNewTrainingReader.Close()
    End If
End If
Dim updateEventCommand As New SqlCommand()
updateEventCommand.Connection = thisConn
updateEventCommand.CommandText = _
"UPDATE TrainingEvent SET StudentID=" & studentID & ", TrainingID=" & trainingID & _
", EventDate='" & trainingDate.ToShortDateString() & "'" & ", Status='" & _
trainingStatus & "' WHERE TrainingEventID=" & trainingEventID
updateEventCommand.ExecuteNonQuery()
Finally
thisConn.Dispose()
End Try

    SqlConnection thisConn = null;
try
{
  thisConn = getSqlConnection();
  thisConn.Open();
  int trainingEventID = trainingEventEntity.TrainingEventID;
  string studentName = trainingEventEntity.LoginName;
  string trainingTitle = trainingEventEntity.Title;
  string trainingType = trainingEventEntity.EventType;
  string trainingDescription = trainingEventEntity.Description;
  DateTime trainingDate = trainingEventEntity.EventDate;
  string trainingStatus = trainingEventEntity.Status;
  int studentID = 0;
  SqlCommand studentCmd = new SqlCommand();
  studentCmd.Connection = thisConn;
  studentCmd.CommandText = "SELECT s.StudentID, s.LoginName"
   + " FROM Student s"
   + " INNER JOIN TrainingEvent e ON s.StudentID = e.StudentID"
   + " WHERE e.TrainingEventID = " + trainingEventID.ToString();
  SqlDataReader studentReader = 
    studentCmd.ExecuteReader(CommandBehavior.Default);
  studentReader.Read();
  if (studentReader[1].ToString() == studentName)
  {
    studentID = int.Parse(studentReader[0].ToString());
    studentReader.Close();
  }
  else
  {
    studentReader.Close();
    SqlCommand changeStudentCmd = new SqlCommand();
    changeStudentCmd.Connection = thisConn;
    changeStudentCmd.CommandText = "SELECT StudentID"
      + " FROM Student"
      + " WHERE LoginName = '" + studentName + "'";
    SqlDataReader changeStudentReader = 
    changeStudentCmd.ExecuteReader(CommandBehavior.Default);
    if (changeStudentReader.Read())
    {
      studentID = int.Parse(changeStudentReader[0].ToString());
      changeStudentReader.Close();
    }
    else
    {
      changeStudentReader.Close();
      SqlCommand addStudentCommand = new SqlCommand();
      addStudentCommand.Connection = thisConn;
      addStudentCommand.CommandText = 
       "INSERT Student(LoginName) VALUES('" + studentName + "')";
      addStudentCommand.ExecuteNonQuery();
      SqlCommand getNewStudentCmd = new SqlCommand();
      getNewStudentCmd.Connection = thisConn;
      getNewStudentCmd.CommandText = "SELECT StudentID"
        + " FROM Student"
        + " WHERE LoginName = '" + studentName + "'";
      SqlDataReader getNewStudentReader = 
        getNewStudentCmd.ExecuteReader(CommandBehavior.Default);
      getNewStudentReader.Read();
      studentID = int.Parse(getNewStudentReader[0].ToString());
      getNewStudentReader.Close();
    }
  }
  int trainingID = 0;
  SqlCommand trainingCmd = new SqlCommand();
  studentCmd.Connection = thisConn;
  studentCmd.CommandText = 
    "SELECT t.TrainingID, t.Title, t.EventType, t.Description"
    + " FROM TrainingObjects t"
    + " INNER JOIN TrainingEvent e ON t.TrainingID = e.TrainingID"
    + " WHERE e.TrainingEventID = " + trainingEventID.ToString();
  SqlDataReader trainingReader = 
  studentCmd.ExecuteReader(CommandBehavior.Default);
  trainingReader.Read();
  if ((trainingReader[1].ToString() == trainingTitle)
    && (trainingReader[2].ToString() == trainingType)
    && (trainingReader[3].ToString() == trainingDescription))
  {
    trainingID = int.Parse(trainingReader[0].ToString());
    trainingReader.Close();
  }
  else
  {
    trainingReader.Close();
    SqlCommand changeTrainingCmd = new SqlCommand();
    changeTrainingCmd.Connection = thisConn;
    changeTrainingCmd.CommandText = "SELECT TrainingID"
    + " FROM TrainingObjects"
    + " WHERE Title = '" + trainingTitle + "'"
    + " AND EventType = '" + trainingType + "'"
    + " AND Description = '" + trainingDescription + "'";
    SqlDataReader changeTrainingReader = 
      changeTrainingCmd.ExecuteReader(CommandBehavior.Default);
    if (changeTrainingReader.Read())
    {
      trainingID = int.Parse(changeTrainingReader[0].ToString());
      changeTrainingReader.Close();
    }
    else
    {
      changeTrainingReader.Close();
      SqlCommand addTrainingCommand = new SqlCommand();
      addTrainingCommand.Connection = thisConn;
      addTrainingCommand.CommandText = "INSERT TrainingObjects(" 
        + "Title, EventType, Description) VALUES('"
        + trainingTitle + "','"
        + trainingType + "','"
        + trainingDescription + "')";
      addTrainingCommand.ExecuteNonQuery();
      SqlCommand getNewTrainingCmd = new SqlCommand();
      getNewTrainingCmd.Connection = thisConn;
      getNewTrainingCmd.CommandText = "SELECT TrainingID"
        + " FROM TrainingObjects"
        + " WHERE Title = '" + trainingTitle + "'"
        + " AND EventType = '" + trainingType + "'"
        + " AND Description = '" + trainingDescription + "'";
      SqlDataReader getNewTrainingReader = 
        getNewTrainingCmd.ExecuteReader(CommandBehavior.Default);
      getNewTrainingReader.Read();
      trainingID = int.Parse(getNewTrainingReader[0].ToString());
      getNewTrainingReader.Close();
    }
  }
  SqlCommand updateEventCommand = new SqlCommand();
  updateEventCommand.Connection = thisConn;
  updateEventCommand.CommandText = "UPDATE TrainingEvent"
    + " SET StudentID=" + studentID
    + ", TrainingID=" + trainingID
    + ", EventDate='" + trainingDate.ToShortDateString() + "'"
    + ", Status='" + trainingStatus + "'"
    + " WHERE TrainingEventID=" + trainingEventID;
  updateEventCommand.ExecuteNonQuery();
}
finally
{
  thisConn.Dispose();
}

Deploying and Using the Solution

To deploy and use the solution

  1. In the Solution Explorer window, right-click BDCModel and then click Deploy.

  2. On the Start menu, click All Programs. In the Microsoft SharePoint 2010 Products group, click SharePoint 2010 Central Administration.

  3. In the Application Management section, click Manage service applications.

  4. Click Business Data Connectivity Service.

  5. Point to the TrainingEventEntity link, and then click the arrow that appears.

  6. Click Set Permissions.

  7. Click Browse and then click All Users.

  8. Click All Authenticated Users.

  9. Click Add.

  10. Click OK.

  11. Click Add.

  12. Check the Edit check box.

  13. Check the Execute check box.

  14. Check the Selectable In Clients check box.

  15. Check the Set Permissions check box.

  16. Click OK.

  17. Open your SharePoint site; for example, http://intranet.contoso.com.

  18. On the Site Actions menu, click More Options to display a list of templates.

  19. Click External List.

  20. Click Create.

  21. In the Name text box, type Training Events.

  22. In the Data source configuration section, click Select External Content Type to display the External Content Type Picker dialog box.

  23. Click BdcModel1 and then click OK.

  24. Click Create to display the empty list.

  25. On the ribbon, click the Items tab.

  26. Click New Item.

  27. Create the new item and click Save.

  28. View the rows in the SQL database.

Additional Resources

For more information, see the following resources: