Beispiel-Code

Hilfsmittel zum Erstellen von SQL-Anweisungen

Beispiel-Code

Beitragvon Josef Pötzl » Di 13. Mär 2012, 22:55

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)))
Josef Pötzl
Moderator
 
Beiträge: 760
Registriert: Mo 30. Nov 2009, 10:08
Wohnort: Klagenfurt
Accessversion: 2010 (2013)

Beispiel-Code

Beitragvon Sten Schmidt » Sa 17. Mär 2012, 23:43

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
 
Sten Schmidt
Entwickler
 
Beiträge: 139
Registriert: Do 18. Mär 2010, 22:24
Accessversion: 2007, 2010
Access-Erfahrung: Experte


Zurück zu SqlTools

cron