開発者みんなが SQL に精通しているとは限らないから、テーブルやビューにアクセス（選択・挿入・更新・削除）するための SQL をテーブルデータゲートウェイがラップして、それらの機能をメソッドで提供する・・・と。
public class ItemsGateway
{
public IDataReader Find(uint id)
{
using (DbConnection connection = CreateConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = @"select * from items where id = ?id";
AddInParameter(command, "?id", DbType.UInt32, id);
return command.ExecuteReader();
}
}
}
public void Insert(string title, string description)
{
using (DbConnection connection = CreateConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = @"insert into items (title, description) values (?title, ?description)";
AddInParameter(command, "?title", DbType.String, title);
AddInParameter(command, "?description", DbType.String, description);
command.ExecuteNonQuery();
}
}
}
public void Update(uint id, string title, string description)
{
using (DbConnection connection = CreateConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = @"update items set title = ?title, description = ?description where id = ?id";
AddInParameter(command, "?id", DbType.UInt32, id);
AddInParameter(command, "?title", DbType.String, title);
AddInParameter(command, "?description", DbType.String, description);
command.ExecuteNonQuery();
}
}
}
public void Delete(uint id)
{
using (DbConnection connection = CreateConnection())
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = @"delete from items where id = ?id";
AddInParameter(command, "?id", DbType.UInt32, id);
command.ExecuteNonQuery();
}
}
}
private void AddInParameter(DbCommand command, string name, DbType type, object value)
{
DbParameter parameter = command.CreateParameter();
parameter.DbType = type;
parameter.ParameterName = name;
parameter.Value = value;
command.Parameters.Add(parameter);
}
// 接続文字列は環境に合わせて変更する必要アリ！！
private const string CONNECTION_STRING = @"Persist Security Info=False;database=test;server=MySqlServer;user id=myUser;Password=myPass";
private DbConnection CreateConnection()
{
return new MySqlConnection(CONNECTION_STRING);
}
}
あと今回は id で絞り込む Find メソッドだけ書いたけど、実際にこのパターンを使う場合は、title で絞り込むものや description で絞り込むものなど、複数用意するほうが便利。