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