using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace Erp.DBUtility { public class YSqlHelper { //Sql連線語句 private static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; /// <summary>執行不帶引數的增刪改SQL語句或儲存過程 ,返回受影響的行數</summary> public static int ExecuteNonQuery(string cmdText, CommandType ct) { int res = 0;//受影響的行數 using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//開啟資料庫連結 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; res = cmd.ExecuteNonQuery();//執行Sql語句並受影響的行數 } } catch { } finally { if (conn.State == ConnectionState.Open)//判斷連線是否處於開啟狀態 { conn.Close();//關閉與資料庫的連結 } } } return res; } /// <summary> 執行帶引數的增刪改SQL語句或儲存過程,返回受影響的行數</summary> public static int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct) { int res = 0;//受影響的行數 using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//開啟資料庫連結 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); res = cmd.ExecuteNonQuery();//執行Sql語句並受影響的行數 } } catch { } finally { if (conn.State == ConnectionState.Open)//判斷連線是否處於開啟狀態 { conn.Close();//關閉與資料庫的連結 } } } return res; } /// <summary> 執行不帶引數的查詢SQL語句或儲存過程,返回DataTable物件</summary> public static DataTable ExecuteQueryDataTable(string cmdText, CommandType ct) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//開啟資料庫連結 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判斷連線是否處於開啟狀態 { conn.Close();//關閉與資料庫的連結 } } } return dt; } /// <summary> 執行帶引數的查詢SQL語句或儲存過程,返回DataTable物件</summary> public static DataTable ExecuteQueryDataTable(string cmdText, SqlParameter[] paras, CommandType ct) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//開啟資料庫連結 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判斷連線是否處於開啟狀態 { conn.Close();//關閉與資料庫的連結 } } } return dt; } /// <summary> 執行不帶引數的查詢SQL語句或儲存過程,返回DataSet物件</summary> public static DataSet ExecuteQueryDataSet(string cmdText, CommandType ct) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//開啟資料庫連結 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(ds, "ds"); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判斷連線是否處於開啟狀態 { conn.Close();//關閉與資料庫的連結 } } } return ds; } /// <summary> 執行帶引數的查詢SQL語句或儲存過程,返回DataSet物件</summary> public static DataSet ExecuteQueryDataSet(string cmdText, SqlParameter[] paras, CommandType ct) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//開啟資料庫連結 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(ds, "ds"); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判斷連線是否處於開啟狀態 { conn.Close();//關閉與資料庫的連結 } } } return ds; } /// <summary>查詢資料是否存在</summary> public static bool ExecuteDataIsExistByData(string sqlStr, CommandType commandType) { bool iss = false; DataSet ds = ExecuteQueryDataSet(sqlStr, commandType); for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count > 0) iss = true; } return iss; } /// <summary>查詢資料是否存在 </summary> public static bool ExecuteDataIsExistByData(string sqlStr, SqlParameter[] paras, CommandType commandType) { bool iss = false; DataSet ds = ExecuteQueryDataSet(sqlStr, paras, commandType); for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count > 0) iss = true; } return iss; } /// <summary>查詢增刪改資料操作是否成功 </summary> public static bool ExecuteDataIsExistByInt(string sqlStr, CommandType commandType) { int ds = ExecuteNonQuery(sqlStr, commandType); bool iss = ds > 0 ? true : false; return iss; } /// <summary>查詢增刪改資料操作是否成功 </summary> public static bool ExecuteDataIsExistByInt(string sqlStr, SqlParameter[] paras, CommandType commandType) { int ds = ExecuteNonQuery(sqlStr, paras, commandType); bool iss = ds > 0 ? true : false; return iss; } } }