Beispiel-Code

[Josef Pötzl]

Unter VBA eine SQL-Anweisung erzeugen:

Code: Alles auswählen

Dim SqlAnweisung As String

SqlAnweisung = SqlTools.SqlGenerator(SqlTools.SqlConverters.DaoSqlConverter) _
               .Select("F1", "F2").SelectField("Count(*)", "", "Anzahl") _
               .From("Tab1") _
               .Where("F3", RelationalOperators_Equal + RelationalOperators_GreaterThan, 5) _
               .GroupBy("F1", "F2") _
               .HavingString("Count(*) > 1") _
               .ToString()

=> Select F1, F2, Count(*) As Anzahl From Tab1 Where (F3 >= 5) Group By F1, F2 Having (Count(*) > 1)
 

MIt ConditionGenerator für komplexere Where-Verkettungen (und/oder gemischt u. ä.):

Code: Alles auswählen

Dim CondGen As ACLibSqlTools.ConditionGenerator

Set CondGen = SqlTools.ConditionGenerator
With CondGen
   .Add "F1", RelationalOperators_Equal, 1
   .BeginGroup("F2", RelationalOperators_Equal, 2) _
          .Add("F3", RelationalOperators_Equal, 3) _
          .ConcatOperator = LogicalOperator_Or
End With

Dim SqlString As String
SqlString = SqlTools.SqlGenerator(SqlTools.SqlConverters.DaoSqlConverter) _
                     .From("Tabelle1") _
                     .WhereCondition(CondGen) _
                     .SelectAll _
                     .ToString()

=> Select * From Tabelle1 Where ((F1 = 1) And ((F2 = 2) Or (F3 = 3)))

[Sten Schmidt]

Unter C# eine SQL-Anweisung erzeugen:

Code: Alles auswählen


using AccessCodeLib.Data.Common.Sql;
using AccessCodeLib.Data.SqlTools;
using AccessCodeLib.Data.SqlTools.Converter.Mssql;
using AccessCodeLib.Data.SqlTools.Sql;
 

Code: Alles auswählen


            string tabName = "TabA";

            Field f1 = new Field("F1");
            Field f2 = new Field("F2");
            Field f3 = new Field("F3");

            FieldList fields = new FieldList();
            fields.Add(f1);
            fields.Add(f2);
            fields.Add(f3);

            SqlGenerator selectQuery = new SqlGenerator(new SqlConverter());
            selectQuery.Select(fields.ToArray()).From(tabName).Where(f2, RelationalOperators.LessThan, 9);
            selectQuery.GroupBy(f2);
            selectQuery.OrderBy(f1);
           
            string SqlAnweisung = selectQuery.ToString();

=> Select F1, F2, F3 From TabA Where (F2 < 9) Group By F2 Order By F1