ASP.NET连接Oracle数据库
首先我们在oracle数据库管理工具P/L SQL Developer中创建一个表,假设为销售表(WARE_RETAIL_PART),并添加几条记录,结果如下:
接着,我们打开VS2010,然后在Web.config文件中找到<connectionStrings></connectionStrings>节点,在节点中添加如下一句话。
- <connectionStrings>
- <add name="OracleConnString" connectionString="Data Source=ORCL;User ID=crm;Password=Oracle11" providerName="System.Data.OracleClient"/>
- </connectionStrings>
其中,Data Source是oracle数据库的实例名,后面的分别是用户名和密码。
然后我们在项目App_Code文件夹下新建一个类,命名为DB.cs,该类主要用于连接和操作oracle数据库。编写代码如下:
- using System;
- using System.Data;
- using System.Configuration;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- using System.Data.SqlClient;
- using System.Data.OracleClient;
-
-
-
- public class DB
- {
-
-
- public DB()
- {
-
-
-
- }
-
- #region 配置连接字符串
-
-
-
- public static OracleConnection GetCon()
- {
-
- return new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnString"].ToString());
-
- }
-
- #endregion
-
-
- #region 执行SQL语句(增删改操作)
-
-
-
-
-
- public static bool exSql(string P_str_cmdtxt)
- {
- OracleConnection con = DB.GetCon();
- con.Open();
- OracleCommand cmd = new OracleCommand(P_str_cmdtxt, con);
- try
- {
- cmd.ExecuteNonQuery();
- return true;
- }
- catch (Exception e)
- {
- return false;
- }
- finally
- {
- con.Dispose();
- }
- }
- #endregion
-
- #region 返回DataSet结果集
-
-
-
-
-
- public static DataSet getDataSet(string P_str_cmdtxt)
- {
- OracleConnection con = DB.GetCon();
- OracleDataAdapter da = new OracleDataAdapter(P_str_cmdtxt, con);
- DataSet ds = new DataSet();
- da.Fill(ds);
- return ds;
- }
- #endregion
-
- #region 根据指定SQL语句select获取记录集合中的第一行数据
-
-
-
-
-
- public static DataRow GetDataRow(string sqlString)
- {
- DataSet ds = getDataSet(sqlString);
- ds.CaseSensitive = false;
- if (ds.Tables[0].Rows.Count > 0)
- {
- return ds.Tables[0].Rows[0];
- }
- else
- {
- return null;
- }
- }
- #endregion
-
- #region 查询数据是否存在的方法
-
-
-
-
-
- public static bool isName(string sql)
- {
-
- OracleConnection con = GetCon();
-
- con.Open();
-
- OracleCommand com = new OracleCommand(sql, con);
-
- if (Convert.ToInt32(com.ExecuteScalar()) > 0)
- {
- return true;
- }
- else
- {
- return false;
- }
- }
- #endregion
-
- #region 返回需要统计数据的结果(比如记录数)
-
-
-
-
-
- public static int countData(string sql)
- {
-
-
- OracleConnection con = GetCon();
-
- con.Open();
-
- OracleCommand com = new OracleCommand(sql, con);
-
- return Convert.ToInt32(com.ExecuteScalar());
-
- }
- #endregion
-
- #region 用来执行用户登录操作,使用参数传递
-
-
-
-
-
-
-
- public static bool entrySql(string sql, string name, string pass, string type)
- {
-
-
- OracleConnection con = DB.GetCon();
- con.Open();
-
- OracleCommand com = new OracleCommand(sql, con);
-
- com.Parameters.Add(new SqlParameter("name", SqlDbType.VarChar, 50));
-
- com.Parameters["name"].Value = name;
-
- com.Parameters.Add(new SqlParameter("pass", SqlDbType.VarChar, 50));
-
- com.Parameters["pass"].Value = pass;
- com.Parameters.Add(new SqlParameter("type", SqlDbType.VarChar, 50));
-
- com.Parameters["type"].Value = type;
-
- if (Convert.ToInt32(com.ExecuteScalar()) > 0)
- {
- con.Close();
- return true;
-
-
- }
- else
- {
- con.Close();
- return false;
- }
- }
- #endregion
-
- #region 绑定用户页面中的GridView控件
-
-
-
-
-
-
- public static bool BindGridView(GridView dl, string SqlCom)
- {
- dl.DataSource = DB.getDataSet(SqlCom);
- try
- {
- dl.DataBind();
- return true;
- }
- catch
- {
- return false;
- }
- finally
- {
- OracleConnection sqlcon = DB.GetCon();
- sqlcon.Close();
- }
- }
- #endregion
- public static bool BindDropDownList(DropDownList ddl, string SqlCom)
- {
- DataSet ds = DB.getDataSet(SqlCom);
- ddl.DataSource = ds.Tables[0].DefaultView;
- try
- {
- ddl.DataTextField = ds.Tables[0].Columns[0].ToString();
- ddl.DataValueField = ds.Tables[0].Columns[0].ToString();
- ddl.DataBind();
-
-
- return true;
- }
- catch
- {
- return false;
- }
- finally
- {
- OracleConnection sqlcon = DB.GetCon();
- sqlcon.Close();
- }
- }
-
- public static void JsExeC(Control up, string name, string js)
- {
-
- ScriptManager.RegisterClientScriptBlock(up, typeof(UpdatePanel), name, js, true);
- }
-
- public static string CleanSQL(string mString)
- {
- if (mString == null)
- mString = "";
- else
- {
- mString = mString.Replace("'", "''");
- mString = mString.Replace(";", "");
- mString = mString.Replace("--", "");
- }
- return mString;
- }
-
-
-
- }
在有了数据库操作类之后,我们可以在页面中访问我们的数据库了,这里我们测试下,在页面前端拖一个GridView,然后在后台查询销售表中的记录,并把记录绑定到GridView中。后台代码如下:
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Data;
- using System.Data.SqlClient;
- using System.Data.OracleClient;
- public partial class Oconnect : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- string sql = "select * from WARE_RETAIL_PART";
-
- DataSet ds = DB.getDataSet(sql);
- GridView1.DataSource = ds.Tables[0].DefaultView;
- GridView1.DataBind();
-
- }
- }
最后,我们点击运行VS,会在浏览器中看到我们销售表里的记录了,如果显示数据,说明连接oracle成功了!