public DataTable GetDataTableFromProcedure(StoredProcedureWithParams storedProcedureWithParams)
{
try
{
using (SqlConnection sqlconnection = new SqlConnection(this.connectionString))
{
using (SqlCommand cmd = new SqlCommand(storedProcedureWithParams.procedurename, sqlconnection))
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in storedProcedureWithParams.sqlParameterCollection)
{
cmd.Parameters.Add(parameter);
}
sqlconnection.Open();
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataTable);
sqlconnection.Close();
return(dataTable);
//cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
return(new DataTable());
}
}
public IEnumerable <IDataRecord> GetDataReaderFromProcedure(StoredProcedureWithParams storedProcedureWithParams)
{
using (SqlConnection sqlconnection = new SqlConnection(this.connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(storedProcedureWithParams.procedurename, sqlconnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(storedProcedureWithParams.sqlParameterCollection);
sqlconnection.Open();
sqlconnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
yield return(reader);
}
sqlconnection.Close();
}
}
}
public IEnumerable <TodoList> GetAllByUsername(string userName)
{
var proc = new StoredProcedureWithParams("TodoListGetAll");
proc.AddParam(new SqlParameter
{
Value = userName,
ParameterName = "UserName",
DbType = DbType.String
});
DataTable dt = this.dataBase.GetDataTableFromProcedure(proc);
IEnumerable <TodoList> todoListCollection = dt.AsEnumerable().Select(row =>
new TodoList
{
TODOXML = row["list_data"].ToString()
});
return(todoListCollection);
}
public void ExecuteNonQueryProcedure(StoredProcedureWithParams storedProcedureWithParams)
{
try
{
using (SqlConnection sqlconnection = new SqlConnection(this.connectionString))
{
using (SqlCommand cmd = new SqlCommand(storedProcedureWithParams.procedurename, sqlconnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(storedProcedureWithParams.sqlParameterCollection);
sqlconnection.Open();
cmd.ExecuteNonQuery();
sqlconnection.Close();
}
}
}
catch (Exception)
{
throw;
}
}
public bool ExecuteScalarProcedure(StoredProcedureWithParams storedProcedureWithParams)
{
throw new NotImplementedException();
}
