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