三层架构之数据库访问层完全篇(C#)
2024-07-21 02:18:05
供稿：网友
using system;
using system.data;
using system.data.sqlclient;
using system.configuration;
namespace dbbase
{
public abstract class base
{
#region "fields of base calss"
protected static string strconn = configurationsettings.appsettings["strconnection"];
protected static string strsql;
#endregion
#region "properties of base class"
}
#endregion
#region "functions of base class"
public base()
{
//
// todo: add constructor logic here
//
}
/// <summary>
/// executing sql commands
/// </summary>
/// <param name="strsql">string</param>
/// <returns>return int</returns>
protected static int executesql(string strsql)
{
sqlconnection mycn = new sqlconnection(strconn);
sqlcommand mycmd = new sqlcommand(strsql,mycn);
try
{
mycn.open();
mycmd.executenonquery();
return 0;
}
catch(system.data.sqlclient.sqlexception e)
{
throw new exception(e.message);
}
finally
{
mycmd.dispose();
mycn.close();
}
}
/// <summary>
///executing sql commands
/// </summary>
/// <param name="strsql">要执行的sql语句,为字符串类型string</param>
/// <returns>返回执行情况,整形int</returns>
protected static int executesqlex(string strsql)
{
sqlconnection mycn = new sqlconnection(strconn);
sqlcommand mycmd = new sqlcommand(strsql,mycn);
try
{
mycn.open();
sqldatareader myreader = mycmd.executereader();
if(myreader.read())
{
return 0;
}
else
{
throw new exception("value unavailable!");
}
}
catch(system.data.sqlclient.sqlexception e)
{
throw new exception(e.message);
}
finally
{
mycmd.dispose();
mycn.close();
}
}
/// <summary>
/// get dataset
/// </summary>
/// <param name="strsql">(string)</param>
/// <returns>(dataset)</returns>
protected static dataset executesql4ds(string strsql)
{
sqlconnection mycn = new sqlconnection(strconn);
try
{
mycn.open();
sqldataadapter sda = new sqldataadapter(strsql,mycn);
dataset ds = new dataset("ds");
sda.fill(ds);
return ds;
}
catch(system.data.sqlclient.sqlexception e)
{
throw new exception(e.message);
}
finally
{
mycn.close();
}
}
/// <summary>
/// get single value
/// </summary>
/// <param name="strsql">(string)</param>
/// <returns>(int)</returns>
protected static int executesql4value(string strsql)
{
sqlconnection mycn = new sqlconnection(strconn);
sqlcommand mycmd = new sqlcommand(strsql,mycn);
try
{
mycn.open();
object r = mycmd.executescalar();
if(object.equals(r,null))
{
throw new exception("value unavailable！");
}
else
{
return (int)r;
}
}
catch(system.data.sqlclient.sqlexception e)
{
throw new exception(e.message);
}
finally
{
mycmd.dispose();
mycn.close();
}
}
/// <summary>
/// get object
/// </summary>
/// <param name="strsql">(string)</param>
/// <returns>(object)</returns>
protected static object executesql4valueex(string strsql)
{
sqlconnection mycn = new sqlconnection(strconn);
sqlcommand mycmd = new sqlcommand(strsql,mycn);
try
{
mycn.open();
object r = mycmd.executescalar();
if(object.equals(r,null))
{
throw new exception("object unavailable!");
}
else
{
return r;
}
}
catch(system.data.sqlclient.sqlexception e)
{
throw new exception(e.message);
}
finally
{
mycmd.dispose();
mycn.close();
}
}
/// <summary>
/// execute multipul sql commands
/// </summary>
/// <param name="strsqls">string</param>
/// <returns>int</returns>
protected static int executesqls(string[] strsqls)
{
sqlconnection mycn = new sqlconnection(strconn);
sqlcommand mycmd = new sqlcommand();
int j=strsqls.length;
try
{
mycn.open();
}
catch(system.data.sqlclient.sqlexception e)
{
throw new exception(e.message);
}
sqltransaction mytrans = mycn.begintransaction();
try
{
mycmd.connection = mycn;
mycmd.transaction = mytrans;
foreach(string str in strsqls)
{
mycmd.commandtext = str;
mycmd.executenonquery();
}
mytrans.commit();
return 0;
}
catch(system.data.sqlclient.sqlexception e)
{
mytrans.rollback();
throw new exception(e.message);
}
finally
{
mycmd.dispose();
mycn.close();
}
}
#endregion
}
}
