public DataTable GetProcedures(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateProceduresDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText =
@"SELECT p.*
FROM INFORMATION_SCHEMA.SYSTEM_PROCEDURES p
WHERE PROCEDURE_CAT = '" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["PROCEDURE_CATALOG"] = r["PROCEDURE_CAT"];
row["PROCEDURE_SCHEMA"] = r["PROCEDURE_SCHEM"];
row["PROCEDURE_NAME"] = r["PROCEDURE_NAME"];
row["PROCEDURE_TYPE"] = r["PROCEDURE_TYPE"];
row["DESCRIPTION"] = r["REMARKS"];
}
}
}
finally { }
return(metaData);
}
private void GetCategories()
{
try
{
// Get the categories via a DataReader
EfzCommand oCmd = new EfzCommand("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName", this.m_oCn);
this.m_oCn.Open();
EfzDataReader oDR = oCmd.ExecuteReader();
// Clear the category list and fill it
cboCategory.Items.Clear();
while (oDR.Read())
{
cboCategory.Items.Add(new Category((string)oDR["CategoryName"], (int)oDR["CategoryID"]));
}
oDR.Close();
this.m_oCn.Close();
// Select the first category
cboCategory.SelectedIndex = 0;
}
catch (EfzException exSql)
{
throw (new Exception(exSql.Message, exSql));
}
catch (Exception ex)
{
throw (new Exception(ex.Message, ex));
}
}
public string GetDatabaseName()
{
string db = "PUBLIC";
try
{
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT DISTINCT SCHEMA FROM INFORMATION_SCHEMA.SYSTEM_SESSIONS";
cmd.Connection = conn;
using (EfzDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
db = reader["SCHEMA"].ToString();
}
reader.Close();
}
}
finally
{
//EfzConnection.ClearAllPools();
}
return(db);
}
public DataTable GetTables(string database)
{
DataTable metaData = new DataTable();
/*SYSTEM_TABLES*/
try
{
metaData = context.CreateTablesDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE='TABLE' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["TABLE_TYPE"] = (r["TABLE_TYPE"].ToString().IndexOf("SYSTEM ") > -1) ? "SYSTEM TABLE" : "TABLE";
row["DESCRIPTION"] = r["REMARKS"] + " : " + r["EFFIPROZ_TYPE"] + (r["COMMIT_ACTION"] == DBNull.Value ? string.Empty : (" " + r["COMMIT_ACTION"]));
}
}
}
finally { }
return(metaData);
}
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 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);
}
public DataTable GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateViewsDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText =
@"SELECT t.TABLE_CAT, t.TABLE_SCHEM, v.TABLE_NAME, v.VIEW_DEFINITION, v.CHECK_OPTION, t.REMARKS, t.EFFIPROZ_TYPE, t.TABLE_TYPE, v.IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS v
left join INFORMATION_SCHEMA.SYSTEM_TABLES t on t.TABLE_NAME = v.TABLE_NAME";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["TABLE_TYPE"] = (r["TABLE_TYPE"].ToString().IndexOf("SYSTEM ") > -1) ? "SYSTEM VIEW" : "VIEW";
row["DESCRIPTION"] = r["REMARKS"] + " : " + r["EFFIPROZ_TYPE"] + (r["CHECK_OPTION"] == DBNull.Value ? string.Empty : (" " + r["CHECK_OPTION"]));
row["IS_UPDATABLE"] = r["IS_UPDATABLE"].ToString().Equals("YES", StringComparison.CurrentCultureIgnoreCase);
//row["INSERTABLE_INTO"] = r["INSERTABLE_INTO"]; // NOT SUPPORTED YET
row["VIEW_TEXT"] = r["VIEW_DEFINITION"];
}
}
}
finally { }
return(metaData);
}
public List <string> GetPrimaryKeyColumns(string database, string table)
{
List <string> primaryKeys = new List <string>();
try
{
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS WHERE TABLE_NAME='" + table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
primaryKeys.Add(r["COLUMN_NAME"].ToString());
}
}
}
finally { }
return(primaryKeys);
}
public bool Read()
{
return(m_reader.Read());
}
public DataTable GetTableColumns(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateColumnsDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME='" + table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
row["DESCRIPTION"] = r["REMARKS"];
row["COLUMN_HASDEFAULT"] = false;
if (r["IS_NULLABLE"] != DBNull.Value)
{
row["IS_NULLABLE"] = r["IS_NULLABLE"];
}
if (r["COLUMN_DEF"] != DBNull.Value)
{
row["COLUMN_HASDEFAULT"] = true;
row["COLUMN_DEFAULT"] = r["COLUMN_DEF"];
}
if (r["IS_GENERATED"] != DBNull.Value && r["IDENTITY_INCREMENT"] != DBNull.Value)
{
row["IS_AUTO_KEY"] = true;
row["AUTO_KEY_SEED"] = Convert.ToInt32(r["IDENTITY_START"]);
row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(r["IDENTITY_INCREMENT"]);
}
int type = Convert.ToInt32(r["DATA_TYPE"]); // dbType enum code
string typeName = (string)r["TYPE_NAME"]; // dbType enum code
int charMax = 0;
int precision = 0;
int scale = 0;
if (r["COLUMN_SIZE"] != DBNull.Value)
{
charMax = Convert.ToInt32(r["COLUMN_SIZE"]);
}
if (r["COLUMN_SIZE"] != DBNull.Value)
{
precision = Convert.ToInt32(r["COLUMN_SIZE"]);
}
if (r["DECIMAL_DIGITS"] != DBNull.Value)
{
scale = Convert.ToInt32(r["DECIMAL_DIGITS"]);
}
row["DATA_TYPE"] = type;
row["TYPE_NAME"] = typeName;
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(typeName, charMax, precision, scale);
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
//TODO: we will have to find the best way to implement this later?
//row["IS_COMPUTED"] = (type == "timestamp") ? true : false;
}
}
}
finally { }
return(metaData);
}
public DataTable GetProcedureParameters(string database, string procedure)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateParametersDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText =
@"SELECT *
FROM INFORMATION_SCHEMA.SYSTEM_PROCEDURECOLUMNS
WHERE PROCEDURE_NAME='" + procedure + "' and PROCEDURE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
if (r["IS_NULLABLE"] != DBNull.Value)
{
row["IS_NULLABLE"] = r["NULLABLE"];
}
int type = Convert.ToInt32(r["DATA_TYPE"]); // dbType enum code
string typeName = (string)r["TYPE_NAME"]; // dbType enum code
int charMax = 0;
int charOctetMax = 0;
int precision = 0;
int scale = 0;
if (r["CHAR_OCTET_LENGTH"] != DBNull.Value)
{
charOctetMax = Convert.ToInt32(r["CHAR_OCTET_LENGTH"]);
}
if (r["LENGTH"] != DBNull.Value)
{
charMax = Convert.ToInt32(r["LENGTH"]);
}
if (r["PRECISION"] != DBNull.Value)
{
precision = Convert.ToInt32(r["PRECISION"]);
}
if (r["SCALE"] != DBNull.Value)
{
scale = Convert.ToInt32(r["SCALE"]);
}
row["DATA_TYPE"] = type;
row["TYPE_NAME"] = typeName;
//row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(typeName, charMax, precision, scale);
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
row["CHARACTER_OCTET_LENGTH"] = charOctetMax;
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["PROCEDURE_CATALOG"] = r["PROCEDURE_CAT"];
row["PROCEDURE_SCHEMA"] = r["PROCEDURE_SCHEM"];
row["PROCEDURE_NAME"] = r["PROCEDURE_NAME"];
row["PARAMETER_NAME"] = r["COLUMN_NAME"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
row["PARAMETER_TYPE"] = r["COLUMN_TYPE"];
row["PARAMETER_HASDEFAULT"] = r["COLUMN_DEF"] != DBNull.Value && r["COLUMN_DEF"] != string.Empty;
row["PARAMETER_DEFAULT"] = r["COLUMN_DEF"];
//row["IS_NULLABLE"] = r["NULLABLE"];
//row["DATA_TYPE"] = r["DATA_TYPE"];
//row["CHARACTER_MAXIMUM_LENGTH"] = r["LENGTH"];
//row["CHARACTER_OCTET_LENGTH"] = r["CHAR_OCTET_LENGTH"];
row["NUMERIC_PRECISION"] = r["PRECISION"];
row["NUMERIC_SCALE"] = r["SCALE"];
row["DESCRIPTION"] = r["REMARKS"];
//row["TYPE_NAME"] = r["TYPE_NAME"];
//row["LOCAL_TYPE_NAME"] = r[""];
}
}
}
finally { }
return(metaData);
}
