C# 數據庫的基本操作（sqlserver）

一、首先是與sqlserver的基本操作
 
 
連接字符在配置文件中是這樣寫的
[html] 
<connectionStrings> 
    <add name="ConnStr" connectionString="data source=xp-ba785745002d;database=databaseonline;Uid=sa;pwd=sasa"/> 
</connectionStrings> 
 
基本操作類如下：
[csharp]
public class SQLHelper 
{ 
    private SqlConnection sqlCon = null; 
    private SqlCommand cmd = null; 
    private SqlDataReader sdr = null; 
    public SQLHelper() 
    { 
        sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); 
    } 
  /// <summary> 
  /// 打開數據庫連接 
  /// </summary> 
  /// <returns></returns> 
    private SqlConnection GetCon() 
    { 
        if (sqlCon.State==ConnectionState.Closed) 
        { 
          sqlCon.Open();   
        } 
        return sqlCon; 
    } 
    /// <summary> 
    /// 執行不帶參數的增刪改sql語句或存儲過程 
    /// </summary> 
    /// <param name="cmdText">增刪改sql語句或存儲過程</param> 
    /// <param name="ct">命令類型</param> 
    /// <returns></returns> 
    public int ExecuteNonQuery(string cmdText, CommandType ct) 
    { 
        int rex; 
        try 
        { 
        SqlCommand sqlcom = new SqlCommand(cmdText,GetCon()); 
        sqlcom.CommandType = ct; 
        rex =sqlcom.ExecuteNonQuery(); 
        } 
        catch (Exception ex) 
        { 
 
            throw ex; 
        } 
        finally 
        { 
            if (sqlCon.State==ConnectionState.Open) 
            { 
               sqlCon.Close();   
            } 
        } 
        return rex; 
    } 
    /// <summary> 
    ///  執行帶參數的增刪改SQL語句或存儲過程 
    /// </summary> 
    /// <param name="cmdText">增刪改SQL語句或存儲過程</param> 
    /// <param name="ct">命令類型</param> 
    /// <returns></returns> 
    public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct) 
    { 
        int res; 
        using (cmd = new SqlCommand(cmdText, GetCon())) 
        { 
            cmd.CommandType = ct; 
            cmd.Parameters.AddRange(paras); 
            res = cmd.ExecuteNonQuery(); 
        } 
        return res; 
    } 
    /// <summary> 
    ///  執行帶參數的查詢SQL語句或存儲過程 
    /// </summary> 
    /// <param name="cmdText">查詢SQL語句或存儲過程</param> 
    /// <param name="paras">參數集合</param> 
    /// <param name="ct">命令類型</param> 
    /// <returns></returns> 
    public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct) 
    { 
        DataTable dt = new DataTable(); 
        cmd = new SqlCommand(cmdText,GetCon()); 
        cmd.CommandType = ct; 
        cmd.Parameters.AddRange(paras); 
        using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) 
        { 
            dt.Load(sdr); 
        } 
        return dt; 
    } 
    /// <summary> 
    /// 執行不帶參數的查詢SQL語句或存儲過程 
    /// </summary> 
    /// <param name="cmdText">查詢SQL語句或存儲過程</param> 
    /// <param name="ct">命令類型</param> 
    /// <returns></returns> 
    public DataTable ExecuteQuery(string cmdText, CommandType ct) 
    { 
        DataTable dt = new DataTable(); 
        cmd = new SqlCommand(cmdText, GetCon()); 
        cmd.CommandType = ct; 
        using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) 
        { 
            dt.Load(sdr); 
        } 
        return dt; 
    } 
} 
下面是兩中調用方法：
[csharp]
public int logincheck(string admin, string pwd) 
{ 
 
    SqlParameter[] paras = new SqlParameter[] {  
    new SqlParameter("@AdminName",admin), 
    new SqlParameter("@Password",pwd) 
    }; 
    int i = Convert.ToInt32(sqlhelper.ExecuteQuery("Admin_check_login", paras, CommandType.StoredProcedure).Rows[0][0].ToString()); 
    return i; 
} 
 
     這種是直接與數據庫交互沒有用到sqlhelper類
[html] 
DataRow dr; 
       string sql = "SELECT PKID, User_Name, Password, User_Grade,(SELECT UserGrade FROM UserGrade WHERE pkid = user_grade) AS UG FROM Users WHERE (User_Name =@UserName) AND (Password = @Password)"; 
 
       SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString); 
       SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(sql, sqlConnection); 
       sqlAdapter1.SelectCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar)); 
       sqlAdapter1.SelectCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar)); 
       sqlAdapter1.SelectCommand.Parameters["@UserName"].Value = tbName.Text.Trim(); 
       sqlAdapter1.SelectCommand.Parameters["@Password"].Value = tbPwd.Text.Trim(); 
 
       DataSet product = new DataSet(); 
       sqlAdapter1.Fill(product,"Users"); 
       dr = product.Tables[0].Rows[0];