如何使用C＃从asp.net中的sql表读取每一行和每一列中的数据？

debugcn 发表于 Dev

普拉迪普·希思（Pradeep Herath）

Sql数据库是StudentInfo，表名称是Registration

ID----------Name---------------Email---------------------------PhoneNo
1           Munasunghe        [email protected]        0717069425    
2           Liyanarachchi     [email protected]            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*/

}

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

 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是主键。

结果应类似于（名称，电子邮件，电话号码是变量，而1,2，...是ID值）

Name[1]=Munasunghe
Name[2]=Liyanarachchi
Email[1][email protected]  
Email[2][email protected]
Phone No[1]=0717069425
Phone No[2]=0756706352

米尔佐乔诺夫

我要说的是，您首先创建一个用于存储数据的新类（例如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; }
}

然后使用此函数返回一个StudentInfo类的列表

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;
}

然后，您可以像这样使用它：

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

要更新值，请执行以下操作：

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();
}

我建议您阅读一些有关sql的文章

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。