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);
}
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);
}
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 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 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 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);
}
}
public string getType(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);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
connect.Close();
}
catch (DB2Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
connect.Close();
}
return(v);
}
/// <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);
}
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);
}
}
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 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 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 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);
}
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 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();
}
private void Form1_Load(object sender, System.EventArgs e)
{
DB2DataReader s = null;
if (fid != 1)
{
UserChoice();
}
else
{
try
{
// OpenRequests();
var cmd = conn.CreateCommand();
cmd.CommandText = "select count(W_ID) from WAREHOUSE";
RecordRequest(cmd, fid, 0);
s = cmd.ExecuteReader();
s.Read();
activewh = (int)s[0];
s.Close();
textBox1.Text = "" + activewh;
// deferred = new Thread(new ThreadStart(new Deferred(db,wid).Run));
// deferred.Name = "Deferred";
// deferred.Start();
}
catch (Exception ex)
{
RecordResponse(ex, fid, 0);
if (s != null)
{
s.Close();
}
s = null;
}
}
}
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();
}
/// <summary>
/// 读取KEY VALUE值
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public override KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters)
{
try
{
KeyValueCollection entity = new KeyValueCollection();
// 执行SQL命令
using (DB2Command cmd = new DB2Command(ReplaceSqlText(sql, parameters), _db2Cn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
// 执行填充数据
using (DB2DataReader reader = cmd.ExecuteReader())
{
entity = GetEntity(reader);
// 反射参数值
ReflectParamValue(parameters, hasConvertParams);
reader.Close();
reader.Dispose();
}
cmd.Cancel();
cmd.Dispose();
}
return(entity);
}
catch (DB2Exception me)
{
KdtLoger.Instance.Error(me);
throw new DataException(me.Message);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message));
}
}
public void obtenerIndices(DB2Connection connect)
{
DB2Command cmd = new DB2Command(@"select ind.indname from syscat.indexes ind join syscat.indexcoluse cols on ind.indname = cols.indname and ind.indschema = 'DB2ADMIN' where ind.tabschema not like 'SYS%';", connect);//OBTENER TABLAS DE LA BASE DE DATOS
DB2DataReader bff = cmd.ExecuteReader();
TreeNode nodo = node2.Nodes.Add("Indices");
nodo.ImageIndex = 1;
nodo.SelectedImageIndex = 1;
while (bff.Read())
{
var Names = bff ["indname"].ToString();
TreeNode _nodo = nodo.Nodes.Add(Names.ToString());
_nodo.ImageIndex = 3;
_nodo.SelectedImageIndex = 3;
_nodo.ContextMenuStrip = subMenus [4];
}
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();
}
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();
}
private void MostrarProcedimiento_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 ROUTINE_DEFINITION FROM SYSIBM.ROUTINES WHERE SPECIFIC_SCHEMA = 'DB2ADMIN' AND ROUTINE_NAME ='" + arbol.SelectedNode.Text.ToUpper() + "';", connection);
DB2DataReader buffer = cmd.ExecuteReader();
while (buffer.Read())
{
var texto = buffer ["ROUTINE_DEFINITION"].ToString();
richTextBox1.Text = texto;
}
buffer.Close();
} catch (DB2Exception ex) {
}
connection.Close();
}
public string coltab(string col)
{
string v = "";
DB2Command cmd = new DB2Command("select tabname from syscat.columns where colname = '" + col + "'", connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
return(v);
}
private void MostrarTriggers_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 TEXT FROM SYSCAT.TRIGGERS WHERE TRIGSCHEMA ='DB2ADMIN' AND TRIGNAME= '" + arbol.SelectedNode.Text + "';", connection);//OBTENER TABLAS DE LA BASE DE DATOS
DB2DataReader bff = cmd.ExecuteReader();
while (bff.Read())
{
var Names = bff ["TEXT"].ToString();
richTextBox1.Text = Names;
break;
}
bff.Close();
} catch (DB2Exception ex) {
}
connection.Close();
}
public string ddl(string q)
{
string v = "";
DB2Command cmd = new DB2Command(q, connect);
if (!connect.IsOpen)
{
connect.Open();
}
using (DB2DataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
v = dr.GetString(0);
}
dr.Close();
}
connect.Close();
return(v);
}
