using System;using System.Collections;using System.Text;using System.Security.Cryptography;using System.IO;
/// <summary> /// 数据库操作类与Xml文档操作(保存/读取Connectionstring) /// </summary> class DBOperation { /// <summary> /// 配置文件结构 /// </summary> public struct ConfigStruct { /// <summary> /// 数据库服务器主机地址 /// </summary> public string hostAddress ; /// <summary> /// 用户名 /// </summary> public string userName ; /// <summary> /// 密码 /// </summary> public string password ; /// <summary> /// 数据库名 /// </summary> public string DBName ; }
/// <summary> /// 类构造函数 /// </summary> public DBOperation() { // }
#region 定义用于写XML文档的字段名的变量 private string str_HA = "hostAddress"; private string str_UN = "userName"; private string str_PWD = "password"; private string str_DBN = "DBName"; #endregion #region 读写Xml文档 /// <summary> /// 读XML文档 /// </summary> /// <param name="name">要取的配置文件中的指定数据源的名字,如:旧系统 "oldsystem" </param> public ConfigStruct readXML(string name) { try { //定义新的结构变量 ConfigStruct cfg = new ConfigStruct();
//定义一个新的dataset System.Data.DataSet ds = new System.Data.DataSet();
//判断文件是否存在,不存在提示错误并返回一个空的结构对象 if (System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory +"config.xml")) { //如果存在则读取config.xml文件的数据 ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml"); } else { // System.Windows.Forms.MessageBox.Show("config.xml文件不存在!" , "警告", // System.Windows.Forms.MessageBoxButtons.OK , // System.Windows.Forms.MessageBoxIcon.Warning); return new ConfigStruct(); } //判断表是否存在,不存在提示错误并返回一个空的结构对象 if (ds.Tables.IndexOf(name.ToUpper())== -1 ) { // System.Windows.Forms.MessageBox.Show("在config.xml中不能找到相关的数据源的配置信息!" , "警告", // System.Windows.Forms.MessageBoxButtons.OK , // System.Windows.Forms.MessageBoxIcon.Warning); return new ConfigStruct(); }
SymmetricMethod sm = new SymmetricMethod(); //存在则取其值 cfg.hostAddress = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_HA) + "'")[0]["value"].ToString()); cfg.userName = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_UN) + "'")[0]["value"].ToString()); cfg.password = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_PWD) + "'")[0]["value"].ToString()); cfg.DBName = sm.Decrypto(ds.Tables[name.ToUpper()].Select("key='" + sm.Encrypto(str_DBN) + "'")[0]["value"].ToString());
ds.Dispose();
return cfg; } catch//(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return new ConfigStruct(); } }
/// <summary> /// 写XML文档 /// </summary> /// <param name="name">数据源的名称</param> /// <param name="hostAddress">主机地址值</param> /// <param name="DBName">数据库名称</param> /// <param name="UserName">用户名</param> /// <param name="password">密码</param> /// <param name="con_str">数据库的数据源信息</param> public Boolean writeXML(string name , string hostAddress ,string DBName , string UserName ,string password) { try { //定义一个dataset System.Data.DataSet ds = new System.Data.DataSet("config"); //判断是否存在config.xml文件,如果存在从该文件中读取内容到dataset if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory +"config.xml")) { ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml"); } //判断是否存在该表,如果存在则删除该表 if(ds.Tables.IndexOf(name.ToUpper()) != -1 ) { ds.Tables.Remove(name.ToUpper()); }
//定义一个datatable System.Data.DataTable dt = new System.Data.DataTable(name.ToUpper());
//为新定义的表增加列 dt.Columns.Add("key"); dt.Columns.Add("value"); SymmetricMethod sm = new SymmetricMethod();
//增加记录表新定义的表中 dt.Rows.Add(new object[2]{ sm.Encrypto( str_HA ) , sm.Encrypto( hostAddress)}); dt.Rows.Add(new object[2]{ sm.Encrypto( str_UN ) , sm.Encrypto( UserName)}); dt.Rows.Add(new object[2]{ sm.Encrypto( str_PWD) , sm.Encrypto( password)}); dt.Rows.Add(new object[2]{ sm.Encrypto( str_DBN) , sm.Encrypto( DBName)}); //将表增加到定义的新的dataset中 ds.Tables.Add(dt);
//写入xml文档 ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml"); //释放datatable 与 dataset dt.Dispose(); ds.Dispose(); return true; } catch//(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return false; } }
/// <summary> /// 写XML文档 /// </summary> /// <param name="hostAddress">数据库服务器主机名</param> /// <param name="con_str">数据库的数据源信息</param> public void writeXML(string name , ConfigStruct con_str) { try { //定义一个dataset System.Data.DataSet ds = new System.Data.DataSet("config");
//判断是否存在config.xml文件,如果存在从该文件中读取内容到dataset if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory +"config.xml")) { ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml"); } //判断是否存在该表,如果存在则删除该表 if(ds.Tables.IndexOf(name.ToUpper()) != -1 ) { ds.Tables.Remove(name.ToUpper()); }
//定义一个datatable System.Data.DataTable dt = new System.Data.DataTable(name.ToUpper());
//为新定义的表增加列 dt.Columns.Add("key"); dt.Columns.Add("value"); SymmetricMethod sm = new SymmetricMethod();
//增加记录表新定义的表中 dt.Rows.Add(new object[2]{ sm.Encrypto( str_HA ), sm.Encrypto( con_str.hostAddress)}); dt.Rows.Add(new object[2]{ sm.Encrypto( str_UN ), sm.Encrypto( con_str.userName)}); dt.Rows.Add(new object[2]{ sm.Encrypto( str_PWD ), sm.Encrypto( con_str.password)}); dt.Rows.Add(new object[2]{ sm.Encrypto( str_DBN ), sm.Encrypto( con_str.DBName)}); //将表增加到定义的新的dataset中 ds.Tables.Add(dt);
//写入xml文档 ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory +"config.xml"); //释放datatable 与 dataset dt.Dispose(); ds.Dispose(); } catch(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); throw exp; } }
#endregion
#region 数据库操作类 /// <summary> /// 返回连接字符串(传入多个字符串) /// </summary> /// <param name="hostAddress">数据库服务器的主机地址</param> /// <param name="userName">登陆的用户名</param> /// <param name="Password">登陆的用户密码</param> /// <param name="DBName">数据库名</param> /// <returns>String 连接字符串</returns> public string getConnectionString(string hostAddress , string userName , string Password , string DBName) { try { return "Data Source="+hostAddress+";Initial Catalog="+DBName+ ";Persist Security Info=True;User ID="+userName+";Password="+Password+ ";packet size=4096"; } catch//(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return null; } }
/// <summary> /// 返回连接字符串(传入一个结构对象) /// </summary> /// <param name="hostAddress">保存数据源的结构对象</param> /// <returns>String 连接字符串</returns> public string getConnectionString(ConfigStruct con_str) { try { return "Data Source="+con_str.hostAddress+";Initial Catalog="+con_str.DBName+ ";Persist Security Info=True;User ID="+con_str.userName+";Password="+con_str.password+ ";packet size=4096"; } catch//(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return null; } }
/// <summary> /// 返回一个新的Connection对象,不打开连接 /// </summary> /// <param name="connectionString">连接字符串</param> /// <returns>System.Data.SqlClient.SqlConnection</returns> public System.Data.SqlClient.SqlConnection connInit(string connectionString) { try { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = connectionString; return conn; } catch //(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return new System.Data.SqlClient.SqlConnection(); } }
/// <summary> /// 返回一个新的Connection对象,不打开连接 /// </summary> /// <param name="connectionString">连接字符串</param> /// <returns>System.Data.SqlClient.SqlConnection</returns> public void connDispose(System.Data.SqlClient.SqlConnection sqlConn) { try { if(sqlConn.State == System.Data.ConnectionState.Open) { sqlConn.Close(); } sqlConn.Dispose(); } catch //(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); } }
/// <summary> /// 返回一个新的Connection对象并找开该连接 /// </summary> /// <param name="connectionString">连接字符串</param> /// <returns>System.Data.SqlClient.SqlConnection</returns> public System.Data.SqlClient.SqlConnection connInitAndOpen(string connectionString) { try { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = connectionString; conn.Open(); return conn; } catch //(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return new System.Data.SqlClient.SqlConnection(); } }
/// <summary> /// 执行SQL语句并返回DateTable /// </summary> /// <param name="conn">连接对象</param> /// <param name="sqlString">SQL语句</param> /// <returns>System.Data.DataTable</returns> public System.Data.DataTable execSqlReturnDataTable(System.Data.SqlClient.SqlConnection conn , string sqlString) { try { if (conn.State == System.Data.ConnectionState.Closed ) { conn.Open(); } System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlString , conn); cmd.CommandTimeout = 36000 ; System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd); System.Data.DataTable dt = new System.Data.DataTable(); dap.Fill(dt); cmd.Dispose(); conn.Close(); dap.Dispose(); conn.Close(); return dt; } catch//(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return new System.Data.DataTable(); }
}
/// <summary> /// 执行SQL语句并返回DateTable /// </summary> /// <param name="conn">连接对象</param> /// <param name="sqlString">SQL语句</param> /// <returns>System.Data.DataSet</returns> public System.Data.DataSet execSqlReturnDataSet(System.Data.SqlClient.SqlConnection conn , string sqlString) { try { if (conn.State == System.Data.ConnectionState.Closed ) { conn.Open(); } System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlString , conn); cmd.CommandTimeout = 36000 ; System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd); System.Data.DataSet ds = new System.Data.DataSet(); dap.Fill(ds); cmd.Dispose(); conn.Close(); dap.Dispose(); return ds; } catch//(Exception exp) { // System.Windows.Forms.MessageBox.Show(exp.Message); return new System.Data.DataSet(); }
} /// <summary> /// 执行SQL语句并返回DateTable /// </summary> /// <param name="conn">连接对象</param> /// <param name="sqlString">SQL语句</param> public Boolean execSql(System.Data.SqlClient.SqlConnection conn , string sqlString) { try { if (conn.State == System.Data.ConnectionState.Closed ) { conn.Open(); } System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlString , conn); cmd.CommandTimeout = 36000 ; int i = cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); return true; } // catch//(NullReferenceException Nexp)// { // //System.Windows.Forms.MessageBox.Show(Nexp.Message);// return false;// } catch//(Exception exp) { //System.Windows.Forms.MessageBox.Show(exp.Message); return false; }
} #endregion }
/// <summary> /// 对称加密算法类 /// </summary> public class SymmetricMethod { private SymmetricAlgorithm mobjCryptoService; private string Key; /// <summary> /// 对称加密类的构造函数 /// </summary> public SymmetricMethod() { mobjCryptoService = new RijndaelManaged(); Key = @"Guz(%&hj7x89H$yuBI0456FtmaT5&fvHUFCy76*h%(HilJ$lhj!y6&(*jkP87jH7"; } /// <summary> /// 获得密钥 /// </summary> /// <returns>密钥</returns> private byte[] GetLegalKey() { string sTemp = Key; mobjCryptoService.GenerateKey(); byte[] bytTemp = mobjCryptoService.Key; int KeyLength = bytTemp.Length; if (sTemp.Length > KeyLength) sTemp = sTemp.Substring(0, KeyLength); else if (sTemp.Length < KeyLength) sTemp = sTemp.PadRight(KeyLength, ' '); return ASCIIEncoding.ASCII.GetBytes(sTemp); } /// <summary> /// 获得初始向量IV /// </summary> /// <returns>初试向量IV</returns> private byte[] GetLegalIV() { string sTemp = @"E4ghj*Ghg7!rNIfb&95GUY86GfghUb#er57HBh(u%g6HJ($jhWk7&!hg4ui%$hjk"; mobjCryptoService.GenerateIV(); byte[] bytTemp = mobjCryptoService.IV; int IVLength = bytTemp.Length; if (sTemp.Length > IVLength) sTemp = sTemp.Substring(0, IVLength); else if (sTemp.Length < IVLength) sTemp = sTemp.PadRight(IVLength, ' '); return ASCIIEncoding.ASCII.GetBytes(sTemp); } /// <summary> /// 加密方法 /// </summary> /// <param name="Source">待加密的串</param> /// <returns>经过加密的串</returns> public string Encrypto(string Source) { byte[] bytIn = UTF8Encoding.UTF8.GetBytes(Source); MemoryStream ms = new MemoryStream(); mobjCryptoService.Key = GetLegalKey(); mobjCryptoService.IV = GetLegalIV(); ICryptoTransform encrypto = mobjCryptoService.CreateEncryptor(); CryptoStream cs = new CryptoStream(ms, encrypto, CryptoStreamMode.Write); cs.Write(bytIn, 0, bytIn.Length); cs.FlushFinalBlock(); ms.Close(); byte[] bytOut = ms.ToArray(); return Convert.ToBase64String(bytOut); } /// <summary> /// 解密方法 /// </summary> /// <param name="Source">待解密的串</param> /// <returns>经过解密的串</returns> public string Decrypto(string Source) { try { byte[] bytIn = Convert.FromBase64String(Source); MemoryStream ms = new MemoryStream(bytIn, 0, bytIn.Length); mobjCryptoService.Key = GetLegalKey(); mobjCryptoService.IV = GetLegalIV(); ICryptoTransform encrypto = mobjCryptoService.CreateDecryptor(); CryptoStream cs = new CryptoStream(ms, encrypto, CryptoStreamMode.Read); StreamReader sr = new StreamReader(cs); return sr.ReadToEnd(); } catch(Exception exp) { return ""; } } }