I have created sql query which works in normal dataset, When iam trying to use same query using store procedure, result does not fetch the value.
string datatypeval="'first name','address'";
string FieldNames1 = "select FId,DataTypeName from tbl_fieldname where DataTypeName in(" + datatypeval + ");";
Above query works perfectly.
When using store procedure, it doesnt fetch output value.
DataTable dt1 = dataval(datatypeval);
public static DataTable dataval(string datatypeval)
{
try
{
DataTable dt1;
string[] ColumnName = new string[1];
string[] values = new string[1];
ColumnName[0] = "@dataval"; values[0] = datatypeval;
dt1 = returnDatatable("datatype_Details", ColumnName, values);
return dt1;
}
catch (Exception e)
{
throw e;
}
}
public static DataTable returnDatatable(string SPName, string[] ParameterName, string[] Values)
{
DataTable ds = new DataTable();
try
{
openDBConnection();
SqlConnection mssqlConn = mssqlConnection;
SqlCommand Cmd = new SqlCommand(SPName, mssqlConn);
SqlDataAdapter da = new SqlDataAdapter(Cmd);
Cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < ParameterName.Length; i++)
{
Cmd.Parameters.AddWithValue(ParameterName[i], Values[i]);
}
da.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
closeDBConnection();
}
return ds;
}
My SP Code:
ALTER PROCEDURE [dbo].[datatype_Details]
(
@dataval VARCHAR
)
AS
BEGIN
select FId,DataTypeName from tbl_fieldname where DataTypeName in(@dataval)
END
What I have tried:
DataTable dt1 = dataval(datatypeval);
public static DataTable dataval(string datatypeval)
{
try
{
DataTable dt1;
string[] ColumnName = new string[1];
string[] values = new string[1];
ColumnName[0] = "@dataval"; values[0] = datatypeval;
dt1 = returnDatatable("datatype_Details", ColumnName, values);
return dt1;
}
catch (Exception e)
{
throw e;
}
}
public static DataTable returnDatatable(string SPName, string[] ParameterName, string[] Values)
{
DataTable ds = new DataTable();
try
{
openDBConnection();
SqlConnection mssqlConn = mssqlConnection;
SqlCommand Cmd = new SqlCommand(SPName, mssqlConn);
SqlDataAdapter da = new SqlDataAdapter(Cmd);
Cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < ParameterName.Length; i++)
{
Cmd.Parameters.AddWithValue(ParameterName[i], Values[i]);
}
da.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
closeDBConnection();
}
return ds;
}