1、C#之数据库连接

最新推荐文章于 2022-10-14 14:35:55 发布

xuxianmei

最新推荐文章于 2022-10-14 14:35:55 发布

阅读量714

点赞数

分类专栏： C#

本文链接：https://blog.csdn.net/tlxxm/article/details/8912353

版权

C# 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

using System;
using System.Collections.Generic;
using System.Text;


using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    /// <summary>
    /// 公有类（用于所有的数据操作类）
    /// </summary>
    public class DBHelper
    {


        string connectionstring = ConfigurationManager.ConnectionStrings["strconn"].ToString();
	//这个strconn在app.config配置文件中.


        /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetConn()
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            return conn;
        }

        /// <summary>
        /// 配置SqlCommand对象
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sqlstr"></param>
        /// <param name="tran"></param>
        /// <param name="sps"></param>
        /// <returns></returns>
        public SqlCommand PrepareCommand(SqlConnection conn, string sqlstr, SqlTransaction tran, SqlParameter[] sps)
        {
            SqlCommand comm = new SqlCommand(sqlstr, conn);
            comm.CommandType = CommandType.StoredProcedure;//存储过程
            if (tran != null)
            {
                comm.Transaction = tran;
            }
            if (sps != null)
            {
                comm.Parameters.AddRange(sps);
            }
            return comm;

        }


        /// <summary>
        /// 执行查询存储过程，返回一个Datatable类型(不带事务)
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="sps"></param>
        /// <returns></returns>
        public DataTable ExecuteStoredSelectSql(string strsql, SqlParameter[] sps)
        {
            using (SqlConnection conn = GetConn())
            {

                using (SqlCommand comm = PrepareCommand(conn, strsql, null, sps))
                {
                    try
                    {
                        SqlDataAdapter da = new SqlDataAdapter(comm);
                        DataTable table = new DataTable();
                        da.Fill(table);
                        return table;
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }

        }


        /// <summary>
        /// 执行存储过程，适用于增加、删除、修改等的存储过程操作(单条记录操作)(无事务)
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="sps"></param>
        /// <returns></returns>
        public bool ExecuteStoredSql(string strsql, SqlParameter[] sps)
        {


            using (SqlConnection conn = GetConn())
            {

                using (SqlCommand comm = PrepareCommand(conn, strsql, null, sps))
                {
                    try
                    {

                        if (conn.State != ConnectionState.Open)
                        {
                            conn.Open();
                        }

                        int i = comm.ExecuteNonQuery();
                        if (i > 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }


                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);

                    }
                }
            }
        }



        /// <summary>
        /// 执行多结果集，并带事务(多个sqlcommand对象 ,)
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="tran"></param>
        /// <param name="sps"></param>
        /// <returns></returns>
        public bool ExecuteInsertRecordsStoredSql(string[] strsqls, SqlParameter[][] sps, DataTable table)
        {
            using (SqlConnection conn = GetConn())
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                try
                {
                    SqlCommand delcomm = PrepareCommand(conn, strsqls[0], null, sps[0]);



                    SqlCommand inscomm = PrepareCommand(conn, strsqls[1], null, sps[1]);

                    delcomm.Transaction = tran;
                    delcomm.ExecuteNonQuery();//先删除

                    inscomm.Transaction = tran;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.InsertCommand = inscomm;
                    da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                    da.UpdateBatchSize = 0;
                    da.Update(table);
                    tran.Commit();
                    return true;

                }
                catch (SqlException ex)
                {
                    tran.Rollback();
                    throw new Exception(ex.Message);


                }


            }


        }

    }


}