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中静态方法不具有多态性

  下一篇： [推荐系统] 自己动手写一个推荐系统