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); }