29 April, 2012

Retrieve data from database by first ASP.net program

You who are very beginner and want to retrieve data from database, can use the code snippet. Though this is not good practice, this is very useful for beginners. Because beginners is not interested to layer architecture can not create connection string in web.config file. Here, I have create a city_info table and displayed this data into a table of asp page. Ok, no more talk. Lets start.

Step 1: Create a table city_info and insert 3 data in the table.

CREATE TABLE city_info
(
      id INT
      , name VARCHAR(100)
)

insert into city_info
(
id, name
)
values
(
      1    , 'Dhaka'
)

insert into city_info
(
id, name
)
values
(
      2      , 'Chittagong'
)


insert into city_info
(
id, name
)
values
(
      3      , 'Comilla'
)

Step 2: Create a literal control in asp page.

<asp:Literal ID = "ltrlCityInfo" runat = "server">asp:Literal>

Step 3: Accessing data in Page_Load method of asp page and bind data with a table in ltrCityInfo




using System.Data.SqlClient; //Use for SqlConnection, SqlCommand
using System.Data;           //Use for CommandType


protected void Page_Load(object sender, EventArgs e)
{
        
//Connection string, here SOFT is database server name, TestDB is database name, user id and password of //database server
        string connectionString = "Data Source=SOFT;Initial Catalog=TestDB;User ID=sa;Password=sa";
        SqlConnection sqlConn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = sqlConn;


        SqlDataReader rdr = null;

        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select id, name from city_info";

        try
        {
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();

            rdr = cmd.ExecuteReader();

            this.ltrlCityInfo.Text = "";
            while (rdr.Read())
            {
                this.ltrlCityInfo.Text += "";
            }

            this.ltrlCityInfo.Text += "
City Name
"; string city = rdr["name"].ToString(); this.ltrlCityInfo.Text += city; this.ltrlCityInfo.Text += "
"; } catch (Exception exp) { throw (exp); } finally { if (sqlConn.State == ConnectionState.Open) sqlConn.Close(); } }

Yes! We have done. After running the project, you will see the following output.

City Name
Dhaka
Chittagong
Comilla

No comments:

Post a Comment