VB.NET & System.Data.SqlClient - DataTable() - How to learn Connector/NET ADO.NET component System.Data.SqlClient namespace is the .NET Framework Data Provider for SQL Server data source, Create a new DataTable and fill it using the DataAdapter from SQL Server (SQL Server 2000,2005,2008 Database)
ShotDev Focus:
- VB.NET & System.Data.SqlClient - DataTable()
Example
DataTable1.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
BindData()
End Sub
Sub BindData()
'*** DataSet to DataTable ***'
myRepeater.DataSource = CreateDsToDt()
myRepeater.DataBind()
'*** DataTable ***'
'myRepeater.DataSource = CreateDataTable()
'myRepeater.DataBind()
'*** DataTable (DataTableRows) ***'
'myRepeater.DataSource = DataTableRows()
'myRepeater.DataBind()
End Sub
'*** DataSet to DataTable ***'
Function CreateDsToDt() As DataTable
Dim objConn As New System.Data.SqlClient.SqlConnection
Dim objCmd As New System.Data.SqlClient.SqlCommand
Dim dtAdapter As New System.Data.SqlClient.SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim strConnString,strSQL As String
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
strSQL = "SELECT * FROM customer"
objConn.ConnectionString = strConnString
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
dtAdapter.SelectCommand = objCmd
dtAdapter.Fill(ds)
dt = ds.Tables(0)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
'*** DataTable ***'
Function CreateDataTable() As DataTable
Dim objConn As New System.Data.SqlClient.SqlConnection
Dim dtAdapter As System.Data.SqlClient.SqlDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
objConn = New System.Data.SqlClient.SqlConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM customer"
dtAdapter = New System.Data.SqlClient.SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
'*** TableRows (DataTable) ***'
Function DataTableRows() As DataTable
Dim dt As New DataTable
Dim dr As DataRow
'*** Column ***'
dt.Columns.Add("CustomerID")
dt.Columns.Add("Name")
dt.Columns.Add("Email")
dt.Columns.Add("CountryCode")
dt.Columns.Add("Budget")
dt.Columns.Add("Used")
'*** Rows ***'
dr = dt.NewRow
dr("CustomerID") = "C001"
dr("Name") = "Win Weerachai"
dr("Email") = "win.weerachai@shotdev.com"
dr("CountryCode") = "TH"
dr("Budget") = "1000000"
dr("Used") = "600000"
dt.Rows.Add(dr)
'*** Rows ***'
dr = dt.NewRow
dr("CustomerID") = "C002"
dr("Name") = "Jake Sully"
dr("Email") = "jake.sully@shotdev.com"
dr("CountryCode") = "EN"
dr("Budget") = "2000000"
dr("Used") = "800000"
dt.Rows.Add(dr)
'*** Rows ***'
dr = dt.NewRow
dr("CustomerID") = "C003"
dr("Name") = "Tony Stark"
dr("Email") = "tony.stark@shotdev.com"
dr("CountryCode") = "US"
dr("Budget") = "3000000"
dr("Used") = "600000"
dt.Rows.Add(dr)
'*** Rows ***'
dr = dt.NewRow
dr("CustomerID") = "C004"
dr("Name") = "Peter Parker"
dr("Email") = "peter.parker@shotdev.com"
dr("CountryCode") = "US"
dr("Budget") = "4000000"
dr("Used") = "100000"
dt.Rows.Add(dr)
Return dt '*** Return DataTable ***'
End Function
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Repeater id="myRepeater" runat="server">
<HeaderTemplate>
<table border="1">
<tr>
<th>CustomerID</th>
<th>Name</th>
<th>Email</th>
<th>CountryCode</th>
<th>Budget</th>
<th>Used</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center"><%#Container.DataItem("CustomerID") %></td>
<td><%#Container.DataItem("Name") %></td>
<td><%#Container.DataItem("Email") %></td>
<td align="center"><%#Container.DataItem("CountryCode") %></td>
<td align="right"><%#Container.DataItem("Budget") %></td>
<td align="right"><%#Container.DataItem("Used") %></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</form>
</body>
</html>
Screenshot
.
.
.
Download this script.
.
.
.
VB.NET & System.Data.SqlClient - DataTable() - How to use DataTable Generate result Column and Rows to Table.
ShotDev Focus:
- VB.NET & System.Data.SqlClient - DataTable()
Example
DataTable2.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
BindTable()
End Sub
'*** Bind To Table ***'
Sub BindTable()
'*** Test DataTable Structure ***'
'********************************'
Dim dt As DataTable
Dim i As Integer
dt = CreateDataTable()
'*** Create Table **'
Dim Tb As New Table
With Tb
'.BorderColor = Drawing.Color.Black
.BorderWidth = 1
End With
'*** Rows ***'
Dim Tr As TableRow
'*** Column ***'
Dim Tc As TableCell
'*** Create Header ***'
'*** New Rows ***'
Tr = New TableRow()
'*** New Cell (Header CustomerID) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblHeadCustomerID As New Label
With lblHeadCustomerID
.Text = "CustomerID"
.Font.Bold = True
End With
Tc.Controls.Add(lblHeadCustomerID) '*** Add lblHeadCustomerID To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Header Name) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblHeadName As New Label
With lblHeadName
.Text = "Name"
.Font.Bold = True
End With
Tc.Controls.Add(lblHeadName) '*** Add lblHeadName To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Header Email) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblHeadEmail As New Label
With lblHeadEmail
.Text = "Email"
.Font.Bold = True
End With
Tc.Controls.Add(lblHeadEmail) '*** Add lblHeadEmail To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Header CountryCode) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblHeadCountryCode As New Label
With lblHeadCountryCode
.Text = "CountryCode"
.Font.Bold = True
End With
Tc.Controls.Add(lblHeadCountryCode) '*** Add lblHeadCountryCode To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Header Budget) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblHeadBudget As New Label
With lblHeadBudget
.Text = "Budget"
.Font.Bold = True
End With
Tc.Controls.Add(lblHeadBudget) '*** Add lblHeadBudget To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Header Used) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblHeadUsed As New Label
With lblHeadUsed
.Text = "Used"
.Font.Bold = True
End With
Tc.Controls.Add(lblHeadUsed) '*** Add lblHeadUsed To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
Tb.Controls.Add(Tr)
'*** End Header ***'
'*************** Input DataTable To TableRows ***************'
For i = 0 To dt.Rows.Count - 1
Tr = New TableRow()
'*** New Cell (CustomerID) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.HorizontalAlign = HorizontalAlign.Center
Tc.BorderWidth = 1
Dim lblCustomerID As New Label
With lblCustomerID
.Text = dt.Rows(i)("CustomerID")
End With
Tc.Controls.Add(lblCustomerID) '*** Add lblCustomerID To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Name) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblName As New Label
With lblName
.Text = dt.Rows(i)("Name")
End With
Tc.Controls.Add(lblName) '*** Add lblName To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Email) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblEmail As New Label
With lblEmail
.Text = dt.Rows(i)("Email")
End With
Tc.Controls.Add(lblEmail) '*** Add lblEmail To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (CountryCode) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblCountryCode As New Label
With lblCountryCode
.Text = dt.Rows(i)("CountryCode")
End With
Tc.Controls.Add(lblCountryCode) '*** Add lblCountryCode To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Budget) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblBudget As New Label
With lblBudget
.Text = dt.Rows(i)("Budget")
End With
Tc.Controls.Add(lblBudget) '*** Add lblBudget To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
'*** New Cell (Used) ***'
Tc = New TableCell()
'Tc.BorderColor = Drawing.Color.Black
Tc.BorderWidth = 1
Dim lblUsed As New Label
With lblUsed
.Text = dt.Rows(i)("Used")
End With
Tc.Controls.Add(lblUsed) '*** Add lblUsed To Cell ***'
Tr.Cells.Add(Tc) '*** Add Cell To Rows ***'
Tb.Controls.Add(Tr)
Next
'************ End Input DataTable To TableRows *************'
Me.lblTable.Controls.Add(Tb)
End Sub
'*** DataTable ***'
Function CreateDataTable() As DataTable
Dim objConn As New System.Data.SqlClient.SqlConnection
Dim dtAdapter As System.Data.SqlClient.SqlDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
objConn = New System.Data.SqlClient.SqlConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM customer"
dtAdapter = New System.Data.SqlClient.SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblTable" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
.
.
.
Download this script.
Loading ...
3lithuania…
…