public void AddProducts(List <Product> products)
{
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = InsertProductsSp;
DataTable productsDataTable = products.ToDataTable(p => new
{
p.Name,
p.SupplierId,
p.CategoryId,
MeasureUnit = ColumnSettings.Build(() => p.MeasureUnit).Name("Quantity Per Unit"),
UnitPrice = p.Price * 1.5m,
p.UnitsInStock,
p.UnitsOnOrder,
p.ReorderLevel,
p.Discontinued
});
sqlCommand.Parameters.AddWithValue("@Products", productsDataTable);
sqlCommand.ExecuteNonQuery();
}
}
finally
{
SqlConn.Close();
}
}
public List <Product> GetTopExpensiveProducts(int count)
{
List <Product> products = new List <Product>();
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandText = GetTopExpensiveProductsSql;
sqlCommand.Parameters.AddWithValue("@Count", count);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
var product = new Product()
{
Id = dataReader.GetInt32(0),
Price = dataReader.GetDecimal(1)
};
products.Add(product);
}
}
}
finally
{
SqlConn.Close();
}
return(products);
}
/// <summary>
/// Gets patient list from the database
/// </summary>
/// <param name="cmdText"></param>
/// <param name="error"></param>
/// <returns></returns>
static private List <PatientData> GetPatientData(string cmdText, out int error)
{
List <PatientData> patList = null;
SqlDataReader reader = null;
error = -1;
try
{
if (OpenConnection())
{
SqlCommand command = new SqlCommand(cmdText, SqlConn);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
if (reader != null)
{
patList = new List <PatientData>();
int colPatID = reader.GetOrdinal("ID");
int colMrn = reader.GetOrdinal("MRN");
int colFirstN = reader.GetOrdinal("FIRSTNAME");
int colLastN = reader.GetOrdinal("LASTNAME");
while (reader.Read())
{
PatientData patData = new PatientData();
patData.PatientId = reader.GetValue(colPatID).ToString();
patData.ExternalId = reader.GetValue(colMrn).ToString();
if (!reader.IsDBNull(colFirstN))
{
patData.FirstName = reader.GetValue(colFirstN).ToString();
}
if (!reader.IsDBNull(colFirstN))
{
patData.LastName = reader.GetValue(colLastN).ToString();
}
patList.Add(patData);
}
}
error = 0;
SqlConn.Close();
}
}
catch (Exception)
{
error = -1;
if (SqlConn != null)
{
SqlConn.Close();
}
}
return(patList);
}
/// <summary>
///
/// </summary>
public void CloseConnection()
{
if (SqlTransac == null)
{
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
internal void closeSqlConnection()
{
if (this.SqlConn != null)
{
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
SqlConn.Dispose();
}
}
/// <summary>
///
/// </summary>
public void RollBackTransac()
{
if (SqlTransac != null)
{
SqlTransac.Rollback();
SqlTransac = null;
}
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
/// <summary>
///
/// </summary>
public void CommitTransac()
{
if (SqlTransac != null)
{
SqlTransac.Commit();
SqlTransac = null;
}
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
static void Main(string[] args)
{
RemotingConfiguration.Configure("Campus.exe.config", false);
AuthenticationObj = new ImplementAuthentication();
RemotingServices.Marshal(AuthenticationObj, "AuthenticationObj.rem", typeof(ImplementAuthentication));
foreach (var service in RemotingConfiguration.GetRegisteredWellKnownServiceTypes())
{
Console.WriteLine(service);
}
OpenDatabaseConnection();
Console.WriteLine("server running...");
Console.Read();
SqlConn.Close();
}
//Checks id database connection is valid
static public bool CheckConnection()
{
try
{
OpenConnection();
SqlConn.Close();
HaveValidDBConnInfo = true;
}
catch (Exception)
{
HaveValidDBConnInfo = false;
if (SqlConn != null && SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
return(HaveValidDBConnInfo);
}
public List <Order> GetOrdersWithSpecificProducts(List <Product> products)
{
List <Order> orders = new List <Order>();
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.CommandText = GetOrdersWithSpecificProductsSp;
sqlCommand.Parameters.AddWithValue("@Ids", products.ToDataTable(p => p.Id));
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
var order = new Order()
{
Id = dataReader.GetInt32(0),
PlacedDate = dataReader.GetDateTime(1)
};
orders.Add(order);
}
}
}
finally
{
SqlConn.Close();
}
return(orders);
}
//Login function
//There's only one user with xxx name and xxx password so the given result of the record can be 0 or 1
//However we need the user rank so we cannot just give back a true or false
public int Login(string Name, string Password)
{
const int Error = -1;
const int NoUserFound = 0;
const int UserIsAdmin = 1;
const int UserIsReceptionist = 2;
const int UserIsMember = 3;
//Database gives back strings, so we first need to store in in the StrUserId variable then convert it to int.
//Before converting we need to check if the value is larger than 0
string StrUserId;
try
{
SqlConn.Open();
string Sql = "SELECT Count(user_id), user_id, user_rank_id FROM user WHERE user_name = '" + Name + "' AND user_password= '******' ";
Dt = new DataTable();
Adapter = new MySqlDataAdapter(Sql, SqlConn);
Adapter.Fill(Dt);
//We need to check if the dataTable actually has rows
if (Dt.Rows.Count > 0)
{
if (Convert.ToInt32(Dt.Rows[0][0]) != 0)
{
StrUserId = Dt.Rows[0][1].ToString();
UserId = Convert.ToInt32(StrUserId);
}
if (Dt.Rows[0][0].ToString() == "1" && Dt.Rows[0][2].ToString() == "1")
{
return(UserIsAdmin);
}
else if (Dt.Rows[0][0].ToString() == "1" && Dt.Rows[0][2].ToString() == "2")
{
return(UserIsReceptionist);
}
else if (Dt.Rows[0][0].ToString() == "1" && Dt.Rows[0][2].ToString() == "3")
{
return(UserIsMember);
}
else
{
return(NoUserFound);
}
}
else
{
return(Error);
}
}
catch (MySqlException e)
{
e.GetType();
return(Error);
}
finally
{
SqlConn.Close();
}
}
/// <summary>
/// Imports the completed.
/// </summary>
/// <param name="success">if set to <c>true</c> [success].</param>
protected override void ImportCompleted(bool success)
{
_end = DateTime.Now;
var elapsed = _end.Subtract(_start);
AppendLog(elapsed.ToString("hh\\:mm\\:ss") + " elapsed.");
AppendLog("Closing database connections.");
try
{
AccessConn.Close();
AccessConn.Dispose();
}
catch (Exception e)
{
AppendLog("******************************************************************************");
AppendLog("Error closing Access database table:");
AppendLog(e.Message);
AppendLog("******************************************************************************");
}
try
{
//if (HospCompDataBc != null)
//{
// HospCompDataBc.Close();
// HospCompDataDt.Dispose();
//}
SqlConn.Close();
SqlConn.Dispose();
}
catch (Exception e)
{
AppendLog("******************************************************************************");
AppendLog("Error closing SQL database table:");
AppendLog(e.Message);
AppendLog("******************************************************************************");
}
try
{
if (success)
{
var rootXml = new XElement("LogLines");
foreach (var line in LogFile)
{
rootXml.Add(new XElement("LogLine", line));
}
DataContextObject.Summary = rootXml.ToString();
DataContextObject.Summary = rootXml.ToString();
var fi = new DateTimeFormatInfo();
DataContextObject.DatasetItem.VersionMonth = fi.GetMonthName(DataContextObject.Month);
DataContextObject.DatasetItem.VersionYear = DataContextObject.Year;
if (DataContextObject.DatasetItem.IsReImport)
{
var linesImported = Session.Query <NursingHomeTarget>()
.Count(hc => hc.Dataset.Id == DataContextObject.DatasetItem.Id);
if (DataContextObject.DatasetItem.File.Contains(" (#"))
{
DataContextObject.DatasetItem.File = DataContextObject.DatasetItem.File.SubStrBefore(" (#");
}
DataContextObject.DatasetItem.File += " (# Rows Imported: " + linesImported + ")";
}
AppendLog("Import completed successfully.");
}
else
{
AppendLog("Import was not completed successfully.");
}
Done = true;
// WPF CommandManager periodically calls IsValid to see if the Next/Done button should be enabled.
// In multi-threaded wizard steps, IsValid returns the value of the Done flag. Call InvalidateRequerySuggested here
// on the UI thread after setting the Done flag to force WPF to call IsValid now so the Next/Done button will become enabled.
//Application.Current.DoEvents();
NotifyUi(this, new ExtendedEventArgs <Action>(CommandManager.InvalidateRequerySuggested));
}
finally
{
base.ImportCompleted(success);
}
}
protected void CloseConnection()
{
SqlConn.Close();
SqlConn = null;
}