• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#对SQlServer访问的完整类

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.ServiceModel.Web;
using System.Text;

namespace Common
{
    public class DBHelper
    {
        private string m_dbs;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DBHelper() { }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        public DBHelper (string connectString)
        {
            m_dbs = connectString;
        }
        public string ConnectString
        {
            get { return m_dbs; }
            set { m_dbs = value; }
        }

        /// <summary>
        /// 插入并获取ID
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="commandStr">SQL语句 包含获取ID的命令</param>
        /// <returns>新记录的ID</returns>
        public int ExecuteScalarInsert(string connectString,string commandStr)
        {
            string err = "";
            int ret = 0;
            if(string.IsNullOrEmpty (connectString ))
            {
                return -1;
            }
            using (SqlConnection dbc = new SqlConnection(connectString))
            {
                SqlCommand insert = new SqlCommand(commandStr, dbc);

                try
                {
                    dbc.Open();
                    ret = Convert.ToInt32(insert.ExecuteScalar());
                }
                catch(Exception ex)
                {
                    err = ex.Message;
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return ret;
            }
        }

        /// <summary>
        /// 插入并获取ID
        /// </summary>
        /// <param name="commandStr">SQL语句 包含获取ID的命令</param>
        /// <returns>新记录的ID</returns>
        public int ExecuteScalarInsert(string commandStr)
        {
            string err = "";
            int ret = 0;
            if (string.IsNullOrEmpty(m_dbs ))
            {
                return -1;
            }
            using (SqlConnection dbc = new SqlConnection(m_dbs))
            {
                SqlCommand insert = new SqlCommand(commandStr, dbc);

                try
                {
                    dbc.Open();
                    ret = Convert.ToInt32(insert.ExecuteScalar());
                }
                catch(Exception ex)
                {
                    err = ex.Message;
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return ret;
            }
        }

        /// <summary>
        /// 添加、删除、更新操作
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="commandstr">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int CommandExecuteNonQuery(string connectString, string commandstr)
        {
            if(string .IsNullOrEmpty (connectString)||string .IsNullOrEmpty (commandstr ))
            {
                return -1;
            }
            string err = "";
            int result = 0;
            using (SqlConnection dbc = new SqlConnection(connectString))
            {
                SqlCommand command = new SqlCommand(commandstr, dbc);
                try
                {
                    dbc.Open();
                    result = command.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    err = ex.Message;
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return result;
            }
            
        }
        
        /// <summary>
        /// 添加、删除、更新操作
        /// </summary>
        /// <param name="commandstr">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int CommandExecuteNonQuery(string commandstr)
        {
            if (string.IsNullOrEmpty(m_dbs )||string .IsNullOrEmpty (commandstr ))
            {
                return -1;
            }
            string err = "";
            int result = 0;
            using (SqlConnection dbc = new SqlConnection(m_dbs))
            {
                SqlCommand command = new SqlCommand(commandstr, dbc);
                try
                {
                    dbc.Open();
                    result = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    err = ex.Message;
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return result;
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="selectstr">SQL语句</param>
        /// <returns>数据表</returns>
        public DataTable GetCommand(string connectString, string selectstr)
        {
            if(string .IsNullOrEmpty (connectString )||string .IsNullOrEmpty (selectstr ))
            {
                return null;
            }
            DataTable table = new DataTable();

            string err = "";
            using (SqlConnection dbc = new SqlConnection(connectString))
            {
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(selectstr, dbc);
                    adapter.Fill(table);
                }
                catch(Exception ex)
                {
                    err = ex.Message;
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return table;
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="selectstr">SQL语句</param>
        /// <returns>数据表</returns>
        public DataTable GetCommand(string selectstr)
        {
            if (string.IsNullOrEmpty(m_dbs))
            {
                return null;
            }
            DataTable table = new DataTable();
            string err = "";
            using (SqlConnection dbc = new SqlConnection(m_dbs))
            {
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(selectstr, dbc);
                    adapter.Fill(table);
                }
                catch(Exception ex)
                {
                    err = ex.Message;
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return table;
            }
        }

        /// <summary>
        /// 执行一个事务
        /// </summary>
        /// <param name="commands">事务中要执行的所有语句</param>
        /// <returns>事务是否成功执行</returns>
        public bool ExecuteTransaction(List <string >commands)
        {
            if(string .IsNullOrEmpty (m_dbs)||commands ==null )
            {
                return false;
            }

            string err = "";
            bool ret = false;
            using (SqlConnection dbc = new SqlConnection(m_dbs))
            {
                dbc.Open();
                using (SqlTransaction transaction = dbc.BeginTransaction())
                {
                    try
                    {
                        foreach (string commandstr in commands)
                        {
                            SqlCommand command = new SqlCommand(commandstr, dbc);
                            command.Transaction = transaction;
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit();
                        ret = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        err = ex.Message;
                    }
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return ret;
            }
        }

        /// <summary>
        /// 执行一个事务
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="commands">事务中要执行的所有语句</param>
        /// <returns>事务是否成功执行</returns>
        public bool ExecuteTransaction(string connectString,List<string> commands)
        {
            if (string.IsNullOrEmpty(connectString) || commands == null)
            {
                return false;
            }

            string err = "";
            bool ret = false;
            using (SqlConnection dbc = new SqlConnection(connectString))
            {
                dbc.Open();
                using (SqlTransaction transaction = dbc.BeginTransaction())
                {
                    try
                    {
                        foreach (string commandstr in commands)
                        {
                            SqlCommand command = new SqlCommand(commandstr, dbc);
                            command.Transaction = transaction;
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit();
                        ret = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        err = ex.Message;
                    }
                }
            }

            if (err.Length > 0)
            {
                throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError);
            }
            else
            {
                return ret;
            }
        }
    }
}

  这种类写了又写,故作记录。


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
C#SQL查询结果ToDataTable发布时间:2022-07-14
下一篇:
C#中foreach的用法发布时间:2022-07-14
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap