C#(.NET)資料訪問串連、查詢、插入等操作的封裝類

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Public 的摘要說明
/// </summary>
public class PublicClass
{ //定義一個公用成員
public SqlConnection conn;

public PublicClass()
{
//
// TODO: 在此處添加建構函式邏輯
//
}
#region 建立資料庫連接
public void OpenConn()
{
String strconn = System.Configuration.ConfigurationManager.AppSettings["sqlconn"].ToString();
conn = new SqlConnection(strconn);
if (conn.State.ToString().ToLower() == "open")
{
//串連為開啟時
}
else
{
//串連為關閉時
conn.Open();
}
}
#endregion
#region 關閉並釋放串連
public void CloseConn()
{
if (conn.State.ToString().ToLower() == "open")
{
//串連為開啟時
conn.Close();
conn.Dispose();
}
}
#endregion
#region 返回DataReader，用於讀取資料
public SqlDataReader DataRead(string sql)
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
#endregion
#region 返回一個資料集
public DataSet MySqlDataSet(string Sql, string tableName)
{
OpenConn();
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter(Sql, conn);
da.Fill(ds, tableName);
CloseConn();
return ds;
}
#endregion
//返回一個資料集
public DataView MySqlDataSource(string Sql)
{
OpenConn();
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter(Sql, conn);
da.Fill(ds, "temp");
CloseConn();
return ds.Tables[0].DefaultView;
}
#region 執行一個SQL操作:添加、刪除、更新操作

//執行一個SQL操作:添加、刪除、更新操作
public void MySqlExcute(string sql)
{
OpenConn();
SqlCommand cmd;
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
CloseConn();
}
#endregion
#region 執行一個SQL操作:添加、刪除、更新操作，返回受影響的行
//執行一個SQL操作:添加、刪除、更新操作，返回受影響的行
public int MySqlExecuteNonQuery(string sql)
{
OpenConn();
SqlCommand cmd;
cmd = new SqlCommand(sql, conn);
int flag = cmd.ExecuteNonQuery();
return flag;
}
#endregion

public object MySqlExecuteScalar(string sql)
{
OpenConn();
SqlCommand cmd;
cmd = new SqlCommand(sql, conn);
object obj = cmd.ExecuteScalar();
cmd.Dispose();
CloseConn();
return obj;
}

/// <summary>
/// 返回DataTable對象
/// </summary>
/// <param name="sql">sql語句</param>
/// <returns></returns>
public DataTable MySqlDataTable(string sql)
{
OpenConn();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(ds, "table");
CloseConn();
return ds.Tables["table"];
}

/// <summary>
/// 返回一個資料集的記錄數
/// </summary>
/// <param name="sql">傳遞的sql語句必須為一個統計查詢</param>
/// <returns></returns>
public int MySqlRecordCount(string sql)
{
//註：Sql 語句必須是一個統計查詢
OpenConn();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
SqlDataReader dr;
dr = cmd.ExecuteReader();
int RecordCount = -1;
while (dr.Read())
{
RecordCount = int.Parse(dr[0].ToString());
}
CloseConn();
return RecordCount;
}

/// <summary>
/// 自訂的功能警告
/// </summary>
/// <param name="str">彈出資訊框內容</param>
public void SetAlert(string str)
{
HttpContext.Current.Response.Write("<script language='JavaScript' type='text/JavaScript'>alert('" + str + "');</script>");

}
//返回上一頁
public void AddErro(string message)
{
HttpContext.Current.Response.Write("<script>alert('" + message + "');history.back(-1);</script>");
}

//關閉視窗
public void SetCloseWindow()
{
HttpContext.Current.Response.Write("<script language='JavaScript' type='text/JavaScript'>window.close();</script>");
}

/// <summary>
/// 地址跳轉
/// </summary>
/// <param name="str">跳轉地址</param>
public void SetLocation(string str)
{
HttpContext.Current.Response.Write("<script language='JavaScript' type='text/JavaScript'>location='" + str + "';</script>");
}

public string AjaxSetAlert(string str)
{
return "<script language='JavaScript' type='text/JavaScript'>alert('" + str + "');</script>";
}

//過濾非法字元
public string FilterStr(string Str)
{
Str = Str.Trim();
Str = Str.Replace("*", "");
Str = Str.Replace("=", "");
Str = Str.Replace("/", "");
Str = Str.Replace("$", "");
Str = Str.Replace("#", "");
Str = Str.Replace("@", "");
Str = Str.Replace("&", "");
return Str;
}

//Md5密碼編譯演算法
public string md5(string str)
{
return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "md5").ToLower().Substring(0, 12);
}
public string RndNum(int VcodeNum)
{
string Vchar = "0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,P,Q,R,S,T,U,W,X";
string[] VcArray = Vchar.Split(new Char[] { ',' }); //將字串產生數組
string VNum = "";
int temp = -1;

Random rand = new Random();

for (int i = 1; i < VcodeNum + 1; i++)
{
if (temp != -1)
{
rand = new Random(i * temp * unchecked((int)DateTime.Now.Ticks));
}

int t = rand.Next(31); //數組一般從0開始讀取，所以這裡為31*Rnd
if (temp != -1 && temp == t)
{
return RndNum(VcodeNum);
}
temp = t;
VNum += VcArray[t];
}
return VNum;
}
}