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(); } } }}
