public void SqlWhileCloneTest() { SqlVariable i = SqlDml.Variable("i", SqlType.Int32); SqlWhile w = SqlDml.While(i <= 1000); SqlBatch b = SqlDml.Batch(); b.Add(SqlDml.Assign(i, i + 1)); SqlTableRef t = SqlDml.TableRef(table1); SqlSelect s = SqlDml.Select(t); s.Columns.Add(t["Name"]); s.Where = t[0] == i; SqlIf f = SqlDml.If(SqlDml.SubQuery(s) == "Unkown", SqlDml.Break, SqlDml.Continue); b.Add(f); w.Statement = b; SqlWhile wClone = (SqlWhile)w.Clone(); Assert.AreNotEqual(w, wClone); Assert.AreEqual(w.NodeType, wClone.NodeType); Assert.AreNotEqual(w.Condition, wClone.Condition); Assert.AreEqual(w.Condition.NodeType, wClone.Condition.NodeType); Assert.AreNotEqual(w.Statement, wClone.Statement); Assert.AreEqual(w.Statement.NodeType, wClone.Statement.NodeType); }
public virtual void Visit(SqlBatch node) { foreach (SqlStatement statement in node) { VisitInternal(statement); } }
/// <inheritdoc /> /// <summary> /// <c>:EXIT</c> directive /// </summary> /// <param name="batch">The batch.</param> /// <param name="exitBatch">The exit batch.</param> public virtual void Exit(SqlBatch batch, string exitBatch) { this.ProcessBatch(batch, 1); if (string.IsNullOrWhiteSpace(exitBatch) || this.arguments.ParseOnly) { return; } using (var command = this.connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = exitBatch; try { this.CustomExitCode = (int)command.ExecuteScalar(); } catch (Exception ex) { // Ignore any error: "When an incorrect query is specified, sqlcmd will exit without a return value." // Error here may include InvalidCastException if the scalar result was null or not an integer. this.WriteStdoutMessage($"Warning: Error in EXIT(query): {ex.Message}"); } } }
public void SqlBatch_ReturnsSameBatchTextAsPassed() { string s = StaticFiles.SimpleSelect(); SqlBatch b = new SqlBatch(s); Assert.AreEqual(s, b.BatchText()); Assert.AreEqual(21, b.BatchText().Length, "expected this specific length because that's what is in the file."); }
/// <summary> /// Called when the parser has a complete batch to process. /// </summary> /// <param name="batch">The batch to process.</param> /// <param name="numberOfExecutions">The number of times to execute the batch (e.g. <c>GO 2</c> to execute the batch twice.</param> /// <remarks> /// If the current error mode (as set by <c>:ON ERROR</c>) is IGNORE, then any <see cref="T:System.Data.SqlClient.SqlException" /> should be caught and /// sent to the STDERR channel, else it should be thrown and the client should handle it. /// </remarks> /// <inheritdoc /> public override void ProcessBatch(SqlBatch batch, int numberOfExecutions) { var sql = batch.Sql; Debug.WriteLine( $"#### '{batch.Source}', Line {batch.BatchBeginLineNumber}, Execution Count: {numberOfExecutions} ####"); Debug.WriteLine(string.IsNullOrWhiteSpace(sql) ? "Empty batch ignored" : sql); }
public void Test1() { //没有参数的查询,返回一个列表集合 var db = DatabaseHelper.GetHelper("test");//test是一个数据库连接 var list = db.ExecuteList <UserProfile3>("select * from UserProfile(nolock)"); //参数化查询,返回一个列表集合 var pars = db.CreateParamterCollection(3); pars.Append("sex", 1); pars.Append("name", "test"); var list2 = db.ExecuteList <UserProfile3>(@"select * from UserProfile(nolock) where Sex=@sex and Name like @name+'%'", pars); //参数化查询,返回一个列表集合 var list3 = db.ExecuteList <UserProfile3>(@"select * from UserProfile(nolock) where Sex={0} and Name like {1}+'%'", 1, "test"); //参数化查询,只返回一行数据 var user1 = db.ExecuteFirst <UserProfile3>("select * from UserProfile(nolock) where id={0}", 2); //执行一组查询,返回DataSet。适合大数据集合操作 var batch = new SqlBatch(); batch.Append("select * from UserProfile(nolock)"); batch.AppendFormat(@"select * from UserProfile(nolock) where Sex={0} and Name like {1}+'%'", 1, "test"); var ds = db.ExecuteDataSet(batch); var list4 = Map.List <UserProfile3>(ds.Tables[0]); var list5 = Map.List <UserProfile3>(ds.Tables[1]); //执行一组查询,返回IDataReader。连接字符串中需要加入配置节:MultipleActiveResultSets=True //适合小数据集操作,只有两三个数据集合,且每个集合数据量只有几十条。 var batch2 = new SqlBatch(); batch.Append("select * from UserProfile(nolock)"); batch.AppendFormat(@"select * from UserProfile(nolock) where Sex={0} and Name like {1}+'%'", 1, "test"); using (var reader = db.ExecuteReader(batch)) { var list6 = Map.List <UserProfile3>(reader); reader.NextResult(); var list7 = Map.List <UserProfile3>(reader); } Assert.IsNotNull(list); Assert.Greater(list.Count, 0); Assert.IsNotNull(list2); Assert.Greater(list2.Count, 0); Assert.IsNotNull(list3); Assert.Greater(list3.Count, 0); }
public virtual DbDataReader ExecuteReader( IRelationalConnection connection, DbTransaction transaction, string sql) { Check.NotNull(connection, nameof(connection)); Check.NotNull(sql, nameof(sql)); return(Execute( connection, () => { var command = new SqlBatch(sql).CreateCommand(connection, transaction); Logger.LogCommand(command); return command.ExecuteReader(); })); }
public void SqlBatchCloneTest() { SqlParameterRef p = SqlDml.ParameterRef("p"); SqlAssignment a = SqlDml.Assign(p, 1); SqlBatch b = SqlDml.Batch(); b.Add(a); b.Add(a); SqlBatch bClone = (SqlBatch)b.Clone(); Assert.AreNotEqual(b, bClone); Assert.AreEqual(b.NodeType, bClone.NodeType); Assert.AreEqual(b.Count, bClone.Count); foreach (SqlStatement s in b) { Assert.IsFalse(bClone.Contains(s)); } }
public virtual Task <DbDataReader> ExecuteReaderAsync( IRelationalConnection connection, DbTransaction transaction, string sql, CancellationToken cancellationToken = default(CancellationToken)) { Check.NotNull(connection, nameof(connection)); Check.NotNull(sql, nameof(sql)); return(ExecuteAsync( connection, () => { var command = new SqlBatch(sql).CreateCommand(connection, transaction); Logger.LogCommand(command); return command.ExecuteReaderAsync(cancellationToken); })); }
/// <summary> /// Gets the update table schema SQL. /// </summary> public static SqlBatch GetUpdateTableSql(this IDataHelper dh, Type modelType) { List <SqlBatch> sqls = new List <SqlBatch>(); var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, modelType); var colIndexs = AttributeHelper.GetColumn2IndexNameDics(dh.Driver.DirverType, modelType); var primaryKeyProps = AttributeHelper.GetPrimaryKeys(dh.Driver.DirverType, modelType, false); var createDdls = AttributeHelper.GetPropertyName2DDLs(dh.Driver.DirverType, modelType, dh.Driver.TypeMapping); var tableName = AttributeHelper.GetTableName(modelType, false, null); ConcurrentDictionary <string, string> columnDdls = new ConcurrentDictionary <string, string>(); foreach (var ddl in createDdls) { columnDdls.TryAdd(columnNameDic[ddl.Key], ddl.Value); } var primaryKeyColumns = primaryKeyProps.ConvertToAll(p => columnNameDic[p]); SqlBatch sql = dh.Driver.GetUpdateTableSql(tableName, columnDdls, primaryKeyColumns, colIndexs); return(sql); }
public void AddTest() { SqlLiteral <int> l1 = SqlDml.Literal(1); SqlLiteral <int> l2 = SqlDml.Literal(2); SqlBinary b = l1 + l2; Assert.AreEqual(b.NodeType, SqlNodeType.Add); b = b - ~l1; Assert.AreEqual(b.NodeType, SqlNodeType.Subtract); Assert.AreEqual(b.Right.NodeType, SqlNodeType.BitNot); SqlSelect s = SqlDml.Select(); s.Columns.Add(1, "id"); b = b / s; Assert.AreEqual(b.NodeType, SqlNodeType.Divide); Assert.AreEqual(b.Right.NodeType, SqlNodeType.SubSelect); SqlCast c = SqlDml.Cast(l1, SqlType.Decimal); Assert.AreEqual(c.NodeType, SqlNodeType.Cast); SqlFunctionCall l = SqlDml.CharLength(SqlDml.Literal("name")); b = c % l; Assert.AreEqual(b.NodeType, SqlNodeType.Modulo); Assert.AreEqual(b.Right.NodeType, SqlNodeType.FunctionCall); b = l1 * (-l2); Assert.AreEqual(b.NodeType, SqlNodeType.Multiply); Assert.AreEqual(b.Right.NodeType, SqlNodeType.Negate); SqlBatch batch = SqlDml.Batch(); SqlVariable v1 = SqlDml.Variable("v1", SqlType.Double); batch.Add(v1.Declare()); batch.Add(SqlDml.Assign(v1, 1.0)); s = SqlDml.Select(); s.Columns.Add(b, "value"); batch.Add(s); }
/// <summary> /// Gets the delete SQL by where. /// </summary> public static SqlBatch GetDeleteSqlByWhere <T>(this IDataHelper dh, string whereCondition, params Params[] paramKeyAndValue) { var type = typeof(T); var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName); List <Params> paramList = new List <Params>(); if (string.IsNullOrWhiteSpace(whereCondition)) { throw new ArgumentNullException("The parameter 'whereCondition' must have a value."); } var sqlString = string.Format("DELETE FROM {0} WHERE {1}", tableName, whereCondition); if (paramKeyAndValue != null && paramKeyAndValue.Length > 0) { paramList.AddRange(paramKeyAndValue); } var sql = new SqlBatch(sqlString, paramList.ToArray()); return(sql); }
public void Batches() { var tests = new List <Tuple <string[], string> > { Tuple.Create(new[] { "a", "#a", "a", "a", "a", "#a", "#a" }, "1t, 1n, 3t, 2n"), Tuple.Create(new[] { "a" }, "1t"), Tuple.Create(new[] { "#a" }, "1n"), Tuple.Create(new string[] {}, ""), Tuple.Create(new[] { "#" }, ""), Tuple.Create(new[] { "a", "a", "a#", "a#a" }, "4t"), Tuple.Create(new[] { "#a", "#a" }, "2n"), Tuple.Create(new[] { "a", "a", "#", "a", "a" }, "2t, 2t"), Tuple.Create(new[] { "#a", "#a", "#", "#a", "#a" }, "4n"), }; foreach (var test in tests) { var batches = SqlBatch.FormBatches(test.Item1.Select(sql => sql.Replace("#", SqlUtility.NoTransactionTag))); string report = TestUtility.Dump(batches, batch => batch.Count + (batch.UseTransacion ? "t" : "n")); Assert.AreEqual(test.Item2, report, "Test: " + TestUtility.Dump(test.Item1) + "."); } }
/// <summary> /// Gets the update SQL by where. /// </summary> public static SqlBatch GetUpdateSqlByWhere <T>(this IDataHelper dh, T model, bool isNullMeansIgnore, string whereCondition, params Params[] paramKeyAndValue) { var type = typeof(T); var tableName = AttributeHelper.GetTableName(type, true, dh.Driver.SafeName); var columnNameDic = AttributeHelper.GetProp2ColumnNameDics(dh.Driver.DirverType, type); var modelValues = ReflectHelper.GetPropertyValues(type, model, !isNullMeansIgnore, true, true); List <string> upPropList = new List <string>(); List <Params> paramList = new List <Params>(); if (modelValues.Count == 0) { throw new PropertyNoneValueException() { ModelObject = model }; } foreach (var prop in modelValues) { var key = prop.Key; var columnName = columnNameDic[key]; upPropList.Add(string.Format("{0}=@SysParam{1}", dh.Driver.SafeName(columnName), columnName)); paramList.Add(new Params("SysParam" + columnName, prop.Value)); } if (string.IsNullOrWhiteSpace(whereCondition)) { throw new ArgumentNullException("The parameter 'whereCondition' must have a value."); } var updateSql = string.Join(",", upPropList); var sqlString = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, updateSql, whereCondition); if (paramKeyAndValue != null && paramKeyAndValue.Length > 0) { paramList.AddRange(paramKeyAndValue); } var sql = new SqlBatch(sqlString, paramList.ToArray()); return(sql); }
private void ExecuteBatch(SqlBatch batch) { if (Driver.ServerInfo.Query.Features.HasFlag(QueryFeatures.Batches)) { if (batch.Count > 0) { using (var command = Connection.CreateCommand(batch)) { Console.WriteLine(command.CommandText); command.ExecuteNonQuery(); } } return; } if (batch.Count > 0) { foreach (var query in batch) { using (var command = Connection.CreateCommand((ISqlCompileUnit)query)) { Console.WriteLine(command.CommandText); command.ExecuteNonQuery(); } } } }
public override void Visit(SqlBatch codeObject) { Format(codeObject); }
public override void Visit(SqlBatch codeObject) { base.Visit(codeObject); parameters = referencedVariables.Except(declaredVariables).ToList(); }
static void Main(string[] args) { var dh = Cocon90.Db.Common.Db.GetDataHelper("Sqlite", "Data Source=${app}\\test.db3"); //var dh = Cocon90.Db.Common.Db.GetDataHelper(DbTypeEnum.Mysql,"server=127.0.0.1;port=3306;database=datarepair;uid=root;pwd=123456;"); //var dh = Cocon90.Db.Common.Db.GetDataHelper(DbTypeEnum.SqlServer, "server=.;database=cy-data;uid=sa;pwd=password001!;"); var updsql = dh.GetUpdateSql(new Model.CountryLanguageModel { Code = 23, CodeAndLang = "bbb", Date = DateTime.Now, Percent = 2 }, true, "IsOfficial=1"); dh.CreateOrUpdateTable <Model.CountryLanguageModel>(); var sqlExist = dh.GetInsertIfNotExistPrimeryKeySql <Model.CountryLanguageModel>(new Model.CountryLanguageModel { CodeAndLang = "asdf", Date = DateTime.Now, Code = 099, Percent = 3 }, 23, "Lang"); //测试InsertIgnore var sql = dh.GetInsertIfNotExistSql(new Model.CountryLanguageModel { Code = 23, CodeAndLang = "bbb", Language = "zh", Date = DateTime.Now, Percent = 2 }, "select 1 from CountryLanguage where IsOfficial=1"); var succ = dh.InsertIfNotExist(new Model.CountryLanguageModel { Code = 23, CodeAndLang = "bbb", Language = "zh", Date = DateTime.Now, Percent = 2 }, "select 1 from CountryLanguage where IsOfficial=1"); var createSql = dh.GetCreateTableSql <Model.CountryLanguageModel>(); var updateTabSql = dh.GetUpdateTableSql(typeof(Model.CountryLanguageModel)); var effRow = dh.CreateOrUpdateTable <Model.CountryLanguageModel>(); var needInserts = new List <Model.CountryLanguageModel>(); Random rand = new Random(); for (int i = 0; i < 500; i++) { needInserts.Add(new Model.CountryLanguageModel() { Percent = (decimal)(rand.NextDouble() * 10), Date = DateTime.Now.AddDays(-1 * i), Guid = Guid.NewGuid(), IsOfficial = rand.Next(0, 2) > 0, Code = i, Language = "Lang_" + i }); } var succRows = dh.Save(needInserts.ToArray()); dh.GetTable("SELECT * FROM countrylanguage"); var lst = dh.GetList <Model.CountryLanguageModel>("SELECT * FROM countrylanguage"); Console.WriteLine(lst.Count); var oneModel = dh.GetOne <Model.CountryLanguageModel>("select * from countrylanguage"); var oneModel2 = dh.GetOneByPrimaryKey <Model.CountryLanguageModel>(1, "Lang_1"); //var successRows = dh.Insert(new Model.CountryLanguage() { Percent = 1.555m, IsOfficial = false, Code = 2, Language = "Lang" }, // new Model.CountryLanguage() { Percent = 1.66m, IsOfficial = true, Code = 3, Language = "Lang" }); var updateSql = dh.GetUpdateSqlByPrimaryKey(new Model.CountryLanguageModel() { Percent = 9.9m }, true, "1=1 AND 2=2", 3, "Lang"); var updateSql2 = dh.GetUpdateSql(new Model.CountryLanguageModel { Code = 3, Percent = 3.3m }, false, null); var updateSql3 = dh.GetUpdateSqlByWhere(new Model.CountryLanguageModel { Code = 3, Percent = 3.3m }, true, "Language='Lang'", new Common.Data.Params("@Name", "song")); var updateRow3 = dh.UpdateByByWhere(new Model.CountryLanguageModel { Percent = 3.3m }, true, "Language='Lang'"); var updateRow = dh.UpdateByPrimaryKey(new Model.CountryLanguageModel { Percent = 4.5m }, true, null, 3, "Lang"); var deleteSql = dh.GetDeleteSqlByPrimaryKey <Model.CountryLanguageModel>("1=1", 3, "Lang"); var deleteSql1 = dh.GetDeleteSqlByPrimaryKey <Model.CountryLanguageModel>(null, 3, "Lang_111"); var deleteSql2 = dh.GetDeleteSqlByWhere <Model.CountryLanguageModel>("Percentage=@Perc", new Common.Data.Params("Perc", 100)); var deleteSql3 = dh.GetDeleteSql(new Model.CountryLanguageModel { Code = 3, Percent = 3.3m }, "1=@myParam", new Common.Data.Params("myParam", 1)); var deleteSql4 = dh.GetDeleteSql <Model.CountryLanguageModel>(null, "1=@myParam", new Common.Data.Params("myParam", 1)); var successRow = dh.Delete(new Model.CountryLanguageModel { Code = 3, Percent = 4.5m }); var saveSql = dh.GetSaveSql(new Model.CountryLanguageModel() { Percent = 1.555m, IsOfficial = false, Code = 2, Language = "Lang" }, new Model.CountryLanguageModel() { Percent = 1.66m, IsOfficial = true, Code = 3, Language = "Lang" }); var saveRows = dh.Save(new Model.CountryLanguageModel() { Percent = 1.555m, IsOfficial = false, Code = 2, Language = "Lang" }, new Model.CountryLanguageModel() { Percent = 1.66m, IsOfficial = true, Code = 3, Language = "Lang" }); var executeNoQuery = dh.ExecNoQuery("update countrylanguage set Percentage=4.4 where Percentage=@Percent", new Model.CountryLanguageModel { Percent = 1.6m }); var executeNoQuery2 = dh.ExecNoQuery("update countrylanguage set Percentage=4.4 where Percentage=@Percent", new { Percent = 1.6m }); var pageSql = dh.Driver.GetPagedSql("select * from countrylanguage", "CountryCode", true, 1, 10); var pageResult = dh.GetPagedResult <Model.CountryLanguageModel>("select * from countrylanguage", "countrycode", true, 1, 10); List <Model.CountryLanguageModel> data = pageResult.Data; int totalRecordCount = pageResult.Total; int pageNum = pageResult.PageNumber; int pageSize = pageResult.PageSize; var sql1 = new SqlBatch("update countrylanguage set Percentage=4.4 where 1=2"); var sql2 = new SqlBatch("update countrylanguage set Percentage=4.4 where 1=4"); var sql3 = new SqlBatch("update countrylanguage set Percentage=4.4 where 1=6"); dh.ExecBatch(new SqlBatch[] { sql1, sql2, sql3 }, true); }
public void Visit(SqlBatch node) { }
/// <summary> /// Called when the parser has a complete batch to process. /// </summary> /// <param name="batch">The batch to process.</param> /// <param name="numberOfExecutions">The number of times to execute the batch (e.g. <c>GO 2</c> to execute the batch twice.</param> /// <remarks> /// If the current error mode (as set by <c>:ON ERROR</c>) is IGNORE, then any <see cref="SqlException" /> should be caught and /// sent to the STDERR channel, else it should be thrown and the client should handle it. /// </remarks> /// <inheritdoc /> public virtual void ProcessBatch(SqlBatch batch, int numberOfExecutions) { if (this.arguments.ParseOnly) { return; } var sql = batch.Sql; if (string.IsNullOrWhiteSpace(sql)) { // Batch is empty. Don't round-trip the SQL server return; } // Get the query timeout var queryTimeout = 0; if (int.TryParse(this.variableResolver.ResolveVariable("SQLCMDSTATTIMEOUT"), out var t)) { queryTimeout = t; } try { // For each execution (numeric argument to GO) for (var i = 0; i < numberOfExecutions; ++i) { // Reset try count var numTries = 0; // Loop till command succeeds, non-retryable error or retry count exceeded while (true) { ++numTries; try { using (var command = this.connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = sql; command.CommandTimeout = queryTimeout; if (this.resultsAs == OutputAs.None) { command.ExecuteNonQuery(); } else { this.OutputWithResults(command); } // Success - exit while loop. break; } } catch (SqlException ex) { if (!IsRetriableError(ex) || numTries >= this.arguments.RetryCount) { // Can't retry this command // Exit both the while loop and the go count as it will always fail. throw; } } } } } catch (SqlException e) { e.AddContextData(batch); this.SqlExceptions.Add(e); this.WriteStderrMessage(e.Format()); if (this.ErrorAction == ErrorAction.Exit) { throw; } // Indicate that errors have occurred during processing and continue this.arguments.ExitCode = 1; } }
public void CreateParser_DoesNotCrash() { SqlBatch b = new SqlBatch(""); Assert.IsNotNull(b, "should be able to create batch."); }
public override void Visit(SqlBatch codeObject) { }
public override void ExplicitVisit(SqlBatch node) { var nodeAnalyse = node.Analyse; var declarationScope = this._DBScope.CreateChildDeclarationScope("Declaration", null); this.currentScopeRef.Push(declarationScope); var batchScope = declarationScope.CreateChildScope("TSqlBatch", null); nodeAnalyse.SqlCodeScope = batchScope; this.currentScopeRef.Push(batchScope); var node_Statements = node.Statements; /* * SqlCodeScope scopeLastStatement = batchScope; */ for (int i = 0, count = node_Statements.Count; i < count; i++) { var statement = node_Statements[i]; /* * SqlCodeScope scopeStatement = null; * if (scopeLastStatement.HasContent) { * scopeStatement = scopeLastStatement.CreateNextScope(statement.GetType().Name, null); * this._CurrentScope.Pop(); * this._CurrentScope.Push(scopeStatement); * this.currentScope = scopeStatement; * scopeLastStatement = scopeStatement; * } */ statement.Accept(this); } this._AnalyseResults.Add(new AnalyseResult() { DeclarationScope = declarationScope, /* * LastScope = scopeLastStatement, */ LastScope = this.currentScopeRef.Current, SqlCodeResult = null, SqlCodeType = null, Fragment = node }); var pop1 = this.currentScopeRef.Pop(); /* * System.Diagnostics.Debug.Assert(ReferenceEquals(scopeLastStatement, pop1), "last statement"); */ var pop2 = this.currentScopeRef.Pop(); System.Diagnostics.Debug.Assert(ReferenceEquals(declarationScope, pop2), "db scope"); /* * var node_Statements = node.Statements; * for (int i = 0, count = node_Statements.Count; i < count; i++) { * var statement = node_Statements[i]; * this.ExplicitVisit(statement); * } * this._Scopes.Pop(); * this.currentScope = this._Scopes.Peek(); */ }