Konu: SPT - SQL Pass Through ile SQL Servera baglanti class i
asagida kodlarini yayinladigim class kodlari ile SQL Pass Through yöntemi ile SQL servera baglanma yöntemi için başlangic olacak ornekleme yaptim bu tip bir class ile ister sorgu formlari ister data isleme formlarinizda ornekledigimin daha fazlasini yapabilirsiniz.
Umarım faydalı olur
* class icin kaynak olarak EPS software kodlarindan faydalandim ve kendime göre cesitli modifiyeler yaptim. en pratik olani updatelist ler icin işi afields() ile vfp ye birakiyor olmak :) ve tüm olayi propertyler sayesinde halletmek. Bu sayede bircok kodlama angaryasindan kurtulabiliyorsunuz ancak
deneyimsiz kullanicilar icin daha öncede bahsi gecmisti RV (remote view) yada CA (cursoradapter) daha kolay gelebilir . Hoş bende SQL e yavaş yavaş ısınyorum ama CA ve SPT arasında gidip gelecegim gibi gözüküyor :)
Visual Fox Pro
&& Class: stb_spt_sql_connector (d:\soykan\mysoftware\emin_elk\lib\soykan.vcx)
&& ParentClass: custom
&& BaseClass: custom
&& Time Stamp: 11/08/06 02:04:08 AM
&&
Define Class stb_spt_sql_connector As Custom
&&server a baglanti
nhandle = 0
&&baglanilacak server adi
cservername = []
&&baglanilacak database adi
cdatabasename = []
&&baglanilacak tableadi
ctablename = []
&&primary key column adi
ckeyfield = []
&login ekrani gozuksunmu
nhold_displogin = 0
&& baglanti asim suresi
nhold_connecttimeout = 0
&& isletilecek kod
cexecstring = []
&& olusacak cursor adi
ccursor = []
&& SQL baglanti cumlesi
cconnectstring = []
&& .t. ise her sql baglantidan sonra baglantiyi kesme
lpersistconnection = .T.
&& isletim sistemi 2000,xp ise trusted_conn secin
ltrustedconnection = .T.
&& baglantida updatable cursor kullanilacakmi
isupdatable = .f.
&& kullanici adi
cuid = []
&& kullanici sifresi
cpassword = []
Name = "stb_spt_sql_connector"
&& execute method
Procedure execute
Local lnError
lnError = 0
&& sql cumlesi yoksa birsey yapma
If Empty(This.cexecstring)
Return .F.
Endif
With This
lnError = SQLExec(.nhandle, .cexecstring, .ccursor)
If lnError < 0 && hata yakalama kismi
.displayerror()
_Cliptext = .cexecstring
Endif
Assert lnError > 0 Message "Error executing SQL command."
If Not .lpersistconnection
.Disconnect()
Endif
Endwith
Return Type("lnError")="N" And lnError > 0
Endproc
&& connect method
Procedure Connect
Local lnResult
&& gizli olursa daha iyi olur (protected)
With This
If .ltrustedconnection
.cconnectstring = "DRIVER=SQL Server" + ;
";SERVER=" + .cservername + ;
";DATABASE=" + .cdatabasename + ;
";Trusted_Connection=Yes"
Else
.cconnectstring = "DRIVER=SQL Server" + ;
";SERVER=" + .cservername + ;
";DATABASE=" + .cdatabasename + ;
";UID=" + .cuid + ;
";PWD=" + .cpassword
Endif
This.nhandle = Sqlstringconnect(.cconnectstring)
Endwith
Endproc
&& disconnect method
Procedure Disconnect
&& gizli olursa daha iyi olur (protected)
Local lcOnError
&& hata ayiklayiciyi kapatiyoruz
lcOnError = On("ERROR")
On Error *
With This
If .nhandle > 0
SQLDisconnect(.nhandle)
Endif
.nhandle = 0
Endwith
On Error &lcOnError
Endproc
&& hata gosterimi
Procedure displayerror
Local laError[1]
=Aerror(laError)
&& hata mesajlarini alt class kodu ile gosterebilirsiniz
_Screen.Newobject('templbl1','stb_lbltemplbl')
_Screen.templbl1.Top = 1
_Screen.templbl1.Caption = laError[1,2]
_Screen.templbl1.Visible = .T.
_Screen.Newobject('templbl2','stb_lbltemplbl')
_Screen.templbl2.Top = _Screen.templbl1.Top + _Screen.templbl1.Height + 5
_Screen.templbl2.Caption = "SQL Error No. " + Alltrim(Str(laError[1,5]))
_Screen.templbl2.Visible = .T.
Endproc
&& baglanti testi
Procedure test_connection
Local lcOnError, llError
lcOnError = On("ERROR")
On Error llSuccess = .F.
This.cexecstring = "select 1"
llSuccess = This.execute()
On Error &lcOnError
Return llSuccess
Endproc
&& primary key haric diger alanlari updatable yapma proseduru
procedure make_updatable_cursor
Local ;
laFields[1],;
lcKeyField,;
lcUpdateNameList,;
lcUpdatableFieldList,;
lcString, ;
lcTableName, ;
ln1, ;
lnConn,;
lnResult
lnConn = This.nhandle
lcTableName = This.ctablename
lcKeyField = This.ckeyfield
lcString = This.cexecstring
lnResult = SQLExec(lnConn, lcString, lcTableName)
Store [] To lcUpdateNameList, lcUpdatableFieldList
lnResult = CursorSetProp("Tables","dbo." + lcTableName, lcTableName)
lnResult =CursorSetProp("KeyFieldList", lcKeyField, lcTableName)
&& update list olusturmak icin Afields kullaniyoruz
=Afields(laFields)
For ln1 = 1 To Alen(laFields,1)
&& PK haric
If laFields[ln1,1] == Upper(lcKeyField)
Else
lcUpdatableFieldList = lcUpdatableFieldList + ;
IIF(Empty(lcUpdatableFieldList),'',',') + laFields[ln1,1]
Endif
lcUpdateNameList = lcUpdateNameList + ;
IIF(Empty(lcUpdateNameList),'',',') + laFields[ln1,1] + ;
" dbo." + lcTableName + "." + laFields[ln1,1]
Next ln1
&& ve son ayarlar
lnResult = CursorSetProp("UpdatableFieldList", lcUpdatableFieldList)
lnResult = CursorSetProp("UpdateNameList", lcUpdateNameList)
lnResult = CursorSetProp("SendUpdates", .T.)
endproc
Procedure Destroy
With This
If .nhandle > 0
.Disconnect()
Endif
If Not Empty( .nhold_displogin )
SQLSetprop(0, 'Displogin', .nhold_displogin)
Endif
If Not Empty( .nhold_connecttimeout )
SQLSetprop(0, 'ConnectTimeOut', .nhold_connecttimeout)
Endif
Endwith
Endproc
Procedure Init
#INCLUDE "foxpro.h"
This.nhold_displogin = SQLGetprop(0, 'Displogin')
SQLSetprop(0, 'Displogin',DB_PROMPTNEVER)
This.nhold_connecttimeout = SQLGetprop(0, 'ConnectTimeOut')
SQLSetprop(0, 'ConnectTimeOut', 3)
If Empty(This.ccursor)
This.ccursor = "T" + Sys(2015)
Endif
Endproc
Enddefine
&&
&& EndDefine: stb_spt_sql_connector
Visual Fox Pro
&&-- Class: stb_lbltemplbl (d:\soykan\mysoftware\emin_elk\lib\soykan.vcx)
&&-- ParentClass: label
&&-- BaseClass: label
&&-- Time Stamp: 11/08/06 01:00:11 AM
&&
DEFINE CLASS stb_lbltemplbl AS label
FontBold = .T.
FontName = "Tahoma"
FontSize = 18
WordWrap = .T.
Caption = "Label1"
Height = 200
Width = 500
ForeColor = RGB(128,0,0)
Name = "stb_lbltemplbl"
ENDDEFINE
&&
&&-- EndDefine: stb_lbltemplbl
&&bir formunuz olsun ve üzerinde bir grid var adi Grid1, classinizi forma suruklediniz biraktiniz
&& form init kodunuz asagidaki gibi olursa class calisacaktir
With This.stb_spt_sql_connector1
.cServername=[(local)] &&serverAdi
.cDatabasename=[Northwind] &&databaseAdi
.cCursor=[Customers] &&cursor adi tablo adi ile ayni verildi
.cUid=[] &&sql server kullanici adi
.cPassword=[] &&sql server sifresi
.cTablename=[Customers] && tabloadi
.cKeyfield=[CustomerID] &&primary key adi
.Connect() &&servera baglaniliyor
&&.cExecstring = [select * from Northwind.dbo.Customers] &&SQL cumlesi
.cExecstring = [select * from ] + .cDatabaseName + [.dbo.]+ .cTableName &&SQL cumlesi
.execute() && ustteki SQL cumlesi calistiriliyor
.isupdatable = .T. && olusan cursor update edilebilir isteniyorsa
If .isupdatable = .T.
.make_updatable_cursor()
CursorSetProp("Buffering",5,.cTablename)
Endif
&& sonuclar gridte gosteriliyor
With This.grid1
.RecordSource = []
.RecordSource = This.stb_spt_sql_connector1.cCursor
.Refresh
Endwith
Endwith
&& form kaydet buton kodu
If Not Tableupdate(2,.T.,Thisform.grid1.RecordSource)
Messagebox([Kaydedilemedi],16,[kaydetme Hatasi])
Else
Wait Window [Kaydedildi...] Nowait
thisform.grid1.Refresh
Endif
