ASP.NET操作ORACLE数据库之模糊查询

时间:2023-03-08 16:41:12
ASP.NET操作ORACLE数据库之模糊查询

ASP.NET操作ORACLE数据库之模糊查询

一、ASP.NET MVC利用OracleHelper辅助类操作ORACLE数据库

          //连接Oracle数据库的连接字符串
string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=localhost) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TestDB)));
User Id=developer; Password=developer";
     
/// <summary>
/// 制作商品查询页面 条件:根据商品名称模糊查询
/// </summary>
/// <returns></returns>
public ActionResult product(string ProName)
{
/*法一、直接使用like模糊查询
string sql = "select * from product where chvProName like '%' || upper(:ProName) || '%'";
*/
//法二、使用concat函数查询
string sql = "select * from product where chvProName like concat(concat('%', upper(:ProName)), '%')"; ViewBag.name = ProName;
//使用OracleHelper辅助类
DataSet ds = OracleHelper.ExecuteDataset(connectionString, CommandType.Text, sql,
new OracleParameter(":ProName", OracleType.NVarChar) { Value = ProName});
return View(ds.Tables[0]);
}

一、ASP.NET Web直接操作ORACLE数据库

//连接Oracle数据库的连接字符串
string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
                (HOST=localhost) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TestDB)));
                User Id=developer; Password=developer";
protected void Button1_Click(object sender, EventArgs e)
{
string proName = this.txtProName.Text.Trim();
/*法一、直接使用like模糊查询
string sql = "select * from product where chvProName like '%'|| upper(:proName) ||'%'";
*/
//法二、使用concat函数查询
string sql = "select * from product where chvProName like concat(concat('%', upper(:proName)), '%')";
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.Parameters.Add(new OracleParameter(":proName", proName) { OracleType = OracleType.NVarChar });
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds); this.GvPro.DataSource = ds.Tables[0].DefaultView;
this.GvPro.DataBind();
}


说明:

  在做该例子的时候,由于是刚接触Oracle,所以很多语句和方法一直都停留在MSSQLServer里面,所以刚开始做的时候写模糊查询like语句的时候居然把“||”写成了“+”,还调试了好几遍,后来被同学指出来了还被他笑了老半天!

  呵呵,,关于Oracle中字符之间的链接我想估计这辈子我都忘不了了:

        Oracle中字符之间的链接用"||"和函数CONCAT(),而非“+”;