public Dictionary <int, string> GetSalesDepartmentList()
{
DBAccess dbAccess = new FuelCenterDB.DBAccess();
Dictionary <int, string> dictSalesDept = new Dictionary <int, string>();
string storedProcedureName = "uspGetSalesDeptList";
SqlConnection sqlConnect = dbAccess.CreateFuelDBConnection();
SqlCommand sqlCommand = dbAccess.SetFuelDBCommandTypeStoredProcedure(sqlConnect, storedProcedureName);
try
{
sqlConnect.Open();
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
//int counter = 0;
if (sqlReader.HasRows == true)
{
while (sqlReader.Read())
{
dictSalesDept.Add((int)sqlReader["SalesDeptID"], (string)sqlReader["SalesDept"]);
}
}
sqlReader.Close();
}
catch (SqlException ex)
{ Message = ex.Message; }
catch (Exception ex)
{ Message = ex.Message; }
finally
{ dbAccess.CloseConnection(ref sqlConnect); }
return(dictSalesDept);
}
public UserData VerifyUserNamePassword(string username, string password)
{
// Method accepts username and password and errMessage as a REF so that if there are any db errors
/// that can be shown.
//bool userExists = false;
UserData userData = new UserData();
string userName = username.ToLower(); // user name is not case sensitive.
//****************
//***************
DBAccess db = new FuelCenterDB.DBAccess();
string sql = "uspCheckUserCredentialsByUserNamePassword";
SqlConnection cn = db.CreateFuelDBConnection();
SqlCommand cmd = db.SetFuelDBCommandTypeStoredProcedure(cn, sql);
cmd.Parameters.AddWithValue("@UserName", SqlDbType.VarChar);
cmd.Parameters["@UserName"].Value = username;
cmd.Parameters.AddWithValue("@UserPassword", SqlDbType.VarChar);
cmd.Parameters["@UserPassword"].Value = password;
try
{
cn.Open();
SqlDataReader sqlResult = cmd.ExecuteReader();
if (sqlResult.HasRows)
{
while (sqlResult.Read())
{
userData.UserId = (int)sqlResult["UserID"];
userData.FirstName = (string)sqlResult["First"];
userData.LastName = (string)sqlResult["Last"];
}
}
else
{
// This will be the default class that is returned so that the programmer can check if
// data was returned. If the user id is 0 then nothing was f0und.
userData.UserId = 0;
Messages.Message = "User Not Found";
}
sqlResult.Close();
}
catch (SqlException ex)
{ Messages.Message = ex.Message; }
catch (Exception ex)
{ Messages.Message = ex.Message; }
finally
{ db.CloseConnection(ref cn); }
return(userData);
}
public List <UserData> GetUsersDataList()
{
List <UserData> userData = new List <UserData>();
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
string sql = "uspGetUserList";
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, sql);
try
{
using (sqlConnect)
{
sqlConnect.Open();
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
UserData newUser = new FuelCenterDB.UserData((string)sqlReader["First"],
(string)sqlReader["Last"],
(int)sqlReader["UserId"]);
userData.Add(newUser);
}
sqlReader.Close();
}
}
}
catch (Exception ex)
{
Message = new Exceptions(ex);
}
finally
{ db.CloseConnection(ref sqlConnect); }
//UserList = userData;
return(userData);
}
protected internal Dictionary <int, string> GetVendorList()
{
Dictionary <int, string> vendorList = new Dictionary <int, string>();
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
string sql = "uspGetVendorList";
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, sql);
try
{
sqlConnect.Open();
using (sqlConnect)
{
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
vendorList.Add((int)sqlReader["VendorId"], (string)sqlReader["VendorName"]);
}
}
sqlReader.Close();
}
}
catch (SqlException ex)
{ Message = ex.Message; }
catch (Exception ex)
{ Message = ex.Message; }
finally
{
db.CloseConnection(ref sqlConnect);
}
//vendorList.OrderBy()
return(vendorList);
}