Network Layout
In K2\SharePoint MS SQL Express, create a database:Express and create a table:Course
USE [Express]
GO
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
In SQL Server 2008 R2 of computer:queen, create a database: ShareDB and Create a table: Course
USE [ShareDB]
GO
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
Visual Studio 2010 Professional is installed on K2.
Create a new project (BdcExpress) based on Business Data Connectity Model
Replace the Course.vb with Course.vb
Replace the CourseService.vb with CourseService.vb
Inside the link CourseService.vb, the connection points to K2\SharePoint SQL Express server, the local SQL Server.
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
Build and deploy the solution
In SharePoint site, create an External List based on the External Content Type:
Can read data, Insert data, delete data, and update data
Delete the External Content Type
Modify the database connection
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
Build and Deploy the solution
Create an external list based on the new external content type: BdcExpress.Course
You cannot open the external list from Computer Queen.
Follow the link to modify the .bdcm file
Retrieve Username and Password from SSS
set SQL Server 2008 securit mode of Queen computer to SQL Server authentication
Modify the connection string
Private Shared Function getSqlConnection(ByVal username As String, ByVal password As String) As SqlConnection
'Dim sqlConn As New SqlConnection("initial Catalog=sharedb;Data Source=queen;User ID=" & username & ";Password=" & password)
Dim sqlStr As String = "Initial Catalog=ShareDB;Data Source=Queen;User ID=" & username & ";Password=" & password
Dim sqlConn As New SqlConnection(sqlStr)
Return (sqlConn)
End Function
If you want to use Windows Authentication mode in SQL Server of Queen computer, order BdcMetaMan from Bdc Meta Man.
or download the 14-day trial version Bdc meta man
It is coded in C#.
Project Name:BdcSharp
Add References:
Add a reference to Microsoft.BusinessData.dll (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\)
Add a reference to Microsoft.Office.SecureStoreService.dll (C:\Windows\assembly\GAC_MSIL\Microsoft.Office.SecureStoreService\14.0.0.0__71e9bce111e9429c\)
SQL Server connection string
Initial Catalog=ShareDB;Data Source=Queen;User ID=sa;Password=mypassword
Queen SQL Server security: SQL Server and Windows Authentication mode
Project with c#
Project Name:BdcSharp
SQL Server Security: Windows Authentication mode
SQL server connection string
Initial Catalog=ShareDB;Data Source=Queen;Integrated Security=SSPI
Impersonate user
Retrieve Username and Password from Secure Store Service
Because the Target Application ID is SINGLE type, only the Credential Owner:aaa\administrator and Windows user:Nicole can access the Target Application ID:QueenID.
I will not use aaa\nicole as Windows User Name. Instead Nicole is typed in. The domain name is hard-coded in the follow program.
Project Name: BdcSharp
Impersonate Username Retrieved from Secure Store Service
using System;
using System.Runtime.InteropServices;
using System.Security.Principal;
public class Impersonate : IDisposable
{
public const int LOGON32_LOGON_INTERACTIVE = 2;
public const int LOGON32_PROVIDER_DEFAULT = 0;
WindowsImpersonationContext impersonationContext;
public Impersonate()
{
//Default login is used.
this.impersonateValidUser("administrator", "aaa", "Vista2006");
}
public Impersonate(string username, string domain, string password)
{
this.impersonateValidUser(username, domain, password);
}
[DllImport("advapi32.dll")]
private static extern int LogonUserA(String lpszUserName,
String lpszDomain,
String lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern int DuplicateToken(IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern bool RevertToSelf();
[DllImport("kernel32.dll", CharSet = CharSet.Auto)]
private static extern bool CloseHandle(IntPtr handle);
private bool impersonateValidUser(string userName, string domain, string password)
{
WindowsIdentity tempWindowsIdentity;
IntPtr token = IntPtr.Zero;
IntPtr tokenDuplicate = IntPtr.Zero;
if (RevertToSelf())
{
if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) != 0)
{
if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
{
tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
impersonationContext = tempWindowsIdentity.Impersonate();
if (impersonationContext != null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if (token != IntPtr.Zero)
CloseHandle(token);
if (tokenDuplicate != IntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}
private void undoImpersonation()
{
impersonationContext.Undo();
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
~Impersonate()
{
Dispose(false);
}
protected virtual void Dispose(bool disposing)
{
if(disposing)
{
//Dispose of managed resources.
if(impersonationContext != null)
{
this.undoImpersonation();
impersonationContext.Dispose();
impersonationContext = null;
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;
using Microsoft.SharePoint;
public static class SecureStoreUtils
{
public static Dictionary<string, string> GetCredentials(string applicationID)
{
var serviceContext = SPServiceContext.Current;
var secureStoreProvider = new SecureStoreProvider { Context = serviceContext };
var credentialMap = new Dictionary<string, string>();
using (var credentials = secureStoreProvider.GetCredentials(applicationID))
{
var fields = secureStoreProvider.GetTargetApplicationFields(applicationID);
for (var i = 0; i < fields.Count; i++)
{
var field = fields[i];
var credential = credentials[i];
var decryptedCredential = ToClrString(credential.Credential);
credentialMap.Add(field.Name, decryptedCredential);
}
}
return credentialMap;
}
public static string ToClrString(this SecureString secureString)
{
var ptr = Marshal.SecureStringToBSTR(secureString);
try
{
return Marshal.PtrToStringBSTR(ptr);
}
finally
{
Marshal.FreeBSTR(ptr);
}
}
}
Using the above two classes
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualBasic;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace BdcSharp.BdcModel1
{
/// <summary>
/// All the methods for retrieving, updating and deleting data are implemented in this class file.
/// The samples below show the finder and specific finder method for Entity1.
/// </summary>
public class Entity1Service
{
private static SqlConnection getSqlConnection()
{
}
public static Entity1 ReadItem(short id)
{
//TODO: This is just a sample. Replace this simple sample with valid code.
SqlConnection aConn = null;
Entity1 objCourse;
SqlDataReader aReader;
objCourse = new Entity1();
try
{
aConn = getSqlConnection();
aConn.Open();
SqlCommand aCmd = new SqlCommand();
aCmd.CommandText = "Select CourseID,CourseName,Description,Grade from Course Where CourseID= " + id.ToString();
aCmd.Connection = aConn;
aReader = aCmd.ExecuteReader(CommandBehavior.CloseConnection);
if (aReader.Read())
{
objCourse.CourseID = id;
objCourse.CourseName = aReader[1].ToString();
objCourse.Description = aReader[2].ToString();
objCourse.Grade = decimal.Parse(aReader[3].ToString());
}
else
{
objCourse.CourseID = -1;
objCourse.CourseName = "Data Not Found";
objCourse.Description = "Data Not Found";
objCourse.Grade = 0;
}
aReader.Close();
return (objCourse);
}
catch (Exception ex)
{
objCourse.CourseID = -1;
objCourse.CourseName = "Data Not Found";
objCourse.Description = ex.ToString();
objCourse.Grade = 0;
return (objCourse);
}
finally
{
aConn.Dispose();
}
SqlConnection aConn = null;
List<Entity1> AllEntity1s = null;
SqlDataReader aReader;
try
{
aConn = getSqlConnection();
AllEntity1s = new List<Entity1>();
aConn.Open();
SqlCommand thisCmd = new SqlCommand();
thisCmd.Connection = aConn;
thisCmd.CommandText = "Select courseID,courseName,Description,grade from course";
aReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (aReader.Read())
{
Entity1 objEntity1 = new Entity1();
objEntity1.CourseID = short.Parse(aReader[0].ToString());
objEntity1.CourseName = aReader[1].ToString();
objEntity1.Description = aReader[2].ToString();
objEntity1.Grade = decimal.Parse(aReader[3].ToString());
AllEntity1s.Add(objEntity1);
}
aReader.Close();
Entity1[] Entity1List = new Entity1[AllEntity1s.Count];
for (int courseCounter = 0; courseCounter <= AllEntity1s.Count - 1; courseCounter++)
{
Entity1List[courseCounter] = AllEntity1s[courseCounter];
}
return (Entity1List);
}
catch (Exception ex)
{
Entity1[] errEntity1List = new Entity1[1];
Entity1 errCourse = new Entity1();
errCourse.CourseID = -1;
errCourse.CourseName = ex.Message;
errCourse.Description = ex.Message;
errCourse.Grade = 0;
errEntity1List[0] = errCourse;
return (errEntity1List);
}
finally
{
aConn.Dispose();
}
}
}
}
}
Properties of class Entity1
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BdcSharp.BdcModel1
{
/// <summary>
/// This class contains the properties for Entity1. The properties keep the data for Entity1.
/// If you want to rename the class, don't forget to rename the entity in the model xml as well.
/// </summary>
public partial class Entity1
{
private Int16 _CourseID;
private string _CourseName;
private string _Description;
private decimal _Grade;
public Int16 CourseID
{
get { return _CourseID; }
set { _CourseID = value; }
}
public string CourseName
{
get { return _CourseName; }
set { _CourseName = value; }
}
public string Description
{
get { return _Description; }
set { _Description = value; }
}
public decimal Grade
{
get { return _Grade; }
set { _Grade = value; }
}
}
}
Create Target Application ID:QueenID----Group type
The C# code:
The updated code is: