与域聚合函数C#参数化查询(DMIN) [英] C# parameterized query with Domain Aggregate functions (DMin)
问题描述
我想用下面的SQL语句的小C#应用程序。
SQL语句运行在Access 2013完美。
<预类=郎-SQL prettyprint-覆盖>
INSERT INTO adressed_to(对话,听众)
值(
DMIN(ID,人物,char_name ='李四'AND用的名字='约翰'),
DMIN(ID,对话,喇叭= 3,dialog_text ='一些文本')
);
当我尝试运行下面的C#代码,我得到一个参数太少的例外,我特里普尔检查拼写,我甚至复制访问查询字符串。
更改在双引号单引号没有工作,我得到了同样的异常。它甚至有可能为C#中运行此查询?
其他查询工作正常。
<预类=郎-cs prettyprint-覆盖>
字符串addListeners = @INSERT INTO adressed_to(对话,听众)
值(
DMIN('身份证','图','char_name =? AND用的名字='),
DMIN('身份证','对话,喇叭= AND dialog_text =')
);??
的foreach(在d.addressed_to字符听众)
{
使用(OleDbCommand的CMD =新的OleDbCommand(addListeners,dbConn))
{
cmd.Parameters.AddWithValue( ?,listener.name);
cmd.Parameters.AddWithValue(,listener.forename?);
cmd.Parameters.AddWithValue(,speakerID?);
cmd.Parameters.AddWithValue(,d.dialog_text?);
cmd.ExecuteNonQuery();
}
}
更改字符串以下的建议没有工作
<预类=郎-CS prettyprint-覆盖>
@INSERT INTO adressed_to(对话,听众)
值(
DMIN(ID,人物,char_name =?AND用的名字=),
DMIN(ID,对话,音箱= AND dialog_text =)
);?;
例外:
类型的异常'System.Data.OleDb.OleDbException'发生在
System.Data.dll中,但在用户代码中
附加信息没有处理:参数太少。预计2
块引用>
解决方案看起来比较难看,但这种使用对我的作品
提供程序= Microsoft.ACE.OLEDB.12.0
:
<预类=郎-CS prettyprint-覆盖>字符串addListeners =
@INSERT INTO adressed_to(对话,听众)
值(
DMIN('身份证','图','char_name = '和;?&放大器;'而用名字='和;?&放大器;','),
DMIN('身份证','对话,喇叭='和;?&安培; 'AND dialog_text ='和;?&放大器;'')
);;
使用(VAR CMD =新的OleDbCommand(addListeners,dbConn))
{
cmd.Parameters.Add(,OleDbType.VarWChar,255?).value的=李四;
cmd.Parameters.Add(,OleDbType.VarWChar,255?).value的=约翰;
cmd.Parameters.Add(,OleDbType.Integer?)。价值= 3;
cmd.Parameters.Add(,OleDbType.VarWChar,255?).value的=一些文本;
cmd.ExecuteNonQuery();
}
也工作好与
<预类=郎-CS prettyprint-覆盖>cmd.Parameters.Add(,OleDbType.VarWChar,255?).value的=奥赖利;
但如果文本参数值包含孤双引号它失败。 (
I want to use the following SQL statement in a small C# application.
The SQL statement runs perfect in Access 2013.
INSERT INTO adressed_to (dialog,listener) VALUES ( DMin("id", "FIGURE", "char_name='Doe' AND forename='John'"), DMin("id", "DIALOG", "speaker=3 AND dialog_text='some text'") );
When i try to run the following C# code, i get a "Too few parameters" exception, i tripple checked the spelling, i even copied the string from the Access query. Changing the single quotes in double quotes did not work, i got the same exception. Is it even possible to run this query within C#?
Other queries are working fine.
string addListeners = @"INSERT INTO adressed_to (dialog,listener) VALUES ( DMin('id', 'FIGURE', 'char_name =? AND forename =?'), DMin('id', 'DIALOG', 'speaker=? AND dialog_text=?') ); "; foreach (Character listener in d.addressed_to) { using (OleDbCommand cmd = new OleDbCommand(addListeners, dbConn)) { cmd.Parameters.AddWithValue("?", listener.name); cmd.Parameters.AddWithValue("?", listener.forename); cmd.Parameters.AddWithValue("?", speakerID); cmd.Parameters.AddWithValue("?", d.dialog_text); cmd.ExecuteNonQuery(); } }
Changing the string to the following as suggested did not work:
@"INSERT INTO adressed_to (dialog,listener) VALUES ( DMin(""id"", ""FIGURE"", ""char_name =? AND forename =?""), DMin(""id"", ""DIALOG"", ""speaker=? AND dialog_text=?"") ); ";
The exception:
An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code Additional information: Too few parameters. Expected 2.
解决方案It looks rather ugly, but this works for me using
Provider=Microsoft.ACE.OLEDB.12.0
:string addListeners = @"INSERT INTO adressed_to (dialog,listener) VALUES ( DMin('id', 'FIGURE', 'char_name=""' & ? & '"" AND forename=""' & ? & '""'), DMin('id', 'DIALOG', 'speaker=' & ? & ' AND dialog_text=""' & ? & '""') ); "; using (var cmd = new OleDbCommand(addListeners, dbConn)) { cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = "Doe"; cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = "John"; cmd.Parameters.Add("?", OleDbType.Integer).Value = 3; cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = "some text"; cmd.ExecuteNonQuery(); }
It also works okay with
cmd.Parameters.Add("?", OleDbType.VarWChar, 255).Value = "O'Reilly";
but it does fail if the text parameter value contains solitary double-quotes. :(
这篇关于与域聚合函数C#参数化查询(DMIN)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!