/// <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);
}
/// <summary>
/// 执行sql语句，返回一条记录
/// </summary>
/// <param name="strSql">执行sql语句</param>
/// <returns> "-1" fail</returns>
public string ExecSqlReturnOne(string strSql)
{
//IDataReader tempReader = (IDataReader)TempReader1;
//[2007/12/01]新增
//if (TempReader1 != null)
//{
// if (!TempReader1.IsClosed)
// {
// TempReader1.Close();
// }
//}
//if (reader != null)
//{
// if (!reader.IsClosed)
// {
// reader.Close();
// }
//}
CloseRead();
if (Neusoft.FrameWork.Management.PublicTrans.Trans != null)
{
this.SetTrans(Neusoft.FrameWork.Management.PublicTrans.Trans);
}
//end;
if (this.ExecQuery(strSql, ref TempReader1) == 0)
{
try
{
string strReturn;
if (TempReader1.Read() == false)
{
this.TempReader.Close();
return("-1");
}
strReturn = TempReader1[0].ToString();
try
{
TempReader1.Close();
}
catch { }
WriteDebug("执行查询sql语句！" + strSql + "返回:" + strReturn);
return(strReturn);
}
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");
}
}
else
{
WriteDebug("执行查询sql语句！" + strSql + "返回:-1");
return("-1");
}
}
/// <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);
}
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);
}