数据访问常用方法

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Windows.Forms;
using System.Data.OleDb;
namespace Charge
{
    public class wglpforever
    {
        #region
        private string OraConnectString = "data source=wglp;uid=wanton_user;pwd=wanton_user";
        private OracleConnection myOraConnection;
        public wglpforever()
        {
            myOraConnection = new OracleConnection(OraConnectString);
        }
        private void showMsg(string p, string p_2)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        #endregion

        #region -------------------多条Sql语句的处理-----------------------
        /// <summary>
        /// 事务
        /// </summary>
        /// <param name="asSql">Sql语句数组</param>
        /// <returns>整型</returns>
        public int OraTransactionByMultSql(string[] asSql)
        {
            myOraConnection.Open();
            OracleTransaction myOraTransaction = myOraConnection.BeginTransaction();
            try
            {
                OracleCommand cmd = myOraConnection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = myOraTransaction;
                foreach (string isSql in asSql)
                {
                    if (isSql != "" && isSql != null)
                    {
                        cmd.CommandText = isSql;
                        cmd.ExecuteNonQuery();
                    }
                }
                myOraTransaction.Commit();
                return 1;
            }
            catch (System.Data.OracleClient.OracleException oex)
            {
                myOraTransaction.Rollback();
                showMsg("Error-TRANSACTION ROLLED BACK " + "/n" + oex.Message, "Rollback Transaction");
                return -1;
            }
            catch (Exception ex)
            {
                showMsg("System Error " + "/n" + ex.Message, "Error");
                return -1;
            }
            finally
            {
                myOraConnection.Close();
            }
        }
        #endregion

        #region -----------------数据的加密与解密------------------------
        /// <summary>
        /// 对数据进行加密
        /// </summary>
        /// <param name="str">要加密的字符串</param>
        /// <returns>加密后的字符串</returns>
        public string EncryptString(string str)    //加密
        {
            char[] Base64Code = new char[] { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/', '=' };
            byte empty = (byte)0;
            System.Collections.ArrayList byteMessage = new System.Collections.ArrayList(System.Text.Encoding.Default.GetBytes(str));
            System.Text.StringBuilder outmessage;
            int messageLen = byteMessage.Count;
            int page = messageLen / 3;
            int use = 0;
            if ((use = messageLen % 3) > 0)
            {
                for (int i = 0; i < 3 - use; i++)
                    byteMessage.Add(empty);
                page++;
            }
            outmessage = new System.Text.StringBuilder(page * 4);
            for (int i = 0; i < page; i++)
            {
                byte[] instr = new byte[3];
                instr[0] = (byte)byteMessage[i * 3];
                instr[1] = (byte)byteMessage[i * 3 + 1];
                instr[2] = (byte)byteMessage[i * 3 + 2];
                int[] outstr = new int[4];
                outstr[0] = instr[0] >> 2;

                outstr[1] = ((instr[0] & 0x03) << 4) ^ (instr[1] >> 4);
                if (!instr[1].Equals(empty))
                    outstr[2] = ((instr[1] & 0x0f) << 2) ^ (instr[2] >> 6);
                else
                    outstr[2] = 64;
                if (!instr[2].Equals(empty))
                    outstr[3] = (instr[2] & 0x3f);
                else
                    outstr[3] = 64;
                outmessage.Append(Base64Code[outstr[0]]);
                outmessage.Append(Base64Code[outstr[1]]);
                outmessage.Append(Base64Code[outstr[2]]);
                outmessage.Append(Base64Code[outstr[3]]);
            }
            return outmessage.ToString();
        }
        /// <summary>
        /// 对数据进行解密
        /// </summary>
        /// <param name="str">要解密的字符串</param>
        /// <returns>解密后的字符串</returns>
        public string DecryptString(string str)   //解密
        {
            if ((str.Length % 4) != 0)
            {
                throw new ArgumentException("不是正确的BASE64编码，请检查。", "str");
            }
            if (!System.Text.RegularExpressions.Regex.IsMatch(str, "^[A-Z0-9/+=]*$", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            {
                throw new ArgumentException("包含不正确的BASE64编码，请检查。", "str");
            }
            string Base64Code = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+/=";
            int page = str.Length / 4;
            System.Collections.ArrayList outMessage = new System.Collections.ArrayList(page * 3);
            char[] message = str.ToCharArray();
            for (int i = 0; i < page; i++)
            {
                byte[] instr = new byte[4];
                instr[0] = (byte)Base64Code.IndexOf(message[i * 4]);
                instr[1] = (byte)Base64Code.IndexOf(message[i * 4 + 1]);
                instr[2] = (byte)Base64Code.IndexOf(message[i * 4 + 2]);
                instr[3] = (byte)Base64Code.IndexOf(message[i * 4 + 3]);
                byte[] outstr = new byte[3];
                outstr[0] = (byte)((instr[0] << 2) ^ ((instr[1] & 0x30) >> 4));
                if (instr[2] != 64)
                {
                    outstr[1] = (byte)((instr[1] << 4) ^ ((instr[2] & 0x3c) >> 2));
                }
                else
                {
                    outstr[2] = 0;
                }
                if (instr[3] != 64)
                {
                    outstr[2] = (byte)((instr[2] << 6) ^ instr[3]);
                }
                else
                {
                    outstr[2] = 0;
                }
                outMessage.Add(outstr[0]);
                if (outstr[1] != 0)
                    outMessage.Add(outstr[1]);
                if (outstr[2] != 0)
                    outMessage.Add(outstr[2]);
            }
            byte[] outbyte = (byte[])outMessage.ToArray(Type.GetType("System.Byte"));
            return System.Text.Encoding.Default.GetString(outbyte);
        }
        #endregion

        #region --------------返回永不重复的序列串-----------------
        /// <summary>
        /// 获取永不重复的序列字符串
        /// </summary>
        /// <returns>序列串</returns>
        public string GetFileName()
        {
            int iRandNum;
            long lTimeNow;
            string strTimeNow = System.DateTime.Now.ToString();
            strTimeNow = strTimeNow.Replace(":", "");
            strTimeNow = strTimeNow.Replace("-", "");
            strTimeNow = strTimeNow.Replace(" ", "");
            lTimeNow = long.Parse(strTimeNow);
            System.Random rand = new Random();
            iRandNum = rand.Next(1, 99999);
            rand = null;
            lTimeNow += iRandNum;
            return lTimeNow.ToString();
        }
        #endregion

        #region--------------CreateCommand----------------------
        /// <summary>
        /// 创建Command命令
        /// </summary>
        /// <param name="sql">Sql查询语句</param>
        /// <returns>Command命令</returns>
        public OracleCommand CreateCom(string sql)
        {
            myOraConnection.Open ();
            OracleCommand cmd = new OracleCommand(sql, myOraConnection);
            return cmd;
        }

        /// <summary>
        /// 创建Command命令
        /// </summary>
        /// <param name="Proc">过程名称</param>
        /// <param name="Params">OracleParameter参数组</param>
        /// <returns>Command命令</returns>
        private OracleCommand CreateCom(string Proc, OracleParameter[] Params)
        {
            myOraConnection.Open();
            OracleCommand cmd = new OracleCommand(Proc,myOraConnection );
            cmd.CommandType = CommandType.StoredProcedure;
            if (Params != null)
            {
                foreach (OracleParameter param in Params)
                {
                    cmd.Parameters.Add(param);
                }
            }
            return cmd;
        }
        #endregion

        #region----------------MakeParameter--------------------
        /// <summary>
        /// 为过程提供参数
        /// </summary>
        /// <param name="param">参数名</param>
        /// <param name="type">参数类型</param>
        /// <param name="size">参数长度</param>
        /// <param name="dirction">传值类型</param>
        /// <param name="value">参数值</param>
        /// <returns>参数</returns>
        private OracleParameter MakeParameter(string param, OracleType type, int size, ParameterDirection dirction, object value)
        {
            OracleParameter parameter;
            if (size > 0)
                parameter = new OracleParameter(param, type, size);
            else
                parameter = new OracleParameter(param, type);
            if (!(dirction == ParameterDirection.Output && value == null))
                parameter.Value = value;
            else
                parameter.Direction = ParameterDirection.Output;
            return parameter;
        }

        /// <summary>
        /// 输入参数
        /// </summary>
        /// <param name="param">参数名</param>
        /// <param name="type">参数类型</param>
        /// <param name="size">参数长度</param>
        /// <param name="value">参数值</param>
        /// <returns>输入参数</returns>
        public OracleParameter MakeInParameter(string param, OracleType type, int size, object value)
        {
            return MakeParameter(param, type, size, ParameterDirection.Input, value);
        }
        /// <summary>
        /// 输出参数
        /// </summary>
        /// <param name="param">参数名</param>
        /// <param name="type">参数类型</param>
        /// <param name="size">参数长度</param>
        /// <param name="value">参数值</param>
        /// <returns>输出参数</returns>
        public OracleParameter MakeOutParameter(string param, OracleType type, int size)
        {
            return MakeParameter(param, type, size, ParameterDirection.Output, null);
        }
        #endregion

        #region-----------------RunSql--------------------------
        /// <summary>
        /// 执行Sql查询语句
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <returns>DataSet记录集</returns>
        public DataSet RunSql(string sql)
        {
            OracleCommand cmd = CreateCom(sql);
            OracleDataAdapter oda = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            return ds;
        }
        public int RunSql(string sql, int i)
        {
            OracleCommand cmd = CreateCom(sql);
            i = cmd.ExecuteNonQuery();
            return i;
        }
        public int RunSql(int i, string sql)
        {
            OracleCommand cmd = CreateCom(sql);
            i = Convert.ToInt32(cmd.ExecuteScalar());
            return i;
        }
        #endregion

        #region-----------------RunProc-------------------------
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="Proc">过程名</param>
        /// <param name="Params">参数数组</param>
        public int RunProc(string Proc, OracleParameter[] Params)
        {
            try
            {
                 OracleCommand cmd = CreateCom(Proc, Params);
                 int count = cmd.ExecuteNonQuery();
                 return count;
            }
            catch (Exception ex)
            {
                showMsg(ex.Message, "");
                return -1;
            }
        }
        public int RunExcel(string Proc, OracleParameter[] Params, int i)
        {
            myOraConnection.Open();
            OracleTransaction myOraTransaction = myOraConnection.BeginTransaction();
            try
            {
                OracleCommand cmd = myOraConnection.CreateCommand();
                cmd.CommandText = Proc;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = myOraTransaction;
                for (int j = 0; j < i; j++)
                {
                    cmd.ExecuteNonQuery();
                }
                myOraTransaction.Commit();
                return 1;
            }
            catch (System.Data.OracleClient.OracleException oex)
            {
                myOraTransaction.Rollback();
                showMsg("Error-TRANSACTION ROLLED BACK " + "/n" + oex.Message, "Rollback Transaction");
                return -1;
            }
            catch (Exception ex)
            {
                showMsg("System Error " + "/n" + ex.Message, "Error");
                return -1;
            }
            finally
            {
                myOraConnection.Close();
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="Proc">过程名</param>
        public int RunProc(string Proc)
        {
            OracleCommand cmd = CreateCom(Proc, null);
            int count = cmd.ExecuteNonQuery();
            return count;
        }
        #endregion

        #region  ---------------------Excel-----------------------------
        //导入Excel的方法
        public DataSet ExcelToDS(string Path, string Tname)
        {
            DataSet ds = new DataSet();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = "select * from [" + Tname + "$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, "table1");
            return ds;
        }
        //导出Excel的方法
        public void ExportExcel(DataGridView asDG)
        {
            if (asDG == null) return;
            if (asDG.RowCount <= 0) return;

            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = "Sheet1";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消

            Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            //写入字段
            for (int i = 0; i < asDG.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = asDG.Columns[i].Name.ToString();
            }
            //写入数值
            for (int r = 0; r < asDG.RowCount; r++)
            {
                for (int i = 0; i < asDG.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = asDG.Rows[r].Cells[i].Value.ToString();
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
            //if (cmbxType.Text != "Notification")
            //{
            //Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[asDG.RowCount + 1, 2]);
            //rg.NumberFormat = "00000000";
            //}
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//强行销毁
            if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
        }
        #endregion

        #region---------------Excel导入数据库-----------------------
        public int ExcelI(DataSet ds)
        {
            myOraConnection.Open();
            OracleTransaction myOraTransaction = myOraConnection.BeginTransaction();
            try
            {
                OracleCommand cmd = myOraConnection.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = myOraTransaction;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    OracleParameter[] Params ={
                //Bc.MakeInParameter ("a_lsh",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["lsh"].ToString ()),
                //Bc.MakeInParameter ("a_dwlsh",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["dwlsh"].ToString ()),
                //Bc.MakeInParameter ("a_bah",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["bah"].ToString ()),
                //Bc.MakeInParameter ("a_xm",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["xm"].ToString ()),
                //Bc.MakeInParameter ("a_sfz",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["sfz"].ToString ()),
                //Bc.MakeInParameter ("a_telephone",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["telephone"].ToString ()),
                //Bc.MakeInParameter ("a_address",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["address"].ToString ()),
                //Bc.MakeInParameter ("a_unitcode",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["unitcode"].ToString ()),
                //Bc.MakeInParameter ("a_unitname",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["unitname"].ToString ()),
                //Bc.MakeInParameter ("a_unit",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["unit"].ToString ()),
                //Bc.MakeInParameter ("a_price",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["price"].ToString ()),
                //Bc.MakeInParameter ("a_rq",OracleType.DateTime,20,Convert .ToDateTime ( ds.Tables [0].Rows [i]["rq"].ToString ())),
                //Bc.MakeInParameter ("a_ywr",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["ywr"].ToString ()),
                //Bc.MakeInParameter ("a_ywks",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["ywks"].ToString ()),
                //Bc.MakeInParameter ("a_unitbm",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["unitbm"].ToString ()),
                //Bc.MakeInParameter ("a_fph",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["fph"].ToString ()),
                //Bc.MakeInParameter ("a_zph",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["zph"].ToString ()),
                //Bc.MakeInParameter ("a_sfzl",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["sfzl"].ToString ()),//现金或支票
                //Bc.MakeInParameter ("a_lb",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["lb"].ToString ()),//门诊或住院
                //Bc.MakeInParameter ("a_userbm",OracleType .VarChar ,20,Session["userbm"].ToString ()),
                //Bc.MakeInParameter ("a_protectid",OracleType .VarChar ,20,ds.Tables [0].Rows [i]["protectid"].ToString ())
            };
                    Bc.RunProc("charge", Params);
                    cmd.CommandText = "charge";
                    if (Params != null)
                    {
                        foreach (OracleParameter param in Params)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    cmd.ExecuteNonQuery();
                }
                myOraTransaction.Commit();
                return 1;
            }
            catch (System.Data.OracleClient.OracleException oex)
            {
                myOraTransaction.Rollback();
                showMsg("Error-TRANSACTION ROLLED BACK " + "/n" + oex.Message, "Rollback Transaction");
                return -1;
            }
            catch (Exception ex)
            {
                showMsg("System Error " + "/n" + ex.Message, "Error");
                return -1;
            }
            finally
            {
                myOraConnection.Close();
            }
        }
        #endregion

        public DataSet Data_Fill(string asSql, string asTable)
        {
            try
            {

                OracleCommand mySqlCommand = new OracleCommand(asSql, myOraConnection );
                OracleDataAdapter mySqlDataAdapter = new OracleDataAdapter();
                DataSet myDataSet = new DataSet();

                mySqlDataAdapter.SelectCommand = mySqlCommand;
                mySqlDataAdapter.Fill(myDataSet, asTable);
                //myDataSet.Tables[0].DefaultView.AllowNew=false;
                return myDataSet;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (null != myOraConnection)
                {
                    if (ConnectionState.Closed != myOraConnection.State)
                    {
                        myOraConnection.Close();
                    }
                }
            }
        }
        public int IUDBySql(string asSql)
        {
            try
            {
                OracleCommand myCommand = new OracleCommand();
                myCommand.Connection = myOraConnection;
                myCommand.CommandText = asSql;
                myCommand.CommandType = CommandType.Text;
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();
                return 1;
            }
            catch (Exception e)
            {
                showMsg(e.ToString(), "error");
                return -1;
            }
            finally
            {
                myOraConnection.Close();
            }
        }
    }
}