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