如何在C＃sqlserver中创建通用参数化查询 [英] how to create a generic parameterized query in C# sqlserver

问题描述

我有一个插入多种表的通用插入方法，这是方法：

 使用（SqlConnection connection =  new  SqlConnection（connectionString））
 {
 connection.Open（）; 
 SqlCommand命令= 
  new  SqlCommand（
   INSERT INTO [ + tableName +  ]（ + columns +  ）OUTPUT INSERTED.ID VALUES（ + data +   ）;，
连接）; 
  return （ int ）command.ExecuteScalar（）; 
} 

如何将其转换为参数查询？

解决方案

 List< string> columns =  new 列表< string>（）{  Col1 ，  Col2}; 
列表< object> data =  new 列表< object>（）{ 1 ，DateTime.Now}; 
  var  tableName =   Table1; 
 
  //  以上数据将传递给方法 
 < span class =code-comment> //  我们可以将列名称填充如下 
  var  strCol =  string  .Join（ 列）; 
  //  和一组参数名称如下 
  var  strParam =  string  .Join（ ，，columns.Select（r = >   < span class =code-string> @ + r））; 
  //  现在我们可以构建完整的插入语句 
  var  sql =   INSERT INTO [ + tableName +  ]（ + strCol +  ）VALUES（ + strParam +  ）; 
 
 使用（SqlConnection connection =  new  SqlConnection（connectionString））
 {
 connection.Open（）; 
 SqlCommand command =  new  SqlCommand（sql，connection）; 
  //  添加名称和值的每个参数 
  for （ int  i =  0 ; i <  strParam.Length; i ++）
 {
 cmd.Parameters.AddWithValue（strParam [i]，data [i]）; 
} 
  return （ int ）command.ExecuteScalar（）; 
} < /   string  >  < /   string  >  

I have a generic insert method that insert to many kind of tables, this is the method:

using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.Open();
   SqlCommand command =
   new SqlCommand(
      "INSERT INTO [" +tableName+ "] (" +columns+ ") OUTPUT INSERTED.ID VALUES (" +data+ ");",
      connection);
   return (int)command.ExecuteScalar();
}

How can i convert it to parameter query?

解决方案

List<string> columns  = new List<string>() {"Col1", "Col2"};
List<object> data  = new List<object>() {1, DateTime.Now};
var tableName ="Table1";

// above data will pass to the method 
// we can buld the column names as below
var strCol = string.Join(",",columns);
// and set of parameter names like below 
var strParam = string.Join(",", columns.Select(r => "@" + r));
//now we can build the full insert statement 
var sql = "INSERT INTO [" +tableName+ "] (" +strCol+ ") VALUES (" +strParam+ ")";

using (SqlConnection connection = new SqlConnection(connectionString))
{
	connection.Open();
	SqlCommand command = new SqlCommand(sql , connection);
	//adding each parameter with name and value 
	for (int i = 0; i < strParam.Length; i++)
	{
	   cmd.Parameters.AddWithValue(strParam[i], data[i]);
	}
   return (int)command.ExecuteScalar();
}</string></string>

这篇关于如何在C＃sqlserver中创建通用参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！