Click here to Skip to main content
Click here to Skip to main content

An Easy Way To Query a Database

, 29 Apr 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

dontumindit

Pakistan Pakistan
I am programming learner.

Comments and Discussions

 
GeneralAdd support for parameters. Don't keep creating and disposin... PinmemberPIEBALDconsult30-Apr-11 19:55 
GeneralElegant solutions but.. PinmemberEdMan19629-Apr-11 23:59 
QuestionWhy using? PinmemberDan Schubel14-Feb-11 6:38 
AnswerRe: Why using? PingroupBoneSoft14-Feb-11 7:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 29 Apr 2011
Article Copyright 2011 by dontumindit
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid