操作Oracle 一条龙

1 引用Oracle.DataAccess.dll

 

2 App.Config中配置连接字符串：

Data Source=(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
);User Id=scott;Password=XXXXX

 

3 建立操作数据库的类OracleHelper.cs 

 

public class OracleHelper
    {
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["dbconnStr"].ConnectionString;

        //创建连接
        public static OracleConnection CreateConnection()
        {
            OracleConnection conn = new OracleConnection(connStr);
            conn.Open();
            return conn;
        }

        //使用已有连接 非查询
        public static int ExecuteNonQuery(OracleConnection conn, string sql, params OracleParameter[] parameters)
        {
            using (OracleCommand cmd = new OracleCommand(sql, conn))
            {
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }


        //使用已有连接 非查询 带事务
        public static int ExecuteNonQuery(OracleConnection conn, OracleTransaction tx, string sql, params OracleParameter[] parameters)
        {
            using (OracleCommand cmd = new OracleCommand(sql, conn))
            {
                cmd.Transaction = tx;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }

        //自己创建连接 非查询
        public static int ExecuteNonQuery(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = CreateConnection())
            {
                return ExecuteNonQuery(conn, sql, parameters);
            }
        }

        //使用已有连接 非查询 带存储过程（返回影响行数或输出参数）
        public static object ExecuteNonQueryProcedure(OracleConnection conn, string proName, params OracleParameter[] parameters)
        {
            using (OracleCommand cmd = new OracleCommand(proName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }

        //自己创建连接 非查询 带存储过程（返回影响行数或输出参数）
        public static object ExecuteNonQueryProcedure(string proName, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = CreateConnection())
            {
                return ExecuteNonQueryProcedure(conn, proName, parameters);
            }
        }

        //使用已有连接 单查询
        public static object ExecuteScalar(OracleConnection conn, string sql, params OracleParameter[] parameters)
        {
            using (OracleCommand cmd = new OracleCommand(sql, conn))
            {
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }

        //自己创建连接 单查询
        public static object ExecuteScalar(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = CreateConnection())
            {
                return ExecuteScalar(conn, sql, parameters);
            }
        }

        //使用已有连接 多查询
        public static DataTable ExecuteReader(OracleConnection conn, string sql, params OracleParameter[] parameters)
        {
            DataTable table = new DataTable();
            using (OracleCommand cmd = new OracleCommand(sql, conn))
            {
                cmd.Parameters.AddRange(parameters);
                OracleDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
            }
            return table;
        }

        //自己创建连接 多查询
        public static DataTable ExecuteReader(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = CreateConnection())
            {
                return ExecuteReader(conn, sql, parameters);
            }
        }
    }

 

4 带有自增触发器的插入：

TM_USER(ID,NAME,Constraint PRI_TM_USER PRIMARY KEY(ID) )

 

INSERT INTO TM_USER(NAME) VALUES ('NO id');

INSERT INTO TM_USER(ID,NAME) VALUES (1, 'id no use');

 

ID NAME
---------- --------------------
1 NO id
2 id no use