private void ErrorLog(Exception ex, string SubDeviceId) { try { string[,] para = { { "SubDeviceId", SubDeviceId }, { "ErrorMessage", ex.Message.ToString() }, { "ErrorDetails", ex.ToString() } }; string sqlQue = "insert into DeviceErrorLog(DeviceErrorLogID,SubDeviceID,ErrorMessage,ErrorDetails) values (NEWID(),@SubDeviceID,@ErrorMessage,@ErrorDetails)"; SqlHelper.ExecuteNoneQueryWithPera(sqlQue, para); CommanClass.SendMail(ex, SubDeviceId); } catch (Exception ex1) { } }
private void SaveData(string FilePath, string DeviceDate, string SubDeviceId, decimal multiplier) { string extension = Path.GetExtension(FilePath), AccessColumn = "", SerialNo = "", DeviceTotalCount = ""; try { DataTable tempTable = GetCSVFileToDataTable("", FilePath, extension, out SerialNo); if (SerialNo != "") { string ColNM = string.Empty, DelCol = string.Empty; string[] SpSrNo = SerialNo.Trim().Split(' '); DataTable dt = SqlHelper.ExecuteProcedure("Select A.ColumnMappingID,A.MainColumnID,B.ColumnName,A.CSVFileColumnsID,C.CSVFileColumnName,D.SerialNo from ColumnMapping as A join MainColumn as B on A.MainColumnID= B.MainColumnID join CSVFileColumns as C on A.CSVFileColumnsID = C.CSVFileColumnsID join MainDeviceMaster as F on C.DeviceID = F.DeviceID join SubDeviceMaster as D on F.DeviceID = D.DeviceID where D.SerialNo = '" + SpSrNo[3].Trim() + "' order by ColumnName"); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { try { AccessColumn += dt.Rows[i]["ColumnName"].ToString() + ","; tempTable.Columns[dt.Rows[i]["CSVFileColumnName"].ToString()].ColumnName = dt.Rows[i]["ColumnName"].ToString(); } catch (Exception ex) { } } AccessColumn = AccessColumn.Remove(AccessColumn.Length - 1, 1); DataTable dtCount = SqlHelper.ExecuteProcedure("select count(DeviceDataID) as TotalCount from DeviceData where SubDeviceId = '" + SubDeviceId + "' and DeviceDate = '" + DeviceDate + "' "); DeviceTotalCount = dtCount.Rows[0]["TotalCount"].ToString(); for (int i = 0; i < tempTable.Columns.Count; i++) { ColNM = tempTable.Columns[i].ColumnName; for (int j = 0; j < dt.Rows.Count; j++) { if (dt.Rows[j]["ColumnName"].ToString() == ColNM) { ColNM = ""; break; } } DelCol += !string.IsNullOrEmpty(ColNM) ? ColNM + "," : ""; } foreach (string str in DelCol.Split(',')) { if (!string.IsNullOrEmpty(str)) { tempTable.Columns.Remove(str); } } string[,] para = new string[dt.Rows.Count + 2, 2]; decimal Val = 0; para[0, 0] = "SubDeviceId"; para[0, 1] = SubDeviceId; para[1, 0] = "DeviceDate"; para[1, 1] = DeviceDate; for (int i = int.Parse(DeviceTotalCount); i < tempTable.Rows.Count; i++) { for (int j = 0; j < dt.Rows.Count; j++) { para[j + 2, 0] = dt.Rows[j]["ColumnName"].ToString(); //if (j == 0) //para[j + 2, 1] = tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString(); if (dt.Rows[j]["ColumnName"].ToString() == "DeviceDateTime") { //para[j + 2, 1] = DateTime.ParseExact(DeviceDate + tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString(), "yyyy-MM-dd mm:ss", null).ToString("yyyy-MM-dd mm:ss"); para[j + 2, 1] = DeviceDate + " " + tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString(); } else { try { Decimal.TryParse(tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString(), out Val); if (dt.Rows[j]["ColumnName"].ToString().ToLower() == "eac") { if (Val != 0) { Val = (Val * Convert.ToDecimal(multiplier)); } } para[j + 2, 1] = Val.ToString(); } catch { para[j + 2, 1] = string.Empty; } } } string sqlQue = "insert into DeviceData(SubDeviceId,DeviceDate," + AccessColumn + ") values (@SubDeviceId,@DeviceDate,@" + AccessColumn.Replace(",", ",@") + ")"; //sqlQue = sqlQue.Replace("@Eac", "@Eac*" + multiplier); SqlHelper.ExecuteNoneQueryWithPera(sqlQue, para); } } else { CommanClass.SendMail(null, SpSrNo[3].Trim()); } } } catch (Exception ex) { ErrorLog(ex, SubDeviceId); } }