public virtual List <T> GetObjects(Dictionary <string, object> sqlParams, string query)
{
List <T> lstObj = new List <T>(64);
using (var cn = SqlAdapter.GetNewConnection())
{
var cmd = cn.CreateCommand();
cmd.CommandText = query;
foreach (var p in sqlParams)
{
cmd.Parameters.Add(CreateSQLParam(cmd, "@" + p.Key, p.Value));
}
using (var r = cmd.ExecuteReader())
{
T obj;
while (r.Read())
{
obj = resultReader(r);
lstObj.Add(obj);
}
}
}
return(lstObj);
}
public virtual int InsertOrUpdateObject(Dictionary <string, object> sqlParams, string key, string databaseTable, string condition)
{
var cols = String.Empty;
var vals = String.Empty;
var updvals = String.Empty;
int rows = -1;
using (var cn = SqlAdapter.GetNewConnection())
{
var cmd = cn.CreateCommand();
foreach (var p in sqlParams)
{
cmd.Parameters.Add(CreateSQLParam(cmd, "@" + p.Key, p.Value));
vals += "@" + p.Key + ",";
updvals += p.Key + "=@" + p.Key + ",";
}
vals = vals.TrimEnd(',');
updvals = updvals.TrimEnd(',');
cols = vals.Replace("@", null);
cmd.CommandText = "SELECT " + key + " FROM " + databaseTable + @" WHERE 1=1 AND " + condition;
var result = cmd.ExecuteScalar();
cmd.CommandText = (result == null || result == DBNull.Value) ? "INSERT INTO " + databaseTable + "(" + cols + ") VALUES(" + vals + ")" + SqlAdapter.LastInsertIdSelector
: "UPDATE " + databaseTable + " SET " + updvals + " WHERE 1=1 AND " + condition + "; SELECT 1;";
result = cmd.ExecuteScalar();
rows = (result != null && result != DBNull.Value) ? Convert.ToInt32(result) : 0;
}
return(rows);
}
public virtual int UpdateDeleteObject(Dictionary <string, object> sqlParams, string query)
{
int row = -1;
using (var cn = SqlAdapter.GetNewConnection())
{
var cmd = cn.CreateCommand();
cmd.CommandText = query;
foreach (var p in sqlParams)
{
cmd.Parameters.Add(CreateSQLParam(cmd, "@" + p.Key, p.Value));
}
row = cmd.ExecuteNonQuery();
}
return(row);
}
public virtual int InsertObject(Dictionary <string, object> sqlParams, string query)
{
int id = -1;
using (var cn = SqlAdapter.GetNewConnection())
{
var cmd = cn.CreateCommand();
cmd.Transaction = cn.BeginTransaction(System.Data.IsolationLevel.Serializable);
cmd.CommandText = query + SqlAdapter.LastInsertIdSelector;
foreach (var p in sqlParams)
{
cmd.Parameters.Add(CreateSQLParam(cmd, "@" + p.Key, p.Value));
}
var result = cmd.ExecuteScalar();
cmd.Transaction.Commit();
id = (result != null && result != DBNull.Value) ? Convert.ToInt32(result) : 0;
}
return(id);
}