OracleHelper

最新推荐文章于 2016-05-27 18:24:00 发布
最新推荐文章于 2016-05-27 18:24:00 发布
阅读量356 收藏
点赞数
分类专栏： 数据库 文章标签： string dataset null parameters exception textbox
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jhlrxw/article/details/7250860
版权
 

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.OracleClient;
using System.Data.OleDb;
using System.IO;
using System.Text;

/// <summary>
/// DBHelper的摘要描述 -- 所有跟数据库访问相关的函数
/// </summary>
///
namespace SysHelper
{
    public class DBHelper
    {
        public static string ErrorMessage = "";
        public DBHelper()
        {
            //构造函数，暂时不用写
        }

        public static readonly string DBString = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();

        //创建数据库连接
        public static OracleConnection NewConnection()
        {
            return new OracleConnection(DBString);
        }


        //开始数据库交易
        public static OracleTransaction BeginTransaction(OracleConnection Conn)
        {
            Conn.Open();
            OracleTransaction Tran = Conn.BeginTransaction();
            return Tran;
        }


        //下面是ExecNoQry的N多重载函数

        //使用ExecuteNonQuery
        public static int ExecNoQry(string ConnStr, string SqlStr)
        {
            return ExecNoQry(ConnStr, CommandType.Text, SqlStr);
        }
               
        public static int ExecNoQry(string ConnStr, CommandType CmdType, string SqlStr)
        {
            return ExecNoQry(ConnStr, CmdType, SqlStr, (OracleParameter[])null);
        }

        public static int ExecNoQry(string SqlStr)
        {
            return ExecNoQry(null, CommandType.Text, SqlStr);
        }

        public static int ExecNoQry(CommandType CmdType, string SqlStr)
        {
            return ExecNoQry((string)null, CmdType, SqlStr, (OracleParameter[])null);
        }

        public static int ExecNoQry(CommandType CmdType, string SqlStr, OracleParameter[] Params)
        {
            return ExecNoQry((string)null, CmdType, SqlStr, Params);
        }

        public static int ExecNoQry(string ConnStr, CommandType CmdType, string SqlStr, OracleParameter[] Params)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }
            int result = -1;
            OracleConnection Conn = new OracleConnection(ConnStr);
            try
            {
                Conn.Open();
                result = ExecNoQry(Conn, CmdType, SqlStr, Params);
            }
            catch (Exception e)
            {
               // throw e;
                // new Exception(e.Message + "\n" + SqlStr);
                ErrorMessage = e.Message.ToString();
            }
            finally
            {
                Conn.Close();
            }
            return result;
        }

        public static int ExecNoQry(OracleConnection Conn, CommandType CmdType, string SqlStr, OracleParameter[] Params)
        {
            OracleCommand Cmd = new OracleCommand(SqlStr, (OracleConnection)Conn);
            Cmd.CommandType = CmdType;
            if (Params != null)
            {
                for (int i = 0; i < Params.Length; i++)
                {
                    Cmd.Parameters.Add((OracleParameter)Params[i]);
                }
            }
            int Rc = Cmd.ExecuteNonQuery();
            return Rc;
        }

        public static int ExecNoQry(OracleTransaction Tran, OracleConnection Conn, CommandType CmdType, string SqlStr, OracleParameter[] Params)
        {
            OracleCommand Cmd = new OracleCommand(SqlStr, (OracleConnection)Conn, (OracleTransaction)Tran);
            Cmd.CommandType = CmdType;
            if (Params != null)
            {
                for (int i = 0; i < Params.Length; i++)
                {
                    Cmd.Parameters.Add((OracleParameter)Params[i]);
                }
            }
            int Rc = Cmd.ExecuteNonQuery();
            return Rc;
        }

        public static void ExecNoQry(string[] Sqls)
        {
            ExecNoQry(null, Sqls);
        }

        public static void ExecNoQry(string ConnStr, string[] Sqls)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }
            using (OracleConnection Conn = new OracleConnection(ConnStr))
            {
                Conn.Open();
                OracleTransaction Tran = Conn.BeginTransaction();
                try
                {
                    OracleCommand Cmd = null;
                    for (int i = 0; i < Sqls.Length; i++)
                    {
                        Cmd = new OracleCommand(Sqls[i], Conn, Tran);
                        Cmd.ExecuteNonQuery();
                    }
                    Tran.Commit();
                }
                catch (Exception e)
                {
                    Tran.Rollback();
                    throw e;
                }
                finally
                {
                    Conn.Close();
                }
            }
        }


        //下面是ExecScalar的N多重载函数

        //使用ExecuteScalar
        public static object ExecScalar(string SqlStr)
        {
            return ExecScalar(null, SqlStr);
        }

        public static object ExecScalar(string ConnStr, string SqlStr)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }

            OracleConnection Conn = new OracleConnection(ConnStr);

            try
            {
                OracleCommand Cmd = new OracleCommand(SqlStr, Conn);

                object Obj = null;
                Conn.Open();
                Obj = Cmd.ExecuteScalar();
                Conn.Close();
                return Obj;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + "\n" + SqlStr);
            }
            finally
            {
                Conn.Close();
            }
        }

 

        //下面是ExecReader的N多重载函数
        public static OracleDataReader ExecReader(string ConnStr, string SqlStr)
        {
            return ExecReader(ConnStr, SqlStr, null);
        }

        public static OracleDataReader ExecReader(string SqlStr)
        {
            return ExecReader(null, SqlStr, null);
        }

        public static OracleDataReader ExecReader(string ConnStr, string SqlStr, OracleParameterCollection Params)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }

            OracleConnection Conn = new OracleConnection(ConnStr);

            try
            {
                if (Params != null)
                {
                    OracleParameter Param = null;

                    for (int i = 0; i < Params.Count; i++)
                    {
                        Param = Params[i];
                        SqlStr = SqlStr.Replace(Param.ParameterName, ComPublic.uPublic.QuoteStr(Param.Value.ToString()));
                    }
                }

                OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
                Conn.Open();
                OracleDataReader dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + "\n" + SqlStr);
            }
            finally
            {
                //con.Close();
            }
        }

 

        //下面是ExecDataSet的N多重载函数
        public static DataSet ExecDataSet(string ConnStr, string SqlStr)
        {
            return ExecDataSet(ConnStr, SqlStr, null);
        }

        public static DataSet ExecDataSet(string SqlStr)
        {
            return ExecDataSet(null, SqlStr, null);
        }

        public static DataSet ExecDataSet(string ConnStr, string SqlStr, OracleParameterCollection Parameters)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }

            using (OracleConnection Conn = new OracleConnection(ConnStr))
            {
                if (Parameters != null)
                {
                    OracleParameter Param = null;

                    for (int i = 0; i < Parameters.Count; i++)
                    {
                        Param = Parameters[i];
                        SqlStr = SqlStr.Replace(Param.ParameterName, ComPublic.uPublic.QuoteStr(Param.Value.ToString()));
                    }
                }

                OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
                Conn.Open();
                OracleDataAdapter Oda = new OracleDataAdapter(Cmd);
                DataSet Ds = new DataSet();
                Oda.Fill(Ds);
                Conn.Close();
                return Ds;
            }
        }


        //下面是ExecDataTable的N多重载函数
        public static DataTable ExecDataTable(string ConnStr, string SqlStr)
        {
            return ExecDataTable(ConnStr, SqlStr, null);
        }

        public static DataTable ExecDataTable(string SqlStr)
        {
            return ExecDataTable(null, SqlStr, null);
        }

        public static DataTable ExecDataTable(string ConnStr, string SqlStr, OracleParameterCollection Parameters)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }

            using (OracleConnection Conn = new OracleConnection(ConnStr))
            {
                if (Parameters != null)
                {
                    OracleParameter Param = null;

                    for (int i = 0; i < Parameters.Count; i++)
                    {
                        Param = Parameters[i];
                        SqlStr = SqlStr.Replace(Param.ParameterName, ComPublic.uPublic.QuoteStr(Param.Value.ToString()));
                    }
                }

                OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
                Conn.Open();
                OracleDataAdapter Oda = new OracleDataAdapter(Cmd);
                DataTable Dt = new DataTable();
                Oda.Fill(Dt);
                Conn.Close();
                return Dt;
            }
        }

        public static DataTable ExecDataTable(string ConnStr, CommandType CmdType, string SqlStr, OracleParameter[] Parameters)
        {
            if (ConnStr == null)
            {
                ConnStr = DBString;
            }
            using (OracleConnection Conn = new OracleConnection(ConnStr))
            {
                OracleCommand Cmd = new OracleCommand(SqlStr, Conn);
                Cmd.CommandType = CmdType;
                if (Parameters != null)
                {
                    for (int i = 0; i < Parameters.Length; i++)
                    {
                        Cmd.Parameters.Add((OracleParameter)Parameters[i]);
                    }
                }
                Conn.Open();
                OracleDataAdapter Oda = new OracleDataAdapter(Cmd);
                DataTable Dt = new DataTable();
                Oda.Fill(Dt);
                Conn.Close();
                return Dt;
            }
        }


        //把数据库取出来的数据，给到输入框内，以便在上面修改记录
        public static void DBInfoToControls(string sSql, string[] sqlFieldName, System.Web.UI.Control[] initcontrol)
        {
            OracleDataReader datareader = SysHelper.DBHelper.ExecReader(sSql);

            if (datareader.Read())
            {
                for (int i = 0; i < sqlFieldName.Length; i++)
                {
                    if (initcontrol[i] is TextBox)
                    {
                        (initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString();
                     }
                    else if (initcontrol[i] is DropDownList)
                    {
                        try
                        {
                            (initcontrol[i] as DropDownList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }
                        catch(Exception e)
                        {
                            (initcontrol[i] as DropDownList).SelectedValue = "";
                        }
                    }
                    else
                    {
                        ;
                    }

                }
            }
        }

        public static void DBInfoToControl(string sSql, string[] sqlFieldName, System.Web.UI.Control[] initcontrol)
        {
            OracleDataReader datareader = SysHelper.DBHelper.ExecReader(sSql);

            if (datareader.Read())
            {
                for (int i = 0; i < sqlFieldName.Length; i++)
                {
                    if (sqlFieldName[i].Trim().Length > 3)
                    {
                        if (initcontrol[i] is TextBox)
                        {

                            if (sqlFieldName[i].Trim().ToUpper().Substring(sqlFieldName[i].Trim().Length - 3, 3).Equals("DATE") || sqlFieldName[i].Trim().ToUpper().Substring(sqlFieldName[i].Trim().Length - 4, 4).Equals("DATE"))
                            {

                                if (datareader[sqlFieldName[i].ToString()].ToString().Trim() == "")
                                    (initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                                else
                                {
                                    DateTime date = System.DateTime.Parse(datareader[sqlFieldName[i].ToString()].ToString().Trim());
                                    (initcontrol[i] as TextBox).Text = string.Format("{0:yyyy-MM-dd}", date);
                                }
                            }
                            else
                                (initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }
                        else if (initcontrol[i] is DropDownList)
                        {
                            (initcontrol[i] as DropDownList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }
                        else if (initcontrol[i] is CheckBox)
                        {
                            if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("Y"))
                            {
                                (initcontrol[i] as CheckBox).Checked = true;
                            }
                            else if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("N"))
                            {
                                (initcontrol[i] as CheckBox).Checked = false;
                            }

                        }
                        else if (initcontrol[i] is Label)
                        {
                            (initcontrol[i] as Label).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }
                        else if (initcontrol[i] is RadioButtonList)
                        {
                            (initcontrol[i] as RadioButtonList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }

                    }
                    else
                    {
                        if (initcontrol[i] is TextBox)
                        {
                            (initcontrol[i] as TextBox).Text = datareader[sqlFieldName[i].ToString()].ToString();
                        }
                        else if (initcontrol[i] is DropDownList)
                        {
                            (initcontrol[i] as DropDownList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString();
                        }
                        else if (initcontrol[i] is CheckBox)
                        {
                            if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("Y"))
                            {
                                (initcontrol[i] as CheckBox).Checked = true;
                            }
                            else if (datareader[sqlFieldName[i].ToString()].ToString().Trim().Equals("N"))
                            {
                                (initcontrol[i] as CheckBox).Checked = false;
                            }

                        }
                        else if (initcontrol[i] is Label)
                        {
                            (initcontrol[i] as Label).Text = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }
                        else if (initcontrol[i] is RadioButtonList)
                        {
                            (initcontrol[i] as RadioButtonList).SelectedValue = datareader[sqlFieldName[i].ToString()].ToString().Trim();
                        }
                    }
                }
            }

        }

        public static DataSet Class_ExcelSql(string excelPath, string sheetName)
        {
            OleDbConnection myConn = new OleDbConnection();
            OleDbCommand myComm = new OleDbCommand();
            OleDbDataAdapter myAdap = new OleDbDataAdapter();
            DataSet myDs = new DataSet();
            if (!File.Exists(excelPath))
            {
                //Response.Write("<script>alter('excel文件不能为空')</scirpt>");
                return null;
            }
            string Conn_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=Yes;'";

            try
            {
                myConn.ConnectionString = Conn_Str;
                myConn.Open();
                myComm.Connection = myConn;
                myComm.CommandText = "select * from [" + sheetName + "$]";
                // Mydr = myComm.ExecuteReader();
                myAdap.SelectCommand = myComm;
                myAdap.Fill(myDs);
            }
            catch (OleDbException Err_My)
            {
                // Response.Write("<script>alter('提示')</scirpt>");

                //MessageBox.Show(Err_My.Message, "提示");
            }
            if (myConn.State == ConnectionState.Open)
            {
                myConn.Close();
                myConn.Dispose();
            }
            myComm.Dispose();
            myAdap.Dispose();
            return myDs;
        }

        public static string importExcelToDB(string excelPath, string sheetName, string table_Name, string change_no)
        {
            string fieldvalue = "";
            DataTable dt = null;
            DataTableReader dr = Class_ExcelSql(excelPath, sheetName).Tables[0].CreateDataReader();
            using (OracleConnection conn = new OracleConnection(DBString))
            {
                conn.Open();
                OracleTransaction transaction = conn.BeginTransaction();
                string sql = "INSERT INTO " + table_Name + "(RPT_ITEM,";
                string insert_sql = "";

                for (int j = 0; j < dr.FieldCount; j++)
                {
                    if (j != dr.FieldCount - 1)
                        sql += dr.GetName(j) + ",";
                    else
                        sql += dr.GetName(j) + ") VALUES(";
                }
                sql += "'" + change_no + "'";

                int i = 0;
                while (dr.Read() && (!dr.GetValue(0).ToString().Equals("")))
                {
                   insert_sql = sql  + ",";
                    for (int j = 0; j < dr.FieldCount; j++)
                    {
                        fieldvalue = dr.GetValue(j).ToString().Trim();
                        if (fieldvalue.Contains("'"))
                            fieldvalue = fieldvalue.Replace("'", "''");
                        if (dr.GetName(j).Length > 4)
                        {
                            if (!dr.GetName(j).Substring(dr.GetName(j).Length - 4, 4).Equals("DATE"))
                            {
                                if (j != dr.FieldCount - 1)
                                    insert_sql += "'" + fieldvalue + "',";
                                else
                                    insert_sql += "'" + fieldvalue + "')";
                            }
                            else
                            {
                                if (j != dr.FieldCount - 1)
                                    if (dr.GetValue(j).ToString().Trim().Contains(" "))
                                        insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim().Substring(0, dr.GetValue(j).ToString().Trim().IndexOf(" ")) + "','YYYY-MM-DD'),";
                                    else
                                        insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim() + "','YYYY-MM-DD'),";
                                else
                                    if (dr.GetValue(j).ToString().Trim().Contains(" "))
                                        insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim().Substring(0, dr.GetValue(j).ToString().Trim().IndexOf(" ")) + "','YYYY-MM-DD'))";
                                    else
                                        insert_sql += "TO_DATE('" + dr.GetValue(j).ToString().Trim() + "','YYYY-MM-DD'))";
                            }
                        }
                        else
                        {
                            if (j != dr.FieldCount - 1)
                                insert_sql += "'" + fieldvalue + "',";
                            else
                                insert_sql += "'" + fieldvalue +  "')";
                        }
                    }
                    try
                    {
                        ExecNoQry(insert_sql);
                        i++;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return ex.Message;

                    }
                }
                transaction.Commit();
                return "Import Success";
            }
        }

 


      

      
    }
}

首席执行官
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oraclehelper
09-15
oraclehelper
OracleHelper(对增删改查分页查询操作进行了面向对象的封装，对批量增删改操作的事务封装)...
weixin_33719619的博客
06-29 140
http://www.daxueit.com/article/5217.html 转载于:https://www.cnblogs.com/ShoneH/p/5627381.html
OracleHelper For .Net
weixin_30790841的博客
01-06 79
前几篇文章里，提供了 SqlHelper, OleDbHelper, AccessHelper 给大家共享，这篇提供一个 OracleHelper 给大家共享，来自 DAAB2.1，使用的是 微软 的.NET 自带的 OracleClient。 使用方式，和 SqlHelper 一样： using System; using System.Data; using Sys...
OracleHelper oracle数据库访问 多种方式 可靠 强大
05-06
OracleHelper 是一个专门用于访问Oracle数据库的工具或库，它为开发者提供了多种高效且可靠的数据库操作方式。在本文中，我们将深入探讨OracleHelper的核心特性、使用场景以及它如何实现对Oracle数据库的强大支持。 ...
C# OracleHelper类, DbHelperOracle.cs
08-17
C#封装的操作Oracle数据库的帮助类，DbHelperOracle.cs
C# OracleHelper
05-07
微软些的C# OracleHelper，有兴趣的参考下
SQLHelperOra.cs
淘气的黑妞_爱说话
04-04 531
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.OracleClient; namespace Maticsoft.DBUtility { /// /// Copyright (C) Matic
OracleHelper.cs源码
Shuttle的技术博客
07-03 3444
 话不多说，直接贴过来，自己收藏，大家要得下！using System;using System.Configuration;using System.Data;using System.Data.OracleClient;using System.Collections;namespace PetShop.DBUtility ...{    /**////     /// A hel
Oracle操作数据库oracleHelper
weixin_30239339的博客
05-27 126
1 OracleOracleHelper //=============================================================================== // OracleHelper based on Microsoft Data Access Application Block (DAAB) for .NET // http:/...
OracleHelper - .net
初夏木香 静谧
01-01 1376
 最近用到了对Oracle的操作，不记得在哪里有见过Oracle helper，自己弄了下来又稍微改了改，现在放上来以备不时之需。using System;using System.Collections.Generic;using System.Text;using System.Data.OracleClient;using System.Data;using Sy
DBHelper连接oracle数据库类
Continuetofantasy的专栏
11-11 2402
Name: Author:  Description:  --%> using System; using System.Collections.Generic; using System.Text; using System.Data.OracleClient; using System.Data; using System.Configuration;
Python Excel处理与OracleHelper工具：实战教程
本资源是一份关于Python编程中的Excel处理和Oracle数据库交互的教程文档，主要关注两个核心功能：读取Excel文件并转换为JSON格式，以及使用OracleHelper工具类与Oracle数据库进行交互。 首先，文档引入了`read_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值