static void CreateDropDatabase()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("TestDatabase");
u.DropDatabase("TestDatabase");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CreateDropDatabase() Completed");
}
static void CreateTable()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseResultSet[] rs;
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("testdb");
cnn.DatabaseName = "testdb";
cnn.MultipleActiveResultSets = true;
cmd.CommandText = "Create table if not exists testtable (id integer, textvalue text);";
rs = cmd.ExecuteNonQuery();
cmd.CommandText = "Insert Into testtable VALUES (1, 'example 1');";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM testtable;";
rs = cmd.ExecuteReader();
foreach (SQLDatabaseResultSet drs in rs)
{
if (drs != null)
{
if (!string.IsNullOrWhiteSpace(drs.ErrorMessage))
{
Console.WriteLine(drs.ErrorMessage);
}
else
{
for (int r = 0; r < drs.RowCount; r++)
{
for (int c = 0; c < drs.ColumnCount; c++)
{
Console.Write(drs.Columns[c] + "(" + drs.DataTypes[c] + ")");
Console.Write("\t");
}
Console.WriteLine("");
for (int c = 0; c < drs.ColumnCount; c++)
{
Console.Write(drs.Rows[r][c]);
Console.Write("\t");
}
}
Console.WriteLine("");
}
}
}
cmd.CommandText = "DROP TABLE testtable;";
cmd.ExecuteNonQuery();
u.DropDatabase("testdb");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CreateTable() Completed");
}
static void ORMClient()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("ormtestdb");
cnn.DatabaseName = "ormtestdb";
ApplicationUser e = new ApplicationUser();
SQLDatabaseOrmClient <ApplicationUser> orm = new SQLDatabaseOrmClient <ApplicationUser>();
orm.Connection = cnn;
orm.CreateTable(e);
e.Id = 1;
e.Name = "SQLUser";
e.Job = "SQL Developer";
orm.Add(e); // add
ApplicationUser user = orm.GetById(1); //get one by id
Console.WriteLine("Id \t {0} ", user.Id);
Console.WriteLine("Name \t {0} ", user.Name);
Console.WriteLine("Job \t {0} ", user.Job);
user.Job = "New Job";
orm.Update(user);
// Get all
IList <ApplicationUser> userList = orm.GetAll();
//Filter example;
SQLDatabaseOrmClient <ApplicationUser> .Filter <ApplicationUser> f = new SQLDatabaseOrmClient <ApplicationUser> .Filter <ApplicationUser>();
f.Add(x => x.Id, 1);//get user with id of 1
//methods for order by and contains including limiting number of returned rows.
//f.Add(x => x.Name, "SQLUser");
//f.OrderBy(x => x.Name, "DESC");
//f.Contains(x => x.Name, "u");
//f.Limit(10, 10);
//to find use following
IList <ApplicationUser> foundUsers = orm.Find(f).ToList();
//to remove use following
orm.Remove(f);
//remove or drop entire entity
orm.DropTable(user);
u.DropDatabase("ormtestdb");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("ORMClient() Completed");
}