Home > DeveloperSection > Blogs > Executing select query in SqlServer using ADO.NET

Executing select query in SqlServer using ADO.NET


ADO.Net ADO.Net 
Ratings:
0 Comment(s)
 4224  View(s)
Rate this:

Executing select query in SqlServer using ADO.NET

Hi.
In this blog I will teach you that how to execute your first query by using SqlCommand object and display record by using SqlDataReader object. In this program I am using three class one is SqlConnection class which establish connection fromsqlserver, second one is SqlCommand class which store query information and is used to execute query and last one is SqlDataReader object which store result set which contains all records of query.

Program which is used to execute sql query for select command

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;             //Namespace required for ado.net programing.

using System.Data.SqlClient;   //Namespace required for ado.net programing.

namespace ConnectionDemo

{

    class Program

    {

        /// <summary>

        /// This property will return connection string required to establish connection to desired server.

        /// </summary>

        public static string getConnectionString

        {

            get

            {

                return @"Data Source=AA-PC;User Id=sa;Password=aaaaa;Initial Catalog=WorkBook";

            }

        }

        /// <summary>

        /// This method will return boolean type value. If it returns true it means connection successfully

        /// establish and if it returns false then it means some problem occurs while establishing connection to server.

        /// </summary>

        /// <returns></returns>

        public static bool establishConnection()

        {

            bool status = false;

            try

            {

                con = new SqlConnection();                      //Create an object of SqlConnection class. This class will reside in System.Data.SqlClient namespace.

                con.ConnectionString = getConnectionString;     //Pass connection string to SqlConnection class by calling ConnectionString property of SqlConnection class.

                con.Open();                                     //Open connection by calling Open() method of SqlConnection class.

                status = true;                                  //Make status to be true flag.

            }

            catch

            {

                status = false;                              //If any exception is generated then make status to be false. This means connection is not established.

            }           

            return status;

        }

        private static SqlConnection con = null;       //Create a reference variable of SqlConnection class.

        private static SqlCommand cmd = null;          //Create a reference variable of SqlCommand class.

        private static SqlDataReader dr = null;        //Create a reference variable of SqlDataReader class.

        /// <summary>

        /// This method will display record from database depends upon query.

        /// </summary>

        public static void displayRecordFromDatabase(string query)

        {

            if (establishConnection())

            {

                if (con != null)

                {

                    try

                    {

                        cmd = con.CreateCommand();                 //Create an object of sqlcommand class.

                        cmd.CommandText = query;               //Pass command text to SqlCommand object.

                        cmd.CommandType = CommandType.Text;    //Tell command type to text.

                        dr = cmd.ExecuteReader();             //Execute sql command by calling execute reader method. This will return DataReader object which will store all rows from table.

                        //Repeate statement untill rows are remaining in database.

                        while (dr.Read())

                        {

                            //Field count property will return total number of columns in a row.

                            for (int i = 0; i < dr.FieldCount; i++)

                            {

                                Console.Write(dr[i] + "\t");

                            }

                            Console.WriteLine();

                        }

                    }

                    catch

                    {

                        Console.WriteLine("Invalid query. Please enter valid query.");

                    }

                    finally

                    {

                        if (dr != null)

                        {

                            dr.Close();    //Close data reader object.

                        }

                        if (con != null)

                            con.Close();   //Close sqlconnection object.

                    }

                }

            }

            else

                Console.WriteLine("Unable to establish connection.");

        }

        static void Main(string[] args)

        {

            string query = String.Empty;            //Initilize empty value in string variable.

            Console.WriteLine("Please eneter sqlquery for select record..");     //Display a message to user.

            query = Console.ReadLine();        //Read input from user.

            while (!string.IsNullOrEmpty(query))

            {

                displayRecordFromDatabase(query);                //Call displayRecordFromDatabase() method.

                Console.ReadLine();                              //Wait for user.

                Console.Clear();                                 //Clear console window.

                Console.WriteLine("Please eneter sqlquery for select record..");   //Again display message.

                query = Console.ReadLine();                      //Read input from user.

            }

        }

    }

}

Output of following code snippet is as follows

Please eneter sqlquery for select record..

select * from product

P0001   AAAA    23

P0002   BBBB    23

P0003   BBBB    23

P0004   AAAA    23

P0005   AAAA    23

P0006   BBBB    23

Please eneter sqlquery for select record..

select * product

Invalid query. Please enter valid query.


Don't want to miss updates? Please click the below button!

Follow MindStick