- 技术人生
- 三层架构之数据库访问层完全篇(C#)
三层架构之数据库访问层完全篇(C#)
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
namespace
DbBase
...
{ public abstract class Base ...{ "Fields of base calss"#region "Fields of base calss" protected static string strConn = ConfigurationSettings.AppSettings["strConnection"]; protected static string strSQL; #endregion "Properties of base class"#region "Properties of base class" } #endregion "Functions of base class"#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 }
}
