public DataSet GetCustomerData(string CustomerCode)
{
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
// Sql Command 명령문
sqlSelectCommand.CommandText =
@"select President, Address, BusinessType, BusinessItem from customers where customercode = @customerCode";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
System.Data.IDataParameter CustomerCodeParam = EnterpriseManager.DbFactory.CreateDataParameter("@customerCode", typeof(string));
CustomerCodeParam.Value = CustomerCode;
sqlSelectCommand.Parameters.Add(CustomerCodeParam);
//
// 명령문을 호출한다
DataSet dataset = this.GetDataSet(sqlSelectCommand);
sqlSelectCommand.Dispose();
return(dataset);
}
private bool TryUpdateTask(uint ncq_id, DateTime oTaskUpdateTime)
{
bool bResult = false;
try
{
using (System.Data.IDbConnection dbConnection = GetDbConnection())
{
dbConnection.Open();
using (System.Data.IDbCommand oUpdateCommand = dbConnection.CreateCommand())
{
oUpdateCommand.CommandText = GetUpdateString(ncq_id, oTaskUpdateTime);
bResult = (oUpdateCommand.ExecuteNonQuery() > 0);
}
}
}
catch
{
}
return(bResult);
}
/// <summary>
/// Get an account from the database via a known ID
/// </summary>
/// <param name="userName"></param>
/// <returns>null if not found</returns>
public ServerUser GetUserByName(String userName)
{
ServerUser account = null;
try {
DbCommand command = dbConnection.CreateCommand();
command.CommandText = "select * from oc_users where uid='" + userName + "';";
DbReader reader = command.ExecuteReader();
while (reader.Read())
{
account = new ServerUser(reader["uid"].ToString(), reader["uid"].ToString(), reader["password"].ToString());
}
reader.Close();
}
catch (Exception e) {
Console.WriteLine("There was an error fetching the account " + e.Message);
}
return(account);
}
internal bool CreateBatchRecordFromDb(string nameOf, int?total)
{
batches = new List <BatchModel>();
bool success = false;
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
command.CommandText = storedProc_CreateBatchRecord;
command.CommandType = System.Data.CommandType.StoredProcedure;
// Add input parameter.
SqlParameter parameterNameOf = new SqlParameter();
parameterNameOf.ParameterName = "@nameOf";
parameterNameOf.SqlDbType = SqlDbType.NVarChar;
parameterNameOf.Direction = ParameterDirection.Input;
parameterNameOf.Value = nameOf;
// Add input parameter.
SqlParameter parameterTotalMade = new SqlParameter();
parameterTotalMade.ParameterName = "@total";
parameterTotalMade.SqlDbType = SqlDbType.Int;
parameterTotalMade.Direction = ParameterDirection.Input;
parameterTotalMade.Value = total;
command.Parameters.Add(parameterNameOf);
command.Parameters.Add(parameterTotalMade);
using (System.Data.IDataReader reader = command.ExecuteReader())
{
if (reader.RecordsAffected > 0)
{
success = true;
}
}
}
return(success);
}
private DbCommand method_1()
{
IDbConnection dbConnection = null;
if (!Data.UseTransaction && !Data.UseSharedConnection)
{
dbConnection = Data.AdoNetProvider.CreateConnection();
dbConnection.ConnectionString = Data.ConnectionString;
}
else
{
if (Data.Connection == null)
{
Data.Connection = Data.AdoNetProvider.CreateConnection();
Data.Connection.ConnectionString = Data.ConnectionString;
}
dbConnection = Data.Connection;
}
System.Data.IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.Connection = dbConnection;
return(new DbCommand(this, dbCommand));
}
public List <BatchModel> GetAllBatchesFromDb()
{
batches = new List <BatchModel>();
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
string text = $"select * from dbo.OnHandInventory";
command.CommandText = text;
command.CommandType = CommandType.Text;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
batches.Add(MapBatchesFromDb(reader));
}
}
}
return(batches);
}
public T ExecuteReturnLastId <T>(Ms.Data.IDbCommand command, string identityColumnName = null)
{
T lastId;
if (command.Data.InnerCommand.CommandText[command.Data.InnerCommand.CommandText.Length - 1] != ';')
{
System.Data.IDbCommand innerCommand = command.Data.InnerCommand;
innerCommand.CommandText = innerCommand.CommandText + ';';
}
while (true)
{
bool flag;
System.Data.IDbCommand command1 = command.Data.InnerCommand;
command1.CommandText = command1.CommandText + "select lastval();";
lastId = default(T);
command.Data.x876bc5bb5331d9cc.xa266202b32508032(false, delegate {
object obj2 = command.Data.InnerCommand.ExecuteScalar();
bool flag = !(obj2.GetType() == typeof(T));
while (true)
{
while (!flag)
{
lastId = (T)obj2;
break;
}
lastId = (T)Convert.ChangeType(obj2, typeof(T));
if (15 != 0)
{
return;
}
}
});
T local = lastId;
if ((((uint)flag) - ((uint)flag)) >= 0)
{
return(local);
}
}
}
//REM: Holt die Daten von der Datenbank
public System.Data.DataSet getData(string sql)
{
System.Data.IDbCommand tDataCommand = getDataCommand(sql);
if (tDataCommand != null && !string.IsNullOrEmpty(tDataCommand.CommandText))
{
if (this.SQLParameter != null)
{
foreach (System.Data.Common.DbParameter tParameter in this.SQLParameter)
{
_COR.SQL.AddParameter(tDataCommand, tParameter.ParameterName, tParameter.Value);
}
}
;
return(_COR.SQL.GetDataSet(tDataCommand));
}
;
return(null);
}
/// <summary>
/// 得到一个命令对象
/// </summary>
/// <param name="strSql">查询字符串</param>
/// <param name="commonType">命令类型</param>
/// <param name="dataParameters">参数集</param>
/// <param name="cb">命令参数，主要用于性能优化</param>
private System.Data.IDbCommand CreateCommand(string strSql, System.Data.CommandType commonType, List <IDataParameter> dataParameters, System.Data.CommandBehavior cb)
{
System.Data.IDbCommand cmd = this.CreateCommand();
cmd.CommandText = strSql;
cmd.CommandType = commonType;
cmd.CommandTimeout = 30000;
if (dataParameters != null && dataParameters.Count > 0)
{
foreach (IDataParameter parameter in dataParameters)
{
cmd.Parameters.Add(parameter);
}
}
if ((cb & CommandBehavior.CloseConnection) != CommandBehavior.CloseConnection)
{
cb |= CommandBehavior.CloseConnection;
}
return(cmd);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(List <string> list)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < list.Count; n++)
{
string strsql = list[n].ToString();
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
/// <summary>
/// 执行查询语句，返回一条记录对应的对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSQL">查询语句</param>
/// <returns></returns>
public T query <T>(string strSQL, params IDataParameter[] iParms) where T : class, new()
{
System.Data.IDbConnection iConn = this.GetConnection();
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, strSQL, iParms);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
if (iReader.Read())
{
PropertyInfo[] pis = typeof(T).GetProperties();
T model = new T();
foreach (PropertyInfo pi in pis)
{
pi.SetValue(model, iReader[pi.Name], null);
}
return(model);
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
return(null);
}
public string[][] GetIndexData(string entityType, string techName, TechCycle cycle, string dataId)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var indexDefine = entity.indexdefinition.FirstOrDefault(p => p.name == techName);
if (indexDefine == null)
{
return(null);
}
var fields = JsonConvert.DeserializeObject <IList <Field> >(indexDefine.fields);
string tableName = "Tech_" + entityType + "_" + indexDefine.table_name + "_" + cycle;
string sql = @"select * from " + tableName + " where f_code='" + dataId + "' order by date desc limit 500";
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql;
IDataReader reader = commond.ExecuteReader();
List <string[]> indexData = new List <string[]>();
while (reader.Read())
{
var data = new List <string>();
data.Add(DateTime.Parse(reader["date"] + "").ToString("yyyyMMdd"));
data.AddRange(fields.Select(filed => reader[filed.name] + ""));
indexData.Add(data.ToArray());
}
entity.Database.Connection.Close();
indexData.Reverse();
return(indexData.ToArray());
}
}
}
public System.Data.DataTable QueryForDataTable(string statementName, StrObjectDict paramObject, StrObjectDict dictParam, System.Collections.Generic.IDictionary <string, System.Data.ParameterDirection> dictParamDirection, out System.Collections.Hashtable htOutPutParameter)
{
ISqlMapper sqlMap = this.GetSqlMap();
System.Data.DataSet dataSet = new System.Data.DataSet();
bool flag = false;
ISqlMapSession sqlMapSession = sqlMap.LocalSession;
if (sqlMapSession == null)
{
sqlMapSession = new SqlMapSession(sqlMap);
sqlMapSession.OpenConnection();
flag = true;
}
System.Data.IDbCommand dbCommand = this.GetDbCommand(sqlMap, statementName, paramObject, dictParam, dictParamDirection, System.Data.CommandType.StoredProcedure);
try
{
dbCommand.Connection = sqlMapSession.Connection;
System.Data.IDbDataAdapter dbDataAdapter = sqlMapSession.CreateDataAdapter(dbCommand);
dbDataAdapter.Fill(dataSet);
}
finally
{
if (flag)
{
sqlMapSession.CloseConnection();
}
}
htOutPutParameter = new System.Collections.Hashtable();
foreach (System.Data.IDataParameter dataParameter in dbCommand.Parameters)
{
if (dataParameter.Direction == System.Data.ParameterDirection.Output)
{
htOutPutParameter[dataParameter.ParameterName] = dataParameter.Value;
}
}
return(dataSet.Tables[0]);
}
public T ExecuteReturnLastId <T>(Ms.Data.IDbCommand command, string identityColumnName = null)
{
T lastId;
bool flag = command.Data.InnerCommand.CommandText[command.Data.InnerCommand.CommandText.Length - 1] == ';';
Label_0049:
if (!flag)
{
System.Data.IDbCommand command2 = command.Data.InnerCommand;
command2.CommandText = command2.CommandText + ';';
if ((((uint)flag) + ((uint)flag)) > uint.MaxValue)
{
goto Label_000B;
}
if (((uint)flag) < 0)
{
goto Label_0049;
}
}
System.Data.IDbCommand innerCommand = command.Data.InnerCommand;
innerCommand.CommandText = innerCommand.CommandText + "select SCOPE_IDENTITY()";
Label_000B:
lastId = default(T);
command.Data.x876bc5bb5331d9cc.xa266202b32508032(false, delegate {
bool flag;
object obj2 = command.Data.InnerCommand.ExecuteScalar();
if (((uint)flag) >= 0)
{
flag = !(obj2.GetType() == typeof(T));
}
if (!flag)
{
lastId = (T)obj2;
}
lastId = (T)Convert.ChangeType(obj2, typeof(T));
});
return(lastId);
}
public System.Data.DataTable QueryForDataTable(string statementName, object paramObject)
{
ISqlMapper sqlMap = this.GetSqlMap();
bool flag = false;
System.DateTime now = System.DateTime.Now;
System.Data.DataTable result;
try
{
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
flag = true;
}
System.Data.DataSet dataSet = new System.Data.DataSet();
IMappedStatement mappedStatement = sqlMap.GetMappedStatement(statementName);
RequestScope requestScope = mappedStatement.Statement.Sql.GetRequestScope(mappedStatement, paramObject, sqlMap.LocalSession);
mappedStatement.PreparedCommand.Create(requestScope, sqlMap.LocalSession, mappedStatement.Statement, paramObject);
System.Reflection.FieldInfo field = requestScope.IDbCommand.GetType().GetField("_innerDbCommand", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
using (System.Data.IDbCommand dbCommand = (System.Data.IDbCommand)field.GetValue(requestScope.IDbCommand))
{
int num = sqlMap.LocalSession.CreateDataAdapter(dbCommand).Fill(dataSet);
}
result = dataSet.Tables[0];
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (flag)
{
sqlMap.CloseConnection();
}
}
return(result);
}
//public override void AddRange(IList<rule> datas)
//{
// using (var entity = new StockManDBEntities())
// {
// foreach (var data in datas)
// {
// var rule = entity.rule.FirstOrDefault(p => p.code == data.code);
// if (rule != null)
// {
// rule.name = data.name;
// rule.state = data.state;
// rule.description = data.description;
// }
// else
// {
// entity.Rule.Add(data);
// }
// }
// entity.SaveChanges();
// foreach (var data in datas)
// {
// foreach (var condition in data.RuleCondition)
// {
// var rule = entity.RuleCondition.FirstOrDefault(p => p.code == condition.code);
// if (rule != null)
// {
// rule.category_code = condition.category_code;
// rule.category_name = condition.category_name;
// rule.object_code = condition.object_code;
// rule.object_name = condition.object_name;
// rule.index_code = condition.index_code;
// rule.index_name = condition.index_name;
// rule.sort = condition.sort;
// rule.rule_code = condition.rule_code;
// }
// else
// {
// entity.RuleCondition.Add(condition);
// }
// }
// }
// }
//}
public void RemoveByUserId(string userId)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var list = entity.rule.Where(p => p.user_id == userId).ToList();
if (list.Count == 0)
{
return;
}
var ids = string.Empty;
foreach (var rule in list)
{
if (ids.Length == 0)
{
ids = "'" + rule.code + "'";
}
else
{
ids += ",'" + rule.code + "'";
}
}
string sql1 = @"delete from RuleCondition where rule_code in (" + ids + ");";
//entity.Database.ExecuteSqlCommand(sql1);
sql1 += @"delete from rule where code in (" + ids + ");";
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql1;
commond.ExecuteNonQuery();
entity.Database.Connection.Close();
}
}
}
protected virtual void InitCommandSetParameter(QueryContext query, System.Data.IDbCommand cmd, string parametername, object parameterValue)
{
IDataParameter sqlparam = null;
//sqlparam = ConnectorTools.AddDataParameterWithValue(cmd, "@" + fieldKey, obj); //NpgsqlCommand cmd = conn.CreateCommand();
sqlparam = cmd.CreateParameter();
sqlparam.ParameterName = "@" + parametername;
sqlparam.Value = parameterValue;
// Détection du type
if (ConnectorTools.FindEngine(this.EngineName) == ConnectorConstants.ConnectorEngineEnum.POSTGRESQL)
{
// !!! A revoir
//https://github.com/npgsql/Npgsql/issues/177
if (parameterValue != null && parameterValue != DBNull.Value && parameterValue is string && ((string)parameterValue).StartsWith("<?xml", StringComparison.OrdinalIgnoreCase))
{
sqlparam.DbType = DbType.Xml;
}
}
cmd.Parameters.Add(sqlparam);
}
public LocationSet GetForLocationType(int locationType)
{
// Sql Command 명령문
System.Data.IDbCommand sqlSelectCommand = EnterpriseManager.DbFactory.CreateDbCommand();
sqlSelectCommand.CommandText =
@"SELECT * FROM Location
WHERE LocationType = @locationType
order by LocationName";
sqlSelectCommand.CommandType = System.Data.CommandType.Text;
//
// 파라미터2값을 만든다
System.Data.IDataParameter locationTypeParam = EnterpriseManager.DbFactory.CreateDataParameter("@locationType", typeof(int));
locationTypeParam.Value = locationType;
sqlSelectCommand.Parameters.Add(locationTypeParam);
//
//
// 명령문을 호출한다
LocationSet entitySet = ((LocationSet)(this.GetEntitySet(sqlSelectCommand, typeof(LocationSet))));
sqlSelectCommand.Dispose();
return(entitySet);
}
public void AddTableInsertDataTemplatedTest()
{
var ScriptRunner = new SqlScriptRunner.ScriptRunner(Mother.AddTable(), new ScriptProcessing.SqliteScriptProcessor());
ScriptRunner.Parameters = new Dictionary <string, string>();
using (var connection = new SQLiteConnection(Mother.ConnectionString()))
{
var success = ScriptRunner.Execute(connection);
if (success)
{
ScriptRunner = new ScriptRunner(Mother.TemplatedInsert(), new ScriptProcessing.SqliteScriptProcessor());
long yValue = Mother.TemplateInteger();
long zValue = Mother.TemplateInteger();
ScriptRunner.Parameters.Add("ZVALUE", zValue.ToString());
ScriptRunner.Parameters.Add("YVALUE", yValue.ToString());
bool s = ScriptRunner.Execute(connection);
Assert.IsTrue(s);
//make sure that the data is actually in the DB now...
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = Mother.SelectDataByYValue(yValue);
var data = cmd.ExecuteReader();
Assert.IsNotNull(data);
Assert.IsTrue(data.FieldCount > 0);
data.Read();
long zValueDB = (long)data[2];
Assert.AreEqual(zValueDB, zValue);
}
}
Mother.DropTable(Mother.ConnectionString());
}
}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbTransaction tran)
{
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.Transaction = tran;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteWithTrans");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
}//Execute(string sql)
public int ExecuteNonQuery(System.Data.IDbCommand command)
{
if (this.ReadOnly)
{
throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");
}
if (this.IsOpen() == false)
{
this.Open();
}
Log.Debug(this.Handle.ToString() + ": " + command.CommandText);
int Intentos = 3;
while (true)
{
try {
if (command.Connection == null)
{
command.Connection = this.DbConnection;
}
this.ResetKeepAliveTimer();
int Res = command.ExecuteNonQuery();
return(Res);
} catch (Exception ex) {
if (this.TryToRecover(ex) || Intentos-- <= 0)
{
Log.Error(command.CommandText, ex);
ex.Data.Add("Command", command.CommandText);
throw;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
System.Data.IDbCommand iCmd = GetCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
IDataParameter[] iParms = (IDataParameter[])myDE.Value;
PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
int val = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
}
iTrans.Commit();
}
catch
{
iTrans.Rollback();
throw;
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
public string GetAllVideoQualityForGroup(int animeGroupID)
{
string vidQuals = "";
using (var session = JMMService.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT anifile.File_Source ";
command.CommandText += "from AnimeGroup ag ";
command.CommandText += "INNER JOIN AnimeSeries ser on ser.AnimeGroupID = ag.AnimeGroupID ";
command.CommandText += "INNER JOIN AnimeEpisode ep on ep.AnimeSeriesID = ser.AnimeSeriesID ";
command.CommandText += "INNER JOIN AniDB_Episode aniep on ep.AniDB_EpisodeID = aniep.EpisodeID ";
command.CommandText += "INNER JOIN CrossRef_File_Episode xref on aniep.EpisodeID = xref.EpisodeID ";
command.CommandText += "INNER JOIN AniDB_File anifile on anifile.Hash = xref.Hash ";
command.CommandText += "INNER JOIN CrossRef_Subtitles_AniDB_File subt on subt.FileID = anifile.FileID "; // See Note 1
command.CommandText += "where ag.AnimeGroupID = " + animeGroupID.ToString();
command.CommandText += " GROUP BY anifile.File_Source ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
string vidQual = rdr[0].ToString().Trim();
if (vidQuals.Length > 0)
{
vidQuals += ",";
}
vidQuals += vidQual;
}
}
}
return(vidQuals);
}
}//Execute(string sql,System.Data.IDataParameter[] param)
/// <summary>
/// 执行一个事务
/// </summary>
/// <param name="sqls">Sql语句组</param>
/// <returns>成功时返回true</returns>
static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
{
System.Data.IDbTransaction myTrans;
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):连接对象为空!");
return(false);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
myTrans = Conn.BeginTransaction();
cmd.Transaction = myTrans;
try
{
foreach (string sql in sqls)
{
if (sql != null)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):" + ex.Message);
return(false);
}
return(true);
}//Execute(string sql)
public object Scalar(string Query)
{
//This method should handle all scalar queries such as COUNT or AVG
//Open connection to database.
this.Open();
//Instantiate object to hold the result of the query.
object result;
using (System.Data.IDbCommand command = this._conn.CreateCommand())
{
command.CommandText = Query;
//Execute the query
result = command.ExecuteScalar();
}
//Close connection to database
this.Close();
//return result object
return(result);
}
public T ExecuteReturnLastId <T>(Ms.Data.IDbCommand command, string identityColumnName = null)
{
if (command.Data.InnerCommand.CommandText[command.Data.InnerCommand.CommandText.Length - 1] != ';')
{
System.Data.IDbCommand command1 = command.Data.InnerCommand;
command1.CommandText = command1.CommandText + ';';
}
System.Data.IDbCommand innerCommand = command.Data.InnerCommand;
innerCommand.CommandText = innerCommand.CommandText + "select IDENTITY_VAL_LOCAL() as LastId from sysibm.sysdummy1;";
T lastId = default(T);
command.Data.x876bc5bb5331d9cc.xa266202b32508032(false, delegate {
object obj2 = command.Data.InnerCommand.ExecuteScalar();
while (true)
{
bool flag = !(obj2.GetType() == typeof(T));
do
{
while (!flag)
{
lastId = (T)obj2;
if ((((uint)flag) - ((uint)flag)) >= 0)
{
break;
}
}
}while ((((uint)flag) + ((uint)flag)) > uint.MaxValue);
lastId = (T)Convert.ChangeType(obj2, typeof(T));
if ((((uint)flag) - ((uint)flag)) <= uint.MaxValue)
{
return;
}
}
});
return(lastId);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
public virtual void Disconnect()
{
// Disconnect can be called from Dispose and should guarantee no errors
// stops a proper reconnection after a timeout
// if(!m_bConnected)
// return;
try {
if (dbTransaction != null)
{
RollbackTransaction(false);
}
if (dbCommand != null)
{
dbCommand.Dispose();
dbCommand = null;
}
if (dbConnection != null)
{
try {
dbConnection.Close();
} catch {
} finally {
dbConnection.Dispose();
dbConnection = null;
}
}
dbConnected = false;
} catch (Exception ex) {
Logger.LogError(6, "Failed to disconnect: " + ex.StackTrace);
}
}
/// <summary>
/// コマンドが完了された時に呼ばれる。
/// </summary>
/// <param name="profiledDbCommand"></param>
/// <param name="executeType"></param>
/// <param name="result"></param>
public void ExecuteNonQueryFinish(System.Data.IDbCommand profiledDbCommand, SqlExecuteType executeType, int result)
{
// IDbProfilerにはないためここには来ない
if (executeType != SqlExecuteType.NonQuery)
{
return;
}
commandText = profiledDbCommand.CommandText; //SQL文
SbForDBLog.Clear();
parametersText = CreateParametersText(profiledDbCommand.Parameters, SbForDBLog);
stopwatch.Stop();
SbForDBLog.Clear();
SetCommonLogString(SbForDBLog, executeType).Append("【影響行数】").Append(result.ToString())
.Replace(Environment.NewLine, " ");
string log = SbForDBLog.ToString();
logger.Info(log, LogType.Sql, result: result);
}
/// <summary>
/// Gets a list of all the possible subtitle languages
/// </summary>
/// <returns></returns>
public List <string> GetAllUniqueSubtitleLanguages()
{
List <string> allLanguages = new List <string>();
using (var session = DatabaseFactory.SessionFactory.OpenSession())
{
System.Data.IDbCommand command = session.Connection.CreateCommand();
command.CommandText = "SELECT Distinct(lan.LanguageName) ";
command.CommandText += "FROM CrossRef_Subtitles_AniDB_File subt ";
command.CommandText += "INNER JOIN Language lan on subt.LanguageID = lan.LanguageID ";
command.CommandText += "ORDER BY lan.LanguageName ";
using (IDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
string lan = rdr[0].ToString().Trim();
allLanguages.Add(lan);
}
}
}
return(allLanguages);
}
public static object ExecuteReaderItem(
this System.Data.IDbCommand command,
DbOneReadHandler handle)
{
LastCommand = command.CommandText;
using (command)
using (var reader = command.ExecuteReader()) {
if (handle != null)
{
if (reader.Read())
{
return(handle(reader));
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
/// <summary>
/// Realiza la desconexión de la Base de Datos
/// </summary>
public void Disconnect()
{
try
{
ValidateDataReader();
bool OpenTransaction = false;
// Disconnect puede llamarse desde 'Dispose' y debe garantizar que no hay errores
if (!m_bConnected)
{
return;
}
// Si quedaron transacciones abiertas, realiza el Rollback
if (m_oTransaction != null)
{
if (m_oLog != null)
{
m_oLog.TraceLog("Al desconectar se detectaron transacciones abiertas...", m_idConexion);
}
RollbackTransaction(true);
OpenTransaction = true;
}
// Elimina el objeto Command
if (m_oCommand != null)
{
m_oCommand.Dispose();
m_oCommand = null;
}
// Elimina el objeto Connection
if (m_oConnection != null)
{
// Intenta cerrar la conexión
try
{
m_oConnection.Close();
}
catch
{
}
m_oConnection.Dispose();
m_oConnection = null;
}
m_bConnected = false;
if (OpenTransaction)
{
throw new DataAccessException("Se han detectado una o mas Transacciones abiertas al momento de desconectarse de la Base de Datos. No se ha podido completar la operación.", -30);
}
}
catch (DataAccessException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new DataAccessException("Error inesperado al desconectase de la Base de Datos.", ex);
}
}
/// <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);
}
}
private void Inicializa()
{
m_oConnection = null;
m_oCommand = null;
m_oTransaction = null;
m_sConnectionString = null;
m_nroTransaction = 0;
m_nCommandTimeout = 0;
m_nRetryConnect = 3;
m_bDisposed = false;
m_bConnected = false;
m_sProviderAssembly = null;
m_sProviderConnectionClass = null;
m_sProviderCommandBuilderClass = null;
m_eProvider = PROVIDER_TYPE.PROVIDER_NONE;
m_idConexion = 0;
m_oDataReader = null;
}
public void Disconnect()
{
// Disconnect can be called from Dispose and should guarantee no errors
if(!m_bConnected)
return;
if(m_oTransaction != null)
RollbackTransaction(false);
if(m_oCommand != null)
{
m_oCommand.Dispose();
m_oCommand = null;
}
if(m_oConnection != null)
{
try
{
m_oConnection.Close();
}
catch
{
}
m_oConnection.Dispose();
m_oConnection = null;
}
m_bConnected = false;
}
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;
}
