/* ------------------------------------------- */
#region [ Insert method ]
/// <summary>
/// Insert the record to table with table_name with given fields.
/// </summary>
/// <param name="table_name">table name</param>
/// <param name="fields">column and values</param>
/// <returns>Returns exec result of Insert.</returns>
public virtual int Insert(string table_name, Hashmap fields)
{
int result = -100;
try
{
if (string.IsNullOrWhiteSpace(table_name))
{
throw new Exception("Table Name can not be null or empty.");
}
if (table_name.Contains("drop") || table_name.Contains("--"))
{
throw new Exception(
"Table Name can not be contain restricted characters and text.");
}
if (fields == null)
{
throw new Exception(
"Column list can not be null.");
}
if (fields.IsEmpty())
{
throw new Exception(
"Column list can not be empty.");
}
QueryFormat qf = new QueryFormat(QueryTypes.Insert);
QueryAdds adds = new QueryAdds(this.conn_type);
string query = "", cols = "", vals = "";
Hashmap h = new Hashmap();
foreach (var field in fields.Keys())
{
cols = string.Format("{0}, {1}{2}{3}", cols, adds.Prefix, field, adds.Suffix);
vals = string.Format("{0}, {1}{2}", cols, adds.ParameterPrefix, field);
h.Set(string.Format("{0}{1}", adds.ParameterPrefix, field), fields.Get(field));
}
cols = cols.TrimStart(',').TrimStart();
vals = vals.TrimStart(',').TrimStart();
query = string.Format(qf.Format, table_name, cols, vals);
result = this.Execute(query, CommandType.Text, h);
}
catch (Exception)
{
throw;
}
return(result);
}
public virtual int Delete(string table_name, string where_column, object where_value)
{
int result = -1;
try
{
if (string.IsNullOrWhiteSpace(table_name))
{
throw new Exception("Table Name can not be null or empty.");
}
if (table_name.Contains("drop") || table_name.Contains("--"))
{
throw new Exception(
"Table Name can not be contain restricted characters and text.");
}
if (string.IsNullOrWhiteSpace(where_column))
{
throw new Exception("Where Column Name can not be null or empty.");
}
if (where_column.Contains("drop") || where_column.Contains("--"))
{
throw new Exception(
"Table Name can not be contain restricted characters and text.");
}
QueryFormat qf = new QueryFormat(QueryTypes.Delete);
QueryAdds qo = new QueryAdds(this.conn_type);
string query = "", vals = "";
vals = string.Format("{0}{1}{2}={3}{1}", qo.Prefix, where_column, qo.Suffix, qo.ParameterPrefix);
query = string.Format(qf.Format, table_name, vals);
Hashmap p = new Hashmap();
p.Set(string.Format("{0}{1}", qo.ParameterPrefix, where_column), where_value);
result = this.ExecuteQuery(query, p);
}
catch (Exception)
{
throw;
}
return(result);
}
public T GetObjectById <T>(int Id) where T : new()
{
T result = default(T);
try
{
T t = (T)Activator.CreateInstance(typeof(T));
string id_col = string.Format("{0}", t.GetType().GetMethod("GetIdColumn").Invoke(t, null));
if (string.IsNullOrWhiteSpace(id_col))
{
throw new Exception("Id Column can not be empty.");
}
string tableName = string.Format("{0}", t.GetType().GetMethod("GetTableName").Invoke(t, null));
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("TableName can not be empty.");
}
IQueryFormat qformat = new QueryFormat(QueryTypes.SelectWhereId);
IQueryAdds adds = new QueryAdds(conn_type);
string q = qformat.Format;
q = q.Replace("#TABLE_NAME#", tableName);
q = q.Replace("#VALS#", string.Format("{0}={1}{0}", id_col, adds.ParameterPrefix));
Hashmap h = new Hashmap();
h.Set(string.Format("{0}{1}", adds.ParameterPrefix, id_col), Id);
DataSet ds = this.GetResultSet(q, CommandType.Text, h);
DataTable dt = ds.Tables[0];
List <T> listT = dt.ToList <T>(true);
result = listT[0];
}
catch (Exception)
{
throw;
}
return(result);
}
/// <summary>
/// Update records with given parameters.
/// </summary>
/// <param name="table_name">table name</param>
/// <param name="where_column">id column name, if null or empty value will be "id"</param>
/// <param name="where_value">id column value</param>
/// <param name="fields">column and values</param>
/// <returns>Returns exec result of Update.</returns>
public virtual int Update(string table_name, string where_column, object where_value, Hashmap fields)
{
int result = -1;
try
{
if (string.IsNullOrWhiteSpace(table_name))
{
throw new Exception("Table Name can not be null or empty.");
}
if (table_name.Contains("drop") || table_name.Contains("--"))
{
throw new Exception(
"Table Name can not be contain restricted characters and text.");
}
if (fields == null)
{
throw new Exception(
"Column list can not be null.");
}
if (fields.IsEmpty())
{
throw new Exception(
"Column list can not be empty.");
}
if (string.IsNullOrWhiteSpace(where_column))
{
throw new Exception("Table Name can not be null or empty.");
}
if (where_column.Contains("drop") || where_column.Contains("--"))
{
throw new Exception(
"Table Name can not be contain restricted characters and text.");
}
QueryFormat qf = new QueryFormat(QueryTypes.Update);
QueryAdds qo = new QueryAdds(this.conn_type);
string query = "", cols = "", vals = "";
//query = qf.Format;
Hashmap h = new Hashmap();
FreeParameter fp;
foreach (var field in fields.Keys())
{
cols = string.Format("{0}, {1}{2}{3}={4}{2}", cols, qo.Prefix, field, qo.Suffix, qo.ParameterPrefix);
fp = new FreeParameter
{
Name = string.Format("{0}{1}", qo.ParameterPrefix, field),
Value = fields.Get(field)
};
h.Set(string.Format("{0}{1}", qo.ParameterPrefix, field), fields.Get(field));
}
h.Set(string.Format("{0}{1}", qo.ParameterPrefix, where_column), where_value);
cols = cols.TrimStart(',').TrimStart();
vals = string.Format("{0}{1}{2}={3}{1}", qo.Prefix, where_column, qo.Suffix, qo.ParameterPrefix);
query = string.Format(qf.Format, table_name, cols, vals);
result = this.ExecuteQuery(query, h);
}
catch (Exception)
{
throw;
}
return(result);
}