I do not know why do not get data back - Ado.net

Nepethya Rana

My sqlQuery = string sql = "SELECT StoreType from location where locationId in (@pRowValue) order by region, district, locationId";

This is my code:

string storeNum = "";

foreach (DataRow row in dt.Rows)

{

storeNum += row["store"] + ",";

//storeNum += string.Format(@"'{0}',", row["store"]);

}

storeNum = storeNum.Remove(storeNum.LastIndexOf(','));

SqlDataAdapter da = new SqlDataAdapter(sql, con);

da.SelectCommand.Parameters.AddWithValue("@pRowValue", storeNum);

da.Fill(dtStoreType);

but I do not get any records back?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nepethya Rana9y ago
I achieved this by creating extension method which i found on somewhere. this might be helpful for someone in future. I wish i had original link.

public static class SqlCommandExt
{
public static SqlParameter[] AddArrayParameters(this SqlCommand cmd, IEnumerable values, string paramNameRoot, int start = 1, string separator = ", ")
{
var parameters = new List();
var parameterNames = new List();
var paramNbr = start;
foreach (var value in values)
{
var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
parameterNames.Add(paramName);
parameters.Add(cmd.Parameters.AddWithValue(paramName, value));
}
cmd.CommandText = cmd.CommandText.Replace("{" + paramNameRoot + "}", string.Join(separator, parameterNames));
return parameters.ToArray();
}
}

-2
Devendra Kumar9y ago
You have to use Transaction alongwith SqlCommand, so that if the process failed, it can be rollback
0
Nilesh Shah9y ago
ur code seems to be not in order.

upload ur code .cs file as zip so we can see actual code.
u set the query value in sql variable, place a debug point at this line and see what do you get in it.
da.SelectCommand.Parameters.AddWithValue("@pRowValue", storeNum);
you should be able to copy the value from SelectCommand and execute it directly in ur DB
0