using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
/// <summary>
///SqlConnDb類(lèi),適用于Sql數(shù)據(jù)庫(kù)操作
/// </summary>
public class SqlConnDb
{
  SqlConnection conn = null; //連接數(shù)據(jù)庫(kù)的對(duì)象
  /// <summary>
  /// 構(gòu)造函數(shù),連接數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)連接字符在web.Config文件的AppSettings下的conStr
  /// </summary>
  public SqlConnDb(){
    if (conn == null){ //判斷連接是否為空
      //string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//連接數(shù)據(jù)庫(kù)的字符串
      string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//連接數(shù)據(jù)庫(kù)的字符串
      conn = new SqlConnection(conString);
      if (conn.State == ConnectionState.Closed){
        conn.Open();//打開(kāi)數(shù)據(jù)庫(kù)連接
      }
    }
  }
  /// <summary>
  /// 從數(shù)據(jù)庫(kù)中查詢(xún)數(shù)據(jù)的,返回為DataSet
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public DataSet query(string sql){
    DataSet ds = new DataSet();//DataSet是表的集合
    SqlDataAdapter da = new SqlDataAdapter(sql, conn);//從數(shù)據(jù)庫(kù)中查詢(xún)
    da.Fill(ds);//將數(shù)據(jù)填充到DataSet
    connClose();//關(guān)閉連接
    return ds;//返回結(jié)果
  }
  /// <summary>
  /// 更新數(shù)據(jù)庫(kù)
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public int update(string sql){
    SqlCommand oc = new SqlCommand();//表示要對(duì)數(shù)據(jù)源執(zhí)行的SQL語(yǔ)句或存儲(chǔ)過(guò)程
    oc.CommandText = sql;//設(shè)置命令的文本
    oc.CommandType = CommandType.Text;//設(shè)置命令的類(lèi)型
    oc.Connection = conn;//設(shè)置命令的連接
    int x = oc.ExecuteNonQuery();//執(zhí)行SQL語(yǔ)句
    connClose();//關(guān)閉連接
    return x;  //返回一個(gè)影響行數(shù)
  }
  /// <summary>
  /// 關(guān)閉數(shù)據(jù)庫(kù)連接
  /// </summary>
  public void connClose() {
    if (conn.State == ConnectionState.Open){
      //判斷數(shù)據(jù)庫(kù)的連接狀態(tài)，如果狀態(tài)是打開(kāi)的話(huà)就將它關(guān)閉
      conn.Close();
    }
  }
}