?? dataaccess.cs
字號:
using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Xml;
namespace CJGL
{
/// <summary>
/// 一個輕量級的數據庫操作類，可用于簡單的分層開發。
/// 數據庫連接配置在WEB.config中ConnectionString配置節。
/// 程序作者：孤獨俠客 QQ：229014536 Http://loners.yer.cn
/// </summary>
public class DataAccess
{
#region 構造函數
///<summary>
///構造函數
///</summary>
public DataAccess()
{
}
#endregion
#region 配置數據庫連接字符串
/// <summary>
/// 配置數據庫連接字符串
/// </summary>
public static string ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];
#endregion
#region 執行SQL語句，返回Bool值
/// <summary>
/// 執行SQL語句，返回Bool值
/// </summary>
/// <param name="sql">要執行的SQL語句</param>
/// <returns>返回BOOL值，True為執行成功</returns>
public bool ExecuteSQL(string sql)
{
SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
SqlCommand cmd=new SqlCommand(sql,con);
try
{
con.Open();
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
}
#endregion
#region 執行SQL語句，返回SqlDataReader
/// <summary>
/// 執行SQL語句，返回SqlDataReader
/// </summary>
/// <param name="sql">要執行的SQL語句</param>
/// <returns>返回SqlDataReader，需手工關閉連接</returns>
public SqlDataReader GetReader(string sql)
{
SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
SqlCommand cmd=new SqlCommand(sql,con);
SqlDataReader dr=null;
try
{
con.Open();
dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
dr.Close();
con.Dispose();
cmd.Dispose();
throw new Exception(ex.ToString());
}
return dr;
}
#endregion
#region 分頁，返回SqlDataReader
/// <summary>
/// 分頁，返回SqlDataReader
/// </summary>
/// <param name="tblName">查詢的表名</param>
/// <param name="fldName">排序字段名</param>
/// <param name="PageSize">每頁中記錄的數量</param>
/// <param name="PageIndex">當前查詢的頁碼</param>
/// <param name="OrderType">設置排序類型, 非 0 值則降序</param>
/// <param name="strWhere">查詢條件(注意:不要加 where)</param>
/// <returns>返回SqlDataReader，需手工關閉連接</returns>
public SqlDataReader GetReaderPage(string tblName,string fldName,int PageSize,int PageIndex,int OrderType,string strWhere)
{
string strTmp,strOrder;
string sql="";
if (OrderType!=0)
{
strTmp="< (select min";
strOrder=" order by " + fldName +" desc";
}
else
{
strTmp=">(select max";
strOrder=" order by " + fldName +" asc";
}
if (strWhere!="")
{
sql = "select top " + PageSize + " * from "+ tblName + " where " + fldName + strTmp + "(";
sql+=fldName + ") from (select top " + (PageIndex-1)*PageSize +" "+ fldName + " from " + tblName + " where (" + strWhere + ") ";
sql+=strOrder + ") as tblTmp) and (" + strWhere + ") " + strOrder;
}
if (PageIndex==1)
{
strTmp ="";
if (strWhere != "")
{
strTmp = " where (" + strWhere + ")";
}
sql = "select top " + PageSize + " * from " + tblName + strTmp + " " + strOrder;
}
SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
SqlCommand cmd=new SqlCommand(sql,con);
SqlDataReader dr=null;
try
{
con.Open();
dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
dr.Close();
con.Dispose();
cmd.Dispose();
throw new Exception(ex.ToString());
}
return dr;
}
#endregion
#region 執行SQL語句，返回DataSet
/// <summary>
/// 執行SQL語句，返回DataSet
/// </summary>
/// <param name="sql">要執行的SQL語句</param>
/// <param name="tablename">DataSet中要填充的表名</param>
/// <returns>返回dataSet類型的執行結果</returns>
public DataSet GetDataSet(string sql,string tablename)
{
DataSet ds= new DataSet();
SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
SqlDataAdapter da=new SqlDataAdapter(sql,con);
try
{
da.Fill(ds,tablename);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
}
return ds;
}
#endregion
#region 執行SQL語句，返回DataTable
/// <summary>
/// 執行SQL語句，返回DataTable
/// </summary>
/// <param name="sql">要執行的SQL語句</param>
/// <returns>返回DataTable類型的執行結果</returns>
public DataTable GetDataTable(string sql)
{
DataSet ds= new DataSet();
SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
SqlDataAdapter da=new SqlDataAdapter(sql,con);
try
{
da.Fill(ds,"tb");
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
}
DataTable result=ds.Tables["tb"];
return result;
}
#endregion
#region 執行SQL語句并返回受影響的行數
/// <summary>
/// 執行SQL語句并返回受影響的行數
/// </summary>
/// <param name="sql">要執行的SQL語句</param>
/// <returns>返回Int類型的受影響的行數</returns>
public int GetCount(string sql)
{
SqlConnection con=new SqlConnection(DataAccess.ConnectionString);
SqlCommand cmd=new SqlCommand(sql,con);
try
{
con.Open();
int count=(int)cmd.ExecuteScalar();
return count;
}
catch
{
return 0;
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
}
#endregion
#region 驗證用戶是否合法管理員
/// <summary>
/// 驗證用戶是否合法管理員
/// </summary>
/// <param name="strname">要驗證的用戶名</param>
/// <param name="strpwd">要驗證的密碼</param>
/// <returns>返回BOOL值，True為驗證成功</returns>
public bool CheckAdmin(string strname,string strpwd)
{
string sql;
strname=NameReplace(strname);
sql="select count(1) from administrator where username='"+ strname +"' and password='"+ strpwd +"'";
if(GetCount(sql)>0)
{
return true;
}
else
{
return false;
}
}
#endregion
#region 驗證用戶是否合法用戶
/// <summary>
/// 驗證用戶是否合法用戶
/// </summary>
/// <param name="strname">要驗證的用戶名</param>
/// <param name="strpwd">要驗證的密碼</param>
/// <returns>返回BOOL值，True為驗證成功</returns>
public bool CheckUser (string strname,string strpwd)
{
string sql;
strname=NameReplace(strname);
sql="select count(1) from student where stuid='"+strname+"'and stupassword='"+strpwd+"'";
if(GetCount(sql)>0)
{
return true;
}
else
{
return false;
}
}
#endregion
#region 注冊新的用戶
/// <summary>
/// 注冊新的用戶
/// </summary>
/// <param name="strname">要注冊的用戶名</param>
/// <param name="strpwd">要注冊的密碼</param>
/// <returns>返回BOOL值，True為驗證成功</returns>
public bool newUser (string stuid,string username,string sex,string major,string tel,string address,string btime,string password)
{
string sql;
username=NameReplace(username);
sql="select count(1) from student where stuid='"+stuid+"'";
if(GetCount(sql)>0)
{
return false;
}
string sql2;
sql2="insert into student (stuid,name,sex,major,tel,address,btime,stupassword) values('"+stuid +"','"+username+"','"+sex+"','"+major+"','"+tel+"','"+address+"','"+btime+"','"+password+"')";
ExecuteSQL(sql2);
return true;
}
#endregion
#region 過濾用戶名中的非法字符
/// <summary>
/// 過濾用戶名中的非法字符
/// </summary>
/// <param name="str">要被過濾的字符串</param>
/// <returns>返回String類型的過濾后的字符串</returns>
public string NameReplace(string str)
{
str=str.Trim();
str=str.Replace("=","");
str=str.Replace("'","");
return str;
}
#endregion
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -