private string Type(string col, string tab) { string v = ""; try { DB2Command cmd = new DB2Command("select typename from syscat.columns where tabname = '" + tab + "' and colname = '" + col + "'", connect); if (!connect.IsOpen) { connect.Open(); } using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { v = dr.GetString(0); } } } catch (DB2Exception e) { System.Windows.Forms.MessageBox.Show(e.ToString()); } return(v); }
public Task <IList <string> > GetOwners() { IList <string> owners = new List <string>(); using (var sqlCon = new DB2Connection(_connectionStr)) { sqlCon.Open(); try { using (DB2Command tableDetailsCommand = sqlCon.CreateCommand()) { tableDetailsCommand.CommandText = "select distinct owner from systables where tabtype in ('T', 'E', 'V', 'S') and owner != 'informix' order by owner"; using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default)) { while (reader.Read()) { owners.Add(reader.GetString("owner")); } } } } finally { sqlCon.Close(); } } return(Task.FromResult(owners)); }
public Task <List <Table> > GetTables(string owner) { var tables = new List <Table>(); using (var sqlCon = new DB2Connection(_connectionStr)) { sqlCon.Open(); try { using (DB2Command tableDetailsCommand = sqlCon.CreateCommand()) { tableDetailsCommand.CommandText = $"select tabname, owner from systables where tabtype in ('T', 'E', 'V', 'S') and owner = '{owner}' order by tabname"; using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default)) { while (reader.Read()) { tables.Add(new Table { Name = reader.GetString("tabname"), Owner = reader.GetString("owner") }); } } } } finally { sqlCon.Close(); } } return(Task.FromResult(tables)); }
protected List <KeyValueCollection> GetEntityPage(DB2DataReader reader, int offset, int limit) { List <KeyValueCollection> entitys = new List <KeyValueCollection>(); // 读取列 List <string> columns = new List <string>(); for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i)); } // 反射创建实体类 int currentIndex = 0; while (reader.Read()) { if (offset > currentIndex++) { continue; } if (offset + limit == currentIndex) { break; } KeyValueCollection entity = new KeyValueCollection(); foreach (var col in columns) { entity[col] = reader[col]; } entitys.Add(entity); } return(entitys); }
/// <summary> /// 获取登记日切日期 /// </summary> /// <returns></returns> public string GetDjrqrq() { string djrq = string.Empty; string cmdString = "select * from djrqb"; this.Open(); try { db2Cmd = new DB2Command(cmdString, this.db2Conn); DB2DataReader dr = db2Cmd.ExecuteReader(); while (dr.Read()) { djrq = dr.GetString(7); } } catch (Exception ex) { LogHelper.WriteLogException("Execute sql command error in Db2Operation.GetDjrqrq()", ex); throw; } finally { this.Close(); } return(djrq); }
private void ModificarForeignKey_Load(object sender, EventArgs e) { PantallaPrincipal pn = new PantallaPrincipal(); DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Parent.Text); try { connection.Open(); DB2Command cmd = new DB2Command(@"select tabname from syscat.references where constname = '" + arbol.SelectedNode.Text + "';", connection); DB2DataReader buffer = cmd.ExecuteReader(); while (buffer.Read()) { var nombre_tabla = buffer ["TABNAME"].ToString(); buffer.Close(); richTextBox1.Text = "ALTER TABLE " + nombre_tabla + " DROP FOREIGN KEY " + arbol.SelectedNode.Text + ";"; break; } } catch (DB2Exception ex) { MessageBox.Show("Error\n" + ex.Message); } connection.Close(); TreeNodeCollection cl = arbol.SelectedNode.Parent.Parent.Parent.Nodes [0].Nodes; foreach (TreeNode tabla in cl) { comboBox1.Items.Add(tabla.Text); } }
public string scriptFK(string col, string tab) { string v = ""; string cref = ""; string tref = ""; if (!isfk(col, tab)) { return("Nada"); } DB2Command cmd = new DB2Command("select ref.constname as FK, ref.tabname as Tabla, key.colname as Ref_Col, key.tabname as Ref_Tabla from syscat.keycoluse as key inner join syscat.references as ref on key.constname = ref.refkeyname where ref.constname = '" + col + "_FK' and ref.tabname = '" + tab + "'", connect); connect.Open(); using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { cref = dr.GetString(2); tref = dr.GetString(3); } dr.Close(); } connect.Close(); v = "alter table " + tab + " add constraint " + col + "_FK foreign key (" + col + ") references " + tref + " (" + cref + ") not enforced"; return(v); }
public bool isfk(string col, string tab) { string v = ""; try { DB2Command cmd = new DB2Command("select constname from syscat.references where constname = '" + col + "_FK' and tabname = '" + tab + "'", connect); connect.Open(); using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { v = dr.GetString(0); } dr.Close(); } connect.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); connect.Close(); } if (v == "") { return(false); } else { return(true); } }
private void caja_SelectedIndexChanged(object sender, EventArgs e) { data_set.Rows.Clear(); Expresion.Items.Clear(); PantallaPrincipal pn = new PantallaPrincipal(); DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Text); try { connection.Open(); int posicion = caja.SelectedIndex; string nombre_tabla = caja.Items [posicion].ToString(); DB2Command cmd = new DB2Command(@"SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '" + nombre_tabla + "';", connection); DB2DataReader buffer = cmd.ExecuteReader(); while (buffer.Read()) { var nombre_campo = buffer ["NAME"].ToString(); Expresion.Items.Add(nombre_campo); } buffer.Close(); } catch (DB2Exception ex) { MessageBox.Show("Ha ocurrido un error\n" + ex.Message); } connection.Close(); }
private bool isPK(string col, string tab) { string v = ""; try { DB2Command cmd = new DB2Command("select colname from syscat.keycoluse where colname = '" + col + "' and tabname = '" + tab + "'", connect); if (!connect.IsOpen) { connect.Open(); } using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { v = dr.GetString(0); } } } catch (Exception e) { MessageBox.Show(e.ToString()); } if (v == "") { return(false); } else { return(true); } }
private string tabCols(string tab) { string c = ""; string col = ""; DB2Command cmd = new DB2Command("select colname from syscat.columns where tabname = '" + tab + "'", connect); if (!connect.IsOpen) { connect.Open(); } using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { c = dr.GetString(0); if (isFK(c, tab)) { col = col + " " + script_FK(c, tab); } if (isPK(c, tab)) { col = col + " " + scriptPk(c, tab); } if (isIND(c, tab)) { col = col + " " + scriptInd(c, tab); } } dr.Close(); } connect.Close(); return(col); }
private void ModificarVista_Load(object sender, EventArgs e) { PantallaPrincipal pn = new PantallaPrincipal(); DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Text); try { connection.Open(); DB2Command cmd = new DB2Command(@"select NAME, TEXT from SYSIBM.SYSVIEWS WHERE CREATOR='DB2ADMIN' and NAME='" + arbol.SelectedNode.Text + "';", connection); DB2DataReader buffer = cmd.ExecuteReader(); while (buffer.Read()) { var function = buffer ["TEXT"].ToString().ToUpper(); valor = buffer ["NAME"].ToString(); int pl = function.IndexOf("SELECT"); function = function.Substring(pl); richTextBox1.Text = function; textBox1.Text = valor; break; } buffer.Close(); } catch (DB2Exception ex) { MessageBox.Show("Error al mostrar Vista\n" + ex.Message); } connection.Close(); }
/// <summary> /// Uses a SQL Data Reader to load a data table with data from the query execution /// </summary> /// <param name="command">Command (Text command or Stored Procedure)</param> /// <param name="type">Type of command (text, stored procedure or table-direct)</param> /// <returns>Data Table with the execution results</returns> public DataTable GetTable(string command, CommandType type) { DataTable resultSet = new DataTable(); try { using (DB2Connection connection = new DB2Connection(connectionString.ConnectionString)) { using (DB2Command cmd = new DB2Command(command)) { cmd.Connection = connection; foreach (DB2Parameter parameter in Parameters) { cmd.Parameters.Add(parameter); } cmd.CommandType = type; cmd.Connection.Open(); DB2DataReader reader = null; reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); resultSet.Load(reader); reader.Close(); cmd.Connection.Close(); } } } catch (Exception ex) { Error aError = new Error(ex.Source, ex.Message, GetCurrentMethod()); ErrorList.Add(aError); } return(resultSet); }
public bool isindex(string col, string tab) { string v = ""; try { DB2Command cmd = new DB2Command("select name from sysibm.sysindexes where name = '" + col + "' and tbname = '" + tab + "' and tbcreator = 'USUARIO'", connect); connect.Open(); using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { v = dr.GetString(0); } dr.Close(); } connect.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); connect.Close(); } if (v == "") { return(false); } else { return(true); } }
private static DataTable connectAndExecute(string Query, string envId) { try { var allEnvs = ConfigurationManager.GetSection("connStr") as System.Collections.Specialized.NameValueCollection; var env = allEnvs[envId]; DataTable results = new DataTable(); _connection = new DB2Connection(env); _connection.Open(); if (_connection.IsOpen) { _command = new DB2Command(Query, _connection); _reader = _command.ExecuteReader(); results.Load(_reader); } _connection.Close(); return(results); } catch (Exception ex) { MessageBox.Show("Error connecting database: \n" + ex.Message); return(null); } }
private void ModificarPrimaryKey_Load(object sender, EventArgs e) { PantallaPrincipal pn = new PantallaPrincipal(); DB2Connection connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Parent.Text); try { connection.Open(); DB2Command cmd = new DB2Command(@"select tab.tabname from syscat.tables tab inner join syscat.tabconst const on const.tabschema = tab.tabschema and const.tabname = tab.tabname and const.type = 'P' join syscat.keycoluse key on const.tabschema = key.tabschema and const.tabname = key.tabname and const.constname = '" + arbol.SelectedNode.Text + "' where tab.type = 'T' and tab.tabschema like 'DB2ADMIN' group by tab.tabschema, const.constname, tab.tabname order by tab.tabschema, const.constname;", connection); DB2DataReader buffer = cmd.ExecuteReader(); while (buffer.Read()) { var nombre_tabla = buffer ["TABNAME"].ToString(); buffer.Close(); richTextBox1.Text = "ALTER TABLE " + nombre_tabla + " DROP PRIMARY KEY;"; break; } } catch (DB2Exception ex) { MessageBox.Show("Error \n" + ex.Message); } connection.Close(); TreeNodeCollection cl = arbol.SelectedNode.Parent.Parent.Parent.Nodes [0].Nodes; foreach (TreeNode tabla in cl) { comboBox1.Items.Add(tabla.Text); } }
public List <Citizen> GetCitizenList(CitizenViewModel model) { List <Citizen> citizenList = new List <Citizen>(); using (conn = new DB2Connection(connectionString)) { conn.Open(); using (DB2Command cmd = conn.CreateCommand()) { cmd.CommandText = CitizenHelper.CitizenSearchQueryBuilder(model); rd = cmd.ExecuteReader(); rd.Read(); do { if (rd.HasRows) { Citizen citizen = new Citizen { Id = int.Parse(rd[0].ToString()), Surname = rd[1].ToString().Trim(' '), CitizenName = rd[2].ToString().Trim(' '), Middlename = rd[3].ToString().Trim(' '), BirthDate = DateTime.Parse(rd[4].ToString()) }; citizenList.Add(citizen); } } while (rd.Read()); } } return(citizenList); }
public string PoC(string name) { string v = ""; DB2Command cmd = new DB2Command(" select routinetype from syscat.routines where routinename = '" + name + "'", connect); try { connect.Open(); using (DB2DataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { v = dr.GetString(0); } dr.Close(); } connect.Close(); } catch (DB2Exception e) { System.Windows.Forms.MessageBox.Show(e.ToString()); connect.Close(); } return(v); }
public async Task <User> GetUser(string userId, CancellationToken ct = default) { User returnUser = new User(); try { if (!_conn.IsOpen) { _conn.Open(); } DB2Transaction trans = _conn.BeginTransaction(); DB2Command cmd = _conn.CreateCommand(); try { cmd.Transaction = trans; cmd.CommandType = CommandType.Text; cmd.CommandText = $"SELECT * FROM USER_TABLE WHERE ID = '{userId}'"; using (DB2DataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { await reader.ReadAsync(); returnUser.FirstName = reader[0].ToString().Trim(); returnUser.MiddleName = reader[1].ToString().Trim(); returnUser.LastName = reader[2].ToString().Trim(); returnUser.UserId = reader[3].ToString().Trim().Replace(".", ""); } else { return(new User()); } } cmd.Transaction.Commit(); return(returnUser); } catch (Exception ex) { cmd.Transaction.Rollback(); throw ex; } } catch (Exception ex) { if (_conn.IsOpen) { _conn.Close(); } throw ex; } finally { _conn.Close(); } }
public DB2DataRow(DbDataReader reader) { this._reader = reader as DB2DataReader; for (int i = 0; i < reader.FieldCount; i++) { this.values[this._reader.GetName(i)] = this.GetData(i); } }
public void FillData(DB2DataReader reader) { var values = new Object[reader.FieldCount]; reader.GetValues(values); FillData(values); }
/// <summary> /// 反射实体类信息 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader"></param> /// <returns></returns> protected List <T> GetEntityCollection <T>(DB2DataReader reader) where T : class, new() { PropertyInfo[] properties = typeof(T).GetProperties(); if (properties == null || properties.Length <= 0) { throw new ArgumentNullException("实体类{0}不包含任何属性信息!".ToFormat(typeof(T).Name)); } // 读取列 Dictionary <string, string> columns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); for (int i = 0; i < reader.FieldCount; i++) { string cname = reader.GetName(i); columns[cname] = cname; } // 反射创建实体类 List <T> entitys = new List <T>(); while (reader.Read()) { T entity = typeof(T).Create <T>(); foreach (PropertyInfo property in properties) { string p_name = property.Name; object[] attributes = property.GetCustomAttributes(typeof(Field), true); object[] proc_attributes = property.GetCustomAttributes(typeof(Proc), true); // 存在自定义Field实例处理方法 if (attributes != null && attributes.Length > 0) { Field dbfield = attributes[0] as Field; // 检测自增变量信息 p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name; } else if (proc_attributes != null && proc_attributes.Length > 0) { Proc dbfield = proc_attributes[0] as Proc; // 检测自增变量信息 p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name; } // 不存在时处理方法 else { } // 读取数据,并赋值 if (columns.ContainsKey(p_name)) { property.SetValue(entity, reader[columns[p_name]].ChangeTo(property.PropertyType), null); } } entitys.Add(entity); } return(entitys); }
public static DatosDeConsultaAltaTemprana toConsultaAltaTempranaResponse(DB2DataReader reader) { return(new DatosDeConsultaAltaTemprana() { Cuit = (decimal)reader["CUIT"], Cuil = (decimal)reader["CUIL"], FechaAltaTemprana = (DateTime)reader["F_ALTA_TEMPRANA"], DescripcionAbreviadaMovimiento = (string)reader["DA_MOVIMIENTO"], FechaFinRelacionLaboral = reader["F_FIN_RLABORAL"] == DBNull.Value ? null : (DateTime?)Convert.ToDateTime(reader["F_FIN_RLABORAL"]) }); }
public void obtenerFunciones(DB2Connection connect) { DB2Command cmd = new DB2Command(@"select routinename from syscat.routines where routineschema not like 'SYS%'and routineschema = 'DB2ADMIN' and text not like '%PROCEDURE%'; ", connect);//OBTENER TABLAS DE LA BASE DE DATOS DB2DataReader bff = cmd.ExecuteReader(); while (bff.Read()) { var Names = bff ["ROUTINENAME"].ToString(); TreeNode nodo = node5.Nodes.Add(Names.ToString()); nodo.ImageIndex = 5; nodo.SelectedImageIndex = 5; nodo.ContextMenuStrip = subMenus [8]; } bff.Close(); }
public void obtenerVistas(DB2Connection connect) { DB2Command cmd = new DB2Command(@"select NAME from SYSIBM.SYSVIEWS WHERE CREATOR ='" + usuario.Text.ToUpper() + "';", connect);//OBTENER TABLAS DE LA BASE DE DATOS DB2DataReader bff = cmd.ExecuteReader(); while (bff.Read()) { var Names = bff ["NAME"].ToString(); TreeNode nodo = node3.Nodes.Add(Names.ToString()); nodo.ImageIndex = 6; nodo.SelectedImageIndex = 6; nodo.ContextMenuStrip = subMenus [10]; } bff.Close(); }
public void obtenerTablas(DB2Connection connect) { DB2Command cmd = new DB2Command("SELECT NAME FROM SYSIBM.SYSTABLES WHERE type = 'T' AND creator = '" + usuario.Text.ToUpper() + "';", connect);//OBTENER TABLAS DE LA BASE DE DATOS DB2DataReader bff = cmd.ExecuteReader(); while (bff.Read()) { var Names = bff ["NAME"].ToString(); TreeNode nodo = node1.Nodes.Add(Names.ToString()); nodo.ImageIndex = 2; nodo.SelectedImageIndex = 2; nodo.ContextMenuStrip = subMenus [2]; } bff.Close(); }
public void obtenerTriggers(DB2Connection connect) { DB2Command cmd = new DB2Command(@"SELECT TRIGNAME FROM SYSCAT.TRIGGERS WHERE TRIGSCHEMA ='" + usuario.Text.ToUpper() + "';", connect);//OBTENER TABLAS DE LA BASE DE DATOS DB2DataReader bff = cmd.ExecuteReader(); while (bff.Read()) { var Names = bff ["TRIGNAME"].ToString(); TreeNode nodo = node6.Nodes.Add(Names.ToString()); nodo.ImageIndex = 7; nodo.SelectedImageIndex = 7; nodo.ContextMenuStrip = subMenus [12]; } bff.Close(); }
private bool CheckExistCitizen(Citizen citizen) { using (conn = new DB2Connection(connectionString)) { conn.Open(); using (DB2Command cmd = conn.CreateCommand()) { cmd.CommandText = $"SELECT * FROM citizen WHERE surname = '{citizen.Surname}' AND citizenname = '{citizen.CitizenName}' " + $"and middlename = '{citizen.Middlename}' AND birthdate = '{citizen.BirthDate.ToShortDateString()}'"; rd = cmd.ExecuteReader(); rd.Read(); return(rd.HasRows); } } }
public void obtenerChecks(DB2Connection connect) { DB2Command cmd = new DB2Command(@"SELECT NAME FROM SYSIBM.SYSCHECKS WHERE TBCREATOR='" + usuario.Text.ToUpper() + "';", connect);//OBTENER TABLAS DE LA BASE DE DATOS DB2DataReader bff = cmd.ExecuteReader(); while (bff.Read()) { var Names = bff ["NAME"].ToString(); TreeNode nodo = node7.Nodes.Add(Names.ToString()); nodo.ImageIndex = 8; nodo.SelectedImageIndex = 8; nodo.ContextMenuStrip = subMenus [14]; } bff.Close(); }
public void obtenerUsuarios(DB2Connection connect) { DB2Command cmd = new DB2Command(@"select * from sysibmadm.authorizationids WHERE AUTHIDTYPE='U';", connect);//OBTENER TABLAS DE LA BASE DE DATOS DB2DataReader bff = cmd.ExecuteReader(); while (bff.Read()) { var Names = bff ["AUTHID"].ToString(); TreeNode nodo = node8.Nodes.Add(Names.ToString()); nodo.ImageIndex = 9; nodo.SelectedImageIndex = 9; // nodo.ContextMenuStrip = subMenus [14]; } bff.Close(); }