Let's go

oracle入门与基础用法

目录：

 

1：进行安装:

首先打开tnsnames.ora的存放目录，一般为D:\app\Administrator\product\11.2.0\client_1\network\admin,就看安装具体位置了。

 

2.打开tnsnames.ora，进行编辑

配置一下文件

 CONN_1=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.144.191)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

Oracle帮助类：

  1     public abstract class OracleHelper
  2     {
  3         //数据库连接字符串(web.config来配置)
  4         public static string connectionString = ConfigurationManager.ConnectionStrings["oraclenc"].ConnectionString;
  5 
  6         public OracleHelper()
  7         {
  8         }
  9 
 10 
 11         /// <summary>
 12         /// 执行SQL语句，返回影响的记录数
 13         /// </summary>
 14         /// <param name="SQLString">SQL语句</param>
 15         /// <returns>影响的记录数</returns>
 16         public static int ExecuteSql(string SQLString)
 17         {
 18             try
 19             {
 20                 using (OracleConnection connection = new OracleConnection(connectionString))
 21                 {
 22                     using (OracleCommand cmd = new OracleCommand(SQLString, connection))
 23                     {
 24                         connection.Open();
 25                         int rows = cmd.ExecuteNonQuery();
 26                         return rows;
 27                     }
 28                 }
 29             }
 30             catch (Exception ex)
 31             {
 32                 throw new Exception("数据库操作异常", ex);
 33             }
 34         }
 35 
 36         /// <summary>
 37         /// 执行多条SQL语句，无事务
 38         /// </summary>
 39         /// <param name="SQLStringList">多条SQL语句</param>  
 40         public static void ExecuteSql(List<String> SQLStringList)
 41         {
 42             try
 43             {
 44                 using (OracleConnection connection = new OracleConnection(connectionString))
 45                 {
 46                     using (OracleCommand cmd = new OracleCommand())
 47                     {
 48                         cmd.Connection = connection;
 49                         connection.Open();
 50 
 51                         for (int n = 0; n < SQLStringList.Count; n++)
 52                         {
 53                             string strsql = SQLStringList[n].ToString();
 54 
 55                             if (strsql.Trim().Length > 1)
 56                             {
 57                                 cmd.CommandText = strsql;
 58                                 cmd.ExecuteNonQuery();
 59                             }
 60                         }
 61                     }
 62                 }
 63             }
 64             catch (Exception ex)
 65             {
 66                 throw new Exception("数据库操作异常", ex);
 67             }
 68         }
 69 
 70         /// <summary>
 71         /// 执行多条SQL语句，实现数据库事务。
 72         /// </summary>
 73         /// <param name="SQLStringList">多条SQL语句</param>        
 74         public static void ExecuteSqlTran(List<String> SQLStringList)
 75         {
 76             try
 77             {
 78                 using (OracleConnection conn = new OracleConnection(connectionString))
 79                 {
 80                     using (OracleCommand cmd = new OracleCommand())
 81                     {
 82                         conn.Open();
 83                         cmd.Connection = conn;
 84                         OracleTransaction tx = conn.BeginTransaction();
 85                         cmd.Transaction = tx;
 86 
 87                         try
 88                         {
 89                             for (int n = 0; n < SQLStringList.Count; n++)
 90                             {
 91                                 string strsql = SQLStringList[n].ToString();
 92                                 if (strsql.Trim().Length > 1)
 93                                 {
 94                                     cmd.CommandText = strsql;
 95                                     cmd.ExecuteNonQuery();
 96                                 }
 97                             }
 98 
 99                             tx.Commit();
100                         }
101                         catch (Exception ex)
102                         {
103                             tx.Rollback();
104                             throw ex;
105                         }
106                     }
107                 }
108             }
109             catch (Exception ex)
110             {
111                 throw new Exception("数据库操作异常", ex);
112             }
113         }
114 
115         /// <summary>
116         /// 执行带一个存储过程参数的的SQL语句。
117         /// </summary>
118         /// <param name="SQLString">SQL语句</param>
119         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
120         /// <returns>影响的记录数</returns>
121         public static int ExecuteSql(string SQLString, string content)
122         {
123             try
124             {
125                 using (OracleConnection connection = new OracleConnection(connectionString))
126                 {
127                     using (OracleCommand cmd = new OracleCommand(SQLString, connection))
128                     {
129                         OracleParameter myParameter = new OracleParameter("@content", OracleType.NVarChar);
130                         myParameter.Value = content;
131                         cmd.Parameters.Add(myParameter);
132                         connection.Open();
133                         int rows = cmd.ExecuteNonQuery();
134                         return rows;
135                     }
136                 }
137             }
138             catch (Exception ex)
139             {
140                 throw new Exception("数据库操作异常", ex);
141             }
142         }
143 
144         /// <summary>
145         /// 执行查询语句，返回DataSet
146         /// </summary>
147         /// <param name="SQLString">查询语句</param>
148         /// <returns>DataSet</returns>
149         public static DataSet Query(string SQLString)
150         {
151             try
152             {
153                 using (OracleConnection connection = new OracleConnection(connectionString))
154                 {
155                     using (OracleDataAdapter cmd = new OracleDataAdapter(SQLString, connection))
156                     {
157                         DataSet ds = new DataSet();
158                         connection.Open();
159                         cmd.Fill(ds, "ds");
160                         return ds;
161                     }
162                 }
163             }
164             catch (Exception ex)
165             {
166                 throw new Exception("数据库操作异常", ex);
167             }
168         }
169 
170 
171 
172 
173     }
View Code

 执行语法实例：

StringBuilder sql = new StringBuilder();
sql.Append(" INSERT  ALL  ");
foreach (DataRow dr in dt.Rows)
{
    sql.Append(" INTO  table1 (id,name) ");
    sql.Append(" VALUES(");
    sql.Append("'" + dr["id"].ToString() + "',")
            .Append("'" + dr["name"].ToString() + "'")
            .Append(" )");
}
sql.Append(" SELECT 1 FROM DUAL   ");
//执行语句
int rcount = OracleHelper.ExecuteSql(sql.ToString());

 帮助类top2：【借鉴】

using Oracle.DataAccess.Client;
using System;
using System.Data;
using System.Collections.Generic;
using System.Configuration;


namespace Common
{
    public class DBHelper
    {
        static string connString = ConfigurationManager.ConnectionStrings["Conn_DB"].ToString();
       
        /// <summary>
        /// 查询表数据（单表）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataSet SelectData(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                using (OracleConnection conn = new OracleConnection(connString))
                {
                    //conn.Open();
                    OracleCommand cmd = new OracleCommand(sql, conn);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch (Exception e)
            {
                Console.Write(e);
            }
            return null;
        }

        //增改删
        /// <summary>
        /// 执行sql 语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回影响的行数</returns>
        public static Boolean AddUpDelData(string sql)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(connString))
                {
                    conn.Open();
                    OracleCommand cmd = new OracleCommand(sql, conn);
                    int row = cmd.ExecuteNonQuery();
                    conn.Close();
                    if (row > 0)
                    {
                        return true;
                    }
                }
            }
            catch (Exception e)
            {
                Console.Write(e);
            }
            return false;
        }

        public static Boolean ExeTransaction(List<string> sqlText)

        {

            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                OracleTransaction tran = conn.BeginTransaction();
                try
                {
                                     
                    OracleCommand cmd = new OracleCommand();

                    cmd.Transaction = tran;

                    cmd.Connection = conn;

                    foreach(string item in sqlText)
                    {
                        cmd.CommandText = item;

                         cmd.ExecuteNonQuery();
                    }
                    tran.Commit();

                    return true;

                }

                catch (Exception et)

                {
                    tran.Rollback();
                    return false;
                }

                finally
                {
                    conn.Close();
                }

            }

        }

    }
}
View Code

 

posted @ 2019-08-23 15:45  chenze  阅读(433)  评论(0)  编辑  收藏  举报
有事您Q我