I'm trying to read an xml file into a SqlDataReader.
I have the following xml file:
<?xml version="1.0" encoding="utf-8" ?>
<Table>
<ID >1</ID>
<Name >Joe</Name>
<Street1 >123 Main Street</Street1>
<Street2 >Apt #1</Street2>
<City >City</City>
<County >County</County>
<State >State</State>
<Zipcode >Zip</Zipcode>
<Province >Province</Province>
<Country >Country</Country>
<Phone >1234567890</Phone>
<Fax >1234567890</Fax>
</Table>
I have the following xsd file:
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" />
<xs:element name="Name" type="xs:string" />
<xs:element name="Street1" type="xs:string" />
<xs:element name="Street2" type="xs:string" />
<xs:element name="City" type="xs:string" />
<xs:element name="County" type="xs:string" />
<xs:element name="State" type="xs:string" />
<xs:element name="Zipcode" type="xs:string" />
<xs:element name="Province" type="xs:string" />
<xs:element name="Country" type="xs:string" />
<xs:element name="Phone" type="xs:unsignedInt" />
<xs:element name="Fax" type="xs:unsignedInt" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
(You can assume the xml, and xsd files are correct, hopefuly I didn't typo when I c/p).
I've
written the following C# code to read in the xml into a SQLDataReader.
I'd like the column names to corrospond to the name of the nodes(for
example "ID", "Name", "Street1" etc...).
Code Sample #1
DataTable table = new DataTable("Table");
SqlDataAdapter adaptor = new SqlDataAdapter();
SqlDataReader reader = null;
string schema = "RelativePath/to/Schema.xsd";
string xml = "RelativePath/to/File.xml";
table.ReadXmlSchema(schema);
table.ReadXml(xml);
adaptor.Fill(table);
table.Load(reader);
I'm getting the following error:
System.InvalidOperationException: The SelectCommand property has not been initialized before calling 'Fill'.
at
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
Code Sample #2
string xml = "C:/Absolute/Path/To/XmlFile.xml";
string SQL = "SELECT * FROM Tables FOR XML";
SqlConnection connection = new SqlConnection();
connection.ConnectionString = xml;
SqlCommand command = connection.CreateCommand();
command.CommandText = SQL;
SqlDataReader reader = (SqlDataReader)command.BeginExecuteXmlReader();
Error:
System.ArgumentException:
Format of the initialization string does not
conform to specification starting at index 0.
at
System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString,
Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String&
keyname, String& keyvalue)
at
System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable,
String connectionString, Boolean buildChain, Hashtable synonyms, Boolean
firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String
connectionString, Hashtable synonyms, Boolean useOdbcRules)
at
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
I'm trying to create a SqlDataReader by reading from an xml file to create a set of data. The
end goal is to run this as a test case in Gallio, to populate a
SQLDataReader with data that causes a bug(instead of going to the db to
get the error causing data).
Something like the following:
Assert.AreEqual(1, Convert.ToInt32(reader["ID"])); (and so on...)
The class I'm test against looks something like
public class MyClass
{
private int id;
... other class members
public int ID { get { return id; } }
... other properties
MyClass(SqlDataReader rdr)
{
id = rdr.GetInt32("ID");
... assign other attributes
}
// default constructor is empty
}