如何阅读每一行并从SQL表中asp.net使用C＃每列数据？ [英] How to read data in each row and each column from sql table in asp.net using C#?

问题描述

sql数据库为 StudentInfo 并表名的注册

  ID ----------名称---------------电子邮件---------- -  -  -  -  -  -  -  - -电话号码
1 Munasunghe [e-mail] 0717069425
2 Liyanarachchi [e-mail] 0756706352
 

---

 保护无效的Page_Load（对象发件人，EventArgs的发送）
{
    查询字符串=选择ID，名称，电子邮件，PHONENO从注册;
    CMD1的SqlCommand =新的SqlCommand（查询）;
    DataTable的DT1 =的GetData（CMD1）;
    INT行数= dt1.Rows.Count;
    / *我想读每行一步一步的数据，并分配给变量* /}
 

函数的的GetData 用于从数据库获取数据。

 私人数据表的GetData（CMD的SqlCommand）
    {
        DataTable的DT =新的DataTable（）;
        字符串strConnString = System.Configuration.ConfigurationManager.ConnectionStrings [conString]的ConnectionString。
        SqlConnection的CON =新的SqlConnection（strConnString）;
        SqlDataAdapter的SDA =新的SqlDataAdapter（）;
        cmd.CommandType = CommandType.Text;
        cmd.Connection = CON;
        尝试
        {
            con.Open（）;
            sda.SelectCommand = CMD;
            sda.Fill（DT）;
            返回DT;
        }
        抓住
        {
            返回null;
        }
        最后
        {
            con.Close（）;
            sda.Dispose（）;
            con.Dispose（）;
        }
    }
 

ID是PrimaryKey的。

结果应该是这样的（名称，电子邮件，联系电话是变量和1,2，...是ID值）

 名称[1] = Munasunghe
命名[2] = Liyanarachchi
电子邮件[1] =[e-mail]
电子邮件[2] =[e-mail]
联系电话[1] = 0717069425
联系电话[2] = 0756706352
 

解决方案

我会说你首先创建一个新的类用于存储数据（如StudentInfo）

 公共类StudentInfo
{
    公共StudentInfo（INT ID，字符串名称，字符串电子邮件，字符串PHONENO）
    {
        this.ID = ID;
        this.Name =名称;
        this.Email =电子邮件;
        this.PhoneNo = PHONENO;
    }
    公众诠释ID {搞定;组; }
    公共字符串名称{;组; }
    公共字符串电子邮件{获得;组; }
    公共字符串PHONENO {搞定;组; }
}
 

然后用这个函数返回的StudentInfo类的List

 公开名单＆LT; StudentInfo＆GT;的GetData（）
{
    清单＆LT; StudentInfo＆GT;数据=新的List＆LT; StudentInfo＆GT;（）;
    SqlConnection的CON =新的SqlConnection（您的连接字符串）;
    的SqlCommand命令=新的SqlCommand（SELECT * FROM [注册]，CON）;
    con.Open（）;
    SqlDataReader的SDR = Command.ExecuteReader却（）;
    而（sdr.Read（））
    {
         data.Add（（int）的特别提款权[ID]（字符串）特别提款权[名称]（字符串）特别提款权[电子邮件]（字符串）特别提款权[PHONENO]）;
    }
    con.Close（）;
    返回的数据;
}
 

然后你使用这样的：

 列表＆LT; StudentInfo＆GT;信息=的GetData（）;
的foreach（在信息StudentInfo SI）
{
     回复于（＆所述; H3和SEQ ID为+ si.ID +＆下; / H3＆GT;＆所述p为H.; StudentName为+ si.Name +＆下; / P＆gt;中）;
}
 

要更新的值做到这一点：

 公共无效的SetValue（INT StudentID，字符串新名称，字符串NEWEMAIL，字符串NewPhone）
{
    SqlConnection的CON =新的SqlConnection（您的连接字符串）;
    的SqlCommand命令=新的SqlCommand（UPDATE [注册] SET [名称] ='+新名称+'，[电邮] ='+ NEWEMAIL +'，[PHONENO] ='+ NewPhone +'WHERE [ID ] =+ StudentID +，CON）;
    con.Open（）;
    command.ExecuteNonQuery（）;
    con.close（）;
}
 

和我建议你阅读有关SQL的一些文章

Sql database is StudentInfo and Table name is Registration

ID----------Name---------------Email---------------------------PhoneNo
1           Munasunghe        [e-mail]        0717069425    
2           Liyanarachchi     [e-mail]            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]=[e-mail]  
Email[2]=[e-mail]
Phone No[1]=0717069425
Phone No[2]=0756706352

解决方案

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

这篇关于如何阅读每一行并从SQL表中asp.net使用C＃每列数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！