How to read data in each row and each column from sql table in asp.net using C#?

Question

Sql database is StudentInfo and Table name is Registration

ID----------Name---------------Email---------------------------PhoneNo
1           Munasunghe        amilamunasinghe@yahoo.com        0717069425    
2           Liyanarachchi     hareshliya6@gmail.com            0756706352

protected void Page_Load(object sender, EventArgs e)
{
    string query = "select ID, Name, Email, PhoneNo from Registration"; 
    SqlCommand cmd1 = new SqlCommand(query);
    DataTable dt1 = GetData(cmd1);
    int rowcount = dt1.Rows.Count;
    /* I want to read data in each row step by step and assign to variables*/

}

The function GetData is used to get data from the Database.

 private DataTable GetData(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        try
        {
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
            return dt;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
            sda.Dispose();
            con.Dispose();
        }
    }

ID is Primarykey.

Results should be like(Name,Email,Phone No are variables and 1,2,... are ID value)

Name[1]=Munasunghe
Name[2]=Liyanarachchi
Email[1]=amilamunasinghe@yahoo.com  
Email[2]=hareshliya6@gmail.com
Phone No[1]=0717069425
Phone No[2]=0756706352

Refer to MSDN documentation. Sample link: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx — shree.pat18, Jan 29 '14 at 06:37

r.mirzojonov · Accepted Answer · 2014-01-31T07:19:42.740

I would say you firstly create a new class for storing your data (like StudentInfo)

public class StudentInfo
{
    public StudentInfo(int ID, string Name, string Email, string PhoneNo)
    {
        this.ID = ID;
        this.Name = Name;
        this.Email = Email;
        this.PhoneNo = PhoneNo;
    }
    public int ID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string PhoneNo { get; set; }
}

Then use this function that return's a List of StudentInfo class

public List<StudentInfo> GetData()
{
    List<StudentInfo> data = new List<StudentInfo>();
    SqlConnection con = new SqlConnection("Your connection string");
    SqlCommand command = new SqlCommand("SELECT * FROM [Registration]", con);
    con.Open();
    SqlDataReader sdr = command.ExecuteReader();
    while(sdr.Read())
    {
         data.Add((int)sdr["ID"], (string)sdr["Name"], (string)sdr["Email"], (string)sdr["PhoneNo"]);
    }
    con.Close();
    return data;
}

Then you use it like this:

List<StudentInfo> info = GetData();
foreach(StudentInfo si in info)
{
     Response.Write("<h3>ID is " + si.ID + "</h3><p>StudentName is " + si.Name + "</p>");
}

To update the values do this:

public void SetValue(int StudentID, String NewName, String NewEmail, String NewPhone)
{
    SqlConnection con = new SqlConnection("Your connection string");
    SqlCommand command = new SqlCommand("UPDATE [Registration] SET [Name]='" + NewName + "', [Email]='" + NewEmail + "', [PhoneNo]='" + NewPhone + "' WHERE [ID]=" + StudentID + "", con);
    con.Open();
    command.ExecuteNonQuery();
    con.close();
}

And I would suggest you to read some articles about sql

I want to retrieve data in .aspx file using lables. so give an example how to code foreach loop. — Pradeep Herath, Jan 29 '14 at 07:08
Suppose that I want Email[2]=hareshliya6@gmail.com and Phone No[1]=0717069425. then what i do? please help me — Pradeep Herath, Jan 31 '14 at 07:07
@Ravshajon I want to take a specific cell data using SqlDataReader sdr = command.ExecuteReader(); how can do it? I don't want to update. suppose that I want to take Email[2] as a variable. — Pradeep Herath, Jan 31 '14 at 14:58
SqlCommand command = new SqlCommand("SELECT [Email] FROM [tablename] WHERE [ID]='Id you want'", con); con.Open(); string Email = commandExecuteScalar(); con.close(); — r.mirzojonov, Jan 31 '14 at 15:20

How to read data in each row and each column from sql table in asp.net using C#?

1 Answers1