get the Dbtype from DataTable

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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..

Answer 5

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

Answer 6

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...

Answer 7

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

Answer 8

yes I am using the SqlDataAdapter and I can't get the DBtype

Answer 9

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

Answer 10

Did you try to use DateTime.Now.ToShortDateString?

---

John

Answer 11

???
How this will help to try datetime.now.toshortdatestring?

Answer 12

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

Answer 13

I am looking to get the DBTYPE

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

Answer 14

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<br/><br/><br/>

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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)

Answer 19

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)

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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)<br/>

        Finally
        End Try
        Return My_DataSet.Tables(0)
    End Function<br/>

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<br/>




dim Column_Name as string<br/>


dim Column_Caption as string<br/>


While index < Me.Data.Columns.Count<br/>




<br/>




            Column_Name = Me.Data.Columns(index).ColumnName<br/>


            Column_Caption = Me.Data.Columns(index).Caption<br/>


            TextBoxColumn = New DataGridViewTextBoxColumn<br/>


            TextBoxColumn.DataPropertyName = Column_Name<br/>


            TextBoxColumn.Name = Column_Name<br/>


            TextBoxColumn.HeaderText = Column_Caption<br/>


            TextBoxColumn.Width = 130<br/>


            Me.Columns.Add(TextBoxColumn)<br/>


            <strong>Select Me.Data.Columns(index).DataType.ToString</strong>




<br/>




                   Case "System.DateTime"<br/>


                   Me.Columns(Column_Name).DefaultCellStyle.Format = "dd-MMM-yy HH:mm:ss"<br/>




<strong>                   case "Date"<br/>


                   Me.Columns(Column_Name).DefaultCellStyle.Format = "dd-MMM-yy"</strong>




<br/>




           End Select<br/>


            index = index + 1<br/>


        End While<br/>

So the pb is in

<strong>Select Me.Data.Columns(index).DataType.ToString</strong>

Cause I can't have

<strong><strong>  case "Date"</strong>




</strong>

any suggestion?

thank you all in advance.

Answer 25

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

Answer 26

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

Answer 27

// 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

Answer 28

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