public override int SelectCount(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition) { StringBuilder sb = new StringBuilder(); SqlCommand command = (SqlCommand)db.Connection.CreateCommand(); int parameterCounter = 1; //创建一个QueryExpression IQueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; queryExpression.Wheres.Add(condition); var sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); sb.Append("select count(0) "); GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping); //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; IDataReader dataReader = db.ExecuteReader(command); int count = 0; if (dataReader.Read()) { count = Convert.ToInt32(dataReader[0]); } dataReader.Close(); return(count); }
public override bool Exists(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition) { IQueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; queryExpression.Wheres.Add(condition); var sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); StringBuilder sb = new StringBuilder(); SqlCommand command = (SqlCommand)db.Connection.CreateCommand(); int parameterCounter = 1; sb.Append("select top 1 1 "); GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping); //sb.Append(conditionCollection.Count > 0 ? " and " : " where "); //sb.Append(" rownum<2"); //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; IDataReader dataReader = db.ExecuteReader(command); bool exists = dataReader.Read(); dataReader.Close(); return(exists); }
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition) { //ITable table=getTables().getTableByClass(clazz); IQueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; queryExpression.Wheres.Add(condition); SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); //执行关联删除 var sb = new StringBuilder(); var command = db.Connection.CreateCommand(); int parameterCounter = 1; sb.Append("delete "); sb.Append(sqlAnalyseResult.MasterTableNameAlias); GenerateSelectSql_FromWhere(sb, command.Parameters, ref parameterCounter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping); //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; int returnCount = db.ExecuteNonQuery(command); return(returnCount); }
public void GetMaxEnergyTest_ReturnAverageEnergy(double expectedResult) { // Arange var sqlAnalizer = new SqlAnalyzer <IEnergyObservation>(ConnectionString); // Act var result = sqlAnalizer.GetMaxEnergy(); // Assert Assert.AreEqual(expectedResult, result, 1); }
public override SelectionResult SelectCollection(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, IQueryExpression queryExpression) { //检查pageSize和pageIndex是否合法 Util.CheckPageSizeAndPageIndex(queryExpression.PageSize, queryExpression.PageIndex); SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); StringBuilder sb = new StringBuilder(); IDbCommand cmd = db.Connection.CreateCommand(); int counter = 1; GenerateSelectSql_SelectFromWhereOrder(sb, cmd.Parameters, ref counter, queryExpression, sqlAnalyseResult, mappingProvider, tableMapping); //执行sql cmd.CommandText = sb.ToString(); cmd.CommandType = CommandType.Text; if (queryExpression.PageIndex != null && queryExpression.PageSize != null) { //分页的时候需要包装分页语句 WrapPaging(db, cmd, queryExpression.PageSize.Value, queryExpression.PageIndex.Value); } SelectionResult result = new SelectionResult(); result.ObjectFiller = sqlAnalyseResult.ObjectFiller; //先关闭datarader if (queryExpression.ReturnMatchedCount) { if (sqlAnalyseResult.HasGroup) { result.TotalMatchedCount = SelectCountGroup(db, mappingProvider, tableMapping, queryExpression.EntityType, queryExpression, sqlAnalyseResult); } else { result.TotalMatchedCount = SelectCount(db, mappingProvider, tableMapping, queryExpression.EntityType, queryExpression.Wheres); } } result.DataReader = db.ExecuteReader(cmd); return(result); }
public SqlAnalyzer GetAnalyzer(bool force = false) { if (myAnalyser != null && !force) { return(myAnalyser); } if (ScriptId > 0) { var cnn = new DatabaseConnection(); myAnalyser = SqlAnalyzer.Load(cnn, this.ScriptId); } return(myAnalyser); }
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition) { //ITable table=getTables().getTableByClass(clazz); IQueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; queryExpression.Wheres.Add(condition); SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); //执行关联删除 var sb = new StringBuilder(); var command = db.Connection.CreateCommand(); int parameterCounter = 1; sb.Append("delete "); sb.Append(sqlAnalyseResult.MasterTableNameAlias); sb.Append(" from "); sb.Append(GetTableName(entityType, mappingProvider, tableMapping)); sb.Append(" "); sb.Append(sqlAnalyseResult.MasterTableNameAlias); if (sqlAnalyseResult.ForeignTables.Count == 0) { //说明是无关联表的条件,只是本表的条件 sb.Append(" where "); GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, true, mappingProvider); } else { //说明有关联表的条件,需要多表关联 sb.Append(" where exists ("); SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, mappingProvider, tableMapping); sb.Append(")"); } //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; int returnCount = db.ExecuteNonQuery(command); return(returnCount); }
private void BtReload_Click(object sender, EventArgs e) { lstBloc.DataSource = null; lstDatabaseObject.DataSource = null; lstResume.DataSource = null; var analyzer = SqlAnalyzer.Analyse(myScript.ScriptId, myScript.ScriptText); lstBloc.DataSource = analyzer.Blocs; lstDatabaseObject.DataSource = analyzer.SqlObjets; lstResume.DataSource = analyzer.Resumes; using var cnn = new DatabaseConnection(); analyzer.Save(cnn); lstBloc.SelectedItem = null; lstDatabaseObject.SelectedItem = null; lstResume.SelectedItem = null; EnableButtons(); }
private void AddScriptToObjectAction(Object currentObjectEdited, int action) { // Ajouter un script à la version var script = new ScriptObject(currentObjectEdited, new SqlAction() { SqlActionId = action }); var crudScript = new Script() { ScriptText = script.ToString(), VersionId = currentObjectEdited.VersionId, }; var scriptId = cnn.ExecuteScalar(Script.SQLInsert, crudScript); // Ajouter l'anlyse pour ce script var analyzer = SqlAnalyzer.Analyse(scriptId, crudScript.ScriptText); analyzer.Valide = EValidation.Valide; analyzer.Save(cnn); }
public override int Delete(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition) { //ITable table=getTables().getTableByClass(clazz); IQueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; queryExpression.Wheres.Add(condition); SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); if (sqlAnalyseResult.ForeignTables.Count > 0) { //不支持 throw ExceptionFactory.NotSupported("SQLite不支持多表关联更新"); } //执行关联删除 StringBuilder sb = new StringBuilder(); IDbCommand command = db.Connection.CreateCommand(); int parameterCounter = 1; string tableName = GetTableName(entityType, mappingProvider, tableMapping); sb.Append("delete from "); sb.Append(tableName); //说明是无关联表的条件,只是本表的条件 sb.Append(" where "); GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, false, mappingProvider); //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; int returnCount = db.ExecuteNonQuery(command); return(returnCount); }
public async Task DoUpdateAsync() { if (IsDisposed) { return; } var buffer = _currentSnapshot; var path = _document.FilePath; // replace with user token var token = _cts.Token; var instance = await FsLintVsPackage.Instance.WithCancellation(token); // this acts as a throttle await Task.Delay(200, token); if (Project == null) { await instance.JoinableTaskFactory.SwitchToMainThreadAsync(); var project = instance.Dte.Solution.FindProjectItem(path)?.ContainingProject; if (project == null) { return; } if (instance.SolutionService.GetProjectOfUniqueName(project.UniqueName, out var vsHierarchy) != VSConstants.S_OK) { return; } if (instance.SolutionService.GetGuidOfProject(vsHierarchy, out var guid) != VSConstants.S_OK) { return; } Project = new LintProjectInfo(project.Name, guid, vsHierarchy); } await Task.Yield(); var connectionString = SqlAnalyzer.tryFindConnectionString(path); if (string.IsNullOrWhiteSpace(connectionString)) { return; } var loadedSchema = SqlAnalysis.databaseSchema(connectionString); if (loadedSchema.IsError) { return; } var source = _currentSnapshot.GetText(); var sourceText = SourceText.ofString(source); var getProjectOptions = _provider.CheckerInstance.GetProjectOptionsFromScript( filename: path, sourceText: sourceText, assumeDotNetFramework: false, useSdkRefs: true, useFsiAuxLib: true, previewEnabled: true, otherFlags: new string[] { "--targetprofile:netstandard" }, loadedTimeStamp: FSharpOption <DateTime> .None, extraProjectInfo: FSharpOption <object> .None, optionsStamp: FSharpOption <long> .None, userOpName: FSharpOption <string> .None ); var(options, errors) = await FSharpAsync.StartAsTask(getProjectOptions, null, token); if (errors.Any()) { return; } var performParseAndCheck = _provider.CheckerInstance.ParseAndCheckFileInProject( filename: path, fileversion: 1, sourceText: sourceText, options: options, textSnapshotInfo: FSharpOption <object> .None, userOpName: FSharpOption <string> .None ); var(parseResults, checkAnswer) = await FSharpAsync.StartAsTask(performParseAndCheck, null, token); if (parseResults.ParseHadErrors || checkAnswer.IsAborted) { return; } var checkResults = SqlAnalyzer.checkAnswerResult(checkAnswer).Value; var context = new SqlAnalyzerContext( fileName: path, content: source.Split('\n'), parseTree: parseResults.ParseTree.Value, symbols: SqlAnalyzer.getSymbols(checkResults) ); var databaseSchema = loadedSchema.ResultValue; var errorMessages = from operation in SyntacticAnalysis.findSqlOperations(context) from analysisOutput in SqlAnalysis.analyzeOperation(operation, connectionString, databaseSchema) select analysisOutput; var oldLintingErrors = this.Factory.CurrentSnapshot; var newLintErrors = new LintingErrorsSnapshot(_document, oldLintingErrors.VersionNumber + 1); foreach (var error in errorMessages) { var span = RangeToSpan(error.Range, buffer); newLintErrors.Errors.Add(new LintError(span, error, Project)); } await instance.JoinableTaskFactory.SwitchToMainThreadAsync(); if (token.IsCancellationRequested) { return; } UpdateLintingErrors(newLintErrors); }
public async Task DoUpdateAsync() { if (IsDisposed) { return; } var buffer = _currentSnapshot; var path = _document.FilePath; // replace with user token var token = _cts.Token; var instance = await FsLintVsPackage.Instance.WithCancellation(token); // this acts as a throttle await Task.Delay(200, token); if (Project == null) { await instance.JoinableTaskFactory.SwitchToMainThreadAsync(); var project = instance.Dte.Solution.FindProjectItem(path)?.ContainingProject; if (project == null) { return; } if (instance.SolutionService.GetProjectOfUniqueName(project.UniqueName, out var vsHierarchy) != VSConstants.S_OK) { return; } if (instance.SolutionService.GetGuidOfProject(vsHierarchy, out var guid) != VSConstants.S_OK) { return; } Project = new LintProjectInfo(project.Name, guid, vsHierarchy); } await Task.Yield(); var connectionString = SqlAnalyzer.tryFindConnectionString(path); if (string.IsNullOrWhiteSpace(connectionString)) { return; } var source = _currentSnapshot.GetText(); var sourceText = SourceText.ofString(source); var getProjectOptions = _provider.CheckerInstance.GetProjectOptionsFromScript( filename: path, source: sourceText, assumeDotNetFramework: false, useSdkRefs: true, useFsiAuxLib: true, previewEnabled: true, otherFlags: new string[] { "--targetprofile:netstandard" }, loadedTimeStamp: FSharpOption <DateTime> .None, extraProjectInfo: FSharpOption <object> .None, optionsStamp: FSharpOption <long> .None, userOpName: FSharpOption <string> .None, sdkDirOverride: FSharpOption <string> .None ); var(options, errors) = await FSharpAsync.StartAsTask(getProjectOptions, null, token); if (errors.Any()) { return; } var performParseAndCheck = _provider.CheckerInstance.ParseAndCheckFileInProject( filename: path, fileVersion: 1, sourceText: sourceText, options: options, userOpName: FSharpOption <string> .None ); var(parseResults, checkAnswer) = await FSharpAsync.StartAsTask(performParseAndCheck, null, token); if (parseResults.ParseHadErrors || checkAnswer.IsAborted) { return; } var checkResults = SqlAnalyzer.checkAnswerResult(checkAnswer).Value; var context = new SqlAnalyzerContext( fileName: path, content: source.Split('\n'), parseTree: parseResults.ParseTree.Value, symbols: SqlAnalyzer.getSymbols(checkResults) ); var operationBlocks = SyntacticAnalysis.findSqlOperations(context); var loadedSchema = SqlAnalysis.databaseSchema(connectionString); var errorMessages = new List <Message>(); if (loadedSchema.IsError) { foreach (var operation in operationBlocks) { var containsSkipAnalysis = operation.blocks.Any(block => block.IsSkipAnalysis); foreach (var block in operation.blocks) { var blockRange = block.Range(); if ((block.IsQuery || block.IsTransaction) && OptionModule.IsSome(blockRange) && !containsSkipAnalysis) { var internalError = (loadedSchema.ErrorValue ?? "").Replace("3D000:", "").Replace("28P01:", ""); var fullError = $"Error occured while trying to connect to the database using the configured connection string in NPGSQL_FSHARP in order to perform static analysis:{internalError}"; var errorMessage = new Message( type: "Error", message: fullError, code: "SQL101", severity: Severity.Warning, range: blockRange.Value, fixes: Microsoft.FSharp.Collections.FSharpList <Fix> .Empty ); errorMessages.Add(errorMessage); } } } } else { var databaseSchema = loadedSchema.ResultValue; foreach (var operation in operationBlocks) { foreach (var analysisOutput in SqlAnalysis.analyzeOperation(operation, connectionString, databaseSchema)) { errorMessages.Add(analysisOutput); } } } var oldLintingErrors = this.Factory.CurrentSnapshot; var newLintErrors = new LintingErrorsSnapshot(_document, oldLintingErrors.VersionNumber + 1); foreach (var error in errorMessages) { var span = RangeToSpan(error.Range, buffer); newLintErrors.Errors.Add(new LintError(span, error, Project)); } await instance.JoinableTaskFactory.SwitchToMainThreadAsync(); if (token.IsCancellationRequested) { return; } UpdateLintingErrors(newLintErrors); }
public override int Update(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition, IDictionary <string, object> updates) { ITable table = mappingProvider.GetTable(entityType); QueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; foreach (string propertyName in updates.Keys) { queryExpression.Selects.Add(propertyName); } queryExpression.Wheres.Add(condition); SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); var sb = new StringBuilder(); var command = db.Connection.CreateCommand(); int parameterCounter = 1; sb.Append("update "); sb.Append(sqlAnalyseResult.MasterTableNameAlias); sb.Append(" set "); var index = 0; foreach (KeyValuePair <string, object> kv in updates) { if (index > 0) { sb.Append(","); } index++; IColumn column = table.Columns[kv.Key]; sb.Append(column.ColumnName); sb.Append("="); sb.Append(CreateParameterNameInStatement(parameterCounter)); //object propertyValue = column.PropertyAdapter.GetValue(obj); //Validate(column, propertyValue);//验证如:检查字符串长度,是否为null AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size); } sb.Append(" from "); sb.Append(GetTableName(entityType, mappingProvider, tableMapping)); sb.Append(" "); sb.Append(sqlAnalyseResult.MasterTableNameAlias); sb.Append(" "); if (sqlAnalyseResult.ForeignTables.Count == 0) { //说明是无关联表的条件,只是本表的条件 sb.Append(" where "); GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, true, mappingProvider); } else { //说明有关联表的条件,需要多表关联 sb.Append(" where exists ("); SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, mappingProvider, tableMapping); sb.Append(")"); } //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; int returnCount = db.ExecuteNonQuery(command); return(returnCount); }
protected virtual void GenerateSelectSql_Where_Item( StringBuilder sb, IDataParameterCollection parameters, ref int parameterCounter, IConditionExpression conditionExpression, SqlColumn sqlColumn, bool includeTableNameAlias, IMappingProvider mappingProvider ) { //为处理Like自动增加% object objValue = conditionExpression.ConditionValue; switch (conditionExpression.ConditionOperator) { case ConditionOperator.Like: case ConditionOperator.NotLike: objValue = "%" + objValue + "%"; break; case ConditionOperator.RightLike: case ConditionOperator.NotRightLike: objValue = "%" + objValue; break; case ConditionOperator.LeftLike: case ConditionOperator.NotLeftLike: objValue = objValue + "%"; break; } bool hasParameter = false; //添加字段名 sb.Append(includeTableNameAlias ? sqlColumn.TableAliasAndColumn : sqlColumn.Column.ColumnName); //添加操作符 switch (conditionExpression.ConditionOperator) { case ConditionOperator.Equal: if (conditionExpression.ConditionValue == null) { sb.Append(" is null"); } else { sb.Append("="); hasParameter = true; } break; case ConditionOperator.GreaterThan: sb.Append(">"); hasParameter = true; break; case ConditionOperator.GreaterThanOrEqual: sb.Append(">="); hasParameter = true; break; case ConditionOperator.LessThan: sb.Append("<"); hasParameter = true; break; case ConditionOperator.LessThanOrEqual: sb.Append("<="); hasParameter = true; break; case ConditionOperator.NotEqual: if (conditionExpression.ConditionValue == null) { sb.Append(" is not null"); } else { sb.Append(" <>"); hasParameter = true; } break; case ConditionOperator.In: case ConditionOperator.NotIn: if (conditionExpression.ConditionOperator == ConditionOperator.NotIn) { sb.Append(" not"); } sb.Append(" in "); if (objValue is IQueryExpression) { hasParameter = true; } else { string v = conditionExpression.ConditionValue + ""; v = v.Trim(); if (!v.StartsWith("(")) { sb.Append("("); } sb.Append(v); if (!v.EndsWith(")")) { sb.Append(")"); } } break; case ConditionOperator.Like: case ConditionOperator.LeftLike: case ConditionOperator.RightLike: sb.Append(" like "); hasParameter = true; break; case ConditionOperator.NotLike: case ConditionOperator.NotLeftLike: case ConditionOperator.NotRightLike: sb.Append(" not like "); hasParameter = true; break; case ConditionOperator.Custom: sb.Append(conditionExpression.ConditionValue); break; } if (hasParameter) { if (objValue is IQueryExpression) { IQueryExpression query = objValue as IQueryExpression; SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, query); sb.Append(" ("); GenerateSelectSql_SelectFromWhereOrder(sb, parameters, ref parameterCounter, query, sqlAnalyseResult, mappingProvider, null); sb.Append(")"); } else { sb.Append(CreateParameterNameInStatement(parameterCounter)); Type propertyType = sqlColumn.FullPropertyName.GroupFunction == GroupFunction.Count ? typeof(int) : sqlColumn.Column.PropertyType; int size = sqlColumn.FullPropertyName.GroupFunction == GroupFunction.Count ? 4 : sqlColumn.Column.Size; AddParameter(parameters, ref parameterCounter, objValue, propertyType, size); } } }