>Insert-Update-Delete Actions on SQLCE table via VFP

>SQL Server Compact 3.5 SP2 is a free, easy-to-use embedded database engine that lets developers build robust Windows Desktop and mobile applications that run on all Windows platforms including Windows XP, Vista, Pocket PC, and Smartphone.

SQL Server Compact Edition shares a common programming model with the other SQL Server editions, enabling developers to transfer skills and knowledge quickly and easily. SQL Server Compact Edition offers a maximum database size of 4 GB, the ability to run in with applications, contains subsets of Transact-SQL and ADO.NET, and, most importantly, sync technologies – all in a compact footprint.

Product Highlights

  • Compact and Capable
  • merge replication
  • Remote Data Access (RDA)
  • Familiar Database for Rapid Application Development
  • Desktop Deployment Options
  • Strong Data Security
  • Flexible Data Access
  • Expand Business Insights Further
  • Build on a platform that can grow with your business
  • Best of all, it’s FREE

you can also download compact vs express comparison doc

After the short explanation about SQLCE let us see IUD samples also select only
i’ve preapared two function named SqlCeQuery() for select only which is used CursorAdapter CA and SqlCeNonQuery() for IUD actions


lcDataSource = [Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;Persist Security Info=False;]

displaySample()
*-updateSample()
*-insertSample()
*-deleteSample()

*-display data
Function displaysample
TEXT TO m.lcSQLquery TEXTMERGE noshow
Select * from [Customers]
ENDTEXT
SqlCeQuery(m.lcDataSource,m.lcSqlQuery,[Customers],.T.)
Endfunc

*- insert test
Function insertSample
TEXT TO m.lcSQLInsert TEXTMERGE noshow
INSERT INTO [Customers] ([Customer ID] ,[Company Name]) Values('SOYKAN','Soykansoft')
ENDTEXT
SqlCENonQuery(m.lcDataSource,m.lcSqlInsert)
displaySample()
Endfunc

*- update test
Function updateSample
TEXT TO m.lcSQLupdate TEXTMERGE noshow
update [Customers] set [Contact Name]='SoykaN' where [Customer ID]='ALFKI'
ENDTEXT
SqlCENonQuery(m.lcDataSource,m.lcSqlUpdate)
displaySample()
Endfunc

*- delete test
Function deleteSample
TEXT TO m.lcSQLdelete TEXTMERGE noshow
delete from [Customers] where [Customer ID]='SOYKAN'
ENDTEXT
SqlCENonQuery(m.lcDataSource,m.lcSQLDelete)
displaySample()
Endfunc

Function SqlCeQuery
Lparameters tcDataSource,tcSQL,tcCursor,tlBrowse
Public oCA As CursorAdapter
Local oConn As ADODB.Connection
Local oRS As ADODB.Recordset
Local oComm As ADODB.Command
Local oException As Exception
Local cConnString

cConnString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;"+ m.tcDataSource

Try
oConn = Createobject('ADODB.Connection')

* Ensure that you handle userid and password if not
* specified in connection string.
* ex. oConn.Open(cConnString, userid, password)
oConn.Open(cConnString)
oComm = Createobject("ADODB.Command")
oComm.ActiveConnection = oConn

oRS = Createobject("ADODB.Recordset")
oRS.Datasource.CursorLocation = 3 &&adUseClient
oRS.Datasource.LockType = 3 &&adLockOptimistic
oRS.ActiveConnection = oConn

oCA=Createobject("CursorAdapter")
oCA.DataSourceType = "ADO"
oCA.Datasource = oRS
oCA.MapBinary = .T.
oCA.MapVarchar = .T.
oCA.SendUpdates = .T.
*oCA.KeyFieldList = "MyGuid" && Put the Key Field list here
oCA.WhereType = 1 && Key Fields

oCA.Alias = m.tcCursor
oCA.SelectCmd = m.tcSQL
oCA.InsertCmdDataSourceType = "ADO"
oCA.UpdateCmdDataSourceType = "ADO"
oCA.DeleteCmdDataSourceType = "ADO"
oCA.RefreshCmdDataSourceType = "ADO"

oCA.InsertCmdDataSource = oComm
oCA.UpdateCmdDataSource = oComm
oCA.DeleteCmdDataSource = oComm
oCA.RefreshCmdDataSource = oComm

If !oCA.CursorFill(.F.,.F.,-1,oComm)
* Replace with error code here
Local laError
Dimension laError[1]
Aerror(laError)
Messagebox(laError[2])
Else
* Replace with user code here. Code below allows for
* you to edit and send updates to the backend.
Local laFlds,lcStr,lnFldCount,i
Dimension laFlds[1]
lnFldCount=Afields(laFlds)
lcStr=""
lcStr2 = ""
For i = 1 To lnFldCount
lcStr = lcStr + laFlds[m.i,1] + ","
*-lcStr2 = lcStr2 + laFlds[m.i,1] + " CARI."+laFlds[m.i,1]+"," && There should be SPACE before CARI
lcStr2 = lcStr2 + laFlds[m.i,1] + m.tcCursor+"."+laFlds[m.i,1]+"," && There should be SPACE before CARI
Endfor
*- IUD ignored
*oCA.UpdatableFieldList = Left(lcStr , Len(lcStr )-1) && To remove last comma
*oCA.UpdateNameList = Left(lcStr2, Len(lcStr2)-1) && To remove last comma

*-BROWSE NORMAL NOWAIT
Endif

Catch To oException
* Replace with exception handling code here
Messagebox(oException.Message)
Endtry

If m.tlBrowse
Browse Normal
Endif

*- close rs and connection
*-oRS.Close()
oConn.Close()
Endfunc

Function SqlCENonQuery
*!* for insert update delete
Lparameters tcDataSource,tcSQL
Local oConn As ADODB.Connection
Local oRS As ADODB.Recordset
Local oComm As ADODB.Command
Local oException As Exception
Local cConnString
Local cSQL
cConnString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + m.tcDataSource

Try
oConn = Createobject('ADODB.Connection')

* Ensure that you handle userid and password if not
* specified in connection string.
* ex. oConn.Open(cConnString, userid, password)
oConn.Open(cConnString)
oComm = Createobject("ADODB.Command")
oComm.ActiveConnection = oConn

oRS = Createobject("ADODB.Recordset")
oRS.Datasource.CursorLocation = 3 &&adUseClient
oRS.Datasource.LockType = 3 &&adLockOptimistic
oRS.ActiveConnection = oConn

Catch To oException
* Replace with exception handling code here
Messagebox(oException.Message)
Endtry

*-oConn.Open() && if closed open
oConn.Execute(m.tcSQL)
*-oRS.Close()
oConn.Close() && if closed open
Endfunc

Advertisements

One thought on “>Insert-Update-Delete Actions on SQLCE table via VFP

  1. English please 😦

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s