ASP.NET DataBase

21738 ワード

//     
    public class DataBase
    {
        private SqlConnection conn;//       

        #region        
        private void Open()
        {
            if (conn == null)
            {
                conn = new SqlConnection();
                //conn.ConnectionString = ConfigurationSettings.AppSettings["connString"].ToString();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ToString();
                conn.Open();
            }
            else
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
            }
        }
        #endregion

        #region        
        private void Close()
        {
            if (conn != null && conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
        #endregion

        #region       
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
        #endregion

        #region         SqlParameter  
        /// <summary>
        ///     
        /// </summary>
        /// <param name="ParamName">           </param>
        /// <param name="DbType">    </param></param>
        /// <param name="Size">    </param>
        /// <param name="Value">   </param>
        /// <returns>   parameter   </returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, Object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        ///       
        /// </summary>
        /// <param name="ParamName">           </param>
        /// <param name="DbType">    </param>
        /// <param name="Size">    </param>
        /// <param name="Direction">    </param>
        /// <param name="Value">   </param>
        /// <returns>   parameter   </returns>
        private SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size>0)
            {
                param = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                param = new SqlParameter(ParamName, DbType);
            }
            param.Direction = Direction;
            if (!(param.Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }

            return param;
        }
        #endregion

        #region         (         )

        /// <summary>
        ///     
        /// </summary>
        /// <param name="procName">    </param>
        /// <param name="prams">    </param>
        /// <returns></returns>
        public int RunProc(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            this.Close();
            //          
            return (int)cmd.Parameters["ReturnValue"].Value;
        }

        /// <summary>
        ///     SQL  
        /// </summary>
        /// <param name="procName">    </param>
        /// <returns></returns>
        public int RunProc(string procName)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            int num = cmd.ExecuteNonQuery();
            this.Close();
            return num;
        }
        
        #endregion

        #region         (    )

        /// <summary>
        ///         ,    DataSet   
        /// </summary>
        /// <param name="procName">    </param>
        /// <param name="prams">    </param>
        /// <param name="tbName">     </param>
        /// <returns></returns>
        public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, prams);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            this.Close();
            return ds;
        }

        /// <summary>
        ///       ,    DataSet   
        /// </summary>
        /// <param name="procName">    </param>
        /// <param name="tbName">     </param>
        /// <returns>DataSet</returns>
        public DataSet RunProcReturn(string procName, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, null);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            return ds;
        }

        #endregion

        #region         SqlDataAdapter

        /// <summary>
        ///     SqlDataAdapter           
        /// </summary>
        /// <param name="procName">    </param>
        /// <param name="prams">    </param>
        /// <returns></returns>
        private SqlDataAdapter CreateDataAdapter(string procName, SqlParameter[] prams)
        {
            this.Open();
            SqlDataAdapter dap = new SqlDataAdapter(procName, conn);
            dap.SelectCommand.CommandType = CommandType.Text;//
            if (prams != null)
            {
                foreach (SqlParameter param in prams)
                {
                    dap.SelectCommand.Parameters.Add(param);//          
                }
            }

            //     
            dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                String.Empty, DataRowVersion.Default, null));

            return dap;
        }

        #endregion

        #region         SqlCommand

        /// <summary>
        ///     SqlCommand           
        /// </summary>
        /// <param name="procName">    </param>
        /// <param name="prams"        </param>
        /// <returns>  SqlCommand  </returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.Text;////           
            if (prams != null)
            {
                foreach (SqlParameter param in prams)
                {
                    cmd.Parameters.Add(param);
                }
            }

            //      
            cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return cmd;
        }

        #endregion
    }