ElfinNet

 using System;
using System.Data;
using ElfinNet.Model;
using ElfinNet.IDAL;
using ElfinNet.DALFactory;

namespace ElfinNet.BLL
{

    /// <summary>
    /// BLL Layer For dbo.User.
    /// </summary>
    public class User
    {
  #region Constructor
  /// <summary>
  /// Default constructor
  /// </summary>
  public User()
  {
  }
  #endregion

        #region Interfaces
  
  /// <summary>
  /// Insert a new user record to database.
  /// </summary>
  /// <param name="oUserInfo"></param>
  /// <returns></returns>
  public int Insert(UserInfo oUserInfo)
  {
   // Validate input
   if (oUserInfo == null)
    return 0;

   // Insert an instance of the User DAL using the DALFactory
   IUser dal = DALFactory.User.Create();

   // Use the dal to insert a new record
   return dal.Insert(oUserInfo);
  }

        /// <summary>
  /// Gets a user record from the database.
  /// </summary>
  /// <param name="userName"></param>
  /// <returns></returns>
  public UserInfo Get(string userName)
  {
   // Validate input
   if(userName.Length==0)
    return null;

   // Get an instance of the User DAL using the DALFactory
   IUser dal = DALFactory.User.Create();

   // Use the dal to get a record
   return dal.Get(userName);
  }
  
  /// <summary>
  /// Gets all user records from the database.
  /// </summary>
  /// <returns></returns>
  public DataSet GetAll()
  {
   // Get an instance of the User DAL using the DALFactory
   IUser dal = DALFactory.User.Create();

   // Use the dal to get all records
   return dal.GetAll();
  }
   
  /// <summary>
  /// Update a user record from the database.
  /// </summary>
  /// <param name="oUserInfo"></param>
  public void Update(UserInfo oUserInfo)
  {
            // Validate input
   if (oUserInfo==null)
    return;

   // Get an instance of the User DAL using the DALFactory
   IUser dal = DALFactory.User.Create();

   // Use the dal to update a new record
   dal.Update(oUserInfo);
  }
  
  
  /// <summary>
  /// Delete a user record from the database.
  /// </summary>
     /// <param name="userName">The primary key value</param>
  /// <returns></returns>
  public int Delete(string userName)
  {
   // Validate input
   if(userName.Length==0)
    return 0;

   // Get an instance of the User DAL using the DALFactory
   IUser dal = DALFactory.User.Create();

   // Use the dal to delete a new record
   return dal.Delete(userName);
  }

        #endregion
  
  #region StaticMethods
  
  #endregion
    }
}

using System;
using System.Data;
using ElfinNet.Model;
using ElfinNet.IDAL;
using ElfinNet.DALFactory;

namespace ElfinNet.BLL
{
    /// <summary>
    /// BLL Layer For dbo.UserGroup.
    /// </summary>
    public class UserGroup
    {
  #region Constructor
  /// <summary>
  /// Default constructor
  /// </summary>
  public UserGroup()
  {
  }
  #endregion

        #region Interfaces
  
  /// <summary>
  /// Insert a new usergroup record to database.
  /// </summary>
  /// <param name="oUserGroupInfo"></param>
  /// <returns></returns>
  public int Insert(UserGroupInfo oUserGroupInfo)
  {
   // Validate input
   if (oUserGroupInfo == null)
    return 0;

   // Insert an instance of the UserGroup DAL using the DALFactory
   IUserGroup dal = DALFactory.UserGroup.Create();

   // Use the dal to insert a new record
   return dal.Insert(oUserGroupInfo);
  }

        /// <summary>
  /// Gets a usergroup record from the database.
  /// </summary>
  /// <param name="userGroupID"></param>
  /// <returns></returns>
  public UserGroupInfo Get(int userGroupID)
  {
   // Validate input
   if(userGroupID<0)
    return null;

   // Get an instance of the UserGroup DAL using the DALFactory
   IUserGroup dal = DALFactory.UserGroup.Create();

   // Use the dal to get a record
   return dal.Get(userGroupID);
  }
  
  /// <summary>
  /// Gets all usergroup records from the database.
  /// </summary>
  /// <returns></returns>
  public DataSet GetAll()
  {
   // Get an instance of the UserGroup DAL using the DALFactory
   IUserGroup dal = DALFactory.UserGroup.Create();

   // Use the dal to get all records
   return dal.GetAll();
  }
   
  /// <summary>
  /// Update a usergroup record from the database.
  /// </summary>
  /// <param name="oUserGroupInfo"></param>
  public void Update(UserGroupInfo oUserGroupInfo)
  {
            // Validate input
   if (oUserGroupInfo==null)
    return;

   // Get an instance of the UserGroup DAL using the DALFactory
   IUserGroup dal = DALFactory.UserGroup.Create();

   // Use the dal to update a new record
   dal.Update(oUserGroupInfo);
  }
  
  
  /// <summary>
  /// Delete a usergroup record from the database.
  /// </summary>
     /// <param name="userGroupID">The primary key value</param>
  /// <returns></returns>
  public int Delete(int userGroupID)
  {
   // Validate input
   if(userGroupID<0)
    return 0;

   // Get an instance of the UserGroup DAL using the DALFactory
   IUserGroup dal = DALFactory.UserGroup.Create();

   // Use the dal to delete a new record
   return dal.Delete(userGroupID);
  }

        #endregion
  
  #region StaticMethods
  
  #endregion
    }
}

using System;
using System.Web;

namespace ElfinNet.DALFactory
{
 /// <summary>
 /// Summary description for DataCache.
 /// </summary>
 public class DataCache
 {
  public DataCache()
  {
   //
   // TODO: Add constructor logic here
   //
  }

  /// <summary>
  ///
  /// </summary>
  /// <param name="CacheKey"></param>
  /// <returns></returns>
  public static object GetCache(string CacheKey)
  {

   System.Web.Caching.Cache objCache = HttpRuntime.Cache;
   return objCache[CacheKey];

  }

  /// <summary>
  ///
  /// </summary>
  /// <param name="CacheKey"></param>
  /// <param name="objObject"></param>
  public static void SetCache(string CacheKey, object objObject)
  {
   System.Web.Caching.Cache objCache = HttpRuntime.Cache;
   objCache.Insert(CacheKey, objObject);
  }
 }
}

using System;
using System.Reflection;
using ElfinNet.IDAL;

namespace ElfinNet.DALFactory
{
    /// <summary>
    /// DAL Factory Layer For dbo.User.
    /// </summary>
    public class User
    {
  private static readonly string path = System.Configuration.ConfigurationSettings.AppSettings["DAL"];
  
  private static object CreateObject(string path,string CacheKey)
  {   
   object objType = DataCache.GetCache(CacheKey);
   if (objType == null)
   {
    try
    {
     objType = Assembly.Load(path).CreateInstance(CacheKey);     
     DataCache.SetCache(CacheKey, objType);
    }
    catch{}

   }
   return objType;
  }
  
  public static IUser Create()
  {  
   //return (IUser)Assembly.Load(path).CreateInstance(path+".User");
  
   string CacheKey = path+".User"; 
   object objType=CreateObject(path,CacheKey);   
   return (IUser)objType;  
  }
  
    }
}

using System;
using System.Reflection;
using ElfinNet.IDAL;

namespace ElfinNet.DALFactory
{
    /// <summary>
    /// DAL Factory Layer For dbo.UserGroup.
    /// </summary>
    public class UserGroup
    {
  private static readonly string path = System.Configuration.ConfigurationSettings.AppSettings["DAL"];
  
  private static object CreateObject(string path,string CacheKey)
  {   
   object objType = DataCache.GetCache(CacheKey);
   if (objType == null)
   {
    try
    {
     objType = Assembly.Load(path).CreateInstance(CacheKey);     
     DataCache.SetCache(CacheKey, objType);
    }
    catch{}

   }
   return objType;
  }
  
  public static IUserGroup Create()
  {  
   //return (IUserGroup)Assembly.Load(path).CreateInstance(path+".UserGroup");
  
   string CacheKey = path+".UserGroup"; 
   object objType=CreateObject(path,CacheKey);   
   return (IUserGroup)objType;  
  }
  
    }
}

using System;
using System.Collections;
using System.Data;
using ElfinNet.Model;

namespace ElfinNet.IDAL
{

    /// <summary>
    /// Interface of Data Access Layer For dbo.User.
    /// </summary>
    public interface IUser
    {
  #region Interfaces
  /// <summary>
  /// Insert a new user record to database.
  /// </summary>
  int Insert(UserInfo oUserInfo);

        /// <summary>
  /// Gets a user record from the database.
  /// </summary>
  /// <param name="user"></param>
  /// <returns></returns>
  UserInfo Get(string userName);
  
  /// <summary>
  /// Gets all user records from the database.
  /// </summary>
  /// <param name="user"></param>
  /// <returns></returns>
  DataSet GetAll();
   
  /// <summary>
  /// Update a user record from the database.
  /// </summary>
  /// <param name="user"></param>
  /// <returns></returns>
  void Update(UserInfo oUserInfo);
  
  
  /// <summary>
  /// Delete a user record from the database.
  /// </summary>
     /// <param name="userName">The primary key value</param>
  int Delete(string userName);

        #endregion
  
  #region StaticMethods
  
  #endregion
    }
}

using System;
using System.Collections;
using System.Data;
using ElfinNet.Model;

namespace ElfinNet.IDAL
{

    /// <summary>
    /// Interface of Data Access Layer For dbo.UserGroup.
    /// </summary>
    public interface IUserGroup
    {
  #region Interfaces
  /// <summary>
  /// Insert a new usergroup record to database.
  /// </summary>
  int Insert(UserGroupInfo oUserGroupInfo);

        /// <summary>
  /// Gets a usergroup record from the database.
  /// </summary>
  /// <param name="usergroup"></param>
  /// <returns></returns>
  UserGroupInfo Get(int userGroupID);
  
  /// <summary>
  /// Gets all usergroup records from the database.
  /// </summary>
  /// <param name="usergroup"></param>
  /// <returns></returns>
  DataSet GetAll();
   
  /// <summary>
  /// Update a usergroup record from the database.
  /// </summary>
  /// <param name="usergroup"></param>
  /// <returns></returns>
  void Update(UserGroupInfo oUserGroupInfo);
  
  
  /// <summary>
  /// Delete a usergroup record from the database.
  /// </summary>
     /// <param name="userGroupID">The primary key value</param>
  int Delete(int userGroupID);

        #endregion
  
  #region StaticMethods
  
  #endregion
    }
}

// File: UserGroupInfo.cs
// 6/16/2006: Bryant    Original Version
//
// ===================================================================

using System;
//using System.Data;

namespace ElfinNet.Model
{
 /// <summary>
 /// <para>UserGroupInfo Object</para>
 /// <para>Summary description for UserGroupInfo.</para>
 /// <para><see cref="member"/></para>
 /// <remarks></remarks>
 /// </summary>
 [Serializable]
 public class UserGroupInfo
 {
  #region Fields
  private int _userGroupID;
  private string _name = String.Empty;
  private bool _active;
  #endregion
  
  #region Contructors
  public UserGroupInfo()
  {
  }
  
  public UserGroupInfo
  (
   int userGroupID,
   string name,
   bool active
  )
  {
   _userGroupID = userGroupID;
   _name        = name;
   _active      = active;
   
  }
  #endregion
  
  #region Public Properties
  
  public int UserGroupID
  {
   get {return _userGroupID;}
   set {_userGroupID = value;}
  }

  public string Name
  {
   get {return _name;}
   set {_name = value;}
  }

  public bool Active
  {
   get {return _active;}
   set {_active = value;}
  }
  #endregion
 }
}

// File: UserInfo.cs
// 6/16/2006: Bryant    Original Version
//
// ===================================================================

using System;
//using System.Data;

namespace ElfinNet.Model
{
 /// <summary>
 /// <para>UserInfo Object</para>
 /// <para>Summary description for UserInfo.</para>
 /// <para><see cref="member"/></para>
 /// <remarks></remarks>
 /// </summary>
 [Serializable]
 public class UserInfo
 {
  #region Fields
  private string _userName;
  private string _password = String.Empty;
  private string _email = String.Empty;
  private int _userGroup;
  private bool _active;
  #endregion
  
  #region Contructors
  public UserInfo()
  {
  }
  
  public UserInfo
  (
   string name,
   string password,
   string email,
   int userGroup,
   bool active
  )
  {
   _userName      = name;
   _password  = password;
   _email     = email;
   _userGroup = userGroup;
   _active    = active;
   
  }
  #endregion
  
  #region Public Properties
  
  public string UserName
  {
   get {return _userName;}
   set {_userName = value;}
  }

  public string Password
  {
   get {return _password;}
   set {_password = value;}
  }

  public string Email
  {
   get {return _email;}
   set {_email = value;}
  }

  public int UserGroup
  {
   get {return _userGroup;}
   set {_userGroup = value;}
  }

  public bool Active
  {
   get {return _active;}
   set {_active = value;}
  }
  #endregion
 }
}

using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

namespace ElfinNet.SQLServerDAL
{
 /// <summary>
 /// Summary description for SQLUtil.
 /// </summary>
 public class SQLUtil
 {
  
  #region Class Declarations
  private string elfinNetDBConnString = System.Configuration.ConfigurationSettings.AppSettings["ElfinNetDBConnString"];

  SqlConnection conn;
  SqlCommand cmd;
  private bool m_bTrapError = true;
  #endregion
  
  #region DatabaseType enum
  public enum DatabaseType
  {
   ElfinNetDB = 0
  }
  #endregion

  #region ParameterType
  //this will need to be updated with additional parameter types.
  public enum ParameterType
  {
   Integer = 1,
   Varchar = 2,
   Money = 3,
   Bit = 4,
   Char = 5,
   Decimal = 6,
   SmallDateTime = 7
  }
  
  public SqlDbType GetParameterType(ParameterType enumParamType)
  {
   switch(enumParamType)
   {
    case ParameterType.Integer:
     return SqlDbType.Int;
    case ParameterType.Varchar:
     return SqlDbType.VarChar;
    case ParameterType.Money:
     return SqlDbType.Money;
    case ParameterType.Bit:
     return SqlDbType.Bit;
    case ParameterType.Char:
     return SqlDbType.Char;
    case ParameterType.Decimal:
     return SqlDbType.Decimal;
    case ParameterType.SmallDateTime:
     return SqlDbType.SmallDateTime;
    default:
     return SqlDbType.Int;
   }
  }

  #endregion
 
  #region Constructor
  public SQLUtil()
  {
   cmd = new SqlCommand();
   cmd.CommandTimeout = 36000;
  }
  #endregion

  #region TrapError
  public bool TrapError {get{return m_bTrapError;}set{m_bTrapError=value;}}
  #endregion
  
  #region GetConnection
  private SqlConnection GetConnection(DatabaseType db)
  {
   if (conn == null)
   {
    conn = new SqlConnection();
    if (db == DatabaseType.ElfinNetDB)
    {
     conn.ConnectionString = elfinNetDBConnString;
    }

    try
    {
     conn.Open();
    }
    catch(Exception er)
    {
     //ErrorMessages.SendErrorAlert(er.Message);
     throw er;
    }
   }
   return conn;
  }
  #endregion
  
  #region AddParameter
  public void AddParameter(string paramName, double val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Decimal;
   param.Precision=18;
   param.Scale=4;
   param.Value = val;

   cmd.Parameters.Add(param);
  }

  public void AddParameter(string paramName, double val,int precision,int scale)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Decimal;
   param.Precision= Convert.ToByte(precision);
   param.Scale= Convert.ToByte(scale);
   param.Value = val;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, decimal val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Decimal;
   param.Precision=18;
   param.Scale=4;
   param.Value = val;

   cmd.Parameters.Add(param);
  }

  public void AddTextParameter(string paramName, string val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Text;
   param.Size = val.Length;
   param.Value = val;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, DateTime _val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.DateTime;
   param.Value = _val;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, DateTime _val, bool isSmallDateTime)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.SmallDateTime;
   param.Value = _val;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, string val, int size)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.VarChar;
   param.Size = size;
   param.Value = val;

   cmd.Parameters.Add(param);
  }

  public void AddParameter(string paramName, int val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Int;
   param.Value = val;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, long val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.BigInt;
   param.Value = val;

   cmd.Parameters.Add(param);
  }  
  public void AddParameter(string paramName, bool val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.TinyInt;
   param.Value = val ? 1 : 0;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, string val, int size, bool isChar)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Char;
   param.Size = size;
   param.Value = val;

   cmd.Parameters.Add(param);
  }

  //public void AddParameter(string paramName, decimal val, int size, byte precision)
  public void AddParameter(string paramName, decimal val, byte precision, byte scale)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Decimal;
   //param.Size = size;
   //param.Precision = precision;
   param.Precision = precision;
   param.Scale = scale;
   param.Value = val;

   cmd.Parameters.Add(param);
  }
  public void AddParameter(string paramName, double val, bool isMoney)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   if (isMoney)
   {
    param.SqlDbType = SqlDbType.Money;
   }
   else
   {
    param.SqlDbType =SqlDbType.SmallMoney;
   }
   param.Value = val;

   cmd.Parameters.Add(param);
  }

  public void AddParameter(string paramName, Guid val)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.UniqueIdentifier;
   param.Value = val;

   cmd.Parameters.Add(param);
  }

  public void AddCharParameter(string paramName, string val, int size, bool isChar)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Input;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.Char;
   param.Size = size;
   param.Value = val;

   cmd.Parameters.Add(param);
  }
  #endregion

  #region ClearParameters
  public void ClearParameters()
  {
   cmd.Parameters.Clear();
  }
  #endregion

  #region AddOutputParameter
  public void AddOutputParameter(string paramName)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Output;
   param.ParameterName = paramName;
   param.SqlDbType = SqlDbType.VarChar;
   param.Size = 100;
   
   cmd.Parameters.Add(param);
  }
  public void AddOutputParameter(string paramName, ParameterType enumParamType, int size)
  {
   SqlParameter param = new SqlParameter();
   param.Direction = ParameterDirection.Output;
   param.ParameterName = paramName;
   param.SqlDbType = GetParameterType(enumParamType);
   if (size>0) {param.Size = size;}
   
   cmd.Parameters.Add(param);
  }
  #endregion

  #region GetParameterValue
  public string GetParameterValue(string paramName)
  {
   if(cmd.Parameters[paramName] != null)
    return cmd.Parameters[paramName].Value.ToString();
   else
    return "";
  }
  #endregion

  #region Close
  public void Close()
  {
   if(cmd.Connection.State!=System.Data.ConnectionState.Closed) cmd.Connection.Close();
  }
  #endregion

  #region ExecuteReader
  public XmlReader ExecuteReader(string storedProc, DatabaseType enumDBType)
  {
   cmd.Connection = GetConnection(enumDBType);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProc;
   
   try
   {
    return cmd.ExecuteXmlReader();
   }
   catch (Exception er)
   {
    throw er;
   }
   finally
   {
    cmd.Dispose();
    conn.Dispose();
   }
  }
  #endregion
  
  #region ExecuteDataReader
  public SqlDataReader ExecuteDataReader(string storedProc, DatabaseType enumDBType)
  {
   cmd.Connection = GetConnection(enumDBType);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProc;
   
   try
   {
    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
   }
   catch (Exception er)
   {
    cmd.Dispose();
    conn.Dispose();
    throw er;
   }
  }
  #endregion

  #region ExecuteScalar
  public object ExecuteScalar(string storedProc, DatabaseType enumDBType)
  {
   cmd.Connection = GetConnection(enumDBType);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProc;
   
   try
   {
    return cmd.ExecuteScalar();
   }
   catch (Exception er)
   {
    throw er;
   }
   finally
   {
    cmd.Dispose();
    conn.Dispose();
   }
  }
  #endregion

  #region ExecuteNonQuery
  public int ExecuteNonQuery(string storedProc, DatabaseType enumDBType)
  {
   cmd.Connection = GetConnection(enumDBType);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProc;
   
   try
   {
    return cmd.ExecuteNonQuery();
   }
   catch(Exception er)
   {
    throw er;
   }
   finally
   {
    cmd.Dispose();
    conn.Dispose();
   }
  }
  #endregion

  #region ExecuteDataSet
  public DataSet ExecuteDataSet(string storedProc, DatabaseType enumDBType)
  {
   cmd.Connection = GetConnection(enumDBType);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProc;
   SqlDataAdapter daGetData = new SqlDataAdapter(cmd);
   DataSet dsReturnData = new DataSet();

 
   try
   {
    daGetData.Fill(dsReturnData);
    return dsReturnData;
   }
   catch (Exception er)
   {
    throw er;
   }
   finally
   {
    cmd.Dispose();
    conn.Dispose();
   }

  }
  #endregion

  #region ExecuteScalarWithReturn
  public string ExecuteScalarWithReturn(string storedProc, string paramName, DatabaseType enumDBType)
  {
   cmd.Connection = GetConnection(enumDBType);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProc;

   
   try
   {
    cmd.ExecuteScalar();
    return GetParameterValue(paramName);
   }
   catch (Exception er)
   {
    throw er;
   }
   finally
   {
    cmd.Dispose();
    conn.Dispose();
   }
  }
  #endregion
 }
}

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ElfinNet.Model;
using ElfinNet.IDAL;

namespace ElfinNet.SQLServerDAL
{
    /// <summary>
    /// Data Access Layer For dbo.User.
    /// </summary>
    public class User : IUser
    {
  #region Constructor
  /// <summary>
  /// Default constructor
  /// </summary>
  public User()
  {
  }
  #endregion

        #region Interfaces
  
  /// <summary>
  /// Insert a new user record to database.
  /// </summary>
  /// <param name="oUserInfo"></param>
  /// <returns></returns>
  public int Insert(UserInfo oUserInfo)
  {
   SQLUtil db = new SQLUtil();
   int identityKey = 0;
   DateTime deadDate = new DateTime();
   
   db.AddOutputParameter("@UserName");
   db.AddParameter("@Password",oUserInfo.Password,50);
   if(oUserInfo.Email!=null&&oUserInfo.Email.Length>0){db.AddParameter("@Email",oUserInfo.Email,50);}
   if(oUserInfo.UserGroup.ToString()!=null&&oUserInfo.UserGroup>0){db.AddParameter("@UserGroup",oUserInfo.UserGroup);}
   db.AddParameter("@Active",oUserInfo.Active);

   identityKey = Convert.ToInt32(db.ExecuteScalarWithReturn("USP_UserInsert", "@UserName", SQLUtil.DatabaseType.ElfinNetDB));
   return identityKey;
  }

        /// <summary>
  /// Gets a user record from the database.
  /// </summary>
  /// <param name="userName"></param>
  /// <returns></returns>
  public UserInfo Get(string userName)
  {
   SQLUtil db = new SQLUtil();
   UserInfo oUserInfo = new UserInfo();

   db.AddParameter("@UserName", userName, 20);
   
   using (SqlDataReader rdr = db.ExecuteDataReader("USP_UserGet", SQLUtil.DatabaseType.ElfinNetDB))
   {
    rdr.Read();
    try{oUserInfo.UserName = rdr.GetString(rdr.GetOrdinal("UserName"));}
    catch{}
    try{oUserInfo.Password = rdr.GetString(rdr.GetOrdinal("Password"));}
    catch{}
    try{oUserInfo.Email = rdr.GetString(rdr.GetOrdinal("Email"));}
    catch{}
    try{oUserInfo.UserGroup = rdr.GetInt32(rdr.GetOrdinal("UserGroup"));}
    catch{}
    try{oUserInfo.Active = rdr.GetBoolean(rdr.GetOrdinal("Active"));}
    catch{}
   }
   
   return oUserInfo;
  }
  
  /// <summary>
  /// Gets all user records from the database.
  /// </summary>
  /// <returns></returns>
  public DataSet GetAll()
  {
   DataSet ds = new DataSet();
   SQLUtil db = new SQLUtil();
   ds = db.ExecuteDataSet("USP_UserGetAll", SQLUtil.DatabaseType.ElfinNetDB);
   return ds;
  }
   
  /// <summary>
  /// Update a user record from the database.
  /// </summary>
  /// <param name="oUserInfo"></param>
  public void Update(UserInfo oUserInfo)
  {
            SQLUtil db = new SQLUtil();
   DateTime deadDate = new DateTime();
   
   db.AddOutputParameter("@UserName");
   db.AddParameter("@Password",oUserInfo.Password,50);
   if(oUserInfo.Email!=null&&oUserInfo.Email.Length>0){db.AddParameter("@Email",oUserInfo.Email,50);}
   if(oUserInfo.UserGroup.ToString()!=null&&oUserInfo.UserGroup>0){db.AddParameter("@UserGroup",oUserInfo.UserGroup);}
   db.AddParameter("@Active",oUserInfo.Active);
   
   db.ExecuteScalar("USP_UserUpdate", SQLUtil.DatabaseType.ElfinNetDB);
  }
  
  
  /// <summary>
  /// Delete a user record from the database.
  /// </summary>
     /// <param name="userName">The primary key value</param>
  /// <returns></returns>
  public int Delete(string userName)
  {
   SQLUtil db = new SQLUtil();
   db.AddParameter("@UserName", userName, 20);
   return db.ExecuteNonQuery("USP_UserDelete",SQLUtil.DatabaseType.ElfinNetDB);
  }

        #endregion
  
  #region StaticMethods
  
  #endregion
    }
}

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ElfinNet.Model;
using ElfinNet.IDAL;

namespace ElfinNet.SQLServerDAL
{
    /// <summary>
    /// Data Access Layer For dbo.UserGroup.
    /// </summary>
    public class UserGroup : IUserGroup
    {
  #region Constructor
  /// <summary>
  /// Default constructor
  /// </summary>
  public UserGroup()
  {
  }
  #endregion

        #region Interfaces
  
  /// <summary>
  /// Insert a new usergroup record to database.
  /// </summary>
  /// <param name="oUserGroupInfo"></param>
  /// <returns></returns>
  public int Insert(UserGroupInfo oUserGroupInfo)
  {
   SQLUtil db = new SQLUtil();
   int identityKey = 0;
   DateTime deadDate = new DateTime();
   
   db.AddOutputParameter("@UserGroupID");
   db.AddParameter("@Name",oUserGroupInfo.Name,50);
   db.AddParameter("@Active",oUserGroupInfo.Active);

   identityKey = Convert.ToInt32(db.ExecuteScalarWithReturn("USP_UserGroupInsert", "@UserGroupID", SQLUtil.DatabaseType.ElfinNetDB));
   return identityKey;
  }

        /// <summary>
  /// Gets a usergroup record from the database.
  /// </summary>
  /// <param name="userGroupID"></param>
  /// <returns></returns>
  public UserGroupInfo Get(int userGroupID)
  {
   SQLUtil db = new SQLUtil();
   UserGroupInfo oUserGroupInfo = new UserGroupInfo();

   db.AddParameter("@UserGroupID", userGroupID);
   
   using (SqlDataReader rdr = db.ExecuteDataReader("USP_UserGroupGet", SQLUtil.DatabaseType.ElfinNetDB))
   {
    rdr.Read();
    try{oUserGroupInfo.UserGroupID = rdr.GetInt32(rdr.GetOrdinal("UserGroupID"));}
    catch{}
    try{oUserGroupInfo.Name = rdr.GetString(rdr.GetOrdinal("Name"));}
    catch{}
    try{oUserGroupInfo.Active = rdr.GetBoolean(rdr.GetOrdinal("Active"));}
    catch{}
   }
   
   return oUserGroupInfo;
  }
  
  /// <summary>
  /// Gets all usergroup records from the database.
  /// </summary>
  /// <returns></returns>
  public DataSet GetAll()
  {
   DataSet ds = new DataSet();
   SQLUtil db = new SQLUtil();
   ds = db.ExecuteDataSet("USP_UserGroupGetAll", SQLUtil.DatabaseType.ElfinNetDB);
   return ds;
  }
   
  /// <summary>
  /// Update a usergroup record from the database.
  /// </summary>
  /// <param name="oUserGroupInfo"></param>
  public void Update(UserGroupInfo oUserGroupInfo)
  {
            SQLUtil db = new SQLUtil();
   DateTime deadDate = new DateTime();
   
   db.AddOutputParameter("@UserGroupID");
   db.AddParameter("@Name",oUserGroupInfo.Name,50);
   db.AddParameter("@Active",oUserGroupInfo.Active);
   
   db.ExecuteScalar("USP_UserGroupUpdate", SQLUtil.DatabaseType.ElfinNetDB);
  }
  
  
  /// <summary>
  /// Delete a usergroup record from the database.
  /// </summary>
     /// <param name="userGroupID">The primary key value</param>
  /// <returns></returns>
  public int Delete(int userGroupID)
  {
   SQLUtil db = new SQLUtil();
   db.AddParameter("@UserGroupID", userGroupID);
   return db.ExecuteNonQuery("USP_UserGroupDelete",SQLUtil.DatabaseType.ElfinNetDB);
  }

        #endregion
  
  #region StaticMethods
  
  #endregion
    }
}