public DataSet Request(DataSet ds, IncludeSchema isEnum, string nombreProcedimiento, IDictionaryEnumerator parametros)
{
DB2Connection lc = OpenConnection();
try
{
DB2Command comm = new DB2Command(nombreProcedimiento, lc);
comm.CommandType = System.Data.CommandType.StoredProcedure;
if (parametros != null)
{
while (parametros.MoveNext())
{
comm.Parameters.Add(parametros.Key.ToString(), parametros.Value);
}
}
DB2DataAdapter adapter = new DB2DataAdapter(comm);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch (Exception e)
{
exceptions = "Error ejecutando SQL." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, nombreProcedimiento, e, exceptions);
}
CloseConnection(lc);
return(ds);
}
public DataTable Getda_excel(string sql, string path)
{
string constr = "Database=cig;Password=Shaorong01;User ID=db2admin";
DB2Connection conn = new DB2Connection(constr);
try
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
DB2Command sqlcon = new DB2Command(sql, conn);
DB2DataAdapter ds = new DB2DataAdapter(sqlcon);
DataTable dt = new DataTable();
ds.Fill(dt);
return(dt);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
finally
{
conn.Close();
}
}
//********************************//
// Desbloquear item de Cuarentena //
//********************************//
public static bool UnlockQuarantine(string itemId)
{
string updQuarantine = "UPDATE CGS.\"QUARANTINE_RULE\" SET QUARANTINE_RULE_ENABLED='N' WHERE ITEM_ID='" + itemId + "'";
DB2Command cmdcgs = new DB2Command();
cmdcgs.Connection = ConnectDB2CGS;
cmdcgs.CommandText = updQuarantine;
ConnectDB2CGS.Open();
if (cmdcgs.ExecuteNonQuery() == 1)
{
string[] itemInfo = WS.getContainerInfo(itemId);
ConnectDB2CGS.Close();
string getQuarantine = "SELECT QUARANTINE_RULE_KEY FROM CGS.\"QUARANTINE_RULE\" WHERE ITEM_ID='" + itemId + "'";
DataTable Qid = new DataTable();
DB2DataAdapter da = new DB2DataAdapter(getQuarantine, ConnectDB2CGS);
da.Fill(Qid);
ConnectDB2CGSDW.Open();
//Inserto en tabla QUARANTINE_RULE_HIST
if (insertQRhistory(Qid.Rows[0][0].ToString(), "DESBLOQUEO DE MATERIAL", "Material validado por " + Global.vrm.QCUser, itemInfo[0], itemId, "N", Global.vrm.QCUser))
{
//Inserto en tabla ITEM_HISTORY_025
insertIhistory(itemId, "QUARANTINE UNLOCK", Global.vrm.QCUser);
}
ConnectDB2CGSDW.Close();
return(true);
}
else
{
ConnectDB2CGS.Close();
return(false);
}
}
public override void Update(System.Data.DataTable data, string selectsql)
{
DB2Command cmd;
using (cmd = new DB2Command(selectsql, conn))
{
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
DB2DataAdapter adt = new DB2DataAdapter(cmd);
DB2CommandBuilder builder = new DB2CommandBuilder(adt);
try
{
adt.UpdateCommand = builder.GetUpdateCommand();
adt.Update(data);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Cancel();
cmd = null;
}
}
}
public TableInfoList(string connectionString, string schema = null)
{
//_connectionString = connectionString;
using (var cn = new DB2Connection(connectionString))
{
cn.Open();
using (var cm = cn.CreateCommand())
{
cm.CommandType = CommandType.Text;
if (string.IsNullOrWhiteSpace(schema))
{
cm.CommandText = SQL_WO_SCHEMA;
}
else
{
cm.CommandText = string.Format(SQL_W_SCHEMA, schema);
}
using (var a = new DB2DataAdapter(cm))
{
_data = new DataTable();
a.Fill(_data);
}
}
}
}
public ArrayList RequestGlobalAsCollection(string sql)
{
ArrayList result = null;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
DB2DataAdapter adapter = new DB2DataAdapter(sql, connection);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 1)
{
throw new Exception("Consulta inválida");
}
ArrayList list = new ArrayList(ds.Tables[0].Rows.Count);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Hashtable table = new Hashtable();
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
DataColumn col = ds.Tables[0].Columns[j];
Object data = ds.Tables[0].Rows[i][j];
table.Add(col.ColumnName, data);
}
list.Add(table);
}
result = list;
return(result);
}
/// <summary>
/// Function that executes the given command on the database and returns the result.
/// </summary>
/// <param name="myquery">the query (stored procedure) to execute on the database.</param>
/// <returns>A dataset which is filled with the table(s) with results from the stored procedure.</returns>
public DataSet Query(string myquery)
{
DataSet TempDataSet = new DataSet();
try
{
DB2DataAdapter DataAdapter = new DB2DataAdapter(myquery, ConnString);
DataAdapter.Fill(TempDataSet);
}
catch (SqlException e_dbconn_sql)
{
//SocketClass.LogError(e_dbconn_sql, "LogServer.txt");
//Create an error message
TempDataSet = new DataSet("Replies");
TempDataSet.Tables.Add("Reply");
TempDataSet.Tables[0].Columns.Add("INT");
DataRow newRow = TempDataSet.Tables[0].NewRow();
newRow[0] = -404;
TempDataSet.Tables[0].Rows.Add(newRow);
}
catch (TimeoutException e_dbconn_conn)
{
//SocketClass.LogError(e_dbconn_conn, "LogServer.txt");
//Create an error message
TempDataSet = new DataSet("Replies");
TempDataSet.Tables.Add("Reply");
TempDataSet.Tables[0].Columns.Add("INT");
DataRow newRow = TempDataSet.Tables[0].NewRow();
newRow[0] = -404;
TempDataSet.Tables[0].Rows.Add(newRow);
}
return(TempDataSet);
}
/// <summary>
/// 获取DataSet数据列表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ctype">类型</param>
/// <param name="dataname">内存表</param>
/// <param name="param">参数</param>
/// <returns>返回自定义内存表</returns>
public DataSet GetDataSet(string sql, CommandType ctype, string dataname, params IDataParameter[] param)
{
Open();
var cmd = new DB2Command();
PrepareCommand(cmd, _connSql, null, ctype, sql, param);
using (var dap = new DB2DataAdapter(cmd))
{
var ds = new DataSet();
try
{
dap.Fill(ds, dataname);
dap.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
return(ds);
}
catch (DB2Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
}
}
private DataTable innerFillDataTable(DB2Command command, int timeout, bool inTransaction)
{
try
{
var table = new DataTable();
command.Connection = inTransaction
? (DB2Connection)TransConnection
: (DB2Connection)Connection;
if (!IsValidTimeout(command, timeout))
{
throw new ArgumentException("Invalid CommandTimeout value", nameof(timeout));
}
if (inTransaction)
{
command.Transaction = (DB2Transaction)Transaction;
}
using (var da = new DB2DataAdapter(command))
{
da.Fill(table);
}
return(table);
}
catch (Exception ex)
{
Logger?.LogError(ex, $"Error at FillDataTable; command text: {command.CommandText}");
throw new DbDataException(ex, command.CommandText);
}
}
public DataSet RequestGlobal(DataSet ds, IncludeSchema isEnum, string dataBase, string sql)
{
DB2Connection lc;
if (dataBase == "")
{
lc = OpenConnectionGlobal();
}
else
{
lc = OpenConnectionGlobal(dataBase);
}
try
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
DB2DataAdapter adapter = new DB2DataAdapter(sql, lc);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch { }
CloseConnection(lc);
return(ds);
}
public static bool queryUser(string user, string pass)
{
DataTable dt = new DataTable();
string queryUser = "******"USER\" u ";
queryUser += "LEFT JOIN CGS.USER_PASSWORD p ";
queryUser += "ON p.USER_KEY=u.USER_KEY ";
queryUser += "WHERE u.USER_ID='" + user + "'";
ConnectDB2CGS.Open();
DB2DataAdapter adapter = new DB2DataAdapter(queryUser, ConnectDB2CGS);
adapter.Fill(dt);
ConnectDB2CGS.Close();
string userId = dt.Rows[0][0].ToString();
string usuario = dt.Rows[0][1].ToString();
string password = dt.Rows[0][2].ToString();
string usuarioFormateado = usuario.Replace(" ", "");
if ((Hash.getHash(pass, password)) && (user == usuarioFormateado))
{
return(true);
}
else
{
return(false);
}
}
public DataTable queryToDataTable(string q, DataTable ds)
{
DB2DataAdapter sda = new DB2DataAdapter(q, con);
ds = new DataTable();
sda.Fill(ds);
return(ds);
}
public IBMRecordsUnit(string conn, string cmdtxt)
{
strConn = conn;
cn = new DB2Connection(strConn);
strCmdText = cmdtxt;
adapter = new DB2DataAdapter(strCmdText, cn);
CmdBuilder = new DB2CommandBuilder(adapter);
}
private void initObject()
{
cn = new DB2Connection(strConn);
//if (!cn.IsOpen)
// cn.Open();
adapter = new DB2DataAdapter(strCmdText, cn);
CmdBuilder = new DB2CommandBuilder(adapter);
cmd = new DB2Command(strCmdText, cn);
}
public object showlistbox(string q)
{
DB2DataAdapter sda = new DB2DataAdapter(q, con);
DataSet ds = new DataSet();
sda.Fill(ds);
object data = ds.Tables[0];
return(data);
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
DB2Command command = (DB2Command)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
DB2Parameter parameter = new DB2Parameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
DB2DataAdapter adapter = new DB2DataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
/// <summary>
/// B2返回一个DataSet
/// </summary>
/// <param name="sql">SQL语句或命令</param>
/// <param name="value">参数值列表</param>
/// <returns>DataSet</returns>
public override DataSet ExecuteDataSetParams(string sql, params object[] value)
{
DB2Command comm = (DB2Command)CreateCommand(sql, value);
DataSet ds = new DataSet();
DB2DataAdapter adapter = new DB2DataAdapter();
adapter.SelectCommand = comm;
adapter.Fill(ds);
return(ds);
}
/// <summary>
/// <para>Create a <see cref="DB2DataAdapter"/> with the given update behavior and connection.</para>
/// </summary>
/// <param name="updateBehavior">
/// <para>One of the <see cref="UpdateBehavior"/> values.</para>
/// </param>
/// <param name="connection">
/// <para>The open connection to the database.</para>
/// </param>
/// <returns>An <see cref="DB2DataAdapter"/>.</returns>
/// <exception cref="ArgumentNullException">
/// <para><paramref name="connection"/> can not be <see langword="null"/> (Nothing in Visual Basic).</para>
/// </exception>
protected override DbDataAdapter GetDataAdapter(UpdateBehavior updateBehavior, IDbConnection connection)
{
string queryStringToBeFilledInLater = String.Empty;
DB2DataAdapter adapter = new DB2DataAdapter(queryStringToBeFilledInLater, (DB2Connection)connection);
if (updateBehavior == UpdateBehavior.Continue)
{
adapter.RowUpdated += new DB2RowUpdatedEventHandler(OnDB2RowUpdated);
}
return(adapter);
}
/// <summary>
/// A2返回一个DataSet
/// </summary>
/// <param name="sql">SQL语句或命令</param>
/// <returns>DataSet</returns>
public override DataSet ExecuteDataSet(string sql)
{
DB2Command comm = (DB2Command)CreateCommand(sql);
DB2DataAdapter adapter = new DB2DataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = comm;
adapter.Fill(ds);
return(ds);
}
public override DbDataAdapter GetAdapter(string selectCommand, DbConnection connection,
CommandParameterCollection parameters)
{
DB2DataAdapter adapter = new DB2DataAdapter(selectCommand, connection as DB2Connection);
foreach (CommandParameter p in parameters)
{
DB2Parameter parameter = adapter.SelectCommand.Parameters.Add(p.Name, (DB2Type)p.DataType, p.Size);
parameter.Value = p.Value;
}
return(adapter);
}
/// <summary>
/// Method to execute a SQL query and return a dataset.
/// </summary>
/// <param name="connectionName">Connection name in the configuration file.</param>
/// <param name="query">Query string to be executed.</param>
/// <returns>DataSet with the query results.</returns>
protected DataSet ExecuteQuery(string connectionName, string query)
{
var dataset = new DataSet();
var connection = GetConnection(connectionName);
// Verify if number of entities match number of records.
using (var adapter = new DB2DataAdapter(query, connection))
{
adapter.Fill(dataset);
}
return(dataset);
}
public override void dame_dataset_de(String consulta, DataSet data_set)
{
try
{
adaptador = new DB2DataAdapter();
adaptador.SelectCommand = new DB2Command(consulta, conexion);
adaptador.Fill(data_set);
}
catch (DB2Exception excepcion)
{
MessageBox.Show(excepcion.Message);
}
}
public DbDataAdapter CreateAdapter()
{
try
{
DbDataAdapter dtAdapter = null;
dtAdapter = new DB2DataAdapter();
return(dtAdapter);
}
catch (Exception)
{
throw;
}
}
public ICustomActivityResult Execute()
{
DB2Connection con = null;
DB2DataAdapter adapter = null;
DataTable dt = new DataTable("resultSet");
try
{
DB2ConnectionStringBuilder cnb = new DB2ConnectionStringBuilder(ConnectionString);
if (!string.IsNullOrEmpty(UserName))
{
cnb.UserID = UserName;
cnb.Password = Password;
}
con = new DB2Connection(cnb.ConnectionString);
con.Open();
using (DB2Command command = new DB2Command(Query, con))
{
command.CommandType = System.Data.CommandType.Text;
command.CommandTimeout = Convert.ToInt32(TimeInSeconds);
adapter = new DB2DataAdapter(command);
adapter.Fill(dt);
}
return(this.GenerateActivityResult(dt));
}
finally
{
if (adapter != null)
{
adapter.Dispose();
}
adapter = null;
if (con != null)
{
con.Close();
con.Dispose();
}
con = null;
dt.Dispose();
dt = null;
}
}
public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
{
DBDataCollection rtn = new DBDataCollection();
rtn.IsSuccess = false;
DataSetStd ds = new DataSetStd();
DB2Command dc = null;
if (this._s == DBStatus.Begin_Trans)
{
dc = new DB2Command(sp_name, conn, tran);
}
else
{
dc = new DB2Command(sp_name, conn);
}
dc.CommandType = CommandType.StoredProcedure;
FillParametersToCommand(dc, dbp);
try
{
if (isReturnDataSet)
{
DB2DataAdapter sqlDa = new DB2DataAdapter();
sqlDa.SelectCommand = dc;
sqlDa.Fill(ds);
rtn.ReturnDataSet = ds;
}
else
{
dc.ExecuteNonQuery();
}
//獲取返回值
foreach (DB2Parameter sp in dc.Parameters)
{
if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
{
rtn.SetValue(sp.ParameterName.Replace("@", ""), sp.Value);
}
}
rtn.IsSuccess = true;
}
finally
{
dc.Cancel();
dc = null;
}
return(rtn);
}
private DataTable GetDB2Table(CustomListData data)
{
DB2Connection con = GetConnection(data);
data.Properties.TryGetValue("SQL Statement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);
DB2DataAdapter da = new DB2DataAdapter(new DB2Command(SQLStatement, con));
DataTable db2result = new DataTable();
da.Fill(db2result);
con.Close();
da.Dispose();
return(db2result);
}
private static string getPNKey(string pn)
{
string pnKey = "";
DataTable dt = new DataTable();
string getkey = "SELECT PART_NUMBER_KEY FROM CGS.\"PART_NUMBER\" WHERE PART_NUMBER='" + pn + "'";
ConnectDB2CGS.Open();
DB2DataAdapter da = new DB2DataAdapter(getkey, ConnectDB2CGS);
da.Fill(dt);
ConnectDB2CGS.Close();
pnKey = dt.Rows[0][0].ToString();
return(pnKey);
}
private static DataTable getItemInfo(string item)
{
DataTable dt = new DataTable();
string getItemKey = "SELECT I.ITEM_KEY,P.PART_NUMBER,I.QUANTITY FROM CGS.\"ITEM\" I";
getItemKey += " LEFT JOIN CGS.\"PART_NUMBER\" P ON P.PART_NUMBER_KEY = I.PART_NUMBER_KEY";
getItemKey += " WHERE I.ITEM_ID = '" + item + "'";
ConnectDB2CGS.Open();
DB2DataAdapter da = new DB2DataAdapter(getItemKey, ConnectDB2CGS);
da.Fill(dt);
ConnectDB2CGS.Close();
return(dt);
}
private DataSet innerFillDataSet(string query, IEnumerable <string> tables, int timeout, bool inTransaction)
{
try
{
var dataSet = new DataSet();
using (var cmd = new DB2Command(query, inTransaction
? (DB2Connection)TransConnection
: (DB2Connection)Connection))
{
if (!IsValidTimeout(cmd, timeout))
{
throw new ArgumentException("Invalid CommandTimeout value", nameof(timeout));
}
if (inTransaction)
{
cmd.Transaction = (DB2Transaction)Transaction;
}
using (var da = new DB2DataAdapter(cmd))
{
da.Fill(dataSet);
if (tables == null)
{
return(dataSet);
}
var tablesArray = tables.ToArray();
if (tablesArray.Length <= dataSet.Tables.Count)
{
for (var i = 0; i < tablesArray.Length; i++)
{
dataSet.Tables[i].TableName = tablesArray[i];
}
}
else
{
for (var i = 0; i < dataSet.Tables.Count; i++)
{
dataSet.Tables[i].TableName = tablesArray[i];
}
}
}
}
return(dataSet);
}
catch (Exception ex)
{
Logger?.LogError(ex, $"Error at FillDataSet; command text: {query}");
throw new DbDataException(ex, query);
}
}
private void tablesandviews(string selecionado)
{
try
{
Table = new DataTable("TestTable");
using (DB2Command _cmd = new DB2Command("SELECT * FROM " + selecionado, _con))
{
DB2DataAdapter _dap = new DB2DataAdapter(_cmd);
_dap.Fill(Table);
dataGridView1.DataSource = Table;
}
}
catch { }
}