XML转换为数据表——返回空白单元格
本文关键字：返回 单元格 空白 数据表 转换 XML | 更新日期: 2023-09-27 18:03:49
这将是一个很长的问题-我已经花了一整天的时间试图解决这个问题，所以也许你们可以帮助!我正在尝试通过允许数据库结构在呈现新结构(以XML格式)时进行更改来证明我的数据库应用程序的未来。
目前，我正设法将数据库结构导出为XML。请看下面的工作代码:
public void generateXMLStructureCompactDB(string fileName)
{
DataTable table = new DataTable();
int position;
FileStream fsWrite;
StreamWriter sw;
string version = "1.1.1.0";
table.Columns.Add("TableName", Type.GetType("System.String"));
table.Columns.Add("ColName", Type.GetType("System.String"));
table.Columns.Add("Position", Type.GetType("System.Int32"));
table.Columns.Add("DataType", Type.GetType("System.String"));
table.Columns.Add("MaximumLength", Type.GetType("System.Int32"));
table.Columns.Add("Precision", Type.GetType("System.Int32"));
table.Columns.Add("Scale", Type.GetType("System.Int32"));
table.Columns.Add("Nullable", Type.GetType("System.Boolean"));
table.Columns.Add("Identity", Type.GetType("System.Boolean"));
table.Columns.Add("IdentitySeed", Type.GetType("System.Int32"));
table.Columns.Add("IdentityIncrement", Type.GetType("System.Int32"));
string sql = "select Table_Name, Column_Name, Ordinal_Position, Data_Type, " +
"Character_Maximum_Length, Numeric_Precision, Numeric_Scale, Is_Nullable, " +
"case when AutoInc_Seed is null then 0 else 1 end as IS_IDENTITY, " +
"AutoInc_Seed, AutoInc_Increment from information_schema.columns";
runSQL(sql, out table);
if (File.Exists(fileName))
File.Delete(fileName);
fsWrite = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
sw = new StreamWriter(fsWrite, Encoding.ASCII);
XmlWriter writer = new XmlTextWriter(sw);
writer.WriteStartDocument(false);
writer.WriteRaw(Environment.NewLine);
writer.WriteComment("DBVersion=" + DBVersion + ", created=" + DateTime.Now.ToString());
writer.WriteRaw(Environment.NewLine);
writer.Close();
sw.Close();
fsWrite.Close();
fsWrite = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.ReadWrite);
sw = new StreamWriter(fsWrite, Encoding.ASCII);
DataTable indexes = new DataTable();
indexes.Columns.Add("TableName", Type.GetType("System.String"));
indexes.Columns.Add("Schema", Type.GetType("System.String"));
indexes.Columns.Add("IndexName", Type.GetType("System.String"));
indexes.Columns.Add("Clustered", Type.GetType("System.Boolean"));
indexes.Columns.Add("Unique", Type.GetType("System.Boolean"));
indexes.Columns.Add("IndexColName", Type.GetType("System.String"));
sql = "select Table_Name, Table_Schema, Index_Name, [Clustered], [Unique], " +
"Column_Name from information_schema.indexes";
runSQL(sql, out indexes);
table.TableName = "Tables";
indexes.TableName = "Indexes";
table.WriteXml(sw);
sw.WriteLine("");
indexes.WriteXml(sw);
sw.Close();
fsWrite.Close();
}
下面是我生成的XML文件(实际版本要大得多，但我把它简化为一个表和一个索引):
<?xml version="1.0" encoding="us-ascii" standalone="no"?>
<!--DBVersion=1.0.0.0, created=14/03/2013 13:18:20-->
<DocumentElement>
<Tables>
<TABLE_NAME>ContactLog</TABLE_NAME>
<COLUMN_NAME>ContactLogId</COLUMN_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<DATA_TYPE>int</DATA_TYPE>
<NUMERIC_PRECISION>10</NUMERIC_PRECISION>
<IS_NULLABLE>NO</IS_NULLABLE>
<IS_IDENTITY>1</IS_IDENTITY>
<AUTOINC_SEED>1</AUTOINC_SEED>
<AUTOINC_INCREMENT>1</AUTOINC_INCREMENT>
</Tables>
<Tables>
<TABLE_NAME>ContactLog</TABLE_NAME>
<COLUMN_NAME>CustomerId</COLUMN_NAME>
<ORDINAL_POSITION>2</ORDINAL_POSITION>
<DATA_TYPE>int</DATA_TYPE>
<NUMERIC_PRECISION>10</NUMERIC_PRECISION>
<IS_NULLABLE>YES</IS_NULLABLE>
<IS_IDENTITY>0</IS_IDENTITY>
</Tables>
<Tables>
<TABLE_NAME>ContactLog</TABLE_NAME>
<COLUMN_NAME>ContactDate</COLUMN_NAME>
<ORDINAL_POSITION>3</ORDINAL_POSITION>
<DATA_TYPE>datetime</DATA_TYPE>
<NUMERIC_PRECISION>23</NUMERIC_PRECISION>
<NUMERIC_SCALE>3</NUMERIC_SCALE>
<IS_NULLABLE>YES</IS_NULLABLE>
<IS_IDENTITY>0</IS_IDENTITY>
</Tables>
<Tables>
<TABLE_NAME>ContactLog</TABLE_NAME>
<COLUMN_NAME>ContactTypeId</COLUMN_NAME>
<ORDINAL_POSITION>4</ORDINAL_POSITION>
<DATA_TYPE>int</DATA_TYPE>
<NUMERIC_PRECISION>10</NUMERIC_PRECISION>
<IS_NULLABLE>YES</IS_NULLABLE>
<IS_IDENTITY>0</IS_IDENTITY>
</Tables>
<Tables>
<TABLE_NAME>ContactLog</TABLE_NAME>
<COLUMN_NAME>AuditText</COLUMN_NAME>
<ORDINAL_POSITION>5</ORDINAL_POSITION>
<DATA_TYPE>nvarchar</DATA_TYPE>
<CHARACTER_MAXIMUM_LENGTH>2048</CHARACTER_MAXIMUM_LENGTH>
<IS_NULLABLE>YES</IS_NULLABLE>
<IS_IDENTITY>0</IS_IDENTITY>
</Tables>
<Tables>
<TABLE_NAME>ContactType</TABLE_NAME>
<COLUMN_NAME>ContactTypeId</COLUMN_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<DATA_TYPE>int</DATA_TYPE>
<NUMERIC_PRECISION>10</NUMERIC_PRECISION>
<IS_NULLABLE>NO</IS_NULLABLE>
<IS_IDENTITY>1</IS_IDENTITY>
<AUTOINC_SEED>1</AUTOINC_SEED>
<AUTOINC_INCREMENT>1</AUTOINC_INCREMENT>
</Tables>
</DocumentElement>
<DocumentElement>
<Indexes>
<TABLE_NAME>ContactLog</TABLE_NAME>
<INDEX_NAME>PK_ContactLog</INDEX_NAME>
<CLUSTERED>false</CLUSTERED>
<UNIQUE>true</UNIQUE>
<COLUMN_NAME>ContactLogId</COLUMN_NAME>
</Indexes>
</DocumentElement>
现在这是读取XML文件的代码部分。XML文件包含两个独立表的列—tables(实际上是列列表)和indexes—索引列表。它根据<DocumentElement>的位置将文件分成两个，然后将两个XML元素放入两个单独的表中。参见下面的代码:
static bool VerifyStructure()
{
FileStream fsWrite;
int i;
StreamWriter sw;
string DBStructureVersion = "0.0.0.0";
string xmlFile;
string s;
string tmp = Environment.GetEnvironmentVariable("TEMP");
if (tmp == "")
tmp = Environment.GetEnvironmentVariable("SystemDrive");
try
{
xmlFile = tmp + @"'dbStructure.xml";
if (File.Exists(xmlFile))
File.Delete(xmlFile);
fsWrite = new FileStream(xmlFile, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
sw = new StreamWriter(fsWrite, Encoding.ASCII);
sw.Write(syntos.Properties.Resources.dbStructure);
sw.Close();
fsWrite.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error reading structure xml file: " + ex.Message);
return false;
}
if (!File.Exists(xmlFile))
{
MessageBox.Show("XML Structure file '" + xmlFile + "' does not exist.");
return false;
}
StreamReader sr = new StreamReader(xmlFile);
s = sr.ReadLine();
while (s != null)
{
if (s.Contains("DBVersion="))
{
i = s.IndexOf("DBVersion=");
DBStructureVersion = s.Substring(i + 10);
i = DBStructureVersion.IndexOf(",");
DBStructureVersion = DBStructureVersion.Substring(0, i);
break;
}
s = sr.ReadLine();
}
sr.Close();
// Split the remaining XML file into two: table datatable file and index datatable file
// Write out to file1 all the table information and to file2 all the index information
string file1path;
string file2path;
try
{
file1path = tmp + @"'tables.xml";
file2path = tmp + @"'indexes.xml";
if (File.Exists(file1path))
File.Delete(file1path);
if (File.Exists(file2path))
File.Delete(file2path);
FileStream fsw1 = new FileStream(file1path, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
FileStream fsw2 = new FileStream(file2path, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
StreamWriter sw1 = new StreamWriter(fsw1, Encoding.ASCII);
StreamWriter sw2 = new StreamWriter(fsw2, Encoding.ASCII);
// Create a file containing just the table / column definitions
sr = new StreamReader(xmlFile);
s = sr.ReadToEnd();
sr.Close();
i = s.IndexOf("<DocumentElement>");
s = s.Substring(i);
i = s.IndexOf("</DocumentElement>");
s = s.Substring(0, i + 18);
sw1.Write(s);
sw1.Close();
fsw1.Close();
// Create a file containing the index column definitions
sr = new StreamReader(xmlFile);
s = sr.ReadToEnd();
sr.Close();
i = s.IndexOf("</DocumentElement>");
s = s.Substring(i + 18);
sw2.Write(s);
sw2.Close();
fsw2.Close();
sr.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error producing the table and index XML file. " + ex.Message);
return false;
}
DataTable table = new DataTable();
table.Columns.Add("TableName", Type.GetType("System.String"));
table.Columns.Add("ColName", Type.GetType("System.String"));
table.Columns.Add("Position", Type.GetType("System.Int32"));
table.Columns.Add("DataType", Type.GetType("System.String"));
table.Columns.Add("MaximumLength", Type.GetType("System.Int32"));
table.Columns.Add("Precision", Type.GetType("System.Int32"));
table.Columns.Add("Scale", Type.GetType("System.Int32"));
table.Columns.Add("Nullable", Type.GetType("System.Boolean"));
table.Columns.Add("Identity", Type.GetType("System.Boolean"));
table.Columns.Add("IdentitySeed", Type.GetType("System.Int32"));
table.Columns.Add("IdentityIncrement", Type.GetType("System.Int32"));
table.TableName = "Tables";
DataTable indexes = new DataTable();
indexes.Columns.Add("TableName", Type.GetType("System.String"));
indexes.Columns.Add("Schema", Type.GetType("System.String"));
indexes.Columns.Add("IndexName", Type.GetType("System.String"));
indexes.Columns.Add("Clustered", Type.GetType("System.Boolean"));
indexes.Columns.Add("Unique", Type.GetType("System.Boolean"));
indexes.Columns.Add("IndexColName", Type.GetType("System.String"));
indexes.TableName = "Indexes";
// Read in the XML for the table / columns
sr = new StreamReader(file1path);
table.ReadXml(sr);
sr.Close();
if (table.Rows.Count == 0)
{
MessageBox.Show("No loadable table rows found in XML file");
sw.Close();
fsWrite.Close();
return false;
}
sr = new StreamReader(file2path);
indexes.ReadXml(sr);
sr.Close();
if (indexes.Rows.Count == 0)
{
MessageBox.Show("No loadable index rows found in XML file");
sw.Close();
fsWrite.Close();
return false;
}
DbFunctions.BackupDatabase();
bool success1 = CreateTempTables(table);
return true;
}
通过使用断点和调试语句，我可以看到表被填充了正确数量的行和列——然而所有这些都是空的。只是空的细胞。我已经习惯了table.Rows.Count，看到这个表中实际上有行。
如果有人能看看我是否写对了，我将永远感激不尽!提前感谢:)
编辑:我已经用不同的XML表测试了它，它可以工作。我生成XML文件的方式一定有问题，但我看不出是什么。
我将在允许的情况下尽快回答这个问题;)
问题总结:当系统生成的XML文件(第一个代码块)再次导入程序(第三个代码块)时，行和列都存在，但所有单元格都是空白的。
列名不匹配-名称区分大小写。您可以在空表上导入XML，该方法将为您创建列。http://msdn.microsoft.com/es-es/library/system.data.datatable.readxml (v = vs.100) . aspx