public Category GetCategoryById(short id)
{
/*using (IDbCommand command = connection.CreateCommand())
* {
* command.CommandText = "SELECT * FROM Category WHERE CategoryId = @Id";
*
* IDbDataParameter parameter = command.CreateParameter();
* parameter.Value = id;
* parameter.ParameterName = "@Id";
* command.Parameters.Add(parameter);
*
* using (IDataReader reader = command.ExecuteReader())
* {
* if (reader.Read())
* {
* Fetch(reader);
* }
* return null;
* }
* }*/
string sql = "SELECT * FROM Category WHERE CategoryId = @Id";
DynamicParameter parameter = new DynamicParameter();
parameter.Add("@Id", id);
return(FetchOne(sql, parameter));
}
public int Add(Category obj)
{
string sql = "INSERT INTO Category VALUES (@Name)";
DynamicParameter parameter = new DynamicParameter();
parameter.Add("@Name", obj.Name);
return(Save(sql, parameter));
}
public List <Product> GetProductsByCategoryId(short id)
{
string sql = "SELECT * FROM Product WHERE CategoryId = @Id";
DynamicParameter parameter = new DynamicParameter();
parameter.Add("@Id", id);
return(FetchAll(sql, parameter));
}
protected int Save(string sql, DynamicParameter parameter, CommandType commandType = CommandType.Text)
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
parameter.Handle(command);
return(command.ExecuteNonQuery());
}
}
protected T FetchOne(string sql, DynamicParameter parameter, CommandType commandType = CommandType.Text)
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
parameter.Handle(command);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Fetch(reader);
}
return(default(T));
}
}
}
protected List <T> FetchAll(string sql, DynamicParameter parameter, CommandType commandType = CommandType.Text)
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = commandType;
if (parameter != null)
{
parameter.Handle(command);
}
using (IDataReader reader = command.ExecuteReader())
{
List <T> list = new List <T>();
while (reader.Read())
{
list.Add(Fetch(reader));
}
return(list);
}
}
}
