三层架构之数据库访问层完全篇

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 // }

///

/// string /// return int

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(); } }

///

/// 要执行的SQL语句,为字符串类型string

/// 返回执行情况,整形int

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(); } }

///

/// (string) /// (DataSet)

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(); } }

///

/// (string) /// (int)

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(); } }

///

/// (string) /// (object)

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(); } }

///

/// string /// int

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 } }