/// <summary>Initializes a new instance of the <see cref="ViewTableForm"/> class.</summary>
/// <param name="services">The services.</param>
/// <param name="settings">The settings.</param>
public ViewTableForm(IApplicationServices services, IApplicationSettings settings)
: this()
{
_services = services;
_settings = settings;
_batch = new QueryBatch();
TableName = string.Empty;
Text = Resources.ViewData;
dataGridViewResult.DefaultCellStyle.NullValue = _settings.NullText;
dataGridViewResult.DataBindingComplete += DataGridViewResultDataBindingComplete;
_services.Settings.DatabaseConnectionReset += SettingsDatabaseConnectionReset;
_services.SystemMessagePosted += ServicesSystemMessagePosted;
}
/// <summary>
/// Executes the <paramref name = "sql" /> query.
/// </summary>
/// <param name = "sql">The SQL to execute.</param>
public void ExecuteQuery(string sql)
{
ValidateState();
DbConnection dbConnection = null;
DbDataAdapter adapter = null;
DbCommand cmd = null;
Query query;
try
{
IsBusy = true;
dbConnection = _factory.CreateConnection();
dbConnection.ConnectionString = _connectionString;
dbConnection.Open();
Messages = string.Empty;
SubscribeToMessages(dbConnection);
if (_enableQueryBatching)
{
Batch = QueryBatch.Parse(sql);
}
else
{
Batch = new QueryBatch(sql);
}
Batch.StartTime = DateTime.Now;
adapter = _factory.CreateDataAdapter();
cmd = dbConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
SetCommandTimeout(cmd, _commandTimeout);
adapter.SelectCommand = cmd;
int queryCount = Batch.Queries.Count;
for (int i = 0; i < queryCount; i++)
{
query = Batch.Queries[i];
cmd.CommandText = query.Sql;
query.Result = new DataSet("Batch " + (i + 1));
query.StartTime = DateTime.Now;
adapter.Fill(query.Result);
query.EndTime = DateTime.Now;
OnBatchProgress(new BatchProgressEventArgs(query, queryCount, i + 1));
}
}
catch (DbException dbException)
{
HandleBatchException(dbException);
}
finally
{
if (Batch != null)
{
Batch.EndTime = DateTime.Now;
}
if (adapter != null)
{
adapter.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
IsBusy = false;
UnsubscribeFromMessages(dbConnection);
}
if (Batch != null)
{
Batch.Messages = Messages;
}
}
/// <summary>
/// Parses an <paramref name="sql"/> string creating a <see cref="QueryBatch"/> as a result.
/// If query batching is enabled, the <paramref name="sql"/> string is split into multiple <see cref="Query"/> objects.
/// </summary>
/// <param name = "sql">The SQL string.</param>
/// <returns>A <see cref="QueryBatch"/> object with 0, 1 or many <see cref="Query"/> objects.</returns>
public static QueryBatch Parse(string sql)
{
var batch = new QueryBatch();
// exit if nothing to do
if (sql == null || sql.Trim().Length == 0)
{
return batch;
}
foreach (string sqlPart in SplitByBatchIndecator(sql, "GO").Where(sqlPart => !string.IsNullOrEmpty(sqlPart)))
{
batch.Add(new Query(sqlPart));
}
return batch;
}
/// <summary>The get.</summary>
/// <param name="viewOrTableName">The view or table name.</param>
/// <returns></returns>
public DataTable Get(string viewOrTableName)
{
DbDataAdapter adapter = null;
DbCommand cmd = null;
DataTable dt = null;
QueryBatch batch = new QueryBatch();
Query query = new Query("SELECT * FROM " + Utility.MakeSqlFriendly(viewOrTableName));
if (string.IsNullOrEmpty(viewOrTableName))
{
return null;
}
if (_dataTables.ContainsKey(viewOrTableName))
{
return _dataTables[viewOrTableName];
}
try
{
if (_dbConnection == null || _dbConnection.State != ConnectionState.Open)
{
_dbConnection = Services.Settings.GetOpenConnection();
}
query.Result = new DataSet(viewOrTableName + " View");
batch.Clear();
batch.Add(query);
adapter = Services.Settings.ProviderFactory.CreateDataAdapter();
cmd = _dbConnection.CreateCommand();
cmd.CommandText = query.Sql;
cmd.CommandType = CommandType.Text;
adapter.SelectCommand = cmd;
adapter.Fill(query.Result);
}
// catch (Exception exp)
// {
// throw;
// }
finally
{
if (adapter != null)
{
adapter.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
if (query.Result.Tables.Count > 0)
{
dt = query.Result.Tables[0];
_dataTables[viewOrTableName] = dt;
}
return dt;
}
