static void Main(string[] args)
{
var builder = new SQLQueryBuilder();
var selectQuery = builder.
Select("students.name", "age", "major", "course", "grade").
Distinct().
From("students").
InnerJoin("grades", ("students.name", "grades.name")).
Where("students.name", "grades.name").AND("students.name", "Markus").OR("students.name", "Joji").AND("age", "99").
OrderBy("students.name").
FinishQuery();
Console.WriteLine(selectQuery + "\r\n");
var updateQuery = builder.
Update("students").
Set(("age", "23")).
Where("name", "markus").
FinishQuery();
var selectQuery2 = builder.
Select("students.name", "course", "grade", "age", "major").
Distinct().
From("students", "grades").
Where("students.name", "grades.name").
OrderBy("major").
FinishQuery();
Console.WriteLine(updateQuery + "\r\n");
Console.WriteLine(selectQuery2 + "\r\n");
//string connectionString = @"Server=(localdb)\MyInstance;Initial Catalog = Local;Integrated Security=true;";
//bool usePostgress = false;
//ExecuteQuery(usePostgress, connectionString, updateQuery);
//ExecuteQuery(usePostgress, connectionString, selectQuery);
//ExecuteQuery(usePostgress, connectionString, selectQuery2);
}
/// <summary>
/// Updates the given columns with the given id in the first column.
/// Each row of rowsToUpdate must have the same size as rowsData.
/// </summary>
/// <param name="tableName">The table where rows should be updated.</param>
/// <param name="rowsToUpdate">The rows with the name and data type to update.</param>
/// <param name="rowsData">The rows with all column data which should be updated.</param>
public override void UpdateTable(string tableName, List <Dictionary <string, Type> > rowsToUpdate, List <List <object> > rowsData)
{
int rowIter = 0;
List <string> queryList = new List <string>();
foreach (Dictionary <string, Type> row in rowsToUpdate)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <object> columnData = rowsData[rowIter];
List <string> columns = new List <string>();
if (!row.Count.Equals(columnData.Count))
{
throw new InvalidDataException(rowIter.ToString() + ". row size from rowsToUpdate doesn't match current row size from rowsData");
}
int columnIter = 0;
foreach (KeyValuePair <string, Type> column in row)
{
if (columnIter.Equals(0))
{
columnIter++;
continue;
}
if (!columnData[columnIter].GetType().Equals(column.Value))
{
throw new TypeLoadException("Type of the data doesn't match the columns type!");
}
if (column.Value == typeof(int))
{
sqb.AddValue(columnData[columnIter].ToString());
}
else if (column.Value == typeof(string))
{
sqb.Apostrophe(sqb.AddValue(columnData[columnIter].ToString()).Flush());
}
else if (column.Value == typeof(double))
{
sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(float))
{
sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(DateTime))
{
DateTime convertTime = (DateTime)columnData[columnIter];
sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
string value = sqb.Flush();
sqb.AddValue(column.Key).Equal().AddValue(value);
columns.Add(sqb.Flush());
columnIter++;
}
sqb.Update().AddValue(tableName).Set().AddValues(columns).Where().AddValue(row.First().Key).Equal().AddValue(columnData[0].ToString());
queryList.Add(sqb.ToString());
rowIter++;
}
CommitBatchQuery(queryList);
}