cannot read multiple rows from sqldatareader

Question

When I query for only one record/row, sqldatareader is giving correct result but when i query for multiple rows, its giving error on the client side. below is my code. please tell me what is the problem here.

   [WebMethod]
    public static string SendMessage(string order)
    {
            string Server = "alyeyey";
            string Username = "apjsjsjs";
            string Password = "jjsjsjs";
            string Database = "Amhshshs";

            string ConnectionString = "Data Source=" + Server + ";";
            ConnectionString += "User ID=" + Username + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + Database;
            string query = "select * from optionsRelation where orderNumber = " + order;//+" OR orderNumber = 17";

            DataTable dt = new DataTable();

            Hashtable sendData = new Hashtable();

        try
            {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    dt.Load(dr);
                }
            }

            //Creating StringBuilder array for storing keys
            StringBuilder[] empKeys = new StringBuilder[4];

            for (int i = 0; i < empKeys.Length; i++)
            {
                empKeys[i] = new StringBuilder();
            }

            //Creating stringbuilder array for storing key values
            StringBuilder[] empDetails = new StringBuilder[4];

            for (int i = 0; i < empDetails.Length; i++)
            {
                empDetails[i] = new StringBuilder();
            }

            //putting datatable data to Keys array i-e empKeys and Values array i-e empDetails array
            int inc = 0;
            int j = 0;
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    empKeys[inc].Append(dc.ColumnName);
                    inc++;
                }
                foreach (DataColumn dc in dt.Columns)
                {
                    empDetails[j].Append(dr[dc]);
                    j++;
                }
            }

            //mapping keys array and values array in hashtable

            for (int k = 0; k < empKeys.Length; k++)
            {
                sendData.Add(empKeys[k].ToString(), empDetails[k].ToString());
            }
            //sendData.Add("orderNum", order);
            JavaScriptSerializer jss = new JavaScriptSerializer();
            string output = jss.Serialize(sendData);
            return output;
        }
        catch (Exception ex)
        {
            return ex.Message + "-" + ex.StackTrace;
        }
    }
}

Answer 1

Something like this? This doesn't solve your client-side error though. It does use paramaters instead of string concatenation.

    string query = "select * from Customer_Order where orderNumber = @ordernumber";

    DataTable dt = new DataTable();

    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.Add(new SqlParameter("ordernumber", ordernumber));
            SqlDataAdapter dr = new SqlDataAdapter(cmd);
            dr.Fill(dt);
        }
    }

Answer 2

error occuring o client side but it is because of data coming from server side. because i have checked sending different values to this client and it is working fine with those values. even when i use query string for only one row like "select * from table where orderNumber = 17", it is working fine. but give error while query for multiple rows. and "order" is the string parameter passed from the jquery ajax call(and i have checked that "order" contains correct string so no problem with that). I am using empKeys and empDetails array to hold datatable values to convert them into hash table because i have to send data in json format to javascript and only with hash table i successfully did this. Below is my whole server side code:

    using System.Web.UI.WebControls;
using System.Web.Services;
using System.Data;
using System.Text;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Collections;
using System.Data.SqlClient;

public partial class orderSearch : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e){}


    [WebMethod]
    public static string SendMessage(string order)
    {
            string Server = "alyeyey";
            string Username = "apjsjsjs";
            string Password = "jjsjsjs";
            string Database = "Amhshshs";

            string ConnectionString = "Data Source=" + Server + ";";
            ConnectionString += "User ID=" + Username + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + Database;
            string query = "select * from optionsRelation where orderNumber = " + order;//+" OR orderNumber = 17";

            DataTable dt = new DataTable();

            Hashtable sendData = new Hashtable();

        try
            {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    dt.Load(dr);
                }
            }

            //Creating StringBuilder array for storing keys
            StringBuilder[] empKeys = new StringBuilder[4];

            for (int i = 0; i < empKeys.Length; i++)
            {
                empKeys[i] = new StringBuilder();
            }

            //Creating stringbuilder array for storing key values
            StringBuilder[] empDetails = new StringBuilder[4];

            for (int i = 0; i < empDetails.Length; i++)
            {
                empDetails[i] = new StringBuilder();
            }

            //putting datatable data to Keys array i-e empKeys and Values array i-e empDetails array
            int inc = 0;
            int j = 0;
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    empKeys[inc].Append(dc.ColumnName);
                    inc++;
                }
                foreach (DataColumn dc in dt.Columns)
                {
                    empDetails[j].Append(dr[dc]);
                    j++;
                }
            }

            //mapping keys array and values array in hashtable

            for (int k = 0; k < empKeys.Length; k++)
            {
                sendData.Add(empKeys[k].ToString(), empDetails[k].ToString());
            }
            //sendData.Add("orderNum", order);
            JavaScriptSerializer jss = new JavaScriptSerializer();
            string output = jss.Serialize(sendData);
            return output;
        }
        catch (Exception ex)
        {
            return ex.Message + "-" + ex.StackTrace;
        }
    }
}

Answer 3

Try this... I think this still has the wrong JSON output but it will give you the idea. See Rick Strahl's article on converting a datatable to json. It's a little more complicated than my example.

            List<Hashtable> sendData = new List<Hashtable>();

            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    Hashtable ht = new Hashtable();
                    ht.Add(dc.ColumnName, dr[dc].ToString());
                    sendData.Add(ht);
                }
            }