diff options
Diffstat (limited to 'slips/src/app/Marina/DataAccess/Builders')
14 files changed, 347 insertions, 0 deletions
diff --git a/slips/src/app/Marina/DataAccess/Builders/DatabaseDelete.cs b/slips/src/app/Marina/DataAccess/Builders/DatabaseDelete.cs new file mode 100644 index 0000000..801aba0 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/DatabaseDelete.cs @@ -0,0 +1,31 @@ +using System.Collections.Generic;
+
+namespace Marina.DataAccess.Builders {
+ public static class DatabaseDelete {
+ public static IQuery Where< T >( DatabaseColumn column, T equalsValue ) {
+ return new DeleteQueryBuilder< T >( column, equalsValue ).Build( );
+ }
+
+ private class DeleteQueryBuilder< T > : IDeleteQueryBuilder {
+ public DeleteQueryBuilder( DatabaseColumn column, T value ) {
+ _column = column;
+ _value = value;
+ }
+
+ public override string ToString() {
+ return string.Format( "DELETE FROM [{0}] WHERE [{0}].[{1}] = @{1};", _column.TableName, _column.ColumnName );
+ }
+
+ public IEnumerable< DatabaseCommandParameter > Parameters() {
+ yield return new DatabaseCommandParameter( _column.ColumnName, _value );
+ }
+
+ public IQuery Build() {
+ return new Query( this );
+ }
+
+ private readonly DatabaseColumn _column;
+ private readonly T _value;
+ }
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/DatabaseInsert.cs b/slips/src/app/Marina/DataAccess/Builders/DatabaseInsert.cs new file mode 100644 index 0000000..57c0c1d --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/DatabaseInsert.cs @@ -0,0 +1,50 @@ +using System.Collections.Generic;
+using System.Text;
+
+namespace Marina.DataAccess.Builders {
+ public static class DatabaseInsert {
+ public static IInsertQueryBuilder Into( string tableName ) {
+ return new InsertQueryBuilder( tableName );
+ }
+
+ private class InsertQueryBuilder : IInsertQueryBuilder {
+ public InsertQueryBuilder( string tableName ) {
+ _tableName = tableName;
+ _parameters = new List< DatabaseCommandParameter >( );
+ }
+
+ public IEnumerable< DatabaseCommandParameter > Parameters() {
+ return _parameters;
+ }
+
+ public IQuery Build() {
+ return new Query( this );
+ }
+
+ public IInsertQueryBuilder AddValue< T >( DatabaseColumn column, T value ) {
+ _parameters.Add( new DatabaseCommandParameter( column.ColumnName, value ) );
+ return this;
+ }
+
+ 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 ( DatabaseCommandParameter parameter in _parameters ) {
+ builder.AppendFormat( "{0}{1},", prefix, parameter.ColumnName );
+ }
+ builder.Remove( builder.Length - 1, 1 );
+ return builder.ToString( );
+ }
+
+ private readonly string _tableName;
+ private readonly IList< DatabaseCommandParameter > _parameters;
+ }
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/DatabaseSelect.cs b/slips/src/app/Marina/DataAccess/Builders/DatabaseSelect.cs new file mode 100644 index 0000000..6e61fff --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/DatabaseSelect.cs @@ -0,0 +1,101 @@ +using System.Collections.Generic;
+using System.Text;
+
+namespace Marina.DataAccess.Builders {
+ public static class DatabaseSelect {
+ public static ISelectQueryBuilder From( string tableName ) {
+ return new SelectQueryBuilder( tableName );
+ }
+
+ private class SelectQueryBuilder : ISelectQueryBuilder {
+ public SelectQueryBuilder( string tableName ) {
+ _tableName = string.Format( "[{0}]", tableName );
+ _innerJoins = new List< InnerJoin >( );
+ _selectColumns = new List< DatabaseColumn >( );
+ }
+
+ public ISelectQueryBuilder AddColumn( DatabaseColumn column ) {
+ _selectColumns.Add( column );
+ return this;
+ }
+
+ public ISelectQueryBuilder InnerJoinOn( DatabaseColumn leftColumn, DatabaseColumn rightColumn ) {
+ _innerJoins.Add( new InnerJoin( leftColumn, rightColumn ) );
+ return this;
+ }
+
+ public ISelectQueryBuilder Where( DatabaseColumn column, string value ) {
+ _whereClause = new WhereClause( column, value );
+ return this;
+ }
+
+ public ISelectQueryBuilder Where< T >( DatabaseColumn column, T value ) {
+ return Where( column, value.ToString( ) );
+ }
+
+ public override string ToString() {
+ return
+ string.Format( "SELECT {0} FROM {1} {2} {3};", GetColumnNames( ), _tableName, GetInnerJoins( ), _whereClause );
+ }
+
+ public IEnumerable< DatabaseCommandParameter > Parameters() {
+ foreach ( DatabaseColumn databaseColumn in _selectColumns ) {
+ yield return new DatabaseCommandParameter( databaseColumn.ColumnName, string.Empty );
+ }
+ }
+
+ public IQuery Build() {
+ return new Query( this );
+ }
+
+ 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( );
+ }
+
+ private readonly string _tableName;
+ private readonly IList< DatabaseColumn > _selectColumns;
+ private readonly IList< InnerJoin > _innerJoins;
+ private WhereClause _whereClause;
+
+ private class InnerJoin : IJoin {
+ public InnerJoin( DatabaseColumn leftColumn, DatabaseColumn rightColumn ) {
+ _leftColumn = leftColumn;
+ _rightColumn = rightColumn;
+ }
+
+ public DatabaseColumn Left() {
+ return _leftColumn;
+ }
+
+ public DatabaseColumn Right() {
+ return _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/slips/src/app/Marina/DataAccess/Builders/DatabaseUpdate.cs b/slips/src/app/Marina/DataAccess/Builders/DatabaseUpdate.cs new file mode 100644 index 0000000..8b90edc --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/DatabaseUpdate.cs @@ -0,0 +1,52 @@ +using System.Collections.Generic;
+using System.Text;
+
+namespace Marina.DataAccess.Builders {
+ public static class DatabaseUpdate {
+ public static IUpdateQueryBuilder Where< T >( DatabaseColumn whereColumn, T whereValue ) {
+ return new UpdateQueryBuilder( whereColumn, whereValue.ToString( ) );
+ }
+
+ private class UpdateQueryBuilder : IUpdateQueryBuilder {
+ public UpdateQueryBuilder( DatabaseColumn whereColumn, string whereValue )
+ : this( new List< DatabaseCommandParameter >( ), new WhereClause( whereColumn, whereValue ) ) {}
+
+ public UpdateQueryBuilder( IList< DatabaseCommandParameter > parameters, WhereClause where ) {
+ _parameters = parameters;
+ _where = where;
+ }
+
+ public IEnumerable< DatabaseCommandParameter > Parameters() {
+ return _parameters;
+ }
+
+ public IQuery Build() {
+ return new Query( this );
+ }
+
+ public IUpdateQueryBuilder Add( DatabaseColumn column, string value ) {
+ _parameters.Add( new DatabaseCommandParameter( column.ColumnName, value ) );
+ return this;
+ }
+
+ 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 ( DatabaseCommandParameter 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( );
+ }
+
+ private readonly IList< DatabaseCommandParameter > _parameters;
+ private readonly WhereClause _where;
+ }
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/IDeleteQueryBuilder.cs b/slips/src/app/Marina/DataAccess/Builders/IDeleteQueryBuilder.cs new file mode 100644 index 0000000..2b7290a --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/IDeleteQueryBuilder.cs @@ -0,0 +1,3 @@ +namespace Marina.DataAccess.Builders {
+ public interface IDeleteQueryBuilder : IQueryBuilder {}
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/IInsertQueryBuilder.cs b/slips/src/app/Marina/DataAccess/Builders/IInsertQueryBuilder.cs new file mode 100644 index 0000000..946c914 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/IInsertQueryBuilder.cs @@ -0,0 +1,5 @@ +namespace Marina.DataAccess.Builders {
+ public interface IInsertQueryBuilder : IQueryBuilder {
+ IInsertQueryBuilder AddValue< T >( DatabaseColumn column, T value );
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/IJoin.cs b/slips/src/app/Marina/DataAccess/Builders/IJoin.cs new file mode 100644 index 0000000..c4ae46f --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/IJoin.cs @@ -0,0 +1,6 @@ +namespace Marina.DataAccess.Builders {
+ internal interface IJoin {
+ DatabaseColumn Left();
+ DatabaseColumn Right();
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/IQuery.cs b/slips/src/app/Marina/DataAccess/Builders/IQuery.cs new file mode 100644 index 0000000..a45527f --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/IQuery.cs @@ -0,0 +1,7 @@ +using System.Data;
+
+namespace Marina.DataAccess.Builders {
+ public interface IQuery {
+ void Prepare( IDbCommand command );
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/IQueryBuilder.cs b/slips/src/app/Marina/DataAccess/Builders/IQueryBuilder.cs new file mode 100644 index 0000000..175c2d5 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/IQueryBuilder.cs @@ -0,0 +1,9 @@ +using System.Collections.Generic;
+
+namespace Marina.DataAccess.Builders {
+ public interface IQueryBuilder {
+ IEnumerable< DatabaseCommandParameter > Parameters();
+
+ IQuery Build();
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/ISelectQueryBuilder.cs b/slips/src/app/Marina/DataAccess/Builders/ISelectQueryBuilder.cs new file mode 100644 index 0000000..058a7a1 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/ISelectQueryBuilder.cs @@ -0,0 +1,11 @@ +namespace Marina.DataAccess.Builders {
+ public interface ISelectQueryBuilder : IQueryBuilder {
+ ISelectQueryBuilder AddColumn( DatabaseColumn column );
+
+ ISelectQueryBuilder InnerJoinOn( DatabaseColumn leftColumn, DatabaseColumn rightColumn );
+
+ ISelectQueryBuilder Where( DatabaseColumn column, string value );
+
+ ISelectQueryBuilder Where< T >( DatabaseColumn column, T value );
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/IUpdateQueryBuilder.cs b/slips/src/app/Marina/DataAccess/Builders/IUpdateQueryBuilder.cs new file mode 100644 index 0000000..c599243 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/IUpdateQueryBuilder.cs @@ -0,0 +1,5 @@ +namespace Marina.DataAccess.Builders {
+ public interface IUpdateQueryBuilder : IQueryBuilder {
+ IUpdateQueryBuilder Add( DatabaseColumn column, string value );
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/Query.cs b/slips/src/app/Marina/DataAccess/Builders/Query.cs new file mode 100644 index 0000000..0b95bf1 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/Query.cs @@ -0,0 +1,22 @@ +using System.Data;
+
+namespace Marina.DataAccess.Builders {
+ public class Query : IQuery {
+ public Query( IQueryBuilder builder ) {
+ _builder = builder;
+ }
+
+ public void Prepare( IDbCommand command ) {
+ command.CommandText = _builder.ToString( );
+ command.CommandType = CommandType.Text;
+ foreach ( DatabaseCommandParameter parameter in _builder.Parameters( ) ) {
+ IDataParameter commandParameter = command.CreateParameter( );
+ commandParameter.ParameterName = "@" + parameter.ColumnName;
+ commandParameter.Value = parameter.Value;
+ command.Parameters.Add( commandParameter );
+ }
+ }
+
+ private readonly IQueryBuilder _builder;
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/SqlQuery.cs b/slips/src/app/Marina/DataAccess/Builders/SqlQuery.cs new file mode 100644 index 0000000..c6b2b47 --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/SqlQuery.cs @@ -0,0 +1,18 @@ +using System.Data;
+
+namespace Marina.DataAccess.Builders {
+ public class SqlQuery : IQuery {
+ public SqlQuery( string sqlQuery ) {
+ _sqlQuery = sqlQuery;
+ }
+
+ public void Prepare( IDbCommand command ) {
+ if ( command != null ) {
+ command.CommandText = _sqlQuery;
+ command.CommandType = CommandType.Text;
+ }
+ }
+
+ private readonly string _sqlQuery;
+ }
+}
\ No newline at end of file diff --git a/slips/src/app/Marina/DataAccess/Builders/WhereClause.cs b/slips/src/app/Marina/DataAccess/Builders/WhereClause.cs new file mode 100644 index 0000000..5768fde --- /dev/null +++ b/slips/src/app/Marina/DataAccess/Builders/WhereClause.cs @@ -0,0 +1,27 @@ +namespace Marina.DataAccess.Builders {
+ public class WhereClause {
+ public WhereClause( DatabaseColumn column, string value ) {
+ _column = column;
+ _value = value;
+ }
+
+ public DatabaseColumn Column() {
+ return _column;
+ }
+
+ public string Value() {
+ return _value;
+ }
+
+ public string ToSql() {
+ return ToString( );
+ }
+
+ public override string ToString() {
+ return string.Format( " WHERE [{0}].[{1}] = {2};", _column.TableName, _column.ColumnName, _value );
+ }
+
+ private readonly DatabaseColumn _column;
+ private readonly string _value;
+ }
+}
\ No newline at end of file |