private void btnPublishData_Click(object sender, EventArgs e)
{
string sQuery1 = string.Empty;
string sQuery2 = string.Empty;
string sQuery3 = string.Empty;
string message = string.Empty;
IBM.Data.DB2.DB2Connection conn = null;
DataTable dt = new DataTable("Table1");
try
{
conn = ConnectDb();
switch (cmbTransactionType.SelectedIndex)
{
case 0:
sQuery1 = "SELECT * FROM DEPOSIT FETCH FIRST 5 ROWS ONLY";
break;
case 1:
sQuery1 = "SELECT * FROM LOAN FETCH FIRST 5 ROWS ONLY";
break;
}
using (IBM.Data.DB2.DB2Command cmd = new IBM.Data.DB2.DB2Command(sQuery1, conn))
{
IBM.Data.DB2.DB2DataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
conn.Close();
if (dt.Rows.Count <= 0)
{
message = "No Record Exist !!!";
}
else
{
SaveData(dt);
message = "Successfully Imported '" + cmbTransactionType.Text + "' Data !!!";
}
}
catch (Exception ex)
{
message = ex.Message;
}
MessageBox.Show(message, "Import from DB2 Utility", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// 输出blob
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public byte[] OutputBlob(string strSql)
{
CloseRead();
if (Neusoft.FrameWork.Management.PublicTrans.Trans != null)
{
this.SetTrans(Neusoft.FrameWork.Management.PublicTrans.Trans);
}
command.CommandText = strSql + "";
command.CommandType = System.Data.CommandType.Text;
IBM.Data.DB2.DB2DataReader reader;
try
{
reader = command.ExecuteReader();
}
catch (IBM.Data.DB2.DB2Exception ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = 1;
this.WriteErr();
return(null);
}
catch (Exception ex)
{
this.Err = ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.WriteErr();
return(null);
}
reader.Read();
// Create a byte array
byte[] byteData = new byte[0];
// fetch the value of Oracle parameter into the byte array
//byteData = (byte[])(cmd.Parameters[0].Value);
try
{
byteData = (byte[])(reader[0]);
}
catch { }
reader.Close();
return(byteData);
}
/*
* Método que trae las posiciones que dispone cada part number.
*/
private static DataTable bringResultsForPositions(string toolIdToSearch, string _ProductPartNumberId)
{
IBM.Data.DB2.DB2Connection conn = null;
DataTable dt = new DataTable("Table1");
conn = ConnectDb();
string queryDbGetRequestedDataForPositions =
@" SELECT CGSLSC.TOOL_SETUP.*, TOOL_KEY AS EXPR1, PRODUCT_PN_KEY AS EXPR2
FROM CGSLSC.TOOL_SETUP
WHERE (TOOL_KEY = " + toolIdToSearch + ") AND (PRODUCT_PN_KEY = " + _ProductPartNumberId + ")";
using (IBM.Data.DB2.DB2Command cmd = new IBM.Data.DB2.DB2Command(queryDbGetRequestedDataForPositions, conn))
{
IBM.Data.DB2.DB2DataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
conn.Close();
return(dt);
}
/*
* Método que devuelve el nombre del part number ID
*/
private static DataTable bringResultsForProductPN(string _ProductPartNumberId)
{
IBM.Data.DB2.DB2Connection conn = null;
DataTable dt = new DataTable("Table1");
conn = ConnectDb();
string queryDbGetRequestedDataForPN =
@" SELECT PART_NUMBER_KEY AS EXPR1, CGS.PART_NUMBER.*
FROM CGS.PART_NUMBER
WHERE (PART_NUMBER_KEY = " + _ProductPartNumberId + ")";
using (IBM.Data.DB2.DB2Command cmd = new IBM.Data.DB2.DB2Command(queryDbGetRequestedDataForPN, conn))
{
IBM.Data.DB2.DB2DataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
conn.Close();
return(dt);
}
/*
* Método que trae todos los datos de la tabla TOOL_SETUP_INFO por toolId
*/
private static DataTable bringResultsForToolId(string toolIdToSearch)
{
IBM.Data.DB2.DB2Connection conn = null;
DataTable dt = new DataTable("Table1");
conn = ConnectDb();
string queryDbGetRequestedDataForToolKey =
@"SELECT TS_INFO_KEY, PRODUCT_PN_KEY, TOOL_KEY, ROUTE_STEP_KEY, LAST_CHANGE_TMST, RECIPE_READY, RECIPE_ID, PROGRAM_ID, CARRIER_PN_KEY, DUAL_SIDED, DUAL_LANE, PFC_MODE, LAST_SETUP_TOOL_KEY, LAST_SETUP_TOOL_STAGE
FROM CGSLSC.TOOL_SETUP_INFO
WHERE (TOOL_KEY = " + toolIdToSearch + ") ORDER BY LAST_CHANGE_TMST DESC";
using (IBM.Data.DB2.DB2Command cmd = new IBM.Data.DB2.DB2Command(queryDbGetRequestedDataForToolKey, conn))
{
IBM.Data.DB2.DB2DataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
conn.Close();
return(dt);
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet"></param>
/// <returns></returns>
public int ExecQuery(string strSql, ref string strDataSet, string strXSLFileName)
{
//[2007/12/01]新增
CloseRead();
// end;
//[2008/03/13]
//if (reader != null)
//{
// if (!reader.IsClosed)
// {
// reader.Close();
// }
//}
// end;
this.command.Connection = this.con as IBM.Data.DB2.DB2Connection;
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
TempReader1 = this.command.ExecuteReader();
XmlDocument doc = new XmlDocument();
XmlNode root;
XmlElement node, row;
doc.AppendChild(doc.CreateXmlDeclaration("1.0", "GB2312", ""));
if (strXSLFileName != null && strXSLFileName != "")
{
string PI = "type='text/xsl' href='" + strXSLFileName + "'";
System.Xml.XmlProcessingInstruction xmlProcessingInstruction = doc.CreateProcessingInstruction("xml-stylesheet", PI);
doc.AppendChild(xmlProcessingInstruction);
}
root = doc.CreateElement("Table");
doc.AppendChild(root);
while (TempReader1.Read())
{
row = doc.CreateElement("Row");
for (int i = 0; i < TempReader1.FieldCount; i++)
{
node = doc.CreateElement("Column");
node.SetAttribute("Name", TempReader1.GetName(i).ToString());
node.InnerText = TempReader1[i].ToString() + "";
row.AppendChild(node);
}
root.AppendChild(row);
}
strDataSet = doc.OuterXml;
TempReader1.Close();
}
catch (IBM.Data.DB2.DB2Exception ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = 1;
this.WriteErr();
return(-1);
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.ErrCode = strSql;
this.WriteErr();
return(-1);
}
WriteDebug("执行查询sql语句！" + strSql);
return(0);
}
/// <summary>
/// 执行sql语句 重载
/// </summary>
/// <param name="strSql"></param>
/// <param name="strDataSet">返回DataSet xml</param>
/// <returns></returns>
public int ExecQuery(string strSql, ref string strDataSet)
{
//[2007/12/01]新增
//if (TempReader1 != null)
//{
// if (!TempReader1.IsClosed)
// {
// TempReader1.Close();
// }
//}
// end;
//[2008/03/13]
//if (reader != null)
//{
// if (!reader.IsClosed)
// {
// reader.Close();
// }
//}
//END
CloseRead();
if (Neusoft.FrameWork.Management.PublicTrans.Trans != null)
{
this.SetTrans(Neusoft.FrameWork.Management.PublicTrans.Trans);
}
this.command.Connection = this.con as IBM.Data.DB2.DB2Connection;
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
TempReader1 = this.command.ExecuteReader();
//TempReader1.FetchSize = this.command.RowSize * 10000;[2007/11/28]
XmlDocument doc = new XmlDocument();
XmlNode root;
XmlNode node, table;
root = doc.CreateElement("DataSet");
doc.AppendChild(root);
while (TempReader1.Read())
{
table = doc.CreateElement("Table");
for (int i = 0; i < TempReader1.FieldCount; i++)
{
node = doc.CreateElement(TempReader1.GetName(i).ToString());
node.InnerText = TempReader1[i].ToString() + "";
table.AppendChild(node);
}
root.AppendChild(table);
}
strDataSet = doc.OuterXml;
TempReader1.Close();
}
catch (IBM.Data.DB2.DB2Exception ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = 1;
this.WriteErr();
return(-1);
}
catch (Exception ex)
{
this.Err = "执行语句产生错误!" + ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.ErrCode = strSql;
this.WriteErr();
return(-1);
}
WriteDebug("执行查询sql语句！" + strSql);
return(0);
}
/// <summary>
/// 执行查询语句,返回Reader
/// </summary>
/// <param name="strSql">执行sql语句</param>
/// <returns>0 success -1 fail</returns>
public int ExecQuery(string strSql, ref IBM.Data.DB2.DB2DataReader Reader)
{
//this.command=new OracleCommand();
a:
CloseRead();
this.command.Connection = this.con as IBM.Data.DB2.DB2Connection;
//this.command.Connection.Close();
//this.command.Connection.Open();
this.command.CommandType = System.Data.CommandType.Text;
this.command.CommandText = strSql + "";
this.command.Parameters.Clear();
try
{
Reader = this.command.ExecuteReader();
//Reader.FetchSize = this.command.RowSize * 10000;//这行代码先注释掉,可能需要改动[2007/11/28]肿瘤医院
}
catch (IBM.Data.DB2.DB2Exception ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = 1;
this.WriteErr();
//新增[2007/11/29]
//trans.Rollback();
//end;
//下面代码可能是如果断开连接,那么重新连接,下面代码是ORACLE专用的,一会儿再改为DB2的[2007/11/28]
//if (ex.Number == 3113)
//{
// while (this.Connect(this.con.ConnectionString) == -1)
// {
// }
// goto a;
//}
// end;
if (this.con.State != ConnectionState.Open)
{
this.con.Open();
goto a;
}
return(-1);
}
catch (Exception ex)
{
//trans.Rollback();//新增[2007/11/29]
this.Err = "执行产生错误!" + ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.ErrCode = strSql;
this.WriteErr();
}
//trans.Commit();//新增[2007/11/29]
WriteDebug("执行查询sql语句！" + strSql);
return(0);
}
private string GetColumnImage(string imageID, string column, string fileName)
{
string retImageFullName = "";
if (_DB2Conn == null)
{
return(retImageFullName);
}
//DB2 Access
IBM.Data.DB2.DB2Command DB2cmd = null;
IBM.Data.DB2.DB2DataReader DB2read = null;
try
{
DB2cmd = new IBM.Data.DB2.DB2Command();
DB2cmd.CommandText = "SELECT " + column + " FROM DBSDESX.CHVT34A0_IMAGENS WHERE CREFARQ = '" + imageID + "'";
DB2cmd.Connection = _DB2Conn;
DB2read = DB2cmd.ExecuteReader(); //System.Data.CommandBehavior.SequentialAccess);
if (!DB2read.Read())
{
return(retImageFullName); //not found
}
}
catch (System.Exception ex)
{
throw ex;
}
//Read BLOB
System.Text.StringBuilder sb = new System.Text.StringBuilder();
int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
long retval;
long startIndex = 0;
try
{
// Read bytes into outByte[] and retain the number of bytes returned.
retval = DB2read.GetBytes(0, startIndex, outByte, 0, bufferSize);
// Continue while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
//Write the buffer. (convert byte to char)
for (int i = 0; i < retval; i++)
{
sb.Append(System.Convert.ToChar(outByte[i]));
}
// Reposition start index to end of last buffer and fill buffer.
startIndex += bufferSize;
retval = DB2read.GetBytes(0, startIndex, outByte, 0, bufferSize);
}
// Write the remaining buffer. (convert byte to char)
for (int i = 0; i < retval; i++)
{
sb.Append(System.Convert.ToChar(outByte[i]));
}
}
catch
{
}
//Close DB2 reader
try
{
DB2read.Close();
DB2cmd.Dispose();
}
catch (System.Exception ex)
{
throw ex;
}
//Create file
try
{
if (sb.Length > 0)
{
string myDocFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonPictures) + "\\";
string text = sb.ToString();
byte[] base64EncodedBytes = System.Convert.FromBase64String(text);
System.IO.File.WriteAllBytes(myDocFolder + fileName, base64EncodedBytes);
retImageFullName = myDocFolder + fileName;
}
}
catch (System.Exception ex)
{
throw ex;
}
return(retImageFullName);
}
