public bool delete(string tableName, int count = 10000)
{
bool ret = true;
int COUNT = 0;
string sql = String.Format("SELECT COUNT(1) FROM {0}.{1}", m_TableSchema, tableName);
if (select(sql))
{
m_reader.Read();
int c = m_reader.GetInt32(0);
COUNT = c / count + 1;
m_reader.Close();
}
else
{
return(false);
}
sql = String.Format("DELETE (SELECT * FROM {0}.{1} FETCH FIRST {2} ROWS ONLY)", m_TableSchema, tableName, count);
for (int n = 0; n < COUNT; n++)
{
update(sql);
}
return(ret);
}
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);
}
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);
}
}
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 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);
}
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);
}
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();
}
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);
}
}
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);
}
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 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 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);
}
}
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);
}
}
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));
}
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));
}
/// <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);
}
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);
}
/// <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 bool RecordExist(string sql)
{
bool exist = false;
DB2Command command = new DB2Command();
command.Connection = connection;
command.CommandText = sql;
DB2DataReader db2dr = command.ExecuteReader();
exist = db2dr.Read();
db2dr.Close();
return(exist);
}
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 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();
}
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();
}
public void obtenerProcedimientos(DB2Connection connect)
{
DB2Command cmd = new DB2Command("SELECT procname FROM syscat.procedures WHERE procschema = '" + usuario.Text.ToUpper() + "';", connect);//OBTENER TABLAS DE LA BASE DE DATOS
DB2DataReader bff = cmd.ExecuteReader();
while (bff.Read())
{
var Names = bff ["PROCNAME"].ToString();
TreeNode nodo = node4.Nodes.Add(Names.ToString());
nodo.ImageIndex = 4;
nodo.SelectedImageIndex = 4;
nodo.ContextMenuStrip = subMenus [6];
}
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 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 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 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 obtenerForeginKeys(DB2Connection connect)
{
DB2Command cmd = new DB2Command(@"select constname from syscat.references ;", connect);//OBTENER TABLAS DE LA BASE DE DATOS
DB2DataReader bff = cmd.ExecuteReader();
TreeNode nodo = node2.Nodes.Add("Llaves Foraneas");
nodo.ImageIndex = 1;
nodo.SelectedImageIndex = 1;
while (bff.Read())
{
var Names = bff ["CONSTNAME"].ToString();
TreeNode _nodo = nodo.Nodes.Add(Names.ToString());
_nodo.ImageIndex = 3;
_nodo.SelectedImageIndex = 3;
_nodo.ContextMenuStrip = subMenus [16];
}
bff.Close();
}
public void obtenerPrimaryKeys(DB2Connection connect)
{
DB2Command cmd = new DB2Command(@"select const.constname 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 = key.constname where tab.type = 'T' and tab.tabschema like 'DB2ADMIN' group by tab.tabschema, const.constname, tab.tabname order by tab.tabschema, const.constname;", connect);//OBTENER TABLAS DE LA BASE DE DATOS
DB2DataReader bff = cmd.ExecuteReader();
TreeNode nodo = node2.Nodes.Add("Llaves Primarias");
nodo.ImageIndex = 1;
nodo.SelectedImageIndex = 1;
while (bff.Read())
{
var Names = bff ["CONSTNAME"].ToString();
TreeNode _nodo = nodo.Nodes.Add(Names.ToString());
_nodo.ImageIndex = 3;
_nodo.SelectedImageIndex = 3;
_nodo.ContextMenuStrip = subMenus [15];
}
bff.Close();
}
private void ModificarCampos_Load_1(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 from SYSIBM.SYSCOLUMNS where tbname = '" + arbol.SelectedNode.Text + "' AND NAME ='" + data_tablas.Cells ["NAME"].Value.ToString() + "' and keyseq > 0 order by keyseq", connection);
DB2DataReader buffer = cmd.ExecuteReader();
if (buffer.Read())
{
checkBox1.Checked = true;
}
buffer.Close();
} catch (DB2Exception ex) {
}
connection.Close();
}