Good practice when retrieving SQL data?

Hi,

I’m currently using the fantastic DorkNozzle ‘framework’ for building a very basic blog in .NET.

My first “self-taught” script is as follows, and is for solely retrieving SQL results.


<%@ Page Language="C#" MasterPageFile="~/Dorknozzle.master" AutoEventWireup="true" Title="Blog" %>
<%@ Import Namespace = "System.Data.SqlClient" %>

<script runat="server">

  protected void Page_Load(object sender, EventArgs e)
  {
    // Define database connection
    SqlConnection conn = new SqlConnection(
        "Server=localhost\\\\SqlExpress;Database=Dorknozzle;" +
        "Integrated Security=True");
    // Create command 
    SqlCommand comm = new SqlCommand(
        "SELECT ID, Title FROM Entries", conn);
    // Open connection
    conn.Open();
    // Execute the command
    SqlDataReader reader = comm.ExecuteReader();
    // Read and display the data
      while(reader.Read())
      {
          entriesLabel.Text += "<a href='BlogEntry.aspx?ID=" + reader["ID"] + "'>" + reader["Title"] + "</a> | " + "<a href='EditEntry.aspx?ID=" + reader["ID"] + "'>Edit</a>" + " - <a href='DeleteBlogEntry.aspx?ID=" + reader["ID"] + "'>Delete</a>" + "<br />";
      }
    // TODO: Do something with the data
    // Close the reader and the connection
    reader.Close();
    conn.Close();
    addBlogLabel.Text = "<p><a href='AddEntry.aspx'>Would you like to Add a Blog Entry?</a></p>";
  }
</script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
           <asp:Label ID="entriesLabel" runat="server" />
           <asp:Label ID="addBlogLabel" runat="server" />
</asp:Content>

Is there anything obvious from the above that I should change to perhaps improve my coding style, without going down the MVC line just yet?

Many thanks for taking the time to look at this.

The only things I can suggest are the following:

  • Move your connection string to the web.config file, so that other sql queries can use it. And if it need to change, you change it in only one place.

  • Move C# code to code behind/.cs page instead of a runat server block. As that just adds clutter.

Other than that it looks pretty good. You could also use using{} blocks, but it is not important if you remember to close your connections when done.

Wow, thanks for such a speedy response :slight_smile:

My web.config file already reads:


<connectionStrings>
		<add name="Dorknozzle" connectionString="Server=localhost\\SqlExpress;               Database=Dorknozzle;Integrated Security=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>

I’ve also reduced my script above to be:


<%@ Page Language="C#" MasterPageFile="~/Dorknozzle.master" AutoEventWireup="true" CodeFile="AccessingData2.aspx.cs" Inherits="AccessingData2" Title="Blog" %>
<%@ Import Namespace = "System.Data.SqlClient" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
           <asp:Label ID="entriesLabel" runat="server" />
           <asp:Label ID="addBlogLabel" runat="server" />
</asp:Content>

I’ve also upgraded my CodeBehind file:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class AccessingData2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {    
        // Define database connection
        SqlConnection conn = new SqlConnection(
            "Server=localhost\\\\SqlExpress;Database=Dorknozzle;" +
            "Integrated Security=True");
        // Create command 
        SqlCommand comm = new SqlCommand(
            "SELECT ID, Title FROM Entries", conn);
        // Open connection
        conn.Open();
        // Execute the command
        SqlDataReader reader = comm.ExecuteReader();
        // Read and display the data
        while (reader.Read())
        {
            entriesLabel.Text += "<a href='BlogEntry.aspx?ID=" + reader["ID"] + "'>" + reader["Title"] + "</a> | " + "<a href='EditEntry.aspx?ID=" + reader["ID"] + "'>Edit</a>" + " - <a href='DeleteBlogEntry.aspx?ID=" + reader["ID"] + "'>Delete</a>" + "<br />";
        }
        // TODO: Do something with the data
        // Close the reader and the connection
        reader.Close();
        conn.Close();
        addBlogLabel.Text = "<p><a href='AddEntry.aspx'>Would you like to Add a Blog Entry?</a></p>";
    }
}

My only concern is this area:


        // Define database connection
        SqlConnection conn = new SqlConnection(
            "Server=localhost\\\\SqlExpress;Database=Dorknozzle;" +
            "Integrated Security=True");
        // Create command 
        SqlCommand comm = new SqlCommand(
            "SELECT ID, Title FROM Entries", conn);

How do I “drop” the connection string from my CodeBehind file and use my web.config connection data to retrieve Entries results?

Many thanks for all your help, NightStalker. It means a lot.

Yes. You can use ConfigurationManager.ConnectionStrings[“Dorknozzle”] in place of the connection string.

Sorry for the sheer newbness but can you show me how this would be used?

Do I add this in my CodeBehind file:

conn = ConfigurationManager.ConnectionStrings["DorkNozzle"];

?

And do I have to load Web.Config or is this done automatically?

If you have a chance… you should look into LINQ TO SQL or Entity Framework. Theses are ORM tools which is the new technologies for making sql database and doing data access.

Entity Framework is more recent through.

Good call, rushino :slight_smile:

I’ll try a Google of those and see how I fair :slight_smile:

I was going to also suggest an ORM. I would definitely recommend one. It will make life a lot easier.

And to answer your questions. Yes, you add this in your code-behind

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“DorkNozzle”]);

    // Create command 

    SqlCommand comm = new SqlCommand(

        "SELECT ID, Title FROM Entries", conn);

But I would suggest using some sort of Data Access Layer or ORM. I used to use a Data Helper class that returned data readers.

You can also combing T-SQL and LINQ (LINQ2SP), and use both in data access, which is what I prefer.

When I use this in my Code-Behind, I get the following error message:

Compiler Error Message: CS0103: The name ‘ConfigurationManager’ does not exist in the current context

Any ideas what may be the problem here?

Data Access Layer? Is that like MVC splitting up data, views and the controller?

Probably missing an import in the code behind file or a reference if your using a web application.

Yes. But you can also have a DAL in WebForms.

Hey rushino,

If it helps I can post my entire code for this script.

Would you be able to decipher the issue from that?

Any help at all is greatly appreciated.

Thank you

Yeah, i can give it a try.

1- What kind of application you doing ? Web Application or Website ?
2- Can you post your header of the file where the errors occurs (I mean the librairies you import ‘using’)

Thanks.

Thanks :slight_smile:

  1. ASP.NET Web Application
    2…

Here’s my full Code-Behind for ‘Default.aspx’:


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {    
        // Define database connection
        SqlConnection conn = new SqlConnection(
            "Server=localhost\\\\SqlExpress;Database=Dorknozzle;" +
            "Integrated Security=True");
        //SqlConnection conn As String = ConfigurationManager.ConnectionStrings["Dorknozzle"].ConnectionString;
        //SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DorkNozzle"]);
        // Create command 
        SqlCommand comm = new SqlCommand(
            "SELECT ID, Title FROM Entries", conn);
        // Open connection
        conn.Open();
        // Execute the command
        SqlDataReader reader = comm.ExecuteReader();
        // Read and display the data
        while (reader.Read())
        {
            entriesLabel.Text += "<a href='BlogEntry.aspx?ID=" + reader["ID"] + "'>" + reader["Title"] + "</a> | " + "<a href='EditEntry.aspx?ID=" + reader["ID"] + "'>Edit</a>" + " - <a href='DeleteBlogEntry.aspx?ID=" + reader["ID"] + "'>Delete</a>" + "<br />";
        }
        // TODO: Do something with the data
        // Close the reader and the connection
        reader.Close();
        conn.Close();
        addBlogLabel.Text = "<p><a href='AddEntry.aspx'>Would you like to Add a Blog Entry?</a></p>";
    }
}

It works fine just now, but I get an error as soon as I try to do:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DorkNozzle"]);

If I remember correctly, you are missing a using System.Configuration.ConfigurationManager

I think you right. A Web Application need reference plus lib import in the code behind file.

Is there anything obvious from the above that I should change to perhaps improve my coding style, without going down the MVC line just yet?

MVC or WebForms or even php it doesn’t matter: directly accessing your storage (might be a database or might be not…) should not be done from page behind, but from a data access layer or a repository .

Almost all the code from Page_Load should belong there. It is very important in the long run to NOT mix the presentation layer with the model layer(mixing tier responsibilities is bad practice, acronyms don’t matter here). For a trivial application you can write all the spaghetti you want, but it will bite you hard in a complex application. And don’t think that you will change your style on the spot. Once you have the “experience” to write spaghetti , you will write it even when you don’t want to, because your mind is trained to.

It 's very important to learn from the start how to write proper code and how to think about your application architecture. Good development practices are hard to master and it’s even harder if you have to change a mindset for that.

I think that asp. net mvc is a much better way to learn how to do it right, instead of WebForms. Be sure to check Asp.Net MVC tutorials and to take it slowly.

Bingo! It now works :slight_smile: Thanks for the help people :slight_smile: