Login Skip Navigation LinksWilsonORMapper > Forums Search
Demo Version Demo Version
Download and try for yourself a fully working demo version, including sample apps and documentation.  The only limitation is that the demo version only works inside the debugger.

PayPal Subscribe
Get It All for $50 USD:
WebPortal, ORMapper,
Source Code, All Updates
PayPal

User Login User Login
Log In
 
 
Reset Password

Wilson ORMapper Forums Wilson ORMapper Forums : Other Databases : CustomProvider Examples

Date Post
9/1/2004 6:37:59 PM Here are some examples of using the new CustomProvider -- note that some of these were tested by others and not by myself so don't take all of them to be gospel:

-- MySql
CustomProvider provider = new CustomProvider("ByteFX.MySqlClient",
"ByteFX.Data.MySqlClient.MySqlConnection", "ByteFX.Data.MySqlClient.MySqlDataAdapter");
provider.StartDelimiter = "`";
provider.EndDelimiter = "`";
provider.IdentityQuery = "SELECT LAST_INSERT_ID()";
provider.SelectPageQuery = "SELECT * LIMIT {0} OFFSET {1}";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- PostgreSql
CustomProvider provider = new CustomProvider("Npgsql",
"Npgsql.NpgsqlConnection", "Npgsql.NpgsqlDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.ParameterPrefix = ":";
provider.IdentityQuery = "SELECT currval('{1}_{0}_seq')";
provider.SelectPageQuery = ""SELECT * LIMIT {0} OFFSET {1}";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- Sqlite
CustomProvider provider = new CustomProvider("Finisar.SQLite",
"Finisar.SQLite.SQLiteConnection", "Finisar.SQLite.SQLiteDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.IdentityQuery = "SELECT last_insert_rowid()";
provider.SelectPageQuery = "SELECT * LIMIT {0} OFFSET {1}";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- Firebird
CustomProvider provider = new CustomProvider("FirebirdSql.Data.Firebird",
"FirebirdSql.Data.Firebird.FbConnection", "FirebirdSql.Data.Firebird.FbDataAdapter");
provider.StartDelimiter = "\""; // ANSI Defaults do not need Setting
provider.EndDelimiter = "\""; // ANSI Defaults do not need Setting
provider.IdentityQuery = "SELECT gen_id(gen_{1}_id, 0) FROM RDB$DATABASE";
provider.SelectPageQuery = "SELECT FIRST {0} SKIP {1} *";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- DB2
CustomProvider provider = new CustomProvider("IBM.Data.DB2",
"IBM.Data.DB2.DB2Connection", "IBM.Data.DB2.DB2DataAdapter");
provider.StartDelimiter = "`";
provider.EndDelimiter = "`";
provider.IdentityQuery = "VALUES IDENTITY_VAL_LOCAL()";
provider.SelectPageQuery = null; // Requires Recompiled Provider
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- VistaDb
CustomProvider provider = new CustomProvider("VistaDB.Provider",
"VistaDB.VistaDBConnection", "VistaDB.VistaDBDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.IdentityQuery = "SELECT LastIdentity({0}) FROM {1}";
provider.SelectPageQuery = "SELECT TOP {2}, {0} *";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- SqlCe
CustomProvider provider = new CustomProvider("System.Data.SqlServerCe",
"System.Data.SqlServerCe.SqlCeConnection", "System.Data.SqlServerCe.SqlCeDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.IdentityQuery = "SELECT @@IDENTITY";
provider.SelectPageQuery = null; // Requires Recompiled Provider
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

-- Sybase
CustomProvider provider = new CustomProvider("Mono.Data.SybaseClient",
"Mono.Data.SybaseClient.SybaseConnection", "Mono.Data.SybaseClient.SybaseDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.IdentityQuery = "SELECT @@IDENTITY";
provider.SelectPageQuery = null; // Requires Recompiled Provider
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);
9/15/2004 5:54:26 AM sybase function limit?
9/15/2004 9:09:46 AM To my limited knowledge, Sybase does not have a "simple" sql syntax for "paging", so you won't be able to use paged resultsets this easily. The solution is to modify the source code, which isn't hard, to have a special Sybase Command class -- very much like the MS Command stuff for MS Sql and Access. It might even be the same syntax as MS, since Sybase and MS Sql have a common history. Again, this is only necessary if you have to have paging -- which may just be a nice to have.

Thanks, Paul Wilson
9/24/2004 12:07:28 AM Paul:

What else needs to be added to the project in order to use a custom provider. I'm specifically speaking about the VistaDB provider. I've added the reference and the using to the Global.cs in my project but am receiving a: An unhandled exception of type 'VistaDB.VistaDBException' occurred in wilsonormapper.dll

Additional information: Must specify a DataSource prior to Open
This is happening line 54 in the Connection.cs

Mike
9/24/2004 6:06:48 AM Hi Mike:

All I did to test with VistaDb was to install it (so the necessary assemblies were in the GAC), convert a database I already had using their tool (so I didn't have to create mappings or entities), and then used the following code:

CustomProvider provider = new CustomProvider("VistaDB.Provider",
"VistaDB.VistaDBConnection", "VistaDB.VistaDBDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.IdentityQuery = "SELECT LastIdentity({0}) FROM {1}";
provider.SelectPageQuery = "SELECT TOP {2}, {0} *";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

I did not even set a reference or any using statements related to VistaDb. So based on my experience and your error, I'm thinking that either your connection string is not valid. Try creating your own VistaDb connection, outside of the mapper, with your connection string. Otherwise, send me as much as you can, and I'll take a look at it.

Thanks, Paul Wilson
9/24/2004 8:35:58 PM Paul:

Here is the global.cs from your WilsonORMapperDemo.cs modified to use the Northwind.vdb that comes with the VistaDB. BTW there is a new Release Candidate today:


using System;
using System.Windows.Forms;
using Wilson.ORMapper;
using VistaDB;

namespace Wilson.ORMapper.Demo
{
public sealed class Global
{
public static ObjectSpace Manager;

[STAThread]
static void Main() {
#if DEBUG
string projectPath = AppDomain.CurrentDomain.BaseDirectory.Replace(@"\bin\Debug", "");
#else
string projectPath = AppDomain.CurrentDomain.BaseDirectory.Replace(@"\bin\Release", "");
#endif

string mappingFile = projectPath + "NorthwindMap.config";
//string connectString = projectPath + "NorthwindDemo.mdb";
string connectString = @"C:\Program Files\VistaDB 2.0\Data\Northwind.vdb";

CustomProvider provider = new CustomProvider("VistaDB.Provider",
"VistaDB.VistaDBConnection", "VistaDB.VistaDBDataAdapter");
provider.StartDelimiter = "[";
provider.EndDelimiter = "]";
provider.IdentityQuery = "SELECT LastIdentity({0}) FROM {1}";
provider.SelectPageQuery = "SELECT TOP {2}, {0} *";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

//string connectString = projectPath + "NorthwindDemo.mdb";
//Global.Manager = new ObjectSpace(mappingFile, connectString, Provider.Access);

Application.Run(new MainForm());
}

private Global() {}
}
}
9/25/2004 7:39:51 AM So is it working now?

Later, Paul Wilson
10/8/2005 12:41:34 AM
When using a VistaDB database, the connection string needs to look something like this:

string connectString = "Datasource=" + appDomain.CurrentDomain.BaseDirectory + "Database.vdb;AccessMode=Local;Cypher=None;Exclusive=False;ReadOnly=False";

That works fine.

However, I am getting exceptions thrown for some null field types. i.e dates, int32 & double.

For eg.
ObjectSpace: SetField failed for NULL startDate

I'm looking into it further but any suggestions welcome.
10/8/2005 7:13:40 AM If your database fields are not setup to support nulls then you should not be using nullValue="..." in the mappings (nor nullable types in .NET v2.0). If that doesn't help then you'll need to send me all the details (db script/schema, xml mappings, entity classes, all other code).

Thanks, Paul Wilson