Network Layout

Install Visual Studio 2010 on Computer K2

Create a Database:Express in K2\SharePoint SQL Server express

Create a table:Course in Database:Express

CourseID is auto_incremental.

Grade has the default value of zero.

USE [Express]
GO

/****** Object: Table [dbo].[Course] Script Date: 01/21/2012 06:02:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Course](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CourseName] [nchar](50) NOT NULL,
[Description] [nchar](50) NOT NULL,
[Grade] [float] Not NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] 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

ALTER TABLE [dbo].[Course] ADD DEFAULT ((0)) FOR [Grade]
GO

Create an external content type to present the data of Course table

Start Visual Studio

If you select the default names created by Visual Studio, there is no clue about the external content type in SharePoint Central Administration.

Customize the names

The result will be:

Make sure that you save the project before you continue!

Open Course.vb

Customize the properties to match the Table:Course of the database.

Partial Public Class Course

Private _CourseID As Int16
Private _CourseName As String
Private _Description As String
Private _Grade As Decimal
Public Property CourseID() As Int16
Get
Return _CourseID
End Get
Set(ByVal value As Int16)
_CourseID = value
End Set
End Property
Public Property CourseName() As String
Get
Return _CourseName
End Get
Set(ByVal value As String)
_CourseName = 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 Property Grade() As Decimal
Get
Return _Grade
End Get
Set(ByVal value As Decimal)
_Grade = value
End Set
End Property

End Class

Course.vb

Because the type of CourseID in the database is Integer 16, the TYPE Name is set to Int16.

Similarly, the Type Name is set to Decimal for Grade because the Grade Column in the table is decimal type.

CourseService.vb defines alll the functions that match the methods defined in BDC model.

CourseService.vb

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Public Class CourseService

Private Shared Function getSqlConnection() As SqlConnection

Dim sqlConn As New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Express;Data Source=K2\SharePoint")
Return (sqlConn)

End Function

Public Shared Function ReadItem(ByVal id As Short) As Course

Dim thisConn As SqlConnection = Nothing
Dim objCourse = New Course()
Try
objCourse = New Course()
thisConn = getSqlConnection()
thisConn.Open()
Dim thisCmd As New SqlCommand()
thisCmd.CommandText = "Select CourseID,CourseName,Description,Grade from Course Where CourseID= " & id.ToString()
thisCmd.Connection = thisConn
Dim thisReader As SqlDataReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection)
If thisReader.Read() Then
objCourse.CourseID = id
objCourse.CourseName = thisReader(1).ToString()
objCourse.Description = thisReader(2).ToString
objCourse.Grade = Decimal.Parse(thisReader(3).ToString())
Else
objCourse.CourseID = -1
objCourse.CourseName = "Data Not Found"
objCourse.Description = "Data Not Found"
objCourse.Grade = 0
End If
thisReader.Close()
Return (objCourse)

Catch ex As Exception
objCourse.CourseID = -1
objCourse.CourseName = "Data Not Found"
objCourse.Description = "Data Not Found"
objCourse.Grade = 0
Return (objCourse)
Finally
thisConn.Dispose()
End Try

End Function

Public Shared Function ReadList() As IEnumerable(Of Course)

Dim thisConn As SqlConnection = Nothing
Dim AllCourses As List(Of Course)
Try
thisConn = getSqlConnection()
AllCourses = New List(Of Course)()
thisConn.Open()
Dim thisCmd As New SqlCommand()
thisCmd.Connection = thisConn
thisCmd.CommandText = "Select CourseID,CourseName,Description,grade from Course"
Dim thisReader As SqlDataReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection)
While thisReader.Read()
Dim objCourse As New Course
objCourse.CourseID = Short.Parse(thisReader(0).ToString())
objCourse.CourseName = thisReader(1).ToString()
objCourse.Description = thisReader(2).ToString()
objCourse.Grade = Decimal.Parse(thisReader(3).ToString())
AllCourses.Add(objCourse)
End While
thisReader.Close()
Dim courseList As Course() = New Course(AllCourses.Count - 1) {}
For courseCounter As Integer = 0 To AllCourses.Count - 1
courseList(courseCounter) = AllCourses(courseCounter)
Next
Return (courseList)
Catch ex As Exception
Dim errCourseList As Course() = New Course(0) {}
Dim errCourse As New Course()
errCourse.CourseID = -1
errCourse.CourseName = ex.Message
errCourse.Description = ex.Message
errCourse.Grade = 0
errCourseList(0) = errCourse
Return (errCourseList)
Finally
thisConn.Dispose()
End Try

End Function

Public Shared Function Create(ByVal newCourse As Course) As Course

Dim thisConn As SqlConnection = Nothing
Try
thisConn = getSqlConnection()
thisConn.Open()
Dim _CourseID As Short = 0
Dim _courseName As String = newCourse.CourseName
Dim _courseDescription As String = newCourse.Description
Dim _courseGrade As Decimal = newCourse.Grade
Dim sqlCmd As New SqlCommand()
sqlCmd.Connection = thisConn
sqlCmd.CommandText = "Select CourseID from Course where CourseName='" & _courseName & "'"
Dim courseReader As SqlDataReader = sqlCmd.ExecuteReader(CommandBehavior.Default)
If courseReader.Read() Then
_CourseID = Short.Parse(courseReader(0).ToString())
courseReader.Close()
Else
courseReader.Close()
Dim addCourse As New SqlCommand()
addCourse.Connection = thisConn
addCourse.CommandText = "Insert Course(CourseName,Description,Grade) VALUES('" & _courseName & "','" & _courseDescription & "'," & _courseGrade & ")"
addCourse.ExecuteNonQuery()
End If
Return (newCourse)
Finally
thisConn.Dispose()
End Try

End Function

Public Shared Sub Update(ByVal course As Course)

Dim thisConn As SqlConnection = Nothing
Try
thisConn = getSqlConnection()
thisConn.Open()
Dim _courseID As Short = Course.CourseID
Dim _courseName As String = Course.CourseName
Dim _courseDescription As String = Course.Description
Dim _courseGrade As Decimal = Course.Grade

Dim updateCourse As New SqlCommand()
updateCourse.Connection = thisConn
updateCourse.CommandText = "UPDATE Course Set CourseName='" & _courseName & "',Description='" & _courseDescription & "',grade=" & _courseGrade & " Where CourseID=" & _CourseID
updateCourse.ExecuteNonQuery()

Catch ex As Exception
Finally
thisConn.Dispose()
End Try

End Sub

Public Shared Sub Delete(ByVal courseID As Short)

Dim thisConn As SqlConnection = Nothing
Try

thisConn = getSqlConnection()
thisConn.Open()
Dim thisCmd As New SqlCommand()
thisCmd.Connection = thisConn
thisCmd.CommandText = "Delete course where courseID=" & courseID
thisCmd.ExecuteNonQuery()

Catch ex As Exception
Finally
thisConn.Dispose()
End Try

End Sub

End Class

CourseService.vb

Build the code

Deploy the code

SharePoint Central Administration

Application Management

Manage Service Applications

Business Data Connection Service

Assign Domain Users group all the permissions

Create an External Content List based on the external content type: Express.Course

Solution:

RequiredInForms System.Boolean false or true

Do the same for Create, ReadList, and Update methods.

If you first modify the Custom Properties for ReadItem and then add Create, ReadList, and Update methods, the Custom Properties settings are automatically in place.

List Settings--mission accomplished.

Even though CourseID is auto-increased in database and Grade accepts the null value, you have to input values for them here. I cannot find a way to validate the data type.

You must set Pre-Updater Field to True for CourseID TypeDescriptor. If not, cannot update the record.

If you retrieve data from Queen SQL Server as follows, you should follow the Kerberos Delegation page. I don't know how to write code to use Secure Store Service.

Private Shared Function getSqlConnection() As SqlConnection

Dim sqlConn As New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ShareDB;Data Source=Queen")
Return (sqlConn)

End Function