如何使用OLeDB查询正确填充DataTable [英] How to populate DataTable correctly using OLeDB Queries
问题描述
编辑2请阅读：这是一个非常的特定问题，由于代码运行良好，因此无法正常使用希望。请仔细阅读并确保在发布答案或评论之前了解我需要帮助的地方。非常感谢。
我是新手程序员。我正在尝试从所有员工假期的访问数据库表中填充数据表，其中包含所选员工假期的详细信息。目前，我的代码使用查询来计算数据库[Holiday]表中具有选定雇员薪资编号的假期。从那里开始，在该程序的 DataTable 中用第一个假期以及相关的PayrollNo 填充程序中的新行，该行的数量是该员工已完成的假期的数量。
例如：Ben有 3个假期，但是表格将在 3填充他的第一个假期行。
这表明程序正确地计算了他的假期，但是我在选择员工的每个假期时做错了事。
它也显示了它以我想要的格式写入表中。
我想做的事情看起来很简单；
下面是我代码的一部分，其中迭代了假期的循环次数，该表中包含了Ben的所有三个假期。选定的雇员在 [假日] 中。
DataTable dt =新的DataTable （）; //创建表
dt.Clear（）;
dt.Columns.Add（ Hol / Abs，typeof（string））; //栏0
dt.Columns.Add（ FirstDay，typeof（DateTime））; //第1列
dt.Columns.Add（ LastDay，typeof（DateTime））; //第2列
dt.Columns.Add（ TotalDays，typeof（int））; //第3列
dt.Columns.Add（原因，typeof（字符串））; //第4栏
LblName.Text = PassName; //加载名称
字符串ConnString = @ Provider = Microsoft.ACE.OLEDB.12.0; Data Source = | DataDirectory | \\HoliPlanData.accdb; Persist Security Info = False;
字符串查询= SELECT PayrollNo FROM [Employee] WHERE（FirstName +’’+ LastName）= @Name; //将提供所选雇员的PayrollNo
字符串CountHolQuery = SELECT COUNT（*）FROM [Holiday] WHERE PayrollNo = @PayrollNo; //将计算所有该雇员的假期
字符串CountAbsQuery =从[缺勤]中选择COUNT（*），其中PayrollNo = @PayrollNo; //将计算该员工的所有缺勤情况
字符串GetStartQuery =从[假日] SELECT STARTDate从[假日] WHERE PayrollNo = @PayrollNo; //将选择假期的开始日期
字符串GetEndQuery = SELECT EndDate FROM [Holiday] WHERE PayrollNo = @PayrollNo; //将选择缺勤
字符串的开始日期GetReasonQuery =从[假日] WHERE PayrollNo = @PayrollNo中选择原因；
string AbsGetStartQuery =从[缺席]中选择StartDate，而PayrollNo = @PayrollNo；
string AbsGetEndQuery =从[缺席]中选择EndDate，薪水编号= @PayrollNo；
string AbsGetReasonQuery =从[缺席]中选择注释，其中PayrollNo = @PayrollNo；
使用（OleDbConnection conn =新的OleDbConnection（ConnString））
使用（OleDbCommand GetPayroll =新的OleDbCommand（Query，conn））
{
conn.Open（） ;
GetPayroll.Parameters.Add（ @ Name，OleDbType.VarChar）.Value = LblName.Text;
int GotPayroll = Convert.ToInt32（GetPayroll.ExecuteScalar（））; //使用查询获取工资单No
OleDbCommand CountRowsInHol = new OleDbCommand（CountHolQuery，conn）;
OleDbCommand CountRowsInAbs = new OleDbCommand（CountAbsQuery，conn）;
CountRowsInHol.Parameters.AddWithValue（ @ PayrollNo，OleDbType.Integer）.Value = GotPayroll;
CountRowsInAbs.Parameters.AddWithValue（ @ PayrollNo，OleDbType.Integer）.Value = GotPayroll;
int HolidayCount =（int）（CountRowsInHol.ExecuteScalar（））; //使用CountHolQuery来获取[假期]中的行数。
int AbsenceCount =（int）（CountRowsInAbs.ExecuteScalar（））; //使用CountAbsQuery来获取[缺席]中的行数
int HolLoopCount = 1;
while（HolLoopCount< = HolidayCount）//将经历[Holiday]中所有SelectedPayroll的所有假期'
{
OleDbCommand GetStart = new OleDbCommand（GetStartQuery，conn）;
OleDbCommand GetEnd =新的OleDbCommand（GetEndQuery，conn）;
OleDbCommand GetReason =新的OleDbCommand（GetReasonQuery，conn）;
GetStart.Parameters.Add（ @ PayrollNo，OleDbType.Integer）.Value = GotPayroll;
GetEnd.Parameters.Add（ @ PayrollNo，OleDbType.Integer）.Value = GotPayroll;
GetReason.Parameters.Add（ @ PayrollNo，OleDbType.Integer）.Value = GotPayroll;
DateTime StartHold = Convert.ToDateTime（GetStart.ExecuteScalar（））;
DateTime EndHold = Convert.ToDateTime（GetEnd.ExecuteScalar（））;
string ReasonHold =（GetReason.ExecuteScalar（））。ToString（）;
DataRow NewLine = dt.NewRow（）;
NewLine [ Hol / Abs] =假日;
NewLine [ FirstDay] = StartHold;
NewLine [ LastDay] = EndHold;
NewLine [ TotalDays] = GetNoWeekends（StartHold，EndHold）;
NewLine [ Reason] = ReasonHold;
dt.Rows.Add（NewLine）;
HolLoopCount = HolLoopCount +1;
}
dataGridView1.DataSource = dt;
dataGridView1.Refresh（）;
}
我的假设是我必须迭代[Holiday]数据表本身，或在代码中创建另一个DataTable来存储所有关联的行，然后再从此处提取所显示表的详细信息。
任何帮助或建议都将不胜感激。
编辑
我添加了其余代码以显示查询和过程。还要说明尽管概述了问题的根源，但我是如何填写表格的。
首先对代码的轻微（不完整）修订：
字符串ConnString = @ Provider = Microsoft.ACE.OLEDB.12.0; Data Source = | DataDirectory | \\HoliPlanData.accdb;持久安全信息= False;
string Query =从[Employee] WHERE（FirstName +’’+ LastName）= @Name中选择PayrollNo。; ///将提供选定的雇员的PayrollNo
字符串getHolidayQuery = @ SELECT StartDate，EndDate，原因
FROM [假日]
WHERE PayrollNo = @PayrollNo; //从假期
字符串中选择数据getAbsencesQuery = @ SELECT StartDate，EndDate，Comments
FROM [Absences]
WHERE PayrollNo = @PayrollNo;
var holidayData = new DataTable（）;
var missingData = new DataTable（）;
使用（OleDbConnection conn = new OleDbConnection（ConnString））
{
var getPayroll = new OleDbCommand（Query，conn）;
getPayroll.Parameters.AddWithValue（ @ name，LblName.Text）;
var holidayQuery = new OleDbCommand（getHolidayQuery，conn）;
var missingsQuery = new OleDbCommand（getAbsencesQuery，conn）;
conn.Open（）;
int GotPayroll = Convert.ToInt32（getPayroll.ExecuteScalar（））; //使用查询获取PayrollNo
holidayQuery.Parameters.AddWithValue（ @ PayrollNo，GotPayroll）;
missingsQuery.Parameters.AddWithValue（ @ PayrollNo，GotPayroll）;
holidayData.Load（holidaysQuery.ExecuteReader（））;
missingData.Load（absencesQuery.ExecuteReader（））;
conn.Close（）;
}
foreach（holidayData.AsEnumerable（）中的DataRow行）
{
//在这里您可以制作新的数据表
//有差异。
//在不存在的情况下，Holiday中的数据将与那些
相关联。有了已知的关系，我们可能有
//从数据库中获得了一个数据表。
//可能应该提供数据样本。
// DateTime StartHold = Convert.ToDateTime（GetStart.ExecuteScalar（））;
// DateTime EndHold = Convert.ToDateTime（GetEnd.ExecuteScalar（））;
//字符串ReasonHold =（GetReason.ExecuteScalar（））。ToString（）;
//以上几行是这样的：
// DateTime StartHold =（DateTime）row [ StartDate];
// DateTime EndHold =（DateTime）row [ EndDate];
//字符串ReasonHold =（string）row [原因];
//每个字段无往返数据库
}
// ...
基本上，我的意思是只对数据库执行一次调用，而不是对每个字段执行标量。使用标量方法，查询中没有什么可将第一行与第二或第三行区分开。例如：对于工资单5，假设有3个假期条目：
5，2016年1月22日，2016年1月26日
5，2016年2月1日，2016年2月5日
5，2016年2月22日，2016年2月26日
使用查询，仅使用PayrollNo作为条件，您将始终从第一行获取值（例如，2016年1月22日为startDate）。
首先将这些数据放入本地数据表中，至少提供了一个可迭代的结构。
使用Linq就像我最初所说的那样，从此数据或直接从源数据创建数据表会更容易（使用Linq可以是IQueryable而不是DataTable，但是也可能与DataTable一起使用）。 Linq是C＃本身的一部分：）意味着语言集成查询-C＃本身内置的IOW查询语言。这是一个Linq代码，用于查询示例Northwind中的某些数据（使用Codeplex中的IQToolkit）-这是您需要的所有代码，包括Form和DataGridView：
void Main（）
{
字符串路径= @ D：\data\Northwind.accdb;
string conStr = Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + path;
var provider =新的AccessQueryProvider（新的OleDbConnection（conStr），
新的ImplicitMapping（），QueryPolicy.Default）;
var sampleOrders = provider.GetTable< Order>（ Orders）
.where（o => o.OrderDate == new DateTime（1997,1,1））;
表格f =新表格{Height = 800，Width = 1024};
DataGridView dgv =新的DataGridView {Dock = DockStyle.Fill};
dgv.DataSource = sampleOrders.ToList（）;
f.Controls.Add（dgv）;
f.Show（）;
}
//实体类
公共类Order {
public int OrderID {get;组; }
公用字串CustomerID {get;组; }
public int EmployeeID {get;组; }
public DateTime OrderDate {get;组; }
公共DateTime？ RequiredDate {get;组; }
公共DateTime？ ShippedDate {get;组; }
public int ShipVia {get;组; }
公有小数？运费{组; }
公共字符串ShipCity {get;组; }
公共字符串ShipCountry {get;组; }
}
Edit 2 Please Read: This is a Very specific question, since the code runs fine, it just doesn't work the way I'd have hoped. Please read through and be sure you understand where I need help before posting an answer or comment. Much appreciated.
I'm a novice programmer/coder. I'm trying to populate a DataTable with details of an selected Employee's Holiday from a Access Database Table of all Employee's Holidays. At the moment, using queries, my code counts how many Holidays are in the Database's [Holiday] Table that have the PayrollNo of the selected Employee. From there it populates new lines in the DataTable in the program with the first Holiday with the associated PayrollNo by the amount of Holidays the employee has take.
For example: Ben has 3 holidays, but the table will populate his first Holiday on 3 lines.
This shows the program counts his holidays correctly but I've done something wrong regarding selecting each individual holiday of an employee.
It also shows its being written to the table in the format I desired.
What I want to be able to do seems quite simple; to have the table populated with all 3 of Ben's Holidays, not just his first.
Below is the section of my code where it iterates a loop for the number of holidays the selected employee has in [Holiday].
DataTable dt = new DataTable(); //Creates Table
dt.Clear();
dt.Columns.Add("Hol/Abs", typeof(string)); // Column 0
dt.Columns.Add("FirstDay", typeof(DateTime)); // Column 1
dt.Columns.Add("LastDay", typeof(DateTime)); // Column 2
dt.Columns.Add("TotalDays", typeof(int)); // Column 3
dt.Columns.Add("Reason", typeof(string)); // Column 4
LblName.Text = PassName; //Loads Name
string ConnString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\HoliPlanData.accdb;Persist Security Info=False";
string Query = "SELECT PayrollNo FROM [Employee] WHERE (FirstName + ' ' + LastName) = @Name"; //Will supply selected Employee's PayrollNo
string CountHolQuery = "SELECT COUNT(*) FROM [Holiday] WHERE PayrollNo = @PayrollNo"; //Will count all of that Employee's Holidays
string CountAbsQuery = "SELECT COUNT(*) FROM [Absences] WHERE PayrollNo = @PayrollNo"; //Will count all of that Employee's Absences
string GetStartQuery = "SELECT StartDate FROM [Holiday] WHERE PayrollNo = @PayrollNo"; // Will select the start date of Holidays
string GetEndQuery = "SELECT EndDate FROM [Holiday] WHERE PayrollNo = @PayrollNo"; // Will select the start date of Absences
string GetReasonQuery = "SELECT Reason FROM [Holiday] WHERE PayrollNo = @PayrollNo";
string AbsGetStartQuery = "SELECT StartDate FROM [Absences] WHERE PayrollNo = @PayrollNo";
string AbsGetEndQuery = "SELECT EndDate FROM [Absences] WHERE PayrollNo = @PayrollNo";
string AbsGetReasonQuery = "SELECT Comments FROM [Absences] WHERE PayrollNo = @PayrollNo";
using (OleDbConnection conn = new OleDbConnection(ConnString))
using (OleDbCommand GetPayroll = new OleDbCommand(Query, conn))
{
conn.Open();
GetPayroll.Parameters.Add("@Name", OleDbType.VarChar).Value = LblName.Text;
int GotPayroll = Convert.ToInt32(GetPayroll.ExecuteScalar()); //Uses Query to Get PayrollNo
OleDbCommand CountRowsInHol = new OleDbCommand(CountHolQuery, conn);
OleDbCommand CountRowsInAbs = new OleDbCommand(CountAbsQuery, conn);
CountRowsInHol.Parameters.AddWithValue("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
CountRowsInAbs.Parameters.AddWithValue("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
int HolidayCount = (int) (CountRowsInHol.ExecuteScalar()); //Uses CountHolQuery to Get HowMany lines are in [Holiday]
int AbsenceCount = (int)(CountRowsInAbs.ExecuteScalar()); //Uses CountAbsQuery to Get HowMany lines are in [Absences]
int HolLoopCount = 1;
while (HolLoopCount <= HolidayCount) //Will go though all SelectedPayroll's holidays' in [Holiday]
{
OleDbCommand GetStart = new OleDbCommand(GetStartQuery, conn);
OleDbCommand GetEnd = new OleDbCommand(GetEndQuery, conn);
OleDbCommand GetReason = new OleDbCommand(GetReasonQuery, conn);
GetStart.Parameters.Add("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
GetEnd.Parameters.Add("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
GetReason.Parameters.Add("@PayrollNo", OleDbType.Integer).Value = GotPayroll;
DateTime StartHold = Convert.ToDateTime(GetStart.ExecuteScalar());
DateTime EndHold = Convert.ToDateTime(GetEnd.ExecuteScalar());
string ReasonHold = (GetReason.ExecuteScalar()).ToString();
DataRow NewLine = dt.NewRow();
NewLine["Hol/Abs"] = "Holiday";
NewLine["FirstDay"] = StartHold;
NewLine["LastDay"] = EndHold;
NewLine["TotalDays"] = GetNoWeekends(StartHold,EndHold);
NewLine["Reason"] = ReasonHold;
dt.Rows.Add(NewLine);
HolLoopCount = HolLoopCount + 1;
}
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
}
My assumption is I'll have to either Iterate though the [Holiday] Datatable itself, or create another DataTable in the code to store all the associated lines before extracting the details for the displayed table from there. Any help or advice would be greatly appreciated.
Edit
I added the rest of my code to show the queries and process. Also to show how I am filling the table, despite outlining how this wasn't where the problem was occurring.
First a slight (incomplete) revision to your code:
string ConnString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\HoliPlanData.accdb;Persist Security Info=False";
string Query = "SELECT PayrollNo FROM [Employee] WHERE (FirstName + ' ' + LastName) = @Name"; //Will supply selected Employee's PayrollNo
string getHolidayQuery = @"SELECT StartDate, EndDate, Reason
FROM [Holiday]
WHERE PayrollNo = @PayrollNo"; // Will select data from Holidays
string getAbsencesQuery = @"SELECT StartDate, EndDate, Comments
FROM [Absences]
WHERE PayrollNo = @PayrollNo";
var holidayData = new DataTable();
var absenceData = new DataTable();
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
var getPayroll = new OleDbCommand(Query, conn);
getPayroll.Parameters.AddWithValue("@name", LblName.Text);
var holidaysQuery = new OleDbCommand(getHolidayQuery, conn);
var absencesQuery = new OleDbCommand(getAbsencesQuery, conn);
conn.Open();
int GotPayroll = Convert.ToInt32(getPayroll.ExecuteScalar()); //Uses Query to Get PayrollNo
holidaysQuery.Parameters.AddWithValue("@PayrollNo", GotPayroll);
absencesQuery.Parameters.AddWithValue("@PayrollNo", GotPayroll);
holidayData.Load(holidaysQuery.ExecuteReader());
absenceData.Load(absencesQuery.ExecuteReader());
conn.Close();
}
foreach (DataRow row in holidayData.AsEnumerable())
{
// here you could craft a new data table
// however there is a discrepancy.
// How would the data in Holiday would relate to those
// in absences. With the relation known, we might have
// gotten a single datatable from the database.
// Probably you should give data samples.
// DateTime StartHold = Convert.ToDateTime(GetStart.ExecuteScalar());
// DateTime EndHold = Convert.ToDateTime(GetEnd.ExecuteScalar());
// string ReasonHold = (GetReason.ExecuteScalar()).ToString();
// Above lines are sort of saying:
// DateTime StartHold = (DateTime)row["StartDate"];
// DateTime EndHold = (DateTime)row["EndDate"];
// string ReasonHold = (string)row["Reason"];
// without roundtripping to database per field
}
//...
Basically, what I mean, instead of executing a scalar per field, do a single call to your database. With your scalar approach, there is nothing in query that differentiates "first" row from a second or third one. ie: For payrollNo 5 assume there are 3 holiday entries:
5, Jan 22, 2016, Jan 26, 2016
5, Feb 1, 2016, Feb 5, 2016
5, Feb 22, 2016, Feb 26, 2016
with your queries, that only use PayrollNo as a criteria, you would always be getting the values from the first row (Jan 22, 2016 for startDate for example).
Getting this data into a local datatable first, provides an iterable structure at least.
Creating a datatable from this data or directly from source, would be easier in one shot, using Linq as I originally said (with Linq it is IQueryable instead of DataTable, but might do with DataTable as well). And Linq is part of C# itself :) Means Language INtegrated Query - IOW query language built into C# itself. Here is a Linq code querying some data from sample Northwind (using IQToolkit from codeplex) - this is all the code you need including Form and DataGridView:
void Main()
{
string path = @"D:\data\Northwind.accdb";
string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path;
var provider= new AccessQueryProvider(new OleDbConnection(conStr),
new ImplicitMapping(), QueryPolicy.Default);
var sampleOrders = provider.GetTable<Order>("Orders")
.Where (o => o.OrderDate == new DateTime(1997,1,1));
Form f = new Form{Height=800,Width=1024};
DataGridView dgv = new DataGridView { Dock=DockStyle.Fill };
dgv.DataSource = sampleOrders.ToList();
f.Controls.Add(dgv);
f.Show();
}
// Entity Class
public class Order {
public int OrderID { get; set; }
public string CustomerID { get; set; }
public int EmployeeID { get; set; }
public DateTime OrderDate { get; set; }
public DateTime? RequiredDate { get; set; }
public DateTime? ShippedDate { get; set; }
public int ShipVia { get; set; }
public decimal? Freight { get; set; }
public string ShipCity { get; set; }
public string ShipCountry { get; set; }
}
这篇关于如何使用OLeDB查询正确填充DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
