|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Provider independant Data Accesswhat I want to achieve is to build my own set of DbCommand/DbConnection/DbDataAdapter/etc. classes that wrap a given .NET Data Provider like MySql, Firebird, SqlClient, OleDbClient, etc and provide the following functionalities on top : - @ as parameter identifier for parameters in every CommandText - Ordering of parameters by their occurence in the CommandText ( I noticed, OleDbClient does not take the parameter names but uses the order they were appended) - mapping of BCL types to DbTypes in the parameter (Set BCL type sets corresponding DbType) - Refetching identity values from the Db after insert through DbDataAdapter. - Fixing schema after reading through DbDataAdapter. For example in our scenario every column in a Firebird db, which has a generator assigned must be an AutoIncrement Column in the DataTable. The Firebird.NET Provider does not see this. - No SQL Processing, correct SQL for Provider will be generated by a higher layer. - Own DbProviderFactory implementation for this. The first three goals can be easily achieved by deriving from the Db*** classes and redirecting calls to an instance of the underlying provider with doing the given steps first. Probably someone knows a more elegant way of doing so, huh ?! The problem is the DbDataAdapter. I cannot see how to derive my own DbDataAdapter and easily redirect calls to an underlying SqlDataAdapter or DbDataAdapter of any other kind since some important methods are inaccessible due to protection level. Externally registering to the events of the DbDataAdapter is not suitable, because there is no way to get notified when the adapter fetched a schema, so i cannot apply my own schema modifications automagically. Directly implementing the IDbDataAdapter interface is not a bet too, because my DbProviderFactory must return a DbDataAdapter not an IDbDataAdapter ( why is this so ? ). So, any expert, how can i A ) Modify a DataTable´s schema after it has been fetched from the Db B ) Postprocess inserting of rows. with a DbDataAdapter and without any additional Method calls for the client ? Any clues ? Thanks a lot in advance Philipp Philipp,
Do you have a larger development team than Microsoft in Redmond, because I am sure that if it was easy what you want that an answer on that could be given in a newsgroup, than they had include it in their on new DBfactoryprovider class. Just my thought, Cor Show quote "Philipp Brune" <philipp.br***@t-online.de> schreef in bericht news:egabkd$1fn$00$1@news.t-online.com... > Hello Newsgroup, > > what I want to achieve is to build my own set of > DbCommand/DbConnection/DbDataAdapter/etc. classes > that wrap a given .NET Data Provider like > MySql, Firebird, SqlClient, OleDbClient, etc > and provide the following functionalities on top : > > - @ as parameter identifier for parameters in every CommandText > - Ordering of parameters by their occurence in the > CommandText ( I noticed, OleDbClient does not take > the parameter names but uses the order they were appended) > - mapping of BCL types to DbTypes in the parameter (Set BCL type sets > corresponding DbType) > - Refetching identity values from the Db after insert through > DbDataAdapter. > - Fixing schema after reading through DbDataAdapter. For example in > our scenario every column in a Firebird db, which has a generator > assigned must be an AutoIncrement Column in the DataTable. The > Firebird.NET Provider does not see this. > - No SQL Processing, correct SQL for Provider will be generated by a > higher layer. > - Own DbProviderFactory implementation for this. > > The first three goals can be easily achieved by deriving from the Db*** > classes and redirecting calls to an instance of the underlying provider > with doing the given steps first. Probably someone knows a more elegant > way of doing so, huh ?! > > The problem is the DbDataAdapter. I cannot see how to derive my own > DbDataAdapter and easily redirect calls to an underlying SqlDataAdapter > or DbDataAdapter of any other kind since some important methods are > inaccessible due to protection level. Externally registering to the > events of the DbDataAdapter is not suitable, because there is no way > to get notified when the adapter fetched a schema, so i cannot apply > my own schema modifications automagically. Directly implementing the > IDbDataAdapter interface is not a bet too, because my DbProviderFactory > must return a DbDataAdapter not an IDbDataAdapter ( why is this so ? ). > > So, any expert, how can i > > A ) Modify a DataTable´s schema after it has been fetched from the Db > B ) Postprocess inserting of rows. > > with a DbDataAdapter and without any additional Method calls for the > client ? Any clues ? > > Thanks a lot in advance > > Philipp > > > Cor,
Thank you for your immediate reply. no i don´t, i am a student and students have a lot of spare-time per definition ;-) No, it is not that much work at all because i basically use the functionality of the underlying .NET data providers. The problem is to integrate the mentioned additional functionality but hiding it from the client of my Db* classes, so the can use the straightforward ado.NET interface without any additional method calls. Basically it is around some additional lines of code in the FillSchema method and the RowUpdating/RowUpdated events. Philipp Cor Ligthert [MVP] schrieb: Show quote > Philipp, > > Do you have a larger development team than Microsoft in Redmond, because I > am sure that if it was easy what you want that an answer on that could be > given in a newsgroup, than they had include it in their on new > DBfactoryprovider class. > > Just my thought, > > Cor > > "Philipp Brune" <philipp.br***@t-online.de> schreef in bericht > news:egabkd$1fn$00$1@news.t-online.com... >> Hello Newsgroup, >> >> what I want to achieve is to build my own set of >> DbCommand/DbConnection/DbDataAdapter/etc. classes >> that wrap a given .NET Data Provider like >> MySql, Firebird, SqlClient, OleDbClient, etc >> and provide the following functionalities on top : >> >> - @ as parameter identifier for parameters in every CommandText >> - Ordering of parameters by their occurence in the >> CommandText ( I noticed, OleDbClient does not take >> the parameter names but uses the order they were appended) >> - mapping of BCL types to DbTypes in the parameter (Set BCL type sets >> corresponding DbType) >> - Refetching identity values from the Db after insert through >> DbDataAdapter. >> - Fixing schema after reading through DbDataAdapter. For example in >> our scenario every column in a Firebird db, which has a generator >> assigned must be an AutoIncrement Column in the DataTable. The >> Firebird.NET Provider does not see this. >> - No SQL Processing, correct SQL for Provider will be generated by a >> higher layer. >> - Own DbProviderFactory implementation for this. >> >> The first three goals can be easily achieved by deriving from the Db*** >> classes and redirecting calls to an instance of the underlying provider >> with doing the given steps first. Probably someone knows a more elegant >> way of doing so, huh ?! >> >> The problem is the DbDataAdapter. I cannot see how to derive my own >> DbDataAdapter and easily redirect calls to an underlying SqlDataAdapter >> or DbDataAdapter of any other kind since some important methods are >> inaccessible due to protection level. Externally registering to the >> events of the DbDataAdapter is not suitable, because there is no way >> to get notified when the adapter fetched a schema, so i cannot apply >> my own schema modifications automagically. Directly implementing the >> IDbDataAdapter interface is not a bet too, because my DbProviderFactory >> must return a DbDataAdapter not an IDbDataAdapter ( why is this so ? ). >> >> So, any expert, how can i >> >> A ) Modify a DataTableŽs schema after it has been fetched from the Db >> B ) Postprocess inserting of rows. >> >> with a DbDataAdapter and without any additional Method calls for the >> client ? Any clues ? >> >> Thanks a lot in advance >> >> Philipp >> >> >> > > Philipp,
As Cor suggested use factory approach supported in .net 2. Or go with an ORM tool (I always suggest LLBLGenPro) that does all that job for you. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Philipp Brune" <philipp.br***@t-online.de> wrote in message news:egabkd$1fn$00$1@news.t-online.com... > Hello Newsgroup, > > what I want to achieve is to build my own set of > DbCommand/DbConnection/DbDataAdapter/etc. classes > that wrap a given .NET Data Provider like > MySql, Firebird, SqlClient, OleDbClient, etc > and provide the following functionalities on top : > > - @ as parameter identifier for parameters in every CommandText > - Ordering of parameters by their occurence in the > CommandText ( I noticed, OleDbClient does not take > the parameter names but uses the order they were appended) > - mapping of BCL types to DbTypes in the parameter (Set BCL type sets > corresponding DbType) > - Refetching identity values from the Db after insert through > DbDataAdapter. > - Fixing schema after reading through DbDataAdapter. For example in > our scenario every column in a Firebird db, which has a generator > assigned must be an AutoIncrement Column in the DataTable. The > Firebird.NET Provider does not see this. > - No SQL Processing, correct SQL for Provider will be generated by a > higher layer. > - Own DbProviderFactory implementation for this. > > The first three goals can be easily achieved by deriving from the Db*** > classes and redirecting calls to an instance of the underlying provider > with doing the given steps first. Probably someone knows a more elegant > way of doing so, huh ?! > > The problem is the DbDataAdapter. I cannot see how to derive my own > DbDataAdapter and easily redirect calls to an underlying SqlDataAdapter > or DbDataAdapter of any other kind since some important methods are > inaccessible due to protection level. Externally registering to the > events of the DbDataAdapter is not suitable, because there is no way > to get notified when the adapter fetched a schema, so i cannot apply > my own schema modifications automagically. Directly implementing the > IDbDataAdapter interface is not a bet too, because my DbProviderFactory > must return a DbDataAdapter not an IDbDataAdapter ( why is this so ? ). > > So, any expert, how can i > > A ) Modify a DataTable´s schema after it has been fetched from the Db > B ) Postprocess inserting of rows. > > with a DbDataAdapter and without any additional Method calls for the > client ? Any clues ? > > Thanks a lot in advance > > Philipp > > > Miha,
thank you for the immediate reply. Well, i´m not sure i really understand this. It is a quite simple task, just some text replacement before command execution ( @ => ? p.E.) and some lines of code after Schema fetch if (hasGenerator(table, column)) { dataTable.Columns[column].AutoIncrement = true; } and after Insertion .... row[column] = dbLayer.FetchInsertedIdentity(table, column); row.AcceptChanges(); .... It is just about using existing .NET data providers, extending their functionality as mentioned and again providing an ado.NET interface to the clients. This cannot be so complex, can it ? It is really just about some lines of code to add, no complex sql dialect manipulation or ORM nescessary. Philipp Miha Markic [MVP C#] schrieb: Show quote > Philipp, > > As Cor suggested use factory approach supported in .net 2. > Or go with an ORM tool (I always suggest LLBLGenPro) that does all that job > for you. > "Philipp Brune" <her***@gmx.de> wrote in message If it's so easy, I'm sure you'll have it completed by the end of the day... news:OPZdz4r6GHA.348@TK2MSFTNGP02.phx.gbl... > It is just about using existing .NET data providers, extending their > functionality as mentioned and again providing an ado.NET interface > to the clients. This cannot be so complex, can it ? It is really just > about some lines of code to add, no complex sql dialect manipulation > or ORM nescessary. ;-) "Mark Rae" <mark@markNOSPAMrae.com> wrote in message Agreed, let us know tomorrow :-)news:OnZWN%23s6GHA.844@TK2MSFTNGP03.phx.gbl... > "Philipp Brune" <her***@gmx.de> wrote in message > news:OPZdz4r6GHA.348@TK2MSFTNGP02.phx.gbl... > >> It is just about using existing .NET data providers, extending their >> functionality as mentioned and again providing an ado.NET interface >> to the clients. This cannot be so complex, can it ? It is really just >> about some lines of code to add, no complex sql dialect manipulation >> or ORM nescessary. > > If it's so easy, I'm sure you'll have it completed by the end of the > day... ;-) -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Philipp Brune" <philipp.br***@t-online.de> ha scritto nel messaggio I'm also trying to overcome this awful limit of the standard ADO.NET 2.0 news:egabkd$1fn$00$1@news.t-online.com... > what I want to achieve is to build my own set of > DbCommand/DbConnection/DbDataAdapter/etc. classes > that wrap a given .NET Data Provider like > MySql, Firebird, SqlClient, OleDbClient, etc > and provide the following functionalities on top : > > - @ as parameter identifier for parameters in every CommandText classes; this alone, I think, is the biggest problem of the ADO.NET 2.0 library... and it's even worse because it could have been easily fixed by having any data provider implement a GetParameterDelimiter() method and using a generic delimiter in the DbCommand class! I really don't know why Microsoft didn't handle this better :-/ I also have another couple suggestions for MS guys, maybe the next versio of ADO.NET will have something like that: 1) Provide a higher-level interface for creating data parameters: I can do SqlCommand.Parameters.Add(string name,SqlDbType type), but I can only do DbCommand.Parameters.Add(Object o); so I have to do DbCommand.CreateParameter(), manually set parameter properties and then DbCommand.Parameters.Add(). Why is this?!? 2) Please, please, *PLEASE* provide a way to map System.Type to System.Data.DbType!!! I'm trying to build a generic data access layer for variable data formats, and there is simply *NO WAY* to do this, other than building my own type mapping method! Massimo
Show quote
"Massimo" <bar***@mclink.it> wrote in message Wow, guys, it is not that easy at all. It is not just parameter naming news:eq9k7Au6GHA.4348@TK2MSFTNGP03.phx.gbl... > "Philipp Brune" <philipp.br***@t-online.de> ha scritto nel messaggio > news:egabkd$1fn$00$1@news.t-online.com... > >> what I want to achieve is to build my own set of >> DbCommand/DbConnection/DbDataAdapter/etc. classes >> that wrap a given .NET Data Provider like >> MySql, Firebird, SqlClient, OleDbClient, etc >> and provide the following functionalities on top : >> >> - @ as parameter identifier for parameters in every CommandText > > I'm also trying to overcome this awful limit of the standard ADO.NET 2.0 > classes; this alone, I think, is the biggest problem of the ADO.NET 2.0 > library... and it's even worse because it could have been easily fixed by > having any data provider implement a GetParameterDelimiter() method and > using a generic delimiter in the DbCommand class! > I really don't know why Microsoft didn't handle this better :-/ convention. It goes way beyond parameters. There are a ton of databases out there each with its own perks and features and slightly different sql language, etc. -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Show quote
"Miha Markic [MVP C#]" <miha at rthand com> ha scritto nel messaggio That's ok; but I'm only talking about command parameters here. Every ADO.NET news:OaRlsPu6GHA.1560@TK2MSFTNGP04.phx.gbl... >>> - @ as parameter identifier for parameters in every CommandText >> >> I'm also trying to overcome this awful limit of the standard ADO.NET 2.0 >> classes; this alone, I think, is the biggest problem of the ADO.NET 2.0 >> library... and it's even worse because it could have been easily fixed by >> having any data provider implement a GetParameterDelimiter() method and >> using a generic delimiter in the DbCommand class! >> I really don't know why Microsoft didn't handle this better :-/ > > Wow, guys, it is not that easy at all. It is not just parameter naming > convention. It goes way beyond parameters. > There are a ton of databases out there each with its own perks and > features and slightly different sql language, etc. 2.0 data provider has to implement them someway, so, if a DB maker implements a data provider, this means parameter support has to be there. And it has to support the same feature set of other providers, since the methods it needs to implement are defined at the interface level. The only true difference here are data types (which can be taken care of by using the abstract System.Data.DbType instead of DB-specific ones) and the infamous parameter delimiter, which is different for any data provider. It really doesn't seem so hard to me to implement some text substitution here to handle this automatically and finally make these parameters usable. Massimo Massimo,
AFAIK is there not any problem to use the same code for parameters forever as long as you use them in the sequence as they are used in the SQL transact code that you use. Therefore you need no wrapper. Cor Show quote "Massimo" <bar***@mclink.it> schreef in bericht news:OMItiWu6GHA.4348@TK2MSFTNGP03.phx.gbl... > "Miha Markic [MVP C#]" <miha at rthand com> ha scritto nel messaggio > news:OaRlsPu6GHA.1560@TK2MSFTNGP04.phx.gbl... > >>>> - @ as parameter identifier for parameters in every CommandText >>> >>> I'm also trying to overcome this awful limit of the standard ADO.NET 2.0 >>> classes; this alone, I think, is the biggest problem of the ADO.NET 2.0 >>> library... and it's even worse because it could have been easily fixed >>> by having any data provider implement a GetParameterDelimiter() method >>> and using a generic delimiter in the DbCommand class! >>> I really don't know why Microsoft didn't handle this better :-/ >> >> Wow, guys, it is not that easy at all. It is not just parameter naming >> convention. It goes way beyond parameters. >> There are a ton of databases out there each with its own perks and >> features and slightly different sql language, etc. > > That's ok; but I'm only talking about command parameters here. Every > ADO.NET 2.0 data provider has to implement them someway, so, if a DB maker > implements a data provider, this means parameter support has to be there. > And it has to support the same feature set of other providers, since the > methods it needs to implement are defined at the interface level. The only > true difference here are data types (which can be taken care of by using > the abstract System.Data.DbType instead of DB-specific ones) and the > infamous parameter delimiter, which is different for any data provider. It > really doesn't seem so hard to me to implement some text substitution here > to handle this automatically and finally make these parameters usable. > > > Massimo > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> ha scritto nel messaggio What do you mean exactly?news:uxLapku6GHA.4116@TK2MSFTNGP03.phx.gbl... > AFAIK is there not any problem to use the same code for parameters forever > as long as you use them in the sequence as they are used in the SQL > transact code that you use. Therefore you need no wrapper. I'm talking about parameter placeholders in the SQL query; even if I use unnamed parameters (which I don't want to), I'd still have to place them in my SQL string... and they need their delimiter there. Which is different for each provider... Massimo Hey Friends,
i got an idea now, how to solve the basic issues of refetching identities / fixing DataTable schemas and CommandText replacement : Since DbDataAdapter and DbCommand inherit from Component which inherits from MarshalByRefObject I will use the System.Runtime.Remoting.Proxies.RealProxy to intercept client calls to certain Methods. Now, i could possibly solve my problem like this : Program my own DbProviderFactory, lets call it DBF that wraps the DbProviderFactory of a given .NET data provider. DBF returns the return values of the wrapped factory for all method calls except CreateCommand / CreateDataAdapter. For those a transparent proxy is returned. The transparent proxy does the following : DbCommand : Intercept calls to the setter Method of CommandText property to fix the given CommandText. DbDataAdapter : Intercept calls to FillSchema / Fill methods for postprocessing the retrieved schema. Additionally the factory will register for the RowUpdating/RowUpdated events for retrieval of the db generated identities. Does this make any sense to you ? Philipp Massimo schrieb: Show quote > "Philipp Brune" <philipp.br***@t-online.de> ha scritto nel messaggio > news:egabkd$1fn$00$1@news.t-online.com... > >> what I want to achieve is to build my own set of >> DbCommand/DbConnection/DbDataAdapter/etc. classes >> that wrap a given .NET Data Provider like >> MySql, Firebird, SqlClient, OleDbClient, etc >> and provide the following functionalities on top : >> >> - @ as parameter identifier for parameters in every CommandText > > I'm also trying to overcome this awful limit of the standard ADO.NET 2.0 > classes; this alone, I think, is the biggest problem of the ADO.NET 2.0 > library... and it's even worse because it could have been easily fixed > by having any data provider implement a GetParameterDelimiter() method > and using a generic delimiter in the DbCommand class! > I really don't know why Microsoft didn't handle this better :-/ > > I also have another couple suggestions for MS guys, maybe the next > versio of ADO.NET will have something like that: > > 1) Provide a higher-level interface for creating data parameters: I can > do SqlCommand.Parameters.Add(string name,SqlDbType type), but I can only > do DbCommand.Parameters.Add(Object o); so I have to do > DbCommand.CreateParameter(), manually set parameter properties and then > DbCommand.Parameters.Add(). Why is this?!? > 2) Please, please, *PLEASE* provide a way to map System.Type to > System.Data.DbType!!! I'm trying to build a generic data access layer > for variable data formats, and there is simply *NO WAY* to do this, > other than building my own type mapping method! > > > Massimo > > Massimo wrote:
Show quote > "Philipp Brune" <philipp.br***@t-online.de> ha scritto nel messaggio You're confusing the problems caused by a chosen 'solution' to the> news:egabkd$1fn$00$1@news.t-online.com... > > > what I want to achieve is to build my own set of > > DbCommand/DbConnection/DbDataAdapter/etc. classes > > that wrap a given .NET Data Provider like > > MySql, Firebird, SqlClient, OleDbClient, etc > > and provide the following functionalities on top : > > > > - @ as parameter identifier for parameters in every CommandText > > I'm also trying to overcome this awful limit of the standard ADO.NET > 2.0 classes; this alone, I think, is the biggest problem of the > ADO.NET 2.0 library... and it's even worse because it could have been > easily fixed by having any data provider implement a > GetParameterDelimiter() method and using a generic delimiter in the > DbCommand class! I really don't know why Microsoft didn't handle > this better :-/ real problem WITH the real problem. It's not that ADO.NET 2.0 lacks some classes and thus has an 'awful' limit, it's just that you try to use it for something that it's not designed for. What you want is a layer on top of ADO.NET. > I also have another couple suggestions for MS guys, maybe the next because not all databases support SqlDbType? :)> versio of ADO.NET will have something like that: > > 1) Provide a higher-level interface for creating data parameters: I > can do SqlCommand.Parameters.Add(string name,SqlDbType type), but I > can only do DbCommand.Parameters.Add(Object o); so I have to do > DbCommand.CreateParameter(), manually set parameter properties and > then DbCommand.Parameters.Add(). Why is this?!? > 2) Please, please, Well, let me tell you as a guy who wrote this code some time ago, it's> PLEASE provide a way to map System.Type to System.Data.DbType!!! I'm > trying to build a generic data access layer for variable data > formats, and there is simply *NO WAY* to do this, other than building > my own type mapping method! simply not possible to create that mapping. DbType is too vague. Not all providers map a given type T to the same DbType. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> ha scritto nel It looks to me like it's designed to offer provider-independant data access, messaggio news:xn0es9gvf59a7w002@news.microsoft.com... > You're confusing the problems caused by a chosen 'solution' to the > real problem WITH the real problem. > > It's not that ADO.NET 2.0 lacks some classes and thus has an 'awful' > limit, it's just that you try to use it for something that it's not > designed for. but then you have to write parameterized queries using a provider-specific syntax. I don't know how you call this, but I call it "bad design"... >> 1) Provide a higher-level interface for creating data parameters: I I meant, why can't I do DbCommand.Parameters.Add(string name,DbType type)? I >> can do SqlCommand.Parameters.Add(string name,SqlDbType type), but I >> can only do DbCommand.Parameters.Add(Object o); so I have to do >> DbCommand.CreateParameter(), manually set parameter properties and >> then DbCommand.Parameters.Add(). Why is this?!? > > because not all databases support SqlDbType? :) need some more lines of code to do this, and it's completely useless... Massimo Massimo wrote:
Show quote > "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> ha scritto MS had this in the past with ADO. As the designer of ADO once said: it> nel messaggio news:xn0es9gvf59a7w002@news.microsoft.com... > > > You're confusing the problems caused by a chosen 'solution' to the > > real problem WITH the real problem. > > > > It's not that ADO.NET 2.0 lacks some classes and thus has an 'awful' > > limit, it's just that you try to use it for something that it's not > > designed for. > > It looks to me like it's designed to offer provider-independant data > access, but then you have to write parameterized queries using a > provider-specific syntax. I don't know how you call this, but I call > it "bad design"... was a maintenance nightmare and they wanted to get rid of it. The main problem is that you'll get something like a big phat featureset which contains ALL features of ALL databases and from that big set you implement the ones supported by the DB the provider has to support and ignore the rest or convert them to features supported. So they defined a common set of base classes you can program against if you really want to and let the provider writer in charge of implementing db specific features. Since the beginning of .NET, people have critisized this approach and they do have a point, however it's not as bad as it looks. The thing is that the assumption that all databases are equal and work the same is not true. So you can create to some extend an API which works on all databases or a subset of the databases, but that API will always either: - have to have DB-specific code paths (most o/r mappers, like ours) use this) - use a common subset of the features of all databases and ignore the rest. the second approach is tempting, though will likely give you problems. The first requires a lot of code and then also for a subset of databases. > >> 1) Provide a higher-level interface for creating data parameters: I That's not entirely true. You can first ask a DbProviderFactory from> >> can do SqlCommand.Parameters.Add(string name,SqlDbType type), but I > >> can only do DbCommand.Parameters.Add(Object o); so I have to do > >> DbCommand.CreateParameter(), manually set parameter properties and > >> then DbCommand.Parameters.Add(). Why is this?!? > > > > because not all databases support SqlDbType? :) > > I meant, why can't I do DbCommand.Parameters.Add(string name,DbType > type)? I need some more lines of code to do this, and it's completely > useless... the DbProviderFactories class in System.Data.Common. Then you can ask that factory to create a DbParameter instance. That parameter is then uninitialized and you can set its DbType to a value if you want and other properties. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------
Show quote
"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> ha scritto nel I absolutely agree on the wrongness of the assumption that "all databases messaggio news:xn0esavt853evl001@news.microsoft.com... >> It looks to me like it's designed to offer provider-independant data >> access, but then you have to write parameterized queries using a >> provider-specific syntax. I don't know how you call this, but I call >> it "bad design"... > > MS had this in the past with ADO. As the designer of ADO once said: it > was a maintenance nightmare and they wanted to get rid of it. The main > problem is that you'll get something like a big phat featureset which > contains ALL features of ALL databases and from that big set you > implement the ones supported by the DB the provider has to support and > ignore the rest or convert them to features supported. > > So they defined a common set of base classes you can program against > if you really want to and let the provider writer in charge of > implementing db specific features. > > Since the beginning of .NET, people have critisized this approach and > they do have a point, however it's not as bad as it looks. The thing is > that the assumption that all databases are equal and work the same is > not true. So you can create to some extend an API which works on all > databases or a subset of the databases, but that API will always either: > - have to have DB-specific code paths (most o/r mappers, like ours) use > this) > - use a common subset of the features of all databases and ignore the > rest. > > the second approach is tempting, though will likely give you problems. > The first requires a lot of code and then also for a subset of > databases. are equal"; and I know I have to use only a small subset of database features if I want my application to work with every one of them. But the problem here is, even if I'm writing a really simple application (in terms of DB queries), I *still* have to worry about provider-specific issues! My application simply does single-row INSERTs. It uses plain SQL language. It doesn't even use stored procedures, since A) it needs to work on any database, and B) it just doesn't need them. The SQL queries it creates *can* actually work on every database known to man... *but* I have to worry about parameter delimiters, because each ADO.NET provider has its own one. So my only options are avoiding parameters (which I don't want to) or... parameterizing parameter markers themselves, using my internal placeholder character for them and then running them through a parser of my own and putting in the actual provider-specific delimiter. This really looks like a nonsense to me, the whole ADO.NET 2.0 database abstraction is invalidated, even for really simple queries, due to... *single characters*, which the framework could have handled by itself quite easily. This isn't even a database-specific issue: it's only created by the *providers*! >> I meant, why can't I do DbCommand.Parameters.Add(string name,DbType I can directly ask a parameter from the DbCommand, for that matter :-)>> type)? I need some more lines of code to do this, and it's completely >> useless... > > That's not entirely true. You can first ask a DbProviderFactory from > the DbProviderFactories class in System.Data.Common. Then you can ask > that factory to create a DbParameter instance. That parameter is then > uninitialized and you can set its DbType to a value if you want and > other properties. But I need to set its values and then pass it do DbCommand.Parameters.Add(), when I could just have used DbCommand.Parameters.Add(name,type), like I do for SqlCommand's. I know, it's really a little issue and it's easy to do it in a method of my own, but then, why wasn't this included in the framework?!? Massimo Lots of people have had problems with the various providers and how they
formulate parameter names and place parameter markers inside their queries. I answered this problem using a helper class, the original post is here: http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/tree/browse_frm/thread/deb2bec6610b7f16/8a917935e2e4eb78?rnum=1&hl=en&_done=%2Fgroup%2Fmicrosoft.public.dotnet.framework.adonet%2Fbrowse_frm%2Fthread%2Fdeb2bec6610b7f16%2F8a917935e2e4eb78%3Ftvc%3D1%26hl%3Den%26#doc_519e9efb82fc7f58 Robert Show quote "Philipp Brune" <philipp.br***@t-online.de> wrote in message news:egabkd$1fn$00$1@news.t-online.com... > Hello Newsgroup, > > what I want to achieve is to build my own set of > DbCommand/DbConnection/DbDataAdapter/etc. classes > that wrap a given .NET Data Provider like > MySql, Firebird, SqlClient, OleDbClient, etc > and provide the following functionalities on top : > > - @ as parameter identifier for parameters in every CommandText > - Ordering of parameters by their occurence in the > CommandText ( I noticed, OleDbClient does not take > the parameter names but uses the order they were appended) > - mapping of BCL types to DbTypes in the parameter (Set BCL type sets > corresponding DbType) > - Refetching identity values from the Db after insert through > DbDataAdapter. > - Fixing schema after reading through DbDataAdapter. For example in > our scenario every column in a Firebird db, which has a generator > assigned must be an AutoIncrement Column in the DataTable. The > Firebird.NET Provider does not see this. > - No SQL Processing, correct SQL for Provider will be generated by a > higher layer. > - Own DbProviderFactory implementation for this. > > The first three goals can be easily achieved by deriving from the Db*** > classes and redirecting calls to an instance of the underlying provider > with doing the given steps first. Probably someone knows a more elegant > way of doing so, huh ?! > > The problem is the DbDataAdapter. I cannot see how to derive my own > DbDataAdapter and easily redirect calls to an underlying SqlDataAdapter > or DbDataAdapter of any other kind since some important methods are > inaccessible due to protection level. Externally registering to the > events of the DbDataAdapter is not suitable, because there is no way > to get notified when the adapter fetched a schema, so i cannot apply > my own schema modifications automagically. Directly implementing the > IDbDataAdapter interface is not a bet too, because my DbProviderFactory > must return a DbDataAdapter not an IDbDataAdapter ( why is this so ? ). > > So, any expert, how can i > > A ) Modify a DataTable´s schema after it has been fetched from the Db > B ) Postprocess inserting of rows. > > with a DbDataAdapter and without any additional Method calls for the > client ? Any clues ? > > Thanks a lot in advance > > Philipp > > > "Robert Simpson" <rmsimpson@nospam.nospam> ha scritto nel messaggio Of course this problem can be solved :-)news:uaxA5Vw6GHA.4276@TK2MSFTNGP04.phx.gbl... > Lots of people have had problems with the various providers and how they > formulate parameter names and place parameter markers inside their > queries. I answered this problem using a helper class, the original post > is here: But I think this issue should definitely be taken care of by the framework, since ADO.NET 2.0 tries to build a generic and rovider-independant data access layer; after creating System.Data.Common, DbProviderFactory, DbConnectionStringBuilder and so on, failing to establish a common interfare to SQL parameters is a little... ridicouous. Massimo I have done something like this, a database abstraction database and provider
agnostic. Subclasses “the normal†DataReader, DataTable and so on. The most difficult part is ironing out the database behavior differences (now supports SQL Server, Oracle, DB2 (also the AS400) and Postgre SQL). The development time was in the years… Show quote "Massimo" wrote: > "Robert Simpson" <rmsimpson@nospam.nospam> ha scritto nel messaggio > news:uaxA5Vw6GHA.4276@TK2MSFTNGP04.phx.gbl... > > > Lots of people have had problems with the various providers and how they > > formulate parameter names and place parameter markers inside their > > queries. I answered this problem using a helper class, the original post > > is here: > > Of course this problem can be solved :-) > But I think this issue should definitely be taken care of by the framework, > since ADO.NET 2.0 tries to build a generic and rovider-independant data > access layer; after creating System.Data.Common, DbProviderFactory, > DbConnectionStringBuilder and so on, failing to establish a common interfare > to SQL parameters is a little... ridicouous. > > > Massimo > >  |
|||||||||||||||||||||||
Other interesting topics