Code behind database connection

caleb whitehead picture caleb whitehead · May 1, 2012 · Viewed 7.8k times · Source

I want to create a database connection in my code behind. The code behind will then take all values of the two fields and place them into a multi-deminsional array. Please let me know if there is a standard or proven way of going about this.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
             ConnectionString="<%$ ConnectionStrings:englishBritishConnectionString %>" 
             SelectCommand="SELECT [input], [ouput] FROM [converstion]">
</asp:SqlDataSource>

This is the standard DB connection with asp.net for more accurate intel.

Answer

rpmerf picture rpmerf · May 1, 2012

This is how I've always done it. The imports/using goes up top, the rest goes in a function. This is the VB version:

Imports System.Data.SqlClient

    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("englishBritishConnectionString").ConnectionString.ToString)
    Dim cmd As New SqlCommand("SELECT [input], [ouput] FROM [converstion]", conn)
    Dim dr As SqlDataReader = Nothing

    Try
        conn.Open()
        dr = cmd.ExecuteReader()
        While dr.Read()
            *do whatever with variables*
        End While
    Catch ex As Exception
        lblStatus.Text = "Error: " + ex.Message + vbCrLf + _
                         "Call stack:" + vbCrLf + ex.StackTrace
    Finally
        dr.Close()
        conn.Close()
    End Try

I will see what I can do about a C# version, its been a while since I used C#.

using System.Data.SqlClient

    SqlConnection conn = New SqlConnection(ConfigurationManager.ConnectionStrings("englishBritishConnectionString").ConnectionString.ToString);
    SqlCommand cmd = New SqlCommand("SELECT [input], [ouput] FROM [converstion]", conn);
    SqlDataReader dr;

    Try
    {
        conn.Open();
        dr = cmd.ExecuteReader();
        While dr.Read();
        {
            *do whatever with variables*
        }
    }
    Catch Exception ex
    {
        lblStatus.Text = "Error: " + ex.Message + " Call stack:" + ex.StackTrace;
    }
    Finally
    {
        dr.Close();
        conn.Close();
    }