none
XML into a SQLDataReader RRS feed

  • Question

  • 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>
            <
    Street3 ></Street3>
            <
    Street4 ></Street4>
            <
    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="Street3" />
                <
    xs:element name="Street4" />
                <
    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 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...).  

    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)


    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 causeing data).

    Something like the following:
    Assert.AreEqual(1, Convert.ToInt32(reader["ID"]));  (and so on...)

    Wednesday, March 25, 2009 3:42 PM

Answers

  • Your only option unless you round-trip via a database is to change your framework to take DbDataReader - the only advantage of using SqlDataReader would give you is calling GetSql<DataType>(int) methods.


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Friday, March 27, 2009 12:29 AM
    Moderator

All replies

  • SqlDataReader will only pull data from SQL Server, not from a DataTable.

    The exception you are getting is because you did not initialize the SqlDataAdapter.Command with the query to execute against a SQL Server.

    If you want a DataReader against the DataTable, you can use the 
           DataSet.CreateDataReader
    or    DataTable.CreateDataReader

    but then you don't need an adapter.

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Thursday, March 26, 2009 3:07 AM
    Moderator
  • Thanks for the info, however, I'm not sure what the query would be given that I'm querying an xml set, not a Database.  How would I get a SqlDataReader from DataSet.CreateDataReader or DataTableReader.CreateDataReader, both methods return a DataTableReader, but I need a SqlDataReader specifically.
    Thursday, March 26, 2009 4:07 PM
  • The only way to get a SqlDataReader would be to stick the data into a SQL Server and query for it.
    What is it that you specifically need SqlDataReader for?
    Have you considered consuming the base class: DbDataReader?


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Thursday, March 26, 2009 7:58 PM
    Moderator
  • I'm building a unit test framework and some of (actually alot) our classes have constructors that take SqlDataReader as a parameter(the default constructor doesn't do anything).  So I would like to manually create a SqlDataReader with data to pass into the object when it's instatiated.  Something like the following:

    public class Person
    {
       private int age;
       private string lastName;
       private string firstName;

       public string Age { get { return age; } }
       public string LastName { get { return lastName; } }
       public string FirstName { get { return firstName; } }

       public Person(){}

       public Person(SqlDataReader reader)
       {
            age= reader.GetInt32("Age");
            lastName = reader.GetString("LastName");
            firstName = reader.GetString("FirstName");
       }
    }

    XML File that holds data:
    <Test id="FirstTest">
      <Age>33</Age>
      <LastName>Smith</LastName>
      <FirstName>Joe</FirstName>
    </Test>
    ... (there could be multiple tests)

    public class PersonTest
    {
        public void Test1()
        {
            XmlDocument doc = new XmlDocument();
            XmlTextReader xmlReader = new XmlTextReader(Path\To\Xml\XmlFile.xml);
            xmlReader.Read();
            doc.Load(xmlReader);
           
            string xpath = "Test[@id=\'FirstTest\']";
            XmlNode node = doc.SelectSingleode(xpath); // this should return just the "Test" tag with attribute "FirstTest"

          SqlDataReader reader = null
          // The following doesn't work, but it may give you the idea of what I'm trying to do.
          // By "creating" the columns in the SqlDataReader, and assigning the values, I can pass it in and populate the fields.
          reader["Age"] = node["Age"].InnerText;
          reader["LastName"] = node["LastName"].InnerText;
          reader["FirstName"] = node["FirstName"].InnerText;

          Person p = new Person(reader);
          Assert.AreEqual(33, Person.Age);
          Assert.AreEqual("Smith", Person.LastName);
          Assert.AreEqual("Joe", Person.FirstName);
        }
    }

    I could put the data into the database as you mentioned, but I was hoping to avoid a trip to the database, also it would involve insuring the test cases(there could be multiple test cases) are in the database when the tests run.  Hope this helps.


    Thursday, March 26, 2009 10:05 PM
  • Your only option unless you round-trip via a database is to change your framework to take DbDataReader - the only advantage of using SqlDataReader would give you is calling GetSql<DataType>(int) methods.


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Friday, March 27, 2009 12:29 AM
    Moderator
  • I think your right, I'll go back and take a look at my code to see what effort it will take to make the change.  Thanks for the feedback.
    Friday, March 27, 2009 6:58 PM