/// <summary>
/// Drop table if it exists for a given class.
/// </summary>
/// <param name="t">Class for which a table should be dropped.</param>
public void DropTable(Type t)
{
if (!_Initialized)
{
throw new InvalidOperationException("Initialize WatsonORM and database using the .InitializeDatabase() method first.");
}
if (t == null)
{
throw new ArgumentNullException(nameof(t));
}
string tableName = _TypeMetadataMgr.GetTableNameFromType(t);
_Logger?.Invoke(_Header + "dropping table " + tableName + " for type " + t.Name);
_Database.DropTable(tableName);
_Logger?.Invoke(_Header + "dropped table " + tableName);
}
static void Main(string[] args)
{
Console.WriteLine("Opening file");
DatabaseClient sql = new DatabaseClient("test");
sql.Logger = Logger;
sql.LogQueries = true;
sql.LogResults = true;
Console.WriteLine("Creating table...");
List <Column> columns = new List <Column>();
columns.Add(new Column("id", true, DataType.Integer, false));
columns.Add(new Column("name", false, DataType.Text, true));
columns.Add(new Column("postal", false, DataType.Integer, true));
sql.CreateTable("company", columns);
Console.WriteLine("Checking if table 'company' exists: " + sql.TableExists("company"));
Console.WriteLine("Retrieving list of tables...");
List <string> tableNames = sql.ListTables();
if (tableNames != null && tableNames.Count > 0)
{
foreach (string curr in tableNames)
{
Console.WriteLine(" " + curr);
}
}
Console.WriteLine("Describing database...");
Dictionary <string, List <Column> > describeResult = sql.DescribeDatabase();
if (describeResult != null && describeResult.Count > 0)
{
foreach (KeyValuePair <string, List <Column> > curr in describeResult)
{
Console.WriteLine(" Table " + curr.Key);
if (curr.Value != null && curr.Value.Count > 0)
{
foreach (Column col in curr.Value)
{
Console.WriteLine(" " + col.ToString());
}
}
}
}
Console.WriteLine("Describing table 'company'...");
List <Column> cols = sql.DescribeTable("company");
if (cols != null && cols.Count > 0)
{
foreach (Column curr in cols)
{
Console.WriteLine(" " + curr.ToString());
}
}
Console.WriteLine("Adding data...");
Dictionary <string, object> d1 = new Dictionary <string, object>();
d1.Add("name", "company 1");
d1.Add("postal", 95128);
Dictionary <string, object> d2 = new Dictionary <string, object>();
d2.Add("name", "company 2");
d2.Add("postal", 62629);
Dictionary <string, object> d3 = new Dictionary <string, object>();
d3.Add("name", "company 3");
d3.Add("postal", 10101);
Dictionary <string, object> d4 = new Dictionary <string, object>();
d4.Add("name", "company 4");
d4.Add("postal", 90210);
Console.WriteLine("Created ID: " + sql.Insert("company", d1).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d2).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d3).ToString());
Console.WriteLine("Created ID: " + sql.Insert("company", d4).ToString());
Console.WriteLine("Selecting companies with postal > 70000...");
Expression eRetrieve1 = new Expression("postal", Operators.GreaterThan, 70000);
DataTable selectResult1 = sql.Select("company", 0, null, null, eRetrieve1, null);
Console.WriteLine("Retrieved " + selectResult1.Rows.Count + " rows");
Console.WriteLine("Selecting companies with postal > 70000 or postal < 50000...");
Expression eRetrieve2 = new Expression
{
LeftTerm = new Expression("postal", Operators.GreaterThan, 70000),
Operator = Operators.Or,
RightTerm = new Expression("postal", Operators.LessThan, 50000)
};
DataTable selectResult2 = sql.Select("company", 0, null, null, eRetrieve2, null);
Console.WriteLine("Retrieved " + selectResult2.Rows.Count + " rows");
Console.WriteLine("Deleting records...");
Expression eDelete = new Expression("id", Operators.GreaterThan, 0);
sql.Delete("company", eDelete);
Console.WriteLine("Dropping table...");
sql.DropTable("company");
}
static void Main(string[] args)
{
try
{
#region Select-Database-Type
/*
*
*
* Create the database 'test' before proceeding if using mssql, mysql, or pgsql
*
*
*/
Console.Write("User: "******"Password: "******"localhost", 1433, user, pass, null, "test");
_Database = new DatabaseClient(_Settings);
_Database.Logger = Logger;
_Database.LogQueries = true;
_Database.LogResults = true;
#endregion
#region Drop-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Dropping table '" + _Table + "'...");
_Database.DropTable(_Table);
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Create-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Creating table '" + _Table + "'...");
List <Column> columns = new List <Column>();
columns.Add(new Column("id", true, DataType.Int, 11, null, false));
columns.Add(new Column("firstname", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("lastname", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("age", false, DataType.Int, 11, null, true));
columns.Add(new Column("value", false, DataType.Long, 12, null, true));
columns.Add(new Column("birthday", false, DataType.DateTime, null, null, true));
columns.Add(new Column("hourly", false, DataType.Decimal, 18, 2, true));
_Database.CreateTable(_Table, columns);
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Check-Existence-and-Describe
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Table '" + _Table + "' exists: " + _Database.TableExists(_Table));
Console.WriteLine("Table '" + _Table + "' configuration:");
columns = _Database.DescribeTable(_Table);
foreach (Column col in columns)
{
Console.WriteLine(col.ToString());
}
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Load-Update-Retrieve-Delete
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Loading rows...");
LoadRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Loading multiple rows...");
LoadMultipleRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Checking existence...");
ExistsRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Counting age...");
CountAge();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Summing age...");
SumAge();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Updating rows...");
UpdateRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows...");
RetrieveRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows by index...");
RetrieveRowsByIndex();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows by between...");
RetrieveRowsByBetween();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving sorted rows...");
RetrieveRowsSorted();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Deleting rows...");
DeleteRows();
Console.WriteLine("Press ENTER to continue");
Console.ReadLine();
#endregion
#region Cause-Exception
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Testing exception...");
try
{
_Database.Query("SELECT * FROM " + _Table + "(((");
}
catch (Exception e)
{
Console.WriteLine("Caught exception: " + e.Message);
Console.WriteLine("Query: " + e.Data["Query"]);
}
#endregion
#region Drop-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Dropping table...");
_Database.DropTable(_Table);
Console.ReadLine();
#endregion
}
catch (Exception e)
{
ExceptionConsole("Main", "Outer exception", e);
}
}
static void Main(string[] args)
{
try
{
#region Setup-Database
Console.Write("Filename: ");
string filename = Console.ReadLine();
if (String.IsNullOrEmpty(filename))
{
return;
}
_Settings = new DatabaseSettings(filename);
_Database = new DatabaseClient(_Settings);
_Database.Logger = Logger;
_Database.LogQueries = true;
_Database.LogResults = true;
#endregion
#region Drop-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Dropping table 'person'...");
_Database.DropTable("person");
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Create-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Creating table 'person'...");
List <Column> columns = new List <Column>();
columns.Add(new Column("id", true, DataType.Int, 11, null, false));
columns.Add(new Column("firstName", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("lastName", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("age", false, DataType.Int, 11, null, true));
columns.Add(new Column("value", false, DataType.Long, 12, null, true));
columns.Add(new Column("birthday", false, DataType.DateTime, null, null, true));
columns.Add(new Column("hourly", false, DataType.Decimal, 18, 2, true));
_Database.CreateTable("person", columns);
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Check-Existence-and-Describe
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Table 'person' exists: " + _Database.TableExists("person"));
Console.WriteLine("Table 'person' configuration:");
columns = _Database.DescribeTable("person");
foreach (Column col in columns)
{
Console.WriteLine(col.ToString());
}
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Load-Update-Retrieve-Delete
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Loading rows...");
LoadRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Updating rows...");
UpdateRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows...");
RetrieveRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows by index...");
RetrieveRowsByIndex();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows by between...");
RetrieveRowsByBetween();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Deleting rows...");
DeleteRows();
Console.WriteLine("Press ENTER to continue");
Console.ReadLine();
#endregion
#region Drop-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Dropping table...");
_Database.DropTable("person");
Console.ReadLine();
#endregion
}
catch (Exception e)
{
ExceptionConsole("Main", "Outer exception", e);
}
}
static void Main(string[] args)
{
try
{
#region Select-Database-Type
/*
*
*
* Create the database 'test' before proceeding
*
*
*/
Console.Write("DB type [mssql|mysql|pgsql]: ");
string dbType = Console.ReadLine();
if (String.IsNullOrEmpty(dbType))
{
return;
}
Console.Write("User: "******"Password: "******"mssql":
client = new DatabaseClient(DbTypes.MsSql, "localhost", 1433, user, password, null, "test");
break;
case "mysql":
client = new DatabaseClient(DbTypes.MySql, "localhost", 3306, user, password, null, "test");
break;
case "pgsql":
client = new DatabaseClient(DbTypes.PgSql, "localhost", 5432, user, password, null, "test");
break;
default:
return;
}
client.DebugRawQuery = true;
client.DebugResultRowCount = true;
#endregion
#region Drop-Table
Console.WriteLine("Dropping table 'person'...");
client.DropTable("person");
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Create-Table
Console.WriteLine("Creating table 'person'...");
List <Column> columns = new List <Column>();
columns.Add(new Column("id", true, DataType.Int, 11, null, false));
columns.Add(new Column("firstName", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("lastName", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("age", false, DataType.Int, 11, null, true));
columns.Add(new Column("value", false, DataType.Long, 12, null, true));
columns.Add(new Column("birthday", false, DataType.DateTime, null, null, true));
columns.Add(new Column("hourly", false, DataType.Decimal, 18, 2, true));
client.CreateTable("person", columns);
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Check-Existence-and-Describe
Console.WriteLine("Table 'person' exists: " + client.TableExists("person"));
Console.WriteLine("Table 'person' configuration:");
columns = client.DescribeTable("person");
foreach (Column col in columns)
{
Console.WriteLine(col.ToString());
}
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Load-Update-Retrieve-Delete
Console.WriteLine("Loading rows...");
LoadRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Updating rows...");
UpdateRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Retrieving rows...");
RetrieveRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Retrieving rows by index...");
RetrieveRowsByIndex();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
Console.WriteLine("Deleting rows...");
DeleteRows();
Console.WriteLine("Press ENTER to continue");
Console.ReadLine();
#endregion
#region Drop-Table
Console.WriteLine("Dropping table...");
client.DropTable("person");
Console.ReadLine();
#endregion
}
catch (Exception e)
{
ExceptionConsole("Main", "Outer exception", e);
}
}
static void Main(string[] args)
{
try
{
#region Select-Database-Type
/*
*
*
* Create the database 'test' before proceeding if using mssql, mysql, or pgsql
*
*
*/
Console.Write("DB type [sqlserver|mysql|postgresql|sqlite]: ");
string dbType = Console.ReadLine();
if (String.IsNullOrEmpty(dbType))
{
return;
}
dbType = dbType.ToLower();
if (dbType.Equals("sqlserver") || dbType.Equals("mysql") || dbType.Equals("postgresql"))
{
Console.Write("User: "******"Password: "******"sqlserver":
_Settings = new DatabaseSettings("localhost", 1433, user, pass, null, "test");
_Database = new DatabaseClient(_Settings);
break;
case "mysql":
_Settings = new DatabaseSettings(DbTypes.Mysql, "localhost", 3306, user, pass, "test");
_Database = new DatabaseClient(_Settings);
break;
case "postgresql":
_Settings = new DatabaseSettings(DbTypes.Postgresql, "localhost", 5432, user, pass, "test");
_Database = new DatabaseClient(_Settings);
break;
default:
return;
}
}
else if (dbType.Equals("sqlite"))
{
Console.Write("Filename: ");
string filename = Console.ReadLine();
if (String.IsNullOrEmpty(filename))
{
return;
}
_Settings = new DatabaseSettings(filename);
_Database = new DatabaseClient(_Settings);
}
else
{
Console.WriteLine("Invalid database type.");
return;
}
_Database.Logger = Logger;
_Database.LogQueries = true;
_Database.LogResults = true;
#endregion
#region Drop-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Dropping table 'person'...");
_Database.DropTable("person");
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Create-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Creating table 'person'...");
List <Column> columns = new List <Column>();
columns.Add(new Column("id", true, DataType.Int, 11, null, false));
columns.Add(new Column("firstName", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("lastName", false, DataType.Nvarchar, 30, null, false));
columns.Add(new Column("age", false, DataType.Int, 11, null, true));
columns.Add(new Column("value", false, DataType.Long, 12, null, true));
columns.Add(new Column("birthday", false, DataType.DateTime, null, null, true));
columns.Add(new Column("hourly", false, DataType.Decimal, 18, 2, true));
_Database.CreateTable("person", columns);
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Check-Existence-and-Describe
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Table 'person' exists: " + _Database.TableExists("person"));
Console.WriteLine("Table 'person' configuration:");
columns = _Database.DescribeTable("person");
foreach (Column col in columns)
{
Console.WriteLine(col.ToString());
}
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
#endregion
#region Load-Update-Retrieve-Delete
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Loading rows...");
LoadRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Updating rows...");
UpdateRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows...");
RetrieveRows();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows by index...");
RetrieveRowsByIndex();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Retrieving rows by between...");
RetrieveRowsByBetween();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Deleting rows...");
DeleteRows();
Console.WriteLine("Press ENTER to continue");
Console.ReadLine();
#endregion
#region Drop-Table
for (int i = 0; i < 24; i++)
{
Console.WriteLine("");
}
Console.WriteLine("Dropping table...");
_Database.DropTable("person");
Console.ReadLine();
#endregion
}
catch (Exception e)
{
ExceptionConsole("Main", "Outer exception", e);
}
}
static async Task DeleteTable(RequestMetadata md)
{
string dbName = md.Http.Request.Url.Elements[0];
string tableName = md.Http.Request.Url.Elements[1];
int idVal = 0;
if (md.Http.Request.Url.Elements.Length == 3)
{
Int32.TryParse(md.Http.Request.Url.Elements[2], out idVal);
}
Table currTable = _Databases.GetTableByName(dbName, tableName);
if (currTable == null)
{
md.Http.Response.StatusCode = 404;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(SerializationHelper.SerializeJson(new ErrorResponse(ErrorCodeEnum.NotFound, "The requested object was not found", null), true));
return;
}
DatabaseClient db = _Databases.GetDatabaseClient(dbName);
if (db == null)
{
md.Http.Response.StatusCode = 404;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(SerializationHelper.SerializeJson(new ErrorResponse(ErrorCodeEnum.NotFound, "The requested object was not found", null), true));
return;
}
if (md.Params.Truncate)
{
#region Truncate
db.Truncate(tableName);
_Logging.Warn("DeleteTable truncated table " + tableName + " in database " + dbName);
md.Http.Response.StatusCode = 204;
await md.Http.Response.Send();
return;
#endregion
}
else if (md.Params.Drop)
{
#region Drop
db.DropTable(tableName);
_Logging.Warn("DeleteTable dropped table " + tableName + " in database " + dbName);
md.Http.Response.StatusCode = 204;
await md.Http.Response.Send();
return;
#endregion
}
else if (md.Http.Request.Url.Elements.Length >= 2)
{
#region Delete-Objects
Expr filter = null;
if (idVal > 0)
{
if (String.IsNullOrEmpty(currTable.PrimaryKey))
{
_Logging.Warn("DeleteTable no primary key defined for table " + tableName + " in database " + dbName);
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(SerializationHelper.SerializeJson(new ErrorResponse(ErrorCodeEnum.InvalidRequest, "Invalid request", "No primary key for table " + tableName), true));
return;
}
filter = new Expr(currTable.PrimaryKey, OperatorEnum.Equals, idVal);
}
if (md.Http.Request.Query.Elements != null && md.Http.Request.Query.Elements.Count > 0)
{
foreach (KeyValuePair <string, string> currKvp in md.Http.Request.Query.Elements)
{
if (Constants.QueryKeys.Contains(currKvp.Key))
{
continue;
}
if (filter == null)
{
filter = new Expr(currKvp.Key, OperatorEnum.Equals, currKvp.Value);
}
else
{
filter.PrependAnd(currKvp.Key, OperatorEnum.Equals, currKvp.Value);
}
}
}
db.Delete(tableName, filter);
md.Http.Response.StatusCode = 204;
await md.Http.Response.Send();
return;
#endregion
}
}