DataTable IPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection(context.ConnectionString))
{
using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach (DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = vistaRow["VIEW_NAME"];
row["DESCRIPTION"] = vistaRow["DESCRIPTION"];
row["VIEW_TEXT"] = vistaRow["VIEW_DEFINITION"];
row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
}
}
}
}
}
catch {}
return(metaData);
}
public DataTable ViewPending(string serch)
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT PoNumber as PONumber,UserId,Date FROM dbo.PaymentOrder where status='Pending'";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
{
var views = new List <ViewSchema>();
var extendedProperties = new List <ExtendedProperty>();
const string sql = "SELECT VIEW_NAME, DESCRIPTION, IS_UPDATABLE FROM GetViews()";
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row[0].ToString();
string description = row[1].ToString();
bool isUpdatable = (bool)(row[2] ?? true);
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUpdatable", isUpdatable));
var view = new ViewSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());
views.Add(view);
}
}
return(views.ToArray());
}
public DataTable ViewTransactions(string schoolname)
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT po.Id AS 'Transaction Id',po.Beneficiary,po.Payee,po.Amount,po.Date,po.Status FROM PaymentOrder po WHERE po.SchoolId=(SELECT s.Id FROM School s WHERE s.SchoolName='{schoolname}') and po.RecievedDate BETWEEN GETDATE()-30 AND GETDATE()";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
public DataTable ViewPending()
{
var data = new DataTable();
using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString))
{
try
{
connection.Open();
using (VistaDBCommand command = new VistaDBCommand())
{
command.Connection = connection;
var query = command.CommandText = $"SELECT Id,PoNumber,Beneficiary,Amount,(SELECT SchoolName FROM School s WHERE s.Id=p.SchoolId) AS 'School' FROM dbo.PaymentOrder p where status='Pending'";
var adapter = new VistaDBDataAdapter(command.CommandText, command.Connection);
adapter.Fill(data);
connection.Close();
}
}
catch (VistaDBException exception)
{
MessageBox.Show("Something went wrong");
Log.Error(exception);
}
}
return(data);
}
public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view)
{
var columns = new List <ViewColumnSchema>();
string sql = string.Format("SELECT * FROM GetViewColumns('{0}')", view.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row["COLUMN_NAME"].ToString();
string nativeType = row["DATA_TYPE_NAME"].ToString();
var size = (int)(row["COLUMN_SIZE"] ?? 0);
var allowNull = (bool)(row["ALLOW_NULL"] ?? true);
var column = new ViewColumnSchema(
view, name, GetDbType(nativeType),
nativeType, size, 0, 0, allowNull);
columns.Add(column);
}
}
return(columns.ToArray());
}
public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
var commands = new List <CommandSchema>();
var extendedProperties = new List <ExtendedProperty>();
const string sql = "SELECT PROC_NAME, PROC_DESCRIPTION FROM sp_stored_procedures()";
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row[0].ToString();
string description = row[1].ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
var command = new CommandSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());
commands.Add(command);
}
}
return(commands.ToArray());
}
override protected void HookupRowUpdateEvents(DbDataAdapter adapter)
{
// We only bother hooking up the event if we have an AutoKey
if (this.GetAutoKeyColumns().Length > 0)
{
VistaDBDataAdapter da = adapter as VistaDBDataAdapter;
da.RowUpdated += new VistaDBRowUpdatedEventHandler(OnRowUpdated);
}
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection:
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
VistaDBCommand command = (VistaDBCommand)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
VistaDBParameter parameter = new VistaDBParameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
VistaDBDataAdapter adapter = new VistaDBDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
public DataTable GetViewData(string connectionString, ViewSchema view)
{
string sql = string.Format("select * from '{0}'", view.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var dataTable = new DataTable())
{
adapter.Fill(dataTable);
return(dataTable);
}
}
static private esDataResponse LoadDataSetFromStoredProcedure(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataSet);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return(response);
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
VistaDBCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
}
scope.Complete();
}
return(request.Table);
}
public DbDataAdapter CreateAdapter()
{
try
{
DbDataAdapter dtAdapter = null;
dtAdapter = new VistaDBDataAdapter();
return(dtAdapter);
}
catch (Exception)
{
throw;
}
}
public override DbDataAdapter GetAdapter(string selectCommand, DbConnection connection,
CommandParameterCollection parameters)
{
VistaDBDataAdapter adapter = new VistaDBDataAdapter(selectCommand, connection as VistaDBConnection);
foreach (CommandParameter p in parameters)
{
VistaDBParameter parameter = adapter.SelectCommand.Parameters.Add(p.Name, (VistaDBType)p.DataType, p.Size);
object value = p.Value;
parameter.Value = value is Variant ? ((Variant)value).Value : value;
}
return(adapter);
}
static private esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return(response);
}
public string GetCommandText(string connectionString, CommandSchema command)
{
string sql = string.Format("SELECT PROC_BODY FROM sp_stored_procedures() WHERE PROC_NAME = '{0}'", command.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string body = row[0].ToString();
return(body);
}
}
return(null);
}
public DataSet ReadData(String sql)
{
VistaDBCommand cmd = new VistaDBCommand(sql, co);
DataSet data = new DataSet();
VistaDBDataAdapter adapter = new VistaDBDataAdapter(sql, co);
try
{
adapter.Fill(data);
_lastError = "";
}
catch (Exception e)
{
_lastError = e.Message;
}
return(data);
}
public string GetViewText(string connectionString, ViewSchema view)
{
string sql = string.Format("select VIEW_DEFINITION from GetViews() where VIEW_NAME = '{0}'", view.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string definition = row[0].ToString();
return(definition);
}
}
return(string.Empty);
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, VistaDBCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema command)
{
var parameters = new List <ParameterSchema>();
var extendedProperties = new List <ExtendedProperty>();
string sql = string.Format("SELECT PARAM_ORDER, PARAM_NAME, PARAM_TYPE, IS_PARAM_OUT, DEFAULT_VALUE FROM sp_stored_procedures() WHERE PROC_NAME = '{0}'", command.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row["PARAM_NAME"].ToString();
var vistaType = (VistaDBType)(row["PARAM_TYPE"] ?? VistaDBType.Unknown);
var isOut = (bool)(row["IS_PARAM_OUT"] ?? false);
string defaultValue = row["DEFAULT_VALUE"].ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, string.Empty));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.DefaultValue, defaultValue));
var parameter = new ParameterSchema(command, name,
isOut ? ParameterDirection.InputOutput : ParameterDirection.Input,
GetDbType(vistaType.ToString()),
vistaType.ToString(), 0, 0, 0, true,
extendedProperties.ToArray());
parameters.Add(parameter);
}
}
return(parameters.ToArray());
}
/// <summary>
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
/// </summary>
/// <remarks>
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">A valid transact-SQL statement to insert new records into the data source</param>
/// <param name="deleteCommand">A valid transact-SQL statement to delete records from the data source</param>
/// <param name="updateCommand">A valid transact-SQL statement used to update records in the data source</param>
/// <param name="dataSet">The DataSet used to update the data source</param>
/// <param name="tableName">The DataTable used to update the data source.</param>
public static void UpdateDataset(VistaDBCommand insertCommand, VistaDBCommand deleteCommand, VistaDBCommand updateCommand, DataSet dataSet, string tableName)
{
if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" );
if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" );
if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" );
if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" );
// Create a VistaDBDataAdapter, and dispose of it after we are done
VistaDBDataAdapter dataAdapter = new VistaDBDataAdapter();
try
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update (dataSet,tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
catch (VistaDBException E)
{string strError=E.Message;}
finally{dataAdapter.Dispose();}
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableForLinqToSql(esDataRequest request, esDataResponse response)
{
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = request.LinqContext.GetCommand(request.LinqQuery) as VistaDBCommand;
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadForLinqToSql", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
VistaDBCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
}
scope.Complete();
}
return request.Table;
}
DataTable IPlugin.GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection conn = new VistaDBConnection(context.ConnectionString))
{
string sql = "SELECT * FROM GetViewColumns('" + view + "')";
using (VistaDBCommand cmd = new VistaDBCommand(sql, conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach (DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
int width = Convert.ToInt32(vistaRow["COLUMN_SIZE"]);
int dec = 0;
int length = 0;
int octLength = width;
bool timestamp = false;
string type = vistaRow["DATA_TYPE_NAME"] as string;
switch (type)
{
case "Char":
case "NChar":
case "NText":
case "NVarchar":
case "Text":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
case "Decimal":
case "Single":
break;
case "Timestamp":
timestamp = true;
break;
default:
width = 0;
dec = 0;
break;
}
string def = Convert.ToString(vistaRow["DEFAULT_VALUE"]);
row["TABLE_NAME"] = view;
row["COLUMN_NAME"] = vistaRow["COLUMN_NAME"];
row["ORDINAL_POSITION"] = vistaRow["COLUMN_ORDINAL"];
row["IS_NULLABLE"] = vistaRow["ALLOW_NULL"];
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = vistaRow["IDENTITY_VALUE"];
row["AUTO_KEY_SEED"] = vistaRow["IDENTITY_SEED"];
row["AUTO_KEY_INCREMENT"] = vistaRow["IDENTITY_STEP"];
row["TYPE_NAME"] = type;
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = vistaRow["COLUMN_DESCRIPTION"];
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, length, (short)width, (short)dec);
if (timestamp)
{
row["IS_COMPUTED"] = true;
}
}
}
}
}
}
catch {}
return(metaData);
}
DataTable IMyMetaPlugin.GetViewColumns(string database, string view)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
string sql = "SELECT * FROM GetViewColumns('" + view + "')";
using (VistaDBCommand cmd = new VistaDBCommand(sql, conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach(DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
int width = Convert.ToInt32(vistaRow["COLUMN_SIZE"]);
int dec = 0;
int length = 0;
int octLength = width;
bool timestamp = false;
string type = vistaRow["DATA_TYPE_NAME"] as string;
switch(type)
{
case "Char":
case "NChar":
case "NText":
case "NVarchar":
case "Text":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
case "Decimal":
case "Single":
break;
case "Timestamp":
timestamp = true;
break;
default:
width = 0;
dec = 0;
break;
}
string def = Convert.ToString(vistaRow["DEFAULT_VALUE"]);
row["TABLE_NAME"] = view;
row["COLUMN_NAME"] = vistaRow["COLUMN_NAME"];
row["ORDINAL_POSITION"] = vistaRow["COLUMN_ORDINAL"];
row["IS_NULLABLE"] = vistaRow["ALLOW_NULL"];
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = vistaRow["IDENTITY_VALUE"];
row["AUTO_KEY_SEED"] = vistaRow["IDENTITY_SEED"];
row["AUTO_KEY_INCREMENT"] = vistaRow["IDENTITY_STEP"];
row["TYPE_NAME"] = type;
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = vistaRow["COLUMN_DESCRIPTION"];
if (timestamp)
{
row["IS_COMPUTED"] = true;
}
}
}
}
}
}
catch{}
return metaData;
}
static private DataTable SaveDynamicEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
VistaDBCommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket.ModifiedColumns);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket.ModifiedColumns);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, null);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
if (!needToDelete && request.Properties != null)
{
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = request.EntitySavePacket;
dataTable.ExtendedProperties["props"] = request.Properties;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new VistaDBRowUpdatedEventHandler(OnRowUpdated);
}
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
da.Update(singleRow);
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.EntitySavePacket.RowState != esDataRowState.Deleted && cmd.Parameters != null)
{
foreach (VistaDBParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
cmd.Dispose();
}
return(dataTable);
}
static private DataTable SaveDynamicCollection_InsertsUpdates(esDataRequest request)
{
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
VistaDBCommand cmd = null;
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new VistaDBRowUpdatedEventHandler(OnRowUpdated);
}
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
if (packet.RowState != esDataRowState.Added && packet.RowState != esDataRowState.Modified)
{
continue;
}
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
switch (packet.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, packet.ModifiedColumns);
SetModifiedValues(request, packet, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, packet.ModifiedColumns);
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
row.AcceptChanges();
row.SetModified();
break;
}
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = packet;
dataTable.ExtendedProperties["props"] = request.Properties;
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
dataTable.Rows.Clear();
}
if (!row.HasErrors && cmd.Parameters != null)
{
foreach (VistaDBParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
packet.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
cmd.Dispose();
}
}
scope.Complete();
}
return(dataTable);
}
private static esDataResponse LoadDataSetFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new VistaDBCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadDataSetFromText", System.Environment.StackTrace))
{
try
{
da.Fill(dataSet);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataSet);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
};
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
static private esDataResponse LoadDataSetFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new VistaDBCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataSet);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
};
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
static private esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid VistaDBTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(VistaDBTransaction transaction, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, (VistaDBConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
//using( VistaDBDataAdapter da = new VistaDBDataAdapter(cmd) )
VistaDBDataAdapter da = new VistaDBDataAdapter(cmd);
DataSet ds = new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
// Return the dataset
return ds;
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, VistaDBCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
static private DataTable SaveDynamicCollection_InsertsUpdates(esDataRequest request)
{
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
VistaDBCommand cmd = null;
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new VistaDBRowUpdatedEventHandler(OnRowUpdated);
}
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
if (packet.RowState != esDataRowState.Added && packet.RowState != esDataRowState.Modified) continue;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
switch (packet.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, packet.ModifiedColumns);
SetModifiedValues(request, packet, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, packet.ModifiedColumns);
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
row.AcceptChanges();
row.SetModified();
break;
}
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = packet;
dataTable.ExtendedProperties["props"] = request.Properties;
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
da.Update(singleRow);
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
dataTable.Rows.Clear();
}
if (!row.HasErrors && cmd.Parameters != null)
{
foreach (VistaDBParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
packet.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
cmd.Dispose();
}
}
scope.Complete();
}
return dataTable;
}
static private esDataResponse LoadManyToMany(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string mmQuery = request.QueryText;
string[] sections = mmQuery.Split('|');
string[] tables = sections[0].Split(',');
string[] columns = sections[1].Split(',');
// We build the query, we don't use Delimiters to avoid tons of extra concatentation
string sql = "SELECT * FROM [" + tables[0];
sql += "] JOIN [" + tables[1] + "] ON [" + tables[0] + "].[" + columns[0] + "] = [";
sql += tables[1] + "].[" + columns[1];
sql += "] WHERE [" + tables[1] + "].[" + sections[2] + "] = @";
if (request.Parameters != null)
{
foreach (esParameter esParam in request.Parameters)
{
sql += esParam.Name;
}
Shared.AddParameters(cmd, request);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return(response);
}
private static tgDataResponse LoadManyToMany(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string mmQuery = request.QueryText;
string[] sections = mmQuery.Split('|');
string[] tables = sections[0].Split(',');
string[] columns = sections[1].Split(',');
// We build the query, we don't use Delimiters to avoid tons of extra concatentation
string sql = "SELECT * FROM [" + tables[0];
sql += "] JOIN [" + tables[1] + "] ON [" + tables[0] + "].[" + columns[0] + "] = [";
sql += tables[1] + "].[" + columns[1];
sql += "] WHERE [" + tables[1] + "].[" + sections[2] + "] = @";
if (request.Parameters != null)
{
foreach (tgParameter esParam in request.Parameters)
{
sql += esParam.Name;
}
Shared.AddParameters(cmd, request);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
private static DataTable SaveDynamicCollection_Deletes(tgDataRequest request)
{
VistaDBCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (tgTransactionScope scope = new tgTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns);
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (tgEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
}
scope.Complete();
}
return request.Table;
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid VistaDBTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An XmlReader containing the resultset generated by the command</returns>
public static string ExecuteXml(VistaDBTransaction transaction, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// // Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, (VistaDBConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
VistaDBDataAdapter obj_Adapter =new VistaDBDataAdapter (cmd);
DataSet ds=new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
obj_Adapter.Fill(ds);
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
string retval= ds.GetXml();
ds.Clear();
obj_Adapter.Dispose ();
return retval;
}
private static DataTable SaveDynamicEntity(tgDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == tgDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
VistaDBCommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case tgDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket.ModifiedColumns);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case tgDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket.ModifiedColumns);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case tgDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, null);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
if (!needToDelete && request.Properties != null)
{
request.Properties["tgDataRequest"] = request;
request.Properties["esEntityData"] = request.EntitySavePacket;
dataTable.ExtendedProperties["props"] = request.Properties;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new VistaDBRowUpdatedEventHandler(OnRowUpdated);
}
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
}
if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null)
{
foreach (VistaDBParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
cmd.Dispose();
}
return dataTable;
}
DataTable IMyMetaPlugin.GetViews(string database)
{
DataTable metaData = new DataTable();
//IVistaDBDatabase db = null;
try
{
metaData = context.CreateViewsDataTable();
using (VistaDBConnection conn = new VistaDBConnection())
{
conn.ConnectionString = context.ConnectionString;
conn.Open();
using (VistaDBCommand cmd = new VistaDBCommand("SELECT * FROM GetViews()", conn))
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter(cmd))
{
DataTable views = new DataTable();
da.Fill(views);
foreach(DataRow vistaRow in views.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = vistaRow["VIEW_NAME"];
row["DESCRIPTION"] = vistaRow["DESCRIPTION"];
row["VIEW_TEXT"] = vistaRow["VIEW_DEFINITION"];
row["IS_UPDATABLE"] = vistaRow["IS_UPDATABLE"];
}
}
}
}
}
catch{}
return metaData;
}
// This is used only to execute the Dynamic Query API
private static void LoadDataTableFromDynamicQuery(tgDataRequest request, tgDataResponse response, VistaDBCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromDynamicQuery", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
/// <summary>
/// Execute a VistaDBCommand (that returns a resultset) against the specified VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
/// <returns>An string containing the resultset generated by the command</returns>
public static string ExecuteXml(VistaDBConnection connection, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
bool mustCloseConnection = false;
// Create a command and prepare it for execution
VistaDBCommand cmd = new VistaDBCommand();
try
{
PrepareCommand(cmd, connection, (VistaDBTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
VistaDBDataAdapter obj_Adapter =new VistaDBDataAdapter (cmd);
DataSet ds=new DataSet();
ds.Locale =CultureInfo.InvariantCulture;
obj_Adapter.Fill(ds);
// Detach the VistaDBParameters from the command object, so they can be used again
cmd.Parameters.Clear();
string retval= ds.GetXml();
ds.Clear();
obj_Adapter.Dispose ();
return retval;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}
private static tgDataResponse LoadDataTableFromStoredProcedure(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
VistaDBCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new VistaDBCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
/// <summary>
/// Private helper method that execute a VistaDBCommand (that returns a resultset) against the specified VistaDBTransaction and VistaDBConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new VistaDBParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid VistaDBConnection</param>
/// <param name="transaction">A valid VistaDBTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
private static void FillDataset(VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params VistaDBParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( dataSet == null ) throw new ArgumentNullException( "dataSet" );
// Create a command and prepare it for execution
VistaDBCommand command = new VistaDBCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
VistaDBDataAdapter dataAdapter = new VistaDBDataAdapter(command);
try
{
// Add the table mappings specified by the user
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index=0; index < tableNames.Length; index++)
{
if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" );
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// Fill the DataSet using default values for DataTable names, etc
dataAdapter.Fill(dataSet);
// Detach the VistaDBParameters from the command object, so they can be used again
command.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
}
finally
{
dataAdapter.Dispose();
}
}
private DataTable SelectToDataTable(string sql)
{
var dataTable = new DataTable();
using (var cn = ConnectionProvider.CreateConnection() as VistaDBConnection)
{
using (var adapter = new VistaDBDataAdapter(sql, cn))
{
adapter.Fill(dataTable);
}
}
return dataTable;
}
