The sforce 'NET Managed Data Provider

1
The sforce .NET Managed Data Provider

• Karl Houseknecht, AVP, Solution Architect

2
Sforce API Limitations

• Low-level interface.
• Steep learning curve.
• Repetitive tasks.
• Best practices not built in.
• Mixture of text and non-text commands.
• Not easily fit into .NET data access paradigm.
• Cannot use ADO.NET data binding.

3
What a .NET developer wants

• Connection management
• Consistent Command interface
• DataSet integration
• Data binding

4
The Sforce .NET Managed Data Provider
5
CRUD Command Syntax

• SELECT Follows standard Sforce query syntax
• RETRIEVE ltobjectTypegtltfield1, field2,
  fieldngtIds ltId1, Id2, Idngt
• INSERT ltobjectTypegt ltfield1, field2, fieldngt
  VALUES ltvalue1, value2, valuengt
• UPDATE ltobjectTypegt SET ltfield1gtltvalue1gt,
  ltfield2gtltvalue2gt, ltfieldngtltvaluengtWHERE Id
  ltId valuegt
• DELETE ltId valuegt
• GETUPDATED ltobjectTypegt ltstartTimegt, ltendTimegt
• GETDELETED ltobjectTypegt ltstartTimegt, ltendTimegt

6
Schema Command Syntax

• DESCRIBEGLOBAL
• DESCRIBESOBJECT ltobjectTypegt
• DESCRIBESOBJECTFIELDS ltobjectTypegt

7
SforceDataReader Example
Dim conSFDC As New SforceConnection("User
IDuser_at_company.comPasswordmypassword")
conSFDC.QueryBatchSize 5
Dim cmdGetAccounts As New SforceCommand("select
Id, Name from Account", conSFDC) Dim
rdrAccounts As SforceDataReader Dim
sbdAccounts As New StringBuilder Try
conSFDC.Open() rdrAccounts
cmdGetAccounts.ExecuteReader While
rdrAccounts.Read With
sbdAccounts
.Append(rdrAccounts.GetString(0))
.Append(", ")
.Append(rdrAccounts("Name").ToString)
.Append(ControlChars.CrLf)
End With End While Catch ex
As Exception MessageBox.Show(ex.Messag
e) Finally If Not rdrAccounts
Is Nothing Then rdrAccounts.Close()
cmdGetAccounts.Dispose()
conSFDC.Dispose() MessageBox.Show(sbdA
ccounts.ToString) End Try
8
SforceDataAdapter Example
Try Dim dadAccounts As New
SforceDataAdapter("select Id, Name from Account",
_ "User
Iduser_at_company.comPasswordmypassword")
Dim dstAccounts As New DataSet
dadAccounts.Fill(dstAccount
s) grdData.DataSource
dstAccounts grdData.DataMember
dstAccounts.Tables(0).TableName Catch ex
As Exception MessageBox.Show(ex.Messag
e) End Try
9
Insert Example
Dim conSFDC As New SforceConnection("User
IDuser_at_company.comPasswordmypassword") Dim
cmdInsertAccount As New SforceCommand("insert
Account (Name) values(_at_Name)", _ conSFDC) cmd
InsertAccount.Parameters("_at_Name").Value
"DreamForce Test" Dim strId As String Try
conSFDC.Open() strId cmdInsertAccount.Execut
eScalar.ToString Catch ex As Exception
MessageBox.Show(ex.Message) Finally
cmdInsertAccount.Dispose() conSFDC.Dispose()
MessageBox.Show(strId) End Try
10
Update Example
Dim conSFDC As New SforceConnection("User
IDuser_at_company.comPasswordmypassword") Dim
strCmd As String "update Account set Name
_at_Name where Id _at_Id" Dim cmdUpdate As New
SforceCommand(strCmd, conSFDC)) cmdUpdate.Paramet
ers("_at_Name").Value InputBox("Input New
Name") cmdUpdate.Parameters("_at_Id").Value
InputBox("Input Account Id") Dim
intRecordsAffected As Integer Try
conSFDC.Open() intRecordsAffected
cmdUpdate.ExecuteNonQuery Catch ex As
Exception MessageBox.Show(ex.Message) Finally
cmdUpdate.Dispose() conSFDC.Dispose()
MessageBox.Show(intRecordsAffected.ToString "
records affected.") End Try
11
Delete Example
Dim conSFDC As New
SforceConnection("User IDuser_at_company.comPasswor
dmypassword") Dim cmdDeleteAccount As
New SforceCommand("delete _at_Id", conSFDC)
cmdDeleteAccount.Parameters("_at_Id").Value
InputBox("Input Account Id") Dim
intRecordsAffected As Integer Try
conSFDC.Open()
intRecordsAffected cmdDeleteAccount.ExecuteNonQu
ery Catch ex As Exception
MessageBox.Show(ex.Message) Finally
cmdDeleteAccount.Dispose()
conSFDC.Dispose() MessageBox.Show(intR
ecordsAffected.ToString " records affected.")
End Try
12
Porting between SQL Server and Sforce
13
Data Model
14
Its Open Source!!!

• The Sforce .NET Managed Data Provider will be
  available on the Sforce SourceForge site soon.
• http//sourceforge.net/projects/sforce

15
References

• Inside .NET Managed Providers -
  http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/dndive/html/data010112001.asp
• Implementing a .NET Framework Data
  Providerhttp//msdn.microsoft.com/library/default
  .asp?url/library/en-us/cpguide/html/cpconimplemen
  tingnetdataprovidergettingstarted.asp