An Easy Way To Query a Database

Introduction

Often one needs to obtain data from database and sometimes it becomes hectic to write some piece of code again and again. Like Connection Open statement, Error Message Coding, Connection string etc. This can be optimized by making a class while calling the methods of class with 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 Types of Data, in case 1st a single row consisting of few columns or a collection of rows, i.e., table is returned.

In the later case, an int number of affected rows is returned.

Using the Code

Make a Class Query, which has two methods, one for each of the above mentioned types.

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

First look at Select Query Method. Here DataSet is used as return type. The complete code is placed in try catch block to ensure smooth functioning. First SqlConnection is created, 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);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comd);
            da.Fill(ds);
            return ds;
                 }
        catch (Exception ex)    {
            MessageBox.Show(ex.Message);
            return null;            
            }
        }

Now for the second type, we need int return type for number of rows effected by the SQL command. THe 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);
            int x = comd.ExecuteNonQuery();
            return x;
            }
        catch (Exception ex)
            {
            data.mymsgshow(ex.Message, 0);
            return -1;
            }
        }

From the Main, these can be called as:

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

Have Fun in Life, Bring Happiness to Faces.