How to: Easily Query a Database

Introduction

Often there is a need to obtain data from a database and sometimes it becomes hectic to write pieces of code again and again. Like Connection Open statement, Error Message Coding, Connection string etc. This can be optimized by making a class and calling the methods of the class with the appropriate query string.

Normally one comes across two types of SQL Queries

• Data Selection Queries i.e. SELECT
• Data Modification Query i.e. INSERT, DELETE etc
  

Both return Different Type of Data, in case 1st a single row consisting of few columns or a collection of rows i.e. table is returned.

In later case, an int number of effects rows are returned.

Using the code

Make a Class Query, which has two methods, one for each above mentioned type.

The methods take query and connection string as argument and returns appropriate type data.

First look at Select Query Method, Here DataSet is used as return type. Complete Code is placed in try catch block to ensure smooth functioning. First SqlConnection is created, and then SqlCommand is created which takes query string and SqlConnection as argument in Constructor. Then Connection is opened. A DataSet is created and then SqlDataAdapter is used to fill the DataSet. Finally Connection is closed and DataSet is returned.
   
public DataSet select_query(string query,string con_str)
{             
    try
    {
        SqlConnection con = new SqlConnection(con_str);
        SqlCommand comd = new SqlCommand(query, con);
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(comd);
        da.Fill(ds);
        con.Close();
        con.Dispose();
         return ds;
    }
    catch (Exception ex)
    {
        data.mymsgshow(ex.Message, 0);
        return null;
     }
}

Now for 2nd type, we need int return type for number of rows affected by the Sql command.  Only Difference here is that ExecuteNonQuery method is used.

public int modify_query(string query,global_data data)
{
    try
    {
        SqlConnection con = new SqlConnection(data.Con_Str);
        SqlCommand comd = new SqlCommand(query,con);
        con.Open();
        int x = comd.ExecuteNonQuery();
        con.Close();
        return x;
    }
    catch (Exception ex)
    {
        data.mymsgshow(ex.Message, 0);
        return -1;
    }
}

From Main these can be called as

    Query myquery = new Query();
    DataSet ds;
    ds = myquery.select_query("select * from tablename" , con_str);


An Alternate to the above is as following(posted by a nice guy to help) but for a bit advance users. A better solution is to allow the exception to fail:

public DataSet select_query(string query, string con_str)
{
    using (SqlConnection con = new SqlConnection(con_str))
    {
       using (SqlCommand comd = new SqlCommand(query, con))
       {
         using (SqlDataAdapter da = new SqlDataAdapter(comd))
         {
           DataSet ds = new DataSet();
           da.Fill(ds);  // Automatically opens/closes connection.
           return ds;
         }
       }
     }
  }
}

public int modify_query(string query, global_data data)
{
    using (SqlConnection con = new SqlConnection(data.Con_Str))
    {
      using (SqlCommand comd = new SqlCommand(query, con))
      {
        con.Open();
        try
        {
          int x = comd.ExecuteNonQuery();
          return x;
        }
        finally
        { 
          con.Close();
        }
      }
    }
  }
}