ADOX vs OleDB for DDL of new Procedure objects

[smitty_one_each]

A couple questions on ADO.Net.
a) Is the functionality of the ADOX.Catalog.Create() method available
in System.Data.OldDB anywhere?
b) When creating a database using DDL against Jet 4.0, I encounter a
problem adding Procedures to the .mdb file, after successfully creating
a bunch of tables via the Command.Execute() method.
For valid command and catalog objects, the C# code will error after
on the second Append() call, irrespective of which order I place these
(i.e., the SQL itself is valid):


cmd.CommandText = "PARAMETERS doc_name TEXT(255); "
+ "INSERT INTO tbl_doc (doc_name) "
+ "VALUES ([doc_name]); ";
cat.Procedures.Append( "qapp_doc", cmd );
cmd.CommandText = "PARAMETERS word_text TEXT(255); "
+ "INSERT INTO tbl_word (word_text) "
+ "VALUES ([word_text]); ";
cat.Procedures.Append( "qapp_word", cmd );

The error is a System.Runtime.InteropServices.COMException , "DBID is
invalid".
I am using Microsoft Visual C# .NET 69586-335-0000007-18478, against
a Jet4.0 (ADO 2.8) installation.
I also tried variations such as s/VALUES ([doc_name])/SELECT doc_name
AS Expr1/ which the Query design grid of MS Access would generate, with
a depressing lack of affection from the system.
I also have *not* tried explicitely Appending the Parameter objects to
my cmd variable prior to Append()ing to the Procedures collection. As
I'm only stashing the procedure objects at the moment, not actually
executing them, this seemed unnecessary effort. Post-mortem inspection
of the .mdb (via MS Access) indicates that the first procedure is, in
fact, properly created.
Possibly I may need to punt on the ADODB, and instead re-connect with
OleDB to support building this file.
Any insight deeply appreciated.
Thanks,
Chris

 

[Cor Ligthert]

Smitty,

The ADOX.Catolog.Create is not in ADONET. Therefore I use only that part to
create the mdb. For the rest I use ADONET.

See this sample.

set a reference to COM adox ext 2.x for dll and security
\\\
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\db1.mdb")
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source=C:\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Close()
End Sub
End Class
////

I hope this helps a little bit?

Cor

 

[smitty_one_each]

Thanks, Cor. From an external vantage, ADOX looks built atop OleDB
anyway (except for things like Create() ) and the OleDB looks like the
better horse to ride.
I was mainly doing a sanity check.
Party,
Chris

 

[Kevin Yu [MSFT]]

Thanks for Cor's quick response.

Hi Chris,

I agree with Cor, that in .NET world, it's better to take advantage of
ADO.NET instead of interoping with ADOX. Although ADO.NET has it's
limitation that we have to use ADOX to create the .mdb file, we can do the
other operations with Jet SQL statements and execute them with
OleDbCommand. It is more reliable and produces less interop errors.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[J L]

Hi Kevin,
Pardon me for jumping in here but your response raised a question I
have asked before but never got an answer on...

In VB6 I used DAO an always created my MDB files on the fly for a
"virgin" installatioin. Now with ADO.Net it appears that is not
possible. I do not want to use interop, I want to stay as pure .Net as
possible (hope that comment makes sense). So what do most of the
developers on here do? Do you create an empty MDB with Access and
distribute it with the program? I think that is the only alternative.
Also any users of Firebird or mySql that can answer similar question
for those databases?

TIA,
John

 

[Mark Rae]

Do you create an empty MDB with Access and distribute it with the program?

Yes. If the app needs only one MDB, then I include it with the installation
package. Otherwise, I add a blank MDB to the project as an embedded
resource, and extract it (and rename it etc) as and when required.

 

[Kevin Yu [MSFT]]

Hi John,

I agree with Mark. In pure .net solution, we are unable to create a .mdb
file. The only solution is to distribute an empty .mdb file with the setup
package.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[smitty_one_each]

I'm just short of understanding
http://msdn.microsoft.com/library/d...ry/en-us/oledb/htm/oledbprocedures_rowset.asp

where are the possible values for the PROCEDURE_TYPE argument held?
Apparently, it's a three-element enumeration, but the location of those
symbols is no' so clear, and I've browsed most of System.Data and
System.Data.OleDB.

IRT Cor's earlier post, CREATE TABLE works fine. Creating a Procedure
object with Parameter arguments is possibly more interesting than it
could be.

Thanks,
Chris

 

[smitty_one_each]

Kevin,
Here is some code that throws no runtime errors, but doesn't perform as
expected, either:

//---------------------------
using System;
using System.Data;
using System.Data.OleDb;


OleDbConnection con = new OleDbConnection(

"Provider='Microsoft.Jet.OLEDB.4.0';Data Source='"
+ file_path
+ "'");
con.Open();
DataTable procs = con.GetOleDbSchemaTable(
OleDbSchemaGuid.Procedures
, new object[] {} );
procs.LoadDataRow( new object[] { Type.Missing
, Type.Missing
, "qapp_doc_name0"
, 0 //blind
guess
, "PARAMETERS doc_name
TEXT(255); "
+ "INSERT INTO tbl_doc
(doc_name) "
+ "VALUES ([doc_name]); "
, "Query to append to
tbl_doc"
, System.DateTime.Now
, System.DateTime.Now }
, true );
procs.AcceptChanges();


//---------------------------
Eek!
R,
C

 

[smitty_one_each]

/// <summary>
/// Since the OleDB no worky-worky for me, we will explicitely
/// create a new catalog object and append a parameter query to
/// it once for each of the query objects in the schema.
/// </summary>
/// <param name="con">Open ADODB.Connection</param>
/// <param name="name">What to name the query</param>
/// <param name="sql">Valid SQL string</param>
private void register_paramter_query( ADODB.Connection con
, String name
, String sql )
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.ActiveConnection = con;
ADODB.CommandClass com = new ADODB.CommandClass();
com.CommandText = sql;
cat.Procedures.Append( name
, com );
}

 

[Kevin Yu [MSFT]]

Hi,

Your .NET code doesn't work because GetOleDbSchemaTable returns a table
which contains information of the current database. Modifying that table
will not update the database.

As Cor suggested, if you need to create a procedure in the mdb database, we
have to use CREATE PROCEDURE. You can use the same code as Cor has
provided. Just change the CREATE TABLE statement to CREATE PROCEDURE ......

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[smitty_one_each]

CREATE PROCEDURE
Saw I not; the cranial
Rectalitis strikes.