使用未知名称/参数数量更新sql语句 [英] update sql statement with unknown name/amount of params
问题描述
我有一个经典的ASP网站,正在逐步升级。我想创建一个安全更新SQL数据库的功能,而无需手动指定参数。
(我不想使用实体框架或Linq)
这是到目前为止的代码:
string updateSql = UPDATE sometable + SET test1 = @ testData1 +其中a = @ aData1;
SqlCommand UpdateCmd =新的SqlCommand(updateSql,conn);
UpdateCmd.Parameters.Add( @ testData1,SqlDbType.NVarChar,10, testData1);
UpdateCmd.Parameters.Add( @ aData1,SqlDbType.NVarChar,20, aData1);
UpdateCmd.Parameters [ @ testData1] .Value = 21515;
UpdateCmd.Parameters [ @ aData1]。Value = 32t3t;
UpdateCmd.ExecuteNonQuery();
伪代码(我想实现)
创建一个涵盖所有变量的Ilist {get;设置:} [在此处验证类型/长度]
为每个包含值的变量(无验证问题)创建sql更新字符串。
执行它。
可能的问题:
我唯一可以预见的问题是列表可能有500个变量,但是每个SQL更新可能只更新2或3列。这样效率不高吗?
您需要执行以下操作...。显然需要更多的编码....
静态void Main(string [] args)
{
var values = new Dictionary< string,object>();
values.Add( name, timmerz);
values.Add( dob,DateTime.Now);
values.Add( sex, m);
SqlUpdate( sometable,values);
}
公共静态无效SqlUpdate(字符串表,字典< string,object>值,字符串where)
{
var equals = new List< string>( );
var parameters = new List< SqlParameter>();
var i = 0;
foreach(值中的可变项)
{
var pn = @sp + i.ToString();
equals.Add(string.Format( {0} = {1},item.Key,pn));
parameters.Add(new SqlParameter(pn,item.Value));
i ++;
}
字符串命令= string.Format( update {0} set {1} where {2},table,string.Join(,,equals.ToArray() ),其中);
var sqlcommand = new SqlCommand(命令);
sqlcommand.Parameters.AddRange(parameters.ToArray());
sqlcommand.ExecuteNonQuery();
}
I have a classic ASP site, that I am slowly upgrading. I would like to create a function to securely update a SQL database without specifying parameters manually. Something just a tad more dynamic.
(I do not want to use entity framework or Linq)
Here is the code so far:
string updateSql = "UPDATE sometable" + "SET test1= @testData1 " + "WHERE a = @aData1";
SqlCommand UpdateCmd = new SqlCommand(updateSql, conn);
UpdateCmd.Parameters.Add("@testData1 ", SqlDbType.NVarChar, 10, "testData1 ");
UpdateCmd.Parameters.Add("@aData1", SqlDbType.NVarChar, 20, "aData1");
UpdateCmd.Parameters["@testData1 "].Value = "21515";
UpdateCmd.Parameters["@aData1"].Value = "32t3t";
UpdateCmd.ExecuteNonQuery();
pseudo-code (what I would like to achieve)
Create an Ilist covering all variables {get; set:} [validate type/length here]
For every variable that contains a value (without validation issues) create sql update string.
Execute it.
Possible problem: The only problem I can foresee, is that the list may have 500 variables, but each SQL update may only have only 2 or 3 columns being updated. Is this not efficient?
you need to do something like this....needs more coding obviously....
static void Main(string[] args)
{
var values = new Dictionary<string, object>( );
values.Add( "name", "timmerz" );
values.Add( "dob", DateTime.Now );
values.Add( "sex", "m" );
SqlUpdate( "sometable", values );
}
public static void SqlUpdate( string table, Dictionary<string,object> values, string where )
{
var equals = new List<string>( );
var parameters = new List<SqlParameter>( );
var i = 0;
foreach( var item in values )
{
var pn = "@sp" + i.ToString( );
equals.Add( string.Format( "{0}={1}", item.Key, pn ) );
parameters.Add( new SqlParameter( pn, item.Value ) );
i++;
}
string command = string.Format( "update {0} set {1} where {2}", table, string.Join( ", ", equals.ToArray( ) ), where );
var sqlcommand = new SqlCommand(command);
sqlcommand.Parameters.AddRange(parameters.ToArray( ) );
sqlcommand.ExecuteNonQuery( );
}
这篇关于使用未知名称/参数数量更新sql语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!