如何将数据库读入数据集 [英] how to read database into dataset
问题描述
在Oracle数据库中，有两个表kpi_lookup和kpi_lookup_detail，然后kpi_lookup_detail有一个外部约束键kpi_lookup_id来检查kpi_look。
两个表中的两个表如下所示：< br $>
KPI_LOOKUP
id，columnheader
1，country; month; amount
KPI_LOOKUP_DETAIL
kpi_lookup_id，内容
1，CN; 2014-01; 1000
1，CN; 2014-02; 2000
1，CN; 2014-03; 3000
我想使用DataSet对象，数据如下：
国家，月份，金额
CN，2014-01,1000
CN，2014-02,2000
CN，2014-02,3000
我需要说一些点，在KPI_LOOKUP中，列标题不是固定长度，也就是说，表中的数据是可能的，例如1，country; month; amcount，还有另一种可能的1，country; month; quarter; quantity; amount等等。
和kp i_lookup_detail，内容数据也是这样的。
谁能用C＃帮我解决这个问题，谢谢大家。
in Oracle Database ,there are two tables kpi_lookup and kpi_lookup_detail,then kpi_lookup_detail has a foreign constraint key "kpi_lookup_id" to check in kpi_look.
the two table in both table are like this below:
KPI_LOOKUP
id,columnheader
1,country;month;amount
KPI_LOOKUP_DETAIL
kpi_lookup_id,content
1,CN;2014-01;1000
1,CN;2014-02;2000
1,CN;2014-03;3000
and I want to use a DataSet object,the data like below:
country,month,amount
CN,2014-01,1000
CN,2014-02,2000
CN,2014-02,3000
and there are some points I need to say, in KPI_LOOKUP, the columnheader is not fixed length, that is to say ,the data in table is possible such as "1, country; month; amcount" and there is also another possible "1, country; month; quarter; quantity; amount" and so on .
and kpi_lookup_detail,the content data is also like this.
who can help me to this in C#,thanks everyone.
推荐答案
最简单的方法是使用DataAdapter
这是一个MSDN页面，里面有一些很好的例子。
从DataAdapter填充数据集 [ ^ ]
正如解决方案2中所指出的，在一列中有多个字段是糟糕的数据库设计。
您还需要规范化表格。
有关数据库规范化的更多信息，请参阅以下链接：
维基百科：数据库规范化 [ ^ ]
规范化教程 [ ^ ]
数据库规范化过程 [ ^ ]
The easiest way is to use aDataAdapter
Here is a MSDN page with some good examples.
Populating a DataSet from a DataAdapter[^]
As pointed out in Solution 2, having multiple fields in one column is bad database design.
You also need to normalize your tables.
See these links for more information about Database Normalization:
Wikipedia: Database normalization[^]
Normalization Tutorial[^]
The Database Normalization Process[^]
如上一个答案中所述，使用DataAdapter将是一种填充数据表的简便方法。要开始使用，您可以从 http://www.oracle.com/下载ODP.NET technetwork / topics / dotnet / index-085163.html [ ^ ]
ODP.NET包含从C＃使用托管客户端Oracle客户端所需的类。例如，您可以将这些类用于任务：
- OracleConnection [ ^ ]
- OracleCommand [ ^ ]
- OracleDataAdapter [ ^ ]
但是，在开始实施之前，我建议你重新考虑表的设计。
As said in the previous answer, using DataAdapter would be an easy way to fill a data table. In order to get started you can download ODP.NET from http://www.oracle.com/technetwork/topics/dotnet/index-085163.html[^]
ODP.NET contains the necessary classes to use managed client Oracle client from C#. For example you would use these classes for the task:
- OracleConnection[^]
- OracleCommand[^]
- OracleDataAdapter[^]
However, before jumping into implementation, I would suggest you reconsidering the design of the tables.
KPI_LOOKUP，列标题不是固定的len gth，也就是说，表中的数据是可能的，例如1，country; month; amcount，还有另一种可能的1，country; month; quarter; quantity; amount等等。
和kpi_lookup_detail，内容数据也是这样的。
KPI_LOOKUP, the columnheader is not fixed length, that is to say ,the data in table is possible such as "1,country;month;amcount" and there is also another possible "1,country;month;quarter;quantity;amount" and so on .
and kpi_lookup_detail,the content data is also like this.
在我看来，这个设计会让你在未来。我不会在单个分隔列中使用可变数量的信息，而是使用单独的表。现在这个表每个标题会有一行（你现在在一个单独的列表中有一行）。然后这些值将引用此表中的行。
In my opinion this design is going to cause you extra work in the future. Instead of using variable amount of information in a single, delimited column, I would use a separate table. Now this table would have one row per each header (the ones you now have in a separated list). The values then would reference rows in this table.
是的，技术性的东西基于OracleConnection，OracleCommand，OracleDataAdapter，
但这里我不知道关心详细的技术问题，这是我关于如何构建DataSet的解决方案，
并欢迎大家提出一些意见
1.将列标题分割为';'，然后将列标题存储到字符串数组中
2.因为外键约束，我们可以得到相应的数据，之后，拆分为';'
3.使用从1到content.rows.count的变量给出数据内容行的循环;
4.构造DataRow变量然后将变量插入DataSet
5.返回数据集
yes ,the technical things are based on the OracleConnection,OracleCommand,OracleDataAdapter,
but here I don't care the detailed technical things,here is my Solution about how to construct the DataSet,
and welcome everyone can give some comments
1. to get the columnheader split by ';' ,then store the column header to an string array
2. because the foreign key constraint, we can get the corresponding data,after that, split by ';'
3. give a loop in data content row using the variable from 1 to content.rows.count;
4. construct DataRow variable then insert the variable into DataSet
5. return the DataSet
public DataSet constructionDataSet(int kpi_lookup_id)
{
DataSet customDs = new DataSet("kpi_lookup");
DataTable customDt = customDs.Tables.Add("kpi");
string strColumnHeader = this.getColumnHeader(kpi_lookup_id);
string[] strListArray = this.getArrayViaString(strColumnHeader);
int ii = 0;
foreach (string m in strListArray)
{
customDt.Columns.Add(m, typeof(string));
ii = ii + 1;
}
DataSet ds = this.GetKPIContent(kpi_lookup_id);
foreach (DataRow dr in ds.Tables[0].Rows) //Tables[0] actually is the customDt
{
string[] contentArray = dr.ItemArray[0].ToString().Split(';');
DataRow newCustomersRow = customDs.Tables["kpi"].NewRow();
for (int j = 0; j < contentArray.Length; j++)
{
newCustomersRow[j] = contentArray[j];
}
customDt.Rows.Add(newCustomersRow);
}
return customDs;
}
============================== ================================================== ======
三个功能事件如下：
======================================================================================
three function events are below:
public string getColumnHeader(int kpi_lookup_id)
{
string strSql = @"select 'ID'||columnheader AS columnheader
from kpi_lookup where id = " + kpi_lookup_id;
string strColumnHeader;
OracleConnection conn = this.DBCon();
conn.Open();
OracleDataAdapter oda = new OracleDataAdapter(strSql, conn);
DataSet ds = new DataSet();
oda.Fill(ds);
strColumnHeader = ds.Tables[0].Rows[0][0].ToString();
ds.Dispose();
return strColumnHeader;
}
public string[] getArrayViaString(string columnHeader)
{
string[] strArrayColumnHeaderList = columnHeader.Split(';');
return strArrayColumnHeaderList;
}
public DataSet GetKPIContent(string sqlText)
{
OracleConnection conn = DBCon();
conn.Open();
OracleDataAdapter oda = new OracleDataAdapter(sqlText, conn);
DataSet ds = new DataSet();
oda.Fill(ds);
conn.Close();
return ds;
}
public DataSet GetKPIContent(int kpi_lookup_id)
{
string strSql = "select id||content as content from
kpi_lookup_detail where kpi_lookup_id ="+kpi_lookup_id +" order by id";
DataSet ds = this.GetKPIContent(strSql);
return ds;
}
这篇关于如何将数据库读入数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
