Retrieve data from database using your 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" />

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 += "<table border="1"><tbody><tr><th>City Name</th></tr><tr><td>";
                string city = rdr["name"].ToString();
                this.ltrlCityInfo.Text += city;
                this.ltrlCityInfo.Text += "</td></tr></tbody></table>";
        }
        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 output.