Migrator Remove SMO code for now (time constraint) because it was failing to execute code
Changeset a840be35eb11…
Parent 17de8d3def74…
by
Changes to 5 files · Browse files at a840be35eb11 Showing diff from parent 17de8d3def74 Diff from another changeset...
|
@@ -46,15 +46,6 @@ <DocumentationFile>bin\Migrator\Release\Migrator.XML</DocumentationFile>
</PropertyGroup>
<ItemGroup>
- <Reference Include="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
- <HintPath>..\..\lib\Microsoft.SqlServer.ConnectionInfo.dll</HintPath>
- </Reference>
- <Reference Include="Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
- <HintPath>..\..\lib\Microsoft.SqlServer.Management.Sdk.Sfc.dll</HintPath>
- </Reference>
- <Reference Include="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
- <HintPath>..\..\lib\Microsoft.SqlServer.Smo.dll</HintPath>
- </Reference>
<Reference Include="MySql.Data, Version=5.0.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d, processorArchitecture=MSIL">
<HintPath>..\..\lib\MySql.Data.dll</HintPath>
<Private>True</Private>
|
||
|
|
|
@@ -31,129 +31,130 @@
namespace Migrator.Providers
{
- /// <summary>
- /// Base class for every transformation providers.
- /// A 'tranformation' is an operation that modifies the database.
- /// </summary>
- public abstract class TransformationProvider : IDisposable
- {
- protected string SCHEMA_INFO_TABLE = "SchemaInfo";
- protected const string VERSION_COLUMN = "Version";
- protected const string ASSEMBLY_ID_COLUMN = "AssemblyId";
+ /// <summary>
+ /// Base class for every transformation providers.
+ /// A 'tranformation' is an operation that modifies the database.
+ /// </summary>
+ public abstract class TransformationProvider : IDisposable
+ {
+ protected string SCHEMA_INFO_TABLE = "SchemaInfo";
+ protected const string VERSION_COLUMN = "Version";
+ protected const string ASSEMBLY_ID_COLUMN = "AssemblyId";
- private ILogger _logger = new NullLogger();
- private IDbConnection _connection;
- private IDbTransaction _transaction;
- private string _assemblyId = null;
- private bool _cacheConvertedValues = true;
- private int? _timeout = null;
- public delegate U ConvertColumnDelegate<T, U>(T currentValue, IDataReader row);
- protected AbstractMapper _mapper;
- #region Properties
- public String AssemblyId
- {
- get { return this._assemblyId; }
- internal set { this._assemblyId = value; }
- }
+ private ILogger _logger = new NullLogger();
+ private IDbConnection _connection;
+ private IDbTransaction _transaction;
+ private string _assemblyId = null;
+ private bool _cacheConvertedValues = true;
+ private int? _timeout = null;
+ public delegate U ConvertColumnDelegate<T, U>(T currentValue, IDataReader row);
+ protected AbstractMapper _mapper;
+ #region Properties
+ public String AssemblyId
+ {
+ get { return this._assemblyId; }
+ internal set { this._assemblyId = value; }
+ }
- /// <summary>
- /// States weither to cache a conversion value or to invoke the conversion function every time. Defaults to cached.
- /// </summary>
- public Boolean CacheConvertedValues
- {
- get { return _cacheConvertedValues; }
- set { _cacheConvertedValues = value; }
- }
+ /// <summary>
+ /// States weither to cache a conversion value or to invoke the conversion function every time. Defaults to cached.
+ /// </summary>
+ public Boolean CacheConvertedValues
+ {
+ get { return _cacheConvertedValues; }
+ set { _cacheConvertedValues = value; }
+ }
- public IDbConnection Connection
- {
- get { return _connection; }
- set { _connection = value; }
- }
- /// <summary>
- /// Returns the event logger
- /// </summary>
- virtual public ILogger Logger
- {
- get { return _logger; }
- set { _logger = value; }
- }
+ public IDbConnection Connection
+ {
+ get { return _connection; }
+ set { _connection = value; }
+ }
+ /// <summary>
+ /// Returns the event logger
+ /// </summary>
+ virtual public ILogger Logger
+ {
+ get { return _logger; }
+ set { _logger = value; }
+ }
- public string ConnectionString
- {
- get { return Connection != null ? (string)Connection.ConnectionString.Clone() : null; }
- }
+ public string ConnectionString
+ {
+ get { return Connection != null ? (string)Connection.ConnectionString.Clone() : null; }
+ }
- public int? Timeout
- {
- get { return this._timeout == null || this._timeout == 0 ? null : this._timeout; }
- set { this._timeout = value; }
- }
- #endregion
+ public int? Timeout
+ {
+ get { return this._timeout == null || this._timeout == 0 ? null : this._timeout; }
+ set { this._timeout = value; }
+ }
- #region Tables
- /// <summary>
- /// Add a new table
- /// </summary>
- /// <param name="tableName">Table name</param>
- /// <param name="columns">Columns</param>
- /// <example>
- /// Adds the Test table with two columns:
- /// <code>
- /// Database.AddTable("Test",
- /// new Column("Id", typeof(int), ColumnProperties.PrimaryKey),
- /// new Column("Title", typeof(string), 100)
- /// );
- /// </code>
- /// </example>
- public virtual void AddTable(string tableName, params Column[] columns)
- {
- AddTable(new Table(tableName, columns));
- }
+ #endregion
- /// <summary>
- /// Adds a new table.
- /// </summary>
- /// <param name="table">The object representation of a table definition.</param>
- public virtual void AddTable(Table table)
- {
- if (TableExists(table.Name))
- {
- Logger.Warn("The table {0} already exists", table.Name);
- return;
- }
+ #region Tables
+ /// <summary>
+ /// Add a new table
+ /// </summary>
+ /// <param name="tableName">Table name</param>
+ /// <param name="columns">Columns</param>
+ /// <example>
+ /// Adds the Test table with two columns:
+ /// <code>
+ /// Database.AddTable("Test",
+ /// new Column("Id", typeof(int), ColumnProperties.PrimaryKey),
+ /// new Column("Title", typeof(string), 100)
+ /// );
+ /// </code>
+ /// </example>
+ public virtual void AddTable(string tableName, params Column[] columns)
+ {
+ AddTable(new Table(tableName, columns));
+ }
- string columnSQL = String.Join(", ", table.Columns.Select(column => GetTextFor(column)));
+ /// <summary>
+ /// Adds a new table.
+ /// </summary>
+ /// <param name="table">The object representation of a table definition.</param>
+ public virtual void AddTable(Table table)
+ {
+ if (TableExists(table.Name))
+ {
+ Logger.Warn("The table {0} already exists", table.Name);
+ return;
+ }
- string fkSQL = String.Join(", ", table.ForeignKeys.Select(fk => GetTextFor(fk)));
+ string columnSQL = String.Join(", ", table.Columns.Select(column => GetTextFor(column)));
- ExecuteNonQuery(string.Format("CREATE TABLE {0} ({1}{2})", table.Name, columnSQL, fkSQL.Trim().Equals(String.Empty) ? "" : ", " + fkSQL));
+ string fkSQL = String.Join(", ", table.ForeignKeys.Select(fk => GetTextFor(fk)));
- }
+ ExecuteNonQuery(string.Format("CREATE TABLE {0} ({1}{2})", table.Name, columnSQL, fkSQL.Trim().Equals(String.Empty) ? "" : ", " + fkSQL));
- /// <summary>
- /// Remove a table from the database.
- /// </summary>
- /// <param name="tableName">Table name</param>
- public abstract void RemoveTable(string tableName);
+ }
- /// <summary>
- /// Rename a Table
- /// </summary>
- /// <param name="tableName">Name of the table to be renames</param>
- /// <param name="newName">New name of the table</param>
- public abstract void RenameTable(string tableName, string newName);
+ /// <summary>
+ /// Remove a table from the database.
+ /// </summary>
+ /// <param name="tableName">Table name</param>
+ public abstract void RemoveTable(string tableName);
- public abstract Table GetTable(string tableName);
+ /// <summary>
+ /// Rename a Table
+ /// </summary>
+ /// <param name="tableName">Name of the table to be renames</param>
+ /// <param name="newName">New name of the table</param>
+ public abstract void RenameTable(string tableName, string newName);
- public abstract string[] GetTables();
+ public abstract Table GetTable(string tableName);
- /// <summary>
- /// Determines if a table exists.
- /// </summary>
- /// <param name="tableName">Table name</param>
- /// <returns><c>true</c> if the table exists</returns>
- public abstract bool TableExists(string tableName);
+ public abstract string[] GetTables();
+
+ /// <summary>
+ /// Determines if a table exists.
+ /// </summary>
+ /// <param name="tableName">Table name</param>
+ /// <returns><c>true</c> if the table exists</returns>
+ public abstract bool TableExists(string tableName);
/// <summary>
/// Sets the name of the schema info table
@@ -164,812 +165,797 @@ SCHEMA_INFO_TABLE = schemaInfoTableName;
Logger.Log("Using schema info table of [{0}]", schemaInfoTableName);
}
- #endregion
+ #endregion
- #region Columns
- /// <summary>
- /// Add a new column to an existing table.
- /// </summary>
- /// <exception cref="NotSupportedException">
- /// Thrown if the <c>type</c> is not supported by the provider
- /// </exception>
- /// <param name="tableName">Table to which to add the column</param>
- /// <param name="column">Column name</param>
- /// <param name="type">Date type of the column</param>
- /// <param name="size">Max length of the column</param>
- /// <param name="property">Properties of the column, see <see cref="ColumnProperties">ColumnProperties</see>,</param>
- /// <param name="defaultValue">Default value</param>
- public abstract void AddColumn(string tableName, string column, Type type, int size, ColumnProperties property, object defaultValue);
+ #region Columns
+ /// <summary>
+ /// Add a new column to an existing table.
+ /// </summary>
+ /// <exception cref="NotSupportedException">
+ /// Thrown if the <c>type</c> is not supported by the provider
+ /// </exception>
+ /// <param name="tableName">Table to which to add the column</param>
+ /// <param name="column">Column name</param>
+ /// <param name="type">Date type of the column</param>
+ /// <param name="size">Max length of the column</param>
+ /// <param name="property">Properties of the column, see <see cref="ColumnProperties">ColumnProperties</see>,</param>
+ /// <param name="defaultValue">Default value</param>
+ public abstract void AddColumn(string tableName, string column, Type type, int size, ColumnProperties property, object defaultValue);
- /// <summary>
- /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
- /// AddColumn(string, string, Type, int, ColumnProperties, object)
- /// </see>
- /// </summary>
- public virtual void AddColumn(string tableName, string column, Type type)
- {
- AddColumn(tableName, column, type, 0, ColumnProperties.Null, null);
- }
+ /// <summary>
+ /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
+ /// AddColumn(string, string, Type, int, ColumnProperties, object)
+ /// </see>
+ /// </summary>
+ public virtual void AddColumn(string tableName, string column, Type type)
+ {
+ AddColumn(tableName, column, type, 0, ColumnProperties.Null, null);
+ }
- /// <summary>
- /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
- /// AddColumn(string, string, Type, int, ColumnProperties, object)
- /// </see>
- /// </summary>
- public virtual void AddColumn(string tableName, string column, Type type, int size)
- {
- AddColumn(tableName, column, type, size, ColumnProperties.Null, null);
- }
+ /// <summary>
+ /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
+ /// AddColumn(string, string, Type, int, ColumnProperties, object)
+ /// </see>
+ /// </summary>
+ public virtual void AddColumn(string tableName, string column, Type type, int size)
+ {
+ AddColumn(tableName, column, type, size, ColumnProperties.Null, null);
+ }
- /// <summary>
- /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
- /// AddColumn(string, string, Type, int, ColumnProperties)
- /// </see>
- /// </summary>
- public virtual void AddColumn(string tableName, string column, Type type, ColumnProperties property)
- {
- AddColumn(tableName, column, type, 0, property, null);
- }
+ /// <summary>
+ /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
+ /// AddColumn(string, string, Type, int, ColumnProperties)
+ /// </see>
+ /// </summary>
+ public virtual void AddColumn(string tableName, string column, Type type, ColumnProperties property)
+ {
+ AddColumn(tableName, column, type, 0, property, null);
+ }
- /// <summary>
- /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
- /// AddColumn(string, string, Type, int, ColumnProperties)
- /// </see>
- /// </summary>
- public virtual void AddColumn(string tableName, string column, Type type, int size, ColumnProperties property)
- {
- AddColumn(tableName, column, type, size, property, null);
- }
+ /// <summary>
+ /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
+ /// AddColumn(string, string, Type, int, ColumnProperties)
+ /// </see>
+ /// </summary>
+ public virtual void AddColumn(string tableName, string column, Type type, int size, ColumnProperties property)
+ {
+ AddColumn(tableName, column, type, size, property, null);
+ }
- /// <summary>
- /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
- /// AddColumn(string, string, Type, int, ColumnProperties, object)
- /// </see>
- /// </summary>
- public virtual void AddColumn(string tableName, string column, Type type, ColumnProperties property, object defaultValue)
- {
- AddColumn(tableName, column, type, 0, property, defaultValue);
- }
+ /// <summary>
+ /// <see cref="TransformationProvider.AddColumn(string, string, Type, int, ColumnProperties, object)">
+ /// AddColumn(string, string, Type, int, ColumnProperties, object)
+ /// </see>
+ /// </summary>
+ public virtual void AddColumn(string tableName, string column, Type type, ColumnProperties property, object defaultValue)
+ {
+ AddColumn(tableName, column, type, 0, property, defaultValue);
+ }
- /// <summary>
- /// Implements 'Alter Table' functionality common in most SQL variants.
- /// </summary>
- /// <param name="tableName">Table Name</param>
- /// <param name="column">Column Name to alter</param>
- /// <param name="type">new type of the Column</param>
- /// <param name="size">new size of the Column</param>
- public abstract void AlterColumn(string tableName, string column, Type type, int size);
+ /// <summary>
+ /// Implements 'Alter Table' functionality common in most SQL variants.
+ /// </summary>
+ /// <param name="tableName">Table Name</param>
+ /// <param name="column">Column Name to alter</param>
+ /// <param name="type">new type of the Column</param>
+ /// <param name="size">new size of the Column</param>
+ public abstract void AlterColumn(string tableName, string column, Type type, int size);
- /// <summary>
- /// Implements 'Alter Table' functionality common in most SQL variants.
- /// </summary>
- /// <remarks>Transformation providers that dont support all possible functionality are free to work around the problem OR throw an exception.
- /// If an exception is thrown please include the specific functionality that isnt implemented in the exception message. E.g. SqlLite cant alter columns to be primary keys.</remarks>
- /// <param name="tableName">Table Name</param>
- /// <param name="column">Column Name to alter</param>
- /// <param name="dataType">new typw of the Column</param>
- /// <param name="size">new size of the Column</param>
- /// <param name="props">New Column Properties</param>
- /// <param name="defaultValue">new Default Value</param>
- public abstract void AlterColumn(string tableName, string column, Type dataType, int size, ColumnProperties props, object defaultValue);
+ /// <summary>
+ /// Implements 'Alter Table' functionality common in most SQL variants.
+ /// </summary>
+ /// <remarks>Transformation providers that dont support all possible functionality are free to work around the problem OR throw an exception.
+ /// If an exception is thrown please include the specific functionality that isnt implemented in the exception message. E.g. SqlLite cant alter columns to be primary keys.</remarks>
+ /// <param name="tableName">Table Name</param>
+ /// <param name="column">Column Name to alter</param>
+ /// <param name="dataType">new typw of the Column</param>
+ /// <param name="size">new size of the Column</param>
+ /// <param name="props">New Column Properties</param>
+ /// <param name="defaultValue">new Default Value</param>
+ public abstract void AlterColumn(string tableName, string column, Type dataType, int size, ColumnProperties props, object defaultValue);
- /// <summary>
- /// Implements 'Alter Table' functionality common in most SQL variants.
- /// </summary>
- /// <param name="tableName">Table Name</param>
- /// <param name="column">Column Name to alter</param>
- /// <param name="dataType">new type of the Column</param>
- /// <param name="props">New Column Properties</param>
- /// <param name="defaultValue">new Default Value</param>
- public virtual void AlterColumn(string tableName, string column, Type dataType, ColumnProperties props, object defaultValue)
- {
- AlterColumn(tableName, column, dataType, 0, props, defaultValue);
- }
+ /// <summary>
+ /// Implements 'Alter Table' functionality common in most SQL variants.
+ /// </summary>
+ /// <param name="tableName">Table Name</param>
+ /// <param name="column">Column Name to alter</param>
+ /// <param name="dataType">new type of the Column</param>
+ /// <param name="props">New Column Properties</param>
+ /// <param name="defaultValue">new Default Value</param>
+ public virtual void AlterColumn(string tableName, string column, Type dataType, ColumnProperties props, object defaultValue)
+ {
+ AlterColumn(tableName, column, dataType, 0, props, defaultValue);
+ }
- /// <summary>
- /// Determines of a column exists.
- /// </summary>
- /// <param name="tableName">Table name</param>
- /// <param name="column">Column name</param>
- /// <returns><c>true</c> if the column exists</returns>
- public abstract bool ColumnExists(string tableName, string column);
+ /// <summary>
+ /// Determines of a column exists.
+ /// </summary>
+ /// <param name="tableName">Table name</param>
+ /// <param name="column">Column name</param>
+ /// <returns><c>true</c> if the column exists</returns>
+ public abstract bool ColumnExists(string tableName, string column);
- public virtual Column[] GetColumns(string tableName)
- {
- return GetTable(tableName).Columns.ToArray();
- }
+ public virtual Column[] GetColumns(string tableName)
+ {
+ return GetTable(tableName).Columns.ToArray();
+ }
- /// <summary>
- /// Removes a column from a table
- /// </summary>
- /// <param name="tableName">table containing the column</param>
- /// <param name="column">column name</param>
- public abstract void RemoveColumn(string tableName, string column);
+ /// <summary>
+ /// Removes a column from a table
+ /// </summary>
+ /// <param name="tableName">table containing the column</param>
+ /// <param name="column">column name</param>
+ public abstract void RemoveColumn(string tableName, string column);
- /// <summary>
- /// Rename a Column
- /// </summary>
- /// <param name="tableName">Table containing the Column to be Renames</param>
- /// <param name="column">Column to be renamed</param>
- /// <param name="newName">New name of the column</param>
- public abstract void RenameColumn(string tableName, string column, string newName);
+ /// <summary>
+ /// Rename a Column
+ /// </summary>
+ /// <param name="tableName">Table containing the Column to be Renames</param>
+ /// <param name="column">Column to be renamed</param>
+ /// <param name="newName">New name of the column</param>
+ public abstract void RenameColumn(string tableName, string column, string newName);
- #region ConvertColumn
- public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
- {
- ConvertColumnValidation(tableName, column, tempColumn);
- this.AddColumn(tableName, tempColumn, typeof(U));
- return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
- }
+ #region ConvertColumn
+ public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
+ {
+ ConvertColumnValidation(tableName, column, tempColumn);
+ this.AddColumn(tableName, tempColumn, typeof(U));
+ return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
+ }
- public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size)
- {
- ConvertColumnValidation(tableName, column, tempColumn);
- this.AddColumn(tableName, tempColumn, typeof(U), size);
- return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
- }
+ public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size)
+ {
+ ConvertColumnValidation(tableName, column, tempColumn);
+ this.AddColumn(tableName, tempColumn, typeof(U), size);
+ return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
+ }
- public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, ColumnProperties property)
- {
- ConvertColumnValidation(tableName, column, tempColumn);
- this.AddColumn(tableName, tempColumn, typeof(U), size, property);
- return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
- }
+ public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, ColumnProperties property)
+ {
+ ConvertColumnValidation(tableName, column, tempColumn);
+ this.AddColumn(tableName, tempColumn, typeof(U), size, property);
+ return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
+ }
- public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, ColumnProperties property)
- {
- ConvertColumnValidation(tableName, column, tempColumn);
- this.AddColumn(tableName, tempColumn, typeof(U), property);
- return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
- }
+ public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, ColumnProperties property)
+ {
+ ConvertColumnValidation(tableName, column, tempColumn);
+ this.AddColumn(tableName, tempColumn, typeof(U), property);
+ return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
+ }
- public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, object defaultValue, ColumnProperties property)
- {
- ConvertColumnValidation(tableName, column, tempColumn);
- this.AddColumn(tableName, tempColumn, typeof(U), size, property, defaultValue);
- return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
- }
+ public virtual bool ConvertColumn<T, U>(string tableName, string column, string tempColumn, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc, int size, object defaultValue, ColumnProperties property)
+ {
+ ConvertColumnValidation(tableName, column, tempColumn);
+ this.AddColumn(tableName, tempColumn, typeof(U), size, property, defaultValue);
+ return this.DoConvertColumn<T, U>(tableName, column, tempColumn, convertFunc);
+ }
- private void ConvertColumnValidation(String tableName, String column, String tempColumn)
- {
- if (!TableExists(tableName) || !ColumnExists(tableName, column) || ColumnExists(tableName, tempColumn))
- throw new ArgumentException();
- }
+ private void ConvertColumnValidation(String tableName, String column, String tempColumn)
+ {
+ if (!TableExists(tableName) || !ColumnExists(tableName, column) || ColumnExists(tableName, tempColumn))
+ throw new ArgumentException();
+ }
- private bool DoConvertColumn<T, U>(string tableName, string column, string tempColumnName, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
- {
- Hashtable data = new Hashtable();
- using (IDataReader reader = this.ExecuteQuery(String.Format("SELECT * FROM {0}", tableName)))
- {
- // Read in and convert all values
- if (reader != null)
- {
- while (reader.Read())
- {
- if (!(reader[column] is DBNull))
- {
- if (!this.CacheConvertedValues || !data.Contains(reader[column]))
- {
- U value = convertFunc.Invoke((T)reader[column], reader);
- data[reader[column]] = value;
- }
- }
- }
- }
- }
+ private bool DoConvertColumn<T, U>(string tableName, string column, string tempColumnName, TransformationProvider.ConvertColumnDelegate<T, U> convertFunc)
+ {
+ Hashtable data = new Hashtable();
+ using (IDataReader reader = this.ExecuteQuery(String.Format("SELECT * FROM {0}", tableName)))
+ {
+ // Read in and convert all values
+ if (reader != null)
+ {
+ while (reader.Read())
+ {
+ if (!(reader[column] is DBNull))
+ {
+ if (!this.CacheConvertedValues || !data.Contains(reader[column]))
+ {
+ U value = convertFunc.Invoke((T)reader[column], reader);
+ data[reader[column]] = value;
+ }
+ }
+ }
+ }
+ }
- try
- {
- // Deal with differences in SQL syntax when working with strings.
- String equalityOp = typeof(T) == typeof(String) ? "LIKE" : "=";
- String insertionSQLTemplate = String.Format("UPDATE {0} SET {1} = {2} WHERE {3} {5} {4}", tableName, tempColumnName, "{0}", column, "{1}", equalityOp);
+ try
+ {
+ // Deal with differences in SQL syntax when working with strings.
+ String equalityOp = typeof(T) == typeof(String) ? "LIKE" : "=";
+ String insertionSQLTemplate = String.Format("UPDATE {0} SET {1} = {2} WHERE {3} {5} {4}", tableName, tempColumnName, "{0}", column, "{1}", equalityOp);
- // Set values in new table.
- foreach (T key in data.Keys)
- {
- this.ExecuteNonQuery(String.Format(insertionSQLTemplate, _mapper.MapValueToText(typeof(U), data[key]), _mapper.MapValueToText(typeof(T), key)));
- }
+ // Set values in new table.
+ foreach (T key in data.Keys)
+ {
+ this.ExecuteNonQuery(String.Format(insertionSQLTemplate, _mapper.MapValueToText(typeof(U), data[key]), _mapper.MapValueToText(typeof(T), key)));
+ }
- // Remove old column
- this.RemoveColumn(tableName, column);
- // Rename new column to old name
- this.RenameColumn(tableName, tempColumnName, column);
- return true;
- }
- catch (Exception ex)
- {
- System.Diagnostics.Debug.Write(ex.ToString());
- if (ColumnExists(tableName, tempColumnName))
- this.RemoveColumn(tableName, tempColumnName);
- throw;
- }
- }
- #endregion
+ // Remove old column
+ this.RemoveColumn(tableName, column);
+ // Rename new column to old name
+ this.RenameColumn(tableName, tempColumnName, column);
+ return true;
+ }
+ catch (Exception ex)
+ {
+ System.Diagnostics.Debug.Write(ex.ToString());
+ if (ColumnExists(tableName, tempColumnName))
+ this.RemoveColumn(tableName, tempColumnName);
+ throw;
+ }
+ }
+ #endregion
- #endregion
+ #endregion
- #region Constraints
+ #region Constraints
- #region Primary Keys
- /// <summary>
- /// Append a primary key to a table.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="tableName">Table name</param>
- /// <param name="columns">Primary column names</param>
- public abstract void AddPrimaryKey(string name, string tableName, params string[] columns);
- #endregion
+ #region Primary Keys
+ /// <summary>
+ /// Append a primary key to a table.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="tableName">Table name</param>
+ /// <param name="columns">Primary column names</param>
+ public abstract void AddPrimaryKey(string name, string tableName, params string[] columns);
+ #endregion
- #region Foreign Keys
- /// <summary>
- /// Append a foreign key (relation) between two tables.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
- /// <param name="foreignKeyColumn">The Foreign Key column name</param>
- /// <param name="entityTable">The table that contains the Entity the Foreign Key references</param>
- /// <param name="primaryKeyColumn">The Primary Key column in the Entity table</param>
- public virtual void AddForeignKey(string name, string foreignKeyTable, string foreignKeyColumn, string entityTable, string primaryKeyColumn)
- {
- AddForeignKey(name, foreignKeyTable, new string[] { foreignKeyColumn }, entityTable, new string[] { primaryKeyColumn });
- }
+ #region Foreign Keys
+ /// <summary>
+ /// Append a foreign key (relation) between two tables.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
+ /// <param name="foreignKeyColumn">The Foreign Key column name</param>
+ /// <param name="entityTable">The table that contains the Entity the Foreign Key references</param>
+ /// <param name="primaryKeyColumn">The Primary Key column in the Entity table</param>
+ public virtual void AddForeignKey(string name, string foreignKeyTable, string foreignKeyColumn, string entityTable, string primaryKeyColumn)
+ {
+ AddForeignKey(name, foreignKeyTable, new string[] { foreignKeyColumn }, entityTable, new string[] { primaryKeyColumn });
+ }
- /// <summary>
- /// Append a foreign key (relation) between two tables.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
- /// <param name="foreignKeyColumn">The Foreign Key column name</param>
- /// <param name="entityTable">The table that contains the Entity the Foreign Key references</param>
- /// <param name="primaryKeyColumn">The Primary Key column in the Entity table</param>
- /// <param name="cascade">What Cascading should be performed</param>
- public virtual void AddForeignKey(string name, string foreignKeyTable, string foreignKeyColumn, string entityTable, string primaryKeyColumn, CascadeBehavior cascade)
- {
- AddForeignKey(name, foreignKeyTable, new string[] { foreignKeyColumn }, entityTable, new string[] { primaryKeyColumn }, cascade);
- }
- /// <summary>
- /// Append a foreign key (relation) between two tables.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
- /// <param name="foreignKeyColumns">The Foreign Key columns name</param>
- /// <param name="entityTableName">The table that contains the Entity the Foreign Key references</param>
- /// <param name="primaryKeyColumns">The Primary Key columns in the Entity table</param>
- /// <param name="cascade">What Cascading should be performed</param>
- public virtual void AddForeignKey(string name, string foreignKeyTable, string[] foreignKeyColumns, string entityTableName, string[] primaryKeyColumns, CascadeBehavior cascade)
- {
- throw new NotImplementedException();
- }
+ /// <summary>
+ /// Append a foreign key (relation) between two tables.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
+ /// <param name="foreignKeyColumn">The Foreign Key column name</param>
+ /// <param name="entityTable">The table that contains the Entity the Foreign Key references</param>
+ /// <param name="primaryKeyColumn">The Primary Key column in the Entity table</param>
+ /// <param name="cascade">What Cascading should be performed</param>
+ public virtual void AddForeignKey(string name, string foreignKeyTable, string foreignKeyColumn, string entityTable, string primaryKeyColumn, CascadeBehavior cascade)
+ {
+ AddForeignKey(name, foreignKeyTable, new string[] { foreignKeyColumn }, entityTable, new string[] { primaryKeyColumn }, cascade);
+ }
+ /// <summary>
+ /// Append a foreign key (relation) between two tables.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
+ /// <param name="foreignKeyColumns">The Foreign Key columns name</param>
+ /// <param name="entityTableName">The table that contains the Entity the Foreign Key references</param>
+ /// <param name="primaryKeyColumns">The Primary Key columns in the Entity table</param>
+ /// <param name="cascade">What Cascading should be performed</param>
+ public virtual void AddForeignKey(string name, string foreignKeyTable, string[] foreignKeyColumns, string entityTableName, string[] primaryKeyColumns, CascadeBehavior cascade)
+ {
+ throw new NotImplementedException();
+ }
- /// <summary>
- /// <see cref="TransformationProvider.AddForeignKey(string, string, string, string, string)">
- /// AddForeignKey(string, string, string, string, string)
- /// </see>
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
- /// <param name="foreignKeyColumns">The Foreign Key columns name</param>
- /// <param name="entityTableName">The table that contains the Entity the Foreign Key references</param>
- /// <param name="primaryKeyColumns">The Primary Key columns in the Entity table</param>
- public abstract void AddForeignKey(string name, string foreignKeyTable, string[] foreignKeyColumns, string entityTableName, string[] primaryKeyColumns);
+ /// <summary>
+ /// <see cref="TransformationProvider.AddForeignKey(string, string, string, string, string)">
+ /// AddForeignKey(string, string, string, string, string)
+ /// </see>
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="foreignKeyTable">Table to add the constraint to. Usually the table with the reference to an Entity in another table.</param>
+ /// <param name="foreignKeyColumns">The Foreign Key columns name</param>
+ /// <param name="entityTableName">The table that contains the Entity the Foreign Key references</param>
+ /// <param name="primaryKeyColumns">The Primary Key columns in the Entity table</param>
+ public abstract void AddForeignKey(string name, string foreignKeyTable, string[] foreignKeyColumns, string entityTableName, string[] primaryKeyColumns);
- public virtual ForeignKey[] GetForeignKeys(string tableName)
- {
- return GetTable(tableName).ForeignKeys.ToArray();
- }
+ public virtual ForeignKey[] GetForeignKeys(string tableName)
+ {
+ return GetTable(tableName).ForeignKeys.ToArray();
+ }
- /// <summary>
- /// Removes a constraint.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="table">Table owning the constraint</param>
- public abstract void RemoveForeignKey(string name, string table);
- #endregion
+ /// <summary>
+ /// Removes a constraint.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="table">Table owning the constraint</param>
+ public abstract void RemoveForeignKey(string name, string table);
+ #endregion
- /// <summary>
- /// Add unique constraint
- /// </summary>
- /// <param name="constraint">Name of the Constraint</param>
- /// <param name="tableName">Name of the table to add the constraint to</param>
- /// <param name="columns">Columns to constrain to uniqueness</param>
- public abstract void AddUniqueConstraint(string constraint, string tableName, params string[] columns);
+ /// <summary>
+ /// Add unique constraint
+ /// </summary>
+ /// <param name="constraint">Name of the Constraint</param>
+ /// <param name="tableName">Name of the table to add the constraint to</param>
+ /// <param name="columns">Columns to constrain to uniqueness</param>
+ public abstract void AddUniqueConstraint(string constraint, string tableName, params string[] columns);
- /// <summary>
- /// Removes an existing constraint from a table
- /// </summary>
- /// <param name="tableName">Name of the table</param>
- /// <param name="constraint">Name of the Constraint</param>
- public abstract void RemoveConstraint(string constraint, string tableName);
+ /// <summary>
+ /// Removes an existing constraint from a table
+ /// </summary>
+ /// <param name="tableName">Name of the table</param>
+ /// <param name="constraint">Name of the Constraint</param>
+ public abstract void RemoveConstraint(string constraint, string tableName);
- /// <summary>
- /// Determines if a constraint exists.
- /// </summary>
- /// <param name="name">Constraint name</param>
- /// <param name="table">Table owning the constraint</param>
- /// <returns><c>true</c> if the constraint exists.</returns>
- public abstract bool ConstraintExists(string name, string table);
+ /// <summary>
+ /// Determines if a constraint exists.
+ /// </summary>
+ /// <param name="name">Constraint name</param>
+ /// <param name="table">Table owning the constraint</param>
+ /// <returns><c>true</c> if the constraint exists.</returns>
+ public abstract bool ConstraintExists(string name, string table);
- #endregion
+ #endregion
- #region Transaction Management
- /// <summary>
- /// Starts a transaction. Called by the migration mediator.
- /// </summary>
- public void BeginTransaction()
- {
- if (_transaction == null && Connection != null)
- {
- EnsureHasConnection();
- _transaction = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
- }
- }
+ #region Transaction Management
+ /// <summary>
+ /// Starts a transaction. Called by the migration mediator.
+ /// </summary>
+ public void BeginTransaction()
+ {
+ if (_transaction == null && Connection != null)
+ {
+ EnsureHasConnection();
+ _transaction = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
+ }
+ }
- /// <summary>
- /// Commit the current transaction. Called by the migrations mediator.
- /// </summary>
- public void Commit()
- {
- if (_transaction != null && Connection != null && Connection.State == ConnectionState.Open)
- {
- try
- {
- _transaction.Commit();
- }
- catch (Exception ex)
- {
- System.Diagnostics.Debug.Write(ex.ToString());
- this.Rollback();
- throw;
- }
- }
- _transaction = null;
- }
+ /// <summary>
+ /// Commit the current transaction. Called by the migrations mediator.
+ /// </summary>
+ public void Commit()
+ {
+ if (_transaction != null && Connection != null && Connection.State == ConnectionState.Open)
+ {
+ try
+ {
+ _transaction.Commit();
+ }
+ catch (Exception ex)
+ {
+ System.Diagnostics.Debug.Write(ex.ToString());
+ this.Rollback();
+ throw;
+ }
+ }
+ _transaction = null;
+ }
- /// <summary>
- /// Rollback the current migration. Called by the migration mediator.
- /// </summary>
- public virtual void Rollback()
- {
- if (_transaction != null && Connection != null && Connection.State == ConnectionState.Open)
- {
- _transaction.Rollback();
- }
- _transaction = null;
- }
- #endregion
+ /// <summary>
+ /// Rollback the current migration. Called by the migration mediator.
+ /// </summary>
+ public virtual void Rollback()
+ {
+ if (_transaction != null && Connection != null && Connection.State == ConnectionState.Open)
+ {
+ _transaction.Rollback();
+ }
+ _transaction = null;
+ }
+ #endregion
- #region SchemaTable Management
- /// <summary>
- /// Creates the Schema Info Table
- /// </summary>
- /// <remarks> This includes opperations to update the SchemaInfo table in the case where it
- /// already existed but the AssemblyId column is missing
- /// </remarks>
- /// <param name="assemblyId"></param>
- public virtual void CreateSchemaInfoTable()
- {
- EnsureHasConnection();
+ #region SchemaTable Management
+ /// <summary>
+ /// Creates the Schema Info Table
+ /// </summary>
+ /// <remarks> This includes opperations to update the SchemaInfo table in the case where it
+ /// already existed but the AssemblyId column is missing
+ /// </remarks>
+ /// <param name="assemblyId"></param>
+ public virtual void CreateSchemaInfoTable()
+ {
+ EnsureHasConnection();
- // Does the old style schema info (without the assembly id column) table exist?
- int version = 0;
- if (this.TableExists(SCHEMA_INFO_TABLE) && !this.ColumnExists(SCHEMA_INFO_TABLE, ASSEMBLY_ID_COLUMN))
- {
- version = this.GetVersion(AssemblyId);
- this.RemoveTable(SCHEMA_INFO_TABLE);
- }
+ // Does the old style schema info (without the assembly id column) table exist?
+ int version = 0;
+ if (this.TableExists(SCHEMA_INFO_TABLE) && !this.ColumnExists(SCHEMA_INFO_TABLE, ASSEMBLY_ID_COLUMN))
+ {
+ version = this.GetVersion(AssemblyId);
+ this.RemoveTable(SCHEMA_INFO_TABLE);
+ }
- // create the table
- if (!this.TableExists(SCHEMA_INFO_TABLE))
- {
- Column[] columns = new Column[] {
+ // create the table
+ if (!this.TableExists(SCHEMA_INFO_TABLE))
+ {
+ Column[] columns = new Column[] {
new Column(VERSION_COLUMN, typeof(int), ColumnProperties.NotNull),
new Column(ASSEMBLY_ID_COLUMN, typeof(string), 255,ColumnProperties.PrimaryKey)
};
- this.AddTable(SCHEMA_INFO_TABLE, columns);
- this.InsertVersionRecord(AssemblyId);
- this.SetVersion(version, AssemblyId); // bring over any previous verion
- }
+ this.AddTable(SCHEMA_INFO_TABLE, columns);
+ this.InsertVersionRecord(AssemblyId);
+ this.SetVersion(version, AssemblyId); // bring over any previous verion
+ }
- // enchure that a version info record exists for the given assemblyId
- if (!VersionRecordExists(AssemblyId))
- this.InsertVersionRecord(AssemblyId);
- }
- /// <summary>
- /// Get or set the current version of the database.
- /// This determines if the migrator should migrate up or down
- /// in the migration numbers.
- /// </summary>
- /// <remark>
- /// This value should not be modified inside a migration.
- /// </remark>
- public virtual int CurrentVersion
- {
- get { return GetVersion(AssemblyId); }
- set { SetVersion(value, AssemblyId); }
- }
-
- /// <summary>
- /// Get the version from the SchemaInfo Table
- /// </summary>
- /// <returns>The current version</returns>
- protected virtual int GetVersion(String assemblyId)
- {
- if (assemblyId == null) return 0; // No schema info.
- if (!TableExists(SCHEMA_INFO_TABLE)) return 0;
-
- return (int)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
- }
-
- /// <summary>
- /// Set the current version in the SchemaInfo Table
- /// </summary>
- /// <param name="version">the new version to set</param>
- protected virtual void SetVersion(int version, String assemblyId)
- {
- if (assemblyId == null)
- throw new ArgumentException();
-
- if (!TableExists(SCHEMA_INFO_TABLE))
- CreateSchemaInfoTable();
-
- ExecuteNonQuery(string.Format("UPDATE {2} SET Version={0} WHERE AssemblyId='{1}'", version, assemblyId, SCHEMA_INFO_TABLE));
- }
-
-
- protected virtual void InsertVersionRecord(String assemblyId)
- {
- ExecuteNonQuery(string.Format("INSERT INTO {1} (Version, AssemblyId) VALUES (0, '{0}')", assemblyId, SCHEMA_INFO_TABLE));
- }
-
- protected virtual bool VersionRecordExists(String assemblyId)
- {
- if (assemblyId == null) return false; // No schema info.
- if (!TableExists(SCHEMA_INFO_TABLE)) return false;
-
- int? ver = (int?)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
- return ver != null;
- }
- #endregion
-
- #region General
- /// <summary>
- /// Builds a command using the current transaction
- /// </summary>
- /// <param name="sql"></param>
- /// <returns></returns>
- public virtual IDbCommand BuildCommand(string sql)
- {
- IDbCommand cmd = Connection.CreateCommand();
- cmd.CommandText = sql;
- cmd.CommandType = CommandType.Text;
- if (this.Timeout != null)
- cmd.CommandTimeout = this.Timeout.Value;
- BeginTransaction();
- if (_transaction != null)
- {
- cmd.Transaction = _transaction;
- }
- return cmd;
- }
-
- protected virtual void EnsureHasConnection()
- {
- if (Connection.State != ConnectionState.Open)
- {
- Connection.Open();
- }
- }
-
- /// <summary>
- /// Execute a raw SQl statement that will not produce a result set (e.g. Update, Insert, Delete etc.)
- /// </summary>
- /// <param name="sql">Raw SQL statement</param>
- /// <returns>the number of rows affected</returns>
- public virtual int ExecuteNonQuery(string sql)
- {
- this.Logger.Trace(sql);
- using (IDbCommand cmd = BuildCommand(sql))
- {
- return cmd.ExecuteNonQuery();
- }
- }
+ // enchure that a version info record exists for the given assemblyId
+ if (!VersionRecordExists(AssemblyId))
+ this.InsertVersionRecord(AssemblyId);
+ }
+ /// <summary>
+ /// Get or set the current version of the database.
+ /// This determines if the migrator should migrate up or down
+ /// in the migration numbers.
+ /// </summary>
+ /// <remark>
+ /// This value should not be modified inside a migration.
+ /// </remark>
+ public virtual int CurrentVersion
+ {
+ get { return GetVersion(AssemblyId); }
+ set { SetVersion(value, AssemblyId); }
+ }
/// <summary>
- /// This method can execute commands with "GO" separator
+ /// Get the version from the SchemaInfo Table
+ /// </summary>
+ /// <returns>The current version</returns>
+ protected virtual int GetVersion(String assemblyId)
+ {
+ if (assemblyId == null) return 0; // No schema info.
+ if (!TableExists(SCHEMA_INFO_TABLE)) return 0;
+
+ return (int)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
+ }
+
+ /// <summary>
+ /// Set the current version in the SchemaInfo Table
+ /// </summary>
+ /// <param name="version">the new version to set</param>
+ protected virtual void SetVersion(int version, String assemblyId)
+ {
+ if (assemblyId == null)
+ throw new ArgumentException();
+
+ if (!TableExists(SCHEMA_INFO_TABLE))
+ CreateSchemaInfoTable();
+
+ ExecuteNonQuery(string.Format("UPDATE {2} SET Version={0} WHERE AssemblyId='{1}'", version, assemblyId, SCHEMA_INFO_TABLE));
+ }
+
+
+ protected virtual void InsertVersionRecord(String assemblyId)
+ {
+ ExecuteNonQuery(string.Format("INSERT INTO {1} (Version, AssemblyId) VALUES (0, '{0}')", assemblyId, SCHEMA_INFO_TABLE));
+ }
+
+ protected virtual bool VersionRecordExists(String assemblyId)
+ {
+ if (assemblyId == null) return false; // No schema info.
+ if (!TableExists(SCHEMA_INFO_TABLE)) return false;
+
+ int? ver = (int?)ExecuteScalar(String.Format("SELECT Version FROM {1} WHERE AssemblyId='{0}'", assemblyId, SCHEMA_INFO_TABLE));
+ return ver != null;
+ }
+ #endregion
+
+ #region General
+ /// <summary>
+ /// Builds a command using the current transaction
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
- public virtual int ExecuteMSSQLBatch(string sql)
+ public virtual IDbCommand BuildCommand(string sql)
{
- this.Logger.Trace(sql);
- using (var connection = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
+ IDbCommand cmd = Connection.CreateCommand();
+ cmd.CommandText = sql;
+ cmd.CommandType = CommandType.Text;
+ if (this.Timeout != null)
+ cmd.CommandTimeout = this.Timeout.Value;
+ BeginTransaction();
+ if (_transaction != null)
{
- var server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(connection));
- return server.ConnectionContext.ExecuteNonQuery(sql);
+ cmd.Transaction = _transaction;
+ }
+ return cmd;
+ }
+
+ protected virtual void EnsureHasConnection()
+ {
+ if (Connection.State != ConnectionState.Open)
+ {
+ Connection.Open();
}
}
- /// <summary>
- /// Execute a raw SQl statement that will not produce a result set (e.g. Update, Insert, Delete etc.)
- /// </summary>
- /// <param name="format">Raw SQL string to format into</param>
- /// <param name="args">Arguments to use against the formatted string</param>
- /// <returns>The number of rows affected</returns>
- public virtual int ExecuteNonQuery(string format, params object[] args)
- {
- return ExecuteNonQuery(string.Format(format, args));
- }
+ /// <summary>
+ /// Execute a raw SQl statement that will not produce a result set (e.g. Update, Insert, Delete etc.)
+ /// </summary>
+ /// <param name="sql">Raw SQL statement</param>
+ /// <returns>the number of rows affected</returns>
+ public virtual int ExecuteNonQuery(string sql)
+ {
+ this.Logger.Trace(sql);
+ using (IDbCommand cmd = BuildCommand(sql))
+ {
+ return cmd.ExecuteNonQuery();
+ }
+ }
- /// <summary>
- /// Execute an SQL query returning results.
- /// </summary>
- /// <param name="sql">The SQL command.</param>
- /// <returns>A data iterator, <see cref="System.Data.IDataReader">IDataReader</see>.</returns>
- public virtual IDataReader ExecuteQuery(string sql)
- {
- this.Logger.Trace(sql);
- IDbCommand cmd = BuildCommand(sql);
- return cmd.ExecuteReader();
- }
+ /// <summary>
+ /// Execute a raw SQl statement that will not produce a result set (e.g. Update, Insert, Delete etc.)
+ /// </summary>
+ /// <param name="format">Raw SQL string to format into</param>
+ /// <param name="args">Arguments to use against the formatted string</param>
+ /// <returns>The number of rows affected</returns>
+ public virtual int ExecuteNonQuery(string format, params object[] args)
+ {
+ return ExecuteNonQuery(string.Format(format, args));
+ }
- /// <summary>
- /// Execute an SQL query returning the first result cast to type T.
- /// </summary>
- /// <param name="sql">The SQL command.</param>
- /// <returns>A data iterator, <see cref="System.Data.IDataReader">IDataReader</see>.</returns>
- internal virtual T ExecuteQuery<T>(string sql)
- {
- var reader = ExecuteQuery(sql);
- if (reader.Read())
- return (T)reader.GetValue(0);
- return default(T);
- }
+ /// <summary>
+ /// Execute an SQL query returning results.
+ /// </summary>
+ /// <param name="sql">The SQL command.</param>
+ /// <returns>A data iterator, <see cref="System.Data.IDataReader">IDataReader</see>.</returns>
+ public virtual IDataReader ExecuteQuery(string sql)
+ {
+ this.Logger.Trace(sql);
+ IDbCommand cmd = BuildCommand(sql);
+ return cmd.ExecuteReader();
+ }
- public virtual object ExecuteScalar(string sql)
- {
- this.Logger.Trace(sql);
- IDbCommand cmd = BuildCommand(sql);
- return cmd.ExecuteScalar();
- }
+ /// <summary>
+ /// Execute an SQL query returning the first result cast to type T.
+ /// </summary>
+ /// <param name="sql">The SQL command.</param>
+ /// <returns>A data iterator, <see cref="System.Data.IDataReader">IDataReader</see>.</returns>
+ internal virtual T ExecuteQuery<T>(string sql)
+ {
+ var reader = ExecuteQuery(sql);
+ if (reader.Read())
+ return (T)reader.GetValue(0);
+ return default(T);
+ }
- /// <param name="commandText">Format String</param>
- /// <param name="parameters">Arguments to use against the formatted string</param>
- /// <returns>Int</returns>
- public virtual int ParameterizedNonQuery(string commandText, Dictionary<String, Object> parameters)
- {
- IDbCommand cmd = BuildCommand(commandText);
- cmd.Prepare();
- foreach (String key in parameters.Keys)
- {
- Object value = parameters[key];
+ public virtual object ExecuteScalar(string sql)
+ {
+ this.Logger.Trace(sql);
+ IDbCommand cmd = BuildCommand(sql);
+ return cmd.ExecuteScalar();
+ }
- IDbDataParameter dataParam = cmd.CreateParameter();
- dataParam.ParameterName = key;
- dataParam.Value = value;
- Type type = value.GetType();
- if (type == typeof(String))
- dataParam.DbType = DbType.String;
- else if (type == typeof(Int32))
- dataParam.DbType = DbType.Int32;
- else if (type == typeof(DateTime))
- dataParam.DbType = DbType.DateTime;
- else if (type == typeof(TimeSpan))
- dataParam.DbType = DbType.UInt64;
- else if (type == typeof(Guid))
- dataParam.DbType = DbType.Guid;
- else if (type == typeof(Boolean))
- dataParam.DbType = DbType.Boolean;
+ /// <param name="commandText">Format String</param>
+ /// <param name="parameters">Arguments to use against the formatted string</param>
+ /// <returns>Int</returns>
+ public virtual int ParameterizedNonQuery(string commandText, Dictionary<String, Object> parameters)
+ {
+ IDbCommand cmd = BuildCommand(commandText);
+ cmd.Prepare();
+ foreach (String key in parameters.Keys)
+ {
+ Object value = parameters[key];
- cmd.Parameters.Add(dataParam);
- }
- return cmd.ExecuteNonQuery();
+ IDbDataParameter dataParam = cmd.CreateParameter();
+ dataParam.ParameterName = key;
+ dataParam.Value = value;
+ Type type = value.GetType();
+ if (type == typeof(String))
+ dataParam.DbType = DbType.String;
+ else if (type == typeof(Int32))
+ dataParam.DbType = DbType.Int32;
+ else if (type == typeof(DateTime))
+ dataParam.DbType = DbType.DateTime;
+ else if (type == typeof(TimeSpan))
+ dataParam.DbType = DbType.UInt64;
+ else if (type == typeof(Guid))
+ dataParam.DbType = DbType.Guid;
+ else if (type == typeof(Boolean))
+ dataParam.DbType = DbType.Boolean;
- }
+ cmd.Parameters.Add(dataParam);
+ }
+ return cmd.ExecuteNonQuery();
- #endregion
+ }
- #region Data Manipulation
- /// <summary>
- /// Copies rows from one table to another
- /// </summary>
- /// <param name="fromTable">The name of the table being copied from</param>
- /// <param name="toTable">The table being copied to...</param>
- /// <param name="fromColumns">The names of the columns being copied. These must be in the same order as the names of the matching <see cref="toColumns"/></param>
- /// <param name="toColumns">The names of the columns in the toTable that match the columns in the fromTable</param>
- /// <param name="fromWhere">Where statement that filters the from table.</param>
- public void CopyFromTable(string fromTable, string toTable, string[] fromColumns, string[] toColumns, string fromWhere)
- {
- if (fromColumns.Length != toColumns.Length)
- {
- throw new ArgumentOutOfRangeException("FromColumns and toColumns must have matching numbers.");
- }
+ #endregion
- string sql = string.Format("Insert Into [{0}] ({1}) Select {2} From [{3}] {4} ",
- toTable,
- string.Join(", ", toColumns),
- string.Join(", ", fromColumns),
- fromTable,
- fromWhere.Length > 1 ? "Where " + fromWhere : "");
+ #region Data Manipulation
+ /// <summary>
+ /// Copies rows from one table to another
+ /// </summary>
+ /// <param name="fromTable">The name of the table being copied from</param>
+ /// <param name="toTable">The table being copied to...</param>
+ /// <param name="fromColumns">The names of the columns being copied. These must be in the same order as the names of the matching <see cref="toColumns"/></param>
+ /// <param name="toColumns">The names of the columns in the toTable that match the columns in the fromTable</param>
+ /// <param name="fromWhere">Where statement that filters the from table.</param>
+ public void CopyFromTable(string fromTable, string toTable, string[] fromColumns, string[] toColumns, string fromWhere)
+ {
+ if (fromColumns.Length != toColumns.Length)
+ {
+ throw new ArgumentOutOfRangeException("FromColumns and toColumns must have matching numbers.");
+ }
- this.Logger.Trace("Copying from table [{0}]", sql);
- this.ExecuteNonQuery(sql);
+ string sql = string.Format("Insert Into [{0}] ({1}) Select {2} From [{3}] {4} ",
+ toTable,
+ string.Join(", ", toColumns),
+ string.Join(", ", fromColumns),
+ fromTable,
+ fromWhere.Length > 1 ? "Where " + fromWhere : "");
- }
+ this.Logger.Trace("Copying from table [{0}]", sql);
+ this.ExecuteNonQuery(sql);
- [Obsolete("Use the other Insert variant, this one is not safe!")]
- public virtual int Insert(string table, params string[] columnValues)
- {
- string[] columns = new string[columnValues.Length];
- string[] values = new string[columnValues.Length];
- int i = 0;
+ }
- foreach (string cs in columnValues)
- {
- columns[i] = cs.Split('=')[0];
- values[i] = cs.Split('=')[1];
- i++;
- }
+ [Obsolete("Use the other Insert variant, this one is not safe!")]
+ public virtual int Insert(string table, params string[] columnValues)
+ {
+ string[] columns = new string[columnValues.Length];
+ string[] values = new string[columnValues.Length];
+ int i = 0;
- return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", values)));
- }
+ foreach (string cs in columnValues)
+ {
+ columns[i] = cs.Split('=')[0];
+ values[i] = cs.Split('=')[1];
+ i++;
+ }
- /// <summary>
- /// Insert a row into a table
- /// </summary>
- /// <param name="tableName">The name of the table</param>
- /// <param name="columns">A list of column names</param>
- /// <param name="values">A list of primitive values that can be converted to SQL by the TransformationProvider</param>
- /// <returns></returns>
- public virtual int Insert(string table, string[] columns, params object[] values)
- {
- if (columns.Length != values.Length)
- throw new ArgumentException("The Number of Columns and the number of Values are not the same!");
+ return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", values)));
+ }
- List<string> sqlValues = new List<string>();
- foreach (var value in values)
- {
- sqlValues.Add(_mapper.MapValueToText(value.GetType(), value));
- }
+ /// <summary>
+ /// Insert a row into a table
+ /// </summary>
+ /// <param name="tableName">The name of the table</param>
+ /// <param name="columns">A list of column names</param>
+ /// <param name="values">A list of primitive values that can be converted to SQL by the TransformationProvider</param>
+ /// <returns></returns>
+ public virtual int Insert(string table, string[] columns, params object[] values)
+ {
+ if (columns.Length != values.Length)
+ throw new ArgumentException("The Number of Columns and the number of Values are not the same!");
- return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", sqlValues.ToArray())));
- }
+ List<string> sqlValues = new List<string>();
+ foreach (var value in values)
+ {
+ sqlValues.Add(_mapper.MapValueToText(value.GetType(), value));
+ }
- public virtual IDataReader Select(string what, string from)
- {
- return Select(what, from, "1=1");
- }
+ return ExecuteNonQuery(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, string.Join(", ", columns), string.Join(", ", sqlValues.ToArray())));
+ }
- public virtual IDataReader Select(string what, string from, string where)
- {
- return ExecuteQuery(string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
- }
+ public virtual IDataReader Select(string what, string from)
+ {
+ return Select(what, from, "1=1");
+ }
- public virtual object SelectScalar(string what, string from)
- {
- return SelectScalar(what, from, "1=1");
- }
+ public virtual IDataReader Select(string what, string from, string where)
+ {
+ return ExecuteQuery(string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
+ }
- public virtual object SelectScalar(string what, string from, string where)
- {
- return ExecuteScalar(string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
- }
- public virtual int Update(string table, params string[] columnValues)
- {
- return ExecuteNonQuery(string.Format("UPDATE {0} SET {1}", table, string.Join(", ", columnValues)));
- }
+ public virtual object SelectScalar(string what, string from)
+ {
+ return SelectScalar(what, from, "1=1");
+ }
- #endregion
+ public virtual object SelectScalar(string what, string from, string where)
+ {
+ return ExecuteScalar(string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
+ }
+ public virtual int Update(string table, params string[] columnValues)
+ {
+ return ExecuteNonQuery(string.Format("UPDATE {0} SET {1}", table, string.Join(", ", columnValues)));
+ }
- #region To SQL Converters
-
- protected virtual string GetTextFor(Column col)
- {
- // Force boolean values to have a default
- if (col.Type == typeof(bool) && col.ColumnProperty != ColumnProperties.Null && col.DefaultValue == null)
- col.DefaultValue = false;
+ #endregion
- string sqlDefaultKeyword = "DEFAULT ";
- string sqlDefault = "";
+ #region To SQL Converters
+
+ protected virtual string GetTextFor(Column col)
+ {
+ // Force boolean values to have a default
+ if (col.Type == typeof(bool) && col.ColumnProperty != ColumnProperties.Null && col.DefaultValue == null)
+ col.DefaultValue = false;
- // assign default values, ignore defaults if they have been overloaded for PK/Identity purposes
- if (col.DefaultValue != null)
- sqlDefault = sqlDefaultKeyword + _mapper.MapValueToText(col.Type, col.DefaultValue);
+ string sqlDefaultKeyword = "DEFAULT ";
+ string sqlDefault = "";
- string sqlIdentity = "";
- string sqlConstraint = "";
- switch (col.ColumnProperty)
- {
- case ColumnProperties.Null:
- sqlConstraint = "NULL";
- break;
- case ColumnProperties.NotNull:
- sqlConstraint = "NOT NULL";
- break;
- // means a unique ID or a uniquely generated GUID
- case ColumnProperties.Identity:
- sqlConstraint = "NOT NULL"; // Identity columns must also be Not Null
- if (col.Type == typeof(Guid))
- sqlDefault = sqlDefaultKeyword + "NEWID()";
- else
- sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
- break;
- case ColumnProperties.PrimaryKey:
- sqlConstraint = "PRIMARY KEY";
- break;
- // means a unique ID or a uniquely generated GUID as the PK
- case ColumnProperties.PrimaryKeyWithIdentity:
- sqlConstraint = "PRIMARY KEY";
- if (col.Type == typeof(Guid))
- sqlDefault = sqlDefaultKeyword + "NEWID()";
- else
- sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
- break;
- case ColumnProperties.UNIQUE:
- sqlConstraint = "UNIQUE";
- break;
- }
+ // assign default values, ignore defaults if they have been overloaded for PK/Identity purposes
+ if (col.DefaultValue != null)
+ sqlDefault = sqlDefaultKeyword + _mapper.MapValueToText(col.Type, col.DefaultValue);
- string sqlType = _mapper.MapFromTypeToText(col.Type, col.Size);
- return string.Join(" ", new string[] { col.Name, sqlType, sqlDefault, sqlIdentity, sqlConstraint });
- }
+ string sqlIdentity = "";
+ string sqlConstraint = "";
+ switch (col.ColumnProperty)
+ {
+ case ColumnProperties.Null:
+ sqlConstraint = "NULL";
+ break;
+ case ColumnProperties.NotNull:
+ sqlConstraint = "NOT NULL";
+ break;
+ // means a unique ID or a uniquely generated GUID
+ case ColumnProperties.Identity:
+ sqlConstraint = "NOT NULL"; // Identity columns must also be Not Null
+ if (col.Type == typeof(Guid))
+ sqlDefault = sqlDefaultKeyword + "NEWID()";
+ else
+ sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
+ break;
+ case ColumnProperties.PrimaryKey:
+ sqlConstraint = "PRIMARY KEY";
+ break;
+ // means a unique ID or a uniquely generated GUID as the PK
+ case ColumnProperties.PrimaryKeyWithIdentity:
+ sqlConstraint = "PRIMARY KEY";
+ if (col.Type == typeof(Guid))
+ sqlDefault = sqlDefaultKeyword + "NEWID()";
+ else
+ sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
+ break;
+ case ColumnProperties.UNIQUE:
+ sqlConstraint = "UNIQUE";
+ break;
+ }
- protected virtual string GetTextFor(ForeignKey fk)
- {
+ string sqlType = _mapper.MapFromTypeToText(col.Type, col.Size);
+ return string.Join(" ", new string[] { col.Name, sqlType, sqlDefault, sqlIdentity, sqlConstraint });
+ }
- return "CONSTRAINT {key.Name} FOREIGN KEY({constrained}) REFERENCES {key.ReferencedTable}({referenced})".FormatWith(
- new { key = fk, constrained = String.Join(",", fk.ConstrainedFields), referenced = String.Join(",", fk.ReferencedFields) });
- }
+ protected virtual string GetTextFor(ForeignKey fk)
+ {
+ return "CONSTRAINT {key.Name} FOREIGN KEY({constrained}) REFERENCES {key.ReferencedTable}({referenced})".FormatWith(
+ new { key = fk, constrained = String.Join(",", fk.ConstrainedFields), referenced = String.Join(",", fk.ReferencedFields) });
+ }
- #endregion
- #region DebugHelpers
+ #endregion
- protected internal String DumpTableContents(String table)
- {
- StringBuilder builder = new StringBuilder();
- int i = 0;
- using (IDataReader reader = ExecuteQuery(string.Format("SELECT * FROM {0}", table)))
- {
- if (i == 0)
- {
- for (int j = 0; j < reader.FieldCount; j++)
- {
- builder.Append(reader.GetName(j).ToString());
- builder.Append("\t\t|\t\t");
- }
- builder.AppendLine();
- builder.AppendLine("---------------------------------------------------------------"); i++;
- }
+ #region DebugHelpers
- while (reader.Read())
- {
- var fields = new Object[reader.FieldCount];
- reader.GetValues(fields);
+ protected internal String DumpTableContents(String table)
+ {
+ StringBuilder builder = new StringBuilder();
+ int i = 0;
+ using (IDataReader reader = ExecuteQuery(string.Format("SELECT * FROM {0}", table)))
+ {
+ if (i == 0)
+ {
+ for (int j = 0; j < reader.FieldCount; j++)
+ {
+ builder.Append(reader.GetName(j).ToString());
+ builder.Append("\t\t|\t\t");
+ }
+ builder.AppendLine();
+ builder.AppendLine("---------------------------------------------------------------"); i++;
+ }
- builder.AppendLine(String.Join("\t\t\t\t", fields));
- }
- }
- System.Diagnostics.Debug.Write(builder.ToString());
- return builder.ToString();
- }
+ while (reader.Read())
+ {
+ var fields = new Object[reader.FieldCount];
+ reader.GetValues(fields);
- #endregion
+ builder.AppendLine(String.Join("\t\t\t\t", fields));
+ }
+ }
+ System.Diagnostics.Debug.Write(builder.ToString());
+ return builder.ToString();
+ }
- #region IDisposable Members
- public virtual void Dispose()
- {
- if (this.Connection != null && this.Connection.State == ConnectionState.Open)
- {
- this.Connection.Close();
- this.Connection.Dispose();
- }
- }
+ #endregion
- #endregion
- }
+ #region IDisposable Members
+ public virtual void Dispose()
+ {
+ if (this.Connection != null && this.Connection.State == ConnectionState.Open)
+ {
+ this.Connection.Close();
+ this.Connection.Dispose();
+ }
+ }
+
+ #endregion
+ }
}
|
|
|
|
|
|
|
|
|
|
|
|
|
Add a tag
Loading...