void BtnAddClick(object sender, EventArgs e)
{
if(ofdFile.ShowDialog() == DialogResult.OK)
{
btnAdd.Enabled = false;
lblLoading.Visible = true;
Workbook MyBook = null;
Microsoft.Office.Interop.Excel.Application MyApp = null;
Worksheet MySheet = null;
MyApp = new Microsoft.Office.Interop.Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(ofdFile.FileName);
MySheet = (Worksheet)MyBook.Sheets[1];
int lastRow = MySheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
Array MyValues;
List<Client> newClients = new List<Client>();
Client theClient;
for (int i = 2; i <= lastRow; i++)
{
MyValues = (Array)MySheet.get_Range("A" + i.ToString(), "D" + i.ToString()).Cells.Value;
theClient = new Client();
theClient.MacsTracID = int.Parse(MyValues.GetValue(1,1).ToString());
theClient.Name = MyValues.GetValue(1,4).ToString();
newClients.Add(theClient);
}
MyApp.Quit();
List<Client> duplicateClients = new List<Client>();
foreach (Client tempClient in newClients)
{
foreach (Client knownClient in clients)
{
if(tempClient.Name == knownClient.Name)
{
duplicateClients.Add(tempClient);
}
}
}
foreach (Client tempClient in duplicateClients)
{
for (int i = 0; i < newClients.Count; i++)
{
if(tempClient.Name == newClients[i].Name)
{
newClients.Remove(newClients[i]);
}
}
}
if(duplicateClients.Count>0)
{
MessageBox.Show("Some clients were already in the database. They have not been added from the spreadsheet");
}
Model.AddClients(newClients);
clients = Model.GetClients();
ReloadClients();
lblLoading.Visible = false;
btnAdd.Enabled = true;
}
}
public ClientDetails(Client passedClient)
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
TheClient = passedClient;
LoadClient();
//
// TODO: Add constructor code after the InitializeComponent() call.
//
}
public static bool AddClient(Client theClient)
{
if(theConnection.ConnectionString == string.Empty)
{
theConnection.ConnectionString = connectionString;
}
try
{
int theID = theClient.MacsTracID;
theConnection.Open();
theCommand.Connection = theConnection;
commandString = "insert into Clients (clientName,clientMTID,cocPrice,mocPrice,monoPrice," +
"cocOEMPrice,mocOEMPrice,monoOEMPrice) Values(@name,@MTID,@cocPrice,@mocPrice,@monoPrice,@cocOEMPrice,@mocOEMPrice,@monoOEMPrice)";
theCommand.CommandText = commandString;
theCommand.Prepare();
theCommand.Parameters.Clear();
theCommand.Parameters.AddWithValue("@name","client name");
theCommand.Parameters.AddWithValue("@MTID",1);
theCommand.Parameters.AddWithValue("@cocPrice",0.0000);
theCommand.Parameters.AddWithValue("@mocPrice",0.0000);
theCommand.Parameters.AddWithValue("@monoPrice",0.0000);
theCommand.Parameters.AddWithValue("@cocOEMPrice",0.0000);
theCommand.Parameters.AddWithValue("@mocOEMPrice",0.0000);
theCommand.Parameters.AddWithValue("@monoOEMPrice",0.0000);
theCommand.Parameters["@name"].Value = theClient.Name;
theCommand.Parameters["@MTID"].Value = theClient.MacsTracID;
theCommand.Parameters["@cocPrice"].Value = theClient.CPrice;
theCommand.Parameters["@mocPrice"].Value = theClient.MoCPrice;
theCommand.Parameters["@monoPrice"].Value = theClient.MPrice;
theCommand.Parameters["@cocOEMPrice"].Value = theClient.COEMPrice;
theCommand.Parameters["@mocOEMPrice"].Value = theClient.MoCOEMPrice;
theCommand.Parameters["@monoOEMPrice"].Value = theClient.MOEMPrice;
theCommand.ExecuteNonQuery();
return true;
}
catch(MySqlException ex)
{
return false;
}
finally
{
theConnection.Close();
}
}
public static List<Client> GetClients()
{
if(theConnection.ConnectionString == string.Empty)
{
theConnection.ConnectionString = connectionString;
}
List<Client> theClients = new List<Client>();
Client theClient;
try
{
commandString = "select * from Clients";
theCommand.Connection = theConnection;
theCommand.CommandText = commandString;
theConnection.Open();
decimal number;
reader = theCommand.ExecuteReader();
while (reader.Read())
{
theClient = new Client();
if(Decimal.TryParse(reader["cocPrice"].ToString(), out number))
{
theClient.CPrice = number;
}
if(Decimal.TryParse(reader["mocPrice"].ToString(), out number))
{
theClient.MoCPrice = number;
}
if(Decimal.TryParse(reader["monoPrice"].ToString(), out number))
{
theClient.MPrice = number;
}
if(Decimal.TryParse(reader["cocOEMPrice"].ToString(), out number))
{
theClient.COEMPrice = number;
}
if(Decimal.TryParse(reader["mocOEMPrice"].ToString(), out number))
{
theClient.MoCOEMPrice = number;
}
if(Decimal.TryParse(reader["monoOEMPrice"].ToString(), out number))
{
theClient.MOEMPrice = number;
}
theClient.ID = int.Parse(reader["clientID"].ToString());
theClient.MacsTracID = int.Parse(reader["clientMTID"].ToString());
theClient.Name = reader["clientName"].ToString();
theClients.Add(theClient);
}
return theClients;
}
catch (MySqlException ex)
{
return new List<Client>();
}
finally
{
theConnection.Close();
}
}