Viewing source for Recipe1103cs.aspx

<%@ Page Language="C#" ClassName="Recipe1201cs" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

    void Page_Load(Object sender, EventArgs e)
    {
        //object vars
        SqlConnection  sqlConnection;
        SqlDataAdapter sqlDataAdapter;
        SqlCommand     sqlCommand;
        SqlParameter   sqlParameter;
        DataSet		   dataSet;
    
        //parameter value variables
        String beginningDate = "1/1/1996";
        String endingDate    = "12/31/1996";
    
    
        try
        {
           sqlConnection = new SqlConnection( "Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)" );

            //pass the stored proc name and SqlConnection
            sqlCommand = new SqlCommand( "Employee Sales by Country", sqlConnection );
            //important to set this as StoredProcedure is *not* the default
            sqlCommand.CommandType = CommandType.StoredProcedure;
    
            //we can do this several different ways.  here's one way:
            sqlParameter = new SqlParameter("@Beginning_Date",SqlDbType.DateTime,8);
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.Value = beginningDate;
            sqlCommand.Parameters.Add(sqlParameter);
    
            //here's another - this works if you know the parameter value when you add the
            //parameter and this doesn't work for output parameters...
            sqlCommand.Parameters.Add( "@Ending_Date",SqlDbType.DateTime,8 ).Value = endingDate;
    
            //instantiate SqlAdapter and DataSet
            sqlDataAdapter = new SqlDataAdapter( sqlCommand );
            dataSet = new DataSet();
    
            //populate the DatSet
            sqlDataAdapter.Fill( dataSet );
    
            //bind to the DataGrid
            dataGrid.DataSource = dataSet;
    
            //bind the Page's data
            DataBind();
        }
        catch( Exception exception )
        {
            lblErrorMsg.Text = exception.ToString();
        }
    }

</script>
<html>
	<head>
		<title>Execute a Stored Procedure and Return Results in a DataSet</title>
	</head>
	<body>
		<form id="Recipe1103" method="post" runat="server">
			<asp:DataGrid id="dataGrid" style="Z-INDEX: 101; LEFT: 9px; POSITION: absolute; TOP: 8px" runat="server" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
				<selecteditemstyle font-bold="True" forecolor="#663399" backcolor="#FFCC66"></selecteditemstyle>
				<itemstyle forecolor="#330099" backcolor="White"></itemstyle>
				<headerstyle font-bold="True" forecolor="#FFFFCC" backcolor="#990000"></headerstyle>
				<footerstyle forecolor="#330099" backcolor="#FFFFCC"></footerstyle>
				<pagerstyle horizontalalign="Center" forecolor="#330099" backcolor="#FFFFCC"></pagerstyle>
			</asp:DataGrid>
			<asp:Label id="lblErrorMsg" style="Z-INDEX: 102; LEFT: 14px; POSITION: absolute; TOP: 187px" runat="server" Width="327px" Height="111px"></asp:Label>
		</form>
	</body>
</html>