diff options
Diffstat (limited to 'src/app/Cmpp298.Assignment3.DataAccess')
14 files changed, 451 insertions, 0 deletions
diff --git a/src/app/Cmpp298.Assignment3.DataAccess/Cmpp298.Assignment3.DataAccess.csproj b/src/app/Cmpp298.Assignment3.DataAccess/Cmpp298.Assignment3.DataAccess.csproj new file mode 100644 index 0000000..e797478 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/Cmpp298.Assignment3.DataAccess.csproj @@ -0,0 +1,59 @@ +<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
+ <PropertyGroup>
+ <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
+ <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
+ <ProductVersion>8.0.50727</ProductVersion>
+ <SchemaVersion>2.0</SchemaVersion>
+ <ProjectGuid>{48B09B0E-F2D5-463D-9FAB-C1781CA46D4D}</ProjectGuid>
+ <OutputType>Library</OutputType>
+ <AppDesignerFolder>Properties</AppDesignerFolder>
+ <RootNamespace>Cmpp298.Assignment3.DataAccess</RootNamespace>
+ <AssemblyName>Cmpp298.Assignment3.DataAccess</AssemblyName>
+ </PropertyGroup>
+ <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
+ <DebugSymbols>true</DebugSymbols>
+ <DebugType>full</DebugType>
+ <Optimize>false</Optimize>
+ <OutputPath>bin\Debug\</OutputPath>
+ <DefineConstants>DEBUG;TRACE</DefineConstants>
+ <ErrorReport>prompt</ErrorReport>
+ <WarningLevel>4</WarningLevel>
+ </PropertyGroup>
+ <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
+ <DebugType>pdbonly</DebugType>
+ <Optimize>true</Optimize>
+ <OutputPath>bin\Release\</OutputPath>
+ <DefineConstants>TRACE</DefineConstants>
+ <ErrorReport>prompt</ErrorReport>
+ <WarningLevel>4</WarningLevel>
+ </PropertyGroup>
+ <ItemGroup>
+ <Reference Include="System" />
+ <Reference Include="System.configuration" />
+ <Reference Include="System.Data" />
+ <Reference Include="System.Xml" />
+ </ItemGroup>
+ <ItemGroup>
+ <Compile Include="CommandParameter.cs" />
+ <Compile Include="DatabaseColumn.cs" />
+ <Compile Include="DatabaseConnectionFactory.cs" />
+ <Compile Include="DatabaseGateway.cs" />
+ <Compile Include="IDatabaseConnectionFactory.cs" />
+ <Compile Include="IDatabaseGateway.cs" />
+ <Compile Include="InnerJoin.cs" />
+ <Compile Include="InsertQueryBuilder.cs" />
+ <Compile Include="Properties\AssemblyInfo.cs" />
+ <Compile Include="SelectQueryBuilder.cs" />
+ <Compile Include="Tables.cs" />
+ <Compile Include="UpdateQueryBuilder.cs" />
+ <Compile Include="WhereClause.cs" />
+ </ItemGroup>
+ <Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" />
+ <!-- To modify your build process, add your task inside one of the targets below and uncomment it.
+ Other similar extension points exist, see Microsoft.Common.targets.
+ <Target Name="BeforeBuild">
+ </Target>
+ <Target Name="AfterBuild">
+ </Target>
+ -->
+</Project>
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/CommandParameter.cs b/src/app/Cmpp298.Assignment3.DataAccess/CommandParameter.cs new file mode 100644 index 0000000..134a5e6 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/CommandParameter.cs @@ -0,0 +1,19 @@ +namespace Cmpp298.Assignment3.DataAccess {
+ public class CommandParameter {
+ private readonly string _columnName;
+ private readonly object _value;
+
+ public CommandParameter( string columnName, object value ) {
+ _columnName = columnName;
+ _value = value;
+ }
+
+ public string ColumnName {
+ get { return _columnName; }
+ }
+
+ public object Value {
+ get { return _value; }
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/DatabaseColumn.cs b/src/app/Cmpp298.Assignment3.DataAccess/DatabaseColumn.cs new file mode 100644 index 0000000..b8e4b13 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/DatabaseColumn.cs @@ -0,0 +1,23 @@ +namespace Cmpp298.Assignment3.DataAccess {
+ public class DatabaseColumn {
+ private readonly string _tableName;
+ private readonly string _columnName;
+
+ internal DatabaseColumn( string tableName, string columnName ) {
+ _tableName = tableName;
+ _columnName = columnName;
+ }
+
+ public string TableName {
+ get { return _tableName; }
+ }
+
+ public string ColumnName {
+ get { return _columnName; }
+ }
+
+ public override string ToString( ) {
+ return string.Format( "[{0}].[{1}]", _tableName, _columnName );
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/DatabaseConnectionFactory.cs b/src/app/Cmpp298.Assignment3.DataAccess/DatabaseConnectionFactory.cs new file mode 100644 index 0000000..57e8373 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/DatabaseConnectionFactory.cs @@ -0,0 +1,21 @@ +using System.Configuration;
+using System.Data;
+using System.Data.Common;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public class DatabaseConnectionFactory : IDatabaseConnectionFactory {
+ private ConnectionStringSettings _settings;
+
+ public DatabaseConnectionFactory( ) : this( ConfigurationManager.ConnectionStrings[ "PayablesConnection" ] ) {}
+
+ public DatabaseConnectionFactory( ConnectionStringSettings connectionStringSettings ) {
+ _settings = connectionStringSettings;
+ }
+
+ public IDbConnection Create( ) {
+ IDbConnection connection = DbProviderFactories.GetFactory( _settings.ProviderName ).CreateConnection( );
+ connection.ConnectionString = _settings.ConnectionString;
+ return connection;
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/DatabaseGateway.cs b/src/app/Cmpp298.Assignment3.DataAccess/DatabaseGateway.cs new file mode 100644 index 0000000..55b0221 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/DatabaseGateway.cs @@ -0,0 +1,67 @@ +using System;
+using System.Collections.Generic;
+using System.Data;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public class DatabaseGateway : IDatabaseGateway {
+ private IDatabaseConnectionFactory _connectionFactory;
+
+ public DatabaseGateway( ) : this( new DatabaseConnectionFactory( ) ) {}
+
+ public DatabaseGateway( IDatabaseConnectionFactory connectionFactory ) {
+ _connectionFactory = connectionFactory;
+ }
+
+ public int InsertRowUsing( InsertQueryBuilder builder ) {
+ return ExecuteBuilderQuery( builder.Parameters, builder.ToString( ) );
+ }
+
+ public void UpdateRowUsing( UpdateQueryBuilder builder ) {
+ ExecuteBuilderQuery( builder.Parameters, builder.ToString( ) );
+ }
+
+ public void Execute( string query ) {
+ using ( IDbConnection connection = _connectionFactory.Create( ) ) {
+ IDbCommand command = connection.CreateCommand( );
+ connection.Open( );
+ command.CommandText = query;
+ command.ExecuteNonQuery( );
+ }
+ }
+
+ public DataTable GetTableFrom( SelectQueryBuilder builder ) {
+ return GetTableFrom( builder.ToString( ) );
+ }
+
+ private DataTable GetTableFrom( string sqlQuery ) {
+ using ( IDbConnection connection = _connectionFactory.Create( ) ) {
+ IDbCommand command = connection.CreateCommand( );
+ command.CommandText = sqlQuery;
+ connection.Open( );
+ IDataReader reader = command.ExecuteReader( );
+ DataTable table = new DataTable( );
+ table.Load( reader );
+ return table;
+ }
+ }
+
+ private int ExecuteBuilderQuery( ICollection< CommandParameter > parameters, string commandText ) {
+ object scalar;
+ using ( IDbConnection connection = _connectionFactory.Create( ) ) {
+ IDbCommand command = connection.CreateCommand( );
+
+ foreach ( CommandParameter parameter in parameters ) {
+ IDataParameter commandParameter = command.CreateParameter( );
+ commandParameter.ParameterName = "@" + parameter.ColumnName;
+ commandParameter.Value = parameter.Value;
+ command.Parameters.Add( commandParameter );
+ }
+
+ command.CommandText = commandText;
+ connection.Open( );
+ scalar = command.ExecuteScalar( );
+ }
+ return DBNull.Value != scalar ? Convert.ToInt32( scalar ) : -1;
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/IDatabaseConnectionFactory.cs b/src/app/Cmpp298.Assignment3.DataAccess/IDatabaseConnectionFactory.cs new file mode 100644 index 0000000..5460f87 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/IDatabaseConnectionFactory.cs @@ -0,0 +1,7 @@ +using System.Data;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public interface IDatabaseConnectionFactory {
+ IDbConnection Create( );
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/IDatabaseGateway.cs b/src/app/Cmpp298.Assignment3.DataAccess/IDatabaseGateway.cs new file mode 100644 index 0000000..363dad4 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/IDatabaseGateway.cs @@ -0,0 +1,13 @@ +using System.Data;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public interface IDatabaseGateway {
+ DataTable GetTableFrom( SelectQueryBuilder builder );
+
+ int InsertRowUsing( InsertQueryBuilder builder );
+
+ void UpdateRowUsing( UpdateQueryBuilder builder );
+
+ void Execute( string query );
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/InnerJoin.cs b/src/app/Cmpp298.Assignment3.DataAccess/InnerJoin.cs new file mode 100644 index 0000000..bff9fcc --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/InnerJoin.cs @@ -0,0 +1,23 @@ +namespace Cmpp298.Assignment3.DataAccess {
+ public class InnerJoin {
+ public InnerJoin( string leftTableName, string leftColumnName, string rightTableName, string rightColumnName )
+ : this( new DatabaseColumn( leftTableName, leftColumnName ), new DatabaseColumn( rightTableName, rightColumnName ) ) {}
+
+ public InnerJoin( DatabaseColumn leftColumn, DatabaseColumn rightColumn ) {
+ _leftColumn = leftColumn;
+ _rightColumn = rightColumn;
+ }
+
+ public override string ToString( ) {
+ return
+ string.Format( "INNER JOIN [{0}] ON [{0}].[{1}] = [{2}].[{3}]",
+ _leftColumn.TableName,
+ _leftColumn.ColumnName,
+ _rightColumn.TableName,
+ _rightColumn.ColumnName );
+ }
+
+ private readonly DatabaseColumn _leftColumn;
+ private readonly DatabaseColumn _rightColumn;
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/InsertQueryBuilder.cs b/src/app/Cmpp298.Assignment3.DataAccess/InsertQueryBuilder.cs new file mode 100644 index 0000000..bbb3d9c --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/InsertQueryBuilder.cs @@ -0,0 +1,39 @@ +using System.Collections.Generic;
+using System.Text;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public class InsertQueryBuilder {
+ private readonly string _tableName;
+ private IList< CommandParameter > _parameters;
+
+ public InsertQueryBuilder( string tableName ) {
+ _tableName = tableName;
+ _parameters = new List< CommandParameter >( );
+ }
+
+ public IList< CommandParameter > Parameters {
+ get { return _parameters; }
+ }
+
+ public void Add( DatabaseColumn column, string value ) {
+ _parameters.Add( new CommandParameter( column.ColumnName, value ) );
+ }
+
+ public override string ToString( ) {
+ StringBuilder builder = new StringBuilder( );
+ builder.AppendFormat( "INSERT INTO {0} ({1}) VALUES ({2});SELECT @@IDENTITY;", _tableName,
+ GetParameterNames( string.Empty ),
+ GetParameterNames( "@" ) );
+ return builder.ToString( );
+ }
+
+ private string GetParameterNames( string prefix ) {
+ StringBuilder builder = new StringBuilder( );
+ foreach ( CommandParameter parameter in _parameters ) {
+ builder.AppendFormat( "{0}{1},", prefix, parameter.ColumnName );
+ }
+ builder.Remove( builder.Length - 1, 1 );
+ return builder.ToString( );
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/Properties/AssemblyInfo.cs b/src/app/Cmpp298.Assignment3.DataAccess/Properties/AssemblyInfo.cs new file mode 100644 index 0000000..4b3d3f1 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/Properties/AssemblyInfo.cs @@ -0,0 +1,35 @@ +using System.Reflection;
+using System.Runtime.CompilerServices;
+using System.Runtime.InteropServices;
+
+// General Information about an assembly is controlled through the following
+// set of attributes. Change these attribute values to modify the information
+// associated with an assembly.
+[assembly: AssemblyTitle("Cmpp298.Assignment3.DataAccess")]
+[assembly: AssemblyDescription("")]
+[assembly: AssemblyConfiguration("")]
+[assembly: AssemblyCompany("")]
+[assembly: AssemblyProduct("Cmpp298.Assignment3.DataAccess")]
+[assembly: AssemblyCopyright("Copyright © 2007")]
+[assembly: AssemblyTrademark("")]
+[assembly: AssemblyCulture("")]
+
+// Setting ComVisible to false makes the types in this assembly not visible
+// to COM components. If you need to access a type in this assembly from
+// COM, set the ComVisible attribute to true on that type.
+[assembly: ComVisible(false)]
+
+// The following GUID is for the ID of the typelib if this project is exposed to COM
+[assembly: Guid("6900dc95-1e04-424e-8c9d-f18dbb9c8251")]
+
+// Version information for an assembly consists of the following four values:
+//
+// Major Version
+// Minor Version
+// Build Number
+// Revision
+//
+// You can specify all the values or you can default the Revision and Build Numbers
+// by using the '*' as shown below:
+[assembly: AssemblyVersion("1.0.0.0")]
+[assembly: AssemblyFileVersion("1.0.0.0")]
diff --git a/src/app/Cmpp298.Assignment3.DataAccess/SelectQueryBuilder.cs b/src/app/Cmpp298.Assignment3.DataAccess/SelectQueryBuilder.cs new file mode 100644 index 0000000..a91e657 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/SelectQueryBuilder.cs @@ -0,0 +1,50 @@ +using System.Collections.Generic;
+using System.Text;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public class SelectQueryBuilder {
+ private readonly string _tableName;
+ private IList< DatabaseColumn > _selectColumns;
+ private IList< InnerJoin > _innerJoins;
+ private WhereClause _whereClause;
+
+ public SelectQueryBuilder( string tableName ) {
+ _tableName = tableName;
+ _innerJoins = new List< InnerJoin >( );
+ _selectColumns = new List< DatabaseColumn >( );
+ }
+
+ public void AddColumn( DatabaseColumn column ) {
+ _selectColumns.Add( column );
+ }
+
+ public void InnerJoin( DatabaseColumn leftColumn, DatabaseColumn rightColumn ) {
+ _innerJoins.Add( new InnerJoin( leftColumn, rightColumn ) );
+ }
+
+ public void Where( DatabaseColumn column, string value ) {
+ _whereClause = new WhereClause( column, value );
+ }
+
+ public override string ToString( ) {
+ return string.Format( "SELECT {0} FROM {1} {2} {3};", GetColumnNames( ), _tableName, GetInnerJoins( ), _whereClause );
+ }
+
+ private string GetInnerJoins( ) {
+ StringBuilder builder = new StringBuilder( );
+ foreach ( InnerJoin innerJoin in _innerJoins ) {
+ builder.Append( innerJoin.ToString( ) );
+ }
+ return builder.ToString( );
+ }
+
+ private string GetColumnNames( ) {
+ StringBuilder builder = new StringBuilder( );
+ foreach ( DatabaseColumn selectColumn in _selectColumns ) {
+ builder.AppendFormat( "{0},", selectColumn );
+ }
+ builder.Remove( builder.Length - 1, 1 );
+ return builder.ToString( );
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/Tables.cs b/src/app/Cmpp298.Assignment3.DataAccess/Tables.cs new file mode 100644 index 0000000..549d7c8 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/Tables.cs @@ -0,0 +1,30 @@ +namespace Cmpp298.Assignment3.DataAccess {
+ public sealed class Tables {
+ public sealed class Invoices {
+ public const string TableName = "Invoices";
+ public static readonly DatabaseColumn InvoiceID = new DatabaseColumn( TableName, "InvoiceID" );
+ public static readonly DatabaseColumn InvoiceNumber = new DatabaseColumn( TableName, "InvoiceNumber" );
+ public static readonly DatabaseColumn InvoiceDate = new DatabaseColumn( TableName, "InvoiceDate" );
+ public static readonly DatabaseColumn InvoiceTotal = new DatabaseColumn( TableName, "InvoiceTotal" );
+ public static readonly DatabaseColumn PaymentTotal = new DatabaseColumn( TableName, "PaymentTotal" );
+ public static readonly DatabaseColumn CreditTotal = new DatabaseColumn( TableName, "CreditTotal" );
+ public static readonly DatabaseColumn DueDate = new DatabaseColumn( TableName, "DueDate" );
+ public static readonly DatabaseColumn PaymentDate = new DatabaseColumn( TableName, "PaymentDate" );
+ public static readonly DatabaseColumn TermsID = new DatabaseColumn( TableName, "TermsID" );
+ public static readonly DatabaseColumn VendorID = new DatabaseColumn( TableName, "VendorID" );
+ }
+
+ public sealed class Vendors {
+ public const string TableName = "Vendors";
+ public static readonly DatabaseColumn VendorID = new DatabaseColumn( TableName, "VendorID" );
+ public static readonly DatabaseColumn Name = new DatabaseColumn( TableName, "Name" );
+ }
+
+ public sealed class Terms {
+ public const string TableName = "Terms";
+ public static readonly DatabaseColumn TermsID = new DatabaseColumn( TableName, "TermsID" );
+ public static readonly DatabaseColumn Description = new DatabaseColumn( TableName, "Description" );
+ public static readonly DatabaseColumn DueDays = new DatabaseColumn( TableName, "DueDays" );
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/UpdateQueryBuilder.cs b/src/app/Cmpp298.Assignment3.DataAccess/UpdateQueryBuilder.cs new file mode 100644 index 0000000..39654b1 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/UpdateQueryBuilder.cs @@ -0,0 +1,42 @@ +using System.Collections.Generic;
+using System.Text;
+
+namespace Cmpp298.Assignment3.DataAccess {
+ public class UpdateQueryBuilder {
+ private IList< CommandParameter > _parameters;
+ private WhereClause _where;
+
+ public UpdateQueryBuilder( DatabaseColumn whereColumn, string whereValue )
+ : this( new List< CommandParameter >( ), new WhereClause( whereColumn, whereValue ) ) {}
+
+ public UpdateQueryBuilder( IList< CommandParameter > parameters, WhereClause where ) {
+ _parameters = parameters;
+ _where = where;
+ }
+
+ public IList< CommandParameter > Parameters {
+ get { return _parameters; }
+ }
+
+ public void Add( DatabaseColumn column, string value ) {
+ _parameters.Add( new CommandParameter( column.ColumnName, value ) );
+ }
+
+ public override string ToString( ) {
+ StringBuilder builder = new StringBuilder( );
+ builder.AppendFormat( "UPDATE [{0}] SET {1};", _where.Column.TableName, GetParameterNames( ) );
+
+ return builder.ToString( );
+ }
+
+ private string GetParameterNames( ) {
+ StringBuilder builder = new StringBuilder( );
+ foreach ( CommandParameter parameter in _parameters ) {
+ builder.AppendFormat( "[{0}].[{1}] = @{1},", _where.Column.TableName, parameter.ColumnName );
+ }
+ builder.Remove( builder.Length - 1, 1 );
+ builder.Append( _where );
+ return builder.ToString( );
+ }
+ }
+}
\ No newline at end of file diff --git a/src/app/Cmpp298.Assignment3.DataAccess/WhereClause.cs b/src/app/Cmpp298.Assignment3.DataAccess/WhereClause.cs new file mode 100644 index 0000000..3912f09 --- /dev/null +++ b/src/app/Cmpp298.Assignment3.DataAccess/WhereClause.cs @@ -0,0 +1,23 @@ +namespace Cmpp298.Assignment3.DataAccess {
+ public class WhereClause {
+ private readonly DatabaseColumn _column;
+ private readonly string _value;
+
+ public WhereClause( DatabaseColumn column, string value ) {
+ _column = column;
+ _value = value;
+ }
+
+ public DatabaseColumn Column {
+ get { return _column; }
+ }
+
+ public string Value {
+ get { return _value; }
+ }
+
+ public override string ToString( ) {
+ return string.Format( " WHERE [{0}].[{1}] = {2};", _column.TableName, _column.ColumnName, _value );
+ }
+ }
+}
\ No newline at end of file |