Data Table

[Arunabh Nag]

I heard somewhere that i can draw a datatable onto a local form, with the connection open....

How do i do this..??

[sknake]

Do you literally mean *draw* the DataTable as in render it like a control -- or do you mean load a table on a form while a DbConnection is open?

Here is an example of loading a DataTable on a form while having an OleDb connection open:

Imports System.IO
Imports System.Data.OleDb

Public Class frmExcel

	Private Shared Function GetExcelConnString(ByVal FileName As String, ByVal FirstRowContainsHeaders As Boolean)
		Dim sFirstRow As String
		If (FirstRowContainsHeaders) Then
			sFirstRow = "Yes"
		Else
			sFirstRow = "No"
		End If

		Dim ext As String = Path.GetExtension(FileName)
		If (String.Compare(ext, ".xls", True) = 0) Then
			Return String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=""Excel 8.0;HDR={1};""", _
				 FileName.Replace("'", "''"), _
				 sFirstRow)
		ElseIf (String.Compare(ext, ".xlsx", True) = 0) Then
			Return String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties=""Excel 12.0;HDR={1};""", _
				 FileName.Replace("'", "''"), _
					sFirstRow)
		End If
		Throw New Exception("Unknown File Type")
	End Function


	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim fileName As String = "C:\data\Spreadsheet.xls"
		Dim connStr As String = GetExcelConnString(fileName, True)

		Dim conn As New OleDbConnection(connStr)
		conn.Open()
		Dim cmd As New OleDbCommand("Select * From [Sheet1$]", conn)
		Dim dr As OleDbDataReader = cmd.ExecuteReader()
		Dim dt As New DataTable()
		dt.Load(dr)
		'The data table is loaded -- and the connection is still open at this point


		cmd.Dispose()
		conn.Close()
		conn.Dispose()

		DataGridView1.DataSource = dt
	End Sub
End Class