ADO Performance
home
DataReader
Reader DT+Load
Next Result
ExecuteScalar
Dataset X Scalar
Table Mapping
DataTable X ExecuteScalar
Order Number:
OrderID
CustomerID
ShipCity
j
10248
VINET
Reims
j
10249
TOMSP
Münster
j
10250
HANAR
Rio de Janeiro
j
10251
VICTE
Lyon
j
10252
SUPRD
Charleroi
j
10253
HANAR
Rio de Janeiro
j
10254
CHOPS
Bern
j
10255
RICSU
Genève
j
10256
WELLI
Resende
j
10257
HILAA
San Cristóbal
1
2
3
4
5
6
7
8
9
10
...
Show code / exibir código
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Dim myConnection As String = WebConfigurationManager.ConnectionStrings("ConnStringKey").ConnectionString
Dim total As Decimal = 0
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sql As String = "Select OrderID, CustomerID, ShipCity FROM Orders"
gvOrder.DataSource = FillGrid(sql).Tables(0)
gvOrder.DataBind()
End Sub
Private Function FillGrid(ByVal sql As String) As DataSet
Dim conn As New SqlConnection(myConnection)
Dim adapter As New SqlDataAdapter(sql, conn)
Dim ds As New DataSet
conn.Open()
adapter.Fill(ds)
conn.Close()
Return ds
End Function
Protected Sub gvOrder_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvOrder.SelectedIndexChanged
Dim order As Integer = gvOrder.SelectedDataKey.Value.ToString()
Dim sql As New StringBuilder
sql.Append("SELECT [Order Details].OrderID, Products.ProductName, [Order Details].UnitPrice, ")
sql.Append("[Order Details].Quantity, [Order Details].UnitPrice * [Order Details].Quantity AS Total ")
sql.Append("FROM [Order Details] INNER JOIN Products ")
sql.Append("ON [Order Details].ProductID = Products.ProductID ")
sql.Append("WHERE [Order Details].OrderID = " & order)
Dim table As New DataTable
table = FillGrid(sql.ToString()).Tables(0)
gvProducts.DataSource = table
gvProducts.DataBind()
'sum the value
Dim SumTotal As Double = 0
For Each row As DataRow In table.Rows()
SumTotal += row("Total")
Next
lblTable.Text = String.Format("Total value (by Table): {0:n2}", SumTotal)
End Sub
Protected Sub btnScalar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnScalar.Click
Dim sql As New StringBuilder
sql.Append("SELECT SUM(UnitPrice * Quantity) AS Total ")
sql.Append("FROM [Order Details] ")
sql.Append("WHERE OrderID = " & txtOrder.Text.Trim)
Dim conn As New SqlConnection(myConnection)
Dim cmd As New SqlCommand(sql.ToString(), conn)
conn.Open()
lblTabela.Text = String.Format("Total value (by Scalar): {0:n2}", cmd.ExecuteScalar())
conn.Close()
End Sub
Protected Sub gvProducts_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvProducts.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
total += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Total"))
ElseIf e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(1).Text = String.Format("{0:n2}", total)
End If
End Sub