摘要:自己動手寫的資料存取類別
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.UI.WebControls.WebParts;
using System.Windows.Forms;
namespace Login
{
class cmd
{
//資料存取層 第一版2013 0603
#region 物件初始化
private SqlConnection t_conn = null; //連線字串
private SqlCommand t_cmd = null;
private SqlDataAdapter t_da = null;
private DataSet t_ds = null;
private DataTable t_dt = null;
public cmd()
{
t_conn = GetConn();
t_cmd = new SqlCommand();
t_da = new SqlDataAdapter();
}
#endregion
#region 取回連線字串
public static SqlConnection GetConn() //打開SqlConnection
{
SqlConnection t_conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WinFormConn"].ConnectionString);
// return System.Configuration.ConfigurationSettings.AppSettings["AppConnectionString"];
return t_conn;
}
#endregion
#region 執行DDL
public int ExceSQLCommand(string SqlStr) //執行SqlCommand
{
int n = 0;
try
{
t_cmd = new SqlCommand(SqlStr, GetConn());
t_cmd.Connection.Open();
t_cmd.CommandType = CommandType.Text;
n = t_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
return n;
}
#endregion
#region 取回ExecuteScalar
public object GetSingleObject(string sqlstr)
{
object obj = null;
t_cmd = new SqlCommand(sqlstr, GetConn());
t_cmd.CommandType = CommandType.Text;
try
{
if (t_cmd.Connection.State == ConnectionState.Closed)
{
t_cmd.Connection.Open();
}
obj = t_cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
} return obj;
}
#endregion
#region 取得DataReader
public SqlDataReader GetDataReader(string SqlStr) //取得DataReader
{
try
{
t_cmd = new SqlCommand(SqlStr, GetConn());
t_cmd.CommandType = CommandType.Text;
if (t_cmd.Connection.State == ConnectionState.Closed)
{
t_cmd.Connection.Open();
}
SqlDataReader dr = t_cmd.ExecuteReader();
return dr;
}
catch (Exception ex)
{
throw ex;
}
}
public SqlDataReader GetDataReader(string SqlStr, SqlParameter[] parameters) //取得DataReader 並帶n個參數
{
t_cmd = new SqlCommand(SqlStr, GetConn());
t_cmd.CommandType = CommandType.Text;
foreach (SqlParameter param in parameters)
{
if (param.Direction == ParameterDirection.Input)
{
param.Direction = ParameterDirection.Input;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.Output)
{
param.Direction = ParameterDirection.Output;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.ReturnValue)
{
param.Direction = ParameterDirection.ReturnValue;
t_cmd.Parameters.Add(param);
}
}
if (t_cmd.Connection.State == ConnectionState.Closed)
{
t_cmd.Connection.Open();
}
SqlDataReader dr = t_cmd.ExecuteReader();
return dr;
}
#endregion
#region 執行預存程序
public int ExecStoreProc(string sqlstr) //不帶任何參數
{
t_cmd = new SqlCommand(sqlstr, GetConn());
t_cmd.CommandType = CommandType.StoredProcedure;
if (t_cmd.Connection.State == ConnectionState.Closed)
{
t_cmd.Connection.Open();
}
int n = t_cmd.ExecuteNonQuery();
return n;
}
public int ExecStoreProc(string sqlstr, SqlParameter[] parameters) //執行StoreProcdurce並帶n組參數
{
t_cmd = new SqlCommand(sqlstr, GetConn());
t_cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in parameters)
{
if (param.Direction == ParameterDirection.Input)
{
param.Direction = ParameterDirection.Input;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.Output)
{
param.Direction = ParameterDirection.Output;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.ReturnValue)
{
param.Direction = ParameterDirection.ReturnValue;
t_cmd.Parameters.Add(param);
}
}
if (t_cmd.Connection.State == ConnectionState.Closed)
{
t_cmd.Connection.Open();
}
int n = t_cmd.ExecuteNonQuery();
return n;
}
#endregion
#region 取回傳回資料集、資料表(DS、DT)
public DataTable GetDataTable(string SqlStr, string table) //傳回datatable
{
try
{
t_dt = new DataTable();
t_da.SelectCommand = t_cmd(SqlStr, GetConn());
t_da.Fill(t_dt);
return t_dt;
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetDataTable(string SqlStr, string table, SqlParameter[] parameters) //傳回datatable
{
try
{
t_dt = new DataTable();
t_cmd = new SqlCommand(SqlStr, GetConn());
foreach (SqlParameter param in parameters)
{
if (param.Direction == ParameterDirection.Input)
{
param.Direction = ParameterDirection.Input;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.Output)
{
param.Direction = ParameterDirection.Output;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.ReturnValue)
{
param.Direction = ParameterDirection.ReturnValue;
t_cmd.Parameters.Add(param);
}
}
t_da.SelectCommand = t_cmd;
t_da.Fill(t_dt);
return t_dt;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet GetDataSet(string SqlStr, string table)//傳回dataset
{
try
{
t_ds = new DataSet();
t_cmd = new SqlCommand(SqlStr, GetConn());
t_da.SelectCommand = t_cmd;
t_da.Fill(t_ds, table);
return t_ds;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet GetDataSet(string SqlStr, string table, SqlParameter[] parameters)//傳回dataset
{
try
{
t_ds = new DataSet();
t_cmd = new SqlCommand(SqlStr, GetConn());
foreach (SqlParameter param in parameters)
{
if (param.Direction == ParameterDirection.Input)
{
param.Direction = ParameterDirection.Input;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.Output)
{
param.Direction = ParameterDirection.Output;
t_cmd.Parameters.Add(param);
}
else if (param.Direction == ParameterDirection.ReturnValue)
{
param.Direction = ParameterDirection.ReturnValue;
t_cmd.Parameters.Add(param);
}
}
t_da.SelectCommand = t_cmd;
t_da.Fill(t_ds, table);
return t_ds;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 釋放物件
public void DBFree()
{
if (t_cmd.Connection.State == ConnectionState.Open)
{
t_cmd.Connection.Close();
t_cmd.Connection.Dispose();
t_conn = null;
}
if (t_cmd != null)
{
t_cmd.Dispose();
}
if (t_ds != null)
{
t_ds.Dispose();
}
if (t_dt != null)
{
t_dt.Dispose();
}
if (t_da != null)
{
t_da.Dispose();
}
}
#endregion
}
}