Parameters with DataAdapter commands

  • Thread starter Thread starter Predrag
  • Start date Start date
P

Predrag

Hello.

I have an issues with my data access layer. It should have methods like:
OpenTable(tableName) that returns DataTable and UpdateTable(dataTable,
tableName) that should update, insert and delete rows from the table.
OpenTable method is fine, but the problem is with UpdateTable method. I
decided to build insert, update and delete commands on-fly, and the problem
is with defining parameters' types. Namely, if it's ODBC provider, I need
OdbcParameter that requires OdbcType if it's SQL server, I need SqlDbType.
But, where do I find OdbcType or SqlDbType. After opening the table, DAL
returned DataTable as the result. Later on, this same object will be passed
in UpdateTable method and in DataTable, there are only Columns with DataType
property which is of different type then OdbcType or SqlDbType.

My thoughts, so far, could be summarized in:

1. I noticed that, if I create a parameter and specify as its value DataType
of the column, SqlDbType of the parameter gets the proper type. I don't
understand this, couldn't find anything about it and am afraid to use it.
2. I could do some type of mapping from DataType to SqlDbType (huge
switch/select case)
3. I could derive SqlDataColumn from DataColumn, populate additional Sql
specific fields during opening the table and insert it in DataTable object
(I already did that for ODBC provider, but it doesn't look very nice
(although it works)) The disadvantage is that update will work only with
data tables created with OpenTable method, because it will rely on sql
specific data members.

Any comment is very appreciated.
Thanks a lot.
- Predrag.
 
Predrag said:
Hello.

I have an issues with my data access layer. It should have methods like:
OpenTable(tableName) that returns DataTable and UpdateTable(dataTable,
tableName) that should update, insert and delete rows from the table.
OpenTable method is fine, but the problem is with UpdateTable method. I
decided to build insert, update and delete commands on-fly, and the problem
is with defining parameters' types. Namely, if it's ODBC provider, I need
OdbcParameter that requires OdbcType if it's SQL server, I need SqlDbType.
But, where do I find OdbcType or SqlDbType. After opening the table, DAL
returned DataTable as the result. Later on, this same object will be passed
in UpdateTable method and in DataTable, there are only Columns with DataType
property which is of different type then OdbcType or SqlDbType.

My thoughts, so far, could be summarized in:

1. I noticed that, if I create a parameter and specify as its value DataType
of the column, SqlDbType of the parameter gets the proper type. I don't
understand this, couldn't find anything about it and am afraid to use it.
Click to expand...

This is the key. Each provider-specific implementation the parameter class
implements the interface IDataParameter, which has a property
IDataParameter.DbType. Most also have an internal type system like SqlType,
etc. But all are required to map their internal types back and forth from
DbType's and you can just use the IDataParameter.DbType and let each
provider map that to an internal type.
2. I could do some type of mapping from DataType to SqlDbType (huge
switch/select case)
Click to expand...

That's already baked into the SqlParameter type.

David
 
Back
Top