Retrieve Database values

This is basically for beginners , that how to get values from database and populate those values in their own variables or sessions or controls
Its very Simple  , .Net provides as DataReader to retrieve records 


METHOD : (For passing values from database to variables , collections)
public void GetValues()
    {
        List items = new List();


        using (SqlConnection con = new SqlConnection("your_connection_string"))
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            using (SqlCommand cm = new SqlCommand("your_storedProcedure_name", con))
            {
                cm.CommandType = CommandType.StoredProcedure;


                using (SqlDataReader Reader = cm.ExecuteReader())
                {
                    while (Reader.Read())
                    {
                        items.Add(Reader["job"].ToString());


                        // similarly other items u can get in reader
                        
                    }
                }


            }
        }



xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

METHOD 2 : (For assigning values to controls like textboxes , dropdowns etc) :



public void FillControls(string StateID)
        {
            using (SqlConnection con = new SqlConnection("your_con"))
            { 
                if (con.State == ConnectionState.Closed)
                    con.Open();
                using (SqlCommand cm = new SqlCommand ("Select stateid,statename from statemaster where stateid=@StateID",con))
                {
                    //add your parameter here if any ( to prevent sql injection 
                    cm.Parameters.AddWithValue ("@StateID" , StateID);


                    using (SqlDataReader Reader = cm.ExecuteReader ())
                    {
                        if(Reader.Read())
                        {
                            txtID.Text = Reader["stateid"].ToString();
                            txtName.Text = Reader["stateName"].ToString();
                            
                        }
                    }
                }
            }
        }
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Know if you want to retrive a single record based on where and you strongly believe that only one records retrieves back then use

if (Reader.Read())
But if you know that multiple records came back in result of your query then use while loop as in above example


Normally when we use GetProductByID() then we can use if statement for reader
And with GetProducts()  we have to use while loop.

Leave a Reply

Your email address will not be published. Required fields are marked *