SQL Server'e toplu kayıt

** 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