This code is part of an application that demonstrates our adopted approach to the separation of GUI and database. The EntityInfo tier is used as a container to carry data objects in and out of the DAL as well as holding the attribute data for BLL objects.
This code is the main DTO tier dealing with saving and reading XML files.
using System;
using System.IO;
using System.Text;
using System.Xml;
using System.Data;
using System.Collections.Generic;
using System.Xml.Serialization;
using System.Data.SqlClient;
using System.Configuration;
using EntityInfo;
namespace DataAccess
{
public class UserDB
{
#region private members
private static string FilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
private static string DataStore = ConfigurationManager.AppSettings[“DataStore”];
private static string xmlFile = ConfigurationManager.AppSettings[“XmlData”];
private static string ConnectionString = ConfigurationManager.ConnectionStrings[“SQLConnString”].ConnectionString;
#endregion private members
// constructor
public UserDB()
{
}
#region XML Access
///// <summary>
///// Serialization – Saves the XML to disk.
///// </summary>
///// <param name=”userList”>The user list.</param>
//public void SaveXmlToDisk(UserList userList)
//{
// XmlSerializer s = new XmlSerializer(typeof(UserList));
// TextWriter w = new StreamWriter(xmlFile);
// s.Serialize(w, userList);
// w.Close();
//}
/// <summary>
/// Serialization – Writes the XML to disk.
/// </summary>
public void SaveXmlToDisk(UserList userList)
{
if (userList == null || userList.Count < 1)
{
throw new ArgumentException(“The collection must contain data”);
}
try
{
if (File.Exists(xmlFile))
{
File.Delete(xmlFile);
}
}
catch (IOException)
{
throw;
}
XmlSerializer xser = new XmlSerializer(userList.GetType());
using (TextWriter writer = new StreamWriter(xmlFile))
{
// serialize without the default namespace
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add(“”, “”);
xser.Serialize(writer, userList, ns);
}
}
///// <summary>
///// Deserialization – Reads the XML from disk.
///// </summary>
//public UserList ReadXmlFromDisk()
//{
// UserList userList;
// //TODO: try catch
// TextReader r = new StreamReader(xmlFile);
// XmlSerializer s = new XmlSerializer(typeof(UserList));
// userList = (UserList)s.Deserialize(r);
// r.Close();
// return userList;
//}
/// <summary>
/// Deserialization – Reads the XML from disk.
/// </summary>
public UserList ReadXmlFromDisk()
{
UserList userList = new UserList();
userList = null;
if (!File.Exists(xmlFile))
{
// if creating a new file, insert at least one record
throw new IOException(“The xml file specified in App.config must exist”);
}
if (File.Exists(xmlFile))
{
using (XmlTextReader xr = new XmlTextReader(xmlFile))
{
XmlSerializer xs = new XmlSerializer(typeof(UserList));
userList = (UserList)xs.Deserialize(xr);
}
}
return userList;
}
public UserInfo Load(string login, string password)
{
UserInfo userInfo = null;
UserList userList = ReadXmlFromDisk();
foreach (UserInfo user in userList)
{
if (user.Login == login && user.Password == password)
{
userInfo = user;
}
}
return userInfo;
}
#endregion XML Access
#region SQL Access
/// <summary>
/// Write to the database
/// </summary>
/// <returns>An error string, if any</returns>
public void WriteToDB(UserInfo userInfo)
{
if (userInfo == null)
{
throw new ArgumentException(“No user was supplied”);
}
try
{
SqlParameter[] paramsToStore = new SqlParameter[9];
SetParameters(userInfo, paramsToStore);
SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, “pUsers_i”, paramsToStore);
}
catch (SqlException)
{
throw;
}
}
private static void SetParameters(UserInfo userInfo, SqlParameter[] paramsToStore)
{
paramsToStore[0] = new SqlParameter(“@Login”, SqlDbType.NVarChar);
paramsToStore[0].Value = userInfo.Login;
paramsToStore[1] = new SqlParameter(“@Password”, SqlDbType.NVarChar);
paramsToStore[1].Value = userInfo.Password;
paramsToStore[2] = new SqlParameter(“@FirstName”, SqlDbType.NVarChar);
paramsToStore[2].Value = userInfo.FirstName;
paramsToStore[3] = new SqlParameter(“@LastName”, SqlDbType.NVarChar);
paramsToStore[3].Value = userInfo.LastName;
paramsToStore[4] = new SqlParameter(“@Address”, SqlDbType.NVarChar);
paramsToStore[4].Value = userInfo.Address;
paramsToStore[5] = new SqlParameter(“@City”, SqlDbType.NVarChar);
paramsToStore[5].Value = userInfo.City;
paramsToStore[6] = new SqlParameter(“@State”, SqlDbType.NVarChar);
paramsToStore[6].Value = userInfo.State;
paramsToStore[7] = new SqlParameter(“@Zip”, SqlDbType.NVarChar);
paramsToStore[7].Value = userInfo.Zip;
paramsToStore[8] = new SqlParameter(“@Telephone”, SqlDbType.NVarChar);
paramsToStore[8].Value = userInfo.Phone;
}
/// <summary>
/// Function to get a list from the database
/// </summary>
/// <returns>A Generic List of UserInfo</returns>
public UserList ReadFromDB()
{
UserInfo userInfo = new UserInfo();
UserList userList = new UserList();
using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, “pUsers_l”, null))
{
// Scroll through the results
while (rdr.Read())
{
userInfo = PopulateItem(rdr);
userList.Add(userInfo);
}
}
return userList;
}
/// <summary>
/// Get an individual item
/// </summary>
/// <param name=”itemId”>unique key</param>
/// <returns>Details about the Item</returns>
public UserInfo GetUser(string login, string password)
{
UserInfo userInfo = new UserInfo();
//Create a parameter
SqlParameter[] paramsToStore = new SqlParameter[2];
paramsToStore[0] = new SqlParameter(“@Login”, SqlDbType.NVarChar);
paramsToStore[0].Value = login;
paramsToStore[1] = new SqlParameter(“@Password”, SqlDbType.NVarChar);
paramsToStore[1].Value = password;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, “pUsers_l”, null))
{
// Scroll through the results
while (rdr.Read())
{
userInfo = PopulateItem(rdr);
}
}
return userInfo;
}
private static UserInfo PopulateItem(SqlDataReader rdr)
{
UserInfo userInfo = new UserInfo();
userInfo.Login = rdr[“Login”].ToString();
userInfo.Password = rdr[“Password”].ToString();
userInfo.FirstName = rdr[“FirstName”].ToString();
userInfo.LastName = rdr[“LastName”].ToString();
userInfo.Address = rdr[“Address”].ToString();
userInfo.City = rdr[“City”].ToString();
userInfo.State = rdr[“State”].ToString();
userInfo.Zip = rdr[“Zip”].ToString();
userInfo.Phone = rdr[“Telephone”].ToString();
return userInfo;
}
#endregion SQL Access
}
}
