common.cs

来自「为了便于企业对客户按照地区进行统计查询分析」· CS 代码 · 共 145 行

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataAccess.Comm
{
    public class Comm
    {
        public Comm()
        {
        }
        //连接数据库
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns>返回连接对象</returns>
        public SqlConnection conn()
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection sqlcnn = new SqlConnection(constr);
            return sqlcnn;
        }

        ///不带参数的查询
        /// <summary>
        /// 不带参数的查询
        /// </summary>
        /// <param name="sqlProcName">存储过程名</param>
        /// <returns></returns>
        public DataTable Search(string sqlProcName)
        {
            DataTable dt;
            try
            {
                SqlDataAdapter adp = new SqlDataAdapter(sqlProcName, conn());
                adp.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                adp.Fill(dt);
               
            }
            catch (Exception ee)
            {
                throw ee;
            }
            return dt;
        }

        // 带参数查询信息
        /// <summary>
        /// 带参数查询信息
        /// </summary>
        /// <param name="sqlProcName">存储过程名</param>
        /// <param name="param">参数</param>
        /// <returns>返回结果</returns>
        public DataTable Search(string sqlProcName, params SqlParameter[] param)
        {
                 DataTable dt;
                try
                {
                    SqlDataAdapter adp = new SqlDataAdapter(sqlProcName, conn());
                    adp.SelectCommand.CommandType = CommandType.StoredProcedure;
                    foreach (SqlParameter sp in param)
                    {
                        adp.SelectCommand.Parameters.Add(sp);
                    }
                    dt = new DataTable();
                    adp.Fill(dt);
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                return dt;
        }

        //不带参数的增、删、改
        /// <summary>
        /// 该方法用于执行对数据库的增、删、改
        /// </summary>
        /// <param name="sqlProcName">存储过程</param>
        /// <returns>返回是否执行成功</returns>
        public int Excute(string sqlProcName)
        {
            SqlConnection sqlcnn = conn();
            SqlCommand cmd = new SqlCommand(sqlProcName, sqlcnn);
            cmd.CommandType = CommandType.StoredProcedure;
           try
            {
                sqlcnn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                if (sqlcnn.State != ConnectionState.Closed)
                {
                    sqlcnn.Close();
                }
            }
        }

        //带参数的增、删、改
        /// <summary>
        /// 该方法用于执行对数据库的增、删、改
        /// </summary>
        /// <param name="sqlProcName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Excute(string sqlProcName, params SqlParameter[] param)
         {
           
             SqlConnection sqlcnn = conn();
             SqlCommand cmd = new SqlCommand(sqlProcName, sqlcnn); 
             cmd.CommandType = CommandType.StoredProcedure;
             try
             {
                 sqlcnn.Open();
                 foreach (SqlParameter sp in param)
                 {
                     cmd.Parameters.Add(sp);
                 }
                 return cmd.ExecuteNonQuery();
             }
             catch (SqlException e)
             {
                 throw e;
             }
             finally
             {
                 if (sqlcnn.State != ConnectionState.Closed)
                 {
                     sqlcnn.Close();
                 }
             }
        }

    }
}