Ado.net/sql/odbc

In the past I've always use ADODB in vb.net for accessing data. I use PervasiveSQL which supports adCmdTableDirect so it allows me to move up and down tables, edit, delete create new records etc.

I thought it was about time I looked at taking the SQL approach. What I can't get my head around is which method to use, you've go system.data.oledb, system.data.odbc and system.data.sqlclient. On top of that there's seems to be no recordset to retrieve a SQL query into, so is it possible to have a local recordset to work on and make changes or do you just issue SQL UPDATE commands?

If somebody could shed some light on the best method I would appreciate it and any decent links to exampes.

Thanks

Hi,
Are you still thinking of connecting to your Pervasive DB? If so does it have
an ODBC driver for .NET.

It does have its own .Net adaptor but I would prefer not to use so in theory I could have any database at the back end.

Hi,
This is an INSERT in Oledb Using Parameters

VB Code:

Dim sqlSaveCust As String = ("INSERT INTO [Customer] (CustCode, SuppCode, CustName, CustAddr1, CustAddr2, CustAddr3, CustAddr4, CustContact, CustPhone, CustFax, CustMobile, CustSupp) " _
                                              & "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
        Dim CMDSaveCust As New OleDb.OleDbCommand(sqlSaveCust, TrenConn)
        CMDSaveCust.CommandType = CommandType.Text
 
        CMDSaveCust.Parameters.Add("@CustCode", OleDb.OleDbType.VarChar, 15).Value = Me.TxtCustCode.Text
        CMDSaveCust.Parameters.Add("@SuppCode", OleDb.OleDbType.VarChar, 15).Value = Me.TxtSuppCode.Text
        CMDSaveCust.Parameters.Add("@CustName", OleDb.OleDbType.VarChar, 50).Value = Me.TxtCustName.Text
        CMDSaveCust.Parameters.Add("@CustAddr1", OleDb.OleDbType.VarChar, 30).Value = Me.TxtCustAdd1.Text
        CMDSaveCust.Parameters.Add("@CustAddr2", OleDb.OleDbType.VarChar, 30).Value = Me.TxtCustAdd2.Text
        CMDSaveCust.Parameters.Add("@CustAddr3", OleDb.OleDbType.VarChar, 30).Value = Me.TxtCustAdd3.Text
        CMDSaveCust.Parameters.Add("@CustAddr4", OleDb.OleDbType.VarChar, 30).Value = Me.TxtCustAdd4.Text
        CMDSaveCust.Parameters.Add("@CustCont", OleDb.OleDbType.VarChar, 30).Value = Me.TxtCustCont.Text
        CMDSaveCust.Parameters.Add("@CustPhone", OleDb.OleDbType.VarChar, 20).Value = Me.TxtCustPhone.Text
        CMDSaveCust.Parameters.Add("@CustFax", OleDb.OleDbType.VarChar, 20).Value = Me.TxtCustFax.Text
        CMDSaveCust.Parameters.Add("@CustMob", OleDb.OleDbType.VarChar, 20).Value = Me.TxtCustMob.Text
        CMDSaveCust.Parameters.Add("@CustSupp", OleDb.OleDbType.Boolean).Value = Me.CB1CustSupp.SelectedIndex
 
        Conn_Open()
        CMDSaveCust.ExecuteNonQuery()
        Conn_Close()

It all depends on your backend DB and the driver it supplies for .NET also if your DB supports Ansi SQL.

Thanks for that. What about queries, in your example you have the customer table but behind that you could have a table for unpaid invoices. How do you query that into a recordset, work on it and push it back to the database. From what I can see there's no recordsets in ADO.NET.

Hi,
No you don't have recordsets but you do have Datasets and DataRows these are what you work with.

Ok I'll look into them. Do they write back the the DB or is it a case of running a SQL command.

Thanks for the advice.

Hi,
You can do it either way, but in .NET you disconnect, work with Data and then re-connect to save changes.

Is there not a overhead in reconnecting all the time? or do you keep the connection open just the query you close.