protected string GetDocketsForUserIdInXML(string userId)
{
dbManager = new DBConnection();
try
{
SqlCommand command = new SqlCommand();
command.Connection = dbManager.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[get_dockets_for_user]";
command.Parameters.AddWithValue("@user_id", userId);
XmlReader reader = command.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.GetXml();
}
finally
{
dbManager.Close();
}
}
protected string getCountryInXml()
{
dbManager = new DBConnection();
try
{
SqlCommand command = new SqlCommand();
command.Connection = dbManager.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[get_country_list]";
XmlReader reader = command.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.GetXml();
}
finally
{
dbManager.Close();
}
}
protected bool Save(string userName, string password, string employeeId)
{
byte[] p = CryptoHelper.Instance.EncryptStringToBytes(password);
DBConnection dbManager = new DBConnection();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[add_user]";
cmd.Parameters.AddWithValue("@login_name", dbManager.CheckNull(userName));
cmd.Parameters.AddWithValue("@password", dbManager.CheckNull(p));
cmd.Parameters.AddWithValue("@employee_id", dbManager.CheckNull(employeeId));
cmd.Connection = dbManager.Connection;
cmd.ExecuteNonQuery();
return true;
}
catch
{
throw;
}
finally
{
dbManager.Close();
}
}
private byte[] GetExistingPassword()
{
// Get the existing password from the DB;
// there really is no need for this to be an instance variable. It is one select.
DBConnection dbManager = new DBConnection();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[get_password]";
cmd.Parameters.AddWithValue("@id", dbManager.CheckNull(this.name));
cmd.Connection = dbManager.Connection;
return (byte[])cmd.ExecuteScalar();
}
catch
{
throw;
}
finally
{
dbManager.Close();
}
}
private void Initialize()
{
// Haha! Now we have the name populated with either the login id or emp id,
// Just get the data and initialize the values and we are done!
DBConnection dbManager = new DBConnection();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[get_user_details]";
cmd.Parameters.AddWithValue("@id", dbManager.CheckNull(this.name));
cmd.Connection = dbManager.Connection;
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
this.id = Convert.ToInt32(reader["id"]);
this.name = Convert.ToString(reader["login_name"]);
this.employeeId = Convert.ToString(reader["employee_id"]);
this.password = string.Empty;
}
}
}
catch
{
throw;
}
finally
{
dbManager.Close();
}
}
private int GetLastDocketNumberFromDB()
{
// Get the number of the last inserted docket from the DB
// the number will have a format of XXX-01, we get the int part of this
// add one and return
int docketNumber = 0;
object returnValue;
dbManager = new DBConnection();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[get_last_docket_number]";
cmd.Connection = dbManager.Connection;
returnValue = cmd.ExecuteScalar();
}
finally
{
dbManager.Close();
}
// Incase there is nothing in the DB we get a nice null value here :)
if (returnValue != null)
{
string numberOfLastInsertedDocket = returnValue.ToString();
string[] lastDocketComponents = numberOfLastInsertedDocket.Split(Settings.Default.DocketNumberSeperator);
// Check if the year part of the last inserted docket is the same as the current year, if not we have to return 0;
string currentYear = DateTime.Now.ToString("yy");
if (lastDocketComponents[0].Contains(currentYear))
{
// oh ok, we already have entries for this year!
// Lets get the int part of this and return that
docketNumber = Convert.ToInt32(lastDocketComponents[lastDocketComponents.Length - 1]);
}
}
// Either this will be a calculated value or zero
return docketNumber;
}
protected bool SetDelete(bool deleted)
{
dbManager = new DBConnection();
int deactivated = deleted ? 1 : 0;
string reason = "DELETED";
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbManager.Connection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[set_docket_deactivation_status]";
cmd.Parameters.AddWithValue("@docket_id", dbManager.CheckNull(this.id));
cmd.Parameters.AddWithValue("@deactivated", dbManager.CheckNull(deactivated));
cmd.Parameters.AddWithValue("@reason", dbManager.CheckNull(reason));
cmd.ExecuteNonQuery();
return true;
}
finally
{
dbManager.Close();
}
}
protected bool Save()
{
SetCreatedOn();
SetDocketNumber();
try
{
dbManager = new DBConnection();
dbManager.Open();
SqlConnection connection = dbManager.Connection;
SqlCommand command = new SqlCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "[dbo].[save_docket]";
command.Connection = connection;
command.Parameters.AddWithValue("@invention_name", dbManager.CheckNull(this.inventionName));
command.Parameters.AddWithValue("@inventor_name", dbManager.CheckNull(this.inventorName));
command.Parameters.AddWithValue("@type_of_app", dbManager.CheckNull(this.typeOfApp));
command.Parameters.AddWithValue("@number", dbManager.CheckNull(this.number));
command.Parameters.AddWithValue("@created_on", dbManager.CheckNull(this.createdOn));
Int32 docketId = Convert.ToInt32(command.ExecuteScalar());
SetId(docketId);
return true;
}
catch (SqlException ex)
{
//Make an entry in the log. Return false;
throw;
}
finally
{
dbManager.Close();
}
}