三層架構之資料庫訪問層完全篇(C#)

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

}

}