using System; using System.Data; using System.Data.SqlClient; namespace Whir.Software.Framework.Ultimate { /// <summary> /// </summary> public class DbHelper { #region 判斷連接是否成功 /// <summary> /// 判斷連接是否成功！ /// </summary> /// <param name="con"> 鏈接字符串</param> /// <returns>true 表示鏈接成功，false表示連接失敗</returns> public static bool IsConnected(string con) { bool flag; var conn = new SqlConnection(con); try { conn.Open(); flag = true; } catch (Exception) { flag = false; } finally { conn.Close(); } return flag; } #endregion /// <summary> /// 執行不帶參數sql語句 /// </summary> /// <param name="sql">增，刪，改sql語句</param> /// <param name="con"></param> /// <returns>返回所影響的行數</returns> public static bool Execute(string sql, string con) { var cmd = new SqlCommand(); var connection = new SqlConnection(con); try { using (connection) { cmd.Connection = connection; cmd.CommandText = sql; connection.Open(); cmd.ExecuteNonQuery(); return true; } } catch (Exception) { return false; } } #endregion #region 執行SQL語句返回DataTable /// <summary> /// 執行SQL語句返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="con"></param> /// <returns></returns> public static DataTable ExcuteDataTable(string sql, string con) { var cmd = new SqlCommand(); var connection = new SqlConnection(con); try { using (connection) { cmd.Connection = connection; cmd.CommandText = sql; connection.Open(); var da = new SqlDataAdapter(cmd); var ds = new DataSet(); da.Fill(ds); return ds.Tables[0]; } } catch (Exception ex) { var dt = new DataTable(); dt.Columns.Add("異常信息"); DataRow row = dt.NewRow(); row["異常信息"] = ex.Message; dt.Rows.Add(row); return dt; } } #endregion #region 執行SQL語句查詢單條記錄 /// <summary> /// 執行SQL語句查詢單條記錄 /// </summary> /// <param name="sql"></param> /// <param name="con"></param> /// <returns></returns> public static object ExecuteScalar(string sql, string con) { var cmd = new SqlCommand(); var connection = new SqlConnection(con); try { using (connection) { cmd.Connection = connection; cmd.CommandText = sql; connection.Open(); return cmd.ExecuteScalar(); } } catch (Exception) { return string.Empty; } } #endregion #region 取得表最大Id /// <summary> /// 取得表最大Id /// </summary> /// <param name="tableName"></param> /// <param name="fieldName"></param> /// <param name="con"></param> /// <returns></returns> public static int GetMaxId(string tableName, string fieldName, string con) { string sql = "SELECT NVL(MAX({0}),0)+1 FROM {1}"; try { sql = string.Format(sql, fieldName, tableName); int result; Int32.TryParse(ExecuteScalar(sql, con).ToString(), out result); return result; } catch (Exception) { return -1; } } #endregion } } |