/// <summary>
/// 生成二进制参数SqlParameter
/// </summary>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <returns></returns>
public IDataParameter CreateImageParameter(string parameterName, byte[] value)
{
IDataParameter param = new SqlParameter(parameterName, SqlDbType.Image);
param.Value = CommOp.TestNull(value);
return(param);
}
/// <summary>
/// 生成二进制参数
/// </summary>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <returns></returns>
public IDataParameter CreateImageParameter(string parameterName, byte[] value)
{
OleDbParameter param = new OleDbParameter(parameterName, OleDbType.Binary);
param.Value = CommOp.TestNull(value);
return(param);
}
public IDataParameter CreateImageParameter(string parameterName, byte[] value)
{
var p = new SQLiteParameter(parameterName, DbType.Object);
p.Value = CommOp.TestNull(value);
return(p);
}
/// <summary>
/// 生成SqlParameter
/// </summary>
/// <param name="parameterName"></param>
/// <param name="value"></param>
/// <returns></returns>
public IDataParameter CreateParameter(string parameterName, object value)
{
if (value is byte[])
{
return(CreateImageParameter(parameterName, (byte[])value));
}
IDataParameter param = new SqlParameter(parameterName, CommOp.TestNull(value));
return(param);
}
/// <summary>
/// 导入DataTable到数据库
/// </summary>
/// <param name="dt">内存中的数据表</param>
/// <param name="tableName">表名,如果为空，则以传入的DataTable的TableName作为表名</param>
/// <param name="buckCopy">是否使用批量导入, 对应的DBComm必须实现ISupportBuckCopy的接口</param>
/// <param name="notifyAfter">发生提示时导入的行数</param>
/// <param name="onRowsCopied">发生提示时执行的委托</param>
/// <returns>成功导入的行数</returns>
public virtual int Import(DataTable dt, string tableName = null, bool buckCopy = true, int notifyAfter = 10, Action <int> onRowsCopied = null)
{
if (dt == null)
{
throw new ArgumentNullException("dt");
}
if (onRowsCopied == null)
{
onRowsCopied = r => { }
}
;
if (tableName.IsEmpty())
{
tableName = dt.TableName;
}
if (notifyAfter <= 0)
{
throw new ArgumentException("notifyAfter<=0");
}
int rowsCopied = 0;
int rowCount = dt.Rows.Count;
//如果目标表不存在则创建
if (!DBComm.TableExists(tableName))
{
DBComm.CreateTable(dt, tableName);
}
//用bcp导入数据
if (buckCopy && DBComm is ISupportBuckCopy)
{
return(((ISupportBuckCopy)DBComm).BuckCopy(dt, tableName, notifyAfter, onRowsCopied));
}
else //用Sql Insert 导入数据
{
BeginTrans();
string sqlFields = "";
string sqlValues = "";
foreach (DataColumn f in dt.Columns)
{
if (!f.AutoIncrement)
{
sqlFields += String.Format(",{1}{0}{2}", f.ColumnName, DBComm.FieldPrefix, DBComm.FieldSuffix);
sqlValues += String.Format(",@{0}", f.ColumnName);
}
}
sqlFields = sqlFields.Substring(1);
sqlValues = sqlValues.Substring(1);
for (int i = 0; i < dt.Rows.Count; i++)
{
int j = 0;
IDataParameter[] sp = new IDataParameter[dt.Columns.Count];
foreach (DataColumn f in dt.Columns)
{
if (!f.AutoIncrement)
{
IDataParameter p = CreateParameter(f.ColumnName, CommOp.TestNull(dt.Rows[i][j]));
sp[j] = p;
}
j++;
}
string sql = String.Format("INSERT INTO {3}{0}{4}({1}) VALUES({2})", tableName, sqlFields, sqlValues, DBComm.FieldPrefix, DBComm.FieldSuffix);
try
{
ExecNonQuery(sql, sp);
}
catch (Exception ex)
{
RollbackTrans();
throw new TableImportException(ex, i + 1, 0);
}
if (i % notifyAfter == 0 || i == rowCount)
{
onRowsCopied(i);
}
}
EndTrans();
}
rowsCopied = dt.Rows.Count;
return(rowsCopied);
}
/// <summary>
/// 将value中的值赋给对象的属性
/// </summary>
/// <param name="obj">对象</param>
/// <param name="pi">对象的属性信息</param>
/// <param name="value">值</param>
void SetValue(object obj, PropertyInfo pi, object value)
{
pi.SetValue(obj, CommOp.HackType(value, pi.PropertyType), null);
}
/// <summary>
/// 生成参数
/// </summary>
/// <param name="parameterName">参数名称</param>
/// <param name="value">参数值</param>
/// <returns>参数对象</returns>
public virtual IDataParameter CreateParameter(string parameterName, object value)
{
return(DBComm.CreateParameter(parameterName, CommOp.TestNull(value)));
}
public IDataParameter CreateParameter(string parameterName, object value)
{
if (value is byte[])
{
return(CreateImageParameter(parameterName, (byte[])value));
}
var p = new Oracle.ManagedDataAccess.Client.OracleParameter(parameterName, CommOp.TestNull(value));
return(p);
}