/// <summary>
/// Constructs a new PostgresConnection and opens a SQLite database specified by databasePath.
/// </summary>
/// <param name="databasePath">
/// Specifies the path to the database file.
/// </param>
public DbConnection(IDbConnection conn)
{
_conn = conn;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
Dbi = Data.Dbi.GetForConnection(conn);
_open = true;
}
/// <summary>
/// Deletes the given object from the database using its primary key.
/// </summary>
/// <param name="obj">
/// The object to delete. It must have a primary key designated using the PrimaryKeyAttribute.
/// </param>
/// <returns>
/// The number of rows deleted.
/// </returns>
public int Delete <T> (T obj)
{
var map = GetMapping(obj.GetType());
var pk = map.PK;
if (pk == null)
{
throw new NotSupportedException("Cannot delete " + map.TableName + ": it has no PK");
}
var q = string.Format("delete from {0} where {1} = ?", Dbi.Quote(map.TableName), Dbi.Quote(pk.Name));
return(Execute(q, pk.GetValue(obj)));
}
public bool CreateTable(Type ty)
{
if (_tables == null)
{
_tables = new Dictionary <string, TableMapping> ();
}
TableMapping map;
if (!_tables.TryGetValue(ty.FullName, out map))
{
map = GetMapping(ty);
_tables.Add(ty.FullName, map);
}
var query = "create table " + Dbi.Quote(map.TableName) + "(\n";
var decls = map.Columns.Select(p => Dbi.GetColumnDeclSql(p));
var decl = string.Join(",\n", decls.ToArray());
query += decl;
query += ")";
var created = false;
try {
created = Execute(query) > 0;
} catch (Exception ex) {
if (Trace)
{
Console.WriteLine(ex.Message);
}
}
foreach (var p in map.Columns.Where(x => x.IsIndexed))
{
var indexName = map.TableName + "_" + p.Name;
var q = string.Format("create index {0} on {1}({2})", Dbi.Quote(indexName), Dbi.Quote(map.TableName), Dbi.Quote(p.Name));
try {
Execute(q);
} catch (Exception ex) {
if (Trace)
{
Console.WriteLine(ex.Message);
}
}
}
return(created);
}
public static Dbi GetForConnection(IDbConnection conn)
{
var tn = conn.GetType().Name;
Dbi r = null;
if (tn == "SqliteConnection")
{
r = new SqliteDbi();
}
else
{
r = new MySqlDbi();
}
r.InitConnection(conn);
return(r);
}
public int UpdatePK(object obj, object newPK)
{
if (obj == null)
{
return(0);
}
var map = GetMapping(obj.GetType());
var pk = map.PK;
if (pk == null)
{
throw new NotSupportedException("Cannot update " + map.TableName + ": it has no PK");
}
var q = string.Format("update {0} set {1} = ? where {2} = ? ", Dbi.Quote(map.TableName), Dbi.Quote(pk.Name), Dbi.Quote(pk.Name));
var c = Execute(q, newPK, pk.GetValue(obj));
pk.SetValue(obj, newPK);
return(c);
}
/// <summary>
/// Updates all of the columns of a table using the specified object
/// except for its primary key.
/// The object is required to have a primary key.
/// </summary>
/// <param name="obj">
/// The object to update. It must have a primary key designated using the PrimaryKeyAttribute.
/// </param>
/// <returns>
/// The number of rows updated.
/// </returns>
public int Update(object obj)
{
if (obj == null)
{
return(0);
}
var map = GetMapping(obj.GetType());
var pk = map.PK;
if (pk == null)
{
throw new NotSupportedException("Cannot update " + map.TableName + ": it has no PK");
}
var cols = from p in map.Columns
where p != pk
select p;
var vals = from c in cols
select c.GetValue(obj);
var ps = new List <object> (vals);
ps.Add(pk.GetValue(obj));
var q = string.Format("update {0} set {1} where {2} = ? ", Dbi.Quote(map.TableName), string.Join(",", (from c in cols
select Dbi.Quote(c.Name) + " = ? ").ToArray()), pk.Name);
return(Execute(q, ps.ToArray()));
}
/// <summary>
/// Attempts to retrieve an object with the given primary key from the table
/// associated with the specified type. Use of this method requires that
/// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
/// </summary>
/// <param name="pk">
/// The primary key.
/// </param>
/// <returns>
/// The object with the given primary key. Throws a not found exception
/// if the object is not found.
/// </returns>
public T Get <T> (object pk) where T : new()
{
var map = GetMapping(typeof(T));
string query = string.Format("select * from {0} where {1} = ?", Dbi.Quote(map.TableName), Dbi.Quote(map.PK.Name));
return(Query <T> (query, pk).First());
}