学习C#,经常和数据库打交道,下面是一个c#对数据库操作的简单类,可供新手参考!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace wcrord
{
class DBManager
{
private bool bIsOpen = false;
private SqlConnection sql;
private string _sqlconnstr;
private string _strTableName;
private string _strErrorMsg;
/// <summary>
/// 构造方法
/// </summary>
/// <param name="strDBname">数据库名称</param>
/// <param name="strTableName">数据表名称</param>
public DBManager(string strDBname, string strTableName)
{
InitDirDirectory();
this._strTableName = strTableName;
this._sqlconnstr = (@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\" + strDBname + ";Integrated Security=True;User Instance=True");
}
~DBManager()
{
AdjustState(sql, false);
sql.Dispose();
}
/// <summary>
/// 初始化数据库的目录
/// </summary>
private void InitDirDirectory()
{
string dataDir = AppDomain.CurrentDomain.BaseDirectory;
if (dataDir.EndsWith(@"\bin\Debug\") || dataDir.EndsWith(@"\bin\Release\"))
{
dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}
}
private void AdjustState(SqlConnection sql,bool bopen)
{
ConnectionState state = sql.State;
if(state == ConnectionState.Open && !bopen)
{
sql.Close();
}
if(state == ConnectionState.Closed && bopen)
{
sql.Open();
}
}
private void SetLastError(string strMsg)
{
this._strErrorMsg = strMsg;
}
/// <summary>
/// 用于获取最后一个错误的信息
/// </summary>
/// <returns></returns>
public string GetLastError()
{
return this._strErrorMsg;
}
/// <summary>
/// 初始化一个要操作数据库
/// </summary>
/// <returns></returns>
public bool OpenDataBase()
{
bool bRet = false;
try
{
sql = new SqlConnection(_sqlconnstr);
bIsOpen = true;
bRet = true;
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
return bRet;
}
/// <summary>
/// 执行简单的sql命令如插入,删除,更新!返回-1未打开数据库,非0为数据更新成功
/// </summary>
/// <param name="strCmd"></param>
/// <returns></returns>
public int ExecCommand(string strCmd)
{
int nRet = -1;
if(!bIsOpen)
{
SetLastError("请先用OpenDataBase方法打开数据库.");
goto _End;
}
try
{
using (SqlCommand cmd = sql.CreateCommand())
{
AdjustState(sql, true);
cmd.CommandText = strCmd;
nRet = cmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
_End:
return nRet;
}
/// <summary>
/// 获取指定条件下数据库加载在内存上的数据表
/// </summary>
/// <param name="sqlCmd"></param>
/// <returns></returns>
public DataTable GetDataTable(string sqlCmd)
{
try
{
AdjustState(sql, true);
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd, sql);
sda.Fill(ds);
return ds.Tables[0];
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
return null;
}
/// <summary>
/// 获取指定条件下数据库加载在内存上的数据记录集
/// </summary>
/// <param name="sqlCmd"></param>
/// <returns></returns>
public DataSet GetDataSet(string sqlCmd)
{
try
{
AdjustState(sql, true);
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd, sql);
sda.Fill(ds);
return ds;
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
return null;
}
/// <summary>
/// 获取指定条件下有多少条数据,如果bFull为真获取表所有数据条数,后两个参数可以忽略
/// </summary>
/// <param name="strItem"></param>
/// <param name="strvalues"></param>
/// <param name="bFull"></param>
/// <returns></returns>
public int GetItemCount(bool bFull ,string strItem, string strvalues)
{
int nRet = -1;
try
{
AdjustState(sql, true);
using(SqlCommand cmd = sql.CreateCommand())
{
if (bFull)
cmd.CommandText = "select count(*) from " + _strTableName + "";
else
cmd.CommandText = "select count(*) from " + _strTableName + " where " + strItem + " = '" + strvalues + "'";
nRet = (int)cmd.ExecuteScalar();
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
return nRet;
}
/// <summary>
/// 获取指定行的指定项数据int型
/// </summary>
/// <param name="strdestItem"></param>
/// <param name="strSrcItem"></param>
/// <param name="strSrcValues"></param>
/// <returns></returns>
public int GetItemIntValues(string strdestItem, string strSrcItem, string strSrcValues)
{
int nDest = -1;
try
{
AdjustState(sql, true);
using (SqlCommand cmd = sql.CreateCommand())
{
cmd.CommandText = "select * from " + _strTableName + " where " + strSrcItem + " = '" + strSrcValues + "'";
using (SqlDataReader rd = cmd.ExecuteReader())
{
rd.Read();
nDest = rd.GetInt32(rd.GetOrdinal(strdestItem));
}
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
return nDest;
}
/// <summary>
/// 获取指定行的指定项的数据string型
/// </summary>
/// <param name="strdestItem"></param>
/// <param name="strSrcItem"></param>
/// <param name="strSrcValues"></param>
/// <returns></returns>
public string GetItemStringValues(string strdestItem, string strSrcItem, string strSrcValues)
{
string strDeststr = "";
try
{
AdjustState(sql, true);
using (SqlCommand cmd = sql.CreateCommand())
{
cmd.CommandText = "select * from " + _strTableName + " where " + strSrcItem + " = '" + strSrcValues + "'";
using (SqlDataReader rd = cmd.ExecuteReader())
{
rd.Read();
strDeststr = rd.GetString(rd.GetOrdinal(strdestItem)).Trim();
}
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
return strDeststr;
}
/// <summary>
/// 获取指定列上的所有数据,返回string数组
/// </summary>
/// <param name="strdestItem"></param>
/// <param name="strSrcItem"></param>
/// <param name="strSrcValues"></param>
/// <param name="nCount"></param>
/// <returns></returns>
public string[] GetlistAllString(string strdestItem, string strSrcItem, string strSrcValues, int nCount)
{
string[] strDeststr = new string[nCount];
try
{
AdjustState(sql, true);
using(SqlCommand cmd = sql.CreateCommand())
{
cmd.CommandText = "select * from " + _strTableName + " where " + strSrcItem + " = '" + strSrcValues + "'";
int i = 0;
using (SqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
strDeststr[i++] = rd.GetString(rd.GetOrdinal(strdestItem)).Trim();
}
}
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
return strDeststr;
}
/// <summary>
/// 获取指定行上的所有数据,返回string数组
/// </summary>
/// <param name="strItem"></param>
/// <param name="strValues"></param>
/// <returns></returns>
public string[] GetRowAllString(string strItem, string strValues,out int nItem)
{
int nCount = 0;
try
{
AdjustState(sql, true);
using(SqlCommand cmd = sql.CreateCommand())
{
cmd.CommandText = "select * from " + _strTableName + " where " + strItem + " = '" + strValues + "'";
using (SqlDataReader rd = cmd.ExecuteReader())
{
nCount = rd.FieldCount;
if (strItem == null || strValues == null)
goto _End;
string[] strArray = new string[nCount];
rd.Read();
for (int i = 0; i < nCount; i++)
{
strArray[i] = rd[i].ToString();
}
nItem = nCount;
return strArray;
}
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
_End:
nItem = nCount;
return null;
}
public string[] GetRowAllStringEx(string strItem1, string strValues1, string strItem2, string strValues2, out int nItem)
{
int nCount = 0;
try
{
AdjustState(sql, true);
using (SqlCommand cmd = sql.CreateCommand())
{
cmd.CommandText = "select * from " + _strTableName + " where " + strItem1 + " = '" + strValues1 + "' and " + strItem2 + "='" + strValues2 + "'";
using (SqlDataReader rd = cmd.ExecuteReader())
{
nCount = rd.FieldCount;
if (strItem1 == null || strValues1 == null)
goto _End;
string[] strArray = new string[nCount];
rd.Read();
for (int i = 0; i < nCount; i++)
{
strArray[i] = rd[i].ToString();
}
nItem = nCount;
return strArray;
}
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
_End:
nItem = nCount;
return null;
}
/// <summary>
/// 辅助函数,匹配数据.返回值:-1数据不存在,0目的值不存在,1匹配成功,否则,使用getlasterror获取信息!
/// </summary>
/// <param name="_srcI">原数据项</param>
/// <param name="_srcV">原数据值</param>
/// <param name="_destI">目的数据项</param>
/// <param name="_destV">目的数据值</param>
/// <param name="_rowV">返回当前行的所有数据</param>
/// <returns></returns>
public int CheckPwd(string _srcI,string _srcV,string _destI,string _destV,out string[] _rowV)
{
if(_srcI ==""||_srcV ==""||_destI ==""||_destV =="")
{
SetLastError("输入信息不完整");
_rowV = null;
return -2;
}
try
{
AdjustState(sql, true);
int nCount = GetItemCount(false, _srcI, _srcV);
if (nCount == 0)
goto _End;
string[]str = new string[nCount];
str = GetlistAllString(_destI, _srcI, _srcV, nCount);
for (int i = 0; i < nCount;i++)
{
if (_destV.Trim() == str[i].Trim())
{
int n = -1;
GetRowAllStringEx(null, null, null, null, out n);
if(n != -1)
{
_rowV = new string[n];
_rowV = GetRowAllStringEx(_destI, str[i].Trim(), _srcI, _srcV, out n);
return 1;
}
}
}
}
catch (System.Exception ex)
{
SetLastError(ex.Message);
}
finally
{
AdjustState(sql, false);
}
_End:
_rowV = null;
return 0;
}
}
}