none
get the Dbtype from DataTable RRS feed

  • Question

  • Hello,
    I have a Datatable that is filled from a dataset

    Th pbs that i am trying to get the DBTYPE but not result to now

    the task is if the type was Date so the display format will be "dd-MMM-yy"
    if the type was Datetime so the display format will be "dd-MMM-yy HH:mm:ss"

    is there is any way to get the DBTYPE and not the Me.Datatabe.Columns(index).DataType.ToString??

    I need help urgent

    thank you very much for helpin
    Monday, October 26, 2009 5:47 PM

All replies

  • Hi...

    From the datatable we can defintely get the DataType.

    Use the DataSet.Tables("TableName").Columns("ColumnName").DataType.FullName

    Construct your statment like this..

    Select Case DataTable.Columns("ColName").DataType.FullName

    Case "System.Int32"

    Case "System.DateTime"

    End Select

    But you will have have a seperate data Type for Date and DateTime. You have to check in the Data whether it includes the Time, if so then you have to set the display format accordingly.

    Thanks.


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Tuesday, October 27, 2009 3:12 PM
  • Hi..

    first, thank you a lot for replying

    In fact, in your solution, I have to read the data an chek the time part for the datetime type.

    but my question was how to get the type to know wish format to give, I don't want to read the data.

    I am building a user datagridview control, and i don't want to test the data in it, I want to get the real type

    thit is why my question was how to get DBtype and not the DataType from datatable

    any one can help me plz
    it is realy urgent

    Tuesday, October 27, 2009 7:29 PM
  • Hi Wael,

     

    Do you mean you want to get the DBtype form database?

     

            cmd.CommandText = "SELECT     c.COLUMN_NAME, c.DATA_TYPE, s.length, c.IS_NULLABLE, c.COLUMN_DEFAULT " +

            " FROM         INFORMATION_SCHEMA.COLUMNS AS c INNER JOIN " +

            " sys.syscolumns AS s ON s.name = c.COLUMN_NAME " +

            "WHERE     (s.id = OBJECT_ID('" + tablename + "')) AND (c.TABLE_NAME = '" + tablename + "')"

     

    You can use this command to get all the columns' dbtype.

     

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 28, 2009 6:33 AM
  • in fact yes
    I am using dataset to get the info from my database,

    I don't know if there is a way to get the real database (instead of using another query to get the real type)


    for example in the datatable you can use Me.Datatabe.Columns(index).DataType.ToString
    but this return the system datatype.

    My probleme is if I am using Me.Datatabe.Columns(index).DataType.ToString and the type from the database is Date (SQL 2008) well "Me.Datatabe.Columns(index).DataType.ToString" return "DateTime" and not "Date" ...

    thank you for replyin

    I am truly need help on this pb..



    Friday, October 30, 2009 8:45 PM
  • I don't know how you generated the DataColumns in your DataTable, nor which version of .NET you're using, but the SqlDbType, as of .NET 3.5, now includes the new SQL Server 2008 datatypes of SqlDbType.Date and SqlDbType.Time (in addition to a few other date-related datatypes). See this link:

    http://msdn.microsoft.com/en-us/library/bb675168.aspx
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, October 31, 2009 10:06 PM
  • well,

    the datacolumn in my datatable are generated via the Dataadapter

    adapter.fill(ds)

    so the ds.tables(0) contain my datatable with diffrent column

    so I transfer my datatable to the datagridview control

    and I test each column to get the type, and here is my pb...
    Saturday, October 31, 2009 11:04 PM
  • Are you using the SqlDataAdapter or the more "generic" OleDbDataAdapter? You should be using SqlDataAdapter (and all the Sql specific data access classes). They all live in the System.Data.SqlClient namespace.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, October 31, 2009 11:49 PM
  • yes I am using the SqlDataAdapter and I can't get the DBtype
    Sunday, November 1, 2009 2:52 PM
  • Ah, I'm an idiot. I see the problem now. Even though SQL Server 2008 has this new Date datatype and even though the SqlDbType now supports the new Date datatype, there is NO Date datatype in .NET. The SqlDbType.Date would map to the usual System.DateTime datatype in your DataColumn. Hmmm, that's not good.

    Sorry for the misunderstanding.

    I don't think there's going to be any easy way to get the datatype directly from the DataSet. I think that the DataColumn.ExtendedProperties collection may be something that may be useful. Take a look at http://msdn.microsoft.com/en-us/library/system.data.datacolumn.extendedproperties.aspx

    It would mean more work either when the DataSet is filled from the database (to create an ExtendedProperty to indicate a Date column), or use a Typed DataSet, where you will already know which columns need to be Date and which need to be DateTime. IOW, your DataColumn.DataType will still be DateTime, but you will need to use an ExtendedProperty to indicate those that are supposed to be only of a Date datatype.

    Does this suggestion help?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Proposed as answer by Yichun_Feng Tuesday, November 3, 2009 4:19 AM
    Sunday, November 1, 2009 4:41 PM
  • Did you try to use DateTime.Now.ToShortDateString?
    John
    Monday, November 2, 2009 3:32 PM
  • ???
    How this will help to try datetime.now.toshortdatestring?
    Monday, November 2, 2009 6:52 PM
  • Are you looking to get DataType from Database Table or from .NET DataTable?
    If you are looking on getting DataType from Database here is a sample based on Northwind Database Order Table



        Private Sub LoadSchema_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadSchema_Button.Click
    
            Me.ListBox1.Items.Clear()
    
            Using Conn As New SqlConnection(My.Settings.MyConnection)
                Using cmd As New SqlCommand("Select * from Orders", Conn)
                    Conn.Open()
                    Using Reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                        For i As Integer = 0 To Reader.FieldCount - 1
                            Me.ListBox1.Items.Add(String.Format("{0}: {1}", Reader.GetName(i), Reader.GetDataTypeName(i)))
    
                        Next
                    End Using
                End Using
            End Using
        End Sub



    John
    • Proposed as answer by Codernater Tuesday, November 3, 2009 10:58 AM
    Monday, November 2, 2009 9:17 PM
  • I am looking to get the DBTYPE

    in your method I can't make a diffrence between the Date and DateTime with the SQL2008

    Tuesday, November 3, 2009 11:56 AM
  • What version of VS are you running?
    Because I tested and it's working. I'm running SQL Server 2K8 and VS2K8

    I used Northwind Database and I added a field to the Orders Table called FieldDateOnly with datatype Date (just to see if it will pick the Date datatype)
    Then I ran the code

       Private Sub LoadSchema_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadSchema_Button.Click
    
            Me.ListBox1.Items.Clear()
    
            Using Conn As New SqlConnection(My.Settings.MyConnection)
                Using cmd As New SqlCommand("Select * from Orders", Conn)
                    Conn.Open()
                    Using Reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                        For i As Integer = 0 To Reader.FieldCount - 1
                            Me.ListBox1.Items.Add(String.Format("{0}: {1}", Reader.GetName(i), Reader.GetDataTypeName(i)))
    
                        Next
                    End Using
                End Using
            End Using
        End Sub


    Below are the results in the Listbox and it should look like this once you run it:

    OrderID: int
    CustomerID: nchar
    EmployeeID: int
    OrderDate: datetime
    RequiredDate: datetime
    ShippedDate: datetime
    ShipVia: int
    Freight: money
    ShipName: nvarchar
    ShipAddress: nvarchar
    ShipCity: nvarchar
    shipRegion: nvarchar
    ShipPostalCode: nvarchar
    ShipCountry: nvarchar
    FieldDateOnly: date




    John
    Tuesday, November 3, 2009 2:48 PM
  • Hi john

    it works with the sqldatareader

    This is how I am trying to get the data and test the type

    Dim My_Table As New DataTable

            Dim My_Con As SqlConnection = New SqlConnection(Glbl_Conn_Config.My_Connection)
            Dim My_Command As SqlCommand = New SqlCommand("StPr_Select_All_Activated_Tbl_Employees", My_Con)
            Dim My_Adapter As New SqlDataAdapter(My_Command)
            Dim My_DataSet As New DataSet
            Try
                My_Command.CommandType = CommandType.StoredProcedure
                My_Adapter.Fill(My_DataSet)

            Catch ex As Exception
                MsgBox("An Error occurs please contact your software developper", MsgBoxStyle.Critical)
            Finally

            End Try


    testing the type:
             My_DataSet.Tables(0).columns(i).DataType.ToString
    Tuesday, November 3, 2009 6:14 PM
  • Have you looked into my suggestion for using the DataColumn.ExtendedProperties collection? The use of this would also be greatly simplified if you used Typed DataSets, but it's not necessary. Just a lot more work if you don't, because you'd probably want to use something along the lines of John's suggestion (using DataReader to find the "Date" columns) and set an ExtendedProperty after you fill your DataSet (using DataAdapter).
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, November 3, 2009 8:32 PM

  • I have 2 Solutions for you but I still prefer SqlDataReader (I changed it little bit so it will accomodate your development)

    1st Solution: Opening Connection Calling a stored procedure Using Sqldatareader to load the data into DataTable then Display the data into a DataGridView

            Using Conn As New SqlConnection(My.Settings.MyConnection)
                Using cmd As New SqlCommand("spS_Orders", Conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    Conn.Open()
                    Using Reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                        Dim SchemaTable As DataTable = Reader.GetSchemaTable()
                        Me.DataGridView1.DataSource = SchemaTable
                    End Using
                End Using
            End Using


    2nd Solution: Opening Connection Calling a stored procedure Using SqlDataAdapter use FillSchema method instead of Fill Method (FillSchema Method Loads the schema of your Table from the datasource into a datatable) Then Iterate thru the columns and the get the datatype and display it in a listbox

            Dim ds As New DataSet
            Dim dt As New DataTable("Orders")
            Using Conn As New SqlConnection(My.Settings.MyConnection)
                Using Adapter As New SqlDataAdapter("spS_Orders", Conn)
                    Adapter.SelectCommand.CommandType = CommandType.StoredProcedure
                    Adapter.FillSchema(ds, SchemaType.Mapped, dt.TableName.ToString())
                End Using
            End Using
    
            For Each dtColumn As DataColumn In ds.Tables("Orders").Columns
                Me.ListBox1.Items.Add(String.Format("{0}: {1} {2}", dtColumn.ColumnName, dtColumn.DataType, dtColumn.DataType.FullName))
            Next
    Bottom line SqlDataAdapter FillSchema doesn't provide what you're looking for it seems like it's converting The Date Datatype coming from the database to DateTime DataType 

    Let me know if you have any other questions




    John
    Tuesday, November 3, 2009 9:31 PM
  • In fact I am not using a typed dataset
    I am filling the dataset and Adding column into datagridview from the datatable (filled from the dataset)
    Wednesday, November 4, 2009 5:38 PM
  • yes this conversion confuse me
    and to change my code from dataset to datareader i have to do a lot of work
    beside I don't know if i can use sorting filtering and adding column from the binding source(since i have to use binding source to open the data to he datagridview)
    Wednesday, November 4, 2009 5:41 PM
  • What I gave you (1st Solution) is not only datareader you are taking advantage of the datareader to load the schema into a datatable. This should not affect your development

    Here is the code again, I made minor adjustment to add your Schema DataTable to your dataset because for some reason you need the Schema Table in a DataSet.

        Private Sub ToolStripButton_LoadSchema_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton_LoadSchema.Click
            Dim ds As New DataSet 'You can reference to your Public Dataset
            Dim SchemaTable As New DataTable 'Your Schema Table
    
            Using Conn As New SqlConnection(My.Settings.MyConnection)
                'Write your SQL Command
                Using cmd As New SqlCommand("spS_Orders", Conn)
                    'Select the Type of your Command
                    cmd.CommandType = CommandType.StoredProcedure
                    Conn.Open()
                    'Define your SqlDataReader Command
                    Using Reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                        'Fill your SchemaTable DataTable with the Reader Records
                        SchemaTable = Reader.GetSchemaTable()
                        'Add your SchemaTable DataTable to your DataSet
                        ds.Tables.Add(SchemaTable)
                    End Using
                End Using
            End Using
            'Bind your SchemaTable to a DataGridView so you can see the results
            Me.DataGridView1.DataSource = SchemaTable
    
        End Sub

    John
    Wednesday, November 4, 2009 7:02 PM
  • There's an easier way to do this, assuming it also supports the new "date" sql type, which it should. And that is to use the GetSchema method of the SqlConnection.  And, as I have said twice already, using the ExtendedProperties collection of DataTables.

    I can't test it using the new "date" sql type because I don't have SQL 2008 currently installed. But all the rest of the code I will show below works fine.
    // this shows how to get schema for only one table, but if your DataSet has more, you can add more
    // this is only to show you the concepts
    SqlConnection oConn = new SqlConnection(this.TestConnection);
    oConn.Open();
    DataTable dtColumns = oConn.GetSchema("Columns", new string[] { null, null, "customer" });
    oConn.Close();
     
    // now get your DataSet
    DataSet dsCustomer = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter("select * from customer", oConn);
    da.Fill(dsCustomer);
     
    // Now set the ExtendedProperties
    for (int i = 0; i < dtColumns.Rows.Count; i++)
    {
        if (dtColumns.Rows[i]["data_type"].ToString() == "date")
        {
            dsCustomer.Tables[0].ExtendedProperties.Add(dtColumns.Rows[i]["column_name"], "date");
        }
    }
     

    Now, all that's left to do is use this information when you are setting your DataGridView columns.

    // when setting your grid columns and you need to know the datatype, do this
    foreach (DataColumn col in dsCustomer.Tables[0].Columns)
    {
        if (col.DataType.Name == "DateTime" && ds.Tables[0].ExtendedProperties.Contains(col.ColumnName))
        {
            // then you'll know this column is actually a "date" sql type.
        }
    }
     



    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Edited by BonnieBMVP Saturday, November 7, 2009 12:40 AM fixed typo: changed if (dtColumns.Rows[i]["data_type"].ToString() == "datetime") to be: if (dtColumns.Rows[i]["data_type"].ToString() == "date")
    Thursday, November 5, 2009 12:28 AM
  • Hi Bonnie,
    I tried your code and it works, it pulls all the tables in the connection very similar to the code I provided Wael, I know you've asked Wael couple times already to try the extended properties, I tried that it has good properties but not what we are looking for.

    I just don't understand why this post is still going and open because the solution was provided from 3 different prespectives!!!!

    Here is Bonnie's Code in VB.NET

        Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
            Dim ds As New DataSet
            Using connection As New SqlConnection(My.Settings.MyConnection)
                'Connect to the database then retrieve the schema information.
                connection.Open()
                Dim table As DataTable = connection.GetSchema("Columns")
                ds.Tables.Add(table)
                Me.DataGridView1.DataSource = table
            End Using
        End Sub




    John
    Thursday, November 5, 2009 3:10 PM
  • Thanks for converting the code to VB, John. I didn't have time to mess with doing that.

    I still think that Wael needs to look at the ExtendedProperties. His first post only said he wanted to know the Sql type of a DataColumn. But, in a subsequent post, he said he needed that info because he was designing a DataGridView class and needs to be able to setup columns in the grid based on the datatype of DataColumns in a DataTable that is being bound to the grid. That's a perfect use for the ExtendedProperties, as I showed in my code snippet. My opinion only, I suppose, but I think it's valid. =0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, November 5, 2009 5:36 PM
  • It look like each one of us talk a different language...

    My request is simple

    this is my Function
     Public Function Fct_Select_All_Activated() As DataTable
            Dim My_Table As New DataTable
           Dim My_Con As SqlConnection = New SqlConnection(Glbl_Conn_Config.My_Connection)
           Dim My_Command As SqlCommand = New SqlCommand("StPr_Select_All_Activated_Tbl_Employees", My_Con)
            Dim My_Adapter As New SqlDataAdapter(My_Command)
           Dim My_DataSet As New DataSet
            Try
                My_Command.CommandType = CommandType.StoredProcedure
                My_Adapter.Fill(My_DataSet)
            Catch ex As Exception
                MsgBox("An Error occurs please contact your software developper", MsgBoxStyle.Critical)
    Finally End Try Return My_DataSet.Tables(0)     End Function

    from my datatable returned by this function can I:
    get the dbtype for each column ?

    the way I am using to Fill the datagridview Class is:
    dim index as integer = 0
    dim Column_Name as string
    dim Column_Caption as string
    While index < Me.Data.Columns.Count

    Column_Name = Me.Data.Columns(index).ColumnName
    Column_Caption = Me.Data.Columns(index).Caption
    TextBoxColumn = New DataGridViewTextBoxColumn
    TextBoxColumn.DataPropertyName = Column_Name
    TextBoxColumn.Name = Column_Name
    TextBoxColumn.HeaderText = Column_Caption
    TextBoxColumn.Width = 130
    Me.Columns.Add(TextBoxColumn)
    Select Me.Data.Columns(index).DataType.ToString
    Case "System.DateTime"
    Me.Columns(Column_Name).DefaultCellStyle.Format = "dd-MMM-yy HH:mm:ss"
    case "Date"
    Me.Columns(Column_Name).DefaultCellStyle.Format = "dd-MMM-yy"

     End Select
    index = index + 1
    End While
    So the pb is in

    Select Me.Data.Columns(index).DataType.ToString
    
    
    
    
    
    Cause I can't have

      case "Date"
    
    
    
    
    
    
    
    
    
    
    any suggestion?

    thank you all in advance.




    Thursday, November 5, 2009 6:28 PM
  • Just for the record, we've been giving pieces of the puzzle and all you have to do is putting the pieces together to get to your objective.
    After seeing your code I couldn't help but writing the entire solution for you :--)

    Go To Northwind Database Orders Table and add a column with Date DataType and Update that column with Dates Data

    To run this sample create a new Windows Form keep the Name Form1 >>> Drag and Drop DataGridView to the Designer Keep the Name DataGridView1
    Double Click on My Project from the Solution Explorer Go To Settings Tab, create new settings Name your Settings MyConnection under the Name Field, drop down the list for the Type and pick Connection last option before Browse, then under the Value Field click the Ellipsis Button and establish connection with Northwind Database.

    Then Copy the code provided with this post and paste it into Form1.vb then Run the project


    #Region "Visual Studio Namespace"
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
    Imports System.IO
    #End Region
    
    Public Class Form1
        Public ds As DataSet
        Public bsOrders As BindingSource
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            ds = New DataSet
    
            'Get Orders Table Schema 
            Dim cmdOrdersSchema As SqlCommand = New SqlCommand("spS_Orders")
            GetDataTableSchema("OrdersSchema", cmdOrdersSchema)
    
            'Get Orders Table
            Dim cmdOrders As SqlCommand = New SqlCommand("spS_Orders")
            GetDataTable("Orders", cmdOrders)
    
            'Create a binding source
            bsOrders = New BindingSource
            With bsOrders
                .DataSource = ds
                .DataMember = ds.Tables("Orders").TableName.ToString()
            End With
    
            FillDataGridView()
    
        End Sub
    
        Public Function GetDataTable(ByVal dtName As String, ByVal Cmd As SqlCommand) As DataTable
            Dim dt As New DataTable
            'dt.TableName = dtName
            Using Conn As New SqlConnection(My.Settings.MyConnection)
                Cmd.Connection = Conn
                Cmd.CommandType = CommandType.StoredProcedure
                Conn.Open()
                Using Adapter As New SqlDataAdapter(Cmd)
                    Adapter.Fill(ds, dtName)
                End Using
            End Using
            Return dt
        End Function
    
        Public Function GetDataTableSchema(ByVal dtName As String, ByVal Cmd As SqlCommand) As DataTable
            Dim dt As New DataTable
            Using Conn As New SqlConnection(My.Settings.MyConnection)
                Cmd.Connection = Conn
                Cmd.CommandType = CommandType.StoredProcedure
                Conn.Open()
                Using Reader As SqlDataReader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                    dt = Reader.GetSchemaTable()
                    dt.TableName = dtName
                    ds.Tables.Add(dt)
                End Using
            End Using
            Return dt
        End Function
    
        Private Sub FillDataGridView()
            Try
                Dim dtOrders As DataTable = ds.Tables("Orders")
                Dim dtSchema As DataTable = ds.Tables("OrdersSchema")
                Dim dgv As DataGridView = Me.DataGridView1
    
                'Assign PrimaryKey to dtSchema DataTable
                dtSchema.Constraints.Add("pkColumnName", dtSchema.Columns("ColumnName"), True)
    
                Dim TxtBoxCol As DataGridViewTextBoxColumn
    
                With Me.DataGridView1
                    .AutoGenerateColumns = False
                    .Columns.Clear()
                    .DataSource = bsOrders 'Binding Source is used for Filtering
                End With
    
                Dim i As Integer = 0
                While i < dtOrders.Columns.Count
                    TxtBoxCol = New DataGridViewTextBoxColumn
                    With TxtBoxCol
                        .DataPropertyName = dtOrders.Columns(i).ColumnName.ToString()
                        .Name = dtOrders.Columns(i).ColumnName.ToString()
                        .HeaderText = dtOrders.Columns(i).ColumnName.ToString()
                        dgv.Columns.Add(TxtBoxCol)
                        Dim dtRow As DataRow = dtSchema.Rows.Find(dtOrders.Columns(i).ColumnName.ToString())
                        Dim dbType As String = CType(dtRow("DataTypeName"), String)
                        Select Case dbType
                            Case Is = "datetime"
                                .DefaultCellStyle.Format = "dddd, MMMM dd, yyyy hh:mm tt"
                            Case Is = "date"
                                .DefaultCellStyle.Format = "dddd, MMMM dd, yyyy"
                            Case Else
                                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                        End Select
                        .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                    End With
                    i += 1
                End While
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
    End Class




    John
    Thursday, November 5, 2009 9:40 PM
  • Arrrggghhhh!!! And my code with the ExtendedProperties would have worked too ... I'm sorry it was in C#, but it's easy enough to convert to VB.

    I think both John and I have been giving you the answer you needed all along ... you have just not put two and two together.

    The code I posted had everything you needed, except actually setting up the DataGridView columns, but since you said you were writing a custom grid class, I figured you could figure out what you needed to do from what I posted.

    The code that John just posted is not assuming a custom grid class, but will work just fine with the standard DataGridView. John's code will also work if you incorporate the ExtendedProperties that I described instead of loading the schema in a separate DataTable as John has done.

    In either case, you hopefully now understand what we have been trying to explain to you all along.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, November 6, 2009 12:03 AM
  • // Now set the ExtendedProperties
    for (int i = 0; i < dtColumns.Rows.Count; i++)
    {
        if (dtColumns.Rows[i]["data_type"].ToString() == "datetime")
        {
            dsCustomer.Tables[0].ExtendedProperties.Add(dtColumns.Rows[i]["column_name"], "date");
        }
    }

    No it will not
    because you are converting all the datetime columns into date
    Friday, November 6, 2009 11:55 AM
  • I'm sorry Wael, that was a typo on my part. Since I don't have SQL 2008, I tested the code using "datetime" and I forgot to change it to "date" when I posted it. That "if" statement should look like this:

        if (dtColumns.Rows[i]["data_type"].ToString() == "date")

    And, for clarification, that code doesn't "convert" anything. It is simply used to "mark" the columns that should be  considered "date" instead of "datetime".

    I'm going to go back to my original code and fix the typo, in case anyone else looks at this example in the future.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, November 7, 2009 12:35 AM
  • SELECT data_type FROM information_schema.columns WHERE table_schema = 'dbo' AND table_name = 'table_name' AND column_name = 'col' ;
    Thursday, June 30, 2011 8:32 AM
  • This was a 2-year-old thread! And it was pretty much answered already. Plus, I think using the Connection.GetSchema() method is a lot cleaner than what you've just posted ... just my opinion.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, June 30, 2011 3:56 PM