226      阿裏雲  技術社區[雲棲]

DbHelper-SQL數據庫訪問助手

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
        #region 執行不帶參數sql語句
        /// <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
    }
}

最後更新：2017-04-03 08:26:22

  上一篇： Java中靜態方法不具有多態性

  下一篇： [推薦係統] 自己動手寫一個推薦係統