cannot read multiple rows from sqldatareader
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))
{
开发者_Python百科 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;
}
}
}
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);
}
}
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;
}
}
}
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);
}
}
精彩评论