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