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; }