Enterprise Library and Access databases

microsoft.public.dotnet.framework.adonet

4 Mar 2007 10:35 AM

nostradumbass77

Using Enterprise Library 2.0 (Jan 06) and .NET 2.0 (VB.Net 2005)

Dim dbPF As Data.Common.DbProviderFactory
        Try
            dbPF =
Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb")

            db = New
Microsoft.Practices.EnterpriseLibrary.Data.GenericDatabase( _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=NotesManagerServer.mdb", dbPF)

            db.CreateConnection()

        Catch ex As Exception
            MsgBox("Could not connect to system database. Application
terminating.")
            End

I have a Select query in the Access database, named GetFileList and
here is the SQL copied from Access:

SELECT FileList.SrNo, FileList.Filename, FileList.CRC32,
FileList.Timestamp
FROM FileList;

This code works just FINE:

        Try
            Dim rs As Data.DataSet
            rs =
db.ExecuteDataSet(System.Data.CommandType.StoredProcedure,
"GetFileList")
            DataGridView1.DataSource = rs
            DataGridView1.DataMember = rs.Tables(0).ToString

        Catch ex As Exception
            MsgBox("Exception occured" + ex.Message)

        End Try

I have a Update query in the Access database, is named InsertFile and
here is the SQL copied from Access:

PARAMETERS Filename Text ( 255 ), CRC32 Long, [Timestamp] Long;
INSERT INTO FileList ( Filename, CRC32, [Timestamp] )
VALUES (@Filename, @CRC32, @Timestamp);

To run this query in VB.Net, I have this code that WORKS:

        Dim p(2) As Data.Common.DbParameter

            p(0) = New Data.OleDb.OleDbParameter
            p(1) = New Data.OleDb.OleDbParameter
            p(2) = New Data.OleDb.OleDbParameter

                p(0).Value = "ASDF"
                p(1).Value = 11
                p(2).Value = 22

                Dim cmd As System.Data.Common.DbCommand
                cmd = db.GetStoredProcCommand("InsertFile")
                cmd.Parameters.Add(p(0))
                cmd.Parameters.Add(p(1))
                cmd.Parameters.Add(p(2))
                cmd.Connection = db.CreateConnection
                cmd.Connection.Open()

                cmd.ExecuteNonQuery()

My problem is that it looks so 'inelegant' and probably is. How can I
run the Access query using just the db object .. as in
db.ExecuteNonQuery ... I've tried various combinations and I get
various errors regarding parameter discovery ...

Anyone?

I just want to use the db object (GenericDatabase) to run the Insert
query.. with parameters...

Thanks in advance.
ND