[ASP.NET]使用Oracle.ManagedDataAccess的OracleParameter参数化和OracleDataAdapter模糊查询

今天写个查询员工的信息的demo遇到了2个问题

问题1.使用Oracle.ManagedDataAccess的OracleParameter参数化

OracleParameter 的使用（参数名要以:开头，不允许包含@等特殊字符）

在使用OracleParameters时，CommandText 中的参数要以"："开头，不能包含@等特殊字符。而在其它地方引用到此参数时可以不必加上"："，程序会自动为其加上"："

1             cmdStr = "select * from sys_user_info where user_no = :userno or user_no = :usernoleave";
2             pars = new OracleParameter[]{
3             new OracleParameter("userno",OracleDbType.Varchar2,10),
4             new OracleParameter("usernoleave",OracleDbType.Varchar2,10),
5         };
6             pars[0].Value = TextBox1.Text.Trim();
7             pars[0].Direction = ParameterDirection.InputOutput;
8             pars[1].Value = "#" + TextBox1.Text.Trim() + "#";
9             pars[1].Direction = ParameterDirection.InputOutput;

参考下 OracleParameter 的使用（参数名要以:开头，不允许包含@等特殊字符）

问题2.OracleDataAdapter模糊查询

1   cmdStr = "select * from newmes.sys_user_info where user_no like '%'||:usernoleave||'%'";

1             cmdStr = "select * from newmes.sys_user_info where user_no like :usernoleave";
2             pars = new OracleParameter[]{
3                 new OracleParameter("usernoleave",OracleDbType.Varchar2,10),
4             };
5             pars[0].Value = "%"+TextBox1.Text.Trim()+"%";

整个Demo的代码如下：

 1    protected void Button1_Click(object sender, EventArgs e)
 2     {
 3         string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;
 4         string cmdStr = string.Empty;
 5         OracleParameter[] pars;        
 6         if (!CheckBox1.Checked)
 7         {
 8             cmdStr = "select * from sys_user_info where user_no = :userno or user_no = :usernoleave";
 9             pars = new OracleParameter[]{
10             new OracleParameter("userno",OracleDbType.Varchar2,10),
11             new OracleParameter("usernoleave",OracleDbType.Varchar2,10),
12         };
13             pars[0].Value = TextBox1.Text.Trim();
14             pars[0].Direction = ParameterDirection.InputOutput;
15             pars[1].Value = "#" + TextBox1.Text.Trim() + "#";
16             pars[1].Direction = ParameterDirection.InputOutput;
17         }
18         else
19         {
20             cmdStr = "select * from newmes.sys_user_info where user_no like '%'||:usernoleave||'%'";
21             pars = new OracleParameter[]{
22                 new OracleParameter("usernoleave",OracleDbType.Varchar2,10),
23             };
24             pars[0].Value = TextBox1.Text.Trim();
25             pars[0].Direction = ParameterDirection.InputOutput;
26         }
27         try
28         {
29             DataTable ds = new DataTable();
30             if (TextBox1.Text.Trim().Equals(""))
31             {
32                 throw new Exception("请输入工号");
33             }
34             using (OracleConnection con = new OracleConnection(ConnStr))
35             {
36                 using (OracleDataAdapter oda = new OracleDataAdapter(cmdStr, con))
37                 {
38                     
39                     oda.SelectCommand.Parameters.AddRange(pars);
40                     oda.SelectCommand.CommandType = CommandType.Text;                   
41                     oda.Fill(ds);
42                 }
43             }
44             if (ds.Rows.Count>0)
45             {
46                 lblMessage.Text = ds.Rows.Count+"pcs data";
47                 lblMessage.ForeColor = System.Drawing.Color.Red;
48                 GridView1.DataSource = ds;
49                 GridView1.DataBind();
50             }
51             else
52             {
53                 lblMessage.Text = "No Data";
54                 lblMessage.ForeColor = System.Drawing.Color.Red;
55             }
56 
57         }
58         catch (Exception ex)
59         {
60             Response.Write("<script>alert('" + ex.Message + "');</script>");
61             GridView1.DataSource = null;
62             GridView1.DataBind();
63             lblMessage.Text = "";
64         }
65     }