ADO.Net • View topic • Provider independent data access: creating a data adapter from a connection

Provider independent data access: creating a data adapter from a connection

1 posts • Page 1 of 1

1. ADO.NET Joins
Hi, Is there a method to combine two ADO.NET DataTables by applying a DataRelation, as if making an SQL join, to produce a single object? I would like to pass the product of this "join" to an ASP.NET DataGrid and ideally would like to be able to edit the data and have the changes reflected in the original DataTables. I think this might be stretching it a bit thought :-) Thanks, Micky.
2. General network error. Check your network documentation.
Hello, I'm getting the following error when running a large SQL. I am using the SqlCommand and calling the ExecuteNonQuery with the CommandTimeout set to 0. Here is the example of the code sqlCommand = new SqlCommand(m_SQLBuffer, (SqlConnection) m_connection); sqlCommand.CommandTimeout=0; //I GET THE EXCEPTION ON THE LINE BELOW m_numOfRowAffected = sqlCommand.ExecuteNonQuery(); // //Throws an exception of System.SystemException {"General network error. Check your network documentation."} Does anyone know what this exception means? Thanks
3. forms as arguments in procedures
In visual basic 6 I was able to do the following: ABCProc(Me) sub ABCProc(frm as form) ... rowTopics.ParentID = CInt(frm.txtParentID.Text) etc. In vb net when I pass the form the controls on it don't come with it so I can't create a new row by the values in controls on another form. That would vastly reduce the amount of code I have to write to produce generic routines. Can somebody help. I've looked in four books and searched msdn without results. dennist
4. Alternatives to databinding
I think I'm going to abandon using databinding in my windows forms project because it simply doesn't work as well as it ought to. There are simply too many road blocks and too much odd are poorly documented behavior to be worth my time any more. I patched up and worked around so many of the oddities in framework that I don't even feel like I still in control of the code anymore. So I'm ditching it (*I think*). Now I need to work out an alternative scheme for getting data from my datasource into the controls on my form and back again, so my question is does anybody know of any good articles or tutorials on this subject. I want to try and make sure I've considered everything before I start what might be a major undertaking so I can decide on the most elegant and flexible solution. Cheers Matt
5. Multi-page data entry with asp.net
Hi, I have a database record with a very large number of fields that need to be entered so I need to write a data entry site using asp.net that will divide the fields into multiple pages. Once all the pages have been entered the user may save or cancel. How can I retain the values entered on previous pages? Thanks, Jeronimo

Provider independent data access: creating a data adapter from a connection

by Harold Howe » Fri, 02 Jun 2006 06:10:28 GMT

I have written a provider independent ado.net utility class for 
executing queries. One of the routines looked like this in .net 1.1:

static public DataSet ExecuteQuery(IDbConnection connection, String sql)
{
   connection.Open();

   DataSet result  = new DataSet();
   IDbDataAdapter adapter = /* magic to create an adapter from a 
connection*/

   // The sybase adapter's implement IDisposable, so wrap with a using. 

   using(adapter as IDisposable)
   {
     adapter.SelectCommand.CommandText = sql;
     adapter.SelectCommand.Connection = conn;
     adapter.Fill(result);
   }

   return result;
}

The magic code to create the adapter was ugly: it essentially performed 
a dictionary lookup on the type of connection, and created the resulting 
adapter type. If IDbConnection had a method called CreateAdapter, 
analagous to CreateCommand, this would have been cleaner.

With the introduction of ADO.NET 2.0 and the DbProviderFactory class, I 
was hoping that some of the ugliness would go away. Unfortunately, I am 
running into some problems. Namely

1- DbConnection still does not have a CreateAdapter method

2- DbConnection does not offer a way to get back to the 
DbProviderFactory that created it.

So I still cannot create the correct adapter given only a generic 
DbConnection. I am looking for suggestions on how to code my routine. So 
far, I have thought of these

1- Make the user pass in the DbProviderFactory that goes with the 
connection. CONS: forces users to use DBProviderFactory. We have a lot 
of code that doesn't.

2- Stick with the dictionary lookup (Dictionary<ConnType, AdapterType>. 
CONS: DLL dependencies explode.

3- Use a different dictionary lookup (Dictionary<ConnType, 
DBProviderFactory>. CONS : A little messy, but better than 2.

4- Don't use provider specific adapters at all, just use a generic one. 
Like this:

class GenericAdapter : System.Data.Common.DbDataAdapter
{
}

static public DataSet ExecuteQuery(DbConnection connection, String sql)
{
   connection.Open();

   DataSet result  = new DataSet();
   GenericAdapter adapter = new GenericAdapter();
   adapter.SelectCommand = conn.CreateCommand();
   adapter.SelectCommand.CommandText = sql;
   adapter.Fill(result);
   return result;
}

I have tested this with two databases, Sybase ASE and ASA, and it seems 
to work. Are there any serious downfalls to not using the vendor 
specific adapter type? Is this code safe?

H^2

Top

I'm still trying to provide my application (ADO.NET 2.0) with a generic data 
access layer, independent of the specific provider used.

Now, my main concern is about connection strings, which can be quite 
different amongst various providers.

Imagine I have read some parameters from a configuration file, like the 
database type and some connection informations, which every data provider in 
the world will definitely supports: hostname, database name, username and 
password. I'm purposely avoiding Windows authentication here, since only SQL 
Server (and maybe Oracle) supports it.

Ok, I'm creating my provider factory with some code:

-----
Enum DBType
{
    SQLServer,
    Oracle,
    MySQL
};                    // So you know what "type" is :-)


DbProviderFactory factory = null;

switch(type)
{
    case DBType.SQLServer:
        factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        break;
    case DBType.Oracle:
        factory = 
DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
        break;
    case DBType.MySQL:
        factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
        break;
    default:
        throw new NotImplementedException;
}

DbConnectionStringBuilder csb = factory.CreateConnectionStringBuilder();
-----


Ok, now what?
The generic DbConnectionStringbuilder is *so* abstract it only lets you add 
generic key-value pairs; you can't even set the data source name or the 
username, even if any provider has these parameters!

So, here we go again: the whole provider-independent data model of ADO.NET 
2.0 isn't so provider-independent at all.

How to solve this without manually specifying the connection string, which I 
don't know and actually don't want to care about? What I want is my 
application to read the hostname, the database name, the username and the 
password from a configuration file, not some f***ing connection string.

I'm also forced to use the factory model, otherwise I'll have to manually 
configure the specific data provider in my db-abstraction class, which I can 
surely do, but then the program won't start (or even compile!) if it 
references Mysql.Data.MySqlClient and that software isn't installed on the 
destination system.

Can someone please help?

Thanks


Massimo

2.Data Adapter Configuration Wizard - Data Connection fails

I can drag any table from server explorer to a form and get a new 
SqlConnection and Adapter. I can also perform "Generate DataSet" and 
"Preview data". However - configuring the adapter using the Data Adapter 
Configuration Wizard fails in the second screen "Choose your data 
connection" with error "An unexpected error has occured.   Error message: 
Object reference not set to an instance of an object.   Call stack:  at 
Microsoft.VSDesigner.Data.VS.VsConnectionManager.CheckConnection(......)"

Not using the wizard I am able to make my programs run. The wizard worked 
fine for the first weeks/months. I am mostly connecting to a local MSDE 
(sp3) using VS.NET2003 EnterpriseArcitect on Win XpPro(sp2).

Any ideas ?


Erik.

3.Data Adapter Configuration Wizard creates new Connection

Hi

I'm working on a project with multiple developers in VB.NET 2003. SQL
Database

We are mainly connecting to the database by dropping SQL Data Adapter
objects on a form, configuring it and generating a dataset. The Data Adapter
wizard creates a connection object on the form as well. Great.

The problem comes in when one developer has created these objects on a form,
and then another developer goes in a re-runs the Data Adapter wizard (to add
a field to the query for example). The wizard then adds another connection
object to the form because it can't find the existing one (because the
workstation ID is different, because of the different developer). Is there a
better way to do this so we don't have these extra connections being created
when we don't want to? This is creating a lot of hassle.

Thanks

4.Server Explorer - Add Data Connection - Add new Data Provider to l

Does anyone know how to add a new data provider to  Visual Studio - to the 
Provider list in Server Explorer.  I am using Sybase's ADO.NET Data Provider. 
 It has been added to my GAC and appears in my References.
However, I would like to add the provider to the Provider list in Server 
Explorer.  Thanks.
~Marsha

5.Typed DataSet with independent data provider?

Hello

I have a project (class library) for data layer of my solution that uses 
Typed DataSet.
I want the Typed DataSet to be independent of the data provider.

Is it possible to use Typed DataSet while the data provider is identified in 
app.config or web.config?

Regards
Hamed

6. Data Provider independent code

7. SQLSummit .NET data provider list (other data access middleware lists)

8. Oracle Data Provider for .NET: Data provider internal error(-3000)

Post a reply

1 posts • Page 1 of 1

Who is online

Users browsing this forum: No registered users and 7 guest

Provider independent data access: creating a data adapter from a connection

Provider independent data access: creating a data adapter from a connection

Jump to:

Next

Provider independent data access: creating a data adapter from a connection

Similar Threads:

Who is online