我这里有两个封装好的且无法修改的两个方法ExecuteSQL(db,sql)和ExecuteBatchSQL(db,sql[])
ExecuteSQL只能单条语句(遇到分号即结束)
ExecuteBatchSQL相当于是把很多ExecuteSQL要执行的sql放在sqlstr数组中执行。
ExecuteSQL 的返回值是 1成功 0无效 -1失败
ExecuteBatchSQL的返回值是 一个数组 每一条sql的 1成功 0无效 -1失败
他们的封装底层逻辑是一样的只是传入的sql参数不一样
现在我想用这两个方法(你视情况而定选择合适的)进一步分装一个可以把datatable批量插入表中的方法：
public static int BulkInsert(string tableName, DataTable srcDt)
{
// retVal > 0 ok
// retVal = 0 no effect
// retVal < 0 error
throw new NotImplementedException();
}
这个方法是来自下面这个DataTool类的，请帮我写好这个方法，并使用兼容C# 5.0 的语法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using DataEngineProxy;
namespace iCostSystem.DataSource
{
internal static class DataTool
{
private const string Db = "FP3DB";
private static readonly DynaWS Ws = new DynaWS("S1Report","iCostSystem","S1",DynaWS._CSharp,"S1Report");
internal static QueriableSqlObj CreateQuery(string sql)
{
var o1 = new QueriableSqlObj(sql);
return o1;
}
internal static QueriableSqlObj SetParameter<T>(this QueriableSqlObj sqlObj, string paramName, T value) where T: IConvertible
{
return sqlObj.AddParam(paramName, value) ? sqlObj : null;
}
internal static DataTable ToDataTable(this QueriableSqlObj sqlObj)
{
var sql = sqlObj.GenerateSql();
return Ws.GetDataTable(Db, sql);
}
internal static List<object> ToList(this QueriableSqlObj sqlObj, string colName = null)
{
var dt = sqlObj.ToDataTable();
return dt.DataTableToSingleList(colName);
}
public static ExecutableSqlObj CreateExecutor(string sqlDel)
{
//throw new NotImplementedException();
return new ExecutableSqlObj(sqlDel);
}
internal static ExecutableSqlObj SetParameter<T>(this ExecutableSqlObj sqlObj, string paramName, T value) where T: IConvertible
{
//throw new NotImplementedException();
sqlObj.AddParam(paramName, value);
return sqlObj;
}
public static int Execute(this ExecutableSqlObj sqlObj)
{
//throw new NotImplementedException();
string finalSql = sqlObj.GenerateSql();
return Ws.ExecuteSQL(Db, finalSql);
}
public static DataTable GetDataTable(string sql)
{
return Ws.GetDataTable(Db, sql);
}
public static int BulkInsert(string tableName, DataTable srcDt)
{
// retVal > 0 ok
// retVal = 0 no effect
// retVal < 0 error
throw new NotImplementedException();
}
public static int ExecuteSql(string sqlToExec)
{
//throw new NotImplementedException();
return Ws.ExecuteSQL(Db,sqlToExec);
}
public static List<object> GetList(string sqlToQuery, string colName = null)
{
return Ws.GetDataTable(Db, sqlToQuery).DataTableToSingleList(colName);
}
public static void ExecuteProcedure(string procName)
{
throw new NotImplementedException();
}
internal static int BulkMerge(string tableName, DataTable srcDt, string[] onColumns)
{
//throw new NotImplementedException();
if (srcDt == null || srcDt.Rows.Count == 0)
return 0;
if (onColumns == null || onColumns.Length == 0)
throw new ArgumentException("onColumns 不能为空");
var tempTableName = "TempDataTable"; // Oracle 临时表名
var columns = new List<string>();
foreach (DataColumn column in srcDt.Columns)
{
columns.Add(column.ColumnName);
}
// 构建 ON 子句
var onClause = new StringBuilder();
for (int i = 0; i < onColumns.Length; i++)
{
if (i > 0) onClause.Append(" AND ");
onClause.AppendFormat("t.{0} = s.{0}", onColumns[i]);
}
// 构建 UPDATE SET 子句
var updateSet = new StringBuilder();
for (int i = 0; i < srcDt.Columns.Count; i++)
{
string colName = srcDt.Columns[i].ColumnName;
if (Array.IndexOf(onColumns, colName) >= 0)
continue;
if (updateSet.Length > 0)
updateSet.Append(", ");
updateSet.AppendFormat("t.{0} = s.{0}", colName);
}
// 构建 INSERT 子句
var insertColumns = string.Join(", ", columns.ToArray());
var insertValues = string.Join(", ", columns.Select(c => "s." + c).ToArray());
// 创建临时表（Oracle 全局临时表）
var createTempTableSql = string.Format(@"
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE {0} (
{1}
) ON COMMIT PRESERVE ROWS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;",
tempTableName,
string.Join(", ", columns.Select(c => c + " " + GetOracleType(srcDt.Columns[c].DataType, srcDt.Columns[c].MaxLength)).ToArray())
);
// 清空临时表
var clearTempTableSql = string.Format("DELETE FROM {0};", tempTableName);
// 插入数据到临时表
var insertTempSql = new StringBuilder();
foreach (DataRow row in srcDt.Rows)
{
var values = new List<string>();
foreach (DataColumn column in srcDt.Columns)
{
if (row.IsNull(column))
{
values.Add("NULL");
}
else if (column.DataType == typeof(string))
{
values.Add("'" + row[column].ToString().Replace("'", "''") + "'");
}
else
{
values.Add("'" + row[column].ToString() + "'");
}
}
insertTempSql.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2});\n",
tempTableName, string.Join(", ", columns.ToArray()), string.Join(", ", values.ToArray()));
}
// MERGE INTO SQL
var mergeSql = string.Format(@"
MERGE INTO {0} t
USING {1} s
ON ({2})
WHEN MATCHED THEN
UPDATE SET {3}
WHEN NOT MATCHED THEN
INSERT ({4})
VALUES ({5});",
tableName, tempTableName, onClause, updateSet, insertColumns, insertValues);
// DROP 临时表（Oracle 会自动清理，但可以手动执行）
var dropTempTableSql = string.Format("DELETE FROM {0};", tempTableName);
// 拼接完整 SQL 并执行
var fullSql = string.Format(@"
{0}
{1}
{2}
{3}
{4}",
createTempTableSql,
clearTempTableSql,
insertTempSql,
mergeSql,
dropTempTableSql);
return ExecuteSql(fullSql);
}
private static string GetOracleType(Type type, int maxLength = 0)
{
if (type == typeof(int) || type == typeof(short) || type == typeof(long))
return "NUMBER";
if (type == typeof(string))
return maxLength > 0 ? string.Format("VARCHAR2({0})", maxLength) : "VARCHAR2(4000)";
if (type == typeof(DateTime))
return "DATE";
if (type == typeof(decimal))
return "NUMBER(18,2)";
if (type == typeof(bool))
return "CHAR(1)";
if (type == typeof(double) || type == typeof(float))
return "BINARY_DOUBLE";
if (type == typeof(byte[]))
return "BLOB";
return "VARCHAR2(4000)";
}
public static bool TestDataEngine()
{
Ws.Login();
return Ws.isLogined();
}
public static void Close()
{
Ws.Logout();
}
private static List<object> DataTableToSingleList(this DataTable src, string colName)
{
if (src == null)
return null;
if (colName != null)
{
return !src.Columns.Contains(colName) ? null : src.AsEnumerable().Select(x => x[colName]).ToList();
}
return src.AsEnumerable().Select(x => x[0]).ToList();
}
}
internal class SqlObj
{
public string Sql { get; private set; }
private readonly Dictionary<string, string> _paramsObjects;
public SqlObj(string sql)
{
Sql = sql;
_paramsObjects = new Dictionary<string, string>();
}
public bool AddParam<T>(string key, T obj) where T: IConvertible
{
var type = typeof(T);
if (type.IsValueType)
{
_paramsObjects.Add(":" + key, obj.ToString());
}else if (type == typeof(string))
{
_paramsObjects.Add(":" + key, "'"+obj+"'");
}
else
{
return false;
}
return true;
}
public string GenerateSql()
{
return _paramsObjects.Aggregate(Sql,
(current, p) =>
Regex.Replace(current, p.Key, p.Value, RegexOptions.IgnoreCase | RegexOptions.Multiline));
}
public static string ExtractSqlStatement(string sql)
{
var sqlPart = sql.Take(50).ToString();
var words = sqlPart.Split(new [] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
var fword = words.FirstOrDefault(s => !string.IsNullOrWhiteSpace(s));
return fword == null ? "" : fword.ToUpper();
}
}
internal class QueriableSqlObj: SqlObj
{
public QueriableSqlObj(string sql) : base (sql)
{
if (ExtractSqlStatement(sql).Equals("SELECT"))
throw new ArgumentException("Invalid Sql String, expect: [select] statement.");
}
}
internal class ExecutableSqlObj: SqlObj
{
public ExecutableSqlObj(string sql) : base (sql)
{
var statement = ExtractSqlStatement(sql);
if (!"MERGE,INSERT,DELETE,UPDATE".Contains(statement))
throw new ArgumentException("Invalid Sql String, expect: [update,delete,insert,merge] statement.");
}
}
}
这篇博客通过测试对比了SQL查询、Array.indexOf、Linq和DataTable.Select在处理10万条数据时的效率。结果显示，SQL查询耗时最长，DataTable.Select最快，效率相差约240倍。作者重构代码后，程序运行时间从1个半小时缩短到1分钟以内，强调了数据库连接的昂贵成本。
最低0.47元/天 解锁文章
扫一扫
999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
