数据库的sql参数封装类的编写
复制代码 代码如下:
usingSystem;
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Web.UI.HtmlControls;
usingSystem.Data.SqlClient;
usingSystem.Text;
namespaceChinaSite.classes
{
publicclassDbAccess
{
SqlConnectionconn=null;
SqlCommandcmd=null;
publicDbAccess()
{
//
//TODO:在此处添加构造函数逻辑
//
conn=newSqlConnection();
//conn.ConnectionString="initialcatalog=pubs;datasource=.;userid=sa;password=";
//conn.ConnectionString=Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
conn.ConnectionString=Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["datasource"]);
cmd=newSqlCommand();
cmd.Connection=conn;
}
///<summary>
///获取数据根据sql语句
///</summary>
///<paramname="sql"></param>
///<returns></returns>
publicDataTableGetTable(stringsql)
{
DataSetds=newDataSet();
try
{
cmd.CommandText=sql;
SqlDataAdapterda=newSqlDataAdapter();
da.SelectCommand=cmd;
da.Fill(ds);
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnnull;
}
returnds.Tables[0]??newDataTable();
}
///<summary>
///获取数据根据sql语句带参数的
///</summary>
///<paramname="sql"></param>
///<paramname="pas"></param>
///<returns></returns>
publicDataTableGetTable(stringsql,paramsSqlParameter[]pas)
{
DataSetds=newDataSet();
try
{
cmd.CommandText=sql;
SqlDataAdapterda=newSqlDataAdapter();
da.SelectCommand=cmd;
cmd.Parameters.Clear();
foreach(SqlParametertemppainpas)
{
cmd.Parameters.Add(temppa);
}
da.Fill(ds);
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnnull;
}
returnds.Tables[0]??newDataTable();
}
///<summary>
///根据sql语句返回跟新状态
///</summary>
///<paramname="sql"></param>
///<returns></returns>
publicboolGetState(stringsql)
{
boolsucc=false;
try
{
cmd.CommandText=sql;
conn.Open();
succ=cmd.ExecuteNonQuery()>0?(true):(false);
conn.Close();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnfalse;
}
returnsucc;
}
///<summary>
///根据sql语句返回跟新状态带参数的
///</summary>
///<paramname="sql">sql语句</param>
///<paramname="pas">参数的集合</param>
///<returns></returns>
publicboolGetState(stringsql,paramsSqlParameter[]pas)
{
boolsucc=false;
try
{
cmd.CommandText=sql;
cmd.Parameters.Clear();
foreach(SqlParametertemppainpas)
{
cmd.Parameters.Add(temppa);
}
conn.Open();
succ=cmd.ExecuteNonQuery()>0?(true):(false);
conn.Close();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnfalse;
}
returnsucc;
}
///<summary>
///根据sql语句返回第一个单元格的数据
///</summary>
///<paramname="sql"></param>
///<returns></returns>
publicstringGetOne(stringsql)
{
stringres="";
try
{
cmd.CommandText=sql;
conn.Open();
res=cmd.ExecuteScalar()==null?(""):(Convert.ToString(cmd.ExecuteScalar()));
conn.Close();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnnull;
}
returnres;
}
///<summary>
///根据sql语句返回第一个单元格的数据带参数的
///</summary>
///<paramname="sql"></param>
///<paramname="pas"></param>
///<returns></returns>
publicstringGetOne(stringsql,paramsSqlParameter[]pas)
{
stringres="";
try
{
cmd.CommandText=sql;
cmd.Parameters.Clear();
foreach(SqlParametertemppainpas)
{
cmd.Parameters.Add(temppa);
}
conn.Open();
res=cmd.ExecuteScalar()==null?(""):(Convert.ToString(cmd.ExecuteScalar()));
conn.Close();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnnull;
}
returnres;
}
///<summary>
///返回数据的DataReader
///</summary>
///<paramname="sql"></param>
///<returns></returns>
publicSqlDataReaderGetDataReader(stringsql)
{
SqlDataReaderdr=null;
try
{
conn.Open();
cmd.CommandText=sql;
dr=cmd.ExecuteReader();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnnull;
}
returndr;
}
///<summary>
///返回数据的DataReader带参数的
///</summary>
///<paramname="sql"></param>
///<paramname="pas"></param>
///<returns></returns>
publicSqlDataReaderGetDataReader(stringsql,paramsSqlParameter[]pas)
{
SqlDataReaderdr=null;
try
{
conn.Open();
cmd.Parameters.Clear();
foreach(SqlParametertemppainpas)
{
cmd.Parameters.Add(temppa);
}
cmd.CommandText=sql;
dr=cmd.ExecuteReader();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
returnnull;
}
returndr;
}
///<summary>
///打开连接
///</summary>
publicvoidOpenConn()
{
if(conn.State!=ConnectionState.Open)
{
try
{
conn.Open();
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
return;
}
}
}
///<summary>
///关闭连接
///</summary>
publicvoidCloseConn()
{
if(conn.State!=ConnectionState.Closed)
{
try
{
conn.Close();
cmd=null;
conn=null;
}
catch(Exceptionex)
{
this.ShowError(ex.Message);
return;
}
}
}
///<summary>
///弹出错误的信息
///</summary>
///<paramname="err"></param>
publicvoidShowError(stringerr)
{
System.Web.HttpContext.Current.Response.Write(Script(err,""));
}
///<summary>
///显示信息
///</summary>
///<paramname="err"></param>
publicvoidShowMessage(stringmes,stringloc)
{
System.Web.HttpContext.Current.Response.Write(Script(mes,loc));
}
///<summary>
///javascript脚本
///</summary>
///<paramname="mess"></param>
///<paramname="loc"></param>
///<returns></returns>
publicstringScript(stringmess,stringloc)
{
StringBuildersb=newStringBuilder();
sb.Append("<scriptlanguage='javascript'>");
sb.Append("alter('");
sb.Append(mess);
sb.Append("');");
sb.Append(loc);
sb.Append("</script>");
returnsb.ToString();
}
}
}