Generate a datatable based on user defined types and the parameter should be stored procedure.According to the stored procedure we have to create datatable dynamically.
for ex:-
create DataTable(parameters)
{
}
output will be like this:
Datatable dt=new Datatable();
dt.Columns.Add("Colname",typeof(datatype)) ;
dt.Columns.Add("Colname",typeof(datatype)) ;//According to the col name
dt.Rows.Add(value,value);
Ramesh MaruthiPosted Jul 24, 2014, 1:05 PM
{
SqlCommand command = new SqlCommand(storeProcedureName, con) { CommandType = CommandType.StoredProcedure };
foreach (SqlParameter param in parameters)
command.Parameters.Add(param);
command.Parameters.Add(new SqlParameter("@retval", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue });
return command;
}
private static DataSet DataSetFromStoredProcedure(string connectionString, string storeProcedureName, params SqlParameter[] parameters)
{
DataSet dataSet;
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand command = GetStoredProcedureCommand(con, storeProcedureName, parameters))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(command);
dataSet = new DataSet();
da.Fill(dataSet);
con.Close();
}
return dataSet;
}
private static DataTable DataTableFromStoredProcedure(string connectionString, string storeProcedureName, params SqlParameter[] parameters)
{
DataSet dataSet = DataSetFromStoredProcedure(connectionString, storeProcedureName, parameters);
if (dataSet.Tables.Count < 1 || dataSet.Tables[0] == null)
return null;
return dataSet.Tables[0];
}