50 States in a control that will work in an ASCX file

Just in case you ever happen to need this (hey, some times the situation arises). Nothing else really to this post. Have a good day!

<div>
 <label>State</label>
 <%= Html.DropDownList(Model + "Region", new SelectList(new[] {
 new { Value = "AL", Text = "Alabama" },
 new { Value = "AK", Text = "Alaska" },
 new { Value = "AZ", Text = "Arizona" },
 new { Value = "AR", Text = "Arkansas" },
 new { Value = "CA", Text = "California" },
 new { Value = "CO", Text = "Colorado" },
 new { Value = "CT", Text = "Connecticut" },
 new { Value = "DE", Text = "Delaware" },
 new { Value = "FL", Text = "Florida" },
 new { Value = "GA", Text = "Georgia" },
 new { Value = "HI", Text = "Hawaii" },
 new { Value = "ID", Text = "Idaho" },
 new { Value = "IL", Text = "Illinois" },
 new { Value = "IN", Text = "Indiana" },
 new { Value = "IA", Text = "Iowa" },
 new { Value = "KS", Text = "Kansas" },
 new { Value = "KY", Text = "Kentucky" },
 new { Value = "LA", Text = "Louisiana" },
 new { Value = "ME", Text = "Maine" },
 new { Value = "MD", Text = "Maryland" },
 new { Value = "MA", Text = "Massachusetts" },
 new { Value = "MI", Text = "Michigan" },
 new { Value = "MN", Text = "Minnesota" },
 new { Value = "MS", Text = "Mississippi" },
 new { Value = "MO", Text = "Missouri" },
 new { Value = "MT", Text = "Montana" },
 new { Value = "NE", Text = "Nebraska" },
 new { Value = "NV", Text = "Nevada" },
 new { Value = "NH", Text = "New Hampshire" },
 new { Value = "NJ", Text = "New Jersey" },
 new { Value = "NM", Text = "New Mexico" },
 new { Value = "NY", Text = "New York" },
 new { Value = "NC", Text = "North Carolina" },
 new { Value = "ND", Text = "North Dakota" },
 new { Value = "OH", Text = "Ohio" },
 new { Value = "OK", Text = "Oklahoma" },
 new { Value = "OR", Text = "Oregon" },
 new { Value = "PA", Text = "Pennsylvania" },
 new { Value = "RI", Text = "Rhode Island" },
 new { Value = "SC", Text = "South Carolina" },
 new { Value = "SD", Text = "South Dakota" },
 new { Value = "TN", Text = "Tennessee" },
 new { Value = "TX", Text = "Texas" },
 new { Value = "UT", Text = "Utah" },
 new { Value = "VT", Text = "Vermont" },
 new { Value = "VA", Text = "Virginia" },
 new { Value = "WA", Text = "Washington" },
 new { Value = "WV", Text = "West Virginia" },
 new { Value = "WI", Text = "Wisconsin" },
 new { Value = "WY", Text = "Wyoming" },
 new { Value = "DC", Text = "District of Columbia" },
 }, "Value", "Text", Model), new { @class = "stateList"})%>
</div>

How to use Stored Procedures

I’ve met quite a few folks who knew what stored procedures were but never actually had to write code to call one themselves. So this is just a quick run-down of what you need to do to call a stored procedure from your database. You’ll see it’s not so bad once you get into it.

What we are going to do is create a simple button that when clicked will call a stored procedure and return results based of a parameter that we supplied through a textbox.

We have some basic elements on an aspx page like this:

<asp:TextBox ID="testData" runat="server"></asp:TextBox>

<asp:Button runat="server" ID="Submit" Text="Submit" 
    OnClick="CallStoredProcedure" />

In your C# file you need to remember to add these references at the top:

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

That way we have access to the SQL tools we need to do this.

Let me just throw out the entirety of the code we will be using and then we will go over each element.

public void CallStoredProcedure(Object sender, EventArgs e)
 {
 // Connection String to Test Database
 String connectionString = "Data Source=localhost;Initial Catalog=Web_Test;UID=testUsername;Password=testPassword";

SqlConnection myConnection = new SqlConnection(connectionString);
 myConnection.Open();
 SqlCommand myProcedureTest = new SqlCommand("my_Stored_Procedure", myConnection);
 myProcedureTest.CommandType = CommandType.StoredProcedure;

 // Add user variables to Stored Procedure Execution
 myProcedureTest.Parameters.Add(new SqlParameter("@Test_Data", testData.Text));

// Execute the Stored Procedure
 SqlDataReader rdr = myProcedureTest.ExecuteReader();
 while (rdr.Read())
 {
    var returnedValue = rdr[0];
    // Do something with returnedValue
 }
}
 String connectionString = "Data Source=localhost;Initial Catalog=Web_Test;UID=testUsername;Password=testPassword";

So the first bit we are looking at is where we define our connection string to our database. These credentials will have to be provided to you if you are not in charge of your databases. Instead of localhost you might receive an IP address or a number of different ways to reach your database, followed by the credentials.

SqlConnection myConnection = new SqlConnection(connectionString);
 myConnection.Open();

The next section is where we create the SQL connection using the connection string we just created. It uses the credentials and information you provided to actually establish a connection to said database. The Open command establishes a connection to the database.

 SqlCommand myProcedureTest = new SqlCommand("my_Stored_Procedure", myConnection);
 myProcedureTest.CommandType = CommandType.StoredProcedure;

Here we are defining the SQL query we want to run and then tell it which database connection we want to use (in case we have more than one). We then clarify that what we are using as a SQL command is actually a stored procedure on the server.

 myProcedureTest.Parameters.Add(new SqlParameter("@Test_Data", testData.Text));

In this example, we are assuming that our Stored Procedure takes a single parameter which we pass in from our text box. In a real application, we would want to be more careful with blindly accepting user input but since this is just an example, we will just roll with it.

 SqlDataReader rdr = myProcedureTest.ExecuteReader();

This creates an object to actually “listen” for data being returned from the stored procedure after we execute everything.

 while (rdr.Read())
 {
    var returnedValue = rdr[0];
    // Do something with returnedValue
    myConnection.Close();
 }

Finally we do something with the returned data. This bit of code essentially says “while there is data to work with, do whatever code we put here and then close the connection to the database.” We are assuming that the stored procedure is only returning a single piece of information (or that we only care about the first piece) based off the “rdr[0]” code. You are responsible for knowing what to do with the returned data and use it how you see fit.

Obviously this tutorial wasn’t exhaustive on the topic of stored procedures by any stretch of the imagination but if you need to begin working with them, this should at least help you get on the ground running.

If you have any questions or comments, feel free to leave them below!

Quick Tutorial on using Session Variables

This is a down and dirty lesson on how to use Session Variables in C#. It won’t take long in your programming life that you will want an easy way to pass data from one page to another. Instead of using query strings (which also have their place in the world) or other methods, we can use these variables.

In your code behind file, wherever you want to add to the current session, you can simple add the line:

Session["yourVariableName"] = valueOfYourVariable; 

The “valueOfYourVariable” can pretty much be whatever you want. Strings, integers, variables, and so many other kinds of data.

On the page that is going to receive the data, pulling that information is as simple as using the following code:

string loadedData = (string)Session["yourVariableName"];

Obviously you need to cast your variable on the receiving page as the same data type as you saved it on the previous page, but other than that, it really is that simple to pass data between pages.

Any other complex data types you would just treat as you would normally once you load them.

 

behemothdan and son

Welcome!

behemothdan and son My name is Daniel and I am web designer and developer. My normal job involves a lot of C# and VB programming in a .NET environment with some classic ASP maintenance here and there. On the side, I do a lot of work in PHP and WordPress.

This site is all about coding, server maintenance, and problem solving issues that I come across in my day-to-day life. For more information, be sure to check out the About page.