‪Black Box
BBSD2DB.cs
Go to the documentation of this file.
1 /*
2 * FILE : BBSD2DB.cs
3 * PROJECT : Black Box
4 * PROGRAMMER : BRIAN HINDS
5 * FIRST VERSION : 1.0.0.0
6 * DATE : March 20, 2019
7 * DESCRIPTION : This application decrypt and insert data into SQL Server database.
8 */
9 
11 using System;
12 using System.Configuration;
13 using System.Data;
14 using System.Data.SqlClient;
15 using System.IO;
16 using System.Windows.Forms;
17 
19 {
20  public partial class ‪BBSD2DB : Form
21  {
22  #region Variables
23 
24  private static string ‪line;
25  private static string[] ‪BBPSensorParsedData;
26  private static DataTable ‪BBPresenseSensorsData = new DataTable("dbo.BBPresenseSensorsData");
27  private static DataTable ‪BBThermostatData = new DataTable("dbo.BBThermostatData");
28  private static SqlConnection ‪cn;
29  private static string ‪tableType;
30 
31  public delegate void ‪UpdateProgressSub(int CurrentLine);
32 
33  private bool ‪abortXmlProcess;
34  private int ‪FileIndex = 0;
35  private string ‪OutputFolder;
36  private int ‪linesWritten = 0;
37  private static int ‪linePerFile = 100;
38  private static int ‪maxFileCount = 100;
39 
43  private static string ‪DB_Connection = ConfigurationManager.ConnectionStrings["BB_db_CONNECTION"].ConnectionString;
44 
45  private long ‪Results;
46 
50  private int ‪blockSize = 0;
51 
52  #endregion Variables
53 
54  #region Constructor
55 
56  public ‪BBSD2DB()
57  {
59  }
60 
61  #endregion Constructor
62 
63  #region CreateTables
64 
68  private static void ‪CreatePresenseSensorTable()
69  {
70  ‪BBPresenseSensorsData.Columns.Add("pLocation", typeof(string));
71  ‪BBPresenseSensorsData.Columns.Add("pStatus", typeof(string));
72  ‪BBPresenseSensorsData.Columns.Add("pTemperature", typeof(float));
73  ‪BBPresenseSensorsData.Columns.Add("pHumidity", typeof(float));
74  ‪BBPresenseSensorsData.Columns.Add("pTime", typeof(string));
75  ‪BBPresenseSensorsData.Columns.Add("pDate", typeof(DateTime));
76  ‪BBPresenseSensorsData.Columns.Add("pDateTimeStamp", typeof(DateTime));
77  }
78 
82  private static void ‪CreateThermostatTable()
83  {
84  ‪BBThermostatData.Columns.Add("tHumidity", typeof(float));
85  ‪BBThermostatData.Columns.Add("tTemperature", typeof(float));
86  ‪BBThermostatData.Columns.Add("tT_heat", typeof(float));
87  ‪BBThermostatData.Columns.Add("tT_cool", typeof(float));
88  ‪BBThermostatData.Columns.Add("tTmode", typeof(string));
89  ‪BBThermostatData.Columns.Add("tFmode", typeof(string));
90  ‪BBThermostatData.Columns.Add("tHold", typeof(string));
91  ‪BBThermostatData.Columns.Add("tTstate", typeof(string));
92  ‪BBThermostatData.Columns.Add("tTtarget", typeof(string));
93  ‪BBThermostatData.Columns.Add("tProgramMode", typeof(string));
94  ‪BBThermostatData.Columns.Add("tFstate", typeof(string));
95  ‪BBThermostatData.Columns.Add("tTime", typeof(string));
96  ‪BBThermostatData.Columns.Add("tDate", typeof(DateTime));
97  ‪BBThermostatData.Columns.Add("tDateTimeStamp", typeof(string));
98  }
99 
100  #endregion CreateTables
101 
102  #region TableInsert
103 
108  private static void ‪BB_Database(string BBData)
109  {
110  ‪BBPSensorParsedData = BBData.Split(',');
111 
112  if (‪tableType == "BBPSD")
113  {
115  try
116  {
124  ‪BBPresenseSensorsData.AcceptChanges();
125  }
126  catch (Exception)
127  {
128  //MessageBox.Show("Error inserting into data table", "Data Table Insert", MessageBoxButtons.OK, MessageBoxIcon.Error);
129  }
130  }
131  if (‪tableType == "BBTD")
132  {
134  try
135  {
150  ‪BBThermostatData.AcceptChanges();
151  }
152  catch (Exception)
153  {
154  //MessageBox.Show("Error inserting into data table", "Data Table Insert", MessageBoxButtons.OK, MessageBoxIcon.Error);
155  }
156  }
157  }
158 
159  #endregion TableInsert
160 
161  #region DataBaseInsert
162 
166  private void ‪insertData()
167  {
168  // Presence Sensor table
169  if (‪tableType == "BBPSD")
170  {
171  try
172  {
173  ‪cn = new SqlConnection(‪DB_Connection);
174  //Open connection to do bulk insert
175  ‪cn.Open();
176  ‪DBConnectionStatus.Image = Properties.Resources.trafficlight_green;
177  ‪DBTransferStatus.Image = Properties.Resources.trafficlight_green;
178 
179  using (SqlBulkCopy bulkInsert = new SqlBulkCopy(‪cn))
180  {
181  // Destination and source table have to have the same name and column name
182  // and data type.
183  bulkInsert.DestinationTableName = ‪BBPresenseSensorsData.TableName;
184 
185  // The layout for the bulk insert is DataTable Column name , DataBase Column name
186  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pLocation", "pLocation"));
187  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pStatus", "pStatus"));
188  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pTemperature", "pTemperature"));
189  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pHumidity", "pHumidity"));
190  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pTime", "pTime"));
191  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pDate", "pDate"));
192  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pDateTimeStamp", "pDateTimeStamp"));
193  bulkInsert.WriteToServer(‪BBPresenseSensorsData);
194  bulkInsert.Close();
195  }
196 
197  //If successful the DataTable is cleared and is ready for new data
198  ‪BBPresenseSensorsData.Rows.Clear();
199 
200  //Close connection after insertion
201  ‪cn.Close();
202  }
203  catch (Exception ex)
204  {
205  ‪pIcon.Image = Properties.Resources.error;
206  ‪DBConnectionStatus.Image = Properties.Resources.trafficlight_red;
207  ‪DBTransferStatus.Image = Properties.Resources.trafficlight_red;
208  MessageBox.Show(ex.ToString());
209  }
210  }
211  // Thermostat Table
212  if (‪tableType == "BBTD")
213  {
214  ‪DBConnectionStatus.Image = Properties.Resources.trafficlight_green;
215  try
216  {
217  ‪cn = new SqlConnection(‪DB_Connection);
218  //Open connection to do bulk insert
219  ‪cn.Open();
220 
221  ‪DBTransferStatus.Image = Properties.Resources.trafficlight_green;
222 
223  using (SqlBulkCopy bulkInsert = new SqlBulkCopy(‪cn))
224  {
225  // Destination and source table have to have the same name and column name
226  // and data type.
227  bulkInsert.DestinationTableName = ‪BBThermostatData.TableName;
228 
229  // The layout for the bulk insert is DataTable Column name , DataBase Column name
230  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tHumidity", "tHumidity"));
231  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tTemperature", "tTemperature"));
232  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tT_heat", "tT_heat"));
233  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tT_cool", "tT_cool"));
234  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tTmode", "tTmode"));
235  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tFmode", "tFmode"));
236  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tHold", "tHold"));
237  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tTstate", "tTstate"));
238  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tTtarget", "tTtarget"));
239  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tProgramMode", "tProgramMode"));
240  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tFstate", "tFstate"));
241  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tTime", "tTime"));
242  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tDate", "tDate"));
243  bulkInsert.ColumnMappings.Add(new SqlBulkCopyColumnMapping("tDateTimeStamp", "tDateTimeStamp"));
244  bulkInsert.WriteToServer(‪BBThermostatData);
245  bulkInsert.Close();
246  }
247 
248  //If successful the DataTable is cleared and is ready for new data
249  ‪BBThermostatData.Rows.Clear();
250 
251  //Close connection after insertion
252  ‪cn.Close();
253  }
254  catch
255  {
256  ‪DBConnectionStatus.Image = Properties.Resources.trafficlight_red;
257  ‪DBTransferStatus.Image = Properties.Resources.trafficlight_red;
258  ‪pIcon.Image = Properties.Resources.error;
259  MessageBox.Show("Error inserting into database", "Database Insert", MessageBoxButtons.OK, MessageBoxIcon.Error);
260  }
261  }
262  }
263 
264  #endregion DataBaseInsert
265 
266  #region GetFileSize
267 
273  private static string ‪CalculateFileSize(long TotalSize)
274  {
275  if (TotalSize >= 1073741824)
276  {
277  decimal FileSize = decimal.Divide(TotalSize, 1073741824);
278  string msg = string.Format("{0:##.##} GB", FileSize);
279  ‪linePerFile = 1000;
280  MessageBox.Show("Application may show Not Responding due to file size \n " + "Current File Size: " + msg + ". During operation \n you may open log folder to check operation progress", "XML2SQL", MessageBoxButtons.OK, MessageBoxIcon.Information);
281  return string.Format("{0:##.##} GB", FileSize);
282  }
283  else if (TotalSize >= 1048576 && TotalSize < 1073741824)
284  {
285  decimal FileSize = decimal.Divide(TotalSize, 1048576);
286  return string.Format("{0:##.##} MB", FileSize);
287  }
288  else if (TotalSize >= 1024 && TotalSize < 1048576)
289  {
290  decimal FileSize = decimal.Divide(TotalSize, 1024);
291  //maxFileCount = 1;
292  //linePerFile = 10;
293  return string.Format("{0:##.##} KB", FileSize);
294  }
295  else
296  {
297  return "0 Bytes";
298  }
299  }
300 
301  #endregion GetFileSize
302 
307  private void ‪BBPSD2DB_Load(object sender, EventArgs e)
308  {
311  }
312 
313  #region BrowseFile
314 
320  private void ‪browseFileBtn_Click(object sender, EventArgs e)
321  {
322  ‪pIcon.Image = Properties.Resources.loading__2016_10_29_16_24_24_UTC_;
323  ‪DBConnectionStatus.Image = Properties.Resources.trafficlight_yellow;
324  ‪DBTransferStatus.Image = Properties.Resources.trafficlight_yellow;
325  ‪xmlProgress.Value = 0;
326  ‪pPerc.Text = Convert.ToString((‪xmlProgress.Value / ‪xmlProgress.Maximum) * 100) + "%";
327 
328  OpenFileDialog openXML = new OpenFileDialog();
329 
330  if (openXML.ShowDialog() == DialogResult.OK)
331  {
332  ‪xmlFile.Text = openXML.FileName;
333  }
334 
336  FileInfo F_Info = new FileInfo(‪xmlFile.Text);
337  ‪Results = F_Info.Length;
338 
339  //Calculation file size and displaying file info
341  ‪fName.Text = F_Info.Name;
342 
343  // Determine which table to insert data
344  if (F_Info.Name.Contains("thermostat"))
345  {
346  ‪tableType = "BBTD";
347  }
348  if (F_Info.Name.Contains("presence"))
349  {
350  ‪tableType = "BBPSD";
351  }
352  }
353 
354  #endregion BrowseFile
355 
356  #region ReadFile
357 
366  public void ‪SplitXML(string FilePath, int LineCount, int MaxXlmFiles, ‪UpdateProgressSub Status, ref bool IsAbort)
367  {
368  // Open the XML file for reading
369  StreamReader Reader = new StreamReader(FilePath);
370 
371  // Create the output directory
372  ‪OutputFolder = FilePath + "_Log";
373  if (Directory.Exists(FilePath) == false)
374  {
375  Directory.CreateDirectory(‪OutputFolder);
376  }
377 
378  //Initializing progress bar parameters
379  ‪xmlProgress.Maximum = MaxXlmFiles;
380  ‪xmlProgress.Step = 1;
381 
382  ‪blockSize = 0;
383 
384  while ((‪line = Reader.ReadLine()) != null)
385  {
386  //Update progress bar
387  ‪xmlProgress.PerformStep();
388 
389  ‪pPerc.Text = Convert.ToString((‪xmlProgress.Value / ‪xmlProgress.Maximum) * 100) + "%";
390 
391  ‪blockSize++;
392 
393  // Update progress
394  ‪FileIndex += 1;
395  if (Status != null)
396  {
397  Status.Invoke((‪FileIndex - 1) * LineCount);
398  }
399 
400  ‪line = Reader.ReadLine();
401 
402  //XLM extraction
404 
405  ‪blockSize++;
406 
407  if (‪blockSize == 1000)
408  {
409  ‪blockSize = 0;
410  ‪insertData();
411  }
412  }
413 
414  Reader.Close();
415  ‪insertData();
416  }
417 
418  #endregion ReadFile
419 
423  public void ‪SplitIt()
424  {
425  ‪startBtn.Enabled = false;
426  ‪cancelBtn.Enabled = true;
427 
428  try
429  {
431 
432  if (!‪abortXmlProcess)
433  {
434  ‪pIcon.Image = Properties.Resources.pass;
435  }
436  else
437  {
438  ‪abortXmlProcess = false;
439  MessageBox.Show("Process Canceled Successfully", "Extraction Process", MessageBoxButtons.OK, MessageBoxIcon.Error);
440  }
441  }
442  catch (Exception)
443  {
444  //MessageBox.Show(ex.Message, "Splitting Process", MessageBoxButtons.OK, MessageBoxIcon.Error);
445  }
446  finally
447  {
448  ‪startBtn.Enabled = true;
449  ‪cancelBtn.Enabled = false;
450  }
451  }
452 
457  public void ‪UpdateProgress(int CurrentLine)
458  {
459  ‪linesWritten = CurrentLine;
460  }
461 
467  private void ‪cancelBtn_Click(object sender, EventArgs e)
468  {
469  ‪abortXmlProcess = true;
470  }
471 
477  private void ‪startBtn_Click(object sender, EventArgs e)
478  {
479  ‪SplitIt();
480  }
481  }
482 }
‪System.Windows.Forms.Label fName
‪static DataTable BBPresenseSensorsData
Definition: BBSD2DB.cs:26
‪System.Windows.Forms.ProgressBar xmlProgress
‪static string DecryptData(string SensorData)
‪This method decrypt the sensor data
‪int blockSize
‪Variables for bulk insert
Definition: BBSD2DB.cs:50
‪System.Windows.Forms.Button cancelBtn
‪void BBPSD2DB_Load(object sender, EventArgs e)
Definition: BBSD2DB.cs:307
‪static void CreateThermostatTable()
‪Setting up data table for thermostat
Definition: BBSD2DB.cs:82
‪delegate void UpdateProgressSub(int CurrentLine)
‪static string DB_Connection
‪Initializing Connection parameters
Definition: BBSD2DB.cs:43
‪void browseFileBtn_Click(object sender, EventArgs e)
‪Executes the open file command
Definition: BBSD2DB.cs:320
‪static DataTable BBThermostatData
Definition: BBSD2DB.cs:27
‪void SplitIt()
‪Calls the splitXml method and notify the user of progress success or failure
Definition: BBSD2DB.cs:423
‪static void BB_Database(string BBData)
‪parse sensor's data
Definition: BBSD2DB.cs:108
‪void insertData()
‪Insert data into database
Definition: BBSD2DB.cs:166
‪static string [] BBPSensorParsedData
Definition: BBSD2DB.cs:25
‪System.Windows.Forms.Label fSize
‪System.Windows.Forms.PictureBox DBConnectionStatus
‪System.Windows.Forms.PictureBox pIcon
‪System.Windows.Forms.TextBox xmlFile
‪void SplitXML(string FilePath, int LineCount, int MaxXlmFiles, UpdateProgressSub Status, ref bool IsAbort)
‪Stream the encrypted file and decrypt it. If file too large, file is split
Definition: BBSD2DB.cs:366
‪System.Windows.Forms.Label pPerc
‪System.Windows.Forms.PictureBox DBTransferStatus
‪void startBtn_Click(object sender, EventArgs e)
‪Start the process
Definition: BBSD2DB.cs:477
‪void InitializeComponent()
‪Required method for Designer support - do not modify the contents of this method with the code editor...
‪void UpdateProgress(int CurrentLine)
‪Display the total lines written after split
Definition: BBSD2DB.cs:457
‪static string tableType
Definition: BBSD2DB.cs:29
‪void cancelBtn_Click(object sender, EventArgs e)
‪Cancel the process
Definition: BBSD2DB.cs:467
‪static void CreatePresenseSensorTable()
‪Setting up data table for presence sensor
Definition: BBSD2DB.cs:68
‪static string CalculateFileSize(long TotalSize)
‪Calculating File size
Definition: BBSD2DB.cs:273
‪static SqlConnection cn
Definition: BBSD2DB.cs:28
‪System.Windows.Forms.Button startBtn