EPolWS
DbTable
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.SqlClient; using System.IO; using System.Windows.Forms; using System.Collections; using System.Diagnostics; using System.Globalization; using System.Threading; using System.Runtime.CompilerServices; using System.Text; using EXCommon; //using EXAttribute; //###2 namespace EXDb97 { public class CDbConnection : CDbEPBase { public CDbConnection(string strConnectionString, string strDabaseName) { EPAssert(Contains(strConnectionString, "INITIALISED") == false, "The stock exchange is unknown"); m_oSqlConnection = new SqlConnection(strConnectionString); m_oSqlConnection.Open(); m_strDabaseName = strDabaseName; m_oSqlConnection.ChangeDatabase(m_strDabaseName); m_oFieldReader = null; m_oLastUsedDate = NOT_USED_DATE; } ~CDbConnection() { Close(); m_oSqlConnection.Close(); } SqlConnection m_oSqlConnection; SqlDataReader m_oFieldReader; string m_strDabaseName; string m_strLastSQL; public string Dump() { return this.IsOpenned + " " + m_strLastSQL; } [MethodImpl(MethodImplOptions.Synchronized)] public bool IsDatabase(string strDatabaseName) { if (DATABASE_TYPE == ENDatabaseType.DbAccess2003) { string strTransformedName = "\\" + strDatabaseName + ".mdb"; return Contains(m_oSqlConnection.DataSource, strTransformedName); } else if (DATABASE_TYPE == ENDatabaseType.SQLServer) { return m_strDabaseName == strDatabaseName; } return false; } // [MethodImpl(MethodImplOptions.Synchronized)] public bool Read() { m_oLastUsedDate = DateTime.UtcNow; if (m_oFieldReader != null) { if (m_oFieldReader.IsClosed == false && m_oFieldReader.Read()) { return true; } else { m_oFieldReader.Close(); m_oFieldReader = null; } } return false; } //int m_nRetry; // [MethodImpl(MethodImplOptions.Synchronized)] public bool UnsafeSelectCommand(string strTableNameSelect) { m_strLastSQL = strTableNameSelect; m_oLastUsedDate = DateTime.Now; SqlCommand oCommand = null; m_oFieldReader = null; try { oCommand = new SqlCommand(strTableNameSelect, m_oSqlConnection); } catch { // Thread.Sleep(100); // oCommand = new SqlCommand(strTableNameSelect, m_oSqlConnection); CTrace.i().vWriteLn(ENMessage.sqlError, "SqlCommand error " + oCommand.ToString()); } finally { if (oCommand != null) { m_oFieldReader = oCommand.ExecuteReader(); // CTrace.i().vWriteSqlLn("m_oFieldReader Depth = " + m_oFieldReader.Depth); } } return m_oFieldReader != null; } public bool SelectCommand(string strTableNameSelect) { Open(); m_strLastSQL = strTableNameSelect; CTrace.i().vWriteLn(ENMessage.sqlTrace, "SQL= " + strTableNameSelect); m_oLastUsedDate = DateTime.UtcNow; bool bRet; try { // m_nRetry = 0; bRet = UnsafeSelectCommand(strTableNameSelect); } catch (SqlException oException) { vWriteSQLErrorMessage(oException, "strTableNameSelect = " + strTableNameSelect); if (oException.Errors[0].Number == 208 || oException.Errors[0].Number == 3078 || oException.Errors[0].Number == 3167) { return false; } //return SelectCommand( strTableNameSelect); MessageBox.Show("Ivalid SQL command " + strTableNameSelect); bRet = false; } catch (Exception oException) { CTrace.i().vWriteSqlLn( oException.Message); /* Thread.Sleep(1000); m_nRetry++; return SelectCommand( strTableNameSelect); * */ bRet = false; } return bRet; } public SqlDataReader Reader { get { m_oLastUsedDate = DateTime.UtcNow; return m_oFieldReader; } } // [MethodImpl(MethodImplOptions.Synchronized)] // [MethodImpl(MethodImplOptions.Synchronized)] public ec dExecuteNoQuery(string strSql) { m_strLastSQL = strSql; ec dRC = ec.ST_SUCCEED; CTrace.i().vWriteLn(ENMessage.sqlTrace, "\n\nstrSql = " + strSql); m_oLastUsedDate = DateTime.UtcNow; Open(); 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 = m_oSqlConnection; try { int dRowChangedTotal = oDbCommand.ExecuteNonQuery(); CTrace.i().vWriteSqlLn( "ExecuteNonQuery dRowChangedTotal = " + dRowChangedTotal); if (dRowChangedTotal == 0) { dRC = ec.ST_SQL_THE_RETURN_ROW_SET_IS_EMPTY; } } catch (SqlException oException) { /* if (DATABASE_TYPE == ENDatabaseType.DbAccess2003) { string strError = ""; if (oException.Errors[0].Number == 3022) { strError= "Failed attempt to insert a field with a duplicate key"; } else if (oException.Errors[0].Number == 3371) { strError= "\nRetry a new time"; } else if (oException.Errors[0].Number == 3159 && Contains(strSql, "delete from")) { strError= "delete from"; } else if (oException.Errors[0].Number == 3260) { strError= "The ressource is locked by another thread"; } else if (oException.Errors[0].Number == 3192 || oException.Errors[0].Number == 3078) { strError= "The ressource is not yet created"; } else if (oException.Errors[0].Number == 3218) { strError= "It is locked for " + strSql; } else if (oException.Errors[0].Number == 3372) { strError= " sql = " + strSql; } else if (oException.Errors[0].Number == 3380) { strError =" sql = " + strSql; } else if (oException.Errors[0].Number == 3381) { strError = " sql = " + strSql; } else if (oException.Errors[0].Number == 1801) // Database 'EPOL_PROD1' already exists. Choose a different database name. { strError = " sql = " + strSql; } else 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 { strError = " sql = " + strSql; dRC = ec.ST_SQL_UNKNOWN_DATABASE_NAME; } else if (oException.Errors[0].Number == 1913) // Operation failed because an index already exist on the table { strError = " sql = " + strSql; dRC = ec.ST_SQL_INDEX_ALREADY_EXIST; } else { CTrace.i().vWriteSqlLn(oException.Errors[0].Number, strError + " sql = " + strSql + " State = " + oException.Errors[0].Number); dRC = ec.ST_SQL_UNKNOWN_EXECUTE_NO_QUERTY_ERROR; } } else { */ vWriteSQLErrorMessage(oException, " sql = " + strSql + "Meessage=" + oException.Errors[0].Message + " State = " + oException.Errors[0].Number); switch (oException.Errors[0].Number) { case 1801: dRC = ec.ST_SQL_DABASE_ALREADY_EXIST; break; case 911: dRC = ec.ST_SQL_UNKNOWN_DATABASE_NAME; break; case 1913: dRC = ec.ST_SQL_INDEX_ALREADY_EXIST; break; case 2627: dRC = ec.ST_SQL_INSERT_RECORD_ALREADY_EXIST; break; default: dRC = ec.ST_SQL_UNKNOWN_EXECUTE_NO_QUERTY_ERROR; break; } // } } catch (Exception oException) { CTrace.i().vWriteSqlLn(oException.Message); } Close(); return dRC; } [MethodImpl(MethodImplOptions.Synchronized)] public ec dUpdateBinary(string strFieldName, byte[] binValue, string strTable, string strWhere) { ec dRC = ec.ST_SUCCEED; if (binValue == null) { binValue = GetSerializedValue(NULL_VALUE); } m_oLastUsedDate = DateTime.UtcNow; try { Open(); // Create SQL command containing ? parameter for BLOB. string strSql = "UPDATE " + strTable + " SET " + strFieldName + "=@binary_field WHERE " + strWhere; CTrace.i().vWriteSqlLn("\nUpdateBinary = " + strSql); SqlCommand cmd = new SqlCommand(strSql, m_oSqlConnection); m_strLastSQL = strSql; // Create parameter for the ? contained in the SQL statement. SqlParameter oParameter = new SqlParameter("@binary_field", SqlDbType.Binary, binValue.Length, ParameterDirection.Input, false, 0, 0, strFieldName, DataRowVersion.Current, binValue); cmd.Parameters.Add(oParameter); // Open connection, execute query, and close connection. int nRowsUpdated = cmd.ExecuteNonQuery(); if (nRowsUpdated == 1) { // CTrace.i().vWriteSqlLn("File2SqlBlob completed successfully.\nPress return to continue."); } else { CTrace.i().vWriteSqlLn(" Error in the update of the binary field " + strFieldName + " " + nRowsUpdated + " updated"); dRC = ec.ST_SQL_BINARY_CAN_FIND_BINARY; } } catch (SqlException ex) { vWriteSQLErrorMessage(ex, ""); dRC = ec.ST_SQL_BINARY_UPDATE_FAILED; } finally { Close(); } return dRC; } public string Database { get { return m_oSqlConnection.Database; } } public string ConnectionString { get { return m_oSqlConnection.ConnectionString; } } public bool IsOpenned { get { return IsClosed == false; } } public bool IsClosed { get { return m_oSqlConnection.State == ConnectionState.Closed || m_oSqlConnection.State == ConnectionState.Broken; } } DateTime m_oLastUsedDate; [MethodImpl(MethodImplOptions.Synchronized)] public virtual void Close() { try { if (m_oFieldReader != null) { m_oFieldReader.Close(); m_oFieldReader = null; } if (m_oSqlConnection != null && IsOpenned) { // CTrace.i().vWriteSqlLn("src = " + m_oSqlConnection.ConnectionString); // CTrace.i().vWriteSqlLn("db = " + m_oSqlConnection.Database); m_oSqlConnection.Close(); } } catch(Exception e) { Debug.WriteLine("e = " + e.Message); } } public DateTime LastUsedDate { get { return m_oLastUsedDate; } } [MethodImpl(MethodImplOptions.Synchronized)] public bool Open() { m_oLastUsedDate = DateTime.UtcNow; // try // { if (IsClosed) { // CTrace.i().vWriteSqlLn("src = " + m_oSqlConnection.ConnectionString); // CTrace.i().vWriteSqlLn("db = " + m_oSqlConnection.Database); m_oSqlConnection.Open(); if (m_oSqlConnection.Database != m_strDabaseName) m_oSqlConnection.ChangeDatabase(m_strDabaseName); } /* } catch(SqlException oOleError) { ManageException(oOleError.ErrorCode + oOleError.Message + " " + CDbConnectionManagement.Instance().Dump()); m_nRetry++; Open(); } m_nRetry=0; */ return true; } public bool IsBusy { get { return m_oFieldReader != null && (m_oFieldReader.IsClosed == false); } } } public class CPageReadErrorException : System.Exception { } ///

/// Description résumée de EXDbCreateTable. /// public abstract class CDbTable : CDbConnectionBase { public CDbTable(string strTableName, bool bShouldRowBeUnique) { Reset(); this.SetName(strTableName); m_bShouldRowBeUnique = bShouldRowBeUnique; } public CDbTable(string strTableName) { Reset(); this.SetName(strTableName); m_bShouldRowBeUnique = false; } byte[] m_oValueBinary; protected CDbTable() { Reset(); } protected ENBoolean m_ebIsTableExist; protected virtual void Reset() { Clear(); m_strTableName = null; m_oFieldNameList = new ArrayList(5); m_oFieldTypeList = new ArrayList(5); m_strValueBinary = null; m_ebIsTableExist = ENBoolean.Unknown; } public virtual bool SetName(string strTableName) { Clear(); m_strTableName = CSerializableBase.ConvertToTableName(strTableName); // CTrace.i().vWriteSqlLn("SetName = " + strTableName + " " + m_bIsTableExist); m_strWhereKey = null; m_bValid = true; return true; } public bool m_bValid; protected string m_strWhereKey; protected int m_nValueTotal, m_nFieldIndex; protected static string BINARY_ARRAY_STORED = "BINARY_ARRAY_STORED"; protected string m_strTableName; public string Name { get { return m_strTableName; } } protected string m_strPrimaryKey; public string m_strMainPrimaryKeyField; public virtual int FieldCount { get { return m_oFieldNameList.Count; } } public string GetFieldName(int i) { return m_oFieldNameList[i].ToString(); } public int GetRecordCount() { int nCount = 0; if (IsTableExist == false) return 0; CDbConnection oConnection = GetConnection(); string strTableNameSelect = "SELECT COUNT(*) FROM " + m_strTableName; CTrace.i().vWriteLn(ENMessage.sqlTrace, "SQL = " + strTableNameSelect + "\n"); oConnection.Open(); oConnection.SelectCommand(strTableNameSelect); while (oConnection.Read()) { nCount = oConnection.Reader.GetInt32(0); } oConnection.Close(); return nCount; } public ArrayList m_oFieldNameList; public ArrayList m_oFieldTypeList; bool m_bShouldRowBeUnique; static string GetType(string strType) { if (Contains(strType, STRING_4000_TYPE)) return "varchar(4000)"; else if (Contains(strType, STRING_255_TYPE)) return "varchar(255)"; else if (Contains(strType, STRING_10_TYPE)) return "char(10)"; else if (Contains(strType, STRING_50_TYPE)) return "varchar(50)"; else if (Contains(strType, STRING_TYPE) || Contains(strType, "String 255")) return "varchar(255)"; else if (Contains(strType, "Integer 64")) return "float"; else if (Contains(strType, INTEGER_TYPE)) return "integer"; else if (Contains(strType, ENTITY_TYPE)) return "integer"; else if (Contains(strType, "Currency")) return "Currency"; else if (Contains(strType, "Decimal")) return "float"; else if (Contains(strType, "Percentage")) return "float"; else if (Contains(strType, DOUBLE_TYPE)) return "float"; else if (Contains(strType, BINARY_TYPE)) return (DATABASE_TYPE == ENDatabaseType.SQLServer) ? "varbinary(4000)" : "oleobject"; else if (Contains(strType, BINARY_COMPRESS_TYPE)) return (DATABASE_TYPE == ENDatabaseType.SQLServer) ? "varbinary(4000)" : "oleobject"; else if (Contains(strType, BOOLEAN_TYPE)) { if (DATABASE_TYPE == ENDatabaseType.SQLServer) { return "bit"; } else { return "logical"; } } else if (Contains(strType, DATE_TYPE)) { if (DATABASE_TYPE == ENDatabaseType.SQLServer) { return "smalldatetime"; } else { return "date"; } } else if (Contains(strType, AUTO_TYPE)) return "integer identity (0,1)"; return strType; } protected object[] ReplaceWithType(string[] sFieldName, object[] oObjs) { object[] oObjectArray = new object[oObjs.Length]; for (int i = 0; i < oObjs.Length; i++) { if (this.sGetTypeOf(sFieldName[i]) == ENTITY_TYPE) { oObjectArray[i] = (P)oObjs[i]; } else { oObjectArray[i] = oObjs[i]; } } return oObjectArray; } protected object ToCorrectType(string sFieldName, object oObj) { object oReturn = oObj; if (sGetTypeOf(sFieldName) == ENTITY_TYPE) { oReturn = (P)oObj ; } return oReturn; } public bool IsTableExist { get { if (m_ebIsTableExist == ENBoolean.Unknown || m_ebIsTableExist == ENBoolean.False) { m_ebIsTableExist = CDbConnectionManagement.Instance().IsTableExist(m_strTableName, GetConnection()) ? ENBoolean.True : ENBoolean.False; } return m_ebIsTableExist == ENBoolean.True ? true: false; } } public ec dDropTable() { string sSql; if (CDbEPBase.DATABASE_TYPE == ENDatabaseType.SQLServer) { sSql = " drop table " + m_strTableName; } else { sSql = " if object_id ( " + m_strTableName + " ) drop table " + m_strTableName; } CTrace.i().vWriteLn(ENMessage.DbTrace, "strSql = " + sSql); ec dRet = ec.ST_SUCCEED; if (CDbConnectionManagement.Instance().IsTableExist(m_strTableName, GetConnection())) { dRet = dExecute(sSql); m_ebIsTableExist = ENBoolean.Unknown; } return dRet; } protected string m_strCreateTableSql; protected string m_sInsertValuesSql; protected string m_strSelectSql; public string sInsertedValues { get { return m_sInsertValuesSql; } } public void vAddInsertValuesFrom(CDbTable oDbTable) { vAddInsertValuesFrom(oDbTable.sInsertedValues); } public void vAddInsertValuesFrom(string sInsertedValues) { if (m_sInsertValuesSql.Length > 0) m_sInsertValuesSql += ','; m_sInsertValuesSql += sInsertedValues; } public virtual void InitialisedColumnList() { m_strSelectSql = ""; m_strWhereKey = null; m_nFieldIndex = 0; } public virtual void vAddColumn(string strFieldName, string strType, bool bPrimaryKey) { //CTrace.i().vWriteSqlLn("vAddColumn = " + strFieldName); if (m_oFieldNameList.Count == 0) { InitialisedColumnList(); } else { m_strCreateTableSql += ", "; m_strSelectSql += ", "; } if (strFieldName[0] != '[') strFieldName = "[" + strFieldName + "]"; m_oFieldNameList.Add(strFieldName); m_oFieldTypeList.Add(strType); m_strCreateTableSql += " " + strFieldName + " " + GetType(strType); if (GetType(strType) == "bit" || strType == AUTO_TYPE) { // m_strCreateTableSql += " yes "; } else { if (bPrimaryKey || strType == AUTO_TYPE) { m_strCreateTableSql += " not null "; } else { m_strCreateTableSql += " null "; } } m_strSelectSql += " " + strFieldName; // Primary Key Management if (bPrimaryKey || strType == AUTO_TYPE) { if (m_nFieldIndex == 0) { m_strPrimaryKey = ""; } else { m_strPrimaryKey += ", "; } m_nFieldIndex++; m_strPrimaryKey += strFieldName; CTrace.i().vWriteLn(ENMessage.DbTrace, " m_strPrimaryKey = " + m_strPrimaryKey); } /* if (DATABASE_TYPE == ENDatabaseType.SQLServer) { if (bPrimaryKey || strType == AUTO_TYPE) { m_strCreateTableSql += " constraint " + CUtility.RemoveBracket(m_strTableName) + "_primary_index primary key clustered (" + m_strPrimaryKey + ")"; } } */ // CTrace.i().vWriteSqlLn("m_strSelectSql = " + m_strSelectSql); } public bool IsField(string strFieldName) { if (strFieldName[0] != '[') strFieldName = "[" + strFieldName + "]"; return m_oFieldNameList.Contains(strFieldName); } bool IsIndexField(int i) { return IsIndexField(m_oFieldNameList[i].ToString()); } public bool IsIndexField(string strFieldName) { return Contains(m_strPrimaryKey, strFieldName); } protected virtual void InitInsertQuery() { m_sInsertValuesSql= m_strWhereKey = ""; m_nValueTotal = 0; } public string sFieldCompare(ENSQLRelation enRelation, string strField, string strValue) { if (enRelation == ENSQLRelation.like) { return sFieldContains(strField, strValue); } return strField + ' ' + sGetRelation(enRelation) + ' ' + FormatValue(strField, strValue); } public string sFieldContains(string strField, string strValue) { return strField + " like %" + FormatValue(strField, strValue) + '%'; } public string sFieldEqual(string strField, string strValue) { return strField + " = " + FormatValue(strField, strValue); } public string sFieldEqual(string strField, int dValue) { return strField + " = " + FormatValue(strField, dValue.ToString() ); } public string sFieldEqual(int nFieldIndex, string strValue) { return m_oFieldNameList[nFieldIndex].ToString() + " = " + FormatValue(nFieldIndex, strValue); } public string sFieldEqual(string strField, bool bValue) { if (bValue) { return strField + " <> " + FormatValue(strField, "0"); } else { return strField + " = " + FormatValue(strField, "0"); } } public virtual bool PrepareLine() { if (m_bShouldRowBeUnique) { return dDeleteCurrent()== ec.ST_SUCCEED; } return true; } public virtual ec dInsertIntoTable() { ec dRC = ec.ST_SUCCEED; if (m_nValueTotal == 0) { CTrace.i().vWriteLn(ENMessage.sqlError, "No field to insert\n"); m_bValid = false; return ec.ST_SQL_INSERT_FAILED_NO_FIELDS; } else if (m_nValueTotal < m_oFieldTypeList.Count) { MessageBox.Show(m_strTableName + " Not enough value for insertion " + m_nValueTotal + " < " + m_oFieldTypeList.Count); m_nValueTotal = 0; m_bValid = false; return ec.ST_SQL_INSERT_FAILED_LESS_VALUE_THAN_FIELD; } m_nValueTotal = 0; dRC = dCreateTable(); string sInsertSql = "insert into " + m_strTableName; sInsertSql += " values (" + m_sInsertValuesSql + ")"; m_sInsertValuesSql = ""; try { PrepareLine(); dRC = dExecute(sInsertSql); CTrace.i().vWriteLn(ENMessage.sqlTrace,"insert = " + sInsertSql); int nBinaryFieldIndex = m_oFieldTypeList.IndexOf(BINARY_TYPE); if (nBinaryFieldIndex >= 0) { string strBinaryFieldName = m_oFieldNameList[nBinaryFieldIndex].ToString(); dRC = dUpdateBinary(strBinaryFieldName, m_oValueBinary); } else { nBinaryFieldIndex = m_oFieldTypeList.IndexOf(BINARY_COMPRESS_TYPE); if (nBinaryFieldIndex >= 0) { string strBinaryFieldName = m_oFieldNameList[nBinaryFieldIndex].ToString(); dRC = dUpdateCompressBinary(strBinaryFieldName, m_strValueBinary); } } m_oValueBinary = null; } catch (SqlException oException) { vWriteSQLErrorMessage(oException, ""); } finally { Close(); } return dRC; } public virtual object GetValue(string strColumnValue, string strWhere) { if (!IsTableExist) { return null; } object oReturn = CDbConnectionManagement.Instance().GetValue(strColumnValue, m_strTableName, GetConnection(), strWhere); if (oReturn == null || oReturn.ToString().Length == 0) return null; return oReturn; } public string GetString(string strColumnValue, string strWhere) { object oValue = GetValue(strColumnValue, strWhere); if (oValue == null || oValue.ToString().Length == 0) return null; return oValue.ToString(); } public int GetInteger(string strColumnValue, string strWhere) { object oValue = GetValue(strColumnValue, strWhere); if (oValue == null) return NOT_USED; return Convert.ToInt32(oValue); } public double GetDouble(string strColumnValue, string strWhere) { object oValue = GetValue(strColumnValue, strWhere); if (oValue == null) return NOT_USED; return Convert.ToDouble(oValue); } public int dGetMax(string strMaxField, string strWhere) { int dMax = NOT_USED; object oMax = GetMax(strMaxField, strWhere); if (oMax != null) { try { dMax = Convert.ToInt32(oMax); } catch { CTrace.i().vWriteLn(ENMessage.sqlWarning,"dGetMax Conversion invalid"); } } return dMax; } public object GetMax(string strMaxField, string strWhere) { if (IsTableExist == false) return NOT_USED; object oMax = CDbConnectionManagement.Instance().GetValue("max(" + strMaxField + ")", m_strTableName, GetConnection(), strWhere); if (oMax == null || oMax.ToString().Length == 0) return null; CTrace.i().vWriteLn(ENMessage.sqlTrace,"Max date " + oMax.ToString() ); return oMax; } public object GetMin(string strMaxField, string strWhere) { object oMin = CDbConnectionManagement.Instance().GetValue("min(" + strMaxField + ")", m_strTableName, GetConnection(), strWhere); if (oMin == null || oMin.ToString().Length == 0) return null; CTrace.i().vWriteLn(ENMessage.sqlTrace,"Max date " + oMin.ToString() ); return oMin; } protected int IndexOf(string strFieldName) { if (strFieldName[0] != '[') strFieldName = "[" + strFieldName + "]"; return m_oFieldNameList.IndexOf(strFieldName); } public virtual string sGetTypeOf(string strFieldName) { int nField = IndexOf(strFieldName); EPAssert(nField >= 0); return m_oFieldTypeList[nField].ToString(); } /* public string FormatValue(string strField, object oValue) { return FormatValue(strField, oValue.ToString()); }*/ public virtual string FormatValue(string strField, string strValue) { int dPointPos = strField.IndexOf('.'); if (dPointPos <= 0) { int nField = IndexOf(strField); return FormatValue(nField, strValue); } else { strField = strField.Substring(dPointPos + 1); int nField = IndexOf(strField); return FormatValue(nField, strValue); } } protected virtual IFormatProvider CultureInfo { get { return new CultureInfo("en-US", true); } } public string FormatDateValue(string strValue) { DateTime dtValue; if (strValue == "29-Feb") { dtValue = new DateTime(2000, 2, 29, 0, 0, 0); } else { try { dtValue = Convert.ToDateTime(strValue); } catch { try { dtValue = Convert.ToDateTime(strValue, CultureInfo); } catch { throw new CPageReadErrorException(); } } } if (DATABASE_TYPE == ENDatabaseType.SQLServer) { return "'" + Convert.ToString(dtValue, CultureInfo) + "'"; } else { return "#" + Convert.ToString(dtValue, CultureInfo) + "#"; } } protected virtual string FormatValue(int nField, string strValue) { string strFieldType = m_oFieldTypeList[nField].ToString(); if (strValue == null || strValue == "") return "''"; if (strValue.Substring(strValue.Length - 1, 1) == "*") { strValue = strValue.Substring(0, strValue.Length - 1); } if (strValue == NO_VALUE_STRING) { if (Contains(strFieldType, STRING_TYPE) || strFieldType == "Memo") { strValue = "'" + NOT_USED_STR + "'"; } else { strValue = NOT_USED_STR; } } /* else if ( strValue.Length < 5 && ( (Contains(strValue, "--") || strValue == "-" || strValue == "NA" || strValue == "N/A" || strValue == "$N/A" || strValue == "NaN" || strValue == "NaN%" || strValue == "N/A%" || strValue == "NM" || strValue.Length == 0 || strValue == NON_APPLICABLE_STRING_ZACKS || strValue == NON_APPLICABLE_STRING_ZACKS_PERCENTAGE) || strValue == "x0") ) { if ( Contains( strFieldType, "Date") ) { strValue = "#" + Convert.ToString(NOT_USED_DATE) + "#"; } else if (Contains( strFieldType, "String")|| strFieldType == "Memo") { strValue = "'" + NO_INFORMATION_STR + "'"; } else { strValue = NO_INFORMATION_STR; } }*/ else if (Contains(strFieldType, STRING_TYPE)) { strValue = " '" + strValue + "' "; } else if (Contains(strFieldType, "Memo")) { strValue = " '" + strValue + "' "; } else if (Contains(strFieldType, BINARY_TYPE)) { if (DATABASE_TYPE == ENDatabaseType.SQLServer) { strValue = " convert (binary(32),'" + strValue + "') "; } else { strValue = " '" + strValue + "' "; } } else if (Contains(strFieldType, INTEGER_TYPE)) { strValue = CTypeNormalizer.Integer(strValue); if (strValue == CTypeNormalizer.OUT_OF_RANGE && (!Contains(strFieldType, "64"))) { MessageBox.Show("The value " + strValue + " is too big for the field " + m_oFieldNameList[nField] + " of type " + strFieldType); strValue = NO_INFORMATION_STR; } } else if (Contains(strFieldType, "Date French")) { DateTime dtValue = Convert.ToDateTime(strValue); strValue = "'" + Convert.ToString(dtValue) + "'"; } else if (Contains(strFieldType, DATE_TYPE)) { strValue = FormatDateValue(strValue); } else if (Contains(strFieldType, "Decimal Million")) { double dValue = 1000000 * double.Parse(strValue, NumberStyles.AllowLeadingSign | NumberStyles.Number | NumberStyles.AllowDecimalPoint | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands | NumberStyles.AllowParentheses, CultureInfo); strValue = Convert.ToString(dValue); } else if (Contains(strFieldType, "Decimal Thousand")) { double dValue = 1000 * double.Parse(strValue, NumberStyles.AllowLeadingSign | NumberStyles.Number | NumberStyles.AllowDecimalPoint | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands | NumberStyles.AllowParentheses, CultureInfo); strValue = Convert.ToString(dValue); } else if (Contains(strFieldType, "Decimal")) { strValue = CTypeNormalizer.Decimal(strValue); } else if (Contains(strFieldType, "Percentage")) { if (strValue == "%") { strValue = NOT_USED_STR; } else { strValue = CTypeNormalizer.Percentage(strValue); } } else if (Contains(strFieldType, "Currency Million")) { double dValue; try { dValue = 1000000 * double.Parse(strValue, NumberStyles.AllowLeadingSign | NumberStyles.Number | NumberStyles.AllowDecimalPoint | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands | NumberStyles.AllowParentheses, CultureInfo); } catch { dValue = 1000000 * double.Parse(strValue, NumberStyles.AllowLeadingSign | NumberStyles.Number | NumberStyles.AllowDecimalPoint | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands | NumberStyles.AllowParentheses, new CultureInfo("en-US", true)); } strValue = Convert.ToString(dValue); } else if (Contains(strFieldType, "Currency Thousand")) { double dValue = 1000 * double.Parse(strValue, NumberStyles.AllowLeadingSign | NumberStyles.Number | NumberStyles.AllowDecimalPoint | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands | NumberStyles.AllowParentheses, CultureInfo); strValue = Convert.ToString(dValue); } else if (Contains(strFieldType, "Currency")) { strValue = CTypeNormalizer.Currency(strValue); } else if (Contains(strFieldType, BINARY_TYPE)) { m_strValueBinary = strValue; strValue = DEFAULT_BINARY_VALUE; } else if (Contains(strFieldType, BOOLEAN_TYPE) && (DATABASE_TYPE == ENDatabaseType.SQLServer)) { if (strValue.ToLower() == "true" || strValue != "0") { strValue = Convert.ToString(1); } else { strValue = Convert.ToString(0); } } return strValue; } const string DEFAULT_BINARY_VALUE = "'BINARY'"; public virtual void Clear() { m_nValueTotal = 0; m_bValid = true; m_sInsertValuesSql = null; } public void vAddAutoIncrement(out int dIndex) { vAddAutoIncrement(0, out dIndex); } public void vAddAutoIncrement(int dStartValue, out int dIndex) { dIndex = dGetNextIncrement(this.m_oFieldNameList[m_nValueTotal].ToString(), dStartValue); vAddValue(dIndex); } public int dGetNextIncrement(string sColumnName, int dStartValue) { int dMax = this.dGetMax(sColumnName, null); if (IsNotUsed(dMax) || dMax < dStartValue) { return dStartValue; } else { return dMax + 1; } } public virtual void vAddValue(string strValue) { CTrace.i().vWriteLn(ENMessage.DbTrace,"m_oFieldTypeList.Count = " + m_oFieldTypeList.Count); CTrace.i().vAssert(m_nValueTotal < m_oFieldTypeList.Count); if (m_nValueTotal == 0) { if (m_strPrimaryKey == null) { CTrace.i().vAssert(false, "There is no primary index in the schema of the table"); } InitInsertQuery(); } else /*if (Contains(m_oFieldTypeList[m_nValueTotal - 1].ToString(), AUTO_TYPE) == false)*/ { CTrace.i().vWriteSqlLn("mm_oFieldTypeList = " + m_oFieldTypeList[m_nValueTotal - 1].ToString()); m_sInsertValuesSql += ", "; } string strField = m_oFieldNameList[m_nValueTotal].ToString(); string strValueFormatted = FormatValue(strField, strValue); m_sInsertValuesSql += strValueFormatted; if (IsIndexField(m_nValueTotal)) { AddToSqlWhere(ref m_strWhereKey, strField, strValueFormatted); } m_nValueTotal++; CTrace.i().vWriteSqlLn(strField + " = " + strValue + " (" + m_nValueTotal + ") "); } // Can not be virtual public virtual void vAddAutoIncrementAndKeyValue(string strValue) { CTrace.i().vWriteLn(ENMessage.DbTrace,"m_oFieldTypeList.Count = " + m_oFieldTypeList.Count); CTrace.i().vAssert( m_nValueTotal < m_oFieldTypeList.Count); if (m_nValueTotal == 0) { if (m_strPrimaryKey == null) { MessageBox.Show("There is no primary index in the schema of the table"); } InitInsertQuery(); } else if (Contains(m_oFieldTypeList[m_nValueTotal - 1].ToString(), AUTO_TYPE) == false) { // CTrace.i().vWriteSqlLn("mm_oFieldTypeList = " + m_oFieldTypeList[m_nValueTotal - 1].ToString()); m_sInsertValuesSql += ", "; } m_nValueTotal++; string strField = m_oFieldNameList[m_nValueTotal].ToString(); string strValueFormatted = FormatValue(strField, strValue); m_sInsertValuesSql += strValueFormatted; AddToSqlWhere(ref m_strWhereKey, strField, strValueFormatted); m_nValueTotal++; CTrace.i().vWriteSqlLn(strField + " = " + strValue + " (" + m_nValueTotal + ") "); } public void AddToSqlWhere(ref string strSqlWhere, string strField, string strValueFormatted) { if (strSqlWhere != "") { strSqlWhere += AND; } // EPAssert(IsUsed(strValueFormatted), strValueFormatted + " should be used"); strSqlWhere += strField + " = " + strValueFormatted; } string m_strValueBinary; // Can not be virtual public void vAddValue(DateTime oDate) { Trace.Assert(m_nValueTotal < m_oFieldTypeList.Count); if (DATABASE_TYPE != ENDatabaseType.SQLServer) { vAddValue(" '" + oDate.ToString(CultureInfo) + "' "); } else { vAddValue(oDate.ToString(CultureInfo)); } } public void vAddValue(bool bValue) { Trace.Assert(m_nValueTotal < m_oFieldTypeList.Count); if (DATABASE_TYPE == ENDatabaseType.SQLServer) { vAddValue(bValue ? 1 : 0); } else { vAddValue(bValue.ToString()); } } public void vAddValue(SProperty[] oPropertyArray) { vAddValue(GetSerializedValue(oPropertyArray)); } public void vAddValue(byte[] binValue) { m_oValueBinary = binValue; CTrace.i().vWrite(ENMessage.DbTrace, " m_oValueBinary = " + Deserialize(m_oValueBinary) ); vAddValue(BINARY_ARRAY_STORED); } // Can not be virtual public virtual void vAddValue(double dValue) { vAddValue(Convert.ToString(dValue)); } public virtual void vAddValue(int nValue) { vAddValue(Convert.ToString(nValue)); } public virtual void vAddValue(P dValue) { vAddValue( ((int)dValue).ToString()); } protected ec dCreatePrimaryIndex() { if (m_strPrimaryKey != null && m_strPrimaryKey != "") { if (DATABASE_TYPE == ENDatabaseType.DbAccess2003) { return dCreateIndex(1, m_strPrimaryKey); } else { // string strSql = "create clustered " + m_strTableName + "_index1 on " + m_strTableName + " ("; // strSql += m_strPrimaryKey + ")"; // return Execute(strSql); return ec.ST_SUCCEED; } } else { m_nValueTotal = 0; return ec.ST_SUCCEED; } m_nFieldIndex = m_nValueTotal = 0; m_bValid = true; return ec.ST_SUCCEED; } public virtual ec dCreateIndex() { return dCreatePrimaryIndex(); } public ec dReIndex() { string strSql = "drop index " + RemoveBracket(m_strTableName) + "_primary_index on " + m_strTableName; try { dExecute(strSql); } catch (Exception oException) { CTrace.i().vWriteSqlLn(oException.Message); } return dCreatePrimaryIndex(); } public virtual ec dCreateIndexMultipleField(int nIndexRank, params string[] strFieldArray) { return dCreateIndex(nIndexRank, sConvert(strFieldArray)); } protected ec dCreateIndex(int nIndexRank, string strField) { string sName = (nIndexRank == 1 ? RemoveBracket(m_strTableName) + "_primary_index" : RemoveBracket(m_strTableName) + "_index1" + nIndexRank); string strSql = " create index " + sName + " on " + m_strTableName + " ("; strSql += strField; strSql += ") " + (nIndexRank == 1 ? " with primary " : ""); return dExecute(strSql); } public virtual bool AddNewColumn(string strFieldName, string strType) { m_nFieldIndex = m_nValueTotal = 0; m_bValid = true; CTrace.i().vWriteLn(ENMessage.DbTrace,"CreateTable = " + IsTableExist); if (IsTableExist ) { EPAssert(m_strTableName.Length > 0, "Should have a name"); string strAlterTableSql = "alter table " + m_strTableName + " Add Column [" + strFieldName + "] " + GetType(strType); try { CTrace.i().vWriteSqlLn(strAlterTableSql); dExecute(strAlterTableSql); } catch (SqlException oOleError) { if (oOleError.Number == 3380) { CTrace.i().vWriteSqlLn("Column exists already"); } else { MessageBox.Show(oOleError.Number + oOleError.Message + " " + CDbConnectionManagement.Instance().Dump()); } } } return m_bValid; } public virtual bool DropColumn(string strFieldName) { m_nFieldIndex = m_nValueTotal = 0; m_bValid = true; CTrace.i().vWrite(ENMessage.DbTrace,"CreateTAble = " + IsTableExist); if (IsTableExist ) { EPAssert(m_strTableName.Length > 0, "Should have a name"); string strAlterTableSql = "alter table " + m_strTableName + " Drop Column [" + strFieldName + "] "; try { CTrace.i().vWriteSqlLn(strAlterTableSql); dExecute(strAlterTableSql); } catch (SqlException oOleError) { if (oOleError.Number == 3380) { CTrace.i().vWriteSqlLn("Column exists already"); } else { MessageBox.Show(oOleError.Number + oOleError.Message + " " + CDbConnectionManagement.Instance().Dump()); } } } return m_bValid; } public virtual ec dCreateTable() { ec dRC; m_nFieldIndex = m_nValueTotal = 0; m_bValid = true; CTrace.i().vWrite(ENMessage.DbTrace,"CreateTAble = " + IsTableExist); if (IsTableExist == false ) { m_ebIsTableExist = ENBoolean.Unknown; EPAssert(m_strTableName.Length > 0, "Should have a name"); string strCreateTableSql =" create table " + m_strTableName + " (" + m_strCreateTableSql; if (DATABASE_TYPE == ENDatabaseType.SQLServer) { if (m_strPrimaryKey != null && m_strPrimaryKey != "") strCreateTableSql += " constraint " + RemoveBracket(m_strTableName) + "_primary_index primary key clustered (" + m_strPrimaryKey + ")"; } strCreateTableSql += ") "; dRC = dExecute(strCreateTableSql); if (dRC == ec.ST_SUCCEED || dRC == ec.ST_SQL_THE_RETURN_ROW_SET_IS_EMPTY) { dRC = dCreateIndex(); } } else { dRC = ec.ST_SQL_CREATE_TABLE_ALREADY_EXIST; } return dRC; } public virtual ec dDelete(string strWhere) { m_strWhereKey = strWhere; if (IsTableExist) { m_nFieldIndex = m_nValueTotal = 0; string strSql = "delete from " + m_strTableName + " where " + strWhere; EPAssert(strWhere.Length > 0); return dExecute(strSql); } return ec.ST_SQL_DELETE_TABLE_NOT_EXISTING; } public virtual ec dUpdate(string strField, int dValue, string strWhere) { return dUpdate(strField, dValue.ToString(), strWhere); } public virtual ec dUpdate(string strField, string strValue, string strWhere) { m_nFieldIndex = m_nValueTotal = 0; CTrace.i().vWriteLn(ENMessage.DbTrace, strValue + " -> " + strField + "\n"); string strSql = "update " + m_strTableName + " set " + sFieldEqual(strField, strValue); if (strWhere.Length > 0) { strSql += " where " + strWhere; } m_strWhereKey = strWhere; return dExecute(strSql); } public virtual void Increment(string strField, string strWhere) { CTrace.i().vWriteSqlLn("SQL IncrementReference " + strWhere); string strSql = "update " + m_strTableName + " set " + strField + " = " + strField + " + 1 where " + strWhere; dExecute(strSql); m_strWhereKey = strWhere; } public void Decrement(string strField, string strWhere) { CTrace.i().vWriteSqlLn("SQL DecrementReference " + strWhere); string strSql = "update " + m_strTableName + " set " + strField + " = " + strField + " - 1 where " + strWhere; dExecute(strSql); m_strWhereKey = strWhere; } public ec dUpdate( string strField1, string strField2, string strField3, string strField4, string strField5, string strValue1, string strValue2, string strValue3, string strValue4, string strValue5, string strWhere) { m_nFieldIndex = m_nValueTotal = 0; string strSql = "update " + m_strTableName + " set " + sFieldEqual(strField1, strValue1); if (strField2 != "") strSql += ", " + sFieldEqual(strField2, strValue2); if (strField3 != "") strSql += ", " + sFieldEqual(strField3, strValue3); if (strField4 != "") strSql += ", " + sFieldEqual(strField4, strValue4); if (strField5 != "") strSql += ", " + sFieldEqual(strField5, strValue5); if (strWhere.Length > 0) { strSql += " where " + strWhere; } m_strWhereKey = strWhere; return dExecute(strSql); } public ec dUpdate( string strField1, string strField2, string strField3, string strValue1, string strValue2, string strValue3, string strWhere) { m_nFieldIndex = m_nValueTotal = 0; string strSql = "update " + m_strTableName + " set " + sFieldEqual(strField1, strValue1); if (strField2 != "") strSql += ", " + sFieldEqual(strField2, strValue2); if (strField3 != "") strSql += ", " + sFieldEqual(strField3, strValue3); if (strWhere.Length > 0) { strSql += " where " + strWhere; } m_strWhereKey = strWhere; return dExecute(strSql); } public virtual bool StartSelection() { return StartSelection(null); } protected string m_strWhereSelection; public ec dExecute(string strSql) { m_nFieldIndex = m_nValueTotal = 0; CDbConnection oConnection; oConnection = GetConnection(); ec dRC= oConnection.dExecuteNoQuery(strSql); if (dRC == ec.ST_SQL_THE_RETURN_ROW_SET_IS_EMPTY) dRC = ec.ST_SUCCEED; oConnection.Close(); return dRC; } public virtual bool StartSelection(string strWhere) { string strSelect = "SELECT " + m_strSelectSql + " FROM " + m_strTableName; if (strWhere != null && strWhere.Length > 0) { strSelect += " where " + strWhere; } CTrace.i().vWriteLn(ENMessage.sqlTrace, "StartSelection SQL = " + strSelect + "\n"); m_strWhereSelection = strWhere; if (m_oDbCurrentConnection != null) { CTrace.i().vWriteLn(ENMessage.sqlWarning, "Old value " + m_oDbCurrentConnection.Dump()); m_oDbCurrentConnection.Close(); } m_oDbCurrentConnection = GetConnection(); return m_oDbCurrentConnection.SelectCommand(strSelect); } protected CDbConnection m_oDbCurrentConnection; public bool NextRecord(out CDbRecordArray oValueArray) { oValueArray = null; bool bRet = NextRecord(); if (bRet) { oValueArray.Fill(m_oDbCurrentConnection); } return bRet; } public virtual bool NextRecord() { if ((m_oDbCurrentConnection.Reader == null || m_oDbCurrentConnection.Reader.IsClosed == true) || (m_oDbCurrentConnection.Read() == false)) { CTrace.i().vWriteSqlLn(" DbTable NextRecord() bRet == false Close()"); Close(); return false; } return true; } public virtual void Close() { if (m_oDbCurrentConnection != null) m_oDbCurrentConnection.Close(); } protected ec dDeleteCurrent() { return dDelete(m_strWhereKey); } public ec dUpdateCompressBinary(string strFieldName, string strBinary) { // System.Text.Encoding.ASCII.GetBytes( byte[] binValue = CCompression.Compress(strBinary); if (binValue != null) { return dUpdateBinary(strFieldName, binValue); } return ec.ST_SQL_BINARY_CAN_FIND_BINARY; } public ec dUpdateBinary(string strFieldName, byte[] binValue) { return dUpdateBinary(strFieldName, binValue, m_strWhereKey); } public ec dUpdateBinary(string strFieldName, byte[] binValue, string strWhereKey) { return GetConnection().dUpdateBinary(strFieldName, binValue, m_strTableName, strWhereKey); } public virtual string DateFieldName { get { return DATE_FIELD; } } public string GetMaxDate(string strWhere) { object oMaxDate = GetMax(DateFieldName, strWhere); if (oMaxDate == null || oMaxDate.ToString().Length == 0) return null; return oMaxDate.ToString(); } public DateTime oGetDate(string sColumnName, string strWhere) { object oDate = GetValue(sColumnName, strWhere); if (oDate == null ) return NOT_USED_DATE; return Convert.ToDateTime(oDate); } public virtual object GetRecordValue(string strFieldName) { int nIndex = IndexOf(strFieldName); if (nIndex == -1) { MessageBox.Show(strFieldName + " is not in the list of field"); return false; } return m_oDbCurrentConnection.Reader.GetValue(nIndex); } public virtual double GetRecordValueToDouble(string strFieldName) { return Convert.ToDouble(GetRecordValue(strFieldName)); } public string GetRecordStringFromBinaryValue(string strFieldName) { int nIndex = IndexOf(strFieldName); if (nIndex == -1) { MessageBox.Show(strFieldName + " is not a field"); return null; } byte[] binValue = new byte[4 * 4096]; m_oDbCurrentConnection.Reader.GetBytes(nIndex, 0, binValue, 0, 4 * 4096); if (binValue == null || binValue.Length == 0) return null; string strValue = CCompression.DeCompress(binValue); if (strValue == null || strValue == DEFAULT_BINARY_VALUE) return null; return strValue; } /* public virtual CIndexKey CreateInstance(CEntityAttribute oEntityAttribute) { CIndexKey oDbIndexKey = null; if ( oEntityAttribute.Is(CEntityAttributeType.ENEntityType.QuoteEntity) ) { oDbIndexKey = new CTicker( oEntityAttribute); } else if (oEntityAttribute.Is(CEntityAttributeType.ENEntityType.Flux) ) { if ( oEntityAttribute.Is(CEntityAttributeType.ENEntityType.SyntheticFlux) ) { oDbIndexKey = new CSyntheticFluxKey( ); } else if ( oEntityAttribute.Is(CEntityAttributeType.ENEntityType.PageDownloadFlux) ) { oDbIndexKey = new CPageDownloadFluxKey(); } } else if (oEntityAttribute.Is(CEntityAttributeType.ENEntityType.StockExchange) ) { oDbIndexKey = new CUnaryKey(oEntityAttribute); } else if (oEntityAttribute.Is(CEntityAttributeType.ENEntityType.DataSerial) ) { oDbIndexKey = new CDataSerialKey(oEntityAttribute); } v EPAssert(oDbIndexKey != null, " No instance found for " + oEntityAttribute.ToString()); return oDbIndexKey; } public CIndexKey CreateInstance(CEntityAttribute oEntityAttribute, string strInstruction) { CIndexKey oDbIndexKey = CreateInstance(oEntityAttribute); CTrace.i().vWriteLn(ENMessage.sqlDb, "NextKey = " + strInstruction); oDbIndexKey.Set(strInstruction); return oDbIndexKey; } */ public string csDumpTable() { if (IsTableExist == false) return null; CDbConnection oConnection = GetConnection(); string strTableNameSelect = "SELECT * FROM " + m_strTableName; CTrace.i().vWriteLn(ENMessage.sqlTrace, "SQL = " + strTableNameSelect + "\n"); oConnection.Open(); oConnection.SelectCommand(strTableNameSelect); object[] oValues = new object[20]; StringBuilder oReport = new StringBuilder(); oReport.Append(this.m_strTableName + "\n"); while (oConnection.Read()) { int dCount = oConnection.Reader.GetValues(oValues); for(int i=0; i

Enter supporting content here