/* ------------------------------------------- */ #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); }