SQLDataReader Issue

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);

Answer 1

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