你的想法很重要-大家都来谈 (C)Copyright 2012 by WeiPt

C#访问Oracle数据库

随着时间的推移知识也在更新,原来可用的技术也会被淘汰或更新。

framework4.0开始不再支持System.Data.OracleClient了,但是令人欣慰的是ORACLE公司自己出了一个Oracle.ManagedDataAccess链接库。

下载地址:https://files.cnblogs.com/files/weipt/OracleBase.rar

添加引用之后就可以在c#中使用了。

1.连接字符串如下

<connectionStrings>
<add name="ConnectionString" connectionString="server=server;uid=sa;pwd=sa;database=dy_db;"/>
<add name="ConnectionString_Oralce" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.254)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=tdc)));Persist Security Info=True;User ID=tdc;Password=tdc;"/>
</connectionStrings>

不用建立tns监听文件,不用安装庞大的oracle数据库客户端

2.建立公共的数据访问方法

    /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {                
                using (OracleCommand cmd = new OracleCommand(SQLString,connection))
                {
                    try
                    {        
                        connection.Open();
                        int rows=cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch(OracleException E)
                    {                    
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }                
            }
        }
        /// <summary>
        /// 执行SQL语句,返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString,params OracleParameter[] cmdParms)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {                
                using (OracleCommand cmd = new OracleCommand())
                {
                    try
                    {        
                        PrepareCommand(cmd, connection, null,SQLString, cmdParms);
                        int rows=cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch(OracleException E)
                    {                
                        throw new Exception(E.Message);
                    }
                }                
            }
        }
 1     /// <summary>
 2         /// 执行查询语句,返回DataSet
 3         /// </summary>
 4         /// <param name="SQLString">查询语句</param>
 5         /// <returns>DataSet</returns>
 6         public static DataSet Query(string SQLString)
 7         {
 8             using (OracleConnection connection = new OracleConnection(connectionString))
 9             {
10                 DataSet ds = new DataSet();
11                 try
12                 {
13                     connection.Open();
14                     OracleDataAdapter command = new OracleDataAdapter(SQLString,connection);                
15                     command.Fill(ds,"ds");
16                 }
17                 catch(OracleException ex)
18                 {                
19                     throw new Exception(ex.Message);
20                 }            
21                 return ds;
22             }            
23         }
View Code

3.使用时和sqlserver不同的地方

1>如果采用sql字符串查询,那么查询语句要遵循ORACLE标准,日期格式要用

to_date('2017-05-24 12:12:12','yyyy-mm-dd hh24:mi:ss')

2>不支持top关键字等关键字

3>如果用传参执行的化,不用转换日期格式,但是不是@了,而是:冒号

 1     /// <summary>
 2         /// 更新一条数据
 3         /// </summary>
 4         public bool Update(OMaticsoft.Model.MDEVICECRUNTIME model)
 5         {
 6             StringBuilder strSql=new StringBuilder();
 7             strSql.Append("update DEVICECRUNTIME set ");
 8             strSql.Append("PARAVALUE=:PARAVALUE,");
 9             strSql.Append("RECEIVETIME=:RECEIVETIME");
10             strSql.Append(" where DEVICEID=:DEVICEID and RESNAME=:RESNAME");
11             OracleParameter[] parameters = {
12                     new OracleParameter("PARAVALUE", OracleDbType.NVarchar2,50),
13                     new OracleParameter("RECEIVETIME", OracleDbType.Date),
14                     new OracleParameter("DEVICEID", OracleDbType.Int32,4),
15                     //new OracleParameter("RUMTIMEID", OracleDbType.Long,4),
16                     new OracleParameter("RESNAME", OracleDbType.NVarchar2,50)};
17             parameters[0].Value = model.PARAVALUE;
18             parameters[1].Value = model.RECEIVETIME;
19             parameters[2].Value = model.DEVICEID;
20             //parameters[3].Value = model.RUMTIMEID;
21             parameters[3].Value = model.RESNAME;
22 
23             int rows=DbHelperOra.ExecuteSql(strSql.ToString(),parameters);
24             if (rows > 0)
25             {
26                 return true;
27             }
28             else
29             {
30                 return false;
31             }
32         }

 

posted @ 2017-05-24 15:23  weipt  阅读(370)  评论(0编辑  收藏  举报

你的想法很重要-大家都来谈 (C)Copyright 2012 by WeiPt