三层架构之数据库访问层完全篇(C#)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DbBase
... {
publicabstractclassBase
...{
"Fieldsofbasecalss"#region"Fieldsofbasecalss"
protectedstaticstringstrConn=ConfigurationSettings.AppSettings["strConnection"];
protectedstaticstringstrSQL;
#endregion
"Propertiesofbaseclass"#region"Propertiesofbaseclass"
}
#endregion
"Functionsofbaseclass"#region"Functionsofbaseclass"
publicBase()
...{
//
//TODO:Addconstructorlogichere
//
}
/**////<summary>
///executingSQLcommands
///</summary>
///<paramname="strSQL">string</param>
///<returns>returnint</returns>
protectedstaticintExecuteSql(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
myCmd.ExecuteNonQuery();
return0;
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///executingSQLcommands
///</summary>
///<paramname="strSQL">要执行的SQL语句,为字符串类型string</param>
///<returns>返回执行情况,整形int</returns>
protectedstaticintExecuteSqlEx(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
SqlDataReadermyReader=myCmd.ExecuteReader();
if(myReader.Read())
...{
return0;
}
else
...{
thrownewException("ValueUnavailable!");
}
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///getdataset
///</summary>
///<paramname="strSQL">(string)</param>
///<returns>(DataSet)</returns>
protectedstaticDataSetExecuteSql4Ds(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
try
...{
myCn.Open();
SqlDataAdaptersda=newSqlDataAdapter(strSQL,myCn);
DataSetds=newDataSet("ds");
sda.Fill(ds);
returnds;
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCn.Close();
}
}
/**////<summary>
///getsinglevalue
///</summary>
///<paramname="strSQL">(string)</param>
///<returns>(int)</returns>
protectedstaticintExecuteSql4Value(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
objectr=myCmd.ExecuteScalar();
if(Object.Equals(r,null))
...{
thrownewException("valueunavailable!");
}
else
...{
return(int)r;
}
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///getobject
///</summary>
///<paramname="strSQL">(string)</param>
///<returns>(object)</returns>
protectedstaticobjectExecuteSql4ValueEx(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
objectr=myCmd.ExecuteScalar();
if(Object.Equals(r,null))
...{
thrownewException("objectunavailable!");
}
else
...{
returnr;
}
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///executemultipulSQLcommands
///</summary>
///<paramname="strSQLs">string</param>
///<returns>int</returns>
protectedstaticintExecuteSqls(string[]strSQLs)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand();
intj=strSQLs.Length;
try
...{
myCn.Open();
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
SqlTransactionmyTrans=myCn.BeginTransaction();
try
...{
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DbBase
... {
publicabstractclassBase
...{
"Fieldsofbasecalss"#region"Fieldsofbasecalss"
protectedstaticstringstrConn=ConfigurationSettings.AppSettings["strConnection"];
protectedstaticstringstrSQL;
#endregion
"Propertiesofbaseclass"#region"Propertiesofbaseclass"
}
#endregion
"Functionsofbaseclass"#region"Functionsofbaseclass"
publicBase()
...{
//
//TODO:Addconstructorlogichere
//
}
/**////<summary>
///executingSQLcommands
///</summary>
///<paramname="strSQL">string</param>
///<returns>returnint</returns>
protectedstaticintExecuteSql(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
myCmd.ExecuteNonQuery();
return0;
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///executingSQLcommands
///</summary>
///<paramname="strSQL">要执行的SQL语句,为字符串类型string</param>
///<returns>返回执行情况,整形int</returns>
protectedstaticintExecuteSqlEx(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
SqlDataReadermyReader=myCmd.ExecuteReader();
if(myReader.Read())
...{
return0;
}
else
...{
thrownewException("ValueUnavailable!");
}
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///getdataset
///</summary>
///<paramname="strSQL">(string)</param>
///<returns>(DataSet)</returns>
protectedstaticDataSetExecuteSql4Ds(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
try
...{
myCn.Open();
SqlDataAdaptersda=newSqlDataAdapter(strSQL,myCn);
DataSetds=newDataSet("ds");
sda.Fill(ds);
returnds;
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCn.Close();
}
}
/**////<summary>
///getsinglevalue
///</summary>
///<paramname="strSQL">(string)</param>
///<returns>(int)</returns>
protectedstaticintExecuteSql4Value(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
objectr=myCmd.ExecuteScalar();
if(Object.Equals(r,null))
...{
thrownewException("valueunavailable!");
}
else
...{
return(int)r;
}
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///getobject
///</summary>
///<paramname="strSQL">(string)</param>
///<returns>(object)</returns>
protectedstaticobjectExecuteSql4ValueEx(stringstrSQL)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand(strSQL,myCn);
try
...{
myCn.Open();
objectr=myCmd.ExecuteScalar();
if(Object.Equals(r,null))
...{
thrownewException("objectunavailable!");
}
else
...{
returnr;
}
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
finally
...{
myCmd.Dispose();
myCn.Close();
}
}
/**////<summary>
///executemultipulSQLcommands
///</summary>
///<paramname="strSQLs">string</param>
///<returns>int</returns>
protectedstaticintExecuteSqls(string[]strSQLs)
...{
SqlConnectionmyCn=newSqlConnection(strConn);
SqlCommandmyCmd=newSqlCommand();
intj=strSQLs.Length;
try
...{
myCn.Open();
}
catch(System.Data.SqlClient.SqlExceptione)
...{
thrownewException(e.Message);
}
SqlTransactionmyTrans=myCn.BeginTransaction();
try
...{