Asp.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("<mce:script language='JavaScript' type='text/JavaScript'><!--

alert('" + str + "');

// --></mce:script>");

}

//傳回上一頁

public void AddErro(string message)

{

HttpContext.Current.Response.Write("<mce:script type="text/javascript"><!--

alert('" + message + "');history.back(-1);

// --></mce:script>");

}

//關閉視窗

public void SetCloseWindow()

{

HttpContext.Current.Response.Write("<mce:script language='JavaScript' type='text/JavaScript'><!--

window.close();

// --></mce:script>");

}

/// <summary>

/// 位址跳轉

/// </summary>

/// <param name="str">跳轉位址</param>

public void SetLocation(string str)

{

HttpContext.Current.Response.Write("<mce:script language='JavaScript' type='text/JavaScript'><!--

location='" + str + "';

// --></mce:script>");

}

public string AjaxSetAlert(string str)

{

return "<mce:script language='JavaScript' type='text/JavaScript'><!--

alert('" + str + "');

// --></mce: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;

}

}