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]

Need to bind a web control to an OleDb data source in your C# web application, here’s the magic. In this case I am binding a ListBox control with an ID of ListBox1 to the ProductName field in the Products table of our old friend the Northwind database.

These are the using statements that you need to apply including the ConfigurationManager to get your web.config connection string conveniently

[codesyntax lang="csharp"]

using System.Data;
using System.Data.OleDb;
using System.Configuration;

[/codesyntax]

Here is our class that does all the work

[codesyntax lang="csharp"]

protected void DataSetFromOleDbDemo()
    {
      string strSQL = "SELECT * FROM Products WHERE CategoryID = 1";

      var conString = ConfigurationManager.ConnectionStrings["Northwind"];
      string Conn = conString.ConnectionString;

      try
      {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSQL, Conn))
        {
          DataSet ds = new DataSet();
          adapter.Fill(ds, "ProductInfo");

          foreach (DataRow dr in ds.Tables["ProductInfo"].Rows)
          {
            ListBox1.Items.Add(dr["ProductName"].ToString());
          }
        }
      }
      catch (Exception)
      {

        throw;
      }
    }

[/codesyntax]

And your OleDb connection string to put in the connectionStrings section of your web.config is as follows

[codesyntax lang="xml"]

<connectionStrings>
    <add name="Northwind" connectionString="Provider=SQLOLEDB;server=YOURSERVER;database=Northwind;uid=USERNAME;password=PASSWORD;" />
</connectionStrings>

[/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]