Make your own free website on Tripod.com
EPolWS
DbConnectionBase
Home
Account
ep_test.txt
SerializableBase
DbConnectionBase
EPolESForm
DbSTable
EmailFactory.cs
epmain
EPolES
DbVariableView.cs
ProfileView
EPolLOcal Main
Page Main Example
EPolWS EPolWS.asmx
EPolWS EPolWS.cs
CommandManager
CommandFactory
CommandFactoryBase
OrganisationView
EPolConsole Main
DbEPBase.cs
DbRecordArray
DbTable

Enter subhead content here

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

Enter supporting content here