我这里有个封装好的且无法修改的方法ExecuteSQL(db,sql)
ExecuteSQL只能单条语句(遇到分号即结束)
ExecuteSQL 的返回值是 1成功 0无效 -1失败
现在我想用这个方法进一步分装一个可以把datatable批量插入表中的方法
BulkInsert的返回值我们不要做任何的定义，保持原来封装好的方法返回值传递过来就好：
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元/天 解锁文章
扫一扫
983
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
