当传递具有新行的 DataRow 集合时...。或“Insert into 语句的语法错误”

本文介绍了一种在使用OleDbCommandBuilder向Access数据库插入数据时遇到的“Insertinto语句的语法错误”的解决方法。通过调整字段名称并在SQL查询中使用方括号,最终解决了由系统保留字引起的错误。

“Insert into 语句的语法错误”的解决方法

前段时间用vb.net+access做练习发现了个问题.我定义了一个非常简单的select查询在sql server企业管理器中没问题,但在.net IDE中调试就通不过,提示 "使用OleDbCommandBuilder时出现“Insert into 语句的语法错误”的解决方法".后来发现是数据库中表的字段名有问题,baidu一下找到下面的东东





 当我们用C#开发数据库访问程序的时候,通常有3种方式:odbc,oleDb,ado.net;其实我想对于这三种方式,这个问题都会出现,但是这次我遇到问题的时候用的是oleDb的方式,我们就用oleDb的方式来描述吧。

 

问题描述:

     我用OleDb的方式向Access数据里写数据,示例源码如下:

    string sql="select * from MultiTable";
    OleDbDataAdapter oleSub=new OleDbDataAdapter(sql,oleCn);
    OleDbCommandBuilder cb1=new OleDbCommandBuilder(oleSub);

   DataSet ds=new DataSet();

   oleSub.Fill(ds."MultiTable");

   DataTable dt=ds.Tables["MultiTable"];

   DataRow dr=dt.NewRow();

   dr["PRSERV"]="WS"+index.ToString().PadLeft(6,''''0'''');

  dr["NUMBER"]="00063";

 ....................................

  dt.Rows.Add(dr);

  oleSub.Update(ds,"MulitTable");
  这段代码编译的时候是没有问题的,但是在运行的时候,会报出一个运行时错误:”Insert into 语句的语法错误“。

   用OleDbAdapter的时候,我并没有指定Insert语句,而是用OleDbCommandBuilder 来自动产生Insert 语句的。仔细想了一下,为什么会产生这个错误呢?我的结论是,可能这张表里的字段名使用了access系统的保留字。于是我在Access里创建了一个查询,自己写了一个insert sql,证实我的结论是正确的,NUMBER是系统的一个保留字,那怎么修改呢?

   一般来说,最简单的方法就是改掉这个字段名,换成非系统保留字的名字,但是库的结构是客户提供的,不允许修改,只有想别的办法。考虑以前的经验,操作Access,Sql Server的时候,如果表的字段中包含了系统的保留字的话,我们在字段外加上方括号就可以了,比如 insert into tblmultitable(prserv,[NUMBER]) values(.......)就可以了。可是从上面的代码中我们看到并没有什么地方我们可以指定Insert 语句。我想OleDbCommandBuilder应该是根据Adapter使用的select语句自动生成insert 语句的,所以只要给select 语句中的字段加上方括号就可以了,所以我作了如下的修改:

   string sql="select PRSERV,[NUMBER],PriorRef,Grantor,Grantee from MultiTable";

   修改完毕以后,测试以后,仍然产生以前的"Insert into 语句的语法错误";问题会出在哪里呢?我想应该还是在OleDbCommanBuilder上,一般来说,只需要这样用OleDbCommanBuilder类就可了:

    OleDbDataAdapter oleSub=new OleDbDataAdapter(sql,oleCn);
    OleDbCommandBuilder cb1=new OleDbCommandBuilder(oleSub); 这句比能少——会报“当传递具有新行的 DataRow 集合时...”的错

    打开MSDN,看看OleDbCommanBuilder的类成员。发现两个很关键的属性:QuotePrefix,QuoteSuffix;仔细想想,OleDb可以访问的数据类型非常多啊,所以关键字段的前缀,后缀的处理方法肯定不尽相同,比如访问Excel的时候表明应该写成[sheet1$的方式],所以提供这样一种方式是相当灵活的。接下来我再次修改代码,对这两个属性赋值:

    OleDbDataAdapter oleSub=new OleDbDataAdapter(sql,oleCn);
    OleDbCommandBuilder cb1=new OleDbCommandBuilder(oleSub);
    cb1.QuotePrefix="[";
    cb1.QuoteSuffix="]";

再次测试,通过!

我这里有个封装好的且无法修改的方法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."); } } }
09-05
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值