|
using System;
using System.Data;
//using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Windows.Forms;
using System.Threading;
using System.Collections;
using System.Runtime.CompilerServices;
using System.IO;
using System.Text;
using EXCommon;
//###*
namespace EXDb97
{
///
/// Description résumée de DbConnectionManagement.
///
public class CDbConnectionManagement : CDbEPBase
{
CDbConnectionManagement()
{
ClearAll();
}
public void ClearAll()
{
m_oDatabaseConnectionArray = new ArrayList(5);
m_nCurrentConnection = -1;
m_nConnectionRetry = 0;
s_oDbQuoteMsgConnectionManagement =
s_oDbRequestQuoteConnectionManagement =s_oDbDataThreadConnectionManagement = s_oDbConnectionManagement = null;
}
public void ResetRetryCounter()
{
m_nConnectionRetry = 0;
}
static CDbConnectionManagement s_oDbConnectionManagement, s_oDbDataThreadConnectionManagement, s_oDbQuoteMsgConnectionManagement,
s_oDbRequestQuoteConnectionManagement;
[MethodImpl(MethodImplOptions.Synchronized)]
static public int GetMaxNumberOfConnection()
{
CTrace.i().vWriteLn(ENMessage.Thread, "Thread.CurrentThread.Name = " + Thread.CurrentThread.Name);
if ( Thread.CurrentThread.Name == null)
{
return 50;
}
else if ( Thread.CurrentThread.Name == "DataThread" )
{
return 100;
}
else if ( Thread.CurrentThread.Name == "QuoteMsgThread" )
{
return 10;
}
else if ( Thread.CurrentThread.Name == "RequestQuoteThread" )
{
return 10;
}
return NOT_USED;
}
[MethodImpl(MethodImplOptions.Synchronized)]
static public CDbConnectionManagement Instance()
{
CTrace.i().vWriteLn(ENMessage.Thread, "Thread.CurrentThread.Name = " + Thread.CurrentThread.Name);
if ( Thread.CurrentThread.Name == null)
{
if ( s_oDbConnectionManagement == null)
{
s_oDbConnectionManagement = new CDbConnectionManagement();
}
return s_oDbConnectionManagement;
}
else if ( Thread.CurrentThread.Name == "DataThread" )
{
if ( s_oDbDataThreadConnectionManagement == null)
{
s_oDbDataThreadConnectionManagement = new CDbConnectionManagement();
}
return s_oDbDataThreadConnectionManagement;
}
else if ( Thread.CurrentThread.Name == "QuoteMsgThread" )
{
if ( s_oDbQuoteMsgConnectionManagement == null)
{
s_oDbQuoteMsgConnectionManagement = new CDbConnectionManagement();
}
return s_oDbQuoteMsgConnectionManagement;
}
else if ( Thread.CurrentThread.Name == "RequestQuoteThread" )
{
if ( s_oDbRequestQuoteConnectionManagement == null)
{
s_oDbRequestQuoteConnectionManagement = new CDbConnectionManagement();
}
return s_oDbRequestQuoteConnectionManagement;
}
return null;
}
int m_nCurrentConnection;
bool IsCurrentConnectionClosed()
{
return m_nCurrentConnection < 0 || IsConnectionClosed(m_nCurrentConnection);
}
bool IsCurrentConnectionOpened()
{
return m_nCurrentConnection >=0 && IsConnectionOpened(m_nCurrentConnection);
}
bool IsConnectionClosed(int i)
{
return IsConnectionOpened( i)== false;
}
bool IsConnectionOpened(int i)
{
return GetDatabaseConnection(i).IsBusy;
}
CDbConnection OpenCurrentConnection()
{
if ( IsCurrentConnectionOpened() )
{
return null;
}
GetCurrentConnection().Open();
return GetCurrentConnection();
}
void CloseCurrentConnection()
{
if ( IsCurrentConnectionOpened() )
{
GetCurrentConnection().Close();
}
}
ArrayList m_oDatabaseConnectionArray;
public CDbConnection GetDatabaseConnection(int i)
{
return (CDbConnection)ArrayList.Synchronized(m_oDatabaseConnectionArray)[i];
}
public CDbConnection GetCurrentConnection()
{
if ( m_nCurrentConnection < 0 && m_nCurrentConnection >= m_oDatabaseConnectionArray.Count) return null;
return GetDatabaseConnection(m_nCurrentConnection);
}
public string Dump()
{
StringBuilder oOutput = new StringBuilder();
for(int i=m_oDatabaseConnectionArray.Count-1; i >=0; i-- )
{
oOutput.Append( Thread.CurrentThread.Name + " " + i + " | src = " + GetDatabaseConnection(i).ConnectionString.Substring(46)
+ " Busy " + GetDatabaseConnection(i).IsBusy + /* " State = " + GetDatabaseConnection(i).IsOpenned + */" date = " + GetDatabaseConnection(i).LastUsedDate
);
}
return oOutput.ToString();
}
public CDbConnection GetConnection( string strDatabaseName, string strDatabasePath)
{
bool bIsSpaceForNewConnection = m_oDatabaseConnectionArray.Count < GetMaxNumberOfConnection();
for(int i=m_oDatabaseConnectionArray.Count-1; i >=0; i-- )
{
CTrace.i().vWriteLn(ENMessage.Thread, Thread.CurrentThread.Name + " " + i + " | src = " + GetDatabaseConnection(i).ConnectionString.Substring(46)
+ " Busy " + GetDatabaseConnection(i).IsBusy + /* " State = " + GetDatabaseConnection(i).IsOpenned + */ " date = " + GetDatabaseConnection(i).LastUsedDate
+ " Database = " + GetDatabaseConnection(i).Database + " " + GetDatabaseConnection(i).Dump());
if ( GetDatabaseConnection(i).IsDatabase(strDatabaseName) )
{
if ( IsConnectionClosed(i) )
{
m_nCurrentConnection = i;
return GetDatabaseConnection(i);
}
}
}
int nOldestConnectionIndex = 0;
if ( bIsSpaceForNewConnection == false )
{
for(int i= m_oDatabaseConnectionArray.Count-1; i >=0; i-- )
{
if ( IsConnectionClosed(i) )
{
m_oDatabaseConnectionArray.RemoveAt(i);
CTrace.i().vWriteLn(ENMessage.Thread, "Remove connection " + i);
return CreateNewConnection(strDatabaseName, strDatabasePath);
}
else if ( ((CDbConnection)m_oDatabaseConnectionArray[i]).LastUsedDate
< ((CDbConnection)m_oDatabaseConnectionArray[nOldestConnectionIndex]).LastUsedDate )
{
nOldestConnectionIndex = i;
}
}
}
if ( bIsSpaceForNewConnection == false )
{
CTrace.i().vWriteLn(ENMessage.Thread, "Remove the latest used connection " + nOldestConnectionIndex);
CDbConnection oDbConnection = ((CDbConnection)m_oDatabaseConnectionArray[nOldestConnectionIndex]);
EPAssert( oDbConnection.IsBusy == false, Dump() );
oDbConnection.Close();
m_oDatabaseConnectionArray.RemoveAt(nOldestConnectionIndex);
}
return CreateNewConnection(strDatabaseName, strDatabasePath);
}
public CDbConnection CreateNewConnection( string strDatabaseName, string strDatabasePath)
{
EPAssert( m_oDatabaseConnectionArray.Count < GetMaxNumberOfConnection(), "More than " + GetMaxNumberOfConnection() + "
connection open");
CDbConnection oConnection = CreateConnection( strDatabaseName, strDatabasePath);
m_oDatabaseConnectionArray.Add(oConnection);
m_nCurrentConnection = m_oDatabaseConnectionArray.Count - 1;
CTrace.i().vWriteLn(ENMessage.Thread, "\n\n###new m_nCurrentConnection= " + m_nCurrentConnection + " " + m_oDatabaseConnectionArray.Count
+ GetMaxNumberOfConnection());
CTrace.i().vWriteLn(ENMessage.Thread, " oWorkingDatabaseConnection.Database = " + oConnection.Database);
EPAssert( Contains( oConnection.Database.ToString(),"180012") == false, "Should not connect to 180012");
return oConnection;
}
/*
public string GetDatabasePath( string strDatabaseName,
CEntityAttribute oEntityAttribute, ENStockExchange enStockExchange)
{
string strDatabaseDirectoryPath = null;
if ( strDatabaseName == CONFIGURATION_DB_NAME )
{
strDatabaseDirectoryPath = Environment.GetEnvironmentVariable("EX_DATABASE_CONFIGURATION_PATH");
}
if ( strDatabaseDirectoryPath == null)
{
strDatabaseDirectoryPath = Environment.GetEnvironmentVariable("EX_DATABASE_PATH");
}
if ( strDatabaseDirectoryPath == null)
{
strDatabaseDirectoryPath = DEFAULT_ROOT_PATH + "\\DB";
}
if ( strDatabaseName != CONFIGURATION_DB_NAME && enStockExchange != ENStockExchange.None )
{
strDatabaseDirectoryPath += "\\" + ToStockExchangeName( enStockExchange);
}
string strDatabasePath = strDatabaseDirectoryPath + "\\" + strDatabaseName + ".mdb";
CTrace.i().vWriteLn(ENMessage.Thread, "Create connection to " + strDatabasePath);
return strDatabasePath;
}
*/
CDbConnection CreateConnectionFromAccess2003(string strDatabaseName, string strDatabasePath)
{
//string strDatabasePath = GetDatabasePath(strDatabaseName, oEntityAttribute, enStockExchange);
FileInfo oDatabaseFileInfo = new FileInfo(strDatabasePath);
if ( ! oDatabaseFileInfo.Exists )
{
CTrace.i().vWriteLn(ENMessage.Thread, "oDatabaseFileInfo.Directory = " + oDatabaseFileInfo.Directory);
Directory.CreateDirectory( oDatabaseFileInfo.DirectoryName );
string strDbTemplate = DEFAULT_ROOT_PATH + "\\DB\\TYPE\\ACCESS2003.mdb";
File.Copy( strDbTemplate, oDatabaseFileInfo.FullName, false);
if ( ! File.Exists( oDatabaseFileInfo.FullName ) )
{
CTrace.i().vWriteLn(ENMessage.Thread, "template" + strDbTemplate);
MessageBox.Show( "Can not create the database " + oDatabaseFileInfo.FullName);
}
}
m_nConnectionRetry =0;
return CreateConnection(ACCESS_DATASOURCE_ROOT + strDatabasePath, strDatabaseName);
}
public const string ACCESS_DATASOURCE_ROOT = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
protected int m_nConnectionRetry;
/*
[MethodImpl(MethodImplOptions.Synchronized)]
public CDbConnection CreateConnection(string strConnectionString, string strDabaseName)
{
CDbConnection oConnection = null;
try
{
oConnection = new CDbConnection(strConnectionString, strDabaseName);
return oConnection;
}
catch (SqlException oException)
{
if (ManageException(SQLErrorMessage( oException)) == false) return null;
}
ResetRetryCounter();
return oConnection;
}
*/
[MethodImpl(MethodImplOptions.Synchronized)]
public CDbConnection CreateConnectionFromSQLServer(string strConnectionString, string strDabaseName)
{
CDbConnection oConnection = null;
oConnection = new CDbConnection(strConnectionString, strDabaseName);
return oConnection;
}
[MethodImpl(MethodImplOptions.Synchronized)]
public bool IsTableExist(string strTableName, CDbConnection oDBConnection)
{
string strSql = " IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='" + RemoveBracket(strTableName)
+ "') ";
strSql += " SELECT 1 ELSE SELECT 0";
//string strSql = "select OBJECT_ID ('" + strTableName + "' )";
// string strSql = "select 1 from " + strTableName + " ";
if (oDBConnection.Open() == false)
{
oDBConnection.Close();
return false;
}
CTrace.i().vWriteLn(ENMessage.DbTrace, "strSql = " + strSql );
bool bResult = false;
try
{
oDBConnection.UnsafeSelectCommand(strSql);
}
catch (SqlException oException)
{
vWriteSQLErrorMessage(oException, "");
if ( oException.Errors.Count >= 1 && (oException.Errors[0].Number == 3078 || oException.Errors[0].Number == 208) )
{
bResult = false;
}
else
{
bResult = oDBConnection.Reader.HasRows;
}
}
finally
{
if (oDBConnection.Reader != null)
{
if (oDBConnection.Reader.Read() )
{
int dID= oDBConnection.Reader.GetInt32(0);
bResult = (dID== 1);
}
}
oDBConnection.Close();
}
CTrace.i().vWriteLn(ENMessage.DbTrace, "IsTableExist = " + bResult);
return bResult;
}
protected bool ManageException(string strErrorMessage)
{
TimeSpan oWaitFiveSeconds= new TimeSpan(0,0,5);
Thread.Sleep( oWaitFiveSeconds);
CTrace.i().vWriteLn(ENMessage.sqlTrace, "ManageException try to reconnect once " + m_nConnectionRetry + strErrorMessage +
"\n");
if ( m_nConnectionRetry == LIMIT_MAX_RETRY_CONNECTION)
{
MessageBox.Show("Retry to connect more than " + LIMIT_MAX_RETRY_CONNECTION + " " + strErrorMessage );
return false;
}
m_nConnectionRetry++;
return true;
}
public object GetValue( string strValueColumn, string strTable, CDbConnection oConnection, string strWhere)
{
oConnection.Open();
string strTableNameSelect = "SELECT " + strValueColumn + " FROM " + strTable;
if ( strWhere != null && strWhere.Length > 0 )
{
strTableNameSelect += " where " + strWhere;
}
CDbConnectionManagement.Instance().ResetRetryCounter();
CTrace.i().vWriteLn(ENMessage.DbTrace, "strTableNameSelect = " + strTableNameSelect);
if (oConnection.SelectCommand( strTableNameSelect) == false)
{
return null;
}
object oValue = null;
if ( oConnection.Read() == false )
{
CTrace.i().vWriteLn(ENMessage.sqlError, "\n###There are nothing to read for the table symbol for the condition " + strWhere
+ "###\n");
}
else
{
oValue = oConnection.Reader.GetValue ( 0 );
}
CTrace.i().vWriteLn(ENMessage.DbTrace, "Value = " + oValue + "\n");
oConnection.Close();
return oValue;
}
CDbConnection CreateConnection(string strDatabaseName, string strDatabasePath)
{
// string strDatabasePath = GetDatabasePath(strDatabaseName, oEntityAttribute, enStockExchange);
if (DATABASE_TYPE == ENDatabaseType.DbAccess2003)
{
return CreateConnectionFromAccess2003(strDatabaseName, strDatabasePath);
}
else if (DATABASE_TYPE == ENDatabaseType.SQLServer)
{
EPAssert(strDatabaseName != null, "The database should not be null");
// string strConnectionString = "Provider=SQLNCLI;Server=ALBATOR" + "\\" + "SQLEXPRESS;Database=" + strDatabaseName +
";";
CDbConnection oConnection = CreateConnectionFromSQLServer(SQLSERVER_MASTER_CONECTION_STRING, strDatabaseName);
if (oConnection == null || oConnection.Database != strDatabaseName)
{
CreateDatabase(strDatabaseName);
}
// strConnectionString = "data source=" + ".\\SQLEXPRESS;Initial Catalog =" + strDatabaseName + ";Integrated Security=SSPI";
// oConnection = CreateConnectionFromSQLServer(strConnectionString);
if (oConnection.Database == strDatabaseName)
{
return oConnection;
}
return null;
}
else
{
MessageBox.Show("Not support database type" + DATABASE_TYPE);
return null;
}
}
public const string SQLSERVER_MASTER_CONECTION_STRING = "data source=" + ".\\SQLEXPRESS;Integrated Security=SSPI";
static public bool ExecuteNoQueryOnMaster(string strSql)
{
bool bValid=true;
SqlCommand oDbCommand = new SqlCommand();
oDbCommand.CommandType = CommandType.Text;
oDbCommand.CommandTimeout = 5;
//the sql that was created above
oDbCommand.CommandText = strSql;
//set to the current CDbConnection instance
oDbCommand.Connection = new SqlConnection( SQLSERVER_MASTER_CONECTION_STRING);
try
{
oDbCommand.Connection.Open();
oDbCommand.ExecuteNonQuery();
oDbCommand.Connection.Close();
}
catch (SqlException oException)
{
vWriteSQLErrorMessage(oException, " sql = " + strSql);
if (oException.Errors[0].Number == 911)
// Could not locate entry in sysdatabases for database 'EPOL_PROD3'. No entry found with that name. Make sure that the name
is entered correctly.Number Error Type: 911
{
bValid = false;
}
else
{
MessageBox.Show(oException.Errors[0].Message + " sql = " + strSql + " State = " + oException.Errors[0].Number);
bValid = false;
}
}
return bValid;
}
static public bool CreateDatabase(string strDatabaseName)
{
if (UseDatabase(strDatabaseName) == false)
{
if (ExecuteNoQueryOnMaster("create database " + strDatabaseName))
{
return UseDatabase(strDatabaseName);
}
}
return true;
}
static public bool UseDatabase(string strDatabaseName)
{
bool bRet = ExecuteNoQueryOnMaster("use " + strDatabaseName);
return bRet;
}
}
}
|
