如何使用DbParameter将多个参数传递给c＃中的参数化查询？

Question

我想开发适用于所有类型数据库（Oracle，MySQL，SQLite）的通用应用程序。所以我从一个共同的地方为每个表单提供了不同的数据源。

而且我也想使用参数化查询谁可以接受多个参数。我无法使用DbParameter传递多个参数，它可以很好地处理单个参数查询，但不是很多。

所以，请帮我...

  dbconnect dc = new dbconnect();//class which contain data source name 
      DbConnection con; 
      DbCommand cmd; 
      DbDataReader dr; 
      DbProviderFactory df; 
      DataAdapter da; 

      df = DbProviderFactories.GetFactory(dbconnect.dbprovider); 
      con = df.CreateConnection(); 
      con.ConnectionString = dbconnect.sqlstr; 
      con.Open(); 
      cmd = df.CreateCommand(); 
      cmd.CommandText = "update customer set" + " name='@name'," + "address='@address'," + "phone='@phone' " + "where code='@code';"; 
      cmd.Connection = con; 
      DbDataAdapter daa = df.CreateDataAdapter(); 
      daa.UpdateCommand = cmd; 

      DbParameter param = df.CreateParameter(); 

      param.ParameterName = "@name"; 
      param.Value = txtname.Text; 

      param.ParameterName = "@address"; 
      param.Value = txtadd.Text; 

      param.ParameterName = "@phone"; 
      param.Value = txtphone.Text; 

      param.ParameterName = "@code"; 
      param.Value = txtcode.Text; 
      daa.UpdateCommand.Parameters.Add(param); 
      daa.UpdateCommand.ExecuteNonQuery(); 

      **But this not working.** 

Answer 1

你只是建立一个参数

 DbParameter param = df.CreateParameter(); 

     param.ParameterName = "@name"; 
     param.Value = txtname.Text; 

等等

你需要为每一个创建一个新DbParameter

 DbParameter param = df.CreateParameter(); 
     param.ParameterName = "@name"; 
     param.Value = txtname.Text; 
     daa.UpdateCommand.Parameters.Add(param); 

     param = df.CreateParameter(); 
     param.ParameterName = "@address"; 
     param.Value = txtadd.Text; 
     daa.UpdateCommand.Parameters.Add(param); 

etc ....

并将每一个添加到该命令。

Answer 2

这段代码的问题是：

DbParameter param = df.CreateParameter(); 

param.ParameterName = "@name"; 
param.Value = txtname.Text; 

param.ParameterName = "@address"; 
param.Value = txtadd.Text; 

param.ParameterName = "@phone"; 
param.Value = txtphone.Text; 

param.ParameterName = "@code"; 
param.Value = txtcode.Text; 
daa.UpdateCommand.Parameters.Add(param); 

你正在创建一个参数对象，改变其ParameterName和Value性能几次，然后补充说单参数的命令。这会给你一个带有单个参数的命令，其名称和值将是你设置的最后一个参数。

相反，您要添加几个单独的参数，因此您需要多次拨打Parameters.Add。例如：

DbParameter nameParam = df.CreateParameter(); 
nameParam.ParameterName = "@name"; 
nameParam.Value = txtname.Text; 
daa.UpdateCommand.Parameters.Add(nameParam); 

DbParameter addressParam = df.CreateParameter(); 
addressParam.ParameterName = "@address"; 
addressParam.Value = txtadd.Text; 
daa.UpdateCommand.Parameters.Add(addressParam); 
// etc 

这可以通过提取方法添加一个参数与所述给定的名称和值来进行了很多清洁器：

AddParameter(daa.UpdateCommand, "@name", txtname.Text); 
AddParameter(daa.UpdateCommand, "@address", txtadd.Text); 
// etc 

Answer 3

DbDataAdapter的通常用于散装或多个行更新。 对于单行更新使用DbCommand。

DbProviderFactory df = DbProviderFactories.GetFactory(dbconnect.dbprovider);    
      using(DbConnection con = df.CreateConnection()) 
      { 
       con.ConnectionString = "dbconnect.sqlstr";    
       DbCommand cmd = df.CreateCommand(); 
       cmd.CommandText = "update customer set" + " name='@name'," + "address='@address'," + "phone='@phone' " + "where code='@code';"; 
       cmd.Connection = con; 

       DbParameter param1 = df.CreateParameter(); 
       param1.ParameterName = "@name"; 
       param1.Value = txtname.Text; 

       DbParameter param2 = df.CreateParameter(); 
       param2.ParameterName = "@address"; 
       param2.Value = txtadd.Text; 

       DbParameter param3 = df.CreateParameter(); 
       param3.ParameterName = "@phone"; 
       param3.Value = txtphone.Text; 

       DbParameter param4 = df.CreateParameter(); 
       param4.ParameterName = "@code"; 
       param4.Value = txtcode.Text; 

       con.Open(); 
       cmd.ExecuteNonQuery(); 
      }