OleDbDataAdapter
-
Hi folks, Consider an SQL Server table that has got a PK (auto-identity column), which has been referenced by another table as a FK. Then imagine 2 DataTables that has been created to refer to the above-mentioned SQL Server tables, either partially or completely. I need to know how can I read/write these DataTables from/to those DataSources using the OleDbDataAdapter. It goes without saying that when you insert a row within the first source table, you'll get an IDENTITY that has to be referenced by the second table as the FK's value. The thing that I cannot understand is that how I can possibly interact with these 2 DataTable(s) through the OleDbDataAdapter and how to map DataSource-DataTable columns to each other as well as how to get the newly added IDENTITY and insert it in the second DataTable. Any sample would be highly appreciated Thanks in advance, Mehdi Mousavi - Software Architect [ http://mehdi.biz ]
-
Hi folks, Consider an SQL Server table that has got a PK (auto-identity column), which has been referenced by another table as a FK. Then imagine 2 DataTables that has been created to refer to the above-mentioned SQL Server tables, either partially or completely. I need to know how can I read/write these DataTables from/to those DataSources using the OleDbDataAdapter. It goes without saying that when you insert a row within the first source table, you'll get an IDENTITY that has to be referenced by the second table as the FK's value. The thing that I cannot understand is that how I can possibly interact with these 2 DataTable(s) through the OleDbDataAdapter and how to map DataSource-DataTable columns to each other as well as how to get the newly added IDENTITY and insert it in the second DataTable. Any sample would be highly appreciated Thanks in advance, Mehdi Mousavi - Software Architect [ http://mehdi.biz ]
First, don't use the
OleDbDataAdapterfor SQL Server! Use the SQL Server-specific classes in theSystem.Data.SqlClientnamespace. They exhibit much more functionality and provide features that the generic OLE DB providers can't provide (at least in an abstract manner). Second, use a typedDataSet, or construct one programmatically at runtime (not fun). You can create an identity column in one of your tables. When you insert a row into thatDataTable, the column value is incremented to the next identity using your incremental step. This allows your FK column to reference the identity PK. This is easy to do. The trick in a multi-user database-driven application is when you use thatDataSetto update the database usingSqlDataAdapter.Update. You should read the article, Inserting relational data using DataSet and DataAdapter[^]. Basically, yourInsertCommandshould also include a SELECT statement to make sure theDataSetis updated appropriately. The relationship that would exist in theDataSetfor the PK/FK would update the FK with the PK value that was actually used when the row was inserted.Microsoft MVP, Visual C# My Articles
-
First, don't use the
OleDbDataAdapterfor SQL Server! Use the SQL Server-specific classes in theSystem.Data.SqlClientnamespace. They exhibit much more functionality and provide features that the generic OLE DB providers can't provide (at least in an abstract manner). Second, use a typedDataSet, or construct one programmatically at runtime (not fun). You can create an identity column in one of your tables. When you insert a row into thatDataTable, the column value is incremented to the next identity using your incremental step. This allows your FK column to reference the identity PK. This is easy to do. The trick in a multi-user database-driven application is when you use thatDataSetto update the database usingSqlDataAdapter.Update. You should read the article, Inserting relational data using DataSet and DataAdapter[^]. Basically, yourInsertCommandshould also include a SELECT statement to make sure theDataSetis updated appropriately. The relationship that would exist in theDataSetfor the PK/FK would update the FK with the PK value that was actually used when the row was inserted.Microsoft MVP, Visual C# My Articles
Heath Stewart wrote:First, don't use the OleDbDataAdapter for SQL Server! We might change the Database engine later from SQL Server 2000 to Oracle 9i, and that's why we tried to use the OleDB Data Provider. Heath Stewart wrote: Second, use a typed DataSet, or construct one programmatically at runtime (not fun). I don't see anything fun in this quote. I've never ever used an untyped DataSet, since it has got many drawbacks... Heath Stewart wrote: You should read the article, Inserting relational data using DataSet and DataAdapter[^]. Thanks a lot, I read the article and it will certainly solve my problem after two damn weeks of trying to post the same question here and there. I do really appreciate your help. Thank you for your time, Mehdi Mousavi - Software Architect [ http://mehdi.biz ]
-
Heath Stewart wrote:First, don't use the OleDbDataAdapter for SQL Server! We might change the Database engine later from SQL Server 2000 to Oracle 9i, and that's why we tried to use the OleDB Data Provider. Heath Stewart wrote: Second, use a typed DataSet, or construct one programmatically at runtime (not fun). I don't see anything fun in this quote. I've never ever used an untyped DataSet, since it has got many drawbacks... Heath Stewart wrote: You should read the article, Inserting relational data using DataSet and DataAdapter[^]. Thanks a lot, I read the article and it will certainly solve my problem after two damn weeks of trying to post the same question here and there. I do really appreciate your help. Thank you for your time, Mehdi Mousavi - Software Architect [ http://mehdi.biz ]
Mehdi Mousavi wrote: We might change the Database engine later from SQL Server 2000 to Oracle 9i, and that's why we tried to use the OleDB Data Provider. Reasonable enough. I just get used to saying this because so many with no such plans use the OLE DB provider and are trying to do SQL Server-specific things many times. Mehdi Mousavi wrote: I don't see anything fun in this quote. I've never ever used an untyped DataSet, since it has got many drawbacks... Constructing typed
DataSetclasses using the DataSet designer is definitely a heck of a lot easier than trying to code the same thing, plus gives you typed access to columns which is much faster. Sure, the same thing could be done manually, too, but again it can be tedious.Microsoft MVP, Visual C# My Articles