數據訪問層，即DataAccess層。

下面放送一些奶酪；

?

實例1

?

using System; using System.Collections.Generic; using System.Linq; using System.Text; using Com.ERuipan.DataAccessLirary; using System.Data; using System.Data.SqlClient; using System.Configuration; using Npgsql; using System.Web;namespace XXXXXXXXServiceLibrary {public class DataAccess{public static string GetConn(){XmlControl xc = new XmlControl(System.Web.HttpContext.Current.Server.MapPath("~/web.config"), false, "configuration");DataView dv = xc.GetData("configuration/appSettings");return dv[0][1].ToString();}public static string pgConnStr = GetConn(); //"Server=192.168.1.200;Port=5432;User Id=postgres;Password=admin110;Database=xxxx";public DataAccess(){}public DataTable GetDataTable(string sql){DataSet ds = XXXXDal.Selcet_tables(DatabaseType.PG, pgConnStr, sql);if (ds != null && ds.Tables.Count > 0)return ds.Tables[0];elsereturn new DataTable();}public DataTable GetDataTable(string commandText, int start, int maxRecord){var dt = new DataTable();using (var connection = new NpgsqlConnection(pgConnStr)){connection.Open();new NpgsqlDataAdapter(commandText, connection).Fill(start, maxRecord, dt);}return dt;}public DataSet GetDataSet(string sql){return ERuiPanDal.Selcet_tables(DatabaseType.PG, pgConnStr, sql);}public string GetJsonData(string sql){return GetJsonData(sql, "data");}public string GetJsonData(string sql, string name){return FormatTool.ToJson(GetDataTable(sql), name);}public string GetJsonData(DataTable dt, string name){return FormatTool.ToJson(dt, name);}public string GetJsonData(DataView dv, string name){return FormatTool.ToJson(dv, name);}public int ExcuteNonQuery(string sql){return ExcuteNonQuery(sql, null);}public int ExcuteNonQuery(string sql, params SqlParameter[] commandParameters){IDbConnection pgConn = null;try{pgConn = XXXXDal.CreateConnection(pgConnStr, DatabaseType.PG);return XXXXDal.ExcuteNonQuery(DatabaseType.PG, pgConn, sql, commandParameters);}catch{return -1;}finally {pgConn.Close();pgConn.Dispose();pgConn = null;}}public int ExcuteNonQuery(Array sqls){string[] strsqls = new string[sqls.Length];for (int i = 0; i < sqls.Length; i++)strsqls[i] = sqls.GetValue(i).ToString();return ExcuteNonQuery(strsqls);}public int ExcuteNonQuery(string[] sqls){IDbConnection pgConn = ERuiPanDal.CreateConnection(pgConnStr, DatabaseType.PG);pgConn.Open();IDbTransaction tran = null;int count = 0;try{tran = pgConn.BeginTransaction();string sqlstr = string.Empty;foreach (string sql in sqls)//count += XXXXDal.ExcuteNonQuery(DatabaseType.PG, tran, sql, null);{count++;if (string.IsNullOrEmpty(sqlstr)){sqlstr = sql;}else{sqlstr += ";" + sql;}}int ret = XXXXDal.ExcuteNonQuery(DatabaseType.PG, tran, sqlstr, null);{if (ret <= 0){count = 0;}}tran.Commit();}catch (Exception ex) { var a = ex; tran.Rollback(); }finally { pgConn.Close();pgConn.Dispose();pgConn = null;tran = null;}return count;}} }

?

?

?

實例2

BaseRepository是數據訪問操作的基類; using System.Linq; using System.Data;namespace Langben.DAL {public abstract class BaseRepository<T> where T : class {///<summary>/// 獲取所有///</summary>///<returns>集合</returns>public virtual IQueryable<T> GetAll(){using (SysEntities db = new SysEntities()){return GetAll(db);}}///<summary>/// 獲取所有///</summary>///<returns>集合</returns>public virtual IQueryable<T> GetAll(SysEntities db){return db.CreateObjectSet<T>().AsQueryable();}///<summary>/// 創建///</summary>///<param name="db">實體數據</param>///<param name="entity">將要創建的對象</param>public virtual void Create(SysEntities db, T entity){if (entity != null){db.CreateObjectSet<T>().AddObject(entity);}}///<summary>/// 創建///</summary>///<param name="entity">一個對象</param>///<returns></returns>public virtual int Create(T entity){using (SysEntities db = new SysEntities()){Create(db, entity);return this.Save(db);}}///<summary>/// 創建對象集合///</summary>///<param name="db">實體數據</param>///<param name="entitys">對象集合</param>public virtual void Create(SysEntities db, IQueryable<T> entitys){foreach (var entity in entitys){this.Create(db, entity);}}///<summary>/// 編輯一個對象///</summary>///<param name="db">實體數據</param>///<param name="entity">將要編輯的一個對象</param>public virtual T Edit(SysEntities db, T entity){db.CreateObjectSet<T>().Attach(entity);db.ObjectStateManager.ChangeObjectState(entity, EntityState.Modified);return entity;}///<summary>/// 編輯對象集合///</summary>///<param name="db">實體數據</param>///<param name="entitys">對象集合</param>public virtual void Edit(SysEntities db, IQueryable<T> entitys){foreach (T entity in entitys){this.Edit(db, entity);}}///<summary>/// 提交保存，持久化到數據庫///</summary>///<param name="db">實體數據</param>///<returns>受影響行數</returns>public int Save(SysEntities db){return db.SaveChanges();}} }
所有數據實體都繼承自IBaseEntity接口; namespace Langben.DAL { public interface IBaseEntity { } }//業務實體（SysException）的定義和驗證元數據（SysExceptionMetadata） using System; using System.ComponentModel.DataAnnotations;namespace Langben.DAL {[MetadataType(typeof(SysExceptionMetadata))]// public partial class SysException : IBaseEntity {#region 自定義屬性，即由數據實體擴展的業務實體 #endregion}public class SysExceptionMetadata {[ScaffoldColumn(false)][Display(Name = "主鍵", Order = 1)]public object Id { get; set; }[ScaffoldColumn(true)][Display(Name = "類型", Order = 2)][StringLength(200, ErrorMessage = "長度不可超過200")]public object LeiXing { get; set; }[ScaffoldColumn(true)][Display(Name = "內容", Order = 3)][StringLength(4000, ErrorMessage = "長度不可超過4000")]public object Message { get; set; }[ScaffoldColumn(true)][Display(Name = "結果", Order = 4)][StringLength(200, ErrorMessage = "長度不可超過200")]public object Result { get; set; }[ScaffoldColumn(true)][Display(Name = "備注", Order = 5)][StringLength(4000, ErrorMessage = "長度不可超過4000")]public object Remark { get; set; }[ScaffoldColumn(true)][Display(Name = "狀態", Order = 6)][StringLength(200, ErrorMessage = "長度不可超過200")]public object State { get; set; }[ScaffoldColumn(true)][Display(Name = "創建時間", Order = 7)][DataType(DataType.DateTime,ErrorMessage="時間格式不正確")]public DateTime? CreateTime { get; set; }[ScaffoldColumn(true)][Display(Name = "創建人", Order = 8)][StringLength(200, ErrorMessage = "長度不可超過200")]public object CreatePerson { get; set; } } }
數據訪問層執行數據庫中的增刪改操作； using System; using System.Collections.Generic; using System.Linq; using System.Data;namespace Langben.DAL {///<summary> /// 異常處理 ///</summary> public class SysExceptionRepository : BaseRepository<SysException>,IDisposable {///<summary>/// 通過主鍵id，獲取異常處理---查看詳細，首次編輯///</summary>///<param name="id">主鍵</param>///<returns>異常處理</returns>public SysException GetById(string id){using (SysEntities db = new SysEntities()){return GetById(db, id);}}///<summary>/// 通過主鍵id，獲取異常處理---查看詳細，首次編輯///</summary>///<param name="id">主鍵</param>///<returns>異常處理</returns>public SysException GetById(SysEntities db, string id){return db.SysException.SingleOrDefault(s => s.Id == id);}///<summary>/// 確定刪除一個對象，調用Save方法///</summary>///<param name="id">一條數據的主鍵</param>///<returns></returns>public int Delete(string id){using (SysEntities db = new SysEntities()){this.Delete(db, id);return Save(db);}}///<summary>/// 刪除一個異常處理///</summary>///<param name="db">實體數據</param>///<param name="id">一條異常處理的主鍵</param>public void Delete(SysEntities db, string id){SysException deleteItem = GetById(db, id);if (deleteItem != null){db.SysException.DeleteObject(deleteItem);}}///<summary>/// 刪除對象集合///</summary>///<param name="db">實體數據</param>///<param name="deleteCollection">主鍵的集合</param>public void Delete(SysEntities db, string[] deleteCollection){//數據庫設置級聯關系，自動刪除子表的內容IQueryable<SysException> collection = from f in db.SysExceptionwhere deleteCollection.Contains(f.Id)select f;foreach (var deleteItem in collection){db.SysException.DeleteObject(deleteItem);}}public void Dispose(){}} } ?

實例3

?

?

using System; using System.Collections.Generic; using System.Text; using System.Collections; using System.Data; using System.Data.OracleClient;namespace SystemDAL {public class DAL{public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ToString();public DAL(){//connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ToString();}/// <summary>/// 執行單條語句/// </summary>/// <param name="sql"></param>/// <returns></returns>public static int ExecuteNonQuery(string sql){int x = 0;try{// Open a connection to the DB.OracleConnection connOra = new OracleConnection(connectionString);connOra.Open();OracleTransaction tran = connOra.BeginTransaction();// Create a command to execute the sql statement.OracleCommand cmdOra = connOra.CreateCommand();cmdOra.CommandText = sql;x = cmdOra.ExecuteNonQuery();tran.Commit();connOra.Close();connOra.Dispose();cmdOra.Dispose();}catch (Exception ex){//log.Error(ex.StackTrace);}return x;}/// <summary>/// 帶參數操作數據庫方法/// </summary>/// <param name="str"></param>/// <param name="sql"></param>/// <returns></returns>public int ExecuteNonQuery2(string sql, OracleParameter[] param){int x=0;try{OracleConnection connOra = new OracleConnection(connectionString);connOra.Open();OracleTransaction tran = connOra.BeginTransaction();OracleCommand cmdOra = connOra.CreateCommand();cmdOra.Transaction = tran;cmdOra.CommandType = CommandType.Text;cmdOra.CommandText = sql;cmdOra.Parameters.Clear();cmdOra.Parameters.AddRange(param);x = cmdOra.ExecuteNonQuery();tran.Commit();connOra.Close();connOra.Dispose();cmdOra.Dispose();}catch (Exception ex){throw ex;}return x;}public static DataTable ExecuteDataTable(String cmdText){DataTable dt = new DataTable();//DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);try{// Open a connection to the DB.//DbConnection connOra = factory.CreateConnection();OracleConnection connOra = new OracleConnection(connectionString);//connOra.ConnectionString = connectionString;connOra.Open();// Create a command to execute the sql statement.//DbCommand cmd = factory.CreateCommand();OracleCommand cmd = connOra.CreateCommand();cmd.CommandText = cmdText;OracleDataAdapter ada = new OracleDataAdapter();//DbDataAdapter ada = factory.CreateDataAdapter();// new OracleDataAdapter(cmd1); ada.SelectCommand = cmd;ada.Fill(dt);connOra.Close();connOra.Dispose();cmd.Dispose();}catch (Exception ex){string str = ex.Message;}return dt;}public static DataTable ExecuteDataTable2(String cmdText,OracleParameter[] param){DataTable dt = new DataTable();//DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);try{// Open a connection to the DB.//DbConnection connOra = factory.CreateConnection();OracleConnection connOra = new OracleConnection(connectionString);//connOra.ConnectionString = connectionString;connOra.Open();// Create a command to execute the sql statement.//DbCommand cmd = factory.CreateCommand();OracleCommand cmd = connOra.CreateCommand();cmd.CommandType = CommandType.Text;cmd.CommandText = cmdText;cmd.Parameters.Clear();cmd.Parameters.AddRange(param);OracleDataAdapter ada = new OracleDataAdapter();//DbDataAdapter ada = factory.CreateDataAdapter();// new OracleDataAdapter(cmd1); ada.SelectCommand = cmd;ada.Fill(dt);connOra.Close();connOra.Dispose();cmd.Dispose();}catch (Exception ex){string str = ex.Message;}return dt;}} }

?

?

?

實例4

?

OracleHelper.cs

?

using System; using System.Collections.Generic; using System.Web; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; using System.Data.SqlClient; using System.Data.Common;namespace ToolHelper {public class OracleHelper{//private static readonly Logger log = LogManager.GetCurrentClassLogger();//數據庫連接對像 /// <summary> /// 該實例使用的數據庫連接字符串 /// </summary> /// //string providerName = ConfigurationManager.ConnectionStrings["OralConnString"].ProviderName;//private string connectionString = ConfigurationManager.ConnectionStrings["OralConnString"].ConnectionString;public static string providerName = "Oracle.DataAccess.Client";//private string connectionString = "User Id=xxxx;Password=yyyy;Data Source=zzzz;enlist=true";//private string connectionString = "User Id=xxxx;Password=yyyy;Data Source=zz;enlist=true";//private string connectionString = " Data Source= (DESCRIPTION ="// + " (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))"// + " (CONNECT_DATA ="// + " (SERVER = DEDICATED)"// + " (SERVICE_NAME = zz)"// + " )"// + " );"// + "User Id=xxxx;Password=yyyy";private static string m_ConnectionStr = null;private static string connectionString {get{if (m_ConnectionStr == null){ //Dictionary<string,string> mConfigDic = XmlReader.GetConfigDic;//m_ConnectionStr = mConfigDic["ConnectionString"];m_ConnectionStr = "Data Source=ORCL;User Id=xxxx;Password=yyyy";}return m_ConnectionStr;}}//= "Data Source= (DESCRIPTION ="// + " (ADDRESS = (PROTOCOL = TCP)(HOST =144.28.59.100)(PORT = 1521))"// + " (CONNECT_DATA ="// + " (SERVER = DEDICATED)"// + " (SERVICE_NAME = orcl)"// + " )"// + " );"// + "User Id=xxxx;Password=abc123";/// <summary>/// 根據查詢語句獲取數據表/// </summary>/// <param name="cmdText"></param>/// <returns></returns>public static DataTable ExecuteDataTable(String cmdText){DataTable dt = new DataTable();//DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);try{// Open a connection to the DB.//DbConnection connOra = factory.CreateConnection();OracleConnection connOra = new OracleConnection(connectionString);//connOra.ConnectionString = connectionString;connOra.Open();// Create a command to execute the sql statement.OracleCommand cmd = connOra.CreateCommand();//DbCommand cmd = factory.CreateCommand();//cmd.Connection = connOra;cmd.CommandText = cmdText;OracleDataAdapter ada = new OracleDataAdapter(cmd);//DbDataAdapter ada = factory.CreateDataAdapter();// new OracleDataAdapter(cmd1); //ada.SelectCommand = cmd;ada.Fill(dt);connOra.Close();connOra.Dispose();cmd.Dispose();}catch (Exception ex){//log4net.LogManager.GetLogger("OracleHelper").Info(ex.Message);}return dt;} /// <summary>/// 執行單條語句/// </summary>/// <param name="sql"></param>/// <returns></returns>public static int ExecuteNonQuery(string sql){//DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);int x = 0;try{// Open a connection to the DB.//DbConnection connOra = factory.CreateConnection();OracleConnection connOra = new OracleConnection(connectionString);//connOra.ConnectionString = connectionString;connOra.Open();DbTransaction tran = connOra.BeginTransaction();// Create a command to execute the sql statement.//DbCommand cmdOra = factory.CreateCommand();OracleCommand cmd = connOra.CreateCommand();cmd.Connection = connOra;cmd.CommandText = sql;x = cmd.ExecuteNonQuery();tran.Commit();connOra.Close();connOra.Dispose();cmd.Dispose();}catch (Exception ex){//log4net.LogManager.GetLogger("OracleHelper").Info(ex.Message);//log.Error(ex.StackTrace);}return x;}public static int ExecuteClobParamNonQuery(string sql, string contentName, string content){DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);int x = 0;try{// Open a connection to the DB.DbConnection connOra = factory.CreateConnection();connOra.ConnectionString = connectionString;connOra.Open();DbTransaction tran = connOra.BeginTransaction();// Create a command to execute the sql statement.DbCommand cmdOra = factory.CreateCommand();cmdOra.Connection = connOra;cmdOra.CommandText = sql;//插入大數據OracleParameter param = new OracleParameter(contentName, OracleDbType.Clob);param.Value = content;cmdOra.Parameters.Add(param);x = cmdOra.ExecuteNonQuery();tran.Commit();connOra.Close();connOra.Dispose();cmdOra.Dispose();}catch (Exception ex){//log.Info(ex.Message);//log.Error(ex.StackTrace);}return x;}/// <summary>/// 執行多條語句/// </summary>/// <param name="strSQLs"></param>/// <returns>返回影響多少行</returns>public static int ExecuteNonQuerySqls(string[] strSQLs){DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);DbConnection connOra = null;DbTransaction tran = null;DbCommand cmdOra = null;int j = strSQLs.Length;int mReturnValue = 0;try{// Open a connection to the DB.connOra = factory.CreateConnection();connOra.ConnectionString = connectionString;connOra.Open();tran = connOra.BeginTransaction();// Create a command to execute the sql statement.cmdOra = factory.CreateCommand();cmdOra.Connection = connOra;foreach (string str in strSQLs){cmdOra.CommandText = str;mReturnValue = mReturnValue + cmdOra.ExecuteNonQuery();}tran.Commit();return mReturnValue;}catch (Exception ex){tran.Rollback();//log.Info(ex.Message);//log.Error(ex.StackTrace);return -1;}finally{connOra.Close();connOra.Dispose();cmdOra.Dispose();}}/// <summary>/// 執行存儲過程/// </summary>/// <param name="strProcName"></param>/// <param name="mParams"></param>/// <returns></returns>public static int ExecProc(string strProcName, DbParameter[] mParams){DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);DbConnection connOra = null;DbCommand cmd = null;int j = mParams.Length;try{// Open a connection to the DB.connOra = factory.CreateConnection();connOra.ConnectionString = connectionString;connOra.Open();// Create a command to execute the sql statement.cmd = factory.CreateCommand();cmd.Connection = connOra;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = strProcName;if (mParams != null){foreach (DbParameter par in mParams){cmd.Parameters.Add(par);}}cmd.ExecuteNonQuery();return 0;}catch (Exception ex){//log.Info(ex.Message);//log.Error(ex.StackTrace);return -1;}finally{connOra.Close();connOra.Dispose();cmd.Dispose();}}} }

?

?

?

?

?

總結

以上是生活随笔為你收集整理的dNet项目数据访问层代码总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。