关闭

SQL数据库操作类

标签: 数据库sqlcommandstringnullexception
450人阅读 评论(0) 收藏 举报

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

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:36000次
    • 积分:544
    • 等级:
    • 排名:千里之外
    • 原创:16篇
    • 转载:13篇
    • 译文:0篇
    • 评论:7条
    最新评论