Tuesday, August 14, 2012

Database Connection with asp.net using C# and retrieving data using Sql DataReader




In the .cs file write the following code to make a database connection :

/*IMPORT THESE NAMESPACES FOR CREATING DATABASE CONNECTION*/

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;


  /*DECLARE CONNECTION VARIABLES*/

    SqlConnection Con;
    SqlCommand Cmd;
    SqlDataReader Rdr;

Copy paste this Page_Load function in your .cs file


protected void Page_Load(object sender, EventArgs e)
    {
        /*WRITE CONNECTION STRING AND OPEN CONNECTION*/
       
 Con = new SqlConnection("Data Source=172.168.15.26;Initial Catalog=MyDatabase;User ID=MydatabaseId;Password=123");
        Con.Open();

        /*CREATE COMMAND*/

        Cmd = new SqlCommand("Select * From ms_prao_mst", Con);
       
/*EXECUTE IT USING EXECUTEREADER IF U ARE USING SQLREADER ELSE USE EXECUTECOMMAND*/

        Rdr = Cmd.ExecuteReader();
        try
        {
            /*LOOP TILL READ FAILS AND EXTRACT THE DATA WHICH U NEED*/
            while (Rdr.Read())
            {
                /*HERE prao_cd IS THE COLUMN NAME IN THE RESULT SET*/

                if (Rdr["prao_cd"] != DBNull.Value)
                {
                     /*lblText -  ID of label control in aspx page */
                    lblText.Text = lblText.Text + " -  - " + Rdr["prao_cd"].ToString();
                }
            }
        }
        finally
        {
            if (Cmd != null)
                Cmd.Dispose();
            if (Con != null)
                Con.Close();
            if (Rdr != null)
                Rdr.Dispose();
        }
       
    }




1 comment: