SQL SERVER數(shù)據(jù)操作類代碼

using System;
using System.Data;
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;
using System.Data.SqlClient;
/// summary>
/// dataOperate 的摘要說(shuō)明 愛智旮旯
/// /summary>
public class dataOperate
{
public dataOperate()
{
//
// TODO: 在此處添加構(gòu)造函數(shù)邏輯
//
}
/// summary>
/// 創(chuàng)建數(shù)據(jù)庫(kù)連接方法
/// /summary>
/// returns>返回SqlConnection對(duì)象/returns>
public static SqlConnection createCon()
{
SqlConnection con = new SqlConnection("server=.;database=db_message;uid=sa;pwd=;");
return con;
}
/// summary>
/// 執(zhí)行SQL語(yǔ)句方法包括刪除，插入，更新
/// /summary>
/// param name="sql">需要執(zhí)行的SQL語(yǔ)句/param>
/// returns>返回一個(gè)布爾值該值表示是否執(zhí)行成功/returns>
public static bool execSql(string sql)
{
//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlConnection con = createCon();
//打開數(shù)據(jù)庫(kù)連接
con.Open();
//創(chuàng)建SqlCommand對(duì)象
SqlCommand com = new SqlCommand(sql, con);
//判斷SQL語(yǔ)句是否執(zhí)行成功
if (com.ExecuteNonQuery() > 0)
{
return true;
}
else
{
return false;
}
}
/// summary>
/// 查詢數(shù)據(jù)方法，并返回一個(gè)DataSet對(duì)象
/// /summary>
/// param name="sql">執(zhí)行的SQL語(yǔ)句/param>
/// returns>返回的DataSet對(duì)象/returns>
public static DataSet getRows(string sql)
{
//創(chuàng)建DataSet對(duì)象
DataSet ds;
//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlConnection con = createCon();
//打開數(shù)據(jù)庫(kù)連接
con.Open();
//創(chuàng)建SqlDataAdapter對(duì)象
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
//實(shí)例DataSet對(duì)象
ds = new DataSet();
//填充DataSet對(duì)象
sda.Fill(ds);
//關(guān)閉數(shù)據(jù)庫(kù)連接
con.Close();
return ds;
}
/// summary>
/// 查詢數(shù)據(jù)是否存在的方法
/// /summary>
/// param name="sql">需要執(zhí)行的SQL語(yǔ)句/param>
/// returns>返回一個(gè)布爾值當(dāng)數(shù)據(jù)存在返回True否則返回False/returns>
public static bool isName(string sql)
{
//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlConnection con = createCon();
//打開數(shù)據(jù)庫(kù)連接
con.Open();
//創(chuàng)建SqlCommand對(duì)象
SqlCommand com = new SqlCommand(sql, con);
//判斷數(shù)據(jù)是否存在并返回相應(yīng)的布爾值
if (Convert.ToInt32(com.ExecuteScalar()) > 0)
{
return true;
}
else
{
return false;
}
}
/// summary>
/// 返回需要統(tǒng)計(jì)數(shù)據(jù)的結(jié)果
/// /summary>
/// param name="sql">需要查詢的SQL語(yǔ)句/param>
/// returns>返回整型變量，表示統(tǒng)計(jì)后的結(jié)果/returns>
public static int countData(string sql)
{
//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlConnection con = createCon();
//打開數(shù)據(jù)庫(kù)連接
con.Open();
//創(chuàng)建SqlCommand對(duì)象
SqlCommand com = new SqlCommand(sql, con);
//返回查詢的結(jié)果
return Convert.ToInt32(com.ExecuteScalar());
}
/// summary>
/// 實(shí)現(xiàn)用戶登錄方法，該方法可以防止SQL注入式攻擊
/// /summary>
/// param name="sql">用來(lái)執(zhí)行的SQL語(yǔ)句/param>
/// param name="name">用戶登錄名/param>
/// param name="pass">用戶密碼/param>
/// returns>返回一個(gè)布爾值，該值表示登錄是否成功/returns>
public static bool enter(string sql, string name, string pass)
{
//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlConnection con = createCon();
//打開數(shù)據(jù)庫(kù)連接
con.Open();
//創(chuàng)建SqlCommand對(duì)象
SqlCommand com = new SqlCommand(sql, con);
//設(shè)置參數(shù)的類型
com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 20));
//設(shè)置參數(shù)值
com.Parameters["@name"].Value = name;
com.Parameters.Add(new SqlParameter("@pass", SqlDbType.VarChar, 20));
com.Parameters["@pass"].Value = pass;
//判斷是否執(zhí)行成功
if (Convert.ToInt32(com.ExecuteScalar()) > 0)
{
return true;
}
else
{
return false;
}
}
/// summary>
/// 查詢數(shù)據(jù)方法，該方法返回一個(gè)SqlDataReader對(duì)象
/// /summary>
/// param name="sql">執(zhí)行SQL語(yǔ)句的方法/param>
/// returns>返回一個(gè)SqlDataReader對(duì)象/returns>
public static SqlDataReader getRow(string sql)
{
//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlConnection con = createCon();
//打開數(shù)據(jù)庫(kù)連接
con.Open();
//創(chuàng)建SqlCommand對(duì)象
SqlCommand com = new SqlCommand(sql, con);
//獲取ExecuteReader返回的SqlDataReader對(duì)象
SqlDataReader sdr = com.ExecuteReader();
return sdr;
}
/// summary>
/// 設(shè)置時(shí)間的顯示樣式
/// /summary>
/// param name="str">表示需要顯示的時(shí)間/param>
/// returns>返回修改后的時(shí)間樣式/returns>
public static string strDate(DateTime str)
{
//設(shè)置時(shí)間的顯示樣式
return str.ToLongDateString() + str.Hour + "小時(shí)" + str.Minute + "分" + str.Second + "秒";
}
/// summary>
/// 過(guò)濾字符方法
/// /summary>
/// param name="str">需要過(guò)濾的字符串/param>
/// returns>返回過(guò)濾后的字符串/returns>
public static string filtrateHtml(string str)
{
str = str.Trim();
str = str.Replace("'", "quot;");
str = str.Replace("", "lt;");
str = str.Replace(">", "gt;");
str = str.Replace(" ", "nbsp;");
str = str.Replace("\n", "br>");
return str;
}
/// summary>
/// 恢復(fù)字符串
/// /summary>
/// param name="str">需要恢復(fù)的字符串/param>
/// returns>返回恢復(fù)后的字符串/returns>
public static string resumeHtml(string str)
{
str = str.Trim();
str = str.Replace("quot;", "'");
str = str.Replace("lt;", "");
str = str.Replace("gt;", ">");
str = str.Replace("nbsp;", " ");
str = str.Replace("br>", "\n");
return str;
}
}