Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Collections.Specialized
Imports System.Data.SqlClient
Public Partial Class _Default
Inherits System.Web.UI.Page
#Region "Data members"
Public Shared Table As New DataTable()
Private ParameterArray As New ArrayList()
#End Region
#Region "Events Handlers"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If IsPostBack AndAlso CBool(Session("IsConnectionInfoSet")) = True Then
CreateTemplatedGridView()
End If
End Sub
Public Sub TableGridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
TableGridView.EditIndex = e.NewEditIndex
TableGridView.DataBind()
Session("SelecetdRowIndex") = e.NewEditIndex
End Sub
Public Sub TableGridView_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
TableGridView.EditIndex = -1
TableGridView.DataBind()
Session("SelecetdRowIndex") = -1
End Sub
Protected Sub TableGridView_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
Dim ServerName As String = DirectCast(Session("Server"), String)
Dim UserName As String = DirectCast(Session("UserName"), String)
Dim Password As String = DirectCast(Session("Password"), String)
Dim DatabaseName As String = DirectCast(Session("DatabaseSelected"), String)
Dim Connection As SqlConnection = New System.Data.SqlClient.SqlConnection(((("Data Source=" & ServerName & ";Initial Catalog=") + DatabaseName & ";User ID=") + UserName & ";Password=") + Password & ";Integrated Security=True; Connect Timeout=120")
Dim Query As String = GenerateDeleteQuery(e.RowIndex)
Dim Command As SqlCommand = New System.Data.SqlClient.SqlCommand(Query, Connection)
Try
If Connection.State = ConnectionState.Closed Then
Connection.Open()
End If
Command.ExecuteNonQuery()
Catch se As SqlException
msg_lbl.Text = se.ToString()
MsgPanel.Visible = True
Connection.Close()
End Try
CreateTemplatedGridView()
End Sub
Public Sub TableGridView_RowUpdating(ByVal sender As [Object], ByVal e As GridViewUpdateEventArgs)
Dim ServerName As String = DirectCast(Session("Server"), String)
Dim UserName As String = DirectCast(Session("UserName"), String)
Dim Password As String = DirectCast(Session("Password"), String)
Dim DatabaseName As String = DirectCast(Session("DatabaseSelected"), String)
Dim Connection As New SqlConnection(((("Data Source=" & ServerName & ";Initial Catalog=") + DatabaseName & ";User ID=") + UserName & ";Password=") + Password & ";Integrated Security=True; Connect Timeout=120")
Dim row As GridViewRow = TableGridView.Rows(e.RowIndex)
For i As Integer = 0 To Table.Columns.Count - 1
Dim field_value As String = DirectCast(row.FindControl(Table.Columns(i).ColumnName), TextBox).Text
ParameterArray.Add(field_value)
Next
Dim Query As String = ""
If CInt(Session("InsertFlag")) = 1 Then
Query = GenerateInsertQuery()
Else
Query = GenerateUpdateQuery()
End If
Dim Command As SqlCommand = New System.Data.SqlClient.SqlCommand(Query, Connection)
Try
If Connection.State = ConnectionState.Closed Then
Connection.Open()
End If
Command.ExecuteNonQuery()
Session("InsertFlag") = If(CInt(Session("InsertFlag")) = 1, 0, 1)
Catch se As SqlException
msg_lbl.Text = se.ToString()
MsgPanel.Visible = True
Connection.Close()
End Try
TableGridView.EditIndex = -1
CreateTemplatedGridView()
End Sub
Protected Sub TableGridView_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
'CreateTemplatedGridView();
TableGridView.PageIndex = e.NewPageIndex
TableGridView.DataBind()
End Sub
Protected Sub msg_button_Click(ByVal sender As Object, ByVal e As EventArgs)
MsgPanel.Visible = False
End Sub
Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As EventArgs)
'msg_lbl.Text = "";
Session("Server") = server_tb.Text
Session("UserName") = user_tb.Text
Session("Password") = pswrd_tb.Text
Session("DatabaseSelected") = database_tb.Text
Session("TableSelected") = table_tb.Text
Session("IsConnectionInfoSet") = True
CreateTemplatedGridView()
End Sub
#End Region
#Region "Methods"
Private Sub PopulateDataTable()
Table = New DataTable()
TableGridView.Columns.Clear()
Dim ServerName As String = DirectCast(Session("Server"), String)
Dim UserName As String = DirectCast(Session("UserName"), String)
Dim Password As String = DirectCast(Session("Password"), String)
Dim DatabaseName As String = DirectCast(Session("DatabaseSelected"), String)
Dim TableName As String = DirectCast(Session("TableSelected"), String)
Dim Connection As SqlConnection = New System.Data.SqlClient.SqlConnection(((("Data Source=" & ServerName & ";Initial Catalog=") + DatabaseName & ";User ID=") + UserName & ";Password=") + Password & ";Integrated Security=True; Connect Timeout=120;")
Dim adapter As New SqlDataAdapter("Select * from " & TableName, Connection)
Try
adapter.Fill(Table)
Catch ex As Exception
msg_lbl.Text = ex.ToString()
MsgPanel.Visible = True
Connection.Close()
End Try
End Sub
Private Sub CreateTemplatedGridView()
' fill the table which is to bound to the GridView
PopulateDataTable()
' add templated fields to the GridView
Dim BtnTmpField As New TemplateField()
BtnTmpField.ItemTemplate = New DynamicallyTemplatedGridViewHandler(ListItemType.Item, "...", "Command")
BtnTmpField.HeaderTemplate = New DynamicallyTemplatedGridViewHandler(ListItemType.Header, "...", "Command")
BtnTmpField.EditItemTemplate = New DynamicallyTemplatedGridViewHandler(ListItemType.EditItem, "...", "Command")
TableGridView.Columns.Add(BtnTmpField)
For i As Integer = 0 To Table.Columns.Count - 1
Dim ItemTmpField As New TemplateField()
' create HeaderTemplate
ItemTmpField.HeaderTemplate = New DynamicallyTemplatedGridViewHandler(ListItemType.Header, Table.Columns(i).ColumnName, Table.Columns(i).DataType.Name)
' create ItemTemplate
ItemTmpField.ItemTemplate = New DynamicallyTemplatedGridViewHandler(ListItemType.Item, Table.Columns(i).ColumnName, Table.Columns(i).DataType.Name)
'create EditItemTemplate
ItemTmpField.EditItemTemplate = New DynamicallyTemplatedGridViewHandler(ListItemType.EditItem, Table.Columns(i).ColumnName, Table.Columns(i).DataType.Name)
' then add to the GridView
TableGridView.Columns.Add(ItemTmpField)
Next
' bind and display the data
TableGridView.DataSource = Table
TableGridView.DataBind()
End Sub
Private Function GenerateUpdateQuery() As String
Dim i As Integer = 0
Dim tempstr As String = ""
Dim temp_index As Integer = -1
Dim TableName As String = DirectCast(Session("TableSelected"), String)
Dim Query As String = ""
Query = "Update " & TableName & " set "
For i = 0 To Table.Columns.Count - 1
Select Case Table.Columns(i).DataType.Name
Case "Boolean", "Int32", "Byte", "Decimal"
If DirectCast(ParameterArray(i), String) = "True" Then
ParameterArray(i) = "1"
ElseIf DirectCast(ParameterArray(i), String) = "False" Then
ParameterArray(i) = "0"
End If
If i = Table.Columns.Count - 1 Then
Query = (Query + Table.Columns(i).ColumnName & "=") + ParameterArray(i)
Else
Query = (Query + Table.Columns(i).ColumnName & "=") + ParameterArray(i) & ", "
End If
Exit Select
Case "String", "DateTime"
If DirectCast(ParameterArray(i), String).Contains("'") Then
tempstr = DirectCast(ParameterArray(i), String)
ParameterArray(i) = DirectCast(ParameterArray(i), String).Replace("'", "''")
temp_index = i
End If
If i = Table.Columns.Count - 1 Then
Query = (Query + Table.Columns(i).ColumnName & "='") + ParameterArray(i) & "' "
Else
Query = (Query + Table.Columns(i).ColumnName & "='") + ParameterArray(i) & "', "
End If
Exit Select
End Select
Next
If temp_index > -1 Then
ParameterArray(temp_index) = tempstr
End If
If Table.Columns(0).DataType.Name = "String" OrElse Table.Columns(0).DataType.Name = "DateTime" Then
Query = ((Query & " where ") + Table.Columns(0).ColumnName & " = '") + ParameterArray(0) & "'"
Else
Query = ((Query & " where ") + Table.Columns(0).ColumnName & " = ") + ParameterArray(0)
End If
Return Query
End Function
Private Function GenerateInsertQuery() As String
Dim i As Integer = 0
Dim tempstr As String = ""
Dim temp_index As Integer = -1
Dim TableName As String = DirectCast(Session("TableSelected"), String)
Dim Query As String = ""
Query = "Insert into " & TableName & "("
For i = 0 To Table.Columns.Count - 1
If i = Table.Columns.Count - 1 Then
Query = Query + Table.Columns(i).ColumnName
Else
Query = Query + Table.Columns(i).ColumnName & ", "
End If
Next
Query = Query & ")" & "Values ("
For i = 0 To Table.Columns.Count - 1
Select Case Table.Columns(i).DataType.Name
Case "Boolean", "Int32", "Byte", "Decimal"
If DirectCast(ParameterArray(i), String) = "True" Then
ParameterArray(i) = "1"
ElseIf DirectCast(ParameterArray(i), String) = "False" Then
ParameterArray(i) = "0"
End If
If i = Table.Columns.Count - 1 Then
Query = Query + ParameterArray(i)
Else
Query = Query + ParameterArray(i) & ", "
End If
Exit Select
Case "String", "DateTime"
If DirectCast(ParameterArray(i), String).Contains("'") Then
tempstr = DirectCast(ParameterArray(i), String)
ParameterArray(i) = DirectCast(ParameterArray(i), String).Replace("'", "''")
temp_index = i
End If
If i = Table.Columns.Count - 1 Then
Query = (Query & "'") + ParameterArray(i) & "' "
Else
Query = (Query & "'") + ParameterArray(i) & "', "
End If
Exit Select
End Select
Next
Query = Query & ")"
Return Query
End Function
Private Function GenerateDeleteQuery(ByVal index As Integer) As String
Dim TableName As String = DirectCast(Session("TableSelected"), String)
Dim query As String = ""
If Table.Columns(0).DataType.Name = "String" OrElse Table.Columns(0).DataType.Name = "DateTime" Then
query = (("Delete from " & TableName & " where ") + Table.Columns(0).ColumnName & "='") + Table.Rows(index)(0).ToString() & "'"
Else
query = (("Delete from " & TableName & " where ") + Table.Columns(0).ColumnName & "=") + Table.Rows(index)(0).ToString()
End If
Return query
End Function
#End Region
Protected Sub BtnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=op.xls")
Response.Charset = ""
' If you want the option to open the Excel file without saving then
' comment out the line below
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Dim stringWrite As New System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
TableGridView.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.[End]()
End Sub
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
' Confirms that an HtmlForm control is rendered for the specified ASP.NET
' server control at run time.
End Sub
End Class
Convert C# to VB.NET
No comments:
Post a Comment