[Quantproject-developers] QuantProject/b2_DataAccess SQLBuilder.cs, 1.4, 1.5 SqlExecutor.cs, 1.3, 1
Brought to you by:
glauco_1
|
From: Glauco S. <gla...@us...> - 2008-12-30 00:15:09
|
Update of /cvsroot/quantproject/QuantProject/b2_DataAccess In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv17032/b2_DataAccess Modified Files: SQLBuilder.cs SqlExecutor.cs Log Message: Now bot Access and MySql are supported Index: SQLBuilder.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/SQLBuilder.cs,v retrieving revision 1.4 retrieving revision 1.5 diff -C2 -d -r1.4 -r1.5 *** SQLBuilder.cs 24 Nov 2008 21:05:12 -0000 1.4 --- SQLBuilder.cs 30 Dec 2008 00:14:52 -0000 1.5 *************** *** 40,58 **** } ! internal static string GetDateConstant( DateTime dateTime ) { ! string getDateConstant; ! getDateConstant = "#" + dateTime.Month + "/" + dateTime.Day + "/" + dateTime.Year + "#"; return getDateConstant; } ! /// <summary> ! /// returns a string to be used as a DateTime constant for a query ! /// for an Access database ! /// </summary> ! /// <param name="dateTime"></param> ! /// <returns></returns> ! internal static string GetDateTimeConstant( DateTime dateTime ) { string dateTimeConstant = --- 40,87 ---- } ! #region GetDateConstant ! private static string getDateConstantForAccess( DateTime dateTime ) { ! string dateConstantForAccess = "#" + dateTime.Month + "/" + dateTime.Day + "/" + dateTime.Year + "#"; + return dateConstantForAccess; + } + private static string getDateConstantForMySQL( DateTime dateTime ) + { + string dateConstantForMySql = + "'" + + dateTime.Year + "-" + + dateTime.Month + "-" + + dateTime.Day + + // " " + + // dateTime.Hour + ":" + + // dateTime.Minute + ":" + + // dateTime.Second + + "'"; + return dateConstantForMySql; + } + internal static string GetDateConstant( DateTime dateTime ) + { + string getDateConstant = null; + switch ( ConnectionProvider.DbType ) + { + case DbType.Access: + getDateConstant = + SQLBuilder.getDateConstantForAccess( dateTime ); + break; + case DbType.MySql: + getDateConstant = + SQLBuilder.getDateConstantForMySQL( dateTime ); + break; + default: + throw new Exception( + "Unknown database type. Complete the switch statement, please" ); + } return getDateConstant; } + #endregion GetDateConstant ! #region GetDateTimeConstant ! private static string getDateTimeConstantForAccess( DateTime dateTime ) { string dateTimeConstant = *************** *** 65,70 **** --- 94,138 ---- dateTime.Second + "#"; + return dateTimeConstant; + } + private static string getDateTimeConstantForMySQL( DateTime dateTime ) + { + string dateTimeConstant = + "'" + + dateTime.Year + "-" + + dateTime.Month + "-" + + dateTime.Day + " " + + dateTime.Hour + ":" + + dateTime.Minute + ":" + + dateTime.Second + + "'"; + return dateTimeConstant; + } + /// <summary> + /// returns a string to be used as a DateTime constant for a query + /// for an Access database + /// </summary> + /// <param name="dateTime"></param> + /// <returns></returns> + internal static string GetDateTimeConstant( DateTime dateTime ) + { + string dateTimeConstant = null; + switch ( ConnectionProvider.DbType ) + { + case DbType.Access: + dateTimeConstant = + SQLBuilder.getDateTimeConstantForAccess( dateTime ); + break; + case DbType.MySql: + dateTimeConstant = + SQLBuilder.getDateTimeConstantForMySQL( dateTime ); + break; + default: + throw new Exception( + "Unknown database type. Complete the switch statement, please" ); + } return dateTimeConstant; } + #endregion GetDateTimeConstant #region GetTimeConstant *************** *** 83,93 **** // private static void getFilterForTime_checkParameters( DateTime time ) // { ! //// if ( ( comparisonOperator != "=" ) && ! //// ( comparisonOperator != "<" ) && ! //// ( comparisonOperator != "<=" ) && ! //// ( comparisonOperator != ">" ) && ! //// ( comparisonOperator != ">=" ) ) ! //// throw new Exception( ! //// "comparisonOperator can either be '=' or '<' or '<=' or '>' or '>='" ); // if ( !ExtendedDateTime.IsTime( time ) ) // throw new Exception( --- 151,161 ---- // private static void getFilterForTime_checkParameters( DateTime time ) // { ! //// if ( ( comparisonOperator != "=" ) && ! //// ( comparisonOperator != "<" ) && ! //// ( comparisonOperator != "<=" ) && ! //// ( comparisonOperator != ">" ) && ! //// ( comparisonOperator != ">=" ) ) ! //// throw new Exception( ! //// "comparisonOperator can either be '=' or '<' or '<=' or '>' or '>='" ); // if ( !ExtendedDateTime.IsTime( time ) ) // throw new Exception( *************** *** 97,100 **** --- 165,189 ---- #region getFilterForTime_actually + + #region getFormatFunctionForTime + private static string getFormatFunctionForTime( string dateTimeFieldName ) + { + string formatFunctionForTime = null; + switch ( ConnectionProvider.DbType ) + { + case DbType.Access: + formatFunctionForTime = "Format([" + dateTimeFieldName + "],'hh.mm.ss')"; + break; + case DbType.MySql: + formatFunctionForTime = "Date_Format(" + dateTimeFieldName + ",'%H.%i.%s')"; + break; + default: + throw new Exception( + "Unknown database type. Complete the switch statement, please" ); + } + return formatFunctionForTime; + } + #endregion getFormatFunctionForTime + private static string getSqlStringForComparisonOperator( SqlComparisonOperator sqlComparisonOperator ) *************** *** 125,129 **** { string filterForDailyTime = ! "(Format([baDateTimeForOpen],'hh.mm.ss')" + SQLBuilder.getSqlStringForComparisonOperator( sqlComparisonOperator ) + SQLBuilder.GetTimeConstant( time ) + ")"; --- 214,220 ---- { string filterForDailyTime = ! // "(Format([baDateTimeForOpen],'hh.mm.ss')" + ! "(" + ! SQLBuilder.getFormatFunctionForTime( "baDateTimeForOpen" ) + SQLBuilder.getSqlStringForComparisonOperator( sqlComparisonOperator ) + SQLBuilder.GetTimeConstant( time ) + ")"; *************** *** 151,154 **** --- 242,268 ---- } #endregion GetFilterForTime + + /// <summary> + /// returns the name of the function used by the current database type, + /// to compute the standard deviation + /// </summary> + /// <returns></returns> + public static string GetStandardDeviationFunctionName() + { + string setStandardDeviationFunctionName = null; + switch ( ConnectionProvider.DbType ) + { + case DbType.Access: + setStandardDeviationFunctionName = "StDev"; + break; + case DbType.MySql: + setStandardDeviationFunctionName = "STDDEV_POP"; + break; + default: + throw new Exception( + "Unknown database type. Complete the switch statement, please" ); + } + return setStandardDeviationFunctionName; + } } } Index: SqlExecutor.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/SqlExecutor.cs,v retrieving revision 1.3 retrieving revision 1.4 diff -C2 -d -r1.3 -r1.4 *** SqlExecutor.cs 8 May 2004 17:17:47 -0000 1.3 --- SqlExecutor.cs 30 Dec 2008 00:14:52 -0000 1.4 *************** *** 1,5 **** using System; using System.Data; ! using System.Data.OleDb; namespace QuantProject.DataAccess --- 1,5 ---- using System; using System.Data; ! using System.Data.Common; namespace QuantProject.DataAccess *************** *** 19,39 **** { DataTable dataTable = new DataTable(); ! OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter( SqlQuery , ConnectionProvider.OleDbConnection ); ! oleDbDataAdapter.Fill( dataTable ); return dataTable; } ! public static void SetDataTable( string SqlQuery , DataTable dataTable ) { ! OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter( SqlQuery , ConnectionProvider.OleDbConnection ); ! oleDbDataAdapter.Fill( dataTable ); } ! public static void ExecuteNonQuery( string SqlNonQuery ) { ! if(ConnectionProvider.OleDbConnection.State != ConnectionState.Open) ! ConnectionProvider.OleDbConnection.Open(); ! OleDbCommand oleDbCommand = new OleDbCommand( SqlNonQuery , ! ConnectionProvider.OleDbConnection ); ! ! oleDbCommand.ExecuteNonQuery(); } } --- 19,43 ---- { DataTable dataTable = new DataTable(); ! // OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter( SqlQuery , ConnectionProvider.DbConnection ); ! DbDataAdapter dbDataAdapter = ! DbDataAdapterProvider.GetDbDataAdapter( SqlQuery ); ! dbDataAdapter.Fill( dataTable ); return dataTable; } ! public static void SetDataTable( string sqlQuery , DataTable dataTable ) { ! DbDataAdapter dbDataAdapter = ! DbDataAdapterProvider.GetDbDataAdapter( sqlQuery ); ! dbDataAdapter.Fill( dataTable ); } ! public static int ExecuteNonQuery( string sqlNonQuery ) { ! if(ConnectionProvider.DbConnection.State != ConnectionState.Open) ! ConnectionProvider.DbConnection.Open(); ! // OleDbCommand oleDbCommand = new OleDbCommand( SqlNonQuery , ! // ConnectionProvider.DbConnection ); ! DbCommand dbCommand = DbCommandProvider.GetDbCommand( sqlNonQuery ); ! int numberOfRowsAffected = dbCommand.ExecuteNonQuery(); ! return numberOfRowsAffected; } } |