** Once ODBC connection ile database ve test tablo yaratiyoruz
** Ayni zamanda dolduracagimiz datayi da bir cursora aliyoruz - crsCustomers
CreateTestData('Driver={SQL Native Client};Trusted_connection=Yes;Server=.\SQLExpress')
** CursorAdapter ile hiz testi, zamanlamaya VFP tarafindaki islemleri dahi katiyoruz
Start = Seconds()
Local loCursor, lcConStr
lcConStr = 'Provider=SQLNCLI;Trusted_connection=Yes;Server=.\SQLExpress'
loCursor = Createobject('CaGeneric',m.lcConStr)
With loCursor
.Alias = 'Customers'
.SelectCmd = 'SELECT * FROM [VFPInsertSpeedTest]..[Customers]'
Endwith
If loCursor.QueryFill()
Select (loCursor.Alias)
loCursor.MakeUpdatable('[VFPInsertSpeedTest]..Customers','customerId')
? loCursor.Alias, Reccount(loCursor.Alias)
Append From Dbf('crsCustomers')
? loCursor.Alias, Reccount(loCursor.Alias)
? 'Insert islemi guncelleniyor'
Tableupdate(2,.T.,loCursor.Alias)
? 'Insert islemi bitti. Gecen sure (saniye)', Seconds()-m.start
Endif
Procedure CreateTestData(tcConnectionString)
Local lcCreateDb, lcCreateTable, lnHandle
lnHandle = Sqlstringconnect(m.tcConnectionString)
** Test database yarat
TEXT TO m.lcCreateDb TEXTMERGE noshow
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'VFPInsertSpeedTest')
DROP DATABASE [VFPInsertSpeedTest]
CREATE DATABASE [VFPInsertSpeedTest]
ENDTEXT
? 'Database yaratildi',SQLExec(m.lnHandle, m.lcCreateDb)
SQLExec(m.lnHandle, 'Use [VFPInsertSpeedTest]')
** Northwind Customers tablosunu bos olarak kopyele
** Indexler insert islemini yavaslatir, yavaslik var mi gormek icin
** bazi indeksleri de yaratalim
TEXT TO m.lcCreateTable TEXTMERGE noshow
SELECT * into [VFPInsertSpeedTest]..[Customers]
from [Northwind]..[Customers]
where 1=2
ALTER TABLE [Customers]
ADD CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
);
CREATE NONCLUSTERED INDEX [CompanyName] ON [Customers]
(
[CompanyName] ASC
);
CREATE NONCLUSTERED INDEX [City] ON [Customers]
(
[City] ASC
);
CREATE NONCLUSTERED INDEX [PostalCode] ON [Customers]
(
[PostalCode] ASC
);
CREATE NONCLUSTERED INDEX [Region] ON [Customers]
(
[Region] ASC
);
ENDTEXT
? 'Tablo yaratildi',SQLExec(m.lnHandle, m.lcCreateTable)
** Son olarak Northwind..Customers'dan datayi crsCustomers tablosuna aliyoruz
? 'Data alindi',SQLExec(m.lnHandle, 'select * from [Northwind]..[Customers]', 'crsCustomers')
Endproc
Define Class CaGeneric As CursorAdapter
CompareMemo = .F.
FetchAsNeeded = .T.
FetchSize = 100
FetchMemo = .T.
BatchUpdateCount = 100
WhereType = 1
AllowSimultaneousFetch = .T.
MapVarchar = .T.
MapBinary = .T.
BufferModeOverride = 5
DataSourceType = 'ADO'
InsertCmdDataSourceType = 'ADO'
UpdateCmdDataSourceType = 'ADO'
DeleteCmdDataSourceType = 'ADO'
Procedure Init(tcConnectionString)
Set Multilocks On
Local loConnDataSource
loConnDataSource = Createobject('ADODB.Connection')
loConnDataSource.ConnectionString = m.tcConnectionString
loConnDataSource.Open()
This.Datasource = Createobject('ADODB.RecordSet')
This.Datasource.CursorLocation = 3 && adUseClient
This.Datasource.LockType = 3 && adLockOptimistic
This.Datasource.ActiveConnection = m.loConnDataSource
loCommand = Createobject('ADODB.Command')
loCommand.ActiveConnection = loConnDataSource
This.AddProperty('oCommand',loCommand)
Store loCommand To ;
This.UpdateCmdDataSource,;
This.InsertCmdDataSource,;
This.DeleteCmdDataSource
Endproc
Procedure MakeUpdatable(tcTableName,tckeyField,tlDoNotIncludeKey)
This.Tables = m.tcTableName
This.KeyFieldList = m.tckeyField
Local ix, lnUpdateableFCount
lnUpdateableFCount = Fcount(This.Alias)-Iif(This.DataSourceType='ADO',1,0) && last one is ADOBOOKMARK
For ix = 1 To m.lnUpdateableFCount
If !m.tlDoNotIncludeKey Or !(Upper(Field(m.ix,This.Alias,0)) == Upper(m.tckeyField))
This.UpdatableFieldList = This.UpdatableFieldList + ;
IIF(Empty(This.UpdatableFieldList),'',',') + ;
FIELD(m.ix,This.Alias,0)
Endif
This.UpdateNameList = This.UpdateNameList + ;
IIF(Empty(This.UpdateNameList),'',',') + ;
TEXTMERGE('<<FIELD(m.ix,this.Alias,0)>> <<m.tcTableName>>.<<FIELD(m.ix,this.Alias,0)>>')
Endfor
Endproc
Procedure QueryFill()
Local llSuccess
If This.DataSourceType ="ADO"
llSuccess = This.CursorFill(.F.,.F.,0,This.oCommand)
Else
llSuccess = This.CursorFill(.F.)
Endif
If !m.llSuccess
If This.DataSourceType ="ADO"
lcMessage = This.oCommand.CommandText + Chr(13) + This.GetErrorExplanation()
Else
lcMessage = This.GetErrorExplanation()
Endif
Messagebox(m.lcMessage)
Endif
Return m.llSuccess
Endproc
Procedure GetErrorExplanation
Local lcError,ix
Local Array aWhy[1]
Aerror(aWhy)
lcError = ""
For ix = 1 To 7
lcError = m.lcError + Transform(aWhy[m.ix]) + Chr(13)
Endfor
Return m.lcError
Endproc
Enddefine