DataAccess

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Configuration;
 
DataAddUpdateDelete.Data_Insert(Convert.ToInt32(strGrantXMLID),
	strGrantName,
	strOrganization,
	dtYear,
	dAmount,
	intDuration,
	strPrimaryNTEE,
	strSpecificNTEE,
	strAddress1 + " " + strAddress2,
	strCity,
	strState,
	strZipCode,
	strPhone,
	"0",
	System.DateTime.Now,
	"0",
	System.DateTime.Now);
 
 
namespace DataAccess
{
    public static class DataSelection
    {
 
        /// <summary>
        /// Executes a stored procedure anda returns a datatable
        /// </summary>
        /// <param name="sp">This must be a stored procedure</param>
        /// <returns>Data Table</returns>
        public static DataTable ReturnDataTable(string sp)
        {
            return ReturnDataTable(sp, null);
        }
 
        /// <summary>
        /// Executes a stored procedure anda returns a datatable
        /// </summary>
        /// <param name="sp">This must be a stored procedure</param>
        /// <param name="Parameters">parameters to pass to stored procedure</param>
        /// <returns>Data Table</returns>
        public static DataTable ReturnDataTable(string sp, ListDictionary Parameters)
        {
            SqlConnection cnDBConn = new SqlConnection(ConfigurationSettings.AppSettings["DBConn"]);
            SqlCommand cmdSPCommand = new SqlCommand();
 
            try
            {
                cnDBConn.Open();
                cmdSPCommand.Connection = cnDBConn;
 
                cmdSPCommand.CommandText = sp;
                cmdSPCommand.CommandType = CommandType.StoredProcedure;
                IDataParameter p;
 
                if (Parameters != null)
                {
                    foreach (System.Collections.DictionaryEntry param in Parameters)
                    {
                        p = param.Key as IDataParameter;
 
                        if (null == p)
                        {
                            p.ParameterName = (string)param.Key;
                            p.Value = param.Value;
                        }
                        else
                        {
                            p.Value = param.Value;
                        }
 
                        cmdSPCommand.Parameters.Add(p);
                    }
                }
                SqlDataAdapter daAdapter = new SqlDataAdapter(cmdSPCommand);
 
                DataTable dt = new DataTable();
                daAdapter.Fill(dt);
 
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cnDBConn.State == ConnectionState.Open)
                    cnDBConn.Close();
            }
 
        }
    }
 
 
    /// <summary>
    /// This class contains all insert stored procedures
    /// </summary>
    public static class DataAddUpdateDelete
    {
 
        public static void Data_Insert(int GrantID, 
            string Grant_Name,
            string Organization,
            DateTime Year,
            decimal Amount,
            int Duration,
            string Primary_NTEE,
            string Specific_NTEE,
            string Address,
            string City,
            string State,
            string Zipcode,
            string Phone,
            string CreatedBy,
            DateTime CreatedDate,
            string ModifiedBy,
            DateTime ModifiedDate
            )
        {
            ListDictionary parameters = new ListDictionary();
 
            parameters.Add(new SqlParameter("@GrantID", SqlDbType.Int, 0), GrantID);
            parameters.Add(new SqlParameter("@Grant_Name", SqlDbType.NVarChar, 128), Grant_Name);
            parameters.Add(new SqlParameter("@Organization", SqlDbType.NVarChar, 128), Organization);
            parameters.Add(new SqlParameter("@Year", SqlDbType.DateTime, 0), Year);
            parameters.Add(new SqlParameter("@Amount", SqlDbType.Money, 0), Amount);
            parameters.Add(new SqlParameter("@Duration", SqlDbType.Int, 0), Duration);
            parameters.Add(new SqlParameter("@Primary_NTEE", SqlDbType.NVarChar, 512), Primary_NTEE);
            parameters.Add(new SqlParameter("@Specific_NTEE", SqlDbType.NVarChar, 512), Specific_NTEE);
            parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 512), Address);
            parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 128), City);
            parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar, 64), State);
            parameters.Add(new SqlParameter("@Zipcode", SqlDbType.NVarChar, 32), Zipcode);
            parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 32), Phone);
            parameters.Add(new SqlParameter("@URL", SqlDbType.NVarChar, 32), "");
            parameters.Add(new SqlParameter("@CreatedBy", SqlDbType.NVarChar, 128), CreatedBy);
            parameters.Add(new SqlParameter("@CreatedDate", SqlDbType.DateTime, 0), CreatedDate);
            parameters.Add(new SqlParameter("@ModifiedBy", SqlDbType.NVarChar, 128), ModifiedBy);
            parameters.Add(new SqlParameter("@ModifiedDate", SqlDbType.DateTime, 0), ModifiedDate);
 
            ExecuteStoredProcedure("sp_Insert", parameters,false);
 
        }
 
        /// <summary>
        /// LoadFromSqlReader does not load data into your BusinessEntity
        /// </summary>
        /// <param name="sp">This must be a stored procedure</param>
        /// <returns>The new Key field ID</returns>
        public static int ExecuteStoredProcedure(string sp)
        {
            return ExecuteStoredProcedure(sp, null, CommandType.StoredProcedure, false);
        }
 
        /// <summary>
        /// This version allows you to pass in Parameters and thier values
        /// </summary>
        /// <param name="sp">This must be a stored procedure</param>
        /// <param name="Parameters">Two types of key/value pairs are allowed</param>
        /// <param name="hasReturnValue">Indicates whether the item has a return value</param>
        /// <returns>The new Key field ID</returns>
        public static int ExecuteStoredProcedure(string sp, ListDictionary Parameters, bool hasReturnValue)
        {
            return ExecuteStoredProcedure(sp, Parameters, CommandType.StoredProcedure, hasReturnValue);
        }
 
 
        /// <summary>
        /// This version allow you to use direct sql.
        /// </summary>
        /// <param name="sp">This must be a stored procedure</param>
        /// <param name="Parameters">Two types of key/value pairs are allowed, see <see cref="LoadFromSql"/></param>
        /// <param name="commandType">This property determines the type being passed in the "sp" parameter</param>
        /// <param name="hasReturnValue">This boolean value indicates whether the call should return a key value from the call</param>
        /// <returns>The new Key field ID</returns>
        public static int ExecuteStoredProcedure(string sp, ListDictionary Parameters, CommandType commandType, bool hasReturnValue)
        {
            int intReturn = -1;
            IDbCommand cmd;
            cmd = new SqlCommand() as IDbCommand;
            cmd.Connection = new SqlConnection() as IDbConnection;
 
            try
            {
                cmd.CommandText = sp;
                cmd.CommandType = commandType;
                IDataParameter p;
 
                if (Parameters != null)
                {
                    foreach (System.Collections.DictionaryEntry param in Parameters)
                    {
                        p = param.Key as IDataParameter;
 
                        if (null == p)
                        {
                            p.ParameterName = (string)param.Key;
                            p.Value = param.Value;
                        }
                        else
                        {
                            p.Value = param.Value;
                        }
 
                        cmd.Parameters.Add(p);
                    }
                }
 
                if (hasReturnValue)
                {
                    SqlParameter prmReturn = new SqlParameter("@Return", SqlDbType.Int);
                    prmReturn.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(prmReturn);
                }
 
                cmd.Connection.ConnectionString = ConfigurationSettings.AppSettings["DBConn"];
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
 
                if (hasReturnValue)
                {
                    // Get the out parameters
                    SqlParameter prmReturn = (SqlParameter)cmd.Parameters["@Return"];
                    intReturn = Convert.ToInt32(prmReturn.Value);
                }
 
                return intReturn;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
            }
        }
 
    }
}