//Simple builder public QueryBuilder(string pluginName) { _sb = new System.Text.StringBuilder(); _plugin = pluginName; _type = CommandType.Text; }
public MySqlCommand() { this.designTimeVisible = true; this.cmdType = System.Data.CommandType.Text; this.parameters = new MySqlParameterCollection(this); this.updatedRowSource = UpdateRowSource.Both; this.cursorPageSize = 0; this.cmdText = string.Empty; this.timedOut = false; }
public int ExcuteDataTable(DataTable srcTable, string commandText, System.Data.CommandType commandType) { if (this.Connection.State != System.Data.ConnectionState.Open) { this.Connection.Open(); } if (!this.needTransaction) { tran = null; } OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, this.Connection, tran, CommandType.Text, commandText); OracleDataAdapter da = new OracleDataAdapter(cmd); try { da.Fill(srcTable); da.Dispose(); } catch (System.Data.OracleClient.OracleException ex) { Errormsg_ = ex.Message; return(-1); } finally { if (mustCloseConnection) { if (!needTransaction) { Connection.Close(); } } } return(1); }
private CommandType getDbCommandType(string typeName) { System.Data.CommandType returnValue = System.Data.CommandType.Text; if (String.IsNullOrEmpty(typeName)) { return(CommandType.Text); } switch (typeName.ToLower()) { case ("text"): { returnValue = System.Data.CommandType.Text; break; } case ("sp"): { returnValue = System.Data.CommandType.StoredProcedure; break; } case ("table"): { returnValue = System.Data.CommandType.TableDirect; break; } default: { returnValue = System.Data.CommandType.Text; break; } } return(returnValue); }
public DataSet ExecuteDataSet(System.Data.IDbConnection connection, System.Data.CommandType commandType, string CommandText) { if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } if (!this.needTransaction) { tran = null; } SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, tran, CommandType.Text, CommandText); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { da.Fill(ds); da.Dispose(); } catch (System.Data.SqlClient.SqlException ex) { throw ex; } finally { if (mustCloseConnection) { if (!needTransaction) { Connection.Close(); } } } return(ds); }
public object ExecuteScalar(System.Data.CommandType CommandType, StoredProcedureEntity Sproc, string Intent = "") { object result = null; List <SqlParameter> SQLParams = new List <SqlParameter>(); try { SqlConnection selectConnection = default(SqlConnection); SQLParams = GetParameters(Sproc); using (selectConnection = new SqlConnection(_ConnectionString)) { if (!string.IsNullOrEmpty(Intent) && Intent == DBIntent.Read.ToString()) { selectConnection = new SqlConnection(_ConnectionString + ApplicationRead); } else { selectConnection = new SqlConnection(_ConnectionString + ApplicationReadWrite); } selectConnection.Open(); SqlCommand selectCMD = new SqlCommand(Sproc.StoredProcedureName, selectConnection); selectCMD.CommandType = CommandType; selectCMD.CommandTimeout = 180; if (SQLParams.Count > 0) { selectCMD.Parameters.AddRange(SQLParams.ToArray()); } result = selectCMD.ExecuteScalar(); selectConnection.Close(); } } catch (Exception exc) { throw new Exception(exc.Message); } return(result); }
public List <Object> Execute(string connectionString, System.Data.CommandType commandType, string query, ParameterType[] parameters) { using (var connection = new SqlConnection(connectionString)) using (var command = connection.CreateCommand()) { connection.ConnectionString = connectionString; command.CommandText = query; command.CommandType = commandType; if (parameters != null) { Array.ForEach(parameters, p => command.Parameters.Add(p)); } connection.Open(); //command.Connection = connection; using (var reader = command.ExecuteReader()) { var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList(); var Object = new Object(); List <Object> personList = new List <Object>(); while (reader.Read()) { foreach (var column in columns) { var col = reader[column]; } } } var results = new List <Object>(); connection.Close(); return(results); } }
public virtual int ExcuteNonQuery(string cmdText, System.Data.CommandType cmdType, System.Data.IDataParameter[] paramters) { int res = 0; try { IDbCommand _DbCommand = _SQLDataAccessConnection.CreateCommand(); if (_SQLDataAccessConnection.IsTransaction) { _DbCommand.Transaction = _SQLDataAccessConnection.GetTransaction(); } res = _DbCommand.ExcuteCommand <int>(_DbCommand.ExecuteNonQuery, cmdText, cmdType, paramters); } catch (Exception ex) { if (_SQLDataAccessConnection.IsTransaction) { _SQLDataAccessConnection.Rollback(); } throw ex; } return(res); }
public object ExecuteScalar(string connString, System.Data.CommandType cmdType, string cmdText, IDbDataParameter[] cmdParms, IDbTransaction trans) { IDbCommand cmd = this.dbElementFactory.GetCommand(); this.conn = this.dbElementFactory.GetConnection(connString); try { PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return(val); } catch (Exception ex) { ex = ex; return(-1); } finally { conn.Close(); } }
public DataTable FetchData(string prmDataTableName, System.Data.CommandType prmCommandType, string prmCommandText, IEnumerable <KeyValuePair <string, object> > prmParameters) { var retTable = new DataTable(prmDataTableName); using (var con = base.GetConnection()) { using (var command = con.CreateCommand()) { command.CommandText = prmCommandText; command.CommandType = prmCommandType; command.Parameters.Clear(); command.Parameters.AddRange(prmParameters.ToSqlParameterCollection()); //fill dataset using (var retAdapter = new SqlDataAdapter(command)) { retAdapter.Fill(retTable); } } } return(retTable); }
public Int32 Execute_SqlParameter(int commandType, string Sqlstr, DataSet DsDbParameter) { DataTable dt = DsDbParameter.Tables[0]; DbParameter[] DbParameter = new DbParameter[dt.Rows.Count]; for (int i = 0; i < DsDbParameter.Tables[0].Rows.Count; i++) { DbParameter[i] = new MySql.Data.MySqlClient.MySqlParameter(dt.Rows[i]["name"].ToString(), dt.Rows[i]["value"]); try { if (dt.Rows[i]["type"] != null) { DbParameter[i].DbType = (DbType)int.Parse(dt.Rows[i]["type"].ToString()); } } catch { } } System.Data.CommandType ComType = (System.Data.CommandType)commandType; return(dataAccess.Execute_Procedure(ComType, Sqlstr, DbParameter)); }
public List <T0> ExecuteQuery <T0>(string sqlQuery, object sqlParam, System.Data.CommandType queryType = System.Data.CommandType.StoredProcedure) { using (var sqlConnection = new SqlConnection(GetConnectionString())) { try { sqlConnection.Open(); var result = sqlConnection.QueryMultiple(sqlQuery, sqlParam, commandTimeout: 30000, commandType: queryType); var res = result.Read <T0>().ToList(); return(res); } catch (Exception ex) { _log.Error("Error on Database Operation. Error Details As: {0}", ex.StackTrace); throw; } finally { sqlConnection.Close(); } } }
public override System.Data.IDataReader ExecuteReader(System.Data.CommandType cmdType, string cmdText, params System.Data.IDataParameter[] commandParameters) { IDbConnection cn = null; try { IDbCommand cmd = new MySqlCommand(); cn = GetConnection(); cn.Open(); PrepareCommand(cn, cmd, cmdType, cmdText, commandParameters); IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(rdr); } catch (Exception ex) { if (cn != null) { cn.Close(); } throw; } }
public int ExecuteNonQuery(string Query, System.Data.CommandType Type, List<DbParameter> Parameters) { int result = 0; try { command = connection.CreateCommand(); command.CommandText = Query; command.CommandType = Type; if (Parameters != null && Parameters.Count > 0) { foreach (DbParameter param in Parameters) { command.Parameters.AddWithValue(param.Name, param.Value); command.Parameters[param.Name].DbType = param.Type; command.Parameters[param.Name].Direction = param.Direction; } } result = command.ExecuteNonQuery(); command.Dispose(); } catch (Exception ex) { throw; } return result; }
/// <summary> /// /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public virtual object ExecuteScalar(System.Data.CommandType cmdType, string cmdText, params System.Data.IDataParameter[] commandParameters) { DbCommand cmd = GetDbCommand(); if (m_BeginTrans) { PrepareCommand(cmd, null, m_Trans, cmdType, cmdText, commandParameters); sw.Reset(); sw.Start(); object val = cmd.ExecuteScalar(); sw.Stop(); log.Debug(string.Format("execute sql takes {0} ms : {1}", sw.ElapsedMilliseconds, cmdText)); cmd.Parameters.Clear(); return(val); } else { using (DbConnection conn = GetDbConnection(ConnectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); sw.Reset(); sw.Start(); object val = cmd.ExecuteScalar(); sw.Stop(); log.Debug(string.Format("execute sql takes {0} ms : {1}", sw.ElapsedMilliseconds, cmdText)); cmd.Parameters.Clear(); return(val); } } }
public static DataSet FillDataSet(string strQuery, System.Data.CommandType cmdtype) { DataSet ds = new DataSet(); try { SqlConnection con = new SqlConnection(); con = Getconnection(); con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = strQuery; cmd.CommandType = cmdtype; cmd.Connection = con; SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); da.Dispose(); } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); } return(ds); }
public int ExecuteNonQuery(string connectionString, System.Data.CommandType commandType, string query, ParameterType[] parameters) { var rowsAffected = 0; using (var connection = new ConnectionType()) { using (var command = new CommandType()) { command.Connection = connection; command.CommandText = query; command.CommandType = commandType; if (parameters != null) { Array.ForEach(parameters, p => command.Parameters.Add(p)); } connection.ConnectionString = connectionString; connection.Open(); rowsAffected = command.ExecuteNonQuery(); } } return(rowsAffected); }
public System.Data.DataSet ExecuteDataSet(System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); MySqlConnection conn = new MySqlConnection(connectionString); try { MySqlConnection.ClearAllPools(); PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); DataSet ds = new DataSet(); MySqlDataAdapter adapter = new MySqlDataAdapter(cmd); adapter.Fill(ds); return(ds); } catch { conn.Close(); throw; } finally { conn.Close(); } }
/// <summary> /// Execute an action query. The name , "Non Query" reflects what Microsoft uses in the .Net framework /// </summary> /// <param name="pSQL">The SQL to be executed</param> /// <param name="pCommandType">Text or Stored Procedure</param> /// <param name="pSQLCmd"></param> /// <returns></returns> public static int ExecuteNonQuery(String pSQL, System.Data.CommandType pCommandType, System.Data.SqlClient.SqlCommand pSQLCmd, List <SqlParameter> parameters) { int status = -1; try { // I don't know why the Framework method is called "ExecuteNonQuery" but I named this // method the same, to reduce confusion. The name should be "ExecuteActionQuery" CheckConnection(); System.Data.SqlClient.SqlCommand objCmd = Config.myConnection.CreateCommand(); objCmd.CommandText = pSQL; objCmd.CommandType = pCommandType; // The default is 'text', which implies an SQL string objCmd.Parameters.Clear(); // Just in case we used any the last time we called this if (parameters != null) { foreach (SqlParameter parameter in parameters) { objCmd.Parameters.Add(parameter); } } objCmd.ExecuteNonQuery(); } catch (Exception ex) { try { //ErrorLog.LogEvent(pSQL + " ERROR in ExcecuteNonQuery() : " + ex.Message, 0, 0); System.Console.WriteLine(DateTime.Now + " ExecuteNonQuery() : " + ex.Message + " : " + pSQL); status = 1; } catch (Exception ex1) { status = 0; Utils.Log(ex1.Message); } } finally {} return(status); }
public static DataSet FillDataSet(string strQuery, System.Data.CommandType cmdtype, string[] para, object[] values) { DataSet ds = new DataSet(); try { SqlConnection con = new SqlConnection(); con = Getconnection(); con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = strQuery; cmd.CommandType = cmdtype; cmd.Connection = con; SqlParameter sqlpara; for (int i = 0; i < para.Length; i++) { sqlpara = new SqlParameter(); sqlpara.ParameterName = para[i]; sqlpara.SqlValue = values[i]; cmd.Parameters.Add(sqlpara); } SqlDataAdapter sqlda = new SqlDataAdapter(cmd); sqlda.Fill(ds); sqlda.Dispose(); } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); } return(ds); }
/// <summary> /// Execute SQL with parameters with no return of data or values /// </summary> /// <param name="connectionString">Connection String</param> /// <param name="SQL">SQL Statement</param> /// <param name="parameters">Parameters</param> /// <param name="TimeOut">Timeout in seconds, with default</param> /// <returns>Rows affected</returns> public static int ExecuteSqlWithParametersNoReturn(string connectionString, string SQL, List <SqlParameter> parameters, int TimeOut = Timeout_Default) { System.Data.CommandType CmdType = System.Data.CommandType.Text; int iRows = 0; using (var conDB = new System.Data.SqlClient.SqlConnection(connectionString)) { conDB.Open(); using (SqlCommand command = new SqlCommand(SQL, conDB)) { command.CommandType = CmdType; command.CommandTimeout = TimeOut; if (parameters != null) { foreach (var p in parameters) { command.Parameters.Add(p); } } var irows = command.ExecuteNonQuery(); } } return(iRows); }
private string ExpandCommandText() { string commandText = this.CommandText; if (ADP.IsEmpty(commandText)) { return(ADP.StrEmpty); } System.Data.CommandType commandType = this.CommandType; System.Data.CommandType type = commandType; if (type != System.Data.CommandType.Text) { if (type == System.Data.CommandType.StoredProcedure) { return(this.ExpandStoredProcedureToText(commandText)); } if (type != System.Data.CommandType.TableDirect) { throw ADP.InvalidCommandType(commandType); } return(commandText); } return(commandText); }
private System.Data.DataTable ExecuteQuery(string command, System.Data.CommandType cmdType, System.Data.SqlClient.SqlParameter[] parameters = null) { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.SqlClient.SqlConnection sqlcnn = new System.Data.SqlClient.SqlConnection(ConnectionString); try { sqlcnn.Open(); using (System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand(command, sqlcnn)) { sqlcmd.CommandTimeout = CmdTimeOut; sqlcmd.CommandType = cmdType; if (parameters != null) { sqlcmd.Parameters.AddRange(parameters); } using (System.Data.SqlClient.SqlDataAdapter sqlda = new System.Data.SqlClient.SqlDataAdapter(sqlcmd)) { sqlda.Fill(dt); } } } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { sqlcnn.Close(); } return(dt); }
public int ExecuteNonQuery(System.Data.IDbConnection connection, System.Data.CommandType commandType, string commandText) { int affectRows; if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } SqlCommand Command = new SqlCommand(); if (!this.needTransaction) { tran = null; } PrepareCommand(Command, connection, tran, CommandType.Text, commandText); try { affectRows = Command.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException ex) { needTransaction = false; throw ex; } finally { if (mustCloseConnection) { if (!needTransaction) { connection.Close(); } } } return(affectRows); }
// private object ExecuteIdentity(System.Data.IDbConnection connection, System.Data.CommandType commandType, string CommandText) // { // object identity; //// string[] commands = CommandText.Split(';');(?<command>*) // string[] commands = SystemFramework.Utils.UtilMethods.SplitEx(CommandText,"$$$");//2005-10-19 用比较少用的"$$$"分割CommandText,防止用户输入带有 ; 的文本时导致Split出错 // // if(commands.Length < 1) // return null; // // if (connection.State != System.Data.ConnectionState.Open) // connection.Open(); // //// tran = connection.BeginTransaction(); // SqlCommand Command = (SqlCommand)connection.CreateCommand(); //// Command.Transaction = tran as SqlCeTransaction; // Command.CommandType = commandType; // Command.CommandText = commands[0]; // // try // { // identity = Command.ExecuteScalar(); // if(commands.Length >= 2) // { // Command.CommandText = commands[1]; // identity = Command.ExecuteScalar(); // } // //// tran.Commit(); // return identity; // } // catch(SqlException ex) // { //// tran.Rollback(); // needTransaction = false; // throw ex; // } // finally // { // if (mustCloseConnection) // { // if(!needTransaction) // { // connection.Close(); // } // } // } // } public object ExecuteScalar(System.Data.IDbConnection connection, System.Data.CommandType commandType, string CommandText) { object affectRows; if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } OracleCommand Command = new OracleCommand(); if (!this.needTransaction) { tran = null; } PrepareCommand(Command, connection, tran, CommandType.Text, CommandText); try { affectRows = Command.ExecuteScalar(); } catch (OracleException ex) { needTransaction = false; throw ex; } finally { if (mustCloseConnection) { if (!needTransaction) { connection.Close(); } } } return(affectRows); }
public static DataSet DataSetReader(string strQuery, System.Data.CommandType cmdtype, string[] para, object[] values) { DataSet dsReader = new DataSet(); try { SqlConnection con = new SqlConnection(); con = Getconnection(); con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = strQuery; cmd.CommandType = cmdtype; cmd.Connection = con; SqlParameter sqlpara; for (int i = 0; i < para.Length; i++) { sqlpara = new SqlParameter(); sqlpara.ParameterName = para[i]; sqlpara.SqlValue = values[i]; cmd.Parameters.Add(sqlpara); } SqlDataReader dataReader; dataReader = cmd.ExecuteReader(); dsReader.Tables[0].Load(dataReader); dataReader.Close(); } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); } return(dsReader); }
public static Cycles LoadCycles(string sql, System.Data.CommandType commandType, System.Data.SqlClient.SqlParameter[] parameters) { return(Cycles.ConvertFromDT(Utils.ExecuteQuery(sql, commandType, parameters))); }
//########## DBQUERY - COM JSON OBJ ########## //Método para trazer resultados do banco de dados com mapeamento de objetos JSON protected List <Dictionary <string, object> > DBQuery(string sql, Dictionary <string, object> parametros, System.Data.CommandType tipo, Dictionary <string, string> objetos) { List <Dictionary <string, object> > saidas = new List <Dictionary <string, object> >(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = tipo; foreach (KeyValuePair <string, object> entry in parametros) { cmd.Parameters.AddWithValue(entry.Key, entry.Value); } conn.Open(); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { Dictionary <string, object> saida = new Dictionary <string, object>(); for (int i = 0; i < rd.FieldCount; i++) { if (objetos.ContainsKey(rd.GetName(i))) { if (saida.ContainsKey(objetos[rd.GetName(i)])) { (saida[objetos[rd.GetName(i)]] as Dictionary <string, object>).Add(rd.GetName(i), rd[i]); } else { Dictionary <string, object> item = new Dictionary <string, object>(); item.Add(rd.GetName(i), rd[i]); saida.Add(objetos[rd.GetName(i)], item); } } else { saida.Add(rd.GetName(i), rd[i]); } } saidas.Add(saida); } rd.Close(); conn.Close(); return(saidas); }
public int ExecuteNonQuery(System.Data.CommandType cmdType, string cmdText) { return(ExecuteNonQuery(cmdType, cmdText, null)); }
public DataTable ExecuteDataTableWithOutputParam(System.Data.CommandType CommandType, StoredProcedureEntity Sproc, out string OutParam, string ConnectionString = "", string Intent = "") { DataSet ds = new DataSet(); List <SqlParameter> SQLParams = new List <SqlParameter>(); try { SqlConnection selectConnection = default(SqlConnection); SQLParams = GetParameters(Sproc); using (selectConnection = new SqlConnection(_ConnectionString)) { if (string.IsNullOrEmpty(ConnectionString)) { if (!string.IsNullOrEmpty(Intent) && Intent == DBIntent.Read.ToString()) { selectConnection = new SqlConnection(_ConnectionString + ApplicationRead); } else { selectConnection = new SqlConnection(_ConnectionString + ApplicationReadWrite); } } else { if (!string.IsNullOrEmpty(Intent) && Intent == DBIntent.Read.ToString()) { selectConnection = new SqlConnection(ConnectionString + ApplicationRead); } else { selectConnection = new SqlConnection(ConnectionString + ApplicationReadWrite); } } selectConnection.Open(); SqlCommand selectCMD = new SqlCommand(Sproc.StoredProcedureName, selectConnection); selectCMD.CommandTimeout = 180; if (SQLParams.Count > 0) { selectCMD.Parameters.AddRange(SQLParams.ToArray()); //selectCMD.Parameters[3].Direction = ParameterDirection.Output; } selectCMD.CommandType = CommandType; SqlDataAdapter ThisDA = new SqlDataAdapter(); ThisDA.SelectCommand = selectCMD; ThisDA.Fill(ds, "Table1"); selectConnection.Close(); OutParam = string.Empty; if (SQLParams.Count > 0) { OutParam = Convert.ToString(selectCMD.Parameters["@TotalRecords"].Value); } } } catch (Exception exc) { string Message = exc.Message + " Connection " + _ConnectionString; throw new Exception(Message); } return(ds.Tables[0]); }
public override IMap <ClassType, DataType> LoadUsingCommand(string Command, System.Data.CommandType CommandType) { this.CommandToLoad = new Command(Command, CommandType); return((IMap <ClassType, DataType>) this); }
public DbSourceCommand() { this.commandText = string.Empty; this.commandType = System.Data.CommandType.Text; this.parameterCollection = new DbSourceParameterCollection(this); }
public static Errors LoadErrors(string sql, System.Data.CommandType commandType, MySqlParameter[] parameters) { return(Errors.ConvertFromDT(Utils.ExecuteQuery(sql, commandType, parameters))); }
public DbExecuteEntity() { this._commandType = System.Data.CommandType.StoredProcedure; this.Conditions = new Hashtable(); }