HI,
I have created StoredProcedure in Mysql -
CREATE PROCEDURE `SP_ValidateUser`(_action varchar(10), _userid varchar(150), _password varchar(100))
BEGIN
if _action = 'Validate' then
Select UserId, Password from table
where UserId = _userid and Password = _password;
end if;
END
and for calling SP i want to create a common Function so I have created like -
public DataTable GetCommonData(string SP, string action, string id, string str1, string str2)
{
DataTable dt = new DataTable();
strcon.Open();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
cmd.Connection = strcon;
cmd.CommandText = SP;
cmd.CommandType = CommandType.StoredProcedure;
string[] strcol = {action,id,str1,str2};
foreach (string str in strcol)
{
cmd.Parameters.AddWithValue("_action", str);
}
ad.Fill(dt);
ad.Dispose();
return dt;
}
for first value that is "action", it is taking value but while coming on second value it is showing error like -
cmd.Parameters.AddWithValue("_action", str);
"_action" already used ...
i can define saperate this line for each value but
i don't want to use many lines...
is there any issue in my code?
Please let me know.....
Loading
Iftikar HussainPosted Aug 22, 2013, 6:16 AM
rachayita jaiswalPosted Aug 22, 2013, 6:12 AM
but i dont want that.
i am sharing my vb.net with mysql code -
this is my common function -
this is my code behind data - two methods with different SP and different parameters but i am using same function -
first -
dtPOI = ReportsClassObj.getCommonFunction("SP_POIDetails(?,?,?,?,?)", "Select", UserName, "")
Second -
dtAlertsData = ReportsClassObj.getCommonFunction("call SP_Settings(?,?,?)", "SelectLA", drRow("vehicleRegNo"), "")
Like that i want in my c# also,
may i am not able to explain my question properly,if so please tell me
Iftikar HussainPosted Aug 22, 2013, 6:01 AM
public DataTable GetCommonData(string SP, SqlParameter[] sqlParameters)
{
DataTable dt = new DataTable();
strcon.Open();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
cmd.Connection = strcon;
cmd.CommandText = SP;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(sqlParameters);
ad.Fill(dt);
ad.Dispose();
return dt;
}
Regards,
Iftikar
rachayita jaiswalPosted Aug 22, 2013, 5:37 AM
supose i have one sp for userid and password now 2nd sp for fetching other details
so i cant use directly these lines -
cmd.Parameters.AddWithValue("_userid", id);
cmd.Parameters.AddWithValue("_password", str1);
and this loop i have used one of my vb.net with mysql project, it is working there....
if you didn't get my question exactly please tell me... i ll explain again.
Iftikar HussainPosted Aug 22, 2013, 5:29 AM
You should not use for loop for adding parameter. try like this
public DataTable GetCommonData(string SP, string action, string id, string str1, string str2)
{
DataTable dt = new DataTable();
strcon.Open();
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
cmd.Connection = strcon;
cmd.CommandText = SP;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_action", str);
cmd.Parameters.AddWithValue("_userid", id);
cmd.Parameters.AddWithValue("_password", str1);
ad.Fill(dt);
ad.Dispose();
return dt;
}
Regards,
Iftikar