三层架构之数据库访问层(doc 6页).doc

三层架构之数据库访问层完全篇(C#)2008-07-15 15:11using System;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace DbBasepublic abstract class Base #region Fields of base calss protected static string strConn = ConfigurationSettings.AppSettingsstrConnection; protected static string strSQL; #endregion #region Properties of base class #endregion #region Functions of base class public Base() / / TODO: Add constructor logic here / / / executing SQL commands / / 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(); / /executing SQL commands / / 要执行的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(); / / get dataset / / (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(); / / get single value / / (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(); / / get object / / (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(); / / execute multipul SQL commands / / 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