locked
SQLDataReader Issue RRS feed

  • Question

  • User-1738841732 posted

    the below Query gets executed in my SQL DB

    WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/Shared.ViewModels.Warehouse')

    select
    t.bondId,
    t.bondName,
    x.inf.value('(ExpDate)[1]', 'datetime2') as ExpDate
    from MyTable as t
    cross apply
    (
    select cast(t.BondXMLinfo as xml) as BondXMLinfo
    ) as ca
    cross apply ca.BondXMLinfo.nodes('/CompanyInfo') as x(inf)


    but, When I try to execute the same SQL Query using SqlDataReader from C#.NET, i get empty output in DataTable.


    using (SqlCommand myCommand = new SqlCommand(<MYSQLQuery>, connection, transaction))
    {
    using (SqlDataReader myReader = myCommand.ExecuteReader())
    {
    DataTable myTable = new DataTable();
    myTable.Load(myReader);

    Tuesday, July 21, 2020 2:21 PM

All replies

  • User-939850651 posted

    Hi mansooraabid,

    According to your description, I tried but could not reproduce your problem.

    What are the results you expect from the query? As written in your code, the DataTable is empty means that the SqlDataReader is empty.

    You could use the debugging tool to check whether the result you expect exists in the SqlDataReader object.

    string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();
                string query = "select * from tableTest";
                using (SqlConnection conn = new SqlConnection(conStr))
                {
                    conn.Open();
                    SqlTransaction transaction = conn.BeginTransaction();
                    using (SqlCommand cmd = new SqlCommand(query, conn, transaction))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            DataTable MyTable = new DataTable();
                            MyTable.Load(reader);
                            //
                            Console.WriteLine("");
                        }
                    }
                }

    Result:

    I recommend that you could use SqlDataAdapter to fill the data table, like this:

    using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) {
                            DataTable MyTable = new DataTable();
                            sda.Fill(MyTable);
                        }

    Hope this can help you.

    Best regards,

    Xudong Peng

    Wednesday, July 22, 2020 6:05 AM