/// <summary>
/// Provides the first column of the first row from the query resultset.
/// Extra columns or rows are ignored.
/// </summary>
/// <param name="datasource"></param>
/// <param name="query">The <see cref="IAdfQuery"/> that defines the datasource name and query statement.</param>
/// <returns>First item of query result. This method also returns 0 if <see cref="IAdfQuery"/> is null.</returns>
/// <exception cref="System.InvalidOperationException">The current state of the connection is closed.</exception>
public static object RunScalar(DataSources datasource, IAdfQuery query)
{
using (new TracingScope())
{
return query == null ? 0 : GetHandler(datasource).RunScalar(query);
}
}
/// <summary>
/// Destroy any connection and reset all the transactions on the specified database.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
public virtual void DestroyConnection(DataSources source)
{
ResetTransaction(source);
IDbConnection connection = GetConnection(source);
connection.Close();
StateManager.Personal.Remove(source);
}
/// <summary>
/// Executes a specified <see cref="IAdfQuery"/> statement and returns the affected row.
/// </summary>
/// <param name="datasource"></param>
/// <param name="query">The <see cref="IAdfQuery"/> that defines the datasource name and query statement.</param>
/// <returns>The query result into <see cref="IInternalState"/> object, if found the <see cref="IAdfQuery"/>;
/// otherwise, null value object of <see cref="Adf.Core.Data.NullInternalState"/>.</returns>
/// <exception cref="System.InvalidOperationException">The current state of the connection is closed.</exception>
public static IInternalState Run(DataSources datasource, IAdfQuery query)
{
using (new TracingScope())
{
return query == null ? NullInternalState.Null : GetHandler(datasource).Run(query);
}
}
public static int Delete(DataSources dataSource, IAdfQuery query)
{
if (query == null) throw new ArgumentNullException("query");
using (new TracingScope())
{
return GetHandler(dataSource).Delete(query);
}
}
/// <summary>
/// Commit the transaction for the database specified.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
/// <exception cref="System.Exception">An error occurred while trying to commit the transaction.</exception>
/// <exception cref="System.InvalidOperationException">The transaction has already been committed or rolled back.-or- The connection is broken.</exception>
public virtual void Commit(DataSources source)
{
IDbTransaction transaction = GetTransaction(source);
if (transaction != null)
{
transaction.Commit();
DestroyConnection(source);
}
}
/// <summary>
/// Provides information to start a transaction to be performed at a data source.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
/// <returns>The <see cref="System.Data.IDbTransaction"/> object for the currently executing transaction.</returns>
/// <exception cref="System.Exception">An error occurred while trying to start the transaction.</exception>
public virtual IDbTransaction StartTransaction(DataSources source)
{
IDbConnection connection = GetConnection(source);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
IDbTransaction transaction = GetTransaction(source) ?? connection.BeginTransaction(IsolationLevel.Serializable);
SetTransaction(source, transaction);
return transaction;
}
/// <summary>
/// Provides information to setup the data adapter by the connection of <see cref="System.Data.IDbConnection"/>.
/// </summary>
/// <param name="datasource"></param>
/// <param name="connection">The <see cref="System.Data.IDbConnection"/> represents an open connection to a data source.</param>
/// <param name="query">The <see cref="IAdfQuery"/> whose SQL information is to be retrieved.</param>
/// <returns>The requested <see cref="System.Data.IDbDataAdapter"/> information object.</returns>
public virtual IDbDataAdapter SetUpAdapter(DataSources datasource, IDbConnection connection, IAdfQuery query)
{
var da = (SQLiteDataAdapter) GetAdapter();
if (query != null)
{
da.TableMappings.Add("Table", query.LeadTable());
var selectCommand = (SQLiteCommand) GetCommand(datasource, connection, query);
da.SelectCommand = selectCommand;
// create command builder for a new command, so we can customize the insert command
var newda = new SQLiteDataAdapter(selectCommand);
// Create and associate a commandbuilder which can generate insert and update queries for the DataAdapter. This is a necessary step!
var commandBuilder = new SQLiteCommandBuilder(newda);
da.InsertCommand = commandBuilder.GetInsertCommand();
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.UpdateCommand = commandBuilder.GetUpdateCommand();
da.DeleteCommand = commandBuilder.GetDeleteCommand();
}
return da;
}
/// <summary>
/// Rolls back a transaction from a pending state.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
/// <exception cref="System.Exception">An error occurred while trying to rollback the transaction.</exception>
/// <exception cref="System.InvalidOperationException">The transaction has already been committed or rolled back.-or- The connection is broken.</exception>
public virtual void Rollback(DataSources source)
{
IDbTransaction transaction = GetTransaction(source);
if (transaction != null)
{
transaction.Rollback();
DestroyConnection(source);
}
}
/// <summary>
/// Saved the specified data of <see cref="IInternalState"/> into database.
/// </summary>
/// <param name="datasource">the datasource to run the query against</param>
/// <param name="query">The <see cref="IAdfQuery"/> that defines the datasource name and query statement.</param>
/// <param name="state">The data of <see cref="IInternalState"/> that needs to be saved.</param>
/// <returns>True if data is successfully saved; otherwise, false.
/// This method also returns false if <see cref="IAdfQuery"/> or <see cref="IInternalState"/> is null.</returns>
/// <exception cref="System.InvalidOperationException">The current state of the connection is closed.</exception>
public static bool Save(DataSources datasource, IAdfQuery query, IInternalState state)
{
using (new TracingScope())
{
return query != null && state != null && GetHandler(datasource).Save(query, state);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="TableDescriber"/> class with the specified table name and <see cref="DataSources"/> object.
/// </summary>
/// <param name="tableName">The name of a table.</param>
/// <param name="datasource">Initialize the connection by <see cref="DataSources"/>.</param>
public TableDescriber(string tableName, DataSources datasource)
{
_name = tableName;
_dataSource = datasource;
}
/// <summary>
/// Reset all database transaction for the specified database.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
public static void ResetTransaction(DataSources source)
{
StateManager.Personal[source, "Transaction"] = null;
}
/// <summary>
/// Executes the specified query and return the result, where each row in the result is stored in an
/// instance of <see cref="IInternalState"/>.
/// </summary>
/// <param name="datasource"></param>
/// <param name="query">The <see cref="IAdfQuery"/> that defines the datasource name and query statement.</param>
/// <returns>Query results, where each row is in a specific <see cref="IInternalState"/> object, if found the <see cref="IAdfQuery"/>;
/// otherwise, a NullArray of <see cref="Adf.Core.Data.NullInternalState"/>.</returns>
/// <exception cref="System.InvalidOperationException">The current state of the connection is closed.</exception>
public static IEnumerable<IInternalState> RunSplit(DataSources datasource, IAdfQuery query)
{
using (new TracingScope())
{
return query == null ? NullInternalState.NullList : GetHandler(datasource).RunAndSplit(query);
}
}
public static string Parse(DataSources type, IAdfQuery query)
{
return Get(type).Parse(query);
}
private static IQueryParser Get(DataSources type)
{
IQueryParser parser;
lock (_parserLock)
{
if (!Parsers.TryGetValue(type, out parser))
{
parser = ObjectFactory.BuildUp<IQueryParser>(type.ToString());
Parsers.Add(type, parser);
}
}
return parser;
}
internal static IAdfQueryHandler GetHandler(DataSources source)
{
lock (_handlerLock)
{
if (!handlers.ContainsKey(source))
{
IAdfQueryHandler handler = ObjectFactory.BuildUp<IAdfQueryHandler>(source.Name);
handler.DataSource = source;
handlers.Add(source, handler);
}
}
return handlers[source];
}
/// <summary>
/// Creates a new SQL Server database connection based on the connection string specified in the data source.
/// </summary>
/// <param name="source">The data source containing the connection string.</param>
/// <returns>The new SQL Server database connection.</returns>
protected static IDbConnection CreateConnection(DataSources source)
{
string connectionString = StateManager.Settings[source.ToString()] as string;
IDbConnection connection = new SQLiteConnection(connectionString);
// SetConnection(source, connection);
return connection;
}
/// <summary>
/// Adds a connection to the connection list.
/// </summary>
/// <param name="dataSource">The data source that is used to indicate the connection.</param>
/// <param name="connection">The connection to set.</param>
protected static void SetConnection(DataSources dataSource, IDbConnection connection)
{
StateManager.Personal[dataSource, "Connection.SqlServer"] = connection;
}
/// <summary>
/// Provides information about SQL command generated by the connection of <see cref="System.Data.IDbConnection"/> and query of <see cref="IAdfQuery"/>.
/// </summary>
/// <param name="connection">The <see cref="System.Data.IDbConnection"/> represents an open connection to a data source.</param>
/// <param name="datasource"></param>
/// <param name="query">The <see cref="IAdfQuery"/> whose SQL command information is to be retrieved.</param>
/// <returns>The requested information of SQL command object.</returns>
public virtual IDbCommand GetCommand(DataSources datasource, IDbConnection connection, IAdfQuery query)
{
var cmd = query.QueryType == QueryType.StoredProcedure
? new SQLiteCommand(query.LeadTable(), (SQLiteConnection) connection) {CommandType = CommandType.StoredProcedure}
: new SQLiteCommand(QueryManager.Parse(datasource, query), (SQLiteConnection) connection);
if (query.TimeOut.HasValue) cmd.CommandTimeout = query.TimeOut.Value;
var allWheres = GetWhereParameters(query);
foreach (IWhere w in allWheres.Where(w => w.Parameter != null))
{
if ((w.Parameter.Type == ParameterType.Value || w.Parameter.Type == ParameterType.QueryParameter) &&
w.Parameter.Value != null)
{
object val = w.Parameter.Value is Guid
? string.Concat("{", w.Parameter.Value.ToString().ToUpper(), "}")
: w.Parameter.Value;
cmd.Parameters.Add(new SQLiteParameter(w.Parameter.Name, val));
}
if (w.Parameter.Type != ParameterType.ValueList) continue;
int i = 0;
foreach (var v in ((IEnumerable) w.Parameter.Value))
{
object val = v is Guid
? string.Concat("{", v.ToString().ToUpper(), "}")
: w.Parameter.Value;
cmd.Parameters.AddWithValue(string.Format("{0}_v{1}", w.Parameter.Name, i++), val);
}
}
return cmd;
}
/// <summary>
/// Add a transaction for a specific database to the transactions list.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
/// <param name="transaction">The <see cref="System.Data.IDbTransaction"/> represents a transaction to be performed at a data source.</param>
public static void SetTransaction(DataSources source, IDbTransaction transaction)
{
StateManager.Personal[source, "Transaction"] = transaction;
}
/// <summary>
/// Specifies the connection from the connection list or
/// create a new connection if it is not yet defined.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> that indicates the connection.</param>
/// <returns>The connection object to associate with the transaction.</returns>
public virtual IDbConnection GetConnection(DataSources source)
{
// IDbConnection connection = StateManager.Personal[source, "Connection.SqlServer"] as IDbConnection;
return CreateConnection(source);
}
/// <summary>
/// Get a transaction object from the transactions list. If none present a null value will be returned.
/// </summary>
/// <param name="source">The <see cref="DataSources"/> used to get the data source name.</param>
/// <returns>The <see cref="System.Data.IDbTransaction"/> object for the currently executing transaction.</returns>
public virtual IDbTransaction GetTransaction(DataSources source)
{
return StateManager.Personal[source, "Transaction"] as IDbTransaction;
}
/// <summary>
/// Handles exceptions thrown when excequring queries.
/// </summary>
/// <param name="exception"></param>
/// <param name="datasource"></param>
/// <param name="query"></param>
public virtual void HandleException(Exception exception, DataSources datasource, IAdfQuery query)
{
LogManager.Log(exception);
if (exception is DBConcurrencyException)
{
ValidationManager.AddError("Adf.Data.UnderlyingDataChanged", query.LeadTable());
}
else if (exception is SQLiteException)
{
switch ((exception as SQLiteException).ErrorCode)
{
case SQLiteErrorCode.Constraint:
ValidationManager.AddError("Adf.Data.ForeignKeyConstraintsViolated", query.LeadTable());
break;
default:
throw new DataException(exception.Message, exception);
}
}
else
{
throw new DataException(exception.Message, exception);
}
}
/// <summary>
/// Creates a new instance of the <see cref="TableDescriber"/> class with the specified table name and <see cref="DataSources"/> object.
/// </summary>
/// <param name="tableName">The name of a table.</param>
/// <param name="datasource">Initialize the connection by <see cref="DataSources"/>.</param>
/// <returns>The new instance of the <see cref="TableDescriber"/> class with the specified table name and <see cref="DataSources"/> object</returns>
public static TableDescriber New(string tableName, DataSources datasource)
{
return new TableDescriber(tableName, datasource);
}
/// <summary>
/// Provides information about SQL command generated by the connection of <see cref="System.Data.IDbConnection"/> and query of <see cref="IAdfQuery"/>.
/// </summary>
/// <param name="connection">The <see cref="System.Data.IDbConnection"/> represents an open connection to a data source.</param>
/// <param name="datasource"></param>
/// <param name="query">The <see cref="IAdfQuery"/> whose SQL command information is to be retrieved.</param>
/// <returns>The requested information of SQL command object.</returns>
public virtual IDbCommand GetCommand(DataSources datasource, IDbConnection connection, IAdfQuery query)
{
var cmd = query.QueryType == QueryType.StoredProcedure
? new SqlCommand(query.LeadTable(), (SqlConnection) connection) {CommandType = CommandType.StoredProcedure}
: new SqlCommand(QueryManager.Parse(datasource, query), (SqlConnection) connection);
if (query.TimeOut.HasValue) cmd.CommandTimeout = query.TimeOut.Value;
var allWheres = GetWhereParameters(query);
foreach (IWhere w in allWheres.Where(w => w.Parameter != null))
{
if ((w.Parameter.Type == ParameterType.Value || w.Parameter.Type == ParameterType.QueryParameter) && w.Parameter.Value != null)
{
cmd.Parameters.Add(new SqlParameter(w.Parameter.Name, w.Parameter.Value));
}
if (w.Parameter.Type == ParameterType.ValueList && w.Parameter.Value != null)
{
int i = 0;
foreach (var v in ((IEnumerable) w.Parameter.Value))
{
cmd.Parameters.AddWithValue(string.Format("{0}_v{1}", w.Parameter.Name, i++), v ?? DBNull.Value);
}
}
}
return cmd;
}
