?? database.cs
字號:
using System;
using System.IO;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using TQMS.DataAccessHelper;
namespace TQMS.DataAccessLayer
{
/// <summary>
/// 類，用于數據訪問的類。
/// </summary>
public class Database : IDisposable
{
/// <summary>
/// 保護變量，數據庫連接。
/// </summary>
protected SqlConnection Connection;
/// <summary>
/// 保護變量，數據庫連接串。
/// </summary>
protected String ConnectionString;
/// <summary>
// 構造函數。
/// </summary>
/// <param name="DatabaseConnectionString">數據庫連接串</param>
public Database()
{
ConnectionString = ConfigurationManager.ConnectionStrings["QuestionManagerConnectionString"].ToString();
}
/// <summary>
/// 析構函數，釋放非托管資源
/// </summary>
~Database()
{
try
{
if (Connection != null)
Connection.Close();
}
catch{}
try
{
Dispose();
}
catch{}
}
/// <summary>
/// 保護方法，打開數據庫連接。
/// </summary>
protected void Open()
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
try
{
Connection.Open();
}
catch(SqlException e)
{
//if (!File.Exists("error.txt"))
//{
// File.Create("error.txt");
//}
//FileStream fs = new FileStream("error.txt", FileAccess.ReadWrite);
//StreamWriter w = new StreamWriter(fs);
//fs.Write(e.Message.
//string strMessage = e.Message;
////以下把信息寫入windows日志
////要把aspnet用戶添加到管理員組中，以便有寫注冊表權限
//if (!EventLog.SourceExists("TQMS"))
// EventLog.CreateEventSource("TQMS", "Application");
// //EventLog.CreateEventSource("mySource", "myLog");
//EventLog Event = new EventLog();
//Event.Source = "TQMS";
//Event.WriteEntry(strMessage, EventLogEntryType.Warning);
////EventLog.Delete("myLog");
//throw new Exception("我處理不了，請最高人民法院處理！");
}
}
}
/// <summary>
/// 公有方法，關閉數據庫連接。
/// </summary>
public void Close()
{
if (Connection != null)
Connection.Close();
}
/// <summary>
/// 公有方法，釋放資源。
/// </summary>
public void Dispose()
{
// 確保連接被關閉
if (Connection != null)
{
Connection.Dispose();
Connection = null;
}
}
/// <summary>
/// 公有方法，獲取數據，返回一個SqlDataReader （調用后注意調用SqlDataReader.Close()）。
/// </summary>
/// <param name="SqlString">Sql語句</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader GetDataReader(String SqlString)
{
Open();
SqlCommand cmd = new SqlCommand(SqlString,Connection);
return cmd.ExecuteReader();
}
/// <summary>
/// 公有方法，獲取數據，返回一個SqlDataReader （調用后注意調用SqlDataReader.Close()、Database.Close()）。
/// </summary>
/// <param name="SqlString">Sql語句</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader GetSaDataReader(String SqlString)
{
Open();
SqlCommand cmd = new SqlCommand(SqlString,Connection);
return cmd.ExecuteReader();
}
/// <summary>
/// 公有方法，獲取數據，返回一個DataSet。
/// </summary>
/// <param name="SqlString">Sql語句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(String SqlString)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
}
/// <summary>
/// 公有方法，獲取數據，返回一個DataTable。
/// </summary>
/// <param name="SqlString">Sql語句</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(String SqlString)
{
DataSet dataset = GetDataSet(SqlString);
dataset.CaseSensitive = false;
return dataset.Tables[0];
}
/// <summary>
/// 公有方法，獲取數據，返回一個DataRow。
/// </summary>
/// <param name="SqlString">Sql語句</param>
/// <returns>DataRow</returns>
public DataRow GetDataRow(String SqlString)
{
DataSet dataset = GetDataSet(SqlString);
dataset.CaseSensitive = false;
if (dataset.Tables[0].Rows.Count>0)
{
return dataset.Tables[0].Rows[0];
}
else
{
return null;
}
}
/// <summary>
/// 公有方法，執行Sql語句。
/// </summary>
/// <param name="SqlString">Sql語句</param>
/// <returns>對Update、Insert、Delete為影響到的行數，其他情況為-1</returns>
public int ExecuteSQL(String SqlString)
{
int count = -1;
Open();
try
{
SqlCommand cmd = new SqlCommand(SqlString,Connection);
count = cmd.ExecuteNonQuery();
}
catch
{
count = -1;
}
finally
{
Close();
}
return count;
}
/// <summary>
/// 公有方法，執行一組Sql語句。
/// </summary>
/// <param name="SqlStrings">Sql語句組</param>
/// <returns>是否成功</returns>
public bool ExecuteSQL(String[] SqlStrings)
{
bool success = true;
Open();
SqlCommand cmd = new SqlCommand();
SqlTransaction trans = Connection.BeginTransaction();
cmd.Connection = Connection;
cmd.Transaction = trans;
try
{
foreach (String str in SqlStrings)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
success = false;
trans.Rollback();
}
finally
{
Close();
}
return success;
}
/// <summary>
/// 公有方法，執行一組Sql語句。
/// </summary>
/// <param name="SqlStrings">Sql語句組</param>
/// <returns>是否成功</returns>
public bool ExecuteSQL(ArrayList SqlStrings)
{
bool success = true;
Open();
SqlCommand cmd = new SqlCommand();
SqlTransaction trans = Connection.BeginTransaction();
cmd.Connection = Connection;
cmd.Transaction = trans;
try
{
foreach (String str in SqlStrings)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
success = false;
trans.Rollback();
}
finally
{
Close();
}
return success;
}
/// <summary>
/// 公有方法，在一個數據表中插入一條記錄。
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Cols">哈西表，鍵值為字段名，值為字段值</param>
/// <returns>是否成功</returns>
public bool Insert(String TableName,Hashtable Cols)
{
int Count = 0;
if (Cols.Count<=0)
{
return true;
}
String Fields = " (";
String Values = " Values(";
foreach(DictionaryEntry item in Cols)
{
if (Count!=0)
{
Fields += ",";
Values += ",";
}
Fields += "["+item.Key.ToString()+"]";
Values += item.Value.ToString();
Count ++;
}
Fields += ")";
Values += ")";
String SqlString = "Insert into "+TableName+Fields+Values;
String[] Sqls = {SqlString};
return ExecuteSQL(Sqls);
}
/// <summary>
/// 公有方法，更新一個數據表。
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Cols">哈西表，鍵值為字段名，值為字段值</param>
/// <param name="Where">Where子句</param>
/// <returns>是否成功</returns>
public bool Update(String TableName,Hashtable Cols,String Where)
{
int Count = 0;
if (Cols.Count<=0)
{
return true;
}
String Fields = " ";
foreach(DictionaryEntry item in Cols)
{
if (Count!=0)
{
Fields += ",";
}
Fields += "["+item.Key.ToString()+"]";
Fields += "=";
Fields += item.Value.ToString();
Count ++;
}
Fields += " ";
String SqlString = "Update "+TableName+" Set "+Fields+Where;
String[] Sqls = {SqlString};
return ExecuteSQL(Sqls);
}
}
}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -