SQL数据库操作类

这是自己项目是使用的操作类，大家可以参考一下，编写适合自己的数据库操作类：

引入命名空间：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);   }