public double Amount() { using (VistaDBConnection connection = Connection.Connexion) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT Amount FROM dbo.SchoolType WHERE Id={TypeId}"; var reader = command.ExecuteReader(); while (reader.Read()) { amount = reader.GetInt32(0); } connection.Close(); } } catch (VistaDBException exception) { MessageBox.Show(exception.Message); } } return(amount); }
static public VistaDBCommand BuildStoredProcDeleteCommand(tgDataRequest request) { VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose; Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request); VistaDBParameter p; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey) { p = types[col.Name]; p = CloneParameter(p); p.SourceVersion = DataRowVersion.Current; cmd.Parameters.Add(p); } } return(cmd); }
public School(string schoolName) { SchoolName = schoolName; using (VistaDBConnection connection = Connection.Connexion) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT Id,TypeId FROM dbo.School WHERE SchoolName='{schoolName}'"; var reader = command.ExecuteReader(); while (reader.Read()) { Id = reader.GetInt32(0); TypeId = reader.GetInt32(1); } connection.Close(); } } catch (VistaDBException exception) { MessageBox.Show(exception.Message); } } }
public double StandingOrder() { double Value = 0; using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString)) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT dbo.StandingOrder('{SchoolName}')"; var reader = command.ExecuteReader(); while (reader.Read()) { Value = reader.GetDouble(0); } connection.Close(); } } catch (VistaDBException exception) { MessageBox.Show("Something went wrong"); Log.Error(exception); } } return(Value); }
public int Role() { using (VistaDBConnection connection = Connection.Connexion) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT RoleId FROM dbo.Users WHERE Id={UserId}"; var reader = command.ExecuteReader(); while (reader.Read()) { RoleId = reader.GetInt32(0); } connection.Close(); } } catch (VistaDBException exception) { MessageBox.Show("Something went wrong"); Log.Error(exception); } } return(RoleId); }
public IEnumerable <long> GetQualified(long qualifier) { List <long> qualifiedRelations = new List <long>(); using (VistaDBConnection conn = new VistaDBConnection(this.db)) { VistaDBCommand cmd; // get qualified root relations cmd = new VistaDBCommand("select id from RootRelations where qualifierId=@qualifierId", conn); cmd.Parameters.AddWithValue("@qualifierId", qualifier); using (VistaDBDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { qualifiedRelations.Add(rd.GetInt64(0)); } } // get qualified inner relations cmd.CommandText = "select id from InnerRelations where qualifierId=@qualifierId"; using (VistaDBDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { qualifiedRelations.Add(rd.GetInt64(0)); } } } return(qualifiedRelations); }
protected static string GetFromStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; string sql = String.Empty; if (iQuery.InternalFromQuery == null) { sql = Shared.CreateFullName(query); if (iQuery.JoinAlias != " ") { sql += " " + iQuery.JoinAlias; } } else { IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal; iSubQuery.IsInSubQuery = true; sql += "("; sql += BuildQuery(request, iQuery.InternalFromQuery, cmd, ref pindex); sql += ")"; if (iSubQuery.SubQueryAlias != " ") { sql += " AS " + iSubQuery.SubQueryAlias; } iSubQuery.IsInSubQuery = false; } return sql; }
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 static DataTable executeReader(this API_VistaDB vistaDB, string command) { var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString); sqlConnection.Open(); try { var sqlCommand = new VistaDBCommand(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = command; sqlCommand.CommandType = CommandType.Text; var reader = sqlCommand.ExecuteReader(); var dataTable = new DataTable(); dataTable.Load(reader); return(dataTable); } catch (Exception ex) { vistaDB.LastError = ex.Message; "[executeNonQuery] {0}".error(ex.Message); //ex.log(); } finally { if (sqlConnection.notNull()) { sqlConnection.Close(); } } return(null); }
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 static object executeScalar(this API_VistaDB vistaDB, string command) { "[API_VistaDB] Executing Scalar: {0}".info(command); VistaDBConnection sqlConnection = null; try { sqlConnection = new VistaDBConnection(vistaDB.ConnectionString); sqlConnection.Open(); var sqlCommand = new VistaDBCommand(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = command; sqlCommand.CommandType = CommandType.Text; return(sqlCommand.ExecuteScalar()); } catch (Exception ex) { vistaDB.LastError = ex.Message; "[executeNonQuery] {0}".error(ex.Message); //ex.log(); } finally { sqlConnection.Close(); } return(null); }
public RecievePay(List <PaymentOrder> list) { using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString)) { try { foreach (var pay in list) { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"UPDATE dbo.PaymentOrder SET Status = 'Recieved',RecievedBy ={LoginUser.UserId},RecievedDate = GETDATE() WHERE Id = {pay.Id}"; command.ExecuteNonQuery(); connection.Close(); } } MessageBox.Show(@"Selected payments confirmed successfully"); } catch (VistaDBException exception) { Log.Error(exception); } } }
protected override IDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters) { VistaDBCommand sqlCommand = (VistaDBCommand)Connection.CreateCommand(); sqlCommand.Transaction = (VistaDBTransaction)CurrentTransaction; if (sqlQuery.StartsWith("!")) { sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = sqlQuery.Substring(1); } else { sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = sqlQuery; } if (parameters != null && !parameters.IsEmpty) { foreach (CSParameter csParameter in parameters) { IDbDataParameter dataParameter = sqlCommand.CreateParameter(); dataParameter.ParameterName = csParameter.Name; dataParameter.Direction = ParameterDirection.Input; dataParameter.Value = ConvertParameter(csParameter.Value); sqlCommand.Parameters.Add(dataParameter); } } return(sqlCommand); }
override internal IDbCommand _LoadFromRawSql(string rawSql, params object[] parameters) { int i = 0; string token = ""; string sIndex = ""; string param = ""; VistaDBCommand cmd = new VistaDBCommand(); foreach (object o in parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = "@p" + sIndex; rawSql = rawSql.Replace(token, param); VistaDBParameter p = new VistaDBParameter(param, o); cmd.Parameters.Add(p); i++; } cmd.CommandText = rawSql; return(cmd); }
public double Recieved() { double Value = 0; using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString)) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT dbo.Recieved('{SchoolName}')"; var reader = command.ExecuteReader(); while (reader.Read()) { Value = reader.GetDouble(0); } connection.Close(); } } catch (VistaDBException exception) { MessageBox.Show(exception.Message); } } recieved = Value; return(recieved); }
static public VistaDBCommand BuildStoredProcUpdateCommand(tgDataRequest request) { VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose; PopulateStoredProcParameters(cmd, request); foreach (tgColumnMetadata col in request.Columns) { if (col.IsComputed) { VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName]; p.Direction = ParameterDirection.InputOutput; } } return(cmd); }
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); }
static public VistaDBCommand BuildStoredProcInsertCommand(tgDataRequest request) { VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spInsert + Delimiters.StoredProcNameClose; PopulateStoredProcParameters(cmd, request); foreach (tgColumnMetadata col in request.Columns) { if (col.HasDefault && col.Default.ToLower() == "GUID()") { VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName]; p.Direction = ParameterDirection.InputOutput; } else if (col.IsComputed || col.IsAutoIncrement) { VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName]; p.Direction = ParameterDirection.Output; } } return(cmd); }
public static void Test() { FileInfo fi = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "\\Cases\\InitialProject.cs"); VistaDBConnection conn = new VistaDBConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\App_Data\\FmqStore.vdb3"); conn.Open(); string sql = "insert into [FileSystem](FileName,FileDir,FileSize,HashCode,BinData,FileVersion,CreateDate,LastChangeDate) values(" + "@FileName,@FileDir,@FileSize,@HashCode,@BinData,1, @CreateDate, @LastChangeDate" + ")"; VistaDBCommand cmd = new VistaDBCommand(sql, conn); cmd.Parameters.AddWithValue("@FileName", fi.Name); cmd.Parameters.AddWithValue("@FileDir", "/cases"); cmd.Parameters.AddWithValue("@FileSize", fi.Length); byte[] fBin = GetFileBytes(fi.FullName); cmd.Parameters.AddWithValue("@HashCode", GetMD5Hash(fBin)); cmd.Parameters.AddWithValue("@BinData", fBin); cmd.Parameters.AddWithValue("@CreateDate", fi.CreationTimeUtc); cmd.Parameters.AddWithValue("@LastChangeDate", fi.LastWriteTimeUtc); cmd.ExecuteNonQuery(); conn.Close(); conn.Dispose(); }
static public VistaDBCommand BuildDynamicDeleteCommand(tgDataRequest request, List <string> modifiedColumns) { Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request); VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } string sql = "DELETE FROM " + CreateFullName(request) + " "; string comma = String.Empty; comma = String.Empty; sql += " WHERE "; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey || col.IsTiraggoConcurrency || col.IsConcurrency) { VistaDBParameter p = types[col.Name]; cmd.Parameters.Add(CloneParameter(p)); sql += comma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; comma = " AND "; } } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
/// <summary> /// Call the export schema and data sql function to write out the xml file /// </summary> /// <param name="outputFilename">Name of the file to write to disk</param> public static void CallExportSchemaAndDataSQL(string outputFilename) { Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData"); using (VistaDBConnection connection = new VistaDBConnection()) { connection.ConnectionString = SampleRunner.ConnectionString; connection.Open(); try { using (VistaDBCommand command = new VistaDBCommand()) { // Straight forward way to call a function is just using SELECT // You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc // Setting this command to a stored proc is a common error, the two are not the same // SqlFunction = SELECT to call // SqlProcdure = EXEC or direct call using StoredProcedure command type command.Connection = connection; command.CommandText = string.Format("SELECT ExportSchemaAndData('{0}');", outputFilename); // This command does not return anything in the rowset, so just execute non query command.ExecuteNonQuery(); } Console.WriteLine(string.Format("Schema and Data export to {0}\\{1}.xml", Directory.GetCurrentDirectory(), outputFilename)); } catch (Exception e) { Console.WriteLine("Failed to execute CLR-Proc ExportSchemaAndData, Reason: " + e.Message); } } }
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column protected void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e) { try { if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) { TransactionMgr txMgr = TransactionMgr.ThreadTransactionMgr(); string[] identityCols = this.GetAutoKeyColumns().Split(';'); VistaDBCommand cmd = new VistaDBCommand(); foreach (string col in identityCols) { cmd.CommandText = "SELECT LastIdentity([" + col + "]) FROM [" + this.QuerySource + "]"; // We make sure we enlist in the ongoing transaction, otherwise, we // would most likely deadlock txMgr.Enlist(cmd, this); object o = cmd.ExecuteScalar(); // Get the Identity Value txMgr.DeEnlist(cmd, this); if (o != null) { e.Row[col] = o; } } e.Row.AcceptChanges(); } } catch {} }
public static List <string> Names() { List <string> names = new List <string>(); using (VistaDBConnection connection = Connection.Connexion) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT Name FROM dbo.Users"; var reader = command.ExecuteReader(); while (reader.Read()) { names.Add(reader.GetString(0)); } connection.Close(); if (names.Count < 1) { names.Add("No Data Available"); } } } catch (VistaDBException exception) { MessageBox.Show("Something went wrong"); Log.Error(exception); } } return(names); }
public int FindId(User user) { int getId = 0; using (VistaDBConnection connection = new VistaDBConnection(new Connection().ConnectionString)) { try { connection.Open(); using (VistaDBCommand command = new VistaDBCommand()) { command.Connection = connection; command.CommandText = $"SELECT Id FROM dbo.Users where Name='{user.Name}'"; var reader = command.ExecuteReader(); while (reader.Read()) { getId = reader.GetInt32(0); } connection.Close(); } } catch (VistaDBException exception) { MessageBox.Show(exception.Message); } } return(getId); }
protected override IDbCommand GetInsertCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"INSERT INTO [AggregateTest] ( [DepartmentID], [FirstName], [LastName], [Age], [HireDate], [Salary], [IsActive] ) VALUES ( @DepartmentID, @FirstName, @LastName, @Age, @HireDate, @Salary, @IsActive )" ; CreateParameters(cmd); return(cmd); }
protected override IDbCommand GetUpdateCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"UPDATE [Employees] SET [LastName]=@LastName, [FirstName]=@FirstName, [Title]=@Title, [TitleOfCourtesy]=@TitleOfCourtesy, [BirthDate]=@BirthDate, [HireDate]=@HireDate, [Address]=@Address, [City]=@City, [Region]=@Region, [PostalCode]=@PostalCode, [Country]=@Country, [HomePhone]=@HomePhone, [Extension]=@Extension, [Photo]=@Photo, [Notes]=@Notes, [ReportsTo]=@ReportsTo WHERE [EmployeeID]=@EmployeeID" ; CreateParameters(cmd); return(cmd); }
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request) { esDataResponse response = new esDataResponse(); VistaDBCommand cmd = null; try { cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } if (request.Parameters != null) { Shared.AddParameters(cmd, request); } switch (request.QueryType) { case esQueryType.TableDirect: cmd.CommandType = CommandType.TableDirect; cmd.CommandText = request.QueryText; break; case esQueryType.StoredProcedure: cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Shared.CreateFullName(request); break; case esQueryType.Text: cmd.CommandType = CommandType.Text; cmd.CommandText = request.QueryText; break; } try { esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); response.RowsEffected = cmd.ExecuteNonQuery(); } finally { esTransactionScope.DeEnlist(cmd); } if (request.Parameters != null) { Shared.GatherReturnParameters(cmd, request, response); } } catch (Exception ex) { CleanupCommand(cmd); response.Exception = ex; } return(response); }
static private void CleanupCommand(VistaDBCommand cmd) { if (cmd != null && cmd.Connection != null) { if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } } }
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"); } } } }
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 static VistaDBCommand PrepareCommand(esDataRequest request) { esDynamicQuerySerializable query = request.DynamicQuery; VistaDBCommand cmd = new VistaDBCommand(); int pindex = NextParamIndex(cmd); string sql = BuildQuery(request, query, cmd, ref pindex); cmd.CommandText = sql; return cmd; }
public static void AddParameters(VistaDBCommand cmd, tgDataRequest request) { if (request.QueryType == tgQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}")) { int i = 0; string token = String.Empty; string sIndex = String.Empty; string param = String.Empty; foreach (tgParameter esParam in request.Parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = Delimiters.Param + "p" + sIndex; request.QueryText = request.QueryText.Replace(token, param); i++; cmd.Parameters.Add(Delimiters.Param + esParam.Name, esParam.Value); } } else { VistaDBParameter param; foreach (tgParameter esParam in request.Parameters) { param = cmd.Parameters.Add(Delimiters.Param + esParam.Name, esParam.Value); switch (esParam.Direction) { case tgParameterDirection.InputOutput: param.Direction = ParameterDirection.InputOutput; break; case tgParameterDirection.Output: param.Direction = ParameterDirection.Output; param.DbType = esParam.DbType; param.Size = esParam.Size; break; case tgParameterDirection.ReturnValue: param.Direction = ParameterDirection.ReturnValue; break; // The default is ParameterDirection.Input; } } } }
/// <summary> /// Simplify the creation of a VistaDB command object by allowing /// a CommandType and Command Text to be provided /// </summary> /// <remarks> /// e.g.: /// VistaDBCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers"); /// </remarks> /// <param name="connection">A valid VistaDBConnection object</param> /// <param name="commandType">CommandType (TableDirect, Text)</param> /// <param name="commandText">CommandText</param> /// <returns>A valid VistaDBCommand object</returns> public static VistaDBCommand CreateCommand(VistaDBConnection connection, CommandType commandType, string commandText ) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported."); // If we receive parameter values, we need to figure out where they go if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" ); // Create a VistaDBCommand VistaDBCommand cmd = new VistaDBCommand(commandText, connection ); cmd.CommandType = CommandType.Text ; return cmd; }
protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; string select = GetSelectStatement(request, query, cmd, ref pindex); string from = GetFromStatement(request, query, cmd, ref pindex); string join = GetJoinStatement(request, query, cmd, ref pindex); string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex); string groupBy = GetGroupByStatement(request, query, cmd, ref pindex); string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex); string orderBy = GetOrderByStatement(request, query, cmd, ref pindex); string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex); string sql = String.Empty; sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy; return sql; }
protected override IDbCommand GetInsertCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"INSERT INTO [Employees] ( [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Photo], [Notes], [ReportsTo] ) VALUES ( @LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City, @Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo )"; CreateParameters(cmd); return cmd; }
// 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 { } }
private static void CleanupCommand(VistaDBCommand cmd) { if (cmd != null && cmd.Connection != null) { if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } } }
private IDbCommand CreateParameters(VistaDBCommand cmd) { VistaDBParameter p; p = cmd.Parameters.Add(Parameters.ID); p.SourceColumn = ColumnNames.ID; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.DepartmentID); p.SourceColumn = ColumnNames.DepartmentID; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.FirstName); p.SourceColumn = ColumnNames.FirstName; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.LastName); p.SourceColumn = ColumnNames.LastName; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Age); p.SourceColumn = ColumnNames.Age; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.HireDate); p.SourceColumn = ColumnNames.HireDate; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Salary); p.SourceColumn = ColumnNames.Salary; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.IsActive); p.SourceColumn = ColumnNames.IsActive; p.SourceVersion = DataRowVersion.Current; return cmd; }
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; }
override protected IDbCommand _Load(string conjuction) { bool hasColumn = false; bool selectAll = true; string query; query = "SELECT "; if( this._distinct) query += " DISTINCT "; if( this._top >= 0) query += " TOP " + this._top.ToString() + " "; if(this._resultColumns.Length > 0) { query += this._resultColumns; hasColumn = true; selectAll = false; } if(this._countAll) { if(hasColumn) { query += ", "; } query += "COUNT(*)"; if(this._countAllAlias != string.Empty) { // Need DBMS string delimiter here query += " AS [" + this._countAllAlias + "]"; } hasColumn = true; selectAll = false; } if(_aggregateParameters != null && _aggregateParameters.Count > 0) { bool isFirst = true; if(hasColumn) { query += ", "; } AggregateParameter wItem; foreach(object obj in _aggregateParameters) { wItem = obj as AggregateParameter; if(wItem.IsDirty) { if(isFirst) { query += GetAggregate(wItem, true); isFirst = false; } else { query += ", " + GetAggregate(wItem, true); } } } selectAll = false; } if(selectAll) { query += "*"; } query += " FROM [" + this._entity.QuerySource + "]"; VistaDBCommand cmd = new VistaDBCommand(); if(_whereParameters != null && _whereParameters.Count > 0) { query += " WHERE "; bool first = true; bool requiresParam; WhereParameter wItem; bool skipConjuction = false; string paramName; string columnName; foreach(object obj in _whereParameters) { // Maybe we injected text or a WhereParameter if(obj.GetType().ToString() == "System.String") { string text = obj as string; query += text; if(text == "(") { skipConjuction = true; } } else { wItem = obj as WhereParameter; if(wItem.IsDirty) { if(!first && !skipConjuction) { if(wItem.Conjuction != WhereParameter.Conj.UseDefault) { if(wItem.Conjuction == WhereParameter.Conj.And) query += " AND "; else query += " OR "; } else { query += " " + conjuction + " "; } } requiresParam = true; columnName = "[" + wItem.Column + "]"; paramName = "@" + wItem.Column + (++inc).ToString(); wItem.Param.ParameterName = paramName; switch(wItem.Operator) { case WhereParameter.Operand.Equal: query += columnName + " = " + paramName + " "; break; case WhereParameter.Operand.NotEqual: query += columnName + " <> " + paramName + " "; break; case WhereParameter.Operand.GreaterThan: query += columnName + " > " + paramName + " "; break; case WhereParameter.Operand.LessThan: query += columnName + " < " + paramName + " "; break; case WhereParameter.Operand.LessThanOrEqual: query += columnName + " <= " + paramName + " "; break; case WhereParameter.Operand.GreaterThanOrEqual: query += columnName + " >= " + paramName + " "; break; case WhereParameter.Operand.Like: query += columnName + " LIKE " + paramName + " "; break; case WhereParameter.Operand.NotLike: query += columnName + " NOT LIKE " + paramName + " "; break; case WhereParameter.Operand.IsNull: query += columnName + " IS NULL "; requiresParam = false; break; case WhereParameter.Operand.IsNotNull: query += columnName + " IS NOT NULL "; requiresParam = false; break; case WhereParameter.Operand.In: query += columnName + " IN (" + wItem.Value + ") "; requiresParam = false; break; case WhereParameter.Operand.NotIn: query += columnName + " NOT IN (" + wItem.Value + ") "; requiresParam = false; break; case WhereParameter.Operand.Between: query += columnName + " BETWEEN " + paramName; cmd.Parameters.Add(paramName, wItem.BetweenBeginValue); paramName = "@" + wItem.Column + (++inc).ToString(); query += " AND " + paramName; cmd.Parameters.Add(paramName, wItem.BetweenEndValue); requiresParam = false; break; } if(requiresParam) { IDbCommand dbCmd = cmd as IDbCommand; dbCmd.Parameters.Add(wItem.Param); wItem.Param.Value = wItem.Value; } first = false; skipConjuction = false; } } } } if(_groupBy.Length > 0) { query += " GROUP BY " + _groupBy; if(this._withRollup) { query += " WITH ROLLUP"; } } if(_orderBy.Length > 0) { query += " ORDER BY " + _orderBy; } cmd.CommandText = query; return cmd; }
protected override IDbCommand GetUpdateCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"UPDATE [AggregateTest] SET [DepartmentID]=@DepartmentID, [FirstName]=@FirstName, [LastName]=@LastName, [Age]=@Age, [HireDate]=@HireDate, [Salary]=@Salary, [IsActive]=@IsActive WHERE [ID]=@ID"; CreateParameters(cmd); return cmd; }
private IDbCommand CreateParameters(VistaDBCommand cmd) { VistaDBParameter p; p = cmd.Parameters.Add(Parameters.EmployeeID); p.SourceColumn = ColumnNames.EmployeeID; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.LastName); p.SourceColumn = ColumnNames.LastName; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.FirstName); p.SourceColumn = ColumnNames.FirstName; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Title); p.SourceColumn = ColumnNames.Title; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.TitleOfCourtesy); p.SourceColumn = ColumnNames.TitleOfCourtesy; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.BirthDate); p.SourceColumn = ColumnNames.BirthDate; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.HireDate); p.SourceColumn = ColumnNames.HireDate; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Address); p.SourceColumn = ColumnNames.Address; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.City); p.SourceColumn = ColumnNames.City; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Region); p.SourceColumn = ColumnNames.Region; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.PostalCode); p.SourceColumn = ColumnNames.PostalCode; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Country); p.SourceColumn = ColumnNames.Country; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.HomePhone); p.SourceColumn = ColumnNames.HomePhone; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Extension); p.SourceColumn = ColumnNames.Extension; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Photo); p.SourceColumn = ColumnNames.Photo; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.Notes); p.SourceColumn = ColumnNames.Notes; p.SourceVersion = DataRowVersion.Current; p = cmd.Parameters.Add(Parameters.ReportsTo); p.SourceColumn = ColumnNames.ReportsTo; p.SourceVersion = DataRowVersion.Current; return cmd; }
tgDataResponse IDataProvider.ExecuteScalar(tgDataRequest request) { tgDataResponse response = new tgDataResponse(); VistaDBCommand cmd = null; try { cmd = new VistaDBCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; if (request.Parameters != null) Shared.AddParameters(cmd, request); switch (request.QueryType) { case tgQueryType.TableDirect: cmd.CommandType = CommandType.TableDirect; cmd.CommandText = request.QueryText; break; case tgQueryType.StoredProcedure: cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Shared.CreateFullName(request); break; case tgQueryType.Text: cmd.CommandType = CommandType.Text; cmd.CommandText = request.QueryText; break; case tgQueryType.DynamicQuery: cmd = QueryBuilder.PrepareCommand(request); break; } try { tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteScalar", System.Environment.StackTrace)) { try { response.Scalar = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { response.Scalar = cmd.ExecuteScalar(); } } finally { tgTransactionScope.DeEnlist(cmd); } if (request.Parameters != null) { Shared.GatherReturnParameters(cmd, request, response); } } catch (Exception ex) { CleanupCommand(cmd); response.Exception = ex; } return response; }
protected override IDbCommand GetUpdateCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"UPDATE [Employees] SET [LastName]=@LastName, [FirstName]=@FirstName, [Title]=@Title, [TitleOfCourtesy]=@TitleOfCourtesy, [BirthDate]=@BirthDate, [HireDate]=@HireDate, [Address]=@Address, [City]=@City, [Region]=@Region, [PostalCode]=@PostalCode, [Country]=@Country, [HomePhone]=@HomePhone, [Extension]=@Extension, [Photo]=@Photo, [Notes]=@Notes, [ReportsTo]=@ReportsTo WHERE [EmployeeID]=@EmployeeID"; CreateParameters(cmd); return cmd; }
protected static string GetOrderByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalOrderByItems != null) { sql += " ORDER BY "; foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems) { bool literal = false; sql += comma; string columnName = orderByItem.Expression.Column.Name; if (columnName != null && columnName[0] == '<') { sql += columnName.Substring(1, columnName.Length - 2); if (orderByItem.Direction == esOrderByDirection.Unassigned) { literal = true; // They must provide the DESC/ASC in the literal string } } else { sql += GetExpressionColumn(orderByItem.Expression, false, false); } if (!literal) { if (orderByItem.Direction == esOrderByDirection.Ascending) sql += " ASC"; else sql += " DESC"; } comma = ","; } } return sql; }
protected static string GetSelectStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { string sql = String.Empty; string comma = String.Empty; bool selectAll = true; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (query.es.Distinct) sql += " DISTINCT "; if (query.es.Top >= 0) sql += " TOP " + query.es.Top.ToString() + " "; if (iQuery.InternalSelectColumns != null) { selectAll = false; foreach (esExpression expressionItem in iQuery.InternalSelectColumns) { if (expressionItem.Query != null) { IDynamicQuerySerializableInternal iSubQuery = expressionItem.Query as IDynamicQuerySerializableInternal; sql += comma; if (iSubQuery.SubQueryAlias == string.Empty) { sql += iSubQuery.JoinAlias + ".*"; } else { iSubQuery.IsInSubQuery = true; sql += " (" + BuildQuery(request, expressionItem.Query as esDynamicQuerySerializable, cmd, ref pindex) + ") AS " + iSubQuery.SubQueryAlias; iSubQuery.IsInSubQuery = false; } comma = ","; } else { sql += comma; string columnName = expressionItem.Column.Name; if (columnName != null && columnName[0] == '<') sql += columnName.Substring(1, columnName.Length - 2); else sql += GetExpressionColumn(expressionItem, false, true); comma = ","; } } sql += " "; } if (query.es.CountAll) { selectAll = false; sql += comma; sql += "COUNT(*)"; if (query.es.CountAllAlias != null) { // Need DBMS string delimiter here sql += " AS " + Delimiters.StringOpen + query.es.CountAllAlias + Delimiters.StringClose; } } if (selectAll) { sql += "*"; } return sql; }
protected static string GetComparisonStatement(esDataRequest request, esDynamicQuerySerializable query, List<esComparison> items, string prefix, VistaDBCommand cmd, ref int pindex) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; string compareTo = String.Empty; foreach (esComparison comparisonItem in items) { esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) sql += "("; else sql += ")"; continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary<string, VistaDBParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { if (comparisonData.Column.Name[0] == '<') { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); } else { sql += comparisonData.Column.Name; } continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as esDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(request, subQuery, cmd, ref pindex) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " = " + compareTo; else sql += compareTo + " = " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " <> " + compareTo; else sql += compareTo + " <> " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " > " + compareTo; else sql += compareTo + " > " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " < " + compareTo; else sql += compareTo + " < " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " <= " + compareTo; else sql += compareTo + " <= " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(comparisonData) + " >= " + compareTo; else sql += compareTo + " >= " + ApplyWhereSubOperations(comparisonData); break; case esComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.In) { sql += ApplyWhereSubOperations(comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(comparisonData) + " NOT IN ("; } foreach (object oin in comparisonData.Values) { string str = oin as string; if (str != null) { // STRING sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose; comma = ","; } else if (null != oin as System.Collections.IEnumerable) { // LIST OR COLLECTION OF SOME SORT System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable; if (enumer != null) { System.Collections.IEnumerator iter = enumer.GetEnumerator(); while (iter.MoveNext()) { object o = iter.Current; string soin = o as string; if (soin != null) sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose; else sql += comma + Convert.ToString(o); comma = ","; } } } else { // NON STRING OR LIST sql += comma + Convert.ToString(oin); comma = ","; } } sql += ")"; requiresParam = false; } } break; case esComparisonOperand.Between: sql += ApplyWhereSubOperations(comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { cmd.Parameters.Add(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString(); sql += " AND " + compareTo; cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { VistaDBParameter p; if (comparisonData.Column.Name != null) { p = types[comparisonData.Column.Name]; p = Cache.CloneParameter(p); p.ParameterName = compareTo; p.Value = comparisonData.Value; if (needsStringParameter) { p.DbType = DbType.String; } } else { p = new VistaDBParameter(compareTo, comparisonData.Value); } cmd.Parameters.Add(p); } } } return sql; }
protected static string GetJoinStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { string sql = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalJoinItems != null) { foreach (esJoinItem joinItem in iQuery.InternalJoinItems) { esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem; switch (joinData.JoinType) { case esJoinType.InnerJoin: sql += " INNER JOIN "; break; case esJoinType.LeftJoin: sql += " LEFT JOIN "; break; case esJoinType.RightJoin: sql += " RIGHT JOIN "; break; case esJoinType.FullJoin: sql += " FULL JOIN "; break; } IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal; sql += Shared.CreateFullName((esProviderSpecificMetadata)iSubQuery.ProviderMetadata); sql += " " + iSubQuery.JoinAlias + " ON "; sql += GetComparisonStatement(request, query, joinData.WhereItems, String.Empty, cmd, ref pindex); } } return sql; }
protected override IDbCommand GetDeleteCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"DELETE FROM [Employees] WHERE [EmployeeID]=@EmployeeID"; VistaDBParameter p; p = cmd.Parameters.Add(Parameters.EmployeeID); p.SourceColumn = ColumnNames.EmployeeID; p.SourceVersion = DataRowVersion.Current; return cmd; }
protected static string GetGroupByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalGroupByItems != null) { sql += " GROUP BY "; foreach (esGroupByItem groupBy in iQuery.InternalGroupByItems) { sql += comma; string columnName = groupBy.Expression.Column.Name; if (columnName != null && columnName[0] == '<') sql += columnName.Substring(1, columnName.Length - 2); else sql += GetExpressionColumn(groupBy.Expression, false, false); comma = ","; } if (query.es.WithRollup) { sql += " WITH ROLLUP"; } } return sql; }
protected static void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e) { try { PropertyCollection props = e.Row.Table.ExtendedProperties; if (props.ContainsKey("props")) { props = (PropertyCollection)props["props"]; } if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update)) { tgDataRequest request = props["tgDataRequest"] as tgDataRequest; tgEntitySavePacket packet = (tgEntitySavePacket)props["esEntityData"]; string source = props["Source"] as string; if (e.StatementType == StatementType.Insert) { if (props.Contains("AutoInc")) { string autoInc = props["AutoInc"] as string; VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]"; object o = null; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { o = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { o = cmd.ExecuteScalar(); } if (o != null) { e.Row[autoInc] = o; e.Command.Parameters["@" + autoInc].Value = o; } } if (props.Contains("EntitySpacesConcurrency")) { string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string; packet.CurrentValues[esConcurrencyColumn] = 1; } } if (props.Contains("Timestamp")) { string column = props["Timestamp"] as string; VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; cmd.CommandText = "SELECT LastTimestamp('" + source + "');"; object o = null; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { o = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { o = cmd.ExecuteScalar(); } if (o != null) { e.Command.Parameters["@" + column].Value = o; } } //------------------------------------------------------------------------------------------------- // Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way //------------------------------------------------------------------------------------------------- if (props.Contains("Defaults")) { // Build the Where parameter and parameters VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; string select = (string)props["Defaults"]; string[] whereParameters = ((string)props["Where"]).Split(','); string comma = String.Empty; string where = String.Empty; int i = 1; foreach (string parameter in whereParameters) { VistaDBParameter p = new VistaDBParameter("@p" + i++.ToString(), e.Row[parameter]); cmd.Parameters.Add(p); where += comma + "[" + parameter + "]=" + p.ParameterName; comma = " AND "; } // Okay, now we can execute the sql and get any values that have defaults that were // null at the time of the insert and/or our timestamp cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";"; VistaDBDataReader rdr = null; try { #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); } if (rdr.Read()) { select = select.Replace("[", String.Empty).Replace("]", String.Empty); string[] selectCols = select.Split(','); for (int k = 0; k < selectCols.Length; k++) { packet.CurrentValues[selectCols[k]] = rdr.GetValue(k); } } } finally { // Make sure we close the reader no matter what if (rdr != null) rdr.Close(); } } if (e.StatementType == StatementType.Update) { string colName = props["EntitySpacesConcurrency"] as string; object o = e.Row[colName]; VistaDBParameter p = e.Command.Parameters["@" + colName]; object v = null; switch (Type.GetTypeCode(o.GetType())) { case TypeCode.Int16: v = ((System.Int16)o) + 1; break; case TypeCode.Int32: v = ((System.Int32)o) + 1; break; case TypeCode.Int64: v = ((System.Int64)o) + 1; break; case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break; case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break; case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break; } p.Value = v; } } } catch { } }
protected static string GetSetOperationStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex) { string sql = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; if (iQuery.InternalSetOperations != null) { foreach (esSetOperation setOperation in iQuery.InternalSetOperations) { switch (setOperation.SetOperationType) { case esSetOperationType.Union: sql += " UNION "; break; case esSetOperationType.UnionAll: sql += " UNION ALL "; break; case esSetOperationType.Intersect: sql += " INTERSECT "; break; case esSetOperationType.Except: sql += " EXCEPT "; break; } sql += BuildQuery(request, setOperation.Query, cmd, ref pindex); } } return sql; }
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 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; }
internal override IDbCommand _LoadFromRawSql(string rawSql, params object[] parameters) { int i = 0; string token = ""; string sIndex = ""; string param = ""; VistaDBCommand cmd = new VistaDBCommand(); foreach(object o in parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = "@p" + sIndex; rawSql = rawSql.Replace(token, param); VistaDBParameter p = new VistaDBParameter(param, o); cmd.Parameters.Add(p); i++; } cmd.CommandText = rawSql; return cmd; }
private static int NextParamIndex(VistaDBCommand cmd) { return cmd.Parameters.Count; }
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; }
protected override IDbCommand GetInsertCommand() { VistaDBCommand cmd = new VistaDBCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"INSERT INTO [AggregateTest] ( [DepartmentID], [FirstName], [LastName], [Age], [HireDate], [Salary], [IsActive] ) VALUES ( @DepartmentID, @FirstName, @LastName, @Age, @HireDate, @Salary, @IsActive )"; CreateParameters(cmd); return cmd; }