This sample uses the SQL Managed Provider and the Northwind database to populate a DropDownList.
Example Stored Procedure:
CREATE Procedure [Cust_GetCompanyName]
as
Select CompanyName
from Customers
GO
Example ASPX Page:
<%@ Page language="c#" Codebehind="Article618.aspx.cs" AutoEventWireup="false" Inherits="ASPAllianceArticles.Article618.Article618" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>Populate a dropdown via stored procedure</title>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<P>Company Name:
<asp:DropDownList id="CompanyNameDropDownList" runat="server"></asp:DropDownList></P>
<P>
<asp:Label id="dbErrorLabel" runat="server" Visible="False">Label</asp:Label></P>
</form>
</body>
</HTML>
Code Behind:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using ASPAlliance.Utilities;
namespace ASPAllianceArticles.Article618
{
/// <summary>
/// Summary description for Article618.
/// </summary>
public class Article618 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label dbErrorLabel;
protected System.Web.UI.WebControls.DropDownList CompanyNameDropDownList;
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection dbConn = null;
string sqlStmt = null;
ASPAlliance.Utilities.SqlDatabaseHelper dbHelper =
new ASPAlliance.Utilities.SqlDatabaseHelper();
try
{
// Get an open database connection
dbConn = dbHelper.openDatabaseConnection();
// define the sql to perform the database insert
sqlStmt = "Cust_GetCompanyName"; // Stored Procedure to use
// Establish a new OracleCommand
SqlCommand cmd = new SqlCommand();
// Set command to create your SQL statement
cmd.CommandText = sqlStmt;
// Set the OracleCommand to your database connection
cmd.Connection = dbConn;
// Set the command type to text
cmd.CommandType = CommandType.StoredProcedure;
// Execute the SQL Statement
SqlDataReader sqlReader = cmd.ExecuteReader();
if (sqlReader.HasRows)
{
CompanyNameDropDownList.DataSource = sqlReader;
CompanyNameDropDownList.DataTextField = "CompanyName";
CompanyNameDropDownList.DataValueField = "CompanyName";
CompanyNameDropDownList.DataBind();
}
}
catch(SqlException ex)
{
dbErrorLabel.Text = ex.Message;
dbErrorLabel.Visible = true;
}
finally
{
// Close the database connection
dbHelper.closeDatabaseConnection(dbConn);
}
}
#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
}
}
End result should be similar to the following:
