将数据库值保存为变量
本文关键字:变量 保存 数据库 | 更新日期: 2023-09-27 18:05:41
我不知道如何从Access数据库中保存个人数据到变量。
我明白你可以这样保存变量:
int memberID;
cmd.CommandText = "SELECT * FROM MemberDetails WHERE [MemberID] =@MemberID";
cmd.Parameters.AddWithValue("@MemberID", memberID);
try
{
con.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
lblName.Text = (memberID);
}
con.Close();
}
catch (Exception)
{
throw;
}
然而,我想把所有的数据保存到单独的变量中,像这样:
cmd.CommandText = "SELECT [MemberID] = @MemberID, [Name] = @Name, [Surname] = @Surname";
cmd.Parameters.AddWithValue("@MemberID", memberID);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Surname", surname);
这显然不起作用(否则我不会在这里),但是像这样的事情是可能的,我该如何去做呢?
也许可以看看微软的这个很棒的教程,它解释了如何使用c#查询MS Access数据库以及如何处理结果:https://msdn.microsoft.com/en-us/library/ms971485.aspx
要达到预期的结果,您可以这样做(这是上面文章中提到的一个稍微修改过的示例):OleDbConnection conn = null;
OleDbDataReader reader = null;
try
{
conn = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + Server.MapPath("MyDataFolder/MyAccessDb.mdb"));
conn.Open();
OleDbCommand cmd =
new OleDbCommand("Select MemberID, Name, Surname FROM MemberDetails WHERE MemberID = @MemberID", conn);
cmd.Parameters.AddWithValue("@MemberID", memberID);
reader = cmd.ExecuteReader();
while(reader.Read())
{
memberID = reader.GetInt32(reader.GetOrdinal("MemberID"));
name = reader["Name"].ToString();
surname = reader["Surname"].ToString();
}
}
finally
{
if (reader != null) reader.Close();
if (conn != null) conn.Close();
}
要小心,只有当数据库查询中只返回一个结果时,这才会正确工作。当多个记录被返回时,只有最后一个记录将被存储在变量中。
当您需要捕获多个结果时,您可能需要创建一个Member类,为每个记录创建一个实例,并将实例添加到成员列表中。像这样:
public class Member
{
public int MemberId {get; set;}
public string Name {get; set;}
public string Surname {get; set;}
}
public IList<Member> GetMembers()
{
OleDbConnection conn = null;
OleDbDataReader reader = null;
try
{
conn = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + Server.MapPath("MyDataFolder/MyAccessDb.mdb"));
conn.Open();
OleDbCommand cmd =
new OleDbCommand("Select MemberID, Name, Surname FROM MemberDetails", conn);
reader = cmd.ExecuteReader();
var members = new List<Member>();
while(reader.Read())
{
var member = new Member();
member.MemberID = reader.GetInt32(reader.GetOrdinal("MemberID"));
member.Name = reader["Name"].ToString();
member.Surname = reader["Surname"].ToString();
members.Add(member);
}
return members;
}
finally
{
if (reader != null) reader.Close();
if (conn != null) conn.Close();
}
return null;
}
纠正我,如果我错了,但它看起来好像你试图检索一行,你有一个已知的MemberID
。如果是这样的话,你可以这样做
int memberID = [some number];
DataTable results = new DataTable(); //Your SQL row will be "saved" in here
string command = string.Format("select * from MemberDetails where MemberID = {0}", memberID);
using (SqlDataAdapter sda = new SqlDataAdapter(command, con))
{
sda.Fill(results); //Your DataTable now has your values
}
您所需要的是可以将memberID
输入条件变量的东西。