C#資料庫操作類（完整通用）

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

 

namespace MSSQL資料庫操作類

{

   public class SqlServer

    {

 

       SqlConnection _con = new SqlConnection();

        SqlCommand _cmd = new SqlCommand();

       SqlDataAdapter _sda = new SqlDataAdapter();

 

       public SqlServer()

           :this(ConfigurationManager.ConnectionStrings["MSSQLConStr"].ConnectionString)

       {

       }

 

       public SqlServer(string connectionString)

       {

           this._con.ConnectionString = connectionString;

           this._cmd.Connection = this._con;

       }

 

       public void Reset()

       {

           this._cmd.Connection = this._con;

           this._cmd.CommandType = CommandType.Text;

           this.SQL = string.Empty;

           this.Parameters.Clear();

       }

 

       public void Open()

       {

           if (this._con.State == ConnectionState.Closed) this._con.Open();

       }

 

       public void Close()

       {

           if (this._con.State == ConnectionState.Open) this._con.Close();

       }

 

       public CommandType CommandType

       {

           get

           {

                return this._cmd.CommandType;

           }

            set

           {

                this._cmd.CommandType = value;

           }

       }

 

       public string SQL

       {

           get

           {

                return this._cmd.CommandText;

           }

           set

           {

                this._cmd.CommandText = value;

           }

       }

 

       public SqlParameterCollection Parameters

       {

           get

           {

                return this._cmd.Parameters;

           }

       }

 

       public string GetDeleteSQL(string tableName, string condition)

       {

           if (!string.IsNullOrEmpty(condition))

           {

                condition = " WHERE "+ condition;

           }

           return string.Format("DELETE FROM {0} {1}", tableName, condition);

       }

       //SELECT TOP 個數 fields FROM tableName WHERE 條件

       public string GetSelectSQL(string table, string condition, int top,string fields, string orderBy)

       {

           #region 不太好的版本

           //string sql = "SELECT ";

           //if (top > 1)

           //{

           //    sql += " TOP " +top.ToString()+" ";

           //}

           //if (!string.IsNullOrEmpty(fields))

           //{

           //    sql += fields;

           //}

           //else

           //{

           //    sql += " * FROM ";

           //}

           //sql += tableName;

           //if (!string.IsNullOrEmpty(condition))

           //{

           //    condition = " WHERE" + condition;

           //}

 

           //sql += condition;

 

           //if (!string.IsNullOrEmpty(orderBy))

           //{

           //    orderBy = " ORDER BY" + orderBy;

           //}

 

           //sql += orderBy;

           #endregion

 

           string t = top > 0 ? "TOP " + top.ToString() :"";

 

           if (string.IsNullOrEmpty(fields))

           {

                fields = "*";

           }

           if (!string.IsNullOrEmpty(condition))

           {

                condition = " WHERE "+ condition;

           }

           if (!string.IsNullOrEmpty(orderBy))

           {

                orderBy = " ORDER BY" + orderBy;

           }

 

           string sql = string.Format("SELECT {0} {1} FROM {2} {3} {4}",t, fields, table, condition, orderBy);

 

           return sql;

 

       }

 

       public string GetSelectSQL(string table, string condition, stringfields, string orderBy)

       {

           return this.GetSelectSQL(table, condition, 0, fields, orderBy);

       }

 

       public string GetGagingSelectSQL(string table, string condition, stringfields, string orderBy)

       {

           string sql = @"SELECT * FROM (

                                          SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum,{1} FROM {2} {3}

                                          ) AST

                           WHERE RowNumBETWEEN  (@CurrentPage-1)*@PageSize+1 AND@CurrentPage*@PageSize ORDER BY {0}";

 

           if (!string.IsNullOrEmpty(condition))

           {

                condition = " WHERE "+ condition;

           }

           if (string.IsNullOrEmpty(fields))

           {

                fields = "*";

           }

 

           return string.Format(sql, orderBy, fields, table, condition);

 

       }

 

       public string GetInsertSQL(string table)

       {

           //INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES(@Sno,@Sname,@Ssex,@Sage,@Sdept)

           string sql = "INSERT INTO {0}({1})VALUES({2})";

           string field = string.Empty;

           string parameter = string.Empty;

           foreach (SqlParameter spr in this.Parameters)

            {

                field +=spr.ParameterName.Remove(0, 1) + ",";

                parameter += spr.ParameterName+ ",";

           }

           field = field.Remove(field.Length - 1, 1);

           parameter = parameter.Remove(parameter.Length - 1, 1);

           return string.Format(sql, table, field, parameter);

       }

 

       public string GetUpdateSQL(string table, string condition)

       {

           //UPDATE Student SET Sno=@Sno,Sname=@Sname WHERE Sno=@Sno AND .....

           string sql = "UPDATE {0} SET {1} {2}";

           string str = string.Empty;

           foreach (SqlParameter spr in this.Parameters)

           {

                str +=spr.ParameterName.Remove(0, 1) + "=" + spr.ParameterName +",";

           }

           str = str.Remove(str.Length - 1,1);

           if (!string.IsNullOrEmpty(condition))

           {

                condition = " WHERE "+ condition;

           }

           return string.Format(sql, table, str, condition);

       }

 

       /// <summary>

       /// 添加SqlParameter，需要5個屬性值

       /// </summary>

       /// <param name="parameterName">參數名</param>

       /// <param name="sqlDbType">參數資料類型</param>

       /// <param name="size">大小</param>

       /// <param name="sourceColumn">源列名</param>

       /// <param name="value">值</param>

       /// <returns>被添加進去的參數對象</returns>

       public SqlParameter AddSqlParameter(string parameterName, SqlDbTypesqlDbType, int size, string sourceColumn, object value)

       {

           SqlParameter spr = new SqlParameter();

 

           spr.ParameterName = parameterName;

           spr.SqlDbType = sqlDbType;

 

           if (size > 0)

           {

                spr.Size = size;

           }

 

           if (!string.IsNullOrEmpty(sourceColumn))

            {

                spr.SourceColumn =sourceColumn;

           }

 

           spr.Value = (value == null) ? DBNull.Value : value;

           return this._cmd.Parameters.Add(spr);

 

       }

 

 

       /// <summary>

       /// 添加SqlParameter，需要4個屬性值

       /// </summary>

       /// <param name="parameterName">參數名</param>

       /// <param name="sqlDbType">參數資料類型</param>

       /// <param name="sourceColumn">源列名</param>

       /// <param name="value">值</param>

       /// <returns>被添加進去的參數對象</returns>

       public SqlParameter AddSqlParameter(string parameterName, SqlDbTypesqlDbType, string sourceColumn, object value)

       {

           return this.AddSqlParameter(parameterName, sqlDbType, 0, sourceColumn,value);

        }

 

       /// <summary>

       /// 添加SqlParameter，需要3個屬性值

       /// </summary>

       /// <param name="parameterName">參數名</param>

       /// <param name="sqlDbType">參數資料類型</param>

       /// <param name="value">值</param>

       /// <returns>被添加進去的參數對象</returns>

       public SqlParameter AddSqlParameter(string parameterName, SqlDbTypesqlDbType, object value)

       {

           return this.AddSqlParameter(parameterName, sqlDbType, null, value);

       }

 

       /// <summary>

       /// 添加SqlParameter，需要2個屬性值

       /// </summary>

       /// <param name="parameterName">參數名</param>

       /// <param name="sqlDbType">參數資料類型</param>

       /// <param name="value">值</param>

       /// <returns>被添加進去的參數對象</returns>

       public SqlParameter AddSqlParameter(string parameterName, object value)

       {

           SqlParameter spr = new SqlParameter();

           spr.ParameterName = parameterName;

           spr.Value = (value == null) ? DBNull.Value : value;

           return this._cmd.Parameters.Add(spr);

       }

 

       public void ClearSqlParameter()

       {

           this._cmd.Parameters.Clear();

       }

 

 

       #region 執行UPDATE、INSERT、DELETE，返回受影響的行數

       public int ExcuteNonQuery()

       {

           try

           {

                this.Open();

                return _cmd.ExecuteNonQuery();

 

           }

           catch (Exception ex)

           {

               throw ex;

           }

           finally

           {

                this.Close();

           }

       }

 

       #endregion

 

       #region  執行SELECT COUNT(*) FROM....類似的SQL語句，返回首行首列

       public object ExcuteScalar()

       {

           try

           {

                this.Open();

 

                return _cmd.ExecuteScalar();

 

           }

           catch (Exception ex)

           {

                throw ex;

           }

           finally

           {

                this.Close();

           }

       }

       #endregion

 

       #region  執行SELECT 屬性列表 FROM....類似的SQL語句，返回首行首列

       public SqlDataReader ExcuteReader()

       {

           try

           {

 

                this.Open();

 

                return _cmd.ExecuteReader();

 

           }

           catch (Exception ex)

           {

                throw ex;

           }

           finally

           {

                // if (con.State ==ConnectionState.Open) con.Close();  //可以使用委託機制來解決資料無法關閉的問題

           }

        }

       #endregion

 

       #region 執行SELECT 屬性列表 FROM....類似的SQL語句，返回一個記憶體中的資料庫

       public DataSet GetDataSet()

       {

           try

           {

 

                this._sda.SelectCommand =this._cmd;

 

                DataSet ds = new DataSet();

                this._sda.Fill(ds);

                return ds;

           }

           catch (Exception ex)

           {

                throw ex;

           }

           finally

           {

 

           }

       }

       #endregion

 

       #region 有缺陷的寫法，返回的資料表不能和其它的資料庫任意進行合并等操作，因為其和ds有關係

       //public DataTable GetDataTable(string SQL)

       //{

       //    DataSet ds =this.GetDataSet(SQL);

       //    return ds.Tables[0];

       //}

       #endregion

 

 

       #region 執行SELECT 屬性列表 FROM....類似的SQL語句，返回一個記憶體中的資料表

       public DataTable GetDataTable()

       {

           try

           {

 

                this._sda.SelectCommand =this._cmd;

 

                DataTable dt = new DataTable();

                this._sda.Fill(dt);

                return dt;

           }

           catch (Exception ex)

           {

                throw ex;

           }

           finally

           {

 

           }

       }

       #endregion

 

 

    }

}