Ado vs Ado.net

[Rick Lipkin]

To those people that use ADO ..

I am not a dot net kinda programmer .. however, I have noticed the Microsoft trend away from ADO ( sqloledb ) to the .Net Framework Data provider SQLNCLI.

The main difference in the two providers is Microsoft is pushing it's .net ( visual studio 2005-08 ) in that direction as well as the difference in RecordSets ( ado ) and DataSets ( ado.net ).

In doing some research .. the Dataset technology is more of a 'de-tached' version of the recordset .. in that you make the connection .. get your data in a cursor and then the Dataset is detatched ..

http://www.dotnetspider.com/forum/ViewF ... umId=33473

I am still using ADO and it works quite nicely in Windows 7 .. so I am not so concerned with it not being supported in the near future .. however, I am concerned with my 'distractors' that say ADO is an 'old' technology and should be not be used.

Like I said .. i am not a fan of the .net Framework .. when you have 5k desktops running an application .. I certainly don't want to have a version distribution problem just to run my application .. and I certainly do not want to run my application from a Citrix Farm and have to deal with all the permissions and nonsense with publishing my applications ..

Also .. the standard ADO class and methods to not seem to apply to ado.net .. If anyone has any comments or suggestions .. I would like to hear your voice.

Rick Lipkin
SC Dept of Health, USA

[nageswaragunupudi]

>
In doing some research .. the Dataset technology is more of a 'de-tached' version of the recordset .. in that you make the connection .. get your data in a cursor and then the Dataset is detatched ..
>
In ADO, we can detach, work and reconnect. In ADO.NET, that is the only way. ( Incidentally, that also means no serverside cursors. Can not browse large tables.)

Microsoft will not 'support' ADO. But I think ADO will not stop working. Thats what I hope.

For Oracle and MySql ADO is not the only way. We can also use direct APIs. But for MSSQL, we are totally dependent on Microsoft's technologies.

We can not work with ADO.NET with (x)Harbour. Atleast thats what I think.

[Rick Lipkin]

Rao

I have not tested the ado.net data provider .. the connection strings look like this :

Standard ADO ( currently using )

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

ADO.net ( from connectionstrings.com )

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

I have been too busy this morning to do a quick test to see if I get a connection .. However, if I do .. I would have no idea ( nor have I found on MSDN ) the ado.net methods for managing the data.

YES .. I do realize you can de-tatch from ADO .. interesting reading if you Google the phrase :
"Differences in ADO and ADO.NET"

Rick Lipkin

[Rick Lipkin]

For those following this thread .. I was able to connect to a MS Sql 2005 server and compiled this small test using the .net connection string :

Code: Select all  Expand view

FUNC _LOGIN( cLOGIN )

LOCAL SAYING,cDEFA
LOCAL oRs, oErr, cSQL

LOCAL xPROVIDER := "SQLNCLI"
LOCAL xSOURCE   := "MSSQL01"
LOCAL xCATALOG  := "PCAS"
LOCAL xUSERID   := "pcasuser"
LOCAL xPASSWORD := "pcas"

#INCLUDE "FIVEWIN.CH"
#include "xbrowse.ch"

cDEFA := SET(7)

oRs  := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

cSQL := "SELECT * FROM employee"

// syntax for .net framework connection
TRY

// syntax for .net framework connection ADO.net
  oRS:Open(cSQL,'Provider='+xPROVIDER+';Server='+xSOURCE+';Database='+xCATALOG+';Uid='+xUSERID+';Pwd='+xPASSWORD )

// syntax for sqloledb or plain ADO
// xPROVIDER := "SQLOLEDB"
* oRS:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
   MsgInfo( "Error in Opening EMPLOYEE here table" )
   RETURN(.F.)
END TRY

Msginfo( "connected" )
Xbrowse( ors )

oRs:Close()

return(.t.)
 

I am running this test on Windows 7 which has 3.5 version of the Framework.

Just thought I would share this .. All I am using from the .net is the connection .. and not any of the GUI .. passing the standard Dataset object to xbrowse.

If anyone can confirm this on their own MS Sql box using their own databases and tables .. I would be interested if the same results apply.

Thanks
Rick Lipkin

[nageswaragunupudi]

>
I was able to connect to a MS Sql 2005 server and compiled this small test using the .net connection string :
>
Very Good experiment. You have successfully started this. Slowly we may all start experimenting with ado.net connections using the new datasets.

[Rick Lipkin]

Rao

I am still dubious that the .net framework was actually engaged .. I did get the syntax from the .net section from ConnectionStrings.com ..

SQLNCLI is labeled as SQL native client under the .net link .. and I do have Sql Studio loaded on my box so I am not sure I am creating a Dataset or a RecordSet using the native SQL client ..

Was hoping that someone could verify my code .. Still using the TOleAuto():New( "ADODB.Recordset" )
syntax .. and I don't know how significant that actually is .. however the SQLNCLI connection string does seem to work and I get all the rows I expect.

Rick Lipkin

[fafi]

Hi ! Rick,

Please test my code :

Tested SQL Server 2005, you have to create ODBC first

I'm sorry Sir . . I don't know about .net
this source code.. tested for Local Server with Local Areal Network

Code: Select all  Expand view

#INCLUDE "FIVEWIN.CH"

// Cursor Type
#define adOpenForwardOnly     0
#define adOpenKeyset          1
#define adOpenDynamic         2
#define adOpenStatic          3

// Lock Types
#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4

// Field Types
#define adEmpty               0
#define adTinyInt            16
#define adSmallInt            2
#define adInteger             3
#define adBigInt             20
#define adUnsignedTinyInt    17
#define adUnsignedSmallInt   18
#define adUnsignedInt        19
#define adUnsignedBigInt     21
#define adSingle              4
#define adDouble              5
#define adCurrency            6
#define adDecimal            14
#define adNumeric           131
#define adBoolean            11
#define adError              10
#define adUserDefined       132
#define adVariant            12
#define adIDispatch           9
#define adIUnknown           13
#define adGUID               72
#define adDate                7
#define adDBDate            133
#define adDBTime            134
#define adDBTimeStamp       135
#define adBSTR                8
#define adChar              129
#define adVarChar           200
#define adLongVarChar       201
#define adWChar             130
#define adVarWChar          202
#define adLongVarWChar      203
#define adBinary            128
#define adVarBinary         204
#define adLongVarBinary     205
#define adChapter           136
#define adFileTime           64
#define adPropVariant       138
#define adVarNumeric        139
#define adArray               // &H2000

#define adRecDeleted          4

#define adUseNone             1
#define adUseServer           2
#define adUseClient           3
#define adUseClientBatch      3

#define adKeyForeign          2


#include "sqlrdd.ch"
#include "sqlodbc.ch"

REQUEST SQLRDD             // SQLRDD should be linked in
REQUEST SR_ODBC            // Needed if you plan to connect with ODBC


//static nRecord,oRecord,oTotal,nTotal

static oRecordSet,oConnection,oBrow


FUNCTION MAIN()
local oConnection := TOleAuto():New( "ADODB.Connection" )

TRY
     
   
   oConnection:Open( "Provider=SQLOLEDB;" + ;
                     "server=SERVER"      + ;
                     ";database=basepro"  + ;
                     ";uid=sa" + ;
                     ";pwd=8091016" )  
 

   CATCH oErr                    
      MsgAlert( "Error to konek basepro" )
      return nil
   END TRY
   
   
   oRecordSet := TOleAuto():New( "ADODB.Recordset" )
   oRecordSet:CursorType     := adOpenDynamic    
   oRecordSet:CursorLocation := adUseClient
   oRecordSet:LockType       := adLockOptimistic  
   
   TRY
      oRecordSet:Open( "SELECT * FROM EMPLOYEE", oConnection )  
   CATCH oErr
      MsgAlert( "Error to Open Employee" )
     
      return nil
   END TRY
   
   
   if oRecordSet:BOF() .or. oRecordSet:Eof()
      MsgAlert( "Record Empty, create one" )
      oRecordSet:AddNew()
      oRecordSet:Fields("id_code" ):Value := "001"
      oRecordSet:Fields("id_name" ):Value := "FAFI"
      oRecordSet:Fields("id_date" ):Value := date()
      oRecordSet:Fields("id_age" ):Value := 10
      oRecordSet:Fields("id_memo" ):Value := "xharbour with fivewin"
      oRecordSet:Update()
   endif  
   
   define dialog oDlg from 1,1 to 400,700 pixel
   
   @ 0, 0 LISTBOX oBrow FIELDS ;
       oRecordset:Fields("id_code" ):Value,;
       oRecordset:Fields("id_name" ):Value, ;
       dtoc(oRecordset:Fields("id_date" ):Value), ;
       str(oRecordset:Fields("id_age" ):Value,3), ;
       oRecordset:Fields("id_memo" ):Value ;
       SIZES 80,200,80,60,200 ;
       HEADERS "Code","Name","Date","Age","Info" SIZE 300,200 of oDlg
       
       oBrow:bLogicLen := { || oRecordset:RecordCount }
       oBrow:bGoTop    := { || oRecordset:MoveFirst() }
       oBrow:bGoBottom := { || oRecordset:MoveLast() }
       oBrow:bSkip     := { | nSkip | Skipper( oRecordset, nSkip ) }
       oBrow:cAlias    := "ARRAY1"
   
    @5,310 button "Add" size 30,12 of oDlg pixel action RecordAction(.t.)
   
    @25,310 button "Edit" size 30,12 of oDlg pixel action RecordAction(.f.)
   
    @40,310 button "Delete" size 30,12 of oDlg pixel action RecordDelete()
   
   
    ACTIVATE DIALOG oDlg centered
   
    oRecordset:Close()

RETURN NIL  


STATIC FUNCTION SKIPPER( oRsx, nSkip )
LOCAL nRec := oRsx:AbsolutePosition

//nRecord := oRsx:AbsolutePosition
//oRecord:Refresh()

//nTotal := oRsx:RecordCount()
//oTotal:Refresh()

oRsx:Move( nSkip )
IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF

RETURN( oRsx:AbsolutePosition - nRec )

static function RecordAction(lAdd)
   
   local lSave := .f.
   
   if lAdd
      cId_code := spac(3)
      cId_name := spac(10)
      cId_date := date()
      cId_age  := 10
      cId_memo := spac(100)
   else
     
      cId_code := oRecordSet:Fields("id_code" ):Value
      cId_name := oRecordSet:Fields("id_name" ):Value
      cId_date := oRecordSet:Fields("id_date" ):Value
      cId_age  := oRecordSet:Fields("id_age" ):Value
      cIde_meno:= oRecordSet:Fields("id_memo" ):Value
     
   endif  
   define dialog  oDlgRecord from 1,1 to 200,200 pixel title if(lAdd,"Add Record","Edit Record")
   
   @1,1 say "Code" size 50,12 of oDlgRecord pixel
   @15,1 say "Name" size 50,12 of oDlgRecord pixel
   
   @1,30 get cId_Code  size 20,12 of oDlgRecord pixel
   @15,30 get cId_Name size 50,12 of oDlgRecord pixel
   @30,20 button "Save" size 30,12 of oDlgRecord pixel action ( lSave := .t.,oDlgRecord:End() )
   @30,60 button "Cancel" size 30,12 of oDlgRecord pixel action ( lSave := .f.,oDlgRecord:End() )
   
   
   activate dialog oDlgRecord centered
   
   if empty(cId_Code) .or. empty(cId_Name)
      lSave := .f.
   endif  
   
   if lSave
      if lAdd
        oRecordSet:AddNew()
      endif  
      oRecordSet:Fields("id_code" ):Value := cId_code
      oRecordSet:Fields("id_name" ):Value := cId_name
      oRecordSet:Fields("id_date" ):Value := date()
      oRecordSet:Fields("id_age" ):Value := 0
      oRecordSet:Fields("id_memo" ):Value := ""
      oRecordSet:Update()
     
      if lAdd
         oBrow:goBottom()
      else
         oBrow:Refresh()
      endif  
         
     
   endif  
   
return nil  

static function RecordDelete()
   
 if MsgYesNo("Delete ?")  
    nRec  := oRecordSet:AbsolutePosition()
    nLast := oRecordSet:RecordCount()
    oRecordSet:delete()
   
    if nRec == nLast
       oRecordSet:MovePrevious()
    else  
       oRecordSet:MoveNext()
    endif  
   
    oBrow:Refresh()
endif    
   
 return nil

 

Please reply.. if you get errors

Regards
Fafi

[Rick Lipkin]

Fafi

Code: Select all  Expand view

#include "sqlrdd.ch"
#include "sqlodbc.ch"

REQUEST SQLRDD             // SQLRDD should be linked in
REQUEST SR_ODBC            // Needed if you plan to connect with ODBC
 

Why do you need the above code ?? .. ADO should be able to connect to any OLE data source without ODBC .. is not SQLRDD a xHb commercial library ??

Rick

[fafi]

Rick

Why do you need the above code ?? .. ADO should be able to connect to any OLE data source without ODBC .. is not SQLRDD a xHb commercial library ??

Yes ! xHB comm lib

Please don't use ..

Just remark that line..

Recompiled with :
free xHarbour and BCC55.. without error .. run on XP 2




Regards
Fafi

[Rick Lipkin]

To All

It seems I may have bad news for using the .Net connectivity framework .. This link seems to be correct for the .net connection string :

http://connectionstrings.com/sql-server-2005

If anyone has any suggestions or comments .. let me know

Rick Lipkin

Code: Select all  Expand view

FUNC _LOGIN( cLOGIN )

LOCAL SAYING,cDEFA
LOCAL oRs, oErr, cSQL, oCn

LOCAL xSOURCE   := "MSSQL01"
LOCAL xCATALOG  := "PCAS"
LOCAL xUSERID   := "pcasuser"
LOCAL xPASSWORD := "pcas"


#INCLUDE "FIVEWIN.CH"
#include "xbrowse.ch"

cDEFA := SET(7)

oCn := CREATEOBJECT( "ADODB.Connection" )
cSQL := "SELECT * FROM employee"

TRY
   oCn:Open( 'Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oERR
   SAYING := "Error in Opening connection"
   MsgInfo( saying )
   RETURN(.F.)
END TRY

Msginfo( "connected" )

oCn:Execute(cSql)

Msginfo( "after execute" )

*Xbrowse( oCn )

oCn:Close()

return(.f.)
 

[kajot]

when I link this samples I get error


Type: C >>>xlink.exe -NOEXPOBJ -MAP -FORCE:MULTIPLE -NOIMPLIB -subsystem:windows -UNMANGLE -LIBPATH:"D:\xHB\lib" -LIBPATH:"D:\xHB\c_lib" -LIBPATH:"D:\xHB\c_lib\win" -LIBPATH:"D:\fwh\lib" "t.obj" "FiveHCM.lib" "FiveHMX.lib" "OptG.lib" "sql.lib" "ace32.lib" "xhb.lib" "dbf.lib" "ntx.lib" "cdx.lib" "rmdbfcdx.lib" "ct3.lib" crt.lib kernel32.lib user32.lib winspool.lib ole32.lib oleaut32.lib odbc32.lib odbccp32.lib uuid.lib wsock32.lib ws2_32.lib wininet.lib advapi32.lib shlwapi.lib msimg32.lib mpr.lib comctl32.lib comdlg32.lib gdi32.lib shell32.lib winmm.lib lz32.lib Netapi32.lib -out:"t.exe"<<<

xLINK: error: Unresolved external symbol '??2@YAPAXI@Z'.

xLINK: error: Unresolved external symbol '??3@YAXPAX@Z'.

xLINK: fatal error: 2 unresolved external(s).


Type: C >>>Couldn't build: t.exe<<<
Type: C >>>TMAKEPROJECT<<<
Type: C >>>TMAKEPROJECT:REFRESH<<<
Type: N >>> 1360<<<