/*public override void Delete(TStorage obj)
* {
* try
* {
* using (var conn = new OdbcConnection(ConnectionString))
* {
* conn.Open();
* using (var cmd = new OdbcCommand())
* {
* cmd.Connection = conn;
*
* cmd.CommandText =
* "DELETE FROM " + obj.Table + " WHERE " + obj.PKField + "=" + obj.ID;
* cmd.ExecuteScalar();
* }
* }
* }
* catch (Exception ex)
* {
*
* }
* }*/
public override void Delete(TStorage obj)
{
try
{
DataSet dataSet = new DataSet();
OdbcConnection conn = new OdbcConnection(ConnectionString);
conn.Open();
OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter();
odbcDataAdapter.SelectCommand = new OdbcCommand("SELECT * FROM " + obj.Table, conn);
OdbcCommandBuilder ocb = new OdbcCommandBuilder(odbcDataAdapter);
odbcDataAdapter.Fill(dataSet, obj.Table);
conn.Close();
DataTable dt = dataSet.Tables[0];
DataColumn[] keyColumns = new DataColumn[1];
keyColumns[0] = dt.Columns[obj.PKField];
dt.PrimaryKey = keyColumns;
DataRow dr = dt.Rows.Find(obj.ID);
dr.Delete();
conn.ConnectionString = ConnectionString;
conn.Open();
odbcDataAdapter.SelectCommand = new OdbcCommand("SELECT * FROM " + obj.Table, conn);
odbcDataAdapter.Update(dataSet, obj.Table);
conn.Close();
}
catch (Exception ex)
{
}
}
public override void Update(TStorage obj)
{
using (var conn = new OdbcConnection(ConnectionString))
{
conn.Open();
using (var cmd = new OdbcCommand())
{
cmd.Connection = conn;
string fields = string.Empty;
string values = string.Empty;
for (int i = 0; i < obj.Fields.Count; i++)
{
var field = obj.Fields.ElementAt(i);
string p = "p" + i.ToString();
cmd.Parameters.AddWithValue(p, field.Value);
fields += field.Key + "=@" + p;
if (i != obj.Fields.Count - 1)
{
fields += ", ";
}
}
cmd.CommandText =
"UPDATE " + obj.Table + " SET " + fields + " WHERE " + obj.PKField + "=" + obj.ID;
cmd.ExecuteScalar();
}
}
}
public override TStorage LoadDetails(TStorage query)
{
TStorage result = null;
using (var conn = new OdbcConnection(ConnectionString))
{
conn.Open();
using (var cmd = new OdbcCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM " + query.Table + " WHERE " + query.PKField + "=" + query.ID;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
result = new TStorage();
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
result.Fields.Add(key, value);
}
}
}
}
return(result);
}
public override IEnumerable <TStorage> LoadList(TStorage query)
{
List <TStorage> result = new List <TStorage>();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM " + query.Table;
if (query.Fields != null && query.Fields.Any())
{
cmd.CommandText += " WHERE ";
string fields = string.Empty;
for (int i = 0; i < query.Fields.Count; i++)
{
var field = query.Fields.ElementAt(i);
string p = "p" + i.ToString();
cmd.Parameters.AddWithValue(p, field.Value);
fields += field.Key + "=@" + p;
if (i != query.Fields.Count - 1)
{
fields += " AND ";
}
}
cmd.CommandText += fields;
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
TStorage s = new TStorage();
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
s.Fields.Add(key, value);
}
result.Add(s);
}
}
}
}
return(result);
}
public override void Delete(TStorage obj)
{
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"DELETE FROM " + obj.Table + " WHERE " + obj.PKField + "=" + obj.ID;
cmd.ExecuteScalar();
}
}
}
public override int Insert(TStorage obj)
{
using (var conn = new OdbcConnection(ConnectionString))
{
conn.Open();
using (var cmd = new OdbcCommand())
{
cmd.Connection = conn;
string fields = string.Empty;
string values = string.Empty;
for (int i = 0; i < obj.Fields.Count; i++)
{
var field = obj.Fields.ElementAt(i);
if (field.Key == obj.PKField)
{
continue;
}
var prm = cmd.Parameters.AddWithValue(field.Key, field.Value);
fields += field.Key;
values += "?";
if (i != obj.Fields.Count - 1)
{
fields += " , ";
values += " , ";
}
}
cmd.CommandText =
"INSERT INTO " + obj.Table + " (" + fields + ") VALUES ( " + values + " ) RETURNING " + obj.PKField;
object retVal = cmd.ExecuteScalar();
return(Convert.ToInt32(retVal));
}
}
}
public abstract IEnumerable <TStorage> LoadList(TStorage query);
public abstract TStorage LoadDetails(TStorage query);
public abstract void Delete(TStorage obj);
public abstract void Update(TStorage obj);
public abstract int Insert(TStorage obj);