/* ------------------------------------------- */
#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 static IConnection Build(DbProperty db_configuration)
{
IConnection conn = null;
try
{
ConnectionTypes conn_type = ConnectionTypeBuilder.GetConnectionType(db_configuration.ConnType);
if (conn_type == ConnectionTypes.Unknown)
{
throw new Exception("Unknown Connection type can not be allowed.");
}
string conn_str = "";
conn_str = db_configuration.ConnString;
conn_str = conn_str ?? string.Empty;
if (string.IsNullOrWhiteSpace(conn_str))
{
Hashmap h = db_configuration.Keys;
if (h != null)
{
string[] keys_ = h.Keys();
foreach (var key in keys_)
{
conn_str = string.Format("{0}{1}={2};", conn_str, key, h.Get(key));
}
conn_str = conn_str.TrimEnd(';');
}
}
// IF CLAUSE 1
if (conn_type == ConnectionTypes.External)
{
conn = new ExternalConnection(db_configuration.InvariantName, conn_str);
}
else
{
conn = new Connection(conn_type, conn_str);
}
}
catch (Exception)
{
throw;
}
// RETURN
return(conn);
}
/// <summary>
/// Gets Scalar Execution Result for given parameters.
/// </summary>
/// <param name="queryOrProcedure">Query Text or Stored Procedure name</param>
/// <param name="cmdType">Command Type; StoredProcedure, Table, Text</param>
/// <param name="parameters">Hashmap parameter that includes parameters</param>
/// <returns>Result returns as object.</returns>
public object ExecuteScalar(string queryOrProcedure, CommandType cmdType, Hashmap parameters)
{
object result = null;
try
{
using (DbCommand cmd = db_conn.CreateCommand())
{
cmd.CommandText = queryOrProcedure;
cmd.CommandType = cmdType;
if (parameters != null)
{
string[] keys = parameters.Keys();
DbParameter prm;
foreach (var key in keys)
{
prm = cmd.CreateParameter();
prm.ParameterName = key;
prm.Value = parameters.Get(key);
cmd.Parameters.Add(prm);
}
}
if (db_transaction != null)
{
cmd.Transaction = db_transaction;
}
result = cmd.ExecuteScalar();
}
}
catch (Exception)
{
throw;
}
return(result);
}
/// <summary>
/// Gets Result for given parameters.
/// </summary>
/// <param name="queryOrProcedure">Query Text or Stored Procedure name</param>
/// <param name="cmdType">Command Type; StoredProcedure, Table, Text</param>
/// <param name="parameters">Hashmap parameter that includes parameters</param>
/// <returns>Result returns as DataSet.</returns>
public DataSet GetResultSet(string queryOrProcedure, CommandType cmdType, Hashmap parameters)
{
DataSet ds = null;
try
{
using (DbCommand cmd = db_conn.CreateCommand())
{
cmd.CommandText = queryOrProcedure;
cmd.CommandType = cmdType;
if (parameters != null)
{
string[] keys = parameters.Keys();
DbParameter prm;
foreach (var key in keys)
{
prm = cmd.CreateParameter();
prm.ParameterName = key;
prm.Value = parameters.Get(key);
cmd.Parameters.Add(prm);
}
}
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
ds = new DataSet();
adapter.Fill(ds);
}
}
}
catch (Exception)
{
throw;
}
return(ds);
}
/// <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);
}
