none
How to get datatypes from GetOleDbSchemaTable? RRS feed

  • Question

  • When I run GetOleDbSchemaTable against Sql Server and my tables has a CHAR, or CHAR(10), or VARCHAR, or VARCHAR(10), as far as I can tell, when I get the Data_Type, they are all 129 or CHAR.

    So how can I tell if the field is varchar or not?  I want to take an action based on the datatype.  Like convert certain columns from CHAR(10) to VARCHAR(10) if it isn't already VARCHAR(10)?

    Friday, October 7, 2016 8:59 PM

Answers

  • Hi Mark Tiede,

    We could the following method to retrieve datatype.

     OleDbConnection cn = new OleDbConnection();
                DataSet tablesFromDB = new DataSet();
                DataTable schemaTbl;
                //Connect to the Northwind database in SQL Server.
                //Be sure to use an account that has permission to retrieve table schema.
                cn.ConnectionString = "Provider=SQLOLEDB;Data Source=10.168.172.127;User ID=sa;Password =sa; Initial Catalog = SQLDemo";
                cn.Open();
                object[] objArrRestrict = new object[] { null, null, null, "TABLE" };
    
                // Get the table names from the database we're connected to
                schemaTbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);
    
                // Not sure if this is correct syntax...fix it if it isn't :)
                string commandText = @"SELECT * FROM {0}";
    
                // Get each table name that we just found and get the schema for that table.
                foreach (DataRow row in schemaTbl.Rows)
                {
                    DataTable dt = new DataTable();
                    dt.TableName = row["TABLE_NAME"].ToString();
                    OleDbCommand command = new OleDbCommand(String.Format(commandText, row["TABLE_NAME"] as String), cn);
                    dt.Load(command.ExecuteReader(CommandBehavior.SchemaOnly));
                    tablesFromDB.Tables.Add(dt);
                }
    
                foreach (DataTable dt in tablesFromDB.Tables)
                {
                    Console.WriteLine(dt.TableName + "-----------");
                    foreach (DataColumn dc in dt.Columns)
                    {
                        Console.WriteLine("{0} -- {1}", dc.ColumnName, dc.DataType);
                        // Do something with the column names and types here
                        // dc.ColumnName is the column name for the current table
                        // dc.DataType.ToString() is the name of the type of data in the column
                    }
                }
    
                cn.Close();

    But from the following link about SQL Server Data Type Mappings, We know that CHAR(10) and VARCHAR (10) all map the same .NET Framework type (String). So that we couldn't distinguish CHAR(10) and VARCHAR (10) by using c#.

    https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mark Tiede Monday, October 17, 2016 2:45 PM
    Monday, October 10, 2016 5:28 AM
    Moderator
  • Here is what I ended up doing and the first answer might have worked, but the ending sentence confused me as to whether it would work or not.

       method Database.GetColumns( connectionString: String; tablename : String ): List<Column>;
       
          var
             isnullable : Boolean;
    
          begin
          result := nil;
    
          using connection := new OleDbConnection( connectionString ) do begin
             connection.Open;
    
             var command := new OleDbCommand( 'select * from ' + tablename, connection );
             var rdr := command.ExecuteReader( CommandBehavior.SchemaOnly);
             try
    
                result := new List<Column>;
    
                var dt := rdr.GetSchemaTable;
    
                for I : Integer := 0 to rdr.FieldCount -1 do begin
                   var aColumn := new Column;
                   aColumn.DataType := rdr.GetDataTypeName(I).Tostring;
    
    
                   var x : DataRow := dt.Rows[I];
    
                   aColumn.Name     := x.ItemArray[0].ToString;
    
                   aColumn.Length   := Convert.ToDecimal( x.ItemArray[2] );
    
                   aColumn.Precision := Int16( x.ItemArray[3] );
                   aColumn.Scale     := Int16( x.ItemArray[4] );
    
                   if Boolean.TryParse( x.ItemArray[8].ToString, out isnullable ) 
                      then aColumn.IsNullable := isnullable
                      else aColumn.IsNullable := false;
    
    
                   result.Add( aColumn );
                   end;
             finally 
                rdr.Close;
                end;
    
             end;
          end;
    
    The key is that the datareader can call GetSchemaTable and THAT one gives more information and allows me to distinguish between CHAR and VARCHAR.


    • Marked as answer by Mark Tiede Monday, October 17, 2016 2:40 PM
    • Edited by Mark Tiede Monday, October 17, 2016 2:45 PM
    Monday, October 17, 2016 2:40 PM

All replies

  • Hi Mark Tiede,

    We could the following method to retrieve datatype.

     OleDbConnection cn = new OleDbConnection();
                DataSet tablesFromDB = new DataSet();
                DataTable schemaTbl;
                //Connect to the Northwind database in SQL Server.
                //Be sure to use an account that has permission to retrieve table schema.
                cn.ConnectionString = "Provider=SQLOLEDB;Data Source=10.168.172.127;User ID=sa;Password =sa; Initial Catalog = SQLDemo";
                cn.Open();
                object[] objArrRestrict = new object[] { null, null, null, "TABLE" };
    
                // Get the table names from the database we're connected to
                schemaTbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);
    
                // Not sure if this is correct syntax...fix it if it isn't :)
                string commandText = @"SELECT * FROM {0}";
    
                // Get each table name that we just found and get the schema for that table.
                foreach (DataRow row in schemaTbl.Rows)
                {
                    DataTable dt = new DataTable();
                    dt.TableName = row["TABLE_NAME"].ToString();
                    OleDbCommand command = new OleDbCommand(String.Format(commandText, row["TABLE_NAME"] as String), cn);
                    dt.Load(command.ExecuteReader(CommandBehavior.SchemaOnly));
                    tablesFromDB.Tables.Add(dt);
                }
    
                foreach (DataTable dt in tablesFromDB.Tables)
                {
                    Console.WriteLine(dt.TableName + "-----------");
                    foreach (DataColumn dc in dt.Columns)
                    {
                        Console.WriteLine("{0} -- {1}", dc.ColumnName, dc.DataType);
                        // Do something with the column names and types here
                        // dc.ColumnName is the column name for the current table
                        // dc.DataType.ToString() is the name of the type of data in the column
                    }
                }
    
                cn.Close();

    But from the following link about SQL Server Data Type Mappings, We know that CHAR(10) and VARCHAR (10) all map the same .NET Framework type (String). So that we couldn't distinguish CHAR(10) and VARCHAR (10) by using c#.

    https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mark Tiede Monday, October 17, 2016 2:45 PM
    Monday, October 10, 2016 5:28 AM
    Moderator
  • I don't use C# and I'm not sure why you posted the code as I'm already getting the information. The question was only, "Is there a way to distinguish CHAR and VARCHAR?"  The end of your post seems to say that it can't be done.  So I suppose I'll have to move away from Oledb and use the database specific drivers.
    Monday, October 10, 2016 12:49 PM
  • So how can I tell if the field is varchar or not?  I want to take an action based on the datatype.  Like convert certain columns from CHAR(10) to VARCHAR(10) if it isn't already VARCHAR(10)?

    So why is it that you can't use T-SQL directly and get table schemas from the database?

    Monday, October 10, 2016 3:56 PM
  • I don't think that will work since I have to support multiple back-end databases and they will have different schema information in different places.

    I think I have what I need now.  At worst, I can get the list of tables from the GetOledbSchemaTable.  Then I can use an OledbReader to get the name of the type and the OledbReader's GetSchema to get the other information about maximum length, precision and scale.

    And I think it will work the same for multiple back-end database types and hide the specifics behind Oledb.

    Monday, October 10, 2016 4:02 PM
  • Hi Mark,

    If your issue is solved please Mark as answer or Vote as helpful post to the appropriate answer so that it will help members if they faces similar issue.


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Monday, October 17, 2016 2:31 PM
    Moderator
  • Here is what I ended up doing and the first answer might have worked, but the ending sentence confused me as to whether it would work or not.

       method Database.GetColumns( connectionString: String; tablename : String ): List<Column>;
       
          var
             isnullable : Boolean;
    
          begin
          result := nil;
    
          using connection := new OleDbConnection( connectionString ) do begin
             connection.Open;
    
             var command := new OleDbCommand( 'select * from ' + tablename, connection );
             var rdr := command.ExecuteReader( CommandBehavior.SchemaOnly);
             try
    
                result := new List<Column>;
    
                var dt := rdr.GetSchemaTable;
    
                for I : Integer := 0 to rdr.FieldCount -1 do begin
                   var aColumn := new Column;
                   aColumn.DataType := rdr.GetDataTypeName(I).Tostring;
    
    
                   var x : DataRow := dt.Rows[I];
    
                   aColumn.Name     := x.ItemArray[0].ToString;
    
                   aColumn.Length   := Convert.ToDecimal( x.ItemArray[2] );
    
                   aColumn.Precision := Int16( x.ItemArray[3] );
                   aColumn.Scale     := Int16( x.ItemArray[4] );
    
                   if Boolean.TryParse( x.ItemArray[8].ToString, out isnullable ) 
                      then aColumn.IsNullable := isnullable
                      else aColumn.IsNullable := false;
    
    
                   result.Add( aColumn );
                   end;
             finally 
                rdr.Close;
                end;
    
             end;
          end;
    
    The key is that the datareader can call GetSchemaTable and THAT one gives more information and allows me to distinguish between CHAR and VARCHAR.


    • Marked as answer by Mark Tiede Monday, October 17, 2016 2:40 PM
    • Edited by Mark Tiede Monday, October 17, 2016 2:45 PM
    Monday, October 17, 2016 2:40 PM