/**/ /// <summary> /// 执行查询语句,返回IDataReader /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>IDataReader</returns> public IDataReader ExecuteReader(string strSQL) { using (System.Data.IDbConnection iConn = this.GetConnection()) { System.Data.IDbCommand iCmd = GetCommand(); try { PrepareCommand(out iCmd, iConn, null, strSQL, null); System.Data.IDataReader iReader = iCmd.ExecuteReader(); iCmd.Parameters.Clear(); return(iReader); } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } /**/ /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public DataSet Query(string sqlString) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(sqlString, iConn)) { DataSet ds = new DataSet(); iConn.Open(); try { System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn); iAdapter.Fill(ds); return(ds); } catch (System.Exception ex) { throw new Exception(ex.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
/// <summary> /// 执行SQL语句,返回影响的记录数 、用于增删改 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public int ExecuteSql(string SqlString) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { iConn.Open(); try { int rows = iCmd.ExecuteNonQuery(); return(rows); } catch (System.Exception ex) { LogHelper.WriteLog(ex, SqlString); return(-1); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
/**/ /// <summary> /// 执行查询语句 /// </summary> /// <param name="SqlString">查询语句</param> /// <returns>DataTable </returns> public DataTable ExecuteQuery(string sqlString) { using (System.Data.IDbConnection iConn = this.GetConnection()) { //System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn); DataSet ds = new DataSet(); try { System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn); iAdapter.Fill(ds); } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } return(ds.Tables[0]); } } /**/ /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public object GetSingle(string SqlString) { using (System.Data.IDbConnection iConn = GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { iConn.Open(); try { object obj = iCmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return(null); } else { return(obj); } } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
/**/ /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public int ExecuteSql(string SqlString, string content) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { System.Data.IDataParameter myParameter = this.iDbPara("@content", "Text"); myParameter.Value = content; iCmd.Parameters.Add(myParameter); iConn.Open(); try { int rows = iCmd.ExecuteNonQuery(); return(rows); } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } } /**/ /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public int ExecuteSqlInsertImg(string SqlString, byte[] fs) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { System.Data.IDataParameter myParameter = this.iDbPara("@content", "Image"); myParameter.Value = fs; iCmd.Parameters.Add(myParameter); iConn.Open(); try { int rows = iCmd.ExecuteNonQuery(); return(rows); } catch (System.Exception e) { throw new Exception(e.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
// 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 string RunProcedureExecuteScalar(string storeProcName, IDataParameter[] parameters) { string result = string.Empty; using (System.Data.IDbConnection iConn = this.GetConnection()) { iConn.Open(); using (SqlCommand scmd = BuildQueryCommand(iConn, storeProcName, parameters)) { object obj = scmd.ExecuteScalar(); if (obj == null) { result = null; } else { result = obj.ToString(); } } if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } return(result); }
public ErrorTypes AddTask(TaskQueueData oTask, Priority oPriority) { ErrorTypes eResult = ErrorTypes.TaskQueue; try { oTask.VisibilityTimeout = m_oVisibilityTimeout; string strId = (string)oTask.m_sKey; string strInsertRow = GetInsertString(oTask, oPriority); using (System.Data.IDbConnection dbConnection = GetDbConnection()) { dbConnection.Open(); using (System.Data.IDbCommand oInsertCommand = dbConnection.CreateCommand()) { oInsertCommand.CommandText = strInsertRow; oInsertCommand.ExecuteNonQuery(); eResult = ErrorTypes.NoError; } } } catch { eResult = ErrorTypes.TaskQueue; } return(eResult); }
public System.Data.SqlClient.SqlDataReader ExecuteReader(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); SqlDataReader dr = null; try { return(dr = (mustCloseConnection && !needTransaction)?cmd.ExecuteReader(CommandBehavior.CloseConnection):cmd.ExecuteReader()); } catch (System.Data.SqlClient.SqlException ex) { this.Connection.Close(); throw ex; } }
private bool AssuredConnected() { switch (connection.State) { case (System.Data.ConnectionState.Closed): connection.Open(); return(false); case (System.Data.ConnectionState.Broken): connection.Close(); connection.Open(); return(false); default: return(true); } }
public void Open() { //This method handles opening the database connection if (this.DbConfiguration == null) { //Return if there is no database configuration return; } else if (this._conn == null) { //Check if there is no database connection this._conn = new DBConnection(); this._conn.ConnectionString = this.DbConfiguration.ToString(); } if (this._conn.State == ConnectionState.Broken) { //Check if there is a broken connection this._conn.Close(); } if (this._conn.State == ConnectionState.Closed) { //Check if connection has closed _conn.Open(); } }
/// <summary> /// Get a Collection of List<T> Objects from a ConnectionObject based in a SQL Statement"/> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="DbConnection">The Connection Object</param> /// <param name="Statement">The SQL Sentences prompt to be fetch into the collection.</param> /// <returns>List<Object></returns> public static List <T> Get <T>(System.Data.IDbConnection DbConnection, string Statement) where T : class, new() { IDbCommand dbCommand = DbConnection.CreateCommand(); dbCommand.CommandText = Statement; DbConnection.Open(); //get properties of the base class; IList <System.Reflection.PropertyInfo> ClassStruct = new T().GetType().GetProperties().ToList(); IDataReader Reader = dbCommand.ExecuteReader(); IEnumerable <string> Columns = Reader.GetSchemaTable().Select().Select(x => x[0].ToString()); List <T> ListOfObjectsToReturn = new List <T>(); while (Reader.Read()) { T instance = new T(); for (int ctd = 0; ctd < ClassStruct.Count(); ctd++) { object value = Reader.GetValue(Reader.GetOrdinal(ClassStruct[ctd].Name)); ClassStruct.First(property => property.Name == ClassStruct[ctd].Name) .SetValue(instance, (value.GetType() == typeof(DBNull) ? null : value), null); } ListOfObjectsToReturn.Add(instance); } DbConnection.Close(); return(ListOfObjectsToReturn); }
//// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public int ExecuteSql(string SqlString) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { iConn.Open(); try { int rows = iCmd.ExecuteNonQuery(); return(rows); } catch (System.Exception E) { throw new Exception(E.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
/// <summary> /// mehod to open the connection to the data base /// </summary> public void Open() { try { // calling method from DBManagerFactory class to get the provider type connection object idbConnection = DBManagerFactory.GetConnection(this.providerType); // passing connection string idbConnection.ConnectionString = this.ConnectionString; if (idbConnection.State == ConnectionState.Open) { idbConnection.Close(); } // chek for open connection state if (idbConnection.State != ConnectionState.Open) { idbConnection.Open(); } // calling method from DBManagerFactory class to get the provider type command object this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType); this.idbTransaction = DBManagerFactory.GetTransaction(idbConnection, this.ProviderType); } catch (Exception ex) { throw ex; } }
public Dictionary <string, string> GetOptionsFields(Guid id, string sql) { Dictionary <string, string> list = new Dictionary <string, string>(); RoadFlow.Platform.DBConnection bdbconn = new RoadFlow.Platform.DBConnection(); RoadFlow.Data.Model.DBConnection dbconn = bdbconn.Get(id); using (System.Data.IDbConnection conn = bdbconn.GetConnection(dbconn)) { if (conn == null) { return(list); } try { conn.Open(); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("连接数据库出错:" + ex.Message); RoadFlow.Platform.Log.Add(ex); } List <System.Data.IDataParameter> parList = new List <System.Data.IDataParameter>(); System.Data.IDbDataAdapter dataAdapter = bdbconn.GetDataAdapter(conn, dbconn.Type, sql, parList.ToArray()); System.Data.DataSet ds = new System.Data.DataSet(); dataAdapter.Fill(ds); foreach (System.Data.DataRow row in ds.Tables[0].Rows) { list.Add(row[0].ToString(), row[1].ToString()); } return(list); } }
public void Connect(String connectionString, String baseDataDir) { if (connectionString == null) { connectionString = defaultConnectionString; } try { dbConnection = new MySqlConnection(connectionString); dbConnection.Open(); // todo: initialize DB structure } catch (Exception) { throw new Exception("Error connecting to database."); } if (baseDataDir != null) { this.baseDataDir = Common.EndDirWithSlash(baseDataDir); } if (!Directory.Exists(this.baseDataDir)) { Directory.CreateDirectory(this.baseDataDir); // TODO: make this recursive to create parents } }
/**/ /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public object GetSingle(string SqlString) { using (System.Data.IDbConnection iConn = GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { iConn.Open(); try { object obj = iCmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return(null); } else { return(obj); } } catch (System.Exception ex) { LogHelper.WriteLog(ex, SqlString); return(-1); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } } /**/ /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public int ExecuteSql(string SqlString, string content) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn)) { System.Data.IDataParameter myParameter = this.iDbPara("@content", "Text"); myParameter.Value = content; iCmd.Parameters.Add(myParameter); iConn.Open(); try { int rows = iCmd.ExecuteNonQuery(); return(rows); } catch (System.Exception ex) { LogHelper.WriteLog(ex, SqlString); return(-1); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } }
public bool Execute(System.Data.IDbConnection connection, System.Data.IDbTransaction transaction = null) { if (_processor == null) { _processor = new ScriptProcessing.SqlServerScriptProcessor(); } if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } IList <string> bits = _processor.ProcessScript(_script, Parameters); foreach (string bit in bits) { System.Data.IDbCommand cmd = connection.CreateCommand(); if (transaction != null) { cmd.Transaction = transaction; } cmd.CommandText = bit; cmd.ExecuteNonQuery(); } return(true); }
public System.Data.IDbCommand BuildConnectionAndCommand(string userId, string userRole, Chart chart, string queryString, ref System.Data.IDbConnection connection) { //string conStr = GetChartConnectionString(chart); //connection = new System.Data.SqlClient.SqlConnection(conStr); connection = GetChartConnection(chart); connection.Open(); Dictionary <string, object> parameters; string query = GetSelectForChart(userId, userRole, chart, queryString, out parameters); System.Data.IDbCommand cmd = GetChartCommand(query, connection); if (parameters != null) { foreach (string key in parameters.Keys) { cmd.Parameters.Add(GetChartParameter(key, parameters[key], connection)); } } cmd.CommandType = CommandType.Text; return(cmd); }
public void Connect(String connectionString, String baseDataDir) { if (connectionString == null) { connectionString = defaultConnectionString; } try { dbConnection = new MySqlConnection(connectionString); dbConnection.Open(); // TODO: "CREATE DATABASE IF NOT EXISTS `mybox`;"; DbCommand command = dbConnection.CreateCommand(); command.CommandText = @"CREATE TABLE IF NOT EXISTS `files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `path` varchar(512) NOT NULL, `user` int(10) NOT NULL, `parent` int(20) NOT NULL, `size` int(20) NOT NULL, `modtime` int(20) NOT NULL, `checksum` varchar(32) NOT NULL, `type` varchar(1) NOT NULL, PRIMARY KEY (`id`), KEY `user` (`user`) ); CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `email` varchar(300) NOT NULL, `password` varchar(75) NOT NULL, `salt` varchar(75) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) );"; command.ExecuteNonQuery(); #if DEBUG // password is 'badpassword' dbConnection.CreateCommand(); command.CommandText = @"INSERT IGNORE INTO `mybox`.`users` (`name`,`password`) VALUES ('test', '3693d93220b28a03d3c70bdc1cab2b890c65a2e6baff3d4a2a651b713c161c5c');"; command.ExecuteNonQuery(); #endif } catch (Exception) { throw new Exception("Error connecting to database."); } if (baseDataDir != null) { this.baseDataDir = Common.EndDirWithSlash(baseDataDir); } if (!Directory.Exists(this.baseDataDir)) { Directory.CreateDirectory(this.baseDataDir); // TODO: make this recursive to create parents } }
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; OracleCommand cmd = new OracleCommand(); PrepareCommand( cmd , connection,tran,CommandType.Text,CommandText); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); try { da.Fill(ds); da.Dispose(); } catch (System.Data.OracleClient.OracleException ex) { throw ex; } finally { if (mustCloseConnection) { if(!needTransaction) { Connection.Close(); } } } return ds; }
private void Connect() { if (factory == null) { factory = DbProviderFactories.GetFactory(StaticsVariable.PROVIDERNAME); } if (conn == null) { conn = factory.CreateConnection(); if (AppSettingHelper.Default.test) { conn.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.11)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=uyumtest)));User Id=uyumsoft;Password=uyumsoft;");//StaticsVariable.CONNECTIONSTR; } else { conn.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id=uyumsoft;Password=uyumsoft;", AppSettingHelper.Default.orahost, AppSettingHelper.Default.oraport, AppSettingHelper.Default.oraservis);//StaticsVariable.CONNECTIONSTR; } Logger.I(string.Format("Data Source=(HOST={0})(PORT={1})(SERVICE_NAME={2})", AppSettingHelper.Default.orahost, AppSettingHelper.Default.oraport, AppSettingHelper.Default.oraservis)); } if (conn.State != ConnectionState.Open) { conn.Open(); } comm = conn.CreateCommand(); }
public ErrorTypes RemoveTask(object key) { ErrorTypes eResult = ErrorTypes.TaskQueue; try { uint nId = (uint)key; string strDeleteRow = GetDeleteString(nId); using (System.Data.IDbConnection dbConnection = GetDbConnection()) { dbConnection.Open(); using (IDbCommand oDelCommand = dbConnection.CreateCommand()) { oDelCommand.CommandText = strDeleteRow; oDelCommand.ExecuteNonQuery(); eResult = ErrorTypes.NoError; } } } catch { } return(eResult); }
private bool ExistUnit(int iUnit) { bool bRet = false; try { Database d = OPS.Components.Data.DatabaseFactory.GetDatabase(); System.Data.IDbConnection DBCon = d.GetNewConnection(); DBCon.Open(); try { String strSQL = String.Format("select count(*) " + "from units " + "where uni_id = {0} ", iUnit); OracleCommand cmd = new OracleCommand(strSQL, (OracleConnection)DBCon); if (Convert.ToInt32(cmd.ExecuteScalar()) > 0) { bRet = true; } cmd.Dispose(); } catch { } DBCon.Close(); } catch { } return(bRet); }
/**/ /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="sqlString">查询语句</param> /// <param name="dataSet">要填充的DataSet</param> /// <param name="tableName">要填充的表名</param> /// <returns>DataSet</returns> public DataSet Query(string sqlString, DataSet dataSet, string tableName) { using (System.Data.IDbConnection iConn = this.GetConnection()) { using (System.Data.IDbCommand iCmd = GetCommand(sqlString, iConn)) { iConn.Open(); try { System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn); ((OleDbDataAdapter)iAdapter).Fill(dataSet, tableName); return(dataSet); } catch (System.Exception ex) { throw new Exception(ex.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } } } /**/ /// <summary> /// 执行SQL语句 返回存储过程 /// </summary> /// <param name="sqlString">Sql语句</param> /// <param name="dataSet">要填充的DataSet</param> /// <param name="startIndex">开始记录</param> /// <param name="pageSize">页面记录大小</param> /// <param name="tableName">表名称</param> /// <returns>DataSet</returns> public DataSet Query(string sqlString, DataSet dataSet, int startIndex, int pageSize, string tableName) { using (System.Data.IDbConnection iConn = this.GetConnection()) { iConn.Open(); try { System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn); ((OleDbDataAdapter)iAdapter).Fill(dataSet, startIndex, pageSize, tableName); return(dataSet); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (iConn.State != ConnectionState.Closed) { iConn.Close(); } } } }
public FileIndex(String absPath) { dbLocation = absPath; if (File.Exists(dbLocation)) { foundAtInit = true; } // load the sqlite-JDBC driver try { dbConnection = new SqliteConnection("URI=file:" + dbLocation + ",version=3"); dbConnection.Open(); // TODO: replace field names with constants DbCommand command = dbConnection.CreateCommand(); command.CommandText = "create table if not exists files (path text primary key, type char(1), modtime bigint, size bigint, checksum text)"; command.ExecuteNonQuery(); } catch (Exception e) { throw new Exception("Unable to load SQLite driver " + e.Message); //Common.ExitError(); } // check to see that the file can be loaded if (!File.Exists(dbLocation)) { throw new Exception("database file " + dbLocation + " not found after init."); //Common.ExitError(); } // prepare the queries so they are nice and fast when the DB is open commandInsertOrReplace = dbConnection.CreateCommand(); commandInsertOrReplace.Parameters.Add(paramPath); commandInsertOrReplace.Parameters.Add(paramType); commandInsertOrReplace.Parameters.Add(paramModtime); commandInsertOrReplace.Parameters.Add(paramSize); commandInsertOrReplace.Parameters.Add(paramChecksum); commandInsertOrReplace.CommandText = "insert or replace into files values(?,?,?,?,?)"; commandInsertOrIgnore = dbConnection.CreateCommand(); commandInsertOrIgnore.Parameters.Add(paramPath); commandInsertOrIgnore.Parameters.Add(paramType); commandInsertOrIgnore.Parameters.Add(paramModtime); commandInsertOrIgnore.Parameters.Add(paramSize); commandInsertOrIgnore.Parameters.Add(paramChecksum); commandInsertOrIgnore.CommandText = "insert or ignore into files values(?,?,?,?,?)"; commandGetFiles = dbConnection.CreateCommand(); commandGetFiles.CommandText = "select * from files"; commandDeleteFile = dbConnection.CreateCommand(); commandDeleteFile.Parameters.Add(paramPath); commandDeleteFile.CommandText = "delete from files where path = ?"; }
public void Done(int id) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "UPDATE Todo SET Done = 1 WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, new { Todoid = id }); } }
public void Delete(Todo item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "delete from Todo WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
public TaskQueueData GetTask() { TaskQueueData oData = null; bool bResult = false; uint ncq_id = 0; string strSelectSQL = GetSelectString(); string strUpdateSQL = GetUpdateString(); try { using (System.Data.IDbConnection dbConnection = GetDbConnection()) { dbConnection.Open(); bool bIsExist = false; using (IDbCommand oSelectCommand = dbConnection.CreateCommand()) { oSelectCommand.CommandText = strSelectSQL; using (System.Data.IDataReader oDataReader = oSelectCommand.ExecuteReader()) { if (true == oDataReader.Read()) { ncq_id = Convert.ToUInt32(oDataReader["cq_id"]); oData = TaskQueueData.DeserializeFromXml(Convert.ToString(oDataReader["cq_data"])); oData.m_oDataKey = ncq_id; bIsExist = true; } } } if (bIsExist) { using (System.Data.IDbCommand oUpdateCommand = dbConnection.CreateCommand()) { oUpdateCommand.CommandText = strUpdateSQL + ncq_id + "';"; if (oUpdateCommand.ExecuteNonQuery() > 0) { bResult = true; } else { bResult = false; } } } } } catch { } return((bResult) ? oData : null); }
public void Update(Accident item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = @"UPDATE AccidentTable SET Verified = @Verified WHERE Id = @Id"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
public void Done(Todo item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "UPDATE Todo SET " + " Done = @Done " + " WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
public bool Connect() { // Check for valid connection string if(m_sConnectionString == null || m_sConnectionString.Length == 0) throw( new Exception("Invalid database connection string")); // Disconnect if already connected Disconnect(); // Get ADONET connection object m_oConnection = GetConnection(); m_oConnection.ConnectionString = this.ConnectionString; // Implement connection retries for(int i=0; i <= m_nRetryConnect; i++) { try { m_oConnection.Open(); if(m_oConnection.State == ConnectionState.Open) { m_bConnected = true; break; } } catch { if(i == m_nRetryConnect) throw; // Wait for 1 second and try again Thread.Sleep(1000); } } // Get command object m_oCommand = m_oConnection.CreateCommand(); m_oCommand.CommandTimeout = m_nCommandTimeout; return m_bConnected; }
/// <summary> /// Realiza una conexión con la Base de Datos /// </summary> /// <returns></returns> public bool Connect() { try { // Validar si el 'ConnectionString' es válido if (m_sConnectionString == null || m_sConnectionString.Length == 0) { if (m_oLog != null) { m_oLog.TraceError("La cadena de conexión para la Base de Datos no es válida.", m_idConexion); } throw (new DataAccessException("La cadena de conexión para la Base de Datos no es válida.", -50)); } // Desconectarse si esta actualmente conectado Disconnect(); // Obtener el objeto ADO.NET Conection m_oConnection = GetConnection(); m_oConnection.ConnectionString = m_sConnectionString; // Intentar conectar for (int i = 0; i <= m_nRetryConnect; i++) { if (m_oLog != null) { if (i > 0) m_oLog.TraceLog("Intento de conexion nro: " + i.ToString(), m_idConexion); } try { m_oConnection.Open(); if (m_oConnection.State == ConnectionState.Open) { m_bConnected = true; break; } } catch { if (i == m_nRetryConnect) throw; // Reintentos cada 1 segundo Thread.Sleep(1000); } } // Obtiene el objeto COMMAND m_oCommand = m_oConnection.CreateCommand(); m_oCommand.CommandTimeout = (m_nCommandTimeout > 0) ? m_nCommandTimeout : m_oConnection.ConnectionTimeout; return m_bConnected; } catch (DataAccessException ex) { throw ex; } catch (Exception ex) { throw new DataAccessException("Error no esperado al realizar la conexión.", ex); } }
public void Connect(String connectionString, String baseDataDir) { if (connectionString == null) connectionString = defaultConnectionString; try { dbConnection = new MySqlConnection (connectionString); dbConnection.Open (); // todo: initialize DB structure } catch (Exception) { throw new Exception("Error connecting to database."); } if (baseDataDir != null) this.baseDataDir = Common.EndDirWithSlash(baseDataDir); if (!Directory.Exists(this.baseDataDir)) { Directory.CreateDirectory(this.baseDataDir); // TODO: make this recursive to create parents } }
public void Open() { EnableRecover = false; if (DbConnection != null && DbConnection.State != System.Data.ConnectionState.Closed && DbConnection.State != System.Data.ConnectionState.Broken) return; Lfx.Workspace.Master.DebugLog(this.Handle, "Abriendo " + this.Name); System.Text.StringBuilder ConnectionString = new System.Text.StringBuilder(); switch (Lfx.Data.DataBaseCache.DefaultCache.AccessMode) { case AccessModes.Odbc: if (Lfx.Data.DataBaseCache.DefaultCache.Provider == null) Lfx.Data.DataBaseCache.DefaultCache.Provider = new qGen.Providers.Odbc(); ConnectionString.Append("DSN=" + Lfx.Data.DataBaseCache.DefaultCache.ServerName + ";"); Lfx.Data.DataBaseCache.DefaultCache.ServerName = "(ODBC)"; break; case AccessModes.MySql: if (Lfx.Data.DataBaseCache.DefaultCache.Provider == null) Lfx.Data.DataBaseCache.DefaultCache.Provider = new qGen.Providers.MySql(); ConnectionString.Append("Convert Zero Datetime=true;"); ConnectionString.Append("Connection Timeout=30;"); ConnectionString.Append("Default Command Timeout=9000;"); ConnectionString.Append("Allow User Variables=True;"); ConnectionString.Append("Allow Batch=True;"); // ConnectionString.Append("KeepAlive=20;"); // No sirve, uso KeepAlive propio ConnectionString.Append("Pooling=true;"); ConnectionString.Append("Cache Server Properties=false;"); switch (System.Text.Encoding.Default.BodyName) { case "utf-8": ConnectionString.Append("charset=utf8;"); break; case "iso-8859-1": ConnectionString.Append("charset=latin1;"); break; } if (Lfx.Data.DataBaseCache.DefaultCache.SlowLink) { ConnectionString.Append("Compress=true;"); ConnectionString.Append("Use Compression=true;"); } Lfx.Data.DataBaseCache.DefaultCache.OdbcDriver = null; Lfx.Data.DataBaseCache.DefaultCache.Mars = false; Lfx.Data.DataBaseCache.DefaultCache.SqlMode = qGen.SqlModes.MySql; break; case AccessModes.Npgsql: if (Lfx.Data.DataBaseCache.DefaultCache.Provider == null) Lfx.Data.DataBaseCache.DefaultCache.Provider = new qGen.Providers.Npgsql(); ConnectionString.Append("CommandTimeout=900;"); Lfx.Data.DataBaseCache.DefaultCache.SqlMode = qGen.SqlModes.PostgreSql; break; case AccessModes.MSSql: if (Lfx.Data.DataBaseCache.DefaultCache.Provider == null) Lfx.Data.DataBaseCache.DefaultCache.Provider = new qGen.Providers.Odbc(); Lfx.Data.DataBaseCache.DefaultCache.OdbcDriver = "SQL Server"; Lfx.Data.DataBaseCache.DefaultCache.SqlMode = qGen.SqlModes.TransactSql; break; } if (Lfx.Data.DataBaseCache.DefaultCache.OdbcDriver != null) ConnectionString.Append("DRIVER={" + Lfx.Data.DataBaseCache.DefaultCache.OdbcDriver + "};"); string Server, Port; if (Lfx.Data.DataBaseCache.DefaultCache.ServerName.IndexOf(':') >= 0) { string[] Temp = Lfx.Data.DataBaseCache.DefaultCache.ServerName.Split(new char[] { ':' }, 2, StringSplitOptions.RemoveEmptyEntries); Server = Temp[0]; Port = Temp[1]; } else { Server = Lfx.Data.DataBaseCache.DefaultCache.ServerName; Port = null; } ConnectionString.Append("SERVER=" + Server + ";"); if(string.IsNullOrEmpty(Port) == false) ConnectionString.Append("PORT=" + Port + ";"); if (Lfx.Data.DataBaseCache.DefaultCache.DataBaseName.Length > 0) ConnectionString.Append("DATABASE=" + Lfx.Data.DataBaseCache.DefaultCache.DataBaseName + ";"); ConnectionString.Append("UID=" + Lfx.Data.DataBaseCache.DefaultCache.UserName + ";"); ConnectionString.Append("PWD=" + Lfx.Data.DataBaseCache.DefaultCache.Password + ";"); DbConnection = Lfx.Data.DataBaseCache.DefaultCache.Provider.GetConnection(); DbConnection.ConnectionString = ConnectionString.ToString(); try { DbConnection.Open(); } catch (Exception ex) { throw ex; } this.SetupConnection(this.DbConnection); EnableRecover = true; if (KeepAlive > 0 && KeepAliveTimer == null) { KeepAliveTimer = new System.Timers.Timer(this.KeepAlive * 1000); KeepAliveTimer.Elapsed += new System.Timers.ElapsedEventHandler(this.KeepAliveTimer_Elapsed); KeepAliveTimer.Start(); } if (DbConnection is System.Data.Odbc.OdbcConnection) { System.Data.Odbc.OdbcConnection OdbcConnection = DbConnection as System.Data.Odbc.OdbcConnection; try { OdbcConnection.StateChange -= new System.Data.StateChangeEventHandler(this.Connection_StateChange); } catch { // Nada } OdbcConnection.StateChange += new System.Data.StateChangeEventHandler(this.Connection_StateChange); } if (Lfx.Workspace.Master.ServerVersion == null) Lfx.Workspace.Master.ServerVersion = this.ServerVersion; }