dabase.cs

人力管理系统

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

namespace DataAccess
{
    public interface IDataAccess
    {
        DataTable ExecuteDateSetBySelect(string procName, params object[] ps);
        int ExecuteDataSetByUpdate(string procName, params object[] ps);
    }

    public sealed class SqlDataAccess : IDataAccess
    {
        private static SqlConnection mycon = null;
        private static readonly string StringConnection = ConfigurationManager.AppSettings["strCon"].ToString();
        private static void OpenConnection()
        {
            if (mycon == null)
            {
                mycon = new SqlConnection(StringConnection);
            }
            if (mycon.State == ConnectionState.Closed || mycon.State == ConnectionState.Broken)
            {
                mycon.Open();
            }
        }

        private SqlCommand CreateCmd(string procName, params object[] ps)
        {
            OpenConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = mycon;



            SqlParameter[] sqlpa = null;

            if (ps != null)
            {
                SqlCommandBuilder.DeriveParameters(cmd);
                cmd.Parameters.RemoveAt(0);
                sqlpa = new SqlParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(sqlpa, 0);
                for (int i = 0; i < sqlpa.Length; ++i)
                {
                    sqlpa[i].Value = ps[i];
                }
            }
            return cmd;

        }

        public DataTable ExecuteDateSetBySelect(string procName, params object[] ps)
        {
            using (SqlCommand cmd = CreateCmd(procName, ps))
            {
                using (SqlDataAdapter sqlad = new SqlDataAdapter())
                {
                    using (DataSet ds = new DataSet())
                    {
                        try
                        {
                            sqlad.SelectCommand = cmd;
                            sqlad.Fill(ds);
                        }
                        catch
                        {
                            return new DataTable();

                        }
                        return ds.Tables[0];
                    }
                }
            }
        }

        public DataTable ExecuteDataSetBySelect(string procName)
        {
            using (SqlCommand cmd = CreateCmd(procName, null))
            {
                using (SqlDataAdapter sqlda = new SqlDataAdapter())
                {
                    using (DataSet ds = new DataSet())
                    {
                        try
                        {
                            sqlda.SelectCommand = cmd;
                            sqlda.Fill(ds);
                        }
                        catch
                        {
                            return new DataTable();
                        }

                        return ds.Tables[0];
                    }
                }
            }
        }

        public int ExecuteDataSetByUpdate(string procName, params object[] ps)
        {
            int i = 0;
            using (SqlCommand cmd = CreateCmd(procName, ps))
            {
                try
                {
                    i = cmd.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                return i;
            }
        }

        public DataSet DataSetBySelect(string procName)
        {
            using (SqlCommand cmd = CreateCmd(procName, null))
            {
                using (SqlDataAdapter sqlda = new SqlDataAdapter())
                {
                    using (DataSet ds = new DataSet())
                    {
                        try
                        {
                            sqlda.SelectCommand = cmd;
                            sqlda.Fill(ds);
                        }
                        catch
                        {
                            return new DataSet();
                        }

                        return ds;
                    }
                }
            }
        }

    }

}