如何使用数据表从SQL表中获取数据 [英] How to get data from SQL table using data table
本文介绍了如何使用数据表从SQL表中获取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我现在正在使用数据阅读器，但我需要在下面的方法中使用Datatable。请告诉我如何使用数据表而不是使用数据阅读器来实现以下方法。
我尝试过的方法：
尝试
{
con.Open（） ;
cmd.CommandText = SELECT c.UHFID，c.EmployeeName，c.EmpRank，c.EmpUnit， c.EmpPNo，c.EmpAdhaarNo，c.EmpDOI，c.EmpValidity，c.EmpPhoto，c.VehicleType，c.VehicleModelNo，c.VehicleNo，c.VehicleOwner，c.VehiclePhoto，c.VisitorsName，c.VisitorType，c。 VisitorsValidity，c.VisitorsVehicle，c.VisitorsAddress，c.VisitorsPhotoIdentity，c.VisitorsPhotoIdentityNo，c.VisitorsPhoto，c.Remarks，c.SemiVisitorName，c.SemiVisitorType，c.SemiVisitorValidity，c.SemiVisitorVehicle，c.SemiVisitorAddress，c.SemiVisitorEmployedLocation， SemiVisitorPhoto FROM RegistrationDetails c内部联接Category.Caster on c.Category = p.Id其中UHFID =' + textBox1.Text + ';
cmd.Connection = con;
rdr = cmd.ExecuteReader（）;
while （rdr.Read（））
{
if （rdr [ UHFID]。ToString（）== textBox1 .Text）
{
// for employye
if （rdr [ EmployeeName]。ToString （）！= ）
{
lbl_empname.Text = rdr [ EmployeeName]。ToString（）;
lbl_rank.Text = rdr [ EmpRank]。ToString（）;
lbl_unit.Text = rdr [ EmpUnit]。ToString（）;
lbl_pno.Text = rdr [ EmpPNo]。ToString（）;
lbl_adhar.Text = rdr [ EmpAdhaarNo]。ToString（）;
DOI = rdr [ EmpDOI]。ToString（）;
lbl_DOI.Text = DOI.Substring（ 0 ， 10 ）;
empvalidity = rdr [ EmpValidity]。ToString（）;
lbl_validity.Text = empvalidity.Substring（ 0 ， 10 ）;
byte [] bytes =（ byte []）rdr [ EmpPhoto];
MemoryStream ms = new MemoryStream（字节）;
pb_employee.Image = Image.FromStream（ms）;
panel_emp.Location = new 点（ 15 ， 36 ）;
panel_emp.Size = new 大小（ 1878 ， 856 ）;
panel_semivisitor.Visible = false ;
panel_vehicle.Visible = false ;
panel_visitor.Visible = false ;
panel_emp.Visible = true ;
}
// 访客
else if （rdr [ VisitorsName]。ToString（）！= && rdr [ 备注]。ToString（）！= ）
{
lbl_visitorname.Text = rdr [ VisitorsName]。ToString（）;
lbl_visitortype.Text = rdr [ VisitorsType]。ToString（）;
validityvisitor = rdr [ VisitorsValidity]。ToString（）;
lbl_visitorvalidity.Text = validityvisitor.Substring（ 0 ， 10 ）;
lbl_visitorehicle.Text = rdr [ VisitorsVehicle]。ToString（） ;
lbl_tomeet.Text = rdr [ 备注]。ToString（）;
lbl_photoidproof.Text = rdr [ VisitorsPhotoIdentity]]。ToString（）;
lbl_idproofdetails.Text = rdr [ VisitorsPhotoIdentityNo]。ToString（）;
byte [] bytes =（ byte []）rdr [ VisitorsPhoto];
MemoryStream ms = new MemoryStream（字节）;
pb_visitor.Image = Image.FromStream（ms）;
panel_visitor.Location = new 点（ 15 ， 36 ）;
panel_visitor.Size = new 大小（ 1878 ， 856 ）;
panel_visitor.Visible = true ;
panel_semivisitor.Visible = false ;
panel_emp.Visible = false ;
panel_vehicle.Visible = false ;
}
// 车辆
else if （rdr [ VehicleType]。ToString（）！= ）
{
lbl_vehicletype.Text = rdr [ VehicleType]的ToString（）;
lbl_vehichlemake.Text = rdr [ VehicleModelNo]。ToString（）;
lbl_vehicleno.Text = rdr [ VehicleNo]。ToString（）;
lbl_vehicleowner.Text = rdr [ VehicleOwner]。ToString（）;
byte [] bytes =（ byte []）rdr [ VehiclePhoto];
MemoryStream ms = new MemoryStream（字节）;
pb_vehicle.Image = Image.FromStream（ms）;
panel_vehicle.Location = new 点（ 15 ， 36 ）;
panel_vehicle.Size = new 大小（ 1878 ， 856 ）;
panel_vehicle.Visible = true ;
panel_visitor.Visible = false ;
panel_semivisitor.Visible = false ;
panel_emp.Visible = false ;
}
// for semivisitor
else if （rdr [ SemiVisitorName]。ToString（）！= && rdr [ address]。ToString（）！= ）
{
lbl_seminame.Text = rdr [ SemiVisitorName]。ToString（）;
lbl_semitype.Text = rdr [ SemiVisitorType]。ToString（）;
validitysemivisitor = rdr [ SemiVisitorValidity]。ToString（）;
lbl_semivalidity.Text = validitysemivisitor.Substring（ 0 ， 10 ）;
lbl_semvehicle.Text = rdr [ SemiVisitorVehicle]。ToString（）;
lbl_semiaddress.Text = rdr [ SemiVisitorAddress]。ToString（）;
lbl_semilocation.Text = rdr [ SemiVisitorEmployedLocation]。ToString（）;
byte [] bytes =（ byte []）rdr [ SemiVisitorPhoto];
MemoryStream ms = new MemoryStream（字节）;
pb_semivisitor.Image = Image.FromStream（ms）;
panel_semivisitor.Location = new 点（ 15 ， 36 ）;
panel_semivisitor.Size = new 大小（ 1878 ， 856 ）;
panel_semivisitor.Visible = true ;
panel_emp.Visible = false ;
panel_vehicle.Visible = false ;
panel_visitor.Visible = false ;
}
}
}
con.Close（）;
con.Open（）;
ds = new DataSet（）;
da = new SqlDataAdapter（ select *来自registrationdetails，con）;
da.Fill（ds， registrationdetails）;
con.Close（）;
}
catch （例外情况）
{
MessageBox.Show（ 无法读取UHF TAG， Ecartes-TAG READER，MessageBoxButtons.OK，MessageBoxIcon.Warning）;
}
解决方案
首先，您应该使用参数而不是将值连接到SQL statamenet中。这使您可以使用 SQL注入 - 维基百科 [ ^ ]。
现在使用数据表可以使用< a href =https://msdn.microsoft.com/en-us/library/905keexk(v=vs.110).aspx> DbDataAdapter.Fill Method（DataTable）（System.Data.Common） [ ^ ]使用指定的SQL语句填充数据表。这里的另一个例子是 C＃DataAdapter.Fill [ ^ ]
如果你想将数据加载到datatable中，使用 DataTable.Load方法 [ ^ ]。
所以，在此行之后：
rdr = cmd .ExecuteReader（）;
add：
DataTabled dt = 新 DataTable（）;
dt.Load（rdr）;
// 数据已加载到datatable对象中;）
I am using data reader right now, but I need to use Datatable in below method. Please let me know how can I implement below method using data table instead of using data reader.
What I have tried:
try
{
con.Open();
cmd.CommandText = "SELECT c.UHFID,c.EmployeeName,c.EmpRank, c.EmpUnit , c.EmpPNo , c.EmpAdhaarNo , c.EmpDOI , c.EmpValidity , c.EmpPhoto,c.VehicleType,c.VehicleModelNo,c.VehicleNo,c.VehicleOwner,c.VehiclePhoto,c.VisitorsName,c.VisitorType,c.VisitorsValidity,c.VisitorsVehicle,c.VisitorsAddress,c.VisitorsPhotoIdentity,c.VisitorsPhotoIdentityNo,c.VisitorsPhoto,c.Remarks,c.SemiVisitorName,c.SemiVisitorType,c.SemiVisitorValidity,c.SemiVisitorVehicle,c.SemiVisitorAddress,c.SemiVisitorEmployedLocation,SemiVisitorPhoto FROM RegistrationDetails c inner join CategoryMaster p on c.Category=p.Id where UHFID ='" + textBox1.Text + "'";
cmd.Connection = con;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr["UHFID"].ToString() == textBox1.Text)
{
//for employye
if (rdr["EmployeeName"].ToString() != "")
{
lbl_empname.Text = rdr["EmployeeName"].ToString();
lbl_rank.Text = rdr["EmpRank"].ToString();
lbl_unit.Text = rdr["EmpUnit"].ToString();
lbl_pno.Text = rdr["EmpPNo"].ToString();
lbl_adhar.Text = rdr["EmpAdhaarNo"].ToString();
DOI = rdr["EmpDOI"].ToString();
lbl_DOI.Text = DOI.Substring(0, 10);
empvalidity = rdr["EmpValidity"].ToString();
lbl_validity.Text = empvalidity.Substring(0, 10);
byte[] bytes = (byte[])rdr["EmpPhoto"];
MemoryStream ms = new MemoryStream(bytes);
pb_employee.Image = Image.FromStream(ms);
panel_emp.Location = new Point(15, 36);
panel_emp.Size = new Size(1878, 856);
panel_semivisitor.Visible = false;
panel_vehicle.Visible = false;
panel_visitor.Visible = false;
panel_emp.Visible = true;
}
//for visitor
else if (rdr["VisitorsName"].ToString() != "" && rdr["Remarks"].ToString() != "")
{
lbl_visitorname.Text = rdr["VisitorsName"].ToString();
lbl_visitortype.Text = rdr["VisitorsType"].ToString();
validityvisitor = rdr["VisitorsValidity"].ToString();
lbl_visitorvalidity.Text = validityvisitor.Substring(0, 10);
lbl_visitorehicle.Text = rdr["VisitorsVehicle"].ToString();
lbl_tomeet.Text = rdr["Remarks"].ToString();
lbl_photoidproof.Text = rdr["VisitorsPhotoIdentity]"].ToString();
lbl_idproofdetails.Text = rdr["VisitorsPhotoIdentityNo"].ToString();
byte[] bytes = (byte[])rdr["VisitorsPhoto"];
MemoryStream ms = new MemoryStream(bytes);
pb_visitor.Image = Image.FromStream(ms);
panel_visitor.Location = new Point(15, 36);
panel_visitor.Size = new Size(1878, 856);
panel_visitor.Visible = true;
panel_semivisitor.Visible = false;
panel_emp.Visible = false;
panel_vehicle.Visible = false;
}
//for vehicle
else if (rdr["VehicleType"].ToString() != "")
{
lbl_vehicletype.Text = rdr["VehicleType"].ToString();
lbl_vehichlemake.Text = rdr["VehicleModelNo"].ToString();
lbl_vehicleno.Text = rdr["VehicleNo"].ToString();
lbl_vehicleowner.Text = rdr["VehicleOwner"].ToString();
byte[] bytes = (byte[])rdr["VehiclePhoto"];
MemoryStream ms = new MemoryStream(bytes);
pb_vehicle.Image = Image.FromStream(ms);
panel_vehicle.Location = new Point(15, 36);
panel_vehicle.Size = new Size(1878, 856);
panel_vehicle.Visible = true;
panel_visitor.Visible = false;
panel_semivisitor.Visible = false;
panel_emp.Visible = false;
}
//for semivisitor
else if (rdr["SemiVisitorName"].ToString() != "" && rdr["address"].ToString() != "")
{
lbl_seminame.Text = rdr["SemiVisitorName"].ToString();
lbl_semitype.Text = rdr["SemiVisitorType"].ToString();
validitysemivisitor = rdr["SemiVisitorValidity"].ToString();
lbl_semivalidity.Text = validitysemivisitor.Substring(0, 10);
lbl_semvehicle.Text = rdr["SemiVisitorVehicle"].ToString();
lbl_semiaddress.Text = rdr["SemiVisitorAddress"].ToString();
lbl_semilocation.Text = rdr["SemiVisitorEmployedLocation"].ToString();
byte[] bytes = (byte[])rdr["SemiVisitorPhoto"];
MemoryStream ms = new MemoryStream(bytes);
pb_semivisitor.Image = Image.FromStream(ms);
panel_semivisitor.Location = new Point(15, 36);
panel_semivisitor.Size = new Size(1878, 856);
panel_semivisitor.Visible = true;
panel_emp.Visible = false;
panel_vehicle.Visible = false;
panel_visitor.Visible = false;
}
}
}
con.Close();
con.Open();
ds = new DataSet();
da = new SqlDataAdapter("select * from registrationdetails", con);
da.Fill(ds, "registrationdetails");
con.Close();
}
catch (Exception ex)
{
MessageBox.Show("Unable to Read the UHF TAG", "Ecartes-TAG READER", MessageBoxButtons.OK, MessageBoxIcon.Warning);
} 解决方案
First of all you should use parameters instead of concatenating the values into the SQL statamenet. This leaves you open to SQL injection - Wikipedia[^].
Now what comes to using a data table you can use DbDataAdapter.Fill Method (DataTable) (System.Data.Common)[^] to fill the data table using the SQL statement specified. Another example here C# DataAdapter.Fill[^]
If you want to load data into datatable, use DataTable.Load method[^].
So, after this line:
rdr = cmd.ExecuteReader();
add:
DataTabled dt = new DataTable(); dt.Load(rdr); //the data have been loaded into the datatable object ;)
这篇关于如何使用数据表从SQL表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
