在ASP.NET(C#)中调用周销售记录存储过程-CSDN博客

本文链接：https://blog.csdn.net/llwen218/article/details/1190161

创建一个名为DynamicSalesByWeek的ASP.NET （C#）程序，把WebForm1.aspx改为default.aspx,创建一个名为DataTier的类库，把Class1.cs改名为DataTier.cs，在Web.config增加要增加连接的数据库名称。

default.aspx的全部内容如下：

<%@ Page language="c#" Codebehind="Default.aspx.cs" AutoEventWireup="false" Inherits="DynamicSalesByWeek.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>Dynamic Sales by Week</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
<style>
   BODY { FONT-SIZE: 10pt; FONT-FAMILY: Tahoma,Arial,sans-serif }
</style>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
   <H3>Dynamic Category Sales by Week Demo</H3>
   <P>This example shows a simple .aspx page with a DataGrid control making use of the
    DynamicCategorySalesByWeek stored procedure.</P>
   <P>Starting Date: 
    <asp:TextBox id="txtStartingDate" runat="server" Width="112px"></asp:TextBox>    
    Number of weeks: 
    <asp:DropDownList id="ddWeeks" runat="server" Width="56px">
     <asp:ListItem Value="1">1</asp:ListItem>
     <asp:ListItem Value="2">2</asp:ListItem>
     <asp:ListItem Value="3">3</asp:ListItem>
     <asp:ListItem Value="4">4</asp:ListItem>
     <asp:ListItem Value="5">5</asp:ListItem>
    </asp:DropDownList>    
    <asp:Button id="Button1" runat="server" Text="Go"></asp:Button></P>
   <P>
    <asp:DataGrid id="DataGrid1" runat="server" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px"
     BackColor="White" CellPadding="3" GridLines="Horizontal" AutoGenerateColumns="False">
     <FooterStyle ForeColor="#4A3C8C" BackColor="#B5C7DE"></FooterStyle>
     <SelectedItemStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#738A9C"></SelectedItemStyle>
     <AlternatingItemStyle BackColor="#F7F7F7"></AlternatingItemStyle>
     <ItemStyle ForeColor="#4A3C8C" BackColor="#E7E7FF"></ItemStyle>
     <HeaderStyle Font-Bold="True" ForeColor="#F7F7F7" BackColor="#4A3C8C"></HeaderStyle>
     <PagerStyle HorizontalAlign="Right" ForeColor="#4A3C8C" BackColor="#E7E7FF" Mode="NumericPages"></PagerStyle>
    </asp:DataGrid></P>
</form>
</body>
</HTML>

default.aspx.cs的全部内容如下：

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace DynamicSalesByWeek
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList ddWeeks;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.TextBox txtStartingDate;
protected System.Web.UI.WebControls.Button Button1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here

   if (!IsPostBack)
   {
    // set defaults
    txtStartingDate.Text = "3/1/1998";
    ddWeeks.SelectedValue = Convert.ToString(4);
   }

   // retrieve the data
   DataSet ds = DataTier.DynamicCategorySalesByWeek(
    Convert.ToDateTime(txtStartingDate.Text)
    , Convert.ToInt32(ddWeeks.SelectedValue)
    );

   // our datagrid has AutoGenerateColumns set to False; we'll
   // add columns here so we can specify column formatting along the way
   foreach(DataColumn c in ds.Tables[0].Columns)
   {
    BoundColumn bc = new BoundColumn();
    bc.HeaderText = c.ColumnName.Replace("Week of ", "Week of <br />");
    bc.DataField = c.ColumnName;

    if (c.DataType != typeof(System.String))
    {
     bc.DataFormatString = "{0:#,##0.00}";
     bc.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
     bc.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
    }

    DataGrid1.Columns.Add(bc);
   }

   // bind the data
   DataGrid1.DataSource = ds;
   DataGrid1.DataBind();

}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
   //
   // CODEGEN: This call is required by the ASP.NET Web Form Designer.
   //
   InitializeComponent();
   base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}

DataTier.cs是连接数据库的类，全部内容如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DynamicSalesByWeek
{
/// <summary>
/// Summary description for Class1.
/// </summary>
public class DataTier
{
public DataTier()
{
   //
   // TODO: Add constructor logic here
   //
}

public static SqlConnection GetConnection()
{
   // get the connection string from web.config
   string sCon = ConfigurationSettings.AppSettings["ConnectionString"];
   return new SqlConnection(sCon);

}

public static DataSet DynamicCategorySalesByWeek(DateTime startingDate, int numWeeks)
{
   using (SqlConnection con = GetConnection())
   {
    SqlCommand cmd = new SqlCommand("DynamicCategorySalesByWeek", con);
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameters
    cmd.Parameters.Add("@startingDate", startingDate);
    cmd.Parameters.Add("@numWeeks", numWeeks);

    // execute via a DataAdapter and return the results as a DataSet
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
   }
} }
}

Web.config的全部内容如下：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>