Figure 1 Inside a Visual Basic Web Form
<%@ Page Language="vb" Codebehind="WebForm.aspx.vb"
Inherits="MyVBDataLayer.WebForm"%>
<html>
<head></head>
<body>
<span>Example of Executing a SQL Statement and Filling a Grid</span>
<asp:DataGrid
id="grdSql"
runat="Server"
autogeneratecolumns="true"
headerstyle-backcolor="#333399"
headerstyle-font-names="tahoma"
headerstyle-font-size="8pt"
headerstyle-forecolor="#ffff66"
itemstyle-backcolor="White"
itemstyle-font-names="tahoma"
itemstyle-font-size="8pt"
alternatingitemstyle-backcolor="LightGoldenrodYellow"
cellpadding="3"
/>
<br><br>
<span>Example of Executing a Stored Procedure and Filling a Grid</span>
<asp:DataGrid
id="grdProc"
runat="Server"
autogeneratecolumns="true"
headerstyle-backcolor="#333399"
headerstyle-font-names="tahoma"
headerstyle-font-size="8pt"
headerstyle-forecolor="#ffff66"
itemstyle-backcolor="White"
itemstyle-font-names="tahoma"
itemstyle-font-size="8pt"
alternatingitemstyle-backcolor="LightGoldenrodYellow"
cellpadding="3"
/>
</body>
</html>
Figure 2 Visual Basic CodeBehind (part 1)
Imports System
Imports System.Data
Imports System.Web.UI
Imports System.Web.UI.WebControls
Public Class WebForm
Inherits System.Web.UI.Page
'//
'// VARIABLE DECLARATION BEGIN HERE
'//
Protected grdSql As DataGrid
'// The DataGrid that shows the data from the SQL statement
Protected grdProc As DataGrid
'// The DataGrid that shows the data from the stored procedure
'//
'//
'// Constructor
'//
Public WebForm()
Figure 3 Visual Basic CodeBehind (part 2)
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim oDs As DataSet = Nothing
Dim oSql As SqlService = Nothing
Dim sSql As String = ""
Dim sProcName As String = ""
'// Instantiate the SqlService object with a connection string
oSql = New MyVBDataLayer.SqlService("localhost", "northwind", _
"sa", "")
'// Specify the SQL Statement
sSql = "SELECT CategoryID, CategoryName, Description FROM " _
"Categories ORDER BY CategoryName"
'// Execute the SQL statement using the SQLService object and
'// return a DataSet
oDs = oSql.RunSql(sSql, "Category")
'// Fill the DataGrid with the data from the DataSet
grdSql.DataSource = New DataView(oDs.Tables("Category"))
grdSql.DataBind()
'// Destroy the DataSet
oDs = Nothing
'// Specify the Stored Procedure
sProcName = "SalesByCategory"
'// Execute the SQL statement using the SQLService object and
'// return a DataSet
oSql.AddParameter("@CategoryName",ssenumSqlDataTypes. _
ssSDT_String, 15, "Produce")
oSql.AddParameter("@OrdYear",ssenumSqlDataTypes. _
ssSDT_String, 4, "1998")
'// Execute the Stored Procedure using the SQLService object and
'// return a DataSet
oDs = oSql.RunProc(sProcName, "Sales")
'// Fill the DataGrid with the data from the DataSet
grdProc.DataSource = New DataView(oDs.Tables("Sales"))
grdProc.DataBind()
'// Destroy the DataSet
oDs = Nothing
'// Destroy the SQLService object
oSql = Nothing
End Sub
End Class
Figure 4 C# CodeBehind (part 1)
//
// NameSpaces to Reference
//
using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace MyCSDataLayer
{
//
// FileName: WebForm.aspx.cs
// Author: Lancelot Web Solutions, LLC
// Date: 07/04/2001
//
// Purpose: This class serves as a codebehind for the WebForm.aspx
// ASP.NET page. When the page loads, we load the 2 DataGrids using
// the custom assembly called MyCSDataLayer. The first DataGrid is
// loaded from the resultset from a SQL statement. The second
// DataGrid is loaded from the resultset from a Stored Procedure
// statement.
//
public class WebForm : System.Web.UI.Page
{
•••
Figure 5 C# CodeBehind (part 2)
//
// Declare Protected variables
//
protected DataGrid grdSql;
// DataGrid that shows the data from the SQL
protected DataGrid grdProc;
// DataGrid that shows the data from the proc
//
//
// Constructor
//
protected WebForm()
{
Page.Init += new System.EventHandler(Page_Init);
}
//
// This code executes when the page is loaded.
//
private void Page_Load(object sender, System.EventArgs e)
{
DataSet oDs = null; // The DataSet to store the data for the
// DataGrids
SqlService oSql = null; // The instance of the SqlService class
string sSql = ""; // The SQL statement used to fill a
// DataSet
string sProcName = ""; // The Stored Procedure used to fill a
// DataSet
// Instantiate the SqlService object with a connection string
oSql = new SqlService("localhost", "northwind", "sa", "");
// Specify the SQL Statement
sSql = "SELECT CategoryID, CategoryName, Description "
sSql += " FROM Categories ORDER BY CategoryName";
// Execute the SQL statement using the SQLService object and return a
// DataSet
oDs = oSql.RunSql(sSql, "Category");
// Fill the DataGrid with the data from the DataSet
grdSql.DataSource = new DataView(oDs.Tables["Category"]);
grdSql.DataBind();
// Destroy the DataSet
oDs=null;
// Specify the Stored Procedure
sProcName = "SalesByCategory";
// Execute the SQL statement using the SQLService object and return a
// DataSet
oSql.AddParameter("@CategoryName",
MyCSDataLayer.ssenumSqlDataTypes.ssSDT_String, 15, "Produce");
oSql.AddParameter("@OrdYear",
MyCSDataLayer.ssenumSqlDataTypes.ssSDT_String, 4, "1998");
// Execute the Stored Procedure using the SQLService object and return
// a DataSet
oDs = oSql.RunProc(sProcName, "Sales");
// Fill the DataGrid with the data from the DataSet
grdProc.DataSource = new DataView(oDs.Tables["Sales"]);
grdProc.DataBind();
// Destroy the DataSet
oDs=null;
// Destroy the SQLService object
oSql=null;
}
}
}
Figure 6 SqlService Class
Public Class SqlService
'//
'// Declare Class level variables
'//
Private m_sUsername As String = "" '// The Database login User ID
Private m_sPassword As String = "" '// The Database login Password
Private m_sServer As String = "" '// The SQL Server instance
Private m_sDatabase As String = "" '// The Database name
Private m_sConnectionString As String = "" '// The Database
connection string
'// The array used to store the parameters to a stored procedure
Private m_oParmList As ArrayList = New ArrayList()
Figure 7 SqlService Class Constructors
'//
'// Class Constructor (zero arguments)
'// Overloaded: Yes
'//
Sub New()
End Sub
'//
'// Class Constructor (with the entire connection string as the argument)
'// Overloaded: Yes
'//
Sub New(ByVal sConnectionString As String)
m_sConnectionString = sConnectionString
End Sub
'//
'// Class Constructor (with connection string arguments)
'// Overloaded: Yes
'//
Sub New(ByVal sServer As String, ByVal sDatabase As String, ByVal
sUsername As String, _
ByVal sPassword As String)
Server = sServer
Database = sDatabase
Username = sUsername
Password = sPassword
End Sub
Figure 8 The RunSql Method that Returns Dataset
'// Public Method
'// Overloaded: Yes
'// Return Value: DataSet
'// Purpose: Executes a SQL statement.
Public Overloads Function RunSql(ByVal sSql As String, _
ByVal sTableName As String) As DataSet
Dim oCmd As SqlCommand = New SqlCommand()'// Create a new SqlCommand
Dim oCn As SqlConnection = Nothing '// Declare the SqlConnection
Dim oDa As SqlDataAdapter = New SqlDataAdapter() '// Create a new
'// SqlDataAdapter
Dim oDs As DataSet = New DataSet() '// Create a new DataSet
'// Prepare connection to the database
oCn = Connect()
With oCmd
'// Set the CommandText, ActiveConnection and the Command Type
'// for the SqlCommand Object
.Connection = oCn
.CommandText = sSql
.CommandType = CommandType.Text
End With
With oDa
'// Assign the SqlCommand Object to the
'// Select command of the SqlDataAdapter and
.SelectCommand = oCmd
'// Execute the Sql Statement and fill the dataset
.Fill(oDs, sTableName)
End With
'// Disconnect from the database
Disconnect(oCn)
'// Return the DataSet
Return oDs
End Function
Figure 9 RunSql for Action Queries
'// Public Method
'// Overloaded: Yes
'// Return Value: None
'// Purpose: Executes a SQL statement and returns nothing.
Public Overloads Sub RunSql(ByVal sSql As String)
Dim oCmd As SqlCommand = New SqlCommand()'// Create a new SqlCommand
Dim oCn As SqlConnection = Nothing '// Declare the SqlConnection
'// Prepare connection to the database
oCn = Connect()
With oCmd
'// Execute the Sql Satement
.CommandText = sSql
.Connection = oCn
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
'// Disconnect from the database
Disconnect(oCn)
End Sub
Figure 10 RunProc Returns a Dataset
'// Public Method
'// Overloaded: Yes
'// Return Value: DataSet
'// Purpose: Executes a stored procedure.
Public Overloads Function RunProc(ByVal sProcName As String, _
ByVal sTableName As String) As DataSet
Dim oCmd As SqlCommand = New SqlCommand()'// Create a new SqlCommand
Dim oCn As SqlConnection = Nothing '// Declare the SqlConnection
Dim oDA As SqlDataAdapter = New SqlDataAdapter() '// Create a new
'//SqlDataAdapter
Dim oDs As DataSet = New DataSet() '// Create a new DataSet
Dim oSqlParameter As SqlParameter = Nothing '// Declare a
'//SqlParameter
Dim oP As Parameter = Nothing '// Declare a Parameter
'// Get an enumerator for the parameter array list
Dim oEnumerator As IEnumerator = m_oParmList.GetEnumerator()
'// Prepare connection to the database
oCn = Connect()
With oCmd
'// Set the CommandText, ActiveConnection and the Command Type
'// for the SqlCommand Object
.Connection = oCn
.CommandText = sProcName
.CommandType = CommandType.StoredProcedure
End With
'// Loop through the Parameters in the ArrayList
Do While (oEnumerator.MoveNext())
oP = Nothing
'// Get the current Parameter object
oP = oEnumerator.Current
'// Instantiate a SqlParameter object
oSqlParameter = ConvertParameterToSqlParameter(oP)
'// Add the SqlParameter object to the SqlCommand object
oCmd.Parameters.Add(oSqlParameter)
Loop
With oDA
'// Assign the SqlCommand Object to the
'// Select command of the SqlDataAdapter and
.SelectCommand = oCmd
'// Execute the Stored Procedure and fill the dataset
.Fill(oDs, sTableName)
End With
'// Disconnect from the database
Disconnect(oCn)
'// Return the DataSet
Return oDs
End Function
Figure 11 RunProc Used for Action Queries
'// Public Method
'// Overloaded: Yes
'// Return Value: None
'// Purpose: Executes a stored procedure.
Public Overloads Sub RunProc(ByVal sProcName As String)
Dim oCmd As SqlCommand = New SqlCommand()'// Create a new SqlCommand
Dim oCn As SqlConnection = Nothing '// Declare the SqlConnection
Dim oSqlParameter As SqlParameter = Nothing '// Declare a
'// SqlParameter
Dim oP As Parameter = Nothing '// Declare a Parameter
'// Get an enumerator for the parameter array list
Dim oEnumerator As IEnumerator = m_oParmList.GetEnumerator()
'// Prepare connection to the database
oCn = Connect()
With oCmd
'// Set the CommandText, ActiveConnection and the Command Type
'// for the SqlCommand Object
.Connection = oCn
.CommandText = sProcName
.CommandType = CommandType.StoredProcedure
End With
'// Loop through the Parameters in the ArrayList
Do While (oEnumerator.MoveNext())
oP = Nothing
'// Get the current Parameter object
oP = oEnumerator.Current
'// Instantiate a SqlParameter object
oSqlParameter = ConvertParameterToSqlParameter(oP)
'// Add the SqlParameter object to the SqlCommand object
oCmd.Parameters.Add(oSqlParameter)
Loop
'// Execute the Stored Procedure
oCmd.ExecuteNonQuery()
'// Disconnect from the database
Disconnect(oCn)
End Sub
Figure 12 The AddParameter Method
'// Public Method
'// Overloaded: Yes
'// Return Value: None
'// Purpose: Adds a parameter for a stored procedure.
Public Sub AddParameter(ByVal sParameterName As String, _
ByVal lSqlType As ssenumSqlDataTypes, _
ByVal iSize As Integer, ByVal sValue As String)
Dim eDataType As SqlDbType
Dim oParam As Parameter = Nothing
Select Case lSqlType
Case ssenumSqlDataTypes.ssSDT_String
eDataType = SqlDbType.VarChar
Case ssenumSqlDataTypes.ssSDT_Integer
eDataType = SqlDbType.Int
Case ssenumSqlDataTypes.ssSDT_DateTime
eDataType = SqlDbType.DateTime
Case ssenumSqlDataTypes.ssSDT_Bit
eDataType = SqlDbType.Bit
Case ssenumSqlDataTypes.ssSDT_Decimal
eDataType = SqlDbType.Decimal
Case ssenumSqlDataTypes.ssSDT_Money
eDataType = SqlDbType.Money
End Select
oParam = New Parameter(sParameterName, eDataType, iSize, sValue)
m_oParmList.Add(oParam)
End Sub
Figure 13 C# RunSql Returns a DataSet
// Public Method
// Overloaded: Yes
// Return Value: DataSet
// Purpose: Executes a SQL statement.
public DataSet RunSql(string sSql, string sTableName)
{
SqlCommand oCmd = new SqlCommand(); // Create a new SqlCommand
SqlConnection oCn = null; // Declare the SqlConnection
SqlDataAdapter oDa = new SqlDataAdapter(); // Create a new
// SqlDataAdapter
DataSet oDs = new DataSet(); // Create a new DataSet
// Connect to the database
oCn = Connect();
// Set the eConnection, CommandText and the CommandType for the
// SqlCommand Object
oCmd.Connection = oCn;
oCmd.CommandText = sSql;
oCmd.CommandType = CommandType.Text;
// Assign the SqlCommand Object to the
// Select command of the SqlDataAdapter and
oDa.SelectCommand = oCmd;
// Execute the Sql Satement
// Fill the dataset
oDa.Fill(oDs, sTableName);
// Disconnect from the database
Disconnect(oCn);
// Return the DataSet
return oDs;
}
Figure 14 C# RunProc Returns a DataSet
// Public Method
// Overloaded: Yes
// Return Value: DataSet
// Purpose: Executes a stored procedure.
public DataSet RunProc(string sProcName, string sTableName)
{
SqlCommand oCmd = new SqlCommand(); // Create a new SqlCommand
SqlConnection oCn = null; // Declare the SqlConnection
SqlParameter oSqlParameter = null // Declare a SqlParameter
SqlDataAdapter oDa = new SqlDataAdapter(); // Create a new
// SqlDataAdapter
DataSet oDs = new DataSet(); // Create a new DataSet
Parameter oP = null; // Declare a Parameter
// Get an enumerator for the parameter array list
IEnumerator oEnumerator = m_oParmList.GetEnumerator();
// Prepare connection to the database
oCn = Connect();
// Set the CommandText, ActiveConnection and the Command Type for the
// SqlCommand Object
oCmd.CommandText = sProcName;
oCmd.Connection = oCn;
oCmd.CommandType = CommandType.StoredProcedure;
// Loop through the Parameters in the ArrayList
while ( oEnumerator.MoveNext() )
{
oP = null;
// Get the current Parameter object
oP = (Parameter)oEnumerator.Current;
// Instantiate a SqlParameter object
oSqlParameter = ConvertParameterToSqlParameter(oP);
// Add the SqlParameter object to the SqlCommand object
oCmd.Parameters.Add(oSqlParameter);
}
// Assign the SqlCommand Object to the
// Select command of the SqlDataAdapter and
oDa.SelectCommand = oCmd;
// Execute the Stored Procedure and fill the dataset
oDa.Fill(oDs, sTableName);
// Disconnect from the database
Disconnect(oCn);
// Return the DataSet
return oDs;
}
|