Export (0) Print
Expand All

© 2004 Microsoft Corporation. All rights reserved.

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;
            
}
Show:
© 2014 Microsoft