1、C#之数据库连接

来源：互联网发布：html js select 添加编辑：程序博客网时间：2024/06/05 12:39
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);                }            }        }    }}