protected override List <PersistentQueueItem <T> > MultiLoadImpl(int batch)
{
List <PersistentQueueItem <T> > result = new List <PersistentQueueItem <T> >(batch);
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = "select top " + batch + " Id, Payload, EnqueueTime, Priority, Try from QueueItems where State = 0 order by Priority";
connection.Open();
using (EfzDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(PersistentQueueItem <T> .FromDataReader(reader));
}
}
if (result.Count > 0)
{
StringBuilder ids = new StringBuilder(result[0].Id.ToString());
for (int i = 1; i < result.Count; i++)
{
ids.Append(",");
ids.Append(result[i].Id);
}
command.CommandText = "update QueueItems set State = 1 where Id in (" + ids.ToString() + ");";
command.ExecuteNonQuery();
}
}
return(result);
}
private void btnMem_Click(object sender, RoutedEventArgs e)
{
string connectionString = "connection type=MEMORY; initial catalog=TestDb; user=SA; password="******"CREATE TABLE Test(ID int, Name varchar(100));";
EfzCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "INSERT INTO Test(ID , Name ) VALUES(1,'Car');";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM TEST;";
cmd.CommandText = sql;
EfzDataReader reader = cmd.ExecuteReader();
reader.Read();
tbkText.Text = String.Format("ID = {0}, Name = {1} ", reader.GetInt32(0), reader.GetString(1));
}
}
private void btnMem_Click(object sender, RoutedEventArgs e)
{
string connectionString = "connection type=MEMORY; initial catalog=TestDb; user=SA; password="******"CREATE TABLE Test(ID int, Name varchar(100));";
EfzCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "INSERT INTO Test(ID , Name ) VALUES(1,'Car');";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM TEST;";
cmd.CommandText = sql;
EfzDataReader reader = cmd.ExecuteReader();
reader.Read();
tbkText.Text = String.Format("ID = {0}, Name = {1} ", reader.GetInt32(0), reader.GetString(1));
}
}
protected override void RemoveImpl(PersistentQueueItem <T> item)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = "delete from QueueItems where Id = " + item.Id;
connection.Open();
command.ExecuteNonQuery();
}
}
protected override void FailImpl(PersistentQueueItem <T> item)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = string.Format("update QueueItems set Priority = {0}, Try = {1}, State = 0 where Id = {2}", item.Priority, item.Try, item.Id);
connection.Open();
command.ExecuteNonQuery();
}
}
protected override void PurgeImpl()
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = EfzQueuePersistence <T> .purgeSql;
connection.Open();
command.ExecuteNonQuery();
}
}
public override void DoCreateFile(string filename)
{
string conns = EfzTool.GetConnectionString(filename, EfzLogin, EfzPassword);
using (DbConnection conn = new EfzConnection(conns))
{
conn.Open();
conn.Close();
}
}
protected override void DiscardImpl(PersistentQueueItem <T> item)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
string sql = string.Format("delete from QueueItems where Id = {0}; insert into DiscardQueueItems(Id, Payload, EnqueueTime) values({0}, '{1}', '{2}');", item.Id, item.PayloadToJson(), item.EnqueueTime.ToString("yyyy-MM-dd HH:mm:ss"));
EfzCommand command = connection.CreateCommand();
command.CommandText = sql;
connection.Open();
command.ExecuteNonQuery();
}
}
protected override void MultiSaveImpl(params PersistentQueueItem <T>[] items)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
StringBuilder sql = new StringBuilder();
for (int i = 0; i < items.Length; i++)
{
PersistentQueueItem <T> item = items[i];
sql.Append(string.Format("insert into QueueItems(Payload, EnqueueTime, Priority, Try, State) values('{0}', '{1}', {2}, 0, 0);", item.PayloadToJson().Replace("'", "''"), item.EnqueueTime.ToString("yyyy-MM-dd HH:mm:ss"), item.Priority));
}
EfzCommand command = connection.CreateCommand();
command.CommandText = sql.ToString();
connection.Open();
command.ExecuteNonQuery();
}
}
protected override int Init(string persistPath)
{
this.connString = EfzQueuePersistence <T> .GetConnectionString(persistPath);
int result;
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = EfzQueuePersistence <T> .databaseSql;
connection.Open();
command.ExecuteNonQuery();
command.CommandText = EfzQueuePersistence <T> .restoreAndCountSql;
result = (int)command.ExecuteScalar();
}
return(result);
}
public static bool EnsureDBConnection()
{
if(m_conDB != null) { //already existing
if(m_conDB.State != ConnectionState.Open) {
m_conDB.Open();
}
return (true);
}
if(!m_bDidCheckFiles) {
if(!EnsureDBInIsolatedStorage()) {
return (false);
}
}
m_conDB = new EfzConnection(m_strConnection);
m_conDB.Open();
return (true);
}
/// <summary>
/// Notice:
/// Because OleDB Suppor many db types .
/// So the UsingOleDbType property in LoginInfo should be specifed. otherwise will use default db type -->Here is Access
/// </summary>
/// <param name="pInfo"></param>
public override void Open(BaseLoginInfo pInfo)
{
//Record to base class (Vital)
baseLoginInfo = pInfo;
LoginInfo_Effiproz myInfo = pInfo as LoginInfo_Effiproz;
LoginInfo_ForAllDbTypes allInfo = pInfo as LoginInfo_ForAllDbTypes;
if ((myInfo == null) && (allInfo == null))
{
throw new ArgumentException("Only Support Effiproz login info and AllDBTypes Info");
}
if (IsOpened)
{
return;
}
string myConnString = string.Empty;
try
{
if (allInfo != null)
{
myInfo = new LoginInfo_Effiproz();
myInfo.InitialCatalog = allInfo.Database;
myInfo.Username = allInfo.Username;
myInfo.Password = allInfo.Pwd;
}
myConnString = ConnSTR.DbConnectionString.EffiprozConnStr.GetConnectionString(myInfo.DBConnectionType, myInfo.InitialCatalog,
myInfo.Username, myInfo.Password);
baseConn = new EfzConnection(myConnString);
baseConn.Open();
invalidator = new InvalidatorEffiproz();
CurDatabase = myInfo.InitialCatalog;
CurPwd = myInfo.Password;
}
catch (Exception e)
{
throw e;
}
}
static void Main(string[] args)
{
string connString = "Connection Type=File ; Initial Catalog=/Storage Card/TestDB; User=sa; Password=;"; //for file DB
//string connString = "Connection Type=Memory ; Initial Catalog=TestDB; User=sa; Password=;";
using (DbConnection conn = new EfzConnection(connString))
{
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE Test(ID INT PRIMARY KEY, Name VARCHAR(100));";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Test(ID , Name) VALUES(@ID , @Name);";
DbParameter id = command.CreateParameter();
id.ParameterName = "@ID";
id.Value = 1;
command.Parameters.Add(id);
DbParameter name = command.CreateParameter();
name.ParameterName = "@NAME";
name.Value = "Van";
command.Parameters.Add(name);
command.ExecuteNonQuery();
id.Value = 2;
name.Value = "Car";
command.ExecuteNonQuery();
command.CommandText = "SELECT * FROM TEST;";
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
System.Console.WriteLine(String.Format("ID= {0} , Name= {1}",
reader.GetInt32(0), reader.GetString(1)));
}
Console.WriteLine("Press Any Key to Continue...");
}
}
static void Main(string[] args)
{
string connString = "Connection Type=File ; Initial Catalog=/Storage Card/TestDB; User=sa; Password=;"; //for file DB
//string connString = "Connection Type=Memory ; Initial Catalog=TestDB; User=sa; Password=;";
using (DbConnection conn = new EfzConnection(connString))
{
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE Test(ID INT PRIMARY KEY, Name VARCHAR(100));";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Test(ID , Name) VALUES(@ID , @Name);";
DbParameter id = command.CreateParameter();
id.ParameterName = "@ID";
id.Value = 1;
command.Parameters.Add(id);
DbParameter name = command.CreateParameter();
name.ParameterName = "@NAME";
name.Value = "Van";
command.Parameters.Add(name);
command.ExecuteNonQuery();
id.Value = 2;
name.Value = "Car";
command.ExecuteNonQuery();
command.CommandText = "SELECT * FROM TEST;";
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
System.Console.WriteLine(String.Format("ID= {0} , Name= {1}",
reader.GetInt32(0), reader.GetString(1)));
}
Console.WriteLine("Press Any Key to Continue...");
}
}
public override void Open(string connectionString)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new Exception("Invalid connection string");
}
try
{
if (IsOpened)
{
return;
}
baseConn = new EfzConnection(connectionString);
baseConn.Open();
invalidator = new InvalidatorEffiproz();
}
catch (OleDbException ee)
{
throw ee;
}
}
public static void ClrFunctionTest()
{
string connString = "Connection Type=Memory ;Initial Catalog=CLRSampleDB; User=sa; Password=;";
using (DbConnection cnn = new EfzConnection(connString))
{
cnn.Open();
using (DbCommand cmd = cnn.CreateCommand())
{
string sql = "CREATE FUNCTION add_num(x INT, y INT)\n" +
"RETURNS INT\n NO SQL\n" +
"LANGUAGE DOTNET\n EXTERNAL NAME 'ClrRoutines:EffiProz.Samples.ClrRoutines.Add'";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT add_num(3,4) from dual;";
int result = (int)cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
}
}
public static void ClrFunctionTest()
{
string connString = "Connection Type=Memory ;Initial Catalog=CLRSampleDB; User=sa; Password=;";
using (DbConnection cnn = new EfzConnection(connString))
{
cnn.Open();
using (DbCommand cmd = cnn.CreateCommand())
{
string sql = "CREATE FUNCTION add_num(x INT, y INT)\n" +
"RETURNS INT\n NO SQL\n" +
"LANGUAGE DOTNET\n EXTERNAL NAME 'ClrRoutines:EffiProz.Samples.ClrRoutines.Add'";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT add_num(3,4) from dual;";
int result = (int)cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
}
}
public override bool CreateDatabase(BaseLoginInfo loginInfo)
{
bool result = false;
string createCmd = string.Empty;
LoginInfo_Effiproz myInfo = loginInfo as LoginInfo_Effiproz;
Debug.Assert(myInfo != null);
createCmd = ConnSTR.DbConnectionString.EffiprozConnStr.GetConnectionString(myInfo.DBConnectionType, myInfo.InitialCatalog, myInfo.Username, myInfo.Password);
try
{
EfzConnection conn = new EfzConnection(createCmd);
conn.Open();
result = true;
}
catch (Exception ee)
{
Debug.Write(ee.Message);
}
return(result);
}
internal void CreateDatabase(string filename)
{
var dbname = "Test_" + Guid.NewGuid();
var connectionString = string.Format("Connection Type=File; Initial Catalog={0}; User=sa; Password=;", dbname);
try
{
var content = File.ReadAllText(filename).Replace("\r\n\\", Delimiter.ToString())
.Replace(";\r\n", Delimiter.ToString())
.Replace("*/\r\n", "*/" + Delimiter)
.Replace("\r\n", " ");
Console.WriteLine(content.Length);
using (DbConnection conn = new EfzConnection(connectionString))
{
conn.ConnectionString = connectionString;
conn.Open();
var commands = (from item in content.Split(Delimiter)
let trim = item.Trim()
where !string.IsNullOrEmpty(trim) && !(trim.StartsWith("/*") && trim.EndsWith("*/"))
select trim).ToArray();
foreach (var processingCommand in commands)
{
var command = conn.CreateCommand();
command.CommandText = processingCommand;
command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Trace.WriteLine("Error: " + e.Message);
}
}
internal void CreateDatabase(string filename)
{
var dbname = "Test_" + Guid.NewGuid();
var connectionString = string.Format("Connection Type=File; Initial Catalog={0}; User=sa; Password=;", dbname);
try
{
var content = File.ReadAllText(filename).Replace("\r\n\\", Delimiter.ToString())
.Replace(";\r\n", Delimiter.ToString())
.Replace("*/\r\n", "*/" + Delimiter)
.Replace("\r\n", " ");
Console.WriteLine(content.Length);
using (DbConnection conn = new EfzConnection(connectionString))
{
conn.ConnectionString = connectionString;
conn.Open();
var commands = (from item in content.Split(Delimiter)
let trim = item.Trim()
where !string.IsNullOrEmpty(trim) && !(trim.StartsWith("/*") && trim.EndsWith("*/"))
select trim).ToArray();
foreach (var processingCommand in commands)
{
var command = conn.CreateCommand();
command.CommandText = processingCommand;
command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Trace.WriteLine("Error: " + e.Message);
}
}
public void Open()
{
m_connection.Open();
}
public void ADOCommandBuilderTest1()
{
string connString = @"Connection Type=File ; Initial Catalog=D:\T2; User=sa; Password=;";
string sql = "CREATE TABLE TEST(ID INT,ID2 INT, NAME VARCHAR(100),ID3 INT,ID4 INT DEFAULT 5, PRIMARY KEY(ID), UNIQUE (ID2), UNIQUE (ID3,ID4));";
using (EfzConnection conn = new EfzConnection(connString))
{
DbCommand command = conn.CreateCommand();
command.CommandText = sql;
conn.Open();
int count = command.ExecuteNonQuery();
//command.CommandText = "INSERT INTO TEST(ID , ID2, NAME ) VALUES(1, 100,'irantha'); INSERT INTO TEST(ID ,ID2, NAME ) VALUES(2, 500,'subash');";
//count = command.ExecuteNonQuery();
//Assert.AreEqual(count, 1);
DataTable tb = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
Assert.IsNotNull(tb);
string tableName = tb.Rows[0]["Table_Name"].ToString();
Assert.AreEqual(tableName, "TEST");
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "ID" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "NAME" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("PRIMARYKEYS", new string[] { null, "PUBLIC", "TEST" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST" });
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST", null, "true" });
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("SCHEMAS");
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("TYPES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("DataTypes");
Assert.IsTrue(tb.Rows.Count > 0);
//PrintDT(tb);
command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TEST";
DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable columnSchemaTbl = reader.GetSchemaTable();
Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsKey"]);
Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
Assert.IsTrue((bool)columnSchemaTbl.Rows[1]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[2]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[3]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[2]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[3]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[4]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[4]["IsKey"]);
Assert.AreEqual("5", columnSchemaTbl.Rows[4][SchemaTableOptionalColumn.DefaultValue]);
command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TEST";
reader = command.ExecuteReader();
columnSchemaTbl = reader.GetSchemaTable();
Assert.IsFalse((bool)columnSchemaTbl.Rows[0]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsUnique"]);
tb = new DataTable("Test");
tb.Load(reader);
tb = conn.GetSchema("METADATACOLLECTIONS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("CharacterSets");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("CheckConstraints");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("Collations");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("Domains");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("ColumnPrivilages");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("PROCEDURES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("PROCEDUREPARAMETERS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TABLEPRIVILEGES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TableConstraints");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TRIGGERS", new string[] { });
Assert.IsTrue(tb.Rows.Count == 0);
tb = conn.GetSchema("VIEWS");
int oldViewCount = tb.Rows.Count;
command = conn.CreateCommand();
command.CommandText = "CREATE VIEW v1 AS SELECT ID,NAME FROM TEST";
command.ExecuteNonQuery();
tb = conn.GetSchema("VIEWS");
Assert.IsTrue(tb.Rows.Count == oldViewCount + 1);
tb = conn.GetSchema("ViewColumns");
Assert.IsTrue(tb.Rows.Count > 0);
command = conn.CreateCommand();
command.CommandText = "CREATE TABLE TEST2(IDD INT,IDD2 INT, NAME VARCHAR(100), FOREIGN KEY(IDD2)" +
" REFERENCES TEST(ID));";
command.ExecuteNonQuery();
tb = conn.GetSchema("EXPORTEDKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("IMPORTEDKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("FOREIGNKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("ForeignKeyColumns");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("RESERVEDWORDS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("RESTRICTIONS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("FUNCTIONS");
tb = conn.GetSchema("FunctionParameters");
// Assert.IsTrue(tb.Rows.Count> 0);
// PrintDT(tb);
}
}
