这是自己项目是使用的操作类,大家可以参考一下,编写适合自己的数据库操作类:
引入命名空间:
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using Microsoft.Win32;
using System.Windows.Forms;
类的主体:
public class DB
{
private static SqlConnection Connection; //静态成员。使用时对象不用实像化
public static SqlCommand Command;
public static SqlDataAdapter Adapter;
public static string s_dsn = "";//保存连接字符串
public DB()
{
if(s_dsn.Length == 0)
InitDSN();
}
public bool InitDSN()//此函数也可以改为静态的,这样每次使用此类时,不必实例化了。
{
try
{//从注册表是得到连接字符串,根据自己的需要可以改动。
RegistryKey myReg=Registry.LocalMachine.OpenSubKey(@"software/ES/ESNetExam/");
string dbname=EnDecode.DencodeStr((string)myReg.GetValue("DBName"));
string dbpasswrod=EnDecode.DencodeStr((string)myReg.GetValue("DBPassword"));
string dbserver=(string)myReg.GetValue("ServerIP");
myReg.Close();
s_dsn="workstation id=" + dbserver;
s_dsn +=";packet size=4096;user id=" + dbname ;
s_dsn +=";password = " + dbpasswrod;
s_dsn += ";data source=" + dbserver;
s_dsn +=";persist security info=False;initial catalog=EastWebExam";
InitConnection();
return true;
}
catch
{
dispose();
return false;
}
}
#region
public static SqlTransaction CreateTransaction()
{
return Connection.BeginTransaction();
}
#endregion
#region 判断连接数据库字符串是否存在
/// <summary>
/// 判断连接数据库字符串是否存在
/// </summary>
public static bool isDbEnbled
{
get
{
try
{
RegistryKey myReg=Registry.LocalMachine.OpenSubKey(@"software/ES/ESNetExam/");
if(myReg == null)
{
return false;
}
string dbserver=(string)myReg.GetValue("ServerIP");
if(dbserver == null || dbserver == "")
{
return false;
}
return true;
}
catch
{
return false;
}
}
}
#endregion
//释放资源
public static void dispose()
{
if(Command != null)
Command.Dispose();
if(Adapter != null)
Adapter.Dispose();
if(Connection != null)
{
CloseConnection();
Connection.Dispose();
}
}
#region 显示出错信息
public static void showError(string err)
{
MessageBox.Show("数据库出错!" + err,"提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
#endregion
#region 初始化连接
/// <summary>
/// 初始化连接
/// </summary>
/// <param name="_StrConnection">连接串</param>
private static void InitConnection()
{
if(Connection == null)
Connection = new SqlConnection(s_dsn);
else
{
CloseConnection();
Connection.ConnectionString = s_dsn;
}
}
#endregion
#region 初始化Command
/// <summary>
/// 初始化Command,默认为Text
/// type:1-text 2-StoredProcedure;3-TableDirect
/// </summary>
public static void InitCommand(int type)
{
if(Command == null)
{
Command = Connection.CreateCommand();
}
else
{
Command.CommandText = "";
Command.Parameters.Clear();
switch(type)
{
case 1:
Command.CommandType = CommandType.Text;
break;
case 2:
Command.CommandType = CommandType.StoredProcedure;
break;
case 3:
Command.CommandType = CommandType.TableDirect;
break;
default:
Command.CommandType = CommandType.Text;
break;
}
}
}
#endregion
#region 初始化Adapter
/// <summary>
/// 初始化Adapter
/// 注,可能需要扩充以适应Adapter和DataSet丰富的功能
/// </summary>
public static void InitAdapter()
{
if(Adapter == null)
{
Adapter = new SqlDataAdapter(Command);
}
else
{
Adapter.SelectCommand = Command;
}
}
#endregion
#region 返回一个值
public object GetOneValue(string sql)
{
OpenConnection();
if(Command == null)
{
InitCommand(1);
}
Command.CommandType = CommandType.Text;
Command.CommandText = sql;
return Command.ExecuteScalar();
}
#endregion
#region 关闭连接
//
//
public static void CloseConnection()
{
if(Connection.State == ConnectionState.Open)
{
try
{
Connection.Close();
}
catch(Exception e1)
{
throw e1;
}
}
}
#endregion
#region 打开连接
//
public static void OpenConnection()
{
if(Connection == null)
{
InitConnection();
}
if(Connection.State == ConnectionState.Closed)
{
try
{
Connection.Open();
}
catch(Exception e1)
{
throw e1;
}
}
}
#endregion
}
使用方法示例:
string sql="select * from company where parent like '" + nodeID + "'";
SqlDataReader dr = null;
try
{
DB.InitCommand(1);
DB.Command.CommandText = sql;
dr = DB.Command.ExecuteReader();
}
catch(SqlException ex)
{
DB.showError(ex.Message);
}