.NET链接Oracle 参数绑定问题

在.NET项目中链接Oracle使用的驱动是 Oracle.ManagedDataAccess.dll ,这里下载
所遇到的问题
使用存储过程一个参数没有问题，发现两个或两个以上会有参数没传过来的现象。
最后通过排查发现是没有添加参数绑定(问题找了好长时间，刚开始还以为驱动的问题+_+)。
需要设置设置属性 BindByName = true;
下面附上 OracleHelper
  1 public class OracleHelper
  2     {
  3         /// <summary>
  4         /// 测试连接字符串
  5         /// </summary>
  6         /// <param name="connectionString"></param>
  7         /// <returns></returns>
  8         public static bool TestConn(string connectionString)
  9         {
 10             using (OracleConnection connection = new OracleConnection(connectionString))
 11             {
 12                 try
 13                 {
 14                     connection.Open();
 15                     return true;
 16                 }
 17                 catch (OracleException e)
 18                 {
 19                     connection.Close();
 20                     //throw e;
 21                     return false;
 22                 }
 23                 finally
 24                 {
 25                     connection.Close();
 26                 }
 27             }
 28         }
 29 
 30         /// <summary>
 31         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
 32         /// </summary>
 33         /// <param name="connectionString"></param>
 34         /// <param name="strSQL">查询语句</param>
 35         /// <returns>SqlDataReader</returns>
 36         public static OracleDataReader ExecuteReader(string connectionString, string strSQL)
 37         {
 38             OracleConnection connection = new OracleConnection(connectionString);
 39             OracleCommand cmd = new OracleCommand(strSQL, connection);
 40             try
 41             {
 42                 connection.Open();
 43                 OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 44                 return myReader;
 45             }
 46             catch (OracleException e)
 47             {
 48                 throw e;
 49             }
 50 
 51         }
 52 
 53         /// <summary>
 54         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
 55         /// </summary>
 56         /// <param name="SQLString">查询语句</param>
 57         /// <param name="connectionString"></param>
 58         /// <param name="cmdParms"></param>
 59         /// <returns>SqlDataReader</returns>
 60         public static OracleDataReader ExecuteReader(string connectionString, string SQLString, List<OracleParameter> cmdParms)
 61         {
 62             OracleConnection connection = new OracleConnection(connectionString);
 63             OracleCommand cmd = new OracleCommand();
 64             try
 65             {
 66                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
 67                 OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 68                 cmd.Parameters.Clear();
 69                 return myReader;
 70             }
 71             catch (OracleException e)
 72             {
 73                 throw e;
 74             }
 75             //            finally
 76             //            {
 77             //                cmd.Dispose();
 78             //                connection.Close();
 79             //            }    
 80 
 81         }
 82 
 83         private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, List<OracleParameter> cmdParms)
 84         {
 85             if (conn.State != ConnectionState.Open)
 86                 conn.Open();
 87             cmd.Connection = conn;
 88             cmd.CommandText = cmdText;
 89             if (trans != null)
 90                 cmd.Transaction = trans;
 91             cmd.CommandType = CommandType.Text;//cmdType;
 92             if (cmdParms != null)
 93             {
 94                 cmd.BindByName = true;
 95                 foreach (OracleParameter parameter in cmdParms)
 96                 {
 97                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
 98                         (parameter.Value == null))
 99                     {
100                         parameter.Value = DBNull.Value;
101                     }
102                     cmd.Parameters.Add(parameter);
103                 }
104             }
105         }
106 
107         /// <summary>
108         /// 执行查询语句，返回DataSet
109         /// </summary>
110         /// <param name="SQLString">查询语句</param>
111         /// <returns>DataSet</returns>
112         public static DataSet Query(string connectionString, string SQLString, List<OracleParameter> cmdParms)
113         {
114             using (OracleConnection connection = new OracleConnection(connectionString))
115             {
116                 OracleCommand cmd = new OracleCommand();
117                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
118                 using (OracleDataAdapter da = new OracleDataAdapter(cmd))
119                 {
120                     DataSet ds = new DataSet();
121                     try
122                     {
123                         da.Fill(ds, "ds");
124                         cmd.Parameters.Clear();
125                     }
126                     catch (OracleException ex)
127                     {
128                         throw new Exception(ex.Message);
129                     }
130                     return ds;
131                 }
132             }
133         }
134 
135         /// <summary>
136         /// 执行查询语句，返回DataSet
137         /// </summary>
138         /// <param name="SQLString">查询语句</param>
139         /// <returns>DataSet</returns>
140         public static DataSet Query(string connectionString, string SQLString)
141         {
142             using (OracleConnection connection = new OracleConnection(connectionString))
143             {
144                 DataSet ds = new DataSet();
145                 try
146                 {
147                     connection.Open();
148                     OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
149                     command.Fill(ds, "ds");
150                 }
151                 catch (OracleException ex)
152                 {
153                     throw new Exception(ex.Message);
154                 }
155                 return ds;
156             }
157         }
158 
159         //public static DataTable QueryDataTable(string connectionString, string SQLString)
160         //{
161         //    return Query(connectionString, SQLString).Tables[0];
162         //}
163 
164         public static DataTable QueryDataTable(string connectionString, string SQLString, List<JsonParamModel> jsonModels)
165         {
166             if (jsonModels.Count == 0)
167             {
168                 return Query(connectionString, SQLString).Tables[0];
169             }
170             var paramsList = new List<OracleParameter>();
171 
172             foreach (var json in jsonModels)
173             {
174                 //var sqlParameter = new SqlParameter(string.Format("@{0}", json.paramName), SqlDbType.Variant);
175                 //ParamAssignment(ref sqlParameter, json);
176                 var sqlParameter = new OracleParameter(string.Format(":{0}", json.paramName), json.paramValue);//@号调整成 :号
177                 //ParamAssignment(ref sqlParameter, json);
178                 paramsList.Add(sqlParameter);
179             }
180 
181             return Query(connectionString, SQLString, paramsList).Tables[0];
182         }
183 
184         private static void ParamAssignment(ref OracleParameter param, JsonParamModel jsonParamModel)
185         {
186             var dbType = jsonParamModel.paramDbType;
187             var value = jsonParamModel.paramValue;
188             switch (dbType)
189             {
190                 case "SqlDbType.DateTime":
191                     param.Value = DateTime.Parse(value);
192                     break;
193                 case "SqlDbType.UniqueIdentifier":
194                     param.Value = Guid.Parse(value);
195                     break;
196                 case "SqlDbType.Int":
197                     param.Value = int.Parse(value);
198                     break;
199                 default:
200                     param.Value = value;
201                     break;
202             }
203         }
204 
205         /// <summary>
206         /// 执行SQL语句，返回影响的记录数
207         /// </summary>
208         /// <param name="SQLString">SQL语句</param>
209         /// <returns>影响的记录数</returns>
210         public static int ExecuteSql(string connectionString, string SQLString)
211         {
212             using (OracleConnection connection = new OracleConnection(connectionString))
213             {
214                 using (OracleCommand cmd = new OracleCommand(SQLString, connection))
215                 {
216                     try
217                     {
218                         connection.Open();
219                         int rows = cmd.ExecuteNonQuery();
220                         return rows;
221                     }
222                     catch (OracleException e)
223                     {
224                         connection.Close();
225                         throw e;
226                     }
227                 }
228             }
229         }
230 
231         /// <summary>
232         /// 执行SQL语句，返回影响的记录数
233         /// </summary>
234         /// <param name="SQLString">SQL语句</param>
235         /// <returns>影响的记录数</returns>
236         public static int ExecuteSql(string connectionString, string SQLString, List<OracleParameter> cmdParms)
237         {
238             using (OracleConnection connection = new OracleConnection(connectionString))
239             {
240                 using (OracleCommand cmd = new OracleCommand())
241                 {
242                     try
243                     {
244                         cmd.BindByName = true;
245 
246                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
247                         int rows = cmd.ExecuteNonQuery();
248                         cmd.Parameters.Clear();
249                         return rows;
250                     }
251                     catch (System.Data.SqlClient.SqlException e)
252                     {
253                         throw e;
254                     }
255                 }
256             }
257         }
258 
259         public static int ExecuteSql(string connectionString, string SQLString, List<JsonParamModel> jsonModels)
260         {
261             if (jsonModels.Count == 0)
262             {
263                 return ExecuteSql(connectionString, SQLString);
264             }
265 
266             var paramsList = new List<OracleParameter>();
267 
268             foreach (var json in jsonModels)
269             {
270                 //var sqlParameter = new SqlParameter(string.Format("@{0}", json.paramName), SqlDbType.Variant);
271                 //ParamAssignment(ref sqlParameter, json);
272                 var sqlParameter = new OracleParameter(string.Format(":{0}", json.paramName), json.paramValue);
273                 //ParamAssignment(ref sqlParameter, json);
274                 paramsList.Add(sqlParameter);
275             }
276 
277             return ExecuteSql(connectionString, SQLString, paramsList);
278         }
279     }