最初接触这类数据库通用类库的时候,感觉就是——太神奇了,省了很多建立连接对象、命令对象等对象的代码。
最近我也自己写了一个,部分代码是直接复制Pet shop的SQLHelper,有非常多的不完善,比如还没方法返回DataReader对象等。不过还是有价值的,这个类库可以使用存储过程、有一套机制通过XML配置数据库连接,在连接有问题时会弹出配置窗口。
主要代码:
using
System;
using
System.Collections.Generic;
using
System.Text;
using
System.Data;
using
System.Data.OleDb;
using
System.Data.SqlClient;
using
System.IO;
using
System.Xml;
namespace
ClassLibrary
{
///<summary>
///
数据库操作
///</summary>
public class OleDbUnit
{
OleDbConnection con;
string serverName = string.Empty;
string userName = string.Empty;
string password = string.Empty;
string dbName = string.Empty;
///<summary>
///
建立连接
///</summary>
///<param name="fileName">XML
配置文件名
</param>
public OleDbUnit(string fileName)
{
try
{
DataSet ds = new DataSet();
ds.ReadXml(fileName);
if (ds.Tables["DB"].Rows[0]["encrypt"].ToString() == "true")
{
serverName = Encryption.Decrypt(ds.Tables["DB"].Rows[0]["ServerName"].ToString(), "00000000");
userName = Encryption.Decrypt(ds.Tables["DB"].Rows[0]["UserName"].ToString(), "00000000");
password = Encryption.Decrypt(ds.Tables["DB"].Rows[0]["Password"].ToString(), "00000000");
dbName = Encryption.Decrypt(ds.Tables["DB"].Rows[0]["DBName"].ToString(), "00000000");
}
else
{
serverName = ds.Tables["DB"].Rows[0]["ServerName"].ToString();
userName = ds.Tables["DB"].Rows[0]["UserName"].ToString();
password = ds.Tables["DB"].Rows[0]["Password"].ToString();
dbName = ds.Tables["DB"].Rows[0]["DBName"].ToString();
}
con = new OleDbConnection("Provider=SQLOLEDB;Server=" + serverName + ";User id=" + userName + ";pwd=" + password + ";DataBase=" + dbName);
}
catch
{
}
}
///<summary>
///
获取连接对象
///</summary>
///<returns>
连接对象
</returns>
public OleDbConnection GetConObj()
{
return con;
}
///<summary>
///
打开数据库
///</summary>
///<returns>1
:打开连接、:已经连接,不做其他操作、:出现连接错误
</returns>
public int Open()
{
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
return 1;
}
else
{
return 2;
}
}
catch
{
return 0;
}
}
///<summary>
///
数据库配置
///</summary>
public void OpenDBSet()
{
//
打开配置窗口
frmConfig frm = new frmConfig();
frm.ShowDialog();
}
///<summary>
///
创建配置文件
///</summary>
public static void CreateConfig()
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.LoadXml("<?xml version='1.0'?><Config><DB encrypt='false'><ServerName></ServerName><UserName></UserName><Password></Password><DBName></DBName></DB></Config>");
xmlDocument.Save("Config.xml");
}
///<summary>
///
关闭数据库连接
///</summary>
public void Close()
{
try
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
catch
{ }
}
///<summary>
///
获取DataSet
///</summary>
///<param name="dataSet">
数据集
</param>
///<param name="cmd">SQL
语句
</param>
///<param name="tabelName">
表名
</param>
///<returns>
数据集
</returns>
public DataSet GetDataSet(DataSet dataSet, string cmd, string tabelName)
{
try
{
OleDbCommand dataCommand = new OleDbCommand(cmd, con);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = dataCommand;
dataAdapter.Fill(dataSet, tabelName);
}
catch (Exception ex)
{
throw new Exception("Error in SQL", ex);
}
return dataSet;
}
///<summary>
///
获取DataSet,带参数功能,可使用存储过程,防止SQL注入
///</summary>
///<param name="dataSet">
数据集
</param>
///<param name="tabelName">
表名
</param>
///<param name="connection">
连接对象
</param>
///<param name="cmdType">
访问类型
</param>
///<param name="cmdText">SQL
语句或存储过程
</param>
///<param name="commandParameters">
参数数组,无时填null
</param>
///<returns></returns>
public DataSet GetDataSet(DataSet dataSet,string tabelName, OleDbConnection connection,
CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
{
try
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dataSet, tabelName);
}
catch(Exception ex)
{
throw ex;
}
return dataSet;
}
///<summary>
///
更新操作
///</summary>
///<param name="tabelName">
表名
</param>
///<param name="set">
要更新的字段和值
</param>
///<param name="where">
更新条件(不带where)
</param>
public void ExecuteUpdate(string tabelName, string set,string where)
{
try
{
string cmd = "update " + tabelName + " set " + set + " where " + where;
OleDbCommand dc = new OleDbCommand(cmd, con);
dc.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
///<summary>
///
删除操作
///</summary>
///<param name="tableName">
表名
</param>
///<param name="where">
删除条件(不带where)
</param>
public void ExecuteDelete(string tableName,string where)
{
try
{
string cmd = "delete from "+tableName+" where "+where;
OleDbCommand dc = new OleDbCommand(cmd, con);
dc.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
///<summary>
///
插入操作
///</summary>
///<param name="tableName">
表名
</param>
///<param name="value">
值
</param>
public void ExecuteInsert(string tableName,string value)
{
try
{
string cmd = "insert into " + tableName + " value(" + value + ")";
OleDbCommand dc = new OleDbCommand(cmd, con);
dc.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
///<summary>
///
插入操作
///</summary>
///<param name="tableName">
表名
</param>
///<param name="fieldName">
字段名
</param>
///<param name="value">
值
</param>
public void ExecuteInsert(string tableName, string fieldName,string value)
{
try
{
string cmd = "insert into " + tableName + "(" + fieldName + ") value(" + value + ")";
OleDbCommand dc = new OleDbCommand(cmd, con);
dc.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
///<summary>
///
公共制定
///</summary>
///<param name="cmd">
命令对象
</param>
///<param name="conn">
连接对象
</param>
///<param name="trans">
事务对象
</param>
///<param name="cmdType">
类型
</param>
///<param name="cmdText">SQL
语句或存储过程名
</param>
///<param name="cmdParms">
参数数组
</param>
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn,
OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
其中frmConfig是配置数据库连接的窗口,其中代码如下:
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace ClassLibrary
{
/// <summary>
/// 配置窗口
/// </summary>
public partial class frmConfig : Form
{
public frmConfig()
{
InitializeComponent();
}
private void frmConfig_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}
private void frmConfig_Load(object sender, EventArgs e)
{
if (!System.IO.File.Exists(System.Environment.CurrentDirectory + @"/Config.xml"))
{
OleDbUnit.CreateConfig();
}
else
{
DataSet ds = new DataSet();
ds.ReadXml(System.Environment.CurrentDirectory + @"/Config.xml");
txtServerName.Text = ds.Tables["DB"].Rows[0]["ServerName"].ToString();
txtUserName.Text = ds.Tables["DB"].Rows[0]["UserName"].ToString();
txtPassword.Text = ds.Tables["DB"].Rows[0]["Password"].ToString();
txtDBName.Text = ds.Tables["DB"].Rows[0]["DBName"].ToString();
if (ds.Tables["DB"].Rows[0]["encrypt"].ToString() == "true")
{
//Hack,因为检查到加密所以CheckBox改变状态,而加密了次,所以这里先解密一次
txtServerName.Text = Encryption.Decrypt(txtServerName.Text, "00000000");
txtUserName.Text = Encryption.Decrypt(txtUserName.Text, "00000000");
txtPassword.Text = Encryption.Decrypt(txtPassword.Text, "00000000");
txtDBName.Text = Encryption.Decrypt(txtDBName.Text, "00000000");
chbCryption.Checked = true;
}
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.Close();
}
private void chbCryption_CheckedChanged(object sender, EventArgs e)
{
if (chbCryption.Checked == true)
{
txtServerName.Text = Encryption.Encrypt(txtServerName.Text, "00000000");
txtUserName.Text = Encryption.Encrypt(txtUserName.Text, "00000000");
txtPassword.Text = Encryption.Encrypt(txtPassword.Text, "00000000");
txtDBName.Text = Encryption.Encrypt(txtDBName.Text, "00000000");
}
else
{
txtServerName.Text = Encryption.Decrypt(txtServerName.Text, "00000000");
txtUserName.Text = Encryption.Decrypt(txtUserName.Text, "00000000");
txtPassword.Text = Encryption.Decrypt(txtPassword.Text, "00000000");
txtDBName.Text = Encryption.Decrypt(txtDBName.Text, "00000000");
}
}
private void btnEnter_Click(object sender, EventArgs e)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(System.Windows.Forms.Application.StartupPath + @"/Config.xml");
if (!fileInfo.Exists)
throw new Exception("没有找到配置文件");
System.Xml.XmlDocument xmlDocument = new System.Xml.XmlDocument();
xmlDocument.Load(fileInfo.FullName);
foreach (System.Xml.XmlNode node in xmlDocument["Config"].ChildNodes)
{
if ("DB" == node.Name)
{
node.ChildNodes[0].InnerText = txtServerName.Text;
node.ChildNodes[1].InnerText = txtUserName.Text;
node.ChildNodes[2].InnerText = txtPassword.Text;
node.ChildNodes[3].InnerText = txtDBName.Text;
if (this.chbCryption.Checked == true)
{
node.Attributes["encrypt"].Value = "true";
}
else
{
node.Attributes["encrypt"].Value = "false";
}
}
}
xmlDocument.Save(fileInfo.FullName);
this.Close();
}
}
}
还有用到的一个加密类Encryption:
using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using System.IO;
namespace ClassLibrary
{
/// <summary>
/// 加密解密通用类库函数
/// </summary>
public class Encryption
{
/// <summary>
/// 不可逆MD5加密
/// </summary>
/// <param name="text">要加密的字符串</param>
/// <returns></returns>
public static string MD5(string text)
{
return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(text, "MD5");
}
/// <summary>
/// 可逆加密
/// </summary>
/// <param name="strText">要加密的字符</param>
/// <param name="strEncrKey">密钥</param>
/// <returns></returns>
public static String Encrypt(String strText, String strEncrKey)
{
Byte[] byKey = { };
Byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
try
{
byKey = System.Text.Encoding.UTF8.GetBytes(strEncrKey.Substring(0, 8));
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
Byte[] inputByteArray = Encoding.UTF8.GetBytes(strText);
MemoryStream ms = new MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(byKey, IV), CryptoStreamMode.Write);
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
return Convert.ToBase64String(ms.ToArray());
}
catch
{
return "err";
}
}
/// <summary>
/// 可逆解密
/// </summary>
/// <param name="strText">要解密的字符</param>
/// <param name="sDecrKey">密钥</param>
/// <returns></returns>
public static String Decrypt(String strText, String sDecrKey)
{
Byte[] byKey = { };
Byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
Byte[] inputByteArray = new byte[strText.Length];
try
{
byKey = System.Text.Encoding.UTF8.GetBytes(sDecrKey.Substring(0, 8));
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
inputByteArray = Convert.FromBase64String(strText);
MemoryStream ms = new MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, IV), CryptoStreamMode.Write);
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
return encoding.GetString(ms.ToArray());
}
catch
{
return "err";
}
}
}
}