Ado.net/sql/odbc

[Foxy69]

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

[Fishy]

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

[Foxy69]

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.

[Fishy]

Hi,
This is an INSERT in Oledb Using Parameters

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.

[Foxy69]

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.

[Fishy]

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

[Foxy69]

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.

[Fishy]

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

[Foxy69]

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