Converting System.Type to OleDbType

Converting System.Type to OleDbType

• Petr Kadlec

  
  

  I’ve just seen your second post and I think I got you.

  Let’s try to define your problem: you fill a dataset, you make some changes in dataset and you want to synchronize it with database. What you’re trying to do is to build on-the-fly the Insert/Update/Delete commands of your DataAdapter.

  If so, let’s try to imagine how insertCommand will be built dynamically:

  1. First of all, you need to create the command
  SqlCommand cmdInsert = new SqlCommand(myConnection);
  cmdInsert.CommandText = “name of my insert SP”;
  cmdInsert.CommandType = CommandType.StoredProcedure;
  //etc.

  2. Fill list of parameters of cmdInsert:
  You can use DeriveParameters method of OleDbCommandBuilder class. This method retrieves parameters information (from the stored procedure specified in the OleDbCommand) and populates the Parameters collection of the specified cmdInsert object.

  OleBbDCommandBuilder.DeriveParameters(cmd);

  
  More details here:
  http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx

  3. Now we need to specify how each parameter value will be filled. For that you’ll use OleDbParameter.SourceColumn property which indicates that parameter value will be taken from specified column of inserted row.

  //foreach parameter of my cmdInsert
  par.SourceColumn = “name of columns of dataset column”;

  More details here:
  http://msdn2.microsoft.com/en-us/library/bbw6zyha.aspx

  As you see, you still have some hard-code values to manage: like name of the stored procedure or source columns of parameters. In order to simplify this, you might establish few name conventions. E.g:
  - insert stored procedure is: tableName + “Insert”
  - name of parameters of insert stored procedures are the same as columns retrieved with Select stored procedure i.e.
  

  //foreach parameter of my cmdInsert
  par.SourceColumn = par.Name;

  
  Is it that you’re looking for

  
  
  

• Robert.Altland

  Exactly! That`s it, that is one solution. Just, I wanted to make software component that could be used in any situation, independent of the database (I just create dataset through the wizard, and then use one generic tableadapter that works with all datatables in dataset. Adapter creates statements dynamically based on datatable). That means avoiding stored procedures and implementing update, insert and delete statements inside my code. Your solution is OK because you retrieve dbtypes from stored procedure, but I had a problem to create dbtype from .NET type because datatable contains .NET types. I thought that I could just convert .Net type from DataColumn to OleDbType that is needed for OleDbCommandParameter, but I found that mapping between OleDbType and System.Type is not 1-1 (for example: String maps to char, nchar, varchar, nvarchar, longnvarchar...) so I solved a problem by extending DataColumnMapping for another coresponding field that contains DbType and now I have mapping for sourceColumnName, dataSetColumnName and columnDbType.

  Thanks. Your answer helped me a lot.

  
  

• Ninj

  That is not a problem. I want to read type from datatable columns (DataColumn property "DataType" ) and depending of that type to create OleDbCommand dinamically for DataAdapter so, I need to put OleDbType as a parameter of OleDbCommand constructor. I need to map System.Type to OleDbType, but I didn`t find appropriate convert method.

  
  

• TechJosh

  missa wrote:

  I can`t belive that it is not alredy written by Microsoft.

  
  
  

  Yes, indeed, Ms has provided data readers with methods like follows: Use reader.GetInt32, reader.GetBoolean, reader.GetDecimal, etc.

  
  
  
  

• Shady9399

  Why do you need this OleDbDataReader.Item and SqlDataReader.Item return an Object, just cast it to the native type:
  
  int count = (int)(dataReader["count"]);
  
  
  
  
• Converting System.Type to OleDbType