using System;
using IBM.Data.Informix;
using System.Data.OleDb;
using System.Xml;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using Oracle.DataAccess.Client;
namespace BlobDB
{
public class BlobToDB
{
public BlobToDB(string dbConnection, string dbType)
{
this.SetDBConnection(dbConnection, dbType);
}
public BlobToDB()
{
this.GetDbConnection();
}
private string dbTypeString = "";
private string dbConnectionString = "";
public byte[] ConvertFileToByte(string filePath)
{
try
{
if (!System.IO.File.Exists(filePath))
{
return null;
}
System.IO.FileStream stream = new System.IO.FileInfo(filePath).OpenRead();
byte[] fileData = new byte[stream.Length];
//从流中读取字节块并将该数据写入给定缓冲区buffer中
stream.Read(fileData, 0, Convert.ToInt32(stream.Length));
stream.Close();
return fileData;
}
catch (Exception e)
{
return null;
}
}
/// <summary>
/// 二进制写入DB(Informix和Oracle)
/// </summary>
/// <param name="tableName">目标表名</param>
/// <param name="dt">写入的数据</param>
/// <returns>成功的写入到表中数据量</returns>
public int WriteBolbToDB(string tableName, DataTable dt)
{
if (dbConnectionString == "" || dbTypeString == "")
{
this.GetDbConnection();
if (dbConnectionString == "" || dbTypeString == "")
{
return 0;
}
}
if (dt == null || dt.Rows.Count == 0 || tableName.Trim() == "")
{
return 0;
}
//构造Insert SQL 前半段
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ");
strSql.Append(tableName);
strSql.Append("(");
foreach (DataColumn dc in dt.Columns)
{
strSql.Append(dc.ColumnName + ",");
}
strSql.Remove(strSql.Length - 1, 1);
strSql.Append(") values (");
int successCount = 0;
if (dbTypeString.ToUpper() == "INFORMIX")
{
successCount = this.WriteBolbToInformix(dt, dbConnectionString, strSql);
}
else if (dbTypeString.Substring(0, 6).ToUpper() == "ORACLE")
{
successCount = this.WriteBolbToOracle(dt, dbConnectionString, strSql);
}
else
{
return 0;
}
return successCount;
}
private int WriteBolbToInformix(DataTable dt, string strConnection, StringBuilder strSql)
{
try
{
int successCount = 0;
//构造Informix SQL 后半段
foreach (DataColumn dc in dt.Columns)
{
strSql.Append("?,");
}
strSql.Remove(strSql.Length - 1, 1);
strSql.Append(")");
using (IfxConnection connection = new IfxConnection(strConnection))
{
IfxCommand cm = new IfxCommand(strSql.ToString(), connection);
connection.Open();
foreach (DataRow dr in dt.Rows)
{
cm.Parameters.Clear();
foreach (DataColumn dc in dr.Table.Columns)
{
switch (dc.DataType.Name)
{
case "Char":
case "String":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.VarChar)).Value = dr[dc.ColumnName];
break;
case "DateTime":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.DateTime)).Value = dr[dc.ColumnName];
break;
case "Int16":
case "Int32":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Integer)).Value = dr[dc.ColumnName];
break;
case "Int64":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Int8)).Value = dr[dc.ColumnName];
break;
case "Float":
case "Decimal":
case "Double":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Float)).Value = dr[dc.ColumnName];
break;
case "Byte":
case "Byte[]":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Byte)).Value = dr[dc.ColumnName];
break;
default:
break;
}
}
successCount += cm.ExecuteNonQuery();
}
connection.Close();
}
return successCount;
}
catch (Exception e)
{
return 0;
}
finally
{
GC.Collect();
}
}
private int WriteBolbToOracle(DataTable dt, string strConnection, StringBuilder strSql)
{
try
{
int successCount = 0;
//构造Oracle SQL 后半段
foreach (DataColumn dc in dt.Columns)
{
strSql.Append(":" + dc.ColumnName + ",");
}
strSql.Remove(strSql.Length - 1, 1);
strSql.Append(")");
using (OracleConnection connection = new OracleConnection(strConnection))
{
OracleCommand cm = new OracleCommand(strSql.ToString(), connection);
connection.Open();
foreach (DataRow dr in dt.Rows)
{
cm.Parameters.Clear();
foreach (DataColumn dc in dr.Table.Columns)
{
switch (dc.DataType.Name)
{
case "Char":
case "String":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Varchar2)).Value = dr[dc.ColumnName];
break;
case "DateTime":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Date)).Value = dr[dc.ColumnName];
break;
case "Int16":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Int16)).Value = dr[dc.ColumnName];
break;
case "Int32":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Int32)).Value = dr[dc.ColumnName];
break;
case "Int64":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Int64)).Value = dr[dc.ColumnName];
break;
case "Decimal":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Decimal)).Value = dr[dc.ColumnName];
break;
case "Float":
case "Double":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Double)).Value = dr[dc.ColumnName];
break;
case "Byte":
case "Byte[]":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Blob)).Value = dr[dc.ColumnName];
break;
default:
break;
}
}
successCount += cm.ExecuteNonQuery();
}
connection.Close();
}
return successCount;
}
catch (Exception e)
{
return 0;
}
finally
{
GC.Collect();
}
}
private void SetDBConnection(string dbConnection, string dbType)
{
dbTypeString = dbType;
dbConnectionString = dbConnection;
}
private void GetDbConnection()
{
try
{
string fileName = string.Format(AppDomain.CurrentDomain.BaseDirectory + @"\dao.config");
if (fileName == null)
{
return;
}
XmlDocument doc = new XmlDocument();
doc.Load(fileName);
XmlElement stepXmlEl = doc.DocumentElement;
if (stepXmlEl == null)
{
return;
}
XmlNodeList conList = doc.GetElementsByTagName("dataSource");
XmlNodeList dbTpyeList = doc.GetElementsByTagName("provider");
dbConnectionString = conList[0].Attributes["connectionString"].Value;
dbTypeString = dbTpyeList[0].Attributes["name"].Value;
}
catch (Exception e)
{
}
}
}
}
using IBM.Data.Informix;
using System.Data.OleDb;
using System.Xml;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using Oracle.DataAccess.Client;
namespace BlobDB
{
public class BlobToDB
{
public BlobToDB(string dbConnection, string dbType)
{
this.SetDBConnection(dbConnection, dbType);
}
public BlobToDB()
{
this.GetDbConnection();
}
private string dbTypeString = "";
private string dbConnectionString = "";
public byte[] ConvertFileToByte(string filePath)
{
try
{
if (!System.IO.File.Exists(filePath))
{
return null;
}
System.IO.FileStream stream = new System.IO.FileInfo(filePath).OpenRead();
byte[] fileData = new byte[stream.Length];
//从流中读取字节块并将该数据写入给定缓冲区buffer中
stream.Read(fileData, 0, Convert.ToInt32(stream.Length));
stream.Close();
return fileData;
}
catch (Exception e)
{
return null;
}
}
/// <summary>
/// 二进制写入DB(Informix和Oracle)
/// </summary>
/// <param name="tableName">目标表名</param>
/// <param name="dt">写入的数据</param>
/// <returns>成功的写入到表中数据量</returns>
public int WriteBolbToDB(string tableName, DataTable dt)
{
if (dbConnectionString == "" || dbTypeString == "")
{
this.GetDbConnection();
if (dbConnectionString == "" || dbTypeString == "")
{
return 0;
}
}
if (dt == null || dt.Rows.Count == 0 || tableName.Trim() == "")
{
return 0;
}
//构造Insert SQL 前半段
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ");
strSql.Append(tableName);
strSql.Append("(");
foreach (DataColumn dc in dt.Columns)
{
strSql.Append(dc.ColumnName + ",");
}
strSql.Remove(strSql.Length - 1, 1);
strSql.Append(") values (");
int successCount = 0;
if (dbTypeString.ToUpper() == "INFORMIX")
{
successCount = this.WriteBolbToInformix(dt, dbConnectionString, strSql);
}
else if (dbTypeString.Substring(0, 6).ToUpper() == "ORACLE")
{
successCount = this.WriteBolbToOracle(dt, dbConnectionString, strSql);
}
else
{
return 0;
}
return successCount;
}
private int WriteBolbToInformix(DataTable dt, string strConnection, StringBuilder strSql)
{
try
{
int successCount = 0;
//构造Informix SQL 后半段
foreach (DataColumn dc in dt.Columns)
{
strSql.Append("?,");
}
strSql.Remove(strSql.Length - 1, 1);
strSql.Append(")");
using (IfxConnection connection = new IfxConnection(strConnection))
{
IfxCommand cm = new IfxCommand(strSql.ToString(), connection);
connection.Open();
foreach (DataRow dr in dt.Rows)
{
cm.Parameters.Clear();
foreach (DataColumn dc in dr.Table.Columns)
{
switch (dc.DataType.Name)
{
case "Char":
case "String":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.VarChar)).Value = dr[dc.ColumnName];
break;
case "DateTime":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.DateTime)).Value = dr[dc.ColumnName];
break;
case "Int16":
case "Int32":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Integer)).Value = dr[dc.ColumnName];
break;
case "Int64":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Int8)).Value = dr[dc.ColumnName];
break;
case "Float":
case "Decimal":
case "Double":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Float)).Value = dr[dc.ColumnName];
break;
case "Byte":
case "Byte[]":
cm.Parameters.Add(new IfxParameter(dc.ColumnName, IfxType.Byte)).Value = dr[dc.ColumnName];
break;
default:
break;
}
}
successCount += cm.ExecuteNonQuery();
}
connection.Close();
}
return successCount;
}
catch (Exception e)
{
return 0;
}
finally
{
GC.Collect();
}
}
private int WriteBolbToOracle(DataTable dt, string strConnection, StringBuilder strSql)
{
try
{
int successCount = 0;
//构造Oracle SQL 后半段
foreach (DataColumn dc in dt.Columns)
{
strSql.Append(":" + dc.ColumnName + ",");
}
strSql.Remove(strSql.Length - 1, 1);
strSql.Append(")");
using (OracleConnection connection = new OracleConnection(strConnection))
{
OracleCommand cm = new OracleCommand(strSql.ToString(), connection);
connection.Open();
foreach (DataRow dr in dt.Rows)
{
cm.Parameters.Clear();
foreach (DataColumn dc in dr.Table.Columns)
{
switch (dc.DataType.Name)
{
case "Char":
case "String":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Varchar2)).Value = dr[dc.ColumnName];
break;
case "DateTime":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Date)).Value = dr[dc.ColumnName];
break;
case "Int16":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Int16)).Value = dr[dc.ColumnName];
break;
case "Int32":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Int32)).Value = dr[dc.ColumnName];
break;
case "Int64":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Int64)).Value = dr[dc.ColumnName];
break;
case "Decimal":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Decimal)).Value = dr[dc.ColumnName];
break;
case "Float":
case "Double":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Double)).Value = dr[dc.ColumnName];
break;
case "Byte":
case "Byte[]":
cm.Parameters.Add(new OracleParameter(dc.ColumnName, OracleDbType.Blob)).Value = dr[dc.ColumnName];
break;
default:
break;
}
}
successCount += cm.ExecuteNonQuery();
}
connection.Close();
}
return successCount;
}
catch (Exception e)
{
return 0;
}
finally
{
GC.Collect();
}
}
private void SetDBConnection(string dbConnection, string dbType)
{
dbTypeString = dbType;
dbConnectionString = dbConnection;
}
private void GetDbConnection()
{
try
{
string fileName = string.Format(AppDomain.CurrentDomain.BaseDirectory + @"\dao.config");
if (fileName == null)
{
return;
}
XmlDocument doc = new XmlDocument();
doc.Load(fileName);
XmlElement stepXmlEl = doc.DocumentElement;
if (stepXmlEl == null)
{
return;
}
XmlNodeList conList = doc.GetElementsByTagName("dataSource");
XmlNodeList dbTpyeList = doc.GetElementsByTagName("provider");
dbConnectionString = conList[0].Attributes["connectionString"].Value;
dbTypeString = dbTpyeList[0].Attributes["name"].Value;
}
catch (Exception e)
{
}
}
}
}