converting datatable/datagrid view to html in vb.net
Following code snippet can be used for finding html from a datatable in vb.net. this code is very useful in many situation such as if you need to send a datatable through email then we can find html of that datatable and can send html body.
Do the imports of
Imports System.Text
variables
declaration
Public DatabasePath As String
1.this is my function where i populate the datatable
Public Function populateGrid(ByVal sql As String, ByVal dg As DataGridView) As DataTable
Dim strCnxn As String
strCnxn = getdbpath()
Dim xDataSet As DataSet = New DataSet
Dim sConnect As String = strCnxn
Dim xSqlConnection As SqlConnection = New SqlConnection(sConnect)
Try
Dim cmdString As String = "select * from alert_data_master (nolock)"
Dim xSqlCommand As New SqlCommand(cmdString, xSqlConnection)
xSqlCommand = New SqlCommand("alert_popup_qry", xSqlConnection)
xSqlCommand.CommandType = CommandType.StoredProcedure
xSqlCommand.Parameters.Add(New SqlParameter("@flg", OleDbType.BSTR))
xSqlCommand.Parameters("@flg").Value = ""
xSqlConnection.Open()
Dim xSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(xSqlCommand)
xSqlDataAdapter.Fill(xDataSet)
Dim dt As New DataTable("Table1")
xDataSet.Tables.Add(dt)
dg.DataSource = xDataSet.Tables(0)
populateGrid = xDataSet.Tables(0)
xSqlDataAdapter.Dispose()
xSqlConnection.Close()
Catch xError As Exception
' Handle Error here!
If Err.Number = 5 Then
MsgBox(Err.Description)
End
Else
MsgBox(Err.Description)
End If
Throw
Finally
If Not (xSqlConnection Is Nothing) Then xSqlConnection.Close()
xSqlConnection.Dispose()
xSqlConnection.Close()
End Try
End Function
2. Here is my database connectivity. Here i use a registry entry to get the connection string.
Public Function getdbpath() As String
'My.Computer.Registry.CurrentUser.CreateSubKey("DB_Path")
'My.Computer.Registry.SetValue("HKEY_CURRENT_USER\DB_Path", "DbPath", "Persist Security Info=False;User ID=sa;pwd=sqlpassw0rd;Initial Catalog=AlertMgmt;Data Source=ITTEST\ITTEST2008")
Dim readValue As String
readValue = My.Computer.Registry.GetValue("HKEY_CURRENT_USER\DB_Path", "DbPath", Nothing)
'MsgBox("The value is: " & readValue)
DatabasePath = readValue
getdbpath = DatabasePath
Return getdbpath
End Function
3. Here is the function to convert the datatable to the html format.
Public Shared Function ConvertToHtmlFile(ByVal myTable As DataTable) As String
Dim myHtmlFile As String = ""
Dim myBuilder As New StringBuilder
If myTable Is Nothing Then
Throw New System.ArgumentNullException("myTable")
Else
'Continue. }
'Get a worker object. StringBuilder myBuilder = new StringBuilder();
'Open tags and write the top portion.
myBuilder.Append("<html xmlns='http://www.w3.org/1999/xhtml'>")
myBuilder.Append("<head>")
myBuilder.Append("<title>")
myBuilder.Append("Page-")
myBuilder.Append(Guid.NewGuid().ToString())
myBuilder.Append("</title>")
myBuilder.Append("</head>")
myBuilder.Append("<body>")
myBuilder.Append("<br /> <table border='1px' cellpadding='5' cellspacing='0' ")
myBuilder.Append("style='border: solid 1px Silver; font-size: x-small;'>")
'Add the headings row.
myBuilder.Append("<br /><tr align='left' valign='top'>")
For Each myColumn As DataColumn In myTable.Columns
myBuilder.Append("<br /><td align='left' valign='top' style='border: solid 1px blue;'>")
myBuilder.Append(myColumn.ColumnName)
myBuilder.Append("</td><p>")
Next
myBuilder.Append("</tr><p>")
'Add the data rows. foreach (DataRow myRow in myTable.Rows) { myBuilder.Append("<br />
'<tr align='left' valign='top'>");
For Each myRow As DataRow In myTable.Rows
myBuilder.Append("<br /><tr align='left' valign='top'>")
For Each myColumn As DataColumn In myTable.Columns
myBuilder.Append("<br /><td align='left' valign='top' style='border: solid 1px blue;'>")
myBuilder.Append(myRow(myColumn.ColumnName).ToString())
myBuilder.Append("</td><p>")
Next
Next
myBuilder.Append("</tr><p>")
End If
'Close tags.
myBuilder.Append("</table><p>")
myBuilder.Append("</body>")
myBuilder.Append("</html>")
'Get the string for return. myHtmlFile = myBuilder.ToString();
myHtmlFile = myBuilder.ToString()
MsgBox(myHtmlFile)
Return myHtmlFile
End Function
source: www.authorcode.com/ and i have modified in my own way.
