<appsettings>
<add key="rpsconnection" value="mssql"/>
<add key="damconnection" value="sqlite" />
</appsettings>
<connectionstrings>
<add name="sqlite" connectionString="" providerName="System.Data.SQLite.SQLiteConnection;System.Data.SQLite"/>
<add name="mssql" connectionString="" providerName="System.Data.SqlClient.SqlConnection;System.Data" />
</connectionstrings>

/// <summary>
/// 數(shù)據(jù)訪問層
/// 透過Dam訪問數(shù)據(jù)庫
/// </summary>
public class DALTest
{
/// <summary>
/// 最簡單的插入操作
/// Execute會(huì)幫你自動(dòng)創(chuàng)建所對(duì)應(yīng)數(shù)據(jù)庫的連接、執(zhí)行操作、釋放Command、關(guān)閉連接、釋放鏈接等
/// </summary>
/// <returns>執(zhí)行后受影響行數(shù)</returns>
public int Insert()
{
return Execute.SExecuteNonQuery("sp_name", null);
//return Execute.SExecuteNonQuery("insert into tablename (..) values (..)", CommandType.Text , null);
}
/// <summary>
/// 帶參數(shù)的插入操作
/// Execute會(huì)幫你自動(dòng)創(chuàng)建所對(duì)應(yīng)數(shù)據(jù)庫的連接
/// 并幫你把參數(shù)類型轉(zhuǎn)換為所對(duì)應(yīng)數(shù)據(jù)庫類型的參數(shù)進(jìn)行數(shù)據(jù)庫操作.
/// </summary>
/// <returns></returns>
public int Insert_Paras()
{
DbParameter[] paras =
{
new MySqlParameter("p1","p1value")
,new SqlParameter("p2","p2value")
};
return Execute.SExecuteNonQuery("sp_name", paras);
}
/// <summary>
/// 帶參數(shù)的插入操作，并能得到參數(shù)返回值
/// Execute會(huì)幫你自動(dòng)創(chuàng)建所對(duì)應(yīng)數(shù)據(jù)庫的連接
/// 并幫你把參數(shù)類型轉(zhuǎn)換為所對(duì)應(yīng)數(shù)據(jù)庫類型的參數(shù)進(jìn)行數(shù)據(jù)庫操作.
/// 執(zhí)行完成您可得到返回值
/// </summary>
/// <returns></returns>
public int Insert_Paras(ref string outputValue)
{
int resultInt = 0;
DbParameter[] paras =
{
new MySqlParameter("p1","p1value")
,new SqlParameter("p2_output","")
{
Direction = ParameterDirection.Output
}
};
resultInt = Execute.SExecuteNonQuery("sp_name", paras);
if (paras[1].Value != null) outputValue = paras[1].Value.ToString();
return resultInt;
}
/// <summary>
/// 帶參數(shù)的插入操作，并能得到參數(shù)返回值，此參數(shù)從BLL層傳來
///同上...
/// </summary>
/// <returns></returns>
public int Insert_Paras(DbParameter[] paras)
{
return Execute.SExecuteNonQuery("sp_name", paras);
//這樣可以在BLL層通過 paras[1]來獲取返回參數(shù)了
}
/// <summary>
/// 執(zhí)行同連接多操作的事務(wù)處理
/// 這里使用1個(gè)帶參數(shù)的存儲(chǔ)過程和一個(gè)不帶參數(shù)的sql語句操作
/// 使用是靜態(tài)調(diào)用方法
/// </summary>
/// <returns></returns>
public bool Insert_Transaction()
{
bool resultBool = false;
DbParameter[] paras =
{
new MySqlParameter("p1","p1value")
,new SqlParameter("p2","p2value")
};
DbConnection connection = null;
DbTransaction transaction = null;
try
{
connection = Factory.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
int resultInt1 = Execute.SExecuteNonQuery(connection, transaction, "sp_name1", paras);
int resultInt2 = Execute.SExecuteNonQuery(connection, transaction, "insert into tablename (...) values (...)", null);
if (resultInt1 > 0 && resultInt2 > 0)
{
transaction.Commit();
resultBool = true;
}
else
{
transaction.Rollback();
}
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
if (connection != null)
{
//這里需要手動(dòng)關(guān)閉連接??！
connection.Close();
connection.Dispose();
}
}
return resultBool;
}
/// <summary>
/// 執(zhí)行同連接多任務(wù)操作（不使用事務(wù)）
/// 這里我們使用對(duì)象方法。這樣執(zhí)行多任務(wù)操作可能會(huì)穩(wěn)定點(diǎn)和效率高點(diǎn)
/// </summary>
/// <returns></returns>
public void Insert_much()
{
Execute exec = Execute.NewExecute;
DbConnection connection = null;
try
{
connection = Factory.CreateConnection();
exec.ExecuteNonQuery(connection, null, "sp_name", null);
exec.ExecuteNonQuery(connection, null, "sp_name2", null);
}
finally
{
if (connection != null)
{
//因?yàn)檎{(diào)用了 帶事務(wù)的那個(gè)方法（但是我們設(shè)置事務(wù)為NULL，事務(wù)無效）
//所以連接不會(huì)自動(dòng)關(guān)閉釋放，我們需要手動(dòng)釋放
connection.Close();
connection.Dispose();
}
}
}
}