[Quantproject-developers] QuantProject/b2_DataAccess SQLBuilder.cs, 1.4, 1.5 SqlExecutor.cs, 1.3, 1.4

[Glauco S. <gla...@us...>]

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