Programmatically Querying MS SQL in C#

There are several ways to query in C#. Sometimes I see very creative ways of using built in controls. But, if you want a simple method to select or insert data from and to the database, I found this to me the most straight-forward.

For this example, we’ll assume you have a table called customers already set up in MS SQL. The table is composed of three columns, an auto-generated id column, a cust_name column, and a entry_date column.

customers
id	cust_name	entry_date

The first step is to include the database location and login information in your web.config file inside the <appSettings> tag, replacing localhost, my_userid, my_password, and my_database with your database information:

<add key="connection" value="server=localhost;Persist Security Info=True;uid=my_userid;pwd=my_password;Initial Catalog=my_database;"/>

Create a new class called Customers.cs inside the App_Code folder to store the row values:

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

public class Customers
{
    private int id = -1;
    private String cust_name;
    private DateTime entry_date;

    public Customers()
    {

    }

    public int Id
    {
        get
        {
            return id;
        }

        set
        {
            id = value;
        }
    }

    public String Cust_name
    {
        get
        {
            return cust_name;
        }

        set
        {
            cust_name = value;
        }
    }

    public DateTime Entry_date
    {
        get
        {
            return entry_date;
        }

        set
        {
            entry_date = value;
        }
    }
}

Then create a new class named CustomersData.cs inside the App_Code folder:

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

public static class CustomersData
{
    private static string connection = ConfigurationManager.AppSettings.Get("connection");
    private static SqlConnection myConnection = new SqlConnection(connection);

    // Get all customers in arraylist
    public static ArrayList getAllCustomers()
    {
        SqlDataReader reader = null;
        ArrayList customersArray = new ArrayList();
        try
        {
            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            SqlCommand command = new SqlCommand("select * from customers;");
            command.Connection = myConnection;
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                Customers customers = new Customers();
                customers.Id = Int32.Parse(reader["id"].ToString().Trim());
                customers.Cust_name = reader["cust_name"].ToString().Trim();
                customers.Entry_date = DateTime.Parse(reader["entry_date"].ToString().Trim());

                customersArray.Add(customers);
            }
        }
        catch(Exception air)
        {
            System.Console.WriteLine(air.ToString());
            myConnection.Close();
            return null;
        }
        myConnection.Close();
        return customersArray;
    }

    // Get single customer by id
    public static Customers getCustomersById(int id)
    {
        SqlDataReader reader = null;
        Customers customers = new Customers();
        try
        {
            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            SqlCommand command = new SqlCommand("select * from customers where id=@id;");
            command.Connection = myConnection;
            command.Parameters.AddWithValue("@id", id);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                customers.Id = Int32.Parse(reader["id"].ToString().Trim());
                customers.Cust_name = reader["cust_name"].ToString().Trim();
                customers.Entry_date = DateTime.Parse(reader["entry_date"].ToString().Trim());

            }
        }
        catch(Exception air)
        {
            System.Console.WriteLine(air.ToString());
            myConnection.Close();
            return null;
        }
        myConnection.Close();
        return customers;
    }

    // Insert new customer row and returns -1 if fails
    public static int insertCustomers(Customers customers)
    {
        Int32 newId = -1;

        try
        {
            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }

            SqlCommand cmd = new SqlCommand("insert into customers values(@cust_name, getDate());select CAST(scope_identity() AS int);");
            cmd.Connection = myConnection;

            cmd.Parameters.AddWithValue("@cust_name", customers.Cust_name);

            newId = (Int32)cmd.ExecuteScalar();
            myConnection.Close();
        }
        catch(Exception air)
        {
            System.Console.WriteLine(air.ToString());
            myConnection.Close();
            return newId;
        }
        return newId;
    }
}

If you are wondering why we use the @cust_name in the query instead of just adding the string, it’s to protect your database against someone trying to add sql code to your database. For example, someone could say their name is “a, getDate());drop table customers;” and erase your table from the database.

With the two classes implemented, you can now make database calls from your code-behind file. For example, in your Default.aspx.cs file, you can have:

// Creating new customer instance c1
Customers c1 = new Customers();

// Setting the c1 name to "John"
c1.Cust_name = "John";

// Inserting c1 into database, and storing the generated id in newId
int newId = CustomersData.insertCustomers(c1);

// Getting the customers info into c2 instance
Customers c2 = CustomersData.getCustomersById(newId);