internal void ExecuteQuery(SQLQuery pQuery) { lock (mLock) { ExecuteQueryThreadSafe(pQuery); } }
public SQLQuery BuildQueryToRemoveChangesForTable(string schemaName, string tableName) { string sql = "delete from " + tableSet.FullyQualifiedSyncTableName + " where `schema` = ?schema AND `table` = ?table;"; var query = new SQLQuery(sql); query.Parameters.Add("schema", schemaName); query.Parameters.Add("table", tableName); return query; }
private void AddColumnParametersToQuery(SQLQuery query, IEnumerable<ColumnInfo> columnInfos, List<Tuple<string, object>> data) { foreach (var columnInfo in columnInfos) { var tuple = data.First(t => t.Item1 == columnInfo.ColumnName); query.Parameters.Add(columnInfo.ColumnName, tuple.Item2); } }
/// <summary> /// Execute() /// </summary> /// <param name='context'>The context for the test, this holds state that is passed beteen tests</param> public override void Execute(Context context) { context.LogInfo("Using database connection string: {0}", ConnectionString); string sqlQueryToExecute = SQLQuery.GetFormattedSqlQuery(context); // Sleep for delay seconds... if (0 < DelayBeforeCheck) { context.LogInfo("Sleeping for: {0} seconds", DelayBeforeCheck); System.Threading.Thread.Sleep(DelayBeforeCheck * 1000); } context.LogInfo("Executing database query: {0}", sqlQueryToExecute); DataSet ds = FillDataSet(ConnectionString, sqlQueryToExecute); if (NumberOfRowsExpected != ds.Tables[0].Rows.Count) { throw new ApplicationException(string.Format("Number of rows expected to be returned by the query does not match the value specified in the teststep. Number of rows the NnumberOfRowsExpected were: {0}, actual: {1}", NumberOfRowsExpected, ds.Tables[0].Rows.Count)); } context.LogInfo("NumberOfRowsExpected: {0}, actual number returned: {1}", NumberOfRowsExpected, ds.Tables[0].Rows.Count); if (0 == NumberOfRowsExpected) { return; } if (0 < DbRowsToValidate.Count) { int rowCount = 0; foreach (var dbRowToValidate in DbRowsToValidate) { context.LogInfo("Validating row number: {0}", rowCount); var resultRow = ds.Tables[0].Rows[rowCount];; var cells = dbRowToValidate.Cells; foreach (DbCellToValidate cell in cells) { object dbData = resultRow[cell.ColumnName]; var dbDataStringValue = string.Empty; if (0 == ValidateData(dbData, cell.ExpectedValue, ref dbDataStringValue)) { context.LogInfo("Validation succeeded for field: {0}. Expected value: {1}", cell.ColumnName, dbDataStringValue); } else { throw new Exception(String.Format("Validation failed for field: {0}. Expected value: {1}, actual value: {2}", cell.ColumnName, cell.ExpectedValue, dbDataStringValue)); } } rowCount++; } } }
/// <summary> /// Creates and initializes a new instance of the QueryTransformer object. /// </summary> /// <param name="query">SQL Query to transform.</param> /// <returns>Returns instance of the QueryTransformer object.</returns> private QueryTransformer CreateQueryTransformer(SQLQuery query) { var qt = QueryTransformerStore.Create(instanceId); qt.QueryProvider = query; qt.AlwaysExpandColumnsInQuery = true; return(qt); }
private void button2_Click(object sender, EventArgs e) { for (int i = 0; i < dataGridView1.SelectedRows.Count; i++) { dataGridView1.Rows.RemoveAt(this.dataGridView1.SelectedRows[i].Index); } SQLQuery.UpdateTable("groups", dataGridView1); dataGridView1.DataSource = SQLQuery.ListGroups(); dataGridView1.Update(); }
private void QBuilder_OnLinkCreated(SQLQuery sender, Link link) { if (CbLinkCreated.IsChecked != true) { return; } BoxLogEvents.Text = "LinkCreated" + Environment.NewLine + BoxLogEvents.Text; }
private void QBuilder_OnDataSourceAdded(SQLQuery sender, DataSource addedobject) { if (CbDataSourceAdded.IsChecked != true) { return; } BoxLogEvents.Text = "DataSourceAdded " + addedobject.NameInQuery + Environment.NewLine + BoxLogEvents.Text; }
public virtual XVar getCurrentRecordInternal() { dynamic fetchedArray = null, keysSet = null, orderClause = null, sql = XVar.Array(); if (XVar.Pack(!(XVar)(this.data == null))) { return(this.data); } sql = XVar.Clone(getSubsetSQLComponents()); orderClause = XVar.Clone(getOrderByClause()); keysSet = XVar.Clone(checkKeysSet()); if (XVar.Pack(keysSet)) { sql.InitAndSetArrayItem(XVar.Array(), "optionalWhere"); sql.InitAndSetArrayItem(XVar.Array(), "optionalHaving"); sql.InitAndSetArrayItem(XVar.Array(), "mandatoryHaving"); sql.InitAndSetArrayItem(XVar.Array(), "mandatoryWhere"); sql.InitAndSetArrayItem(CommonFunctions.KeyWhere((XVar)(this.keys)), "mandatoryWhere", null); sql.InitAndSetArrayItem(SecuritySQL(new XVar("Search"), (XVar)(this.tName)), "mandatoryWhere", null); } GlobalVars.strSQL = XVar.Clone(SQLQuery.buildSQL((XVar)(sql["sqlParts"]), (XVar)(sql["mandatoryWhere"]), (XVar)(sql["mandatoryHaving"]), (XVar)(sql["optionalWhere"]), (XVar)(sql["optionalHaving"]))); if (XVar.Pack(!(XVar)(keysSet))) { GlobalVars.strSQL = XVar.Clone(CommonFunctions.applyDBrecordLimit((XVar)(MVCFunctions.Concat(GlobalVars.strSQL, orderClause)), new XVar(1), (XVar)(this.connection.dbType))); } if (XVar.Pack(this.eventsObject.exists(new XVar("BeforeQueryView")))) { dynamic strSQLbak = null, strWhereClause = null, strWhereClauseBak = null; strWhereClause = XVar.Clone(SQLQuery.combineCases((XVar)(sql["mandatoryWhere"]), new XVar("and"))); strSQLbak = XVar.Clone(GlobalVars.strSQL); strWhereClauseBak = XVar.Clone(strWhereClause); this.eventsObject.BeforeQueryView((XVar)(GlobalVars.strSQL), ref strWhereClause, this); if ((XVar)(strSQLbak == GlobalVars.strSQL) && (XVar)(strWhereClauseBak != strWhereClause)) { GlobalVars.strSQL = XVar.Clone(SQLQuery.buildSQL((XVar)(sql["sqlParts"]), (XVar)(new XVar(0, strWhereClause)))); if (XVar.Pack(!(XVar)(keysSet))) { GlobalVars.strSQL = XVar.Clone(CommonFunctions.applyDBrecordLimit((XVar)(MVCFunctions.Concat(GlobalVars.strSQL, orderClause)), new XVar(1), (XVar)(this.connection.dbType))); } } } CommonFunctions.LogInfo((XVar)(GlobalVars.strSQL)); fetchedArray = XVar.Clone(this.connection.query((XVar)(GlobalVars.strSQL)).fetchAssoc()); this.data = XVar.Clone(this.cipherer.DecryptFetchedArray((XVar)(fetchedArray))); if (XVar.Pack(!(XVar)(keysSet))) { this.keys = XVar.Clone(getKeysFromData((XVar)(this.data))); setKeysForJs(); } if ((XVar)(MVCFunctions.count(this.data)) && (XVar)(this.eventsObject.exists(new XVar("ProcessValuesView")))) { this.eventsObject.ProcessValuesView((XVar)(this.data), this); } return(this.data); }
public static bool projectRefersToModels(UIProgress paramUIProgress, Session paramSession, long?paramLong) { paramUIProgress.Progress = UILanguage.get("tab.project.processing.model.check"); SQLQuery sQLQuery = paramSession.createSQLQuery("SELECT count(1) from CNDON where PRJID = :id and TAKEOFFTYPE = :type"); sQLQuery.setLong("id", paramLong.Value); sQLQuery.setString("type", "BIM"); Number number = (Number)sQLQuery.list().get(0); return(number.intValue() != 0); }
void checkSQLReqParameter(SQLQuery value) { if (value == null) { throw new Exception("The body is missing or not formatted correctly. Maybe just a comma is missing between two attributes."); } if (string.IsNullOrEmpty(value.SQL) && string.IsNullOrEmpty(value.userQuery)) { throw new Exception("No SQL nor userQuery defined. Maybe your JSON is badly formatted. Use JSON.stringify"); } }
public void NonQueryTestNoParameters() { var query = new SQLQuery(sqlString, SQLQueryType.NonQuery); queryRunner.RunQuery(query); VerifyLogging(sqlString); Assert.AreEqual(sqlString, MockDatabaseFactory.DbCommand.Object.CommandText); MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1)); MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0)); MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(1)); Assert.AreEqual(345, query.RowCount); }
public void InsertDataBalance(SQLiteConnection AnyConnectionObject, ref int Choice2) { string Duration = null; string ChooseTable = null; SQLiteCommand SQLQuery; SQLQuery = AnyConnectionObject.CreateCommand(); if (Choice2 == 1) { Console.Write("Enter the leg raised (left/right): "); string LegRaised = Console.ReadLine(); Console.WriteLine("Enter the duration: "); Duration = Console.ReadLine(); ChooseTable = "StandingOnOneFoot"; SQLQuery.CommandText = "INSERT INTO " + ChooseTable + " (Date, LegRaisedToFront, Time) VALUES(strftime('%d/%m/%Y %H:%M', 'now', 'localtime'), @param1, @param2)"; SQLQuery.Parameters.Add(new SQLiteParameter("@param1", LegRaised)); SQLQuery.Parameters.Add(new SQLiteParameter("@param2", Duration)); SQLQuery.ExecuteNonQuery(); } if (Choice2 == 2) { Console.Write("*******GUIDELINES*******: For (Decimal), maximum number that can be entered is 9999.99. Max number of decimal places is 2, values like 9999.999 cannot be entered\n"); Console.Write("*******END OF GUIDELINES**\n\n\n"); Console.Write("Enter duration: "); Duration = Console.ReadLine(); Console.Write("Enter the number of steps taken (Decimal): "); double StepsTaken = Convert.ToDouble(Console.ReadLine()); ChooseTable = "HeelToToeWalk"; SQLQuery.CommandText = "INSERT INTO " + ChooseTable + " (Date, Time, Steps) VALUES(strftime('%d/%m/%Y %H:%M', 'now', 'localtime'), @param1, @param2)"; SQLQuery.Parameters.Add(new SQLiteParameter("@param1", Duration)); SQLQuery.Parameters.Add(new SQLiteParameter("@param2", StepsTaken)); SQLQuery.ExecuteNonQuery(); } }
private SQLQuery GetUpdateQuery(SyncChange change, List <Tuple <string, object> > data) { var tableMap = tableSet.GetTableMapping(change); var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable); var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName); var updates = tableInfo.Columns.Where(c => !c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName); var sql = "update " + tableMap.FullyQualifiedDestinationTable + " set " + string.Join(", ", updates) + " where " + string.Join(" AND ", whereColumns) + ";"; var query = new SQLQuery(sql, SQLQueryType.NonQuery); AddColumnParametersToQuery(query, tableInfo.Columns, data); return(query); }
public Form1() { SQLContext sqlContext = new SQLContext { LoadingOptions = { OfflineMode = true } }; _query = new SQLQuery(sqlContext); _query.SQLUpdated += Query_SQLUpdated; InitializeComponent(); comboBoxSyntax.SelectedItem = "MS SQL Server"; }
private void GetDataFromTable(SyncInfo reportInfo, TableInfo tableInfo, TableMapping tableMap, bool isSource, string tableName) { string sql; string parameterSymbol = isSource ? "?" : "@"; bool firstKey = true; if (isSource && !string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly)) { sql = tableMap.CustomSourceSQLForSyncOnly; } else { var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = " + parameterSymbol + c.ColumnName + ")"); sql = "select * from " + tableName + " where " + string.Join(" AND ", whereColumns) + ";"; } var query = new SQLQuery(sql); foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID)) { if (sql.Contains(parameterSymbol + column.ColumnName)) { query.Parameters.Add(column.ColumnName, firstKey ? reportInfo.PrimaryKey1 : reportInfo.PrimaryKey2); } firstKey = false; } if (isSource) { query.ProcessRow = reader => { for (int i = 0; i < reader.FieldCount; i++) { reportInfo.SourceDataList.Insert(i, ConvertDataTypes(reader[i])); } return(false); }; sourceDB.RunQuery(query); } else { query.ProcessRow = reader => { for (int i = 0; i < reader.FieldCount; i++) { reportInfo.DestinationDataList.Insert(i, ConvertDataTypes(reader[i])); } return(false); }; destinationDB.RunQuery(query); } }
public SQLResult Get([FromBody] SQLQuery value) { try { if (!SAPB1.brokerConf.sql) { throw new Exception("SQL module was disabled in web.config for SQL Broker"); } checkSQLReqParameter(value); return(SAPB1.SQLQuery(value, true, new SQLResult(Request))); } catch (Exception e) { return(handleException(e)); } }
public virtual void execute(Session param1Session, string param1String1, string param1String2) { l.info("Rebuilding index " + param1String2 + " on table " + param1String1); try { SQLQuery sQLQuery = param1Session.createSQLQuery("ALTER INDEX " + param1String2 + " ON " + param1String1 + " REBUILD"); sQLQuery.executeUpdate(); } catch (Exception exception) { l.error("Could not rebuild index", exception); } }
/// <summary> /// The user have decided to save or not to save the current file via the parameter /// Then are all building objects removed and the update of the GUI comes in /// Lastly all components are reset to how they were when the application started /// </summary> /// <param name="save"></param> public void NewFile(bool save) { if (save) { SaveFile(); } SQLQuery.DeleteAllBuildings(); buildingManager.DeleteAll(); ClearTable(); filePath = null; savedUpToDate = true; buildingManager.SetDisplayList(); }
//TODO: get foreign key info... public static TableInfo GetTableInfo(this QueryRunner database, string schema, string tableName) { var sql = @"SELECT distinct ISNULL(sch.name, '') 'SchemaName', tables.name 'TableName', c.name 'ColumnName', c.column_id 'ColumnID', t.Name 'Datatype', c.max_length 'MaxLength', c.precision 'Precision' , c.scale 'Scale', c.is_nullable 'Nullable', ISNULL((select is_primary_key from sys.indexes i inner join sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id where ic.object_id = c.object_id AND ic.column_id = c.column_id and is_primary_key = 1),0) as 'PrimaryKey', ISNULL(c.is_identity, 0) 'IsIdentity' FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id INNER JOIN sys.tables tables ON (tables.object_id = c.object_id) LEFT OUTER JOIN sys.schemas sch on (sch.schema_id = tables.schema_id) WHERE c.object_id = OBJECT_ID(@tableName) order by TableName, ColumnID;"; var tableInfo = tableInfoCache.FirstOrDefault(t => t.ConnectionString == database.ConnectionString && t.SchemaName == schema && t.TableName == tableName); if (tableInfo == null) { var columnList = new List<ColumnInfo>(); string actualSchemaName = string.Empty; string actualTableName = string.Empty; var query = new SQLQuery(sql); var schemaTableName = string.IsNullOrEmpty(schema) ? tableName : schema + "." + tableName; query.Parameters.Add("tableName", schemaTableName); query.ProcessRow = reader => { columnList.Add(new ColumnInfo( (string) reader["ColumnName"], Convert.ToInt32(reader["ColumnID"]), (string) reader["DataType"], Convert.ToInt32(reader["MaxLength"]), Convert.ToInt32(reader["Precision"]), Convert.ToInt32(reader["Scale"]), (bool) reader["Nullable"], (bool) reader["PrimaryKey"], (bool) reader["IsIdentity"])); actualSchemaName = (string) reader["SchemaName"]; actualTableName = (string) reader["TableName"]; return true; }; database.RunQuery(query); if (columnList.Count == 0) { throw new ApplicationException("Couldn't find any columns for table " + schemaTableName); } tableInfo = new TableInfo(database.ConnectionString, actualTableName, actualSchemaName, columnList.ToArray()); tableInfoCache.Add(tableInfo); } return tableInfo; }
//TODO: get foreign key info... public static TableInfo GetTableInfo(this QueryRunner database, string schema, string tableName) { var sql = @"SELECT distinct ISNULL(sch.name, '') 'SchemaName', tables.name 'TableName', c.name 'ColumnName', c.column_id 'ColumnID', t.Name 'Datatype', c.max_length 'MaxLength', c.precision 'Precision' , c.scale 'Scale', c.is_nullable 'Nullable', ISNULL((select is_primary_key from sys.indexes i inner join sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id where ic.object_id = c.object_id AND ic.column_id = c.column_id and is_primary_key = 1),0) as 'PrimaryKey', ISNULL(c.is_identity, 0) 'IsIdentity' FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id INNER JOIN sys.tables tables ON (tables.object_id = c.object_id) LEFT OUTER JOIN sys.schemas sch on (sch.schema_id = tables.schema_id) WHERE c.object_id = OBJECT_ID(@tableName) order by TableName, ColumnID;" ; var tableInfo = tableInfoCache.FirstOrDefault(t => t.ConnectionString == database.ConnectionString && t.SchemaName == schema && t.TableName == tableName); if (tableInfo == null) { var columnList = new List <ColumnInfo>(); string actualSchemaName = string.Empty; string actualTableName = string.Empty; var query = new SQLQuery(sql); var schemaTableName = string.IsNullOrEmpty(schema) ? tableName : schema + "." + tableName; query.Parameters.Add("tableName", schemaTableName); query.ProcessRow = reader => { columnList.Add(new ColumnInfo( (string)reader["ColumnName"], Convert.ToInt32(reader["ColumnID"]), (string)reader["DataType"], Convert.ToInt32(reader["MaxLength"]), Convert.ToInt32(reader["Precision"]), Convert.ToInt32(reader["Scale"]), (bool)reader["Nullable"], (bool)reader["PrimaryKey"], (bool)reader["IsIdentity"])); actualSchemaName = (string)reader["SchemaName"]; actualTableName = (string)reader["TableName"]; return(true); }; database.RunQuery(query); if (columnList.Count == 0) { throw new ApplicationException("Couldn't find any columns for table " + schemaTableName); } tableInfo = new TableInfo(database.ConnectionString, actualTableName, actualSchemaName, columnList.ToArray()); tableInfoCache.Add(tableInfo); } return(tableInfo); }
static void SQLADONETQuery(SQLQuery q, SQLResult result, ConnectionParams cp, bool rollbackOnly) { using (SqlConnection t = new SqlConnection(cp.getConnectionString())) //{ t.Open(); using (SqlCommand sqlCommand = new SqlCommand { Connection = t }) { sqlCommand.Connection.Open(); if (!string.IsNullOrEmpty(q.userQuery)) { if (!string.IsNullOrEmpty(q.SQL)) { throw new Exception("SQL must be empty for a user query request"); } q.SQL = getUserQuerySQL(t, q, cp); checkRollbackOnlyConditions(result.rollbackOnly, q.SQL); } result.SQL = q.SQL; result.userQuery = !string.IsNullOrEmpty(q.userQuery) ? q.uqCategory + "." + q.userQuery : null; result.extendedUQ = q.uqCategory != null && q.uqCategory.EndsWith(UQCATEGORY_EXTENSION); sqlCommand.CommandText = q.SQL; using (SqlDataReader rs = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) { result.statusCode = System.Net.HttpStatusCode.OK; var data = new Newtonsoft.Json.Linq.JArray(); do { while (rs.Read()) { var row = new Newtonsoft.Json.Linq.JObject(); for (int i = 0; i < rs.FieldCount; i++) { row.Add(rs.GetName(i), rs.GetValue(i).ToString()); } data.Add(row); } result.data = data; if (q.columnInfo) { int cc = rs.FieldCount; for (int i = 0; i < cc; i++) { SQLResult.Column column = new SQLResult.Column(); column.name = rs.GetName(i); column.dataType = rs.GetDataTypeName(i); //column.subType = ;//Hmm, What to give here? result.columns.Add(column); } } } while (rs.NextResult()); } } }
public void TruncateDestinationTables() { var destinationQueryRunner = new QueryRunner(tableSet.DestinationConnectionStringName); var destinationQueries = new List <SQLQuery>(); foreach (var tableMap in tableSet.Mappings.Where(ts => ts.TruncateDestinationAndBulkLoadFromSource).OrderByDescending(ts => ts.Ordinal)) { Logging.WriteMessageToApplicationLog("About to delete all rows in table " + tableMap.FullyQualifiedDestinationTable, EventLogEntryType.Information); var destinationSql = "delete from " + tableMap.FullyQualifiedDestinationTable + ";"; var destinationQuery = new SQLQuery(destinationSql, SQLQueryType.NonQuery); destinationQueries.Add(destinationQuery); } destinationQueryRunner.RunQuery(destinationQueries, true); }
public virtual XVar getFullWhereComponent(dynamic _param_pageObj) { #region pass-by-value parameters dynamic pageObj = XVar.Clone(_param_pageObj); #endregion dynamic searchWhere = XVar.Array(), whereComponents = XVar.Array(); whereComponents = XVar.Clone(pageObj.getWhereComponents()); searchWhere = XVar.Clone(XVar.Array()); searchWhere.InitAndSetArrayItem(whereComponents["searchWhere"], null); searchWhere.InitAndSetArrayItem(pageObj.getCurrentTabWhere(), null); whereComponents.InitAndSetArrayItem(SQLQuery.combineCases((XVar)(searchWhere), new XVar("and")), "searchWhere"); return(whereComponents); }
public void UpdateStatusOfChanges(IEnumerable<SyncChange> changes, RowChangeStatus newStatus) { string sql = "update " + tableSet.FullyQualifiedSyncTableName + " set status = ?status where id = ?id;"; var queryList = new List<SQLQuery>(); //TODO:this can be a single query... foreach (var change in changes) { var query = new SQLQuery(sql, SQLQueryType.NonQuery); query.Parameters.Add("status", (int) newStatus); query.Parameters.Add("id", change.RowId); queryList.Add(query); } SourceDb.RunQuery(queryList, true); }
public Customer GetCustomerNoOrders(int customerId) { Customer customer = null; var query = new SQLQuery(SQLSelectSingleCustomer); query.ProcessRow = dr => { customer = CreateCustomerFromDataReader(dr); return(true); }; query.Parameters.Add("CustomerId", customerId); queryRunner.RunQuery(query); return(customer); }
/// <summary> /// parses the entire workload /// </summary> /// <param name="Workload"></param> /// <returns></returns> public static SQLQuery[] Parse(String[] Workload) { var toreturn = new List <SQLQuery>(); for (int i = 2; i < Workload.Length; i++) { SQLQuery sqlQuery = ParseLine(Workload[i]); if (sqlQuery != null) { toreturn.Add(sqlQuery); } } return(toreturn.ToArray()); }
public void SetPagedDataSource(SQLQuery s, BindingNavigator bnav) { this.s = s; int count = DataProvider.ExecuteCount(s.CountQuery); pg = new Paging(count, 5); bnav.BindingSource = pg.BindingSource; pg.BindingSource.PositionChanged += new EventHandler(bs_PositionChanged); //first page string q = s.GetPagingQuery(pg.GetStartRowNum(1), pg.GetEndRowNum(1), true); DataTable dt = DataProvider.ExecuteDt(q); DataSource = dt; }
private void ExecuteQueryThreadSafe(SQLQuery pQuery) { /* try { OpenConnection(pQuery.GetDatabaseName()); var data = ExecuteRequest(pQuery.GetQuery()); pQuery.Result = ReadResult(data); CloseConnection(); } catch(Exception exception) { Error.Create(exception.Message, EErrorSeverity.MAJOR); }*/ }
public async Task <IHttpActionResult> Login(Credentials cred) { var processingResult = new ServiceProcessingResult <LoginReturn> { IsSuccessful = true }; try { var sqlQuery = "SELECT schcode,schname,invno FROM InvoiceInfo WHERE Schcode=@Schcode AND Invno=@Invno"; MySqlParameter[] parameters = new MySqlParameter[] { new MySqlParameter("@Invno", cred.password), new MySqlParameter("@Schcode", cred.schcode) }; var sqlQueryService = new SQLQuery(); var getLoginResult = await sqlQueryService.ExecuteReaderAsync <LoginReturn>(CommandType.Text, sqlQuery, parameters); if (!getLoginResult.IsSuccessful) { processingResult.IsSuccessful = false; processingResult.Error = new ProcessingError("Failed to login.", "Failed to login.", true, false); ExceptionlessClient.Default.CreateLog(typeof(InvoiceController).FullName, "Login failed", "Error").AddObject(cred).AddTags("Controller Error").Submit(); return(Ok(processingResult)); } var retval = (List <LoginReturn>)getLoginResult.Data; if (retval == null) { processingResult.IsSuccessful = false; processingResult.Error = new ProcessingError("Improper Credeintials.", "Improper Credentials.", true, false); return(Ok(processingResult)); } if (retval.Count == 1) { processingResult.Data = retval[0]; } else { processingResult.IsSuccessful = false; processingResult.Error = new ProcessingError("Improper Credeintials.", "Improper Credentials.", true, false); } return(Ok(processingResult)); } catch (Exception ex) { processingResult.IsSuccessful = false; processingResult.Error = new ProcessingError("Failed to login.", "Failed to login.", true, false); ex.ToExceptionless() .SetMessage("Login Failed") .Submit(); return(Ok(processingResult)); } }
public ActionResult PostBid(AuctionItemPage currentPage, BidStuff BidThing) { string idStr = currentPage.PageLink.ToString(); int id = Convert.ToInt32(idStr); BidVM bid = new BidVM(); bid.ItemId = id; bid.BidAmount = BidThing.BidAmount; bid.NewBidder = BidThing.NewBidder; string message = SQLQuery.AddBidToDB(bid); return(RedirectToAction("Index")); }
public void UpdateStatusOfChanges(IEnumerable <SyncChange> changes, RowChangeStatus newStatus) { string sql = "update " + tableSet.FullyQualifiedSyncTableName + " set status = ?status where id = ?id;"; var queryList = new List <SQLQuery>(); //TODO:this can be a single query... foreach (var change in changes) { var query = new SQLQuery(sql, SQLQueryType.NonQuery); query.Parameters.Add("status", (int)newStatus); query.Parameters.Add("id", change.RowId); queryList.Add(query); } SourceDb.RunQuery(queryList, true); }
public MainWindow() { InitializeComponent(); var sqlContext = new SQLContext { LoadingOptions = { OfflineMode = true } }; _query = new SQLQuery(sqlContext); _query.SQLUpdated += _query_SQLUpdated; InitializeComponent(); ComboBoxSyntax.SelectedItem = "MS SQL Server"; Loaded += MainWindow_Loaded; }
public Dictionary <OrderStatus, int> GetOrderCountForAllStatus(DateTime startDate, DateTime endDate) { var returnDict = new Dictionary <OrderStatus, int>(); var query = new SQLQuery(SQLCountOrderAllStatusByDate, SQLQueryType.DataReader); query.ProcessRow = new Func <DbDataReader, bool>(dr => { returnDict.Add((OrderStatus)(int)dr["Status"], (int)dr["count"]); return(true); }); query.Parameters.Add("StartDate", startDate); query.Parameters.Add("EndDate", endDate); queryRunner.RunQuery(query); return(returnDict); }
public static DataView GetDataView(string sqlCommand, SQLQuery sqlQuery) { if (string.IsNullOrEmpty(sqlCommand)) { return(null); } if (sqlQuery.SQLContext.MetadataProvider == null) { return(null); } if (!sqlQuery.SQLContext.MetadataProvider.Connected) { sqlQuery.SQLContext.MetadataProvider.Connect(); } if (string.IsNullOrEmpty(sqlCommand)) { return(null); } if (!sqlQuery.SQLContext.MetadataProvider.Connected) { sqlQuery.SQLContext.MetadataProvider.Connect(); } var command = CreateSqlCommand(sqlCommand, sqlQuery); DataTable table = new DataTable("result"); using (var dbReader = command.ExecuteReader()) { for (int i = 0; i < dbReader.FieldCount; i++) { table.Columns.Add(dbReader.GetName(i)); } while (dbReader.Read()) { object[] values = new object[dbReader.FieldCount]; dbReader.GetValues(values); table.Rows.Add(values); } return(table.DefaultView); } }
private IList <string> getTables(Session paramSession, int paramInt, string paramString) { List <object> arrayList = new List <object>(); if (paramInt == ProjectServerDBUtil.SQLSERVER_DBMS) { string str = string.Format("SELECT QUOTENAME( TABLE_SCHEMA )+'.'+QUOTENAME( TABLE_NAME ) AS tableName FROM INFORMATION_SCHEMA.tables WHERE TABLE_CATALOG='{0}';", new object[] { paramString }); SQLQuery sQLQuery = paramSession.createSQLQuery(str); System.Collections.IList list = sQLQuery.list(); foreach (string str1 in list) { arrayList.Add(str1); } } return(arrayList); }
private List<SyncChange> GetChanges() { string sql = "select * from " + tableSet.FullyQualifiedSyncTableName + " where status = ?status order by id;"; var changes = new List<SyncChange>(); var query = new SQLQuery(sql); query.Parameters.Add("status", (int)RowChangeStatus.none); query.ProcessRow = reader => { changes.Add(SyncChangeFromDataReader(reader)); return true; }; SourceDb.RunQuery(query); return changes; }
public void Select() { var query = new SQLQuery<Order>() .Select.Add(x => x.CustomerID).Add(x => x.OrderDate) .Return(); string sql = query.Build().SQL; Assert.IsTrue(sql.Contains("a.[CustomerID]")); Assert.IsTrue(sql.Contains("a.[OrderDate]")); }
public void TruncateDestinationTables() { var destinationQueryRunner = new QueryRunner(tableSet.DestinationConnectionStringName); var destinationQueries = new List<SQLQuery>(); foreach (var tableMap in tableSet.Mappings.Where(ts=>ts.TruncateDestinationAndBulkLoadFromSource).OrderByDescending(ts => ts.Ordinal)) { Logging.WriteMessageToApplicationLog("About to delete all rows in table " + tableMap.FullyQualifiedDestinationTable, EventLogEntryType.Information); var destinationSql = "delete from " + tableMap.FullyQualifiedDestinationTable + ";"; var destinationQuery = new SQLQuery(destinationSql, SQLQueryType.NonQuery); destinationQueries.Add(destinationQuery); } destinationQueryRunner.RunQuery(destinationQueries, true); }
public void From() { string sql = new SQLQuery<Product>() .Build() .SQL; Assert.IsTrue(sql.Contains("FROM [Product]")); }
private IEnumerable<SyncInfo> GetSyncChangedList() { string sql = "select distinct `schema`, `table`, `pk1`, `pk2` from " + tableSet.FullyQualifiedSyncTableName + " where `datetime` >= DATE_SUB(curdate(), INTERVAL 1 DAY);"; var syncChangeList = new List<SyncInfo>(); var syncChangesQuery = new SQLQuery(sql) { ProcessRow = reader => { syncChangeList.Add(PopulateChangeListFromDataReader(reader)); return true; } }; sourceDB.RunQuery(syncChangesQuery); return syncChangeList; }
private SQLQuery GetUpdateQuery(SyncChange change, List<Tuple<string, object>> data) { var tableMap = tableSet.GetTableMapping(change); var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable); var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName); var updates = tableInfo.Columns.Where(c => !c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName); var sql = "update " + tableMap.FullyQualifiedDestinationTable + " set " + string.Join(", ", updates) + " where " + string.Join(" AND ", whereColumns) + ";"; var query = new SQLQuery(sql, SQLQueryType.NonQuery); AddColumnParametersToQuery(query, tableInfo.Columns, data); return query; }
private List<Tuple<string, object>> GetDataFromSource(SyncChange change) { var tableMap = tableSet.GetTableMapping(change); var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable); if (change.Operation == Operation.delete) { return GetDataForDelete(change); } string sql = null; if (!string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly)) { sql = tableMap.CustomSourceSQLForSyncOnly; } else { var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = ?" + c.ColumnName + ")"); sql = "select * from " + tableMap.FullyQualifiedSourceTable + " where " + string.Join(" AND ", whereColumns) + ";"; } var query = new SQLQuery(sql); bool firstKey = true; //yes, it's hacky foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID)) { if (sql.Contains("?" + column.ColumnName)) //get rid of this and just throw..... { query.Parameters.Add(column.ColumnName, firstKey ? change.PrimaryKey1 : change.PrimaryKey2); } firstKey = false; } var dataList = new List<Tuple<string, object>>(); query.ProcessRow = reader => { for (int i = 0; i < reader.FieldCount; i++) { dataList.Add(new Tuple<string, object>(reader.GetName(i), reader[i])); } return false; //return true here, but have a separate variable to see if this gets called again, if so, throw error... }; sourceDb.RunQuery(query); return ConvertDataTypes(dataList); }
public void OrderBy() { var query = new SQLQuery<Order>() .From .InnerJoin<Customer>(x => x.CustomerID, x => x.ID) .LeftOuterJoin<OrderItem>(x => x.ID, x => x.OrderID, false) .OrderBy.Ascending<Customer>(x => x.Name) .Descending(x => x.OrderDate) .Return(); string sql = query.Build().SQL; Assert.IsTrue(sql.Contains("b.[Name]")); Assert.IsTrue(sql.Contains("a.[OrderDate] DESC")); }
public void InnerJoin() { var query = new SQLQuery<Order>() .From .InnerJoin<Customer>(x => x.CustomerID, x => x.ID) .Return(); QueryStatement statement = query.Build(); }
private SQLQuery GetInsertQuery(SyncChange change, List<Tuple<string, object>> rowData) { var tableMap = tableSet.GetTableMapping(change); var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable); var columnList = string.Join(",", tableInfo.Columns.OrderBy(c => c.ColumnID).Select(c => "[" + c.ColumnName + "]")); var parameterList = string.Join(",", tableInfo.Columns.OrderBy(c => c.ColumnID).Select(c => "@" + c.ColumnName)); var sql = "insert into " + tableMap.FullyQualifiedDestinationTable + " ( " + columnList + ") values (" + parameterList + ");"; if (tableInfo.Columns.Any(c => c.Identity)) { sql = "set identity_insert " + tableMap.FullyQualifiedDestinationTable + " ON; " + sql + " set identity_insert " + tableMap.FullyQualifiedDestinationTable + " OFF;"; } var query = new SQLQuery(sql, SQLQueryType.NonQuery); foreach (var columnInfo in tableInfo.Columns) { var tuple = rowData.First(t => t.Item1 == columnInfo.ColumnName); query.Parameters.Add("@" + columnInfo.ColumnName, tuple.Item2); } return query; }
public void Where() { var query = new SQLQuery<Order>() .From .InnerJoin<Customer>(x => x.CustomerID, x => x.ID) .LeftOuterJoin<OrderItem>(x => x.ID, x => x.OrderID, false) .Where.Equal<Customer>(x => x.Name, "Tim") .Or .START .Equal<Customer, string>(x => x.Name, "Julia") .And .Equal(x => x.ID, 5) .END .Return(); QueryStatement statement = query.Build(); Assert.AreEqual(3, statement.Parameters.Length); }
public void LeftJoin() { var query = new SQLQuery<Order>() .From.InnerJoin<Customer>(x => x.CustomerID, x => x.ID, false) .LeftOuterJoin<OrderItem>(x => x.ID, x => x.OrderID) .LeftOuterJoin<OrderItem, Product, int>(x => x.ProductID, x => x.ID) .Return(); QueryStatement statement = query.Build(); Assert.IsTrue(statement.SQL.Contains("LEFT OUTER JOIN")); }
private void GetDataFromTable(SyncInfo reportInfo, TableInfo tableInfo, TableMapping tableMap, bool isSource, string tableName) { string sql; string parameterSymbol = isSource ? "?" : "@"; bool firstKey = true; if (isSource && !string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly)) { sql = tableMap.CustomSourceSQLForSyncOnly; } else { var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = " + parameterSymbol + c.ColumnName + ")"); sql = "select * from " + tableName + " where " + string.Join(" AND ", whereColumns) + ";"; } var query = new SQLQuery(sql); foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID)) { if (sql.Contains(parameterSymbol + column.ColumnName)) { query.Parameters.Add(column.ColumnName, firstKey ? reportInfo.PrimaryKey1 : reportInfo.PrimaryKey2); } firstKey = false; } if (isSource) { query.ProcessRow = reader => { for (int i = 0; i < reader.FieldCount; i++) { reportInfo.SourceDataList.Insert(i, ConvertDataTypes(reader[i])); } return false; }; sourceDB.RunQuery(query); } else { query.ProcessRow = reader => { for (int i = 0; i < reader.FieldCount; i++) { reportInfo.DestinationDataList.Insert(i, ConvertDataTypes(reader[i])); } return false; }; destinationDB.RunQuery(query); } }