microtuts

.NET C#: Old School ADO.NET Db Data Retrieval

This post is part of a series called “Old School .NET”. In the modern world of MVC, ORMs and slick front-end development I still find I always need to go back and maintain web forms and the likes. Most of these techniques aren’t really so old but I often completely forget what I’m doing when I’m thrust back into this world.

So how do I quickly grab some data from a database? Well, here’s a way:

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ToString()))
{     
    connection.Open();
           
    SqlCommand command = new SqlCommand("SELECT * FROM TableName WHERE id = " + id);
    command.Connection = connection;
    SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            CompanyName.Text = (string)reader["CompanyName"];
            AttendedBefore.SelectedIndex = ((Boolean)reader["AttendedBefore"]) ? 0 : 1;
        }
    }
}

For those of you learning or coming over from a different environment let me break it down a bit.

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ToString()))

The C# using command simply says: “use this connection only within the scope of this block”. It saves us from having to call connection.Close() and it seems like the cool thing to do.

ConfigurationManager.ConnectionStrings["Database"].ToString()

This line could be directly placed by the raw connection string if you like. In this case I’m grabbing that connection string from the web.config file. It’s the cool thing to do. Keep it all in one place. Adding a connection string to the web.config file looks like this.

web.config here

Adding a connection string to the web.config file looks like this.

SqlCommand command = new SqlCommand("SELECT * FROM TableName WHERE id = " + id);
command.Connection = connection;
SqlDataReader reader = command.ExecuteReader();

This should be pretty straightforward. We create a command from a raw SQL statement, assign the connection we initialized in the using statement to the command and then execute it.

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            CompanyName.Text = (string)reader["CompanyName"];
            AttendedBefore.SelectedIndex = ((Boolean)reader["AttendedBefore"]) ? 0 : 1;
        }
    }

Here’s where we make sure some rows were returned and then loop through the data and get things done. In the first line I assign the database column “CompanyName” to the text field of a control also named “CompanyName”.

The next line is setting the status of two radio buttons. In this case I have a column named “AttendedBefore” in the database. It’s datatype is set to bit (true, false… or 1, 0). I use a shorthand if statement to map the true/false value to the SelectedIndex of the radio button group (1 or 0).


Creating your first programming language is easier than you think.

Understanding how a programming language works is a surefire way to be a better developer. Read more.


Suggested Reading


written by Ryan Bosinger
Ryan is a web developer living and working in Banff Alberta. He also writes for giftsforpeoplewitheverything.com and freefoley.com.

Leave a Reply