Nice class with a couple of methods to handle execution of a stored procedure with an array of parameters or a regular query and return a dataset object, all you need do is pass in the connection string when creating the connection.

[codesyntax lang="csharp"]

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

namespace Connection
{
  public class Connect
  {
    private SqlConnection sqlConn;

    // Constructor
    public Connect(string ConnectionString)
    {
      sqlConn = new SqlConnection(ConnectionString);
    }

    // Open the connection 
    public void OpenCon()
    {
      sqlConn.Open();
    }

    // Close the connection 
    public void CloseCon()
    {
      sqlConn.Close();
    }

    public DataSet ExecuteProc(string spName, SqlParameter[] spParams)
    {
      try
      {
        SqlCommand cmd = new SqlCommand(spName, sqlConn);
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        if (spParams != null)
        {
          cmd.Parameters.AddRange(spParams);
        }
        DataSet ds = new DataSet();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          OpenCon();
          da.Fill(ds);
          cmd.Dispose();
          da.Dispose();
        }
        return ds;
      }
      catch (Exception ex)
      {
        throw new Exception("Error while executing the stored proc : " + ex.Message);
      }
      finally
      {
        CloseCon();
      }
    }

    public DataSet ExecuteQuery(string queryString)
    {
      try
      {
        SqlCommand cmd = new SqlCommand(queryString, sqlConn);
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.Text;

        DataSet ds = new DataSet();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          OpenCon();
          da.Fill(ds);
          cmd.Dispose();
          da.Dispose();
        }
        return ds;
      }
      catch (Exception ex)
      {
        throw new Exception("Error while executing the query : " + ex.Message);
      }
      finally
      {
        CloseCon();
      }
    }

  }
}

[/codesyntax]

 

Usage in your code behind, place these using directives

[codesyntax lang="csharp"]

using Connection;
using System.Data.SqlClient;

[/codesyntax]

Call a stored procedure in an event within the page such as follows

[codesyntax lang="csharp"]

Connect Conn = new Connect(WebConfigurationManager.ConnectionStrings["NAMEOFCONNECTIONSTRING"].ConnectionString);
      Conn.ExecuteProc("AddRecord", new SqlParameter[] {
      new SqlParameter("@Name", txtName.Text),
      new SqlParameter("@Address", txtAddress.Text),
      new SqlParameter("@Telephone", txtTelephone.Text),
      new SqlParameter("@Comments", txtComments.Text),
      new SqlParameter("@CodeField", ddlCodeField.SelectedValue),
    });

[/codesyntax]

Something simple, you want to use ADO.NET to bind data from SQL Server to a GridView, here is your code behind

[codesyntax lang="csharp"]

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

public partial class Default2 : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    string conn = ConfigurationManager.ConnectionStrings["STRINGNAME"].ConnectionString;
    SqlConnection con = new SqlConnection(conn);

    using (con)
    {
      SqlCommand cmd = new SqlCommand("SELECT * FROM Table", con);
      con.Open();
      SqlDataReader rdr = cmd.ExecuteReader();
      gv.DataSource = rdr;
      gv.DataBind();
    }
  }
}

[/codesyntax]

Here is your control

[codesyntax lang="csharp"]

<asp:GridView ID=”gv” runat=”server” />

[/codesyntax]

Remember to add the usual using directives at the start of the codebehind

[codesyntax lang="csharp"]

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

[/codesyntax]

For a DropDownList on your ASP.NET page with an ID of Category such a

[codesyntax lang="html4strict"]

<asp:DropDownList ID="Category" runat="server">
</asp:DropDownList>

[/codesyntax]

The following in your code behind will bind data from an example Northwind database on SQL Server to the text and values of the DropDownList

[codesyntax lang="csharp"]

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

namespace AppDevStuff
{
  public partial class _Default : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      using(SqlConnection cnn = new SqlConnection("Data Source=YOURSERVERNAME; Database=Northwind; UID=YOURUSERNAME; PWd=YOURPASSWORD"))
      {
        SqlCommand cmd;
        SqlDataReader rdr;
        if (!Page.IsPostBack)
        {
          cmd = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName;", cnn);
          cnn.Open();
          rdr = cmd.ExecuteReader();

          Category.DataSource = rdr;
          Category.DataTextField = "CategoryName";
          Category.DataValueField = "CategoryID";
          Category.DataBind();
        }
      }
    }
  }
}

[/codesyntax]