Public feed
#602213
/// <summary>
/// Uploads information from an excel file to the database
/// </summary>
/// <param name="path">The path to the excel file slected by the user</param>
/// <returns>Dataset of failedUploads so the user can manually handle them afterwards</returns>
public DataSet UploadExcelData(string path)
{
string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DRAWING.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
//Create a connection. As far as I can tell this seems to work just like SqlConnections
OleDbConnection conn = new OleDbConnection(strConnection);
//Create the command for the new connection. SQL select statement. I can show you how to
//select from more than one sheet later if you'd like
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
//Make the data adapter
OleDbDataAdapter da = new OleDbDataAdapter(command);
//And the datasets
DataSet projectData = new DataSet();
//This dataset will hold any of the objects that could not be inserted
DataSet failedObjects = new DataSet();
//Create a dataTable to hold the data for failedObjects
DataTable failedObjectsTable = new DataTable();
DataColumn failedObject = new DataColumn("FailedUpdate", System.Type.GetType("System.String"));
DataColumn reason = new DataColumn("Reason", System.Type.GetType("System.String"));
failedObjectsTable.Columns.Add(failedObject);
failedObjectsTable.Columns.Add(reason);
DataRow failedObjectRow;
//Open connection to the excel file
conn.Open();
//Fill things
da.Fill(projectData, "projectData");
//Close connection
conn.Close();
//Create dataRow and dataTable so that we can loop through our dataset
DataRow row;
DataTable projectDataTable = projectData.Tables["projectData"];
//Loop through the projectData and start inserting things
for(int i = 0; i < projectDataTable.Rows.Count; i++)
{
row = projectDataTable.Rows[i];
//Get the project ID. This is used in a lot of drawing creations so I grab it outside of the If statements
int projectId = (int)Int32.Parse(row["PROJECT ID"].ToString());
//These variables are used to define the parent attributes for drawings
int parentDrawingId = 0;
int parentRevisionNumber = 0;
//Once again spool gets it's own things. It's like the spoiled baby of the family
int spoolId = 0;
int spoolRevisionNumber = 0;
//Drawing object used to help create the new drawings from the excel sheet
Drawing entity = new Drawing();
//Check if the MWP column has data in it
if(!(row["MODULE NUMBER/LOCATION"].ToString().Equals("")))
{
try
{
//Grab revisionNumber from the excel file
int revisionNumber = (int)Int32.Parse(row["Revision2"].ToString());
//To get drawingTypeId we must first go through the DrawingTypeBLL
//to create a drawing type object
DrawingTypeBLLBase drawingTypeBLLBase = new DrawingTypeBLLBase();
//Create a TList and populate it from the DrawingTypeBLL
TList<DrawingType> drawingTypeList = new TList<DrawingType>();
drawingTypeList = drawingTypeBLLBase.GetByDrawingTypeName("Modular Work Package");
//Create a DrawingType object and fill it with the first value from the DrawingTypeList
DrawingType drawingType = drawingTypeList[0];
//Finally we can get the drawingTypeId by using our new drawing type object
int drawingTypeId = drawingType.DrawingTypeId;
//internalDrawingNumber and moduleId come straight from the excel file. I like these ones better
string internalDrawingNumber = (string)row["MODULE NUMBER/LOCATION"];
string moduleId = (string)row["MODULE"];
//Populate our Drawing object called entity
entity = CreateDrawing(revisionNumber, projectId, drawingTypeId, parentDrawingId, parentRevisionNumber, internalDrawingNumber, moduleId);
//Save the drawing object
entity = Save(entity);
//Set the parent values so the next drawing will automatically be a child of the current one
parentDrawingId = entity.DrawingId;
parentRevisionNumber = entity.RevisionNumber;
}catch(Exception ex){
failedObjectRow = failedObjectsTable.NewRow();
failedObjectRow["FailedUpdate"] = (string)row["MODULE NUMBER/LOCATION"];
failedObjectRow["Reason"] = ex.Message.ToString();
failedObjectsTable.Rows.Add(failedObjectRow);
}
}
if (!(row["P&ID REFERENCE NO#"].ToString().Equals("")))
{
try
{
//Grab revisionNumber from the excel file
int revisionNumber = (int)Int32.Parse(row["Revision2"].ToString());
//To get drawingTypeId we must first go through the DrawingTypeBLL
//to create a drawing type object
DrawingTypeBLLBase drawingTypeBLLBase = new DrawingTypeBLLBase();
//Create a TList and populate it from the DrawingTypeBLL
TList<DrawingType> drawingTypeList = new TList<DrawingType>();
drawingTypeList = drawingTypeBLLBase.GetByDrawingTypeName("P&ID Drawing");
//Create a DrawingType object and fill it with the first value from the DrawingTypeList
DrawingType drawingType = drawingTypeList[0];
//Finally we can get the drawingTypeId by using our new drawing type object
int drawingTypeId = drawingType.DrawingTypeId;
//internalDrawingNumber and moduleId come straight from the excel file. I like these ones better
string internalDrawingNumber = (string)row["P&ID REFERENCE NO#"];
string moduleId = (string)row["MODULE"];
//Populate our Drawing object called entity
entity = CreateDrawing(revisionNumber, projectId, drawingTypeId, parentDrawingId, parentRevisionNumber, internalDrawingNumber, moduleId);
//Save the drawing object
entity = Save(entity);
//Set the parent values so the next drawing will automatically be a child of the current one
parentDrawingId = entity.DrawingId;
parentRevisionNumber = entity.RevisionNumber;
}catch(Exception ex){
failedObjectRow = failedObjectsTable.NewRow();
failedObjectRow["Failed Update"] = (string)row["P&ID REFERENCE NO#"];
failedObjectRow["Reason"] = ex.Message.ToString();
failedObjectsTable.Rows.Add(failedObjectRow);
}
}
if (!(row["REFDRAWING/ISOMETRIC"].ToString().Equals("")))
{
try
{
//Grab revisionNumber from the excel file
int revisionNumber = (int)Int32.Parse(row["Revision1"].ToString());
//To get drawingTypeId we must first go through the DrawingTypeBLL
//to create a drawing type object
DrawingTypeBLLBase drawingTypeBLLBase = new DrawingTypeBLLBase();
//Create a TList and populate it from the DrawingTypeBLL
TList<DrawingType> drawingTypeList = new TList<DrawingType>();
drawingTypeList = drawingTypeBLLBase.GetByDrawingTypeName("Isometric");
//Create a DrawingType object and fill it with the first value from the DrawingTypeList
DrawingType drawingType = drawingTypeList[0];
//Finally we can get the drawingTypeId by using our new drawing type object
int drawingTypeId = drawingType.DrawingTypeId;
//internalDrawingNumber and moduleId come straight from the excel file. I like these ones better
string internalDrawingNumber = (string)row["REFDRAWING/ISOMETRIC"];
string moduleId = (string)row["MODULE"];
//Populate our Drawing object called entity
entity = CreateDrawing(revisionNumber, projectId, drawingTypeId, parentDrawingId, parentRevisionNumber, internalDrawingNumber, moduleId);
//Save the drawing object
entity = Save(entity);
//Set the parent values so the next drawing will automatically be a child of the current one
parentDrawingId = entity.DrawingId;
parentRevisionNumber = entity.RevisionNumber;
}
catch (Exception ex)
{
failedObjectRow = failedObjectsTable.NewRow();
failedObjectRow["FailedUpdate"] = (string)row["REFDRAWING/ISOMETRIC"];
failedObjectRow["Reason"] = ex.Message.ToString();
failedObjectsTable.Rows.Add(failedObjectRow);
}
}
if (!(row["SPOOL NO#"].ToString().Equals("")))
{
try
{
//Spools get their own entity because they're weird
Spool spoolEntity = new Spool();
//And it's own spool revision entity
SpoolRevision spoolRevisionEntity = new SpoolRevision();
//Create references to the BLLs for Spool and SpoolRevision
SpoolBLLBase spoolBLL = new SpoolBLLBase();
SpoolRevisionBLLBase spoolRevisionBLL = new SpoolRevisionBLLBase();
//Grab revisionNumber from the excel file
int revisionNumber = (int)Int32.Parse(row["Revision"].ToString());
//internalDrawingNumber and moduleId come straight from the excel file. I like these ones better
string spoolNumber = (string)row["SPOOL NO#"];
string moduleId = (string)row["MODULE"];
int controlNumber = (int) Int32.Parse(row["CONTROL NO#"].ToString());
//Populate our Spool object called entity
spoolEntity = WMS.Services.SpoolBLL.CreateSpool(spoolNumber, moduleId, controlNumber, parentDrawingId, parentRevisionNumber);
//Save the drawing object
spoolEntity = spoolBLL.Save(spoolEntity);
//Do the same thing for the SpoolRevision
spoolRevisionEntity = WMS.Services.SpoolRevisionBLL.CreateSpoolRevision(spoolEntity.SpoolId, revisionNumber, System.DateTime.Today.Date);
spoolRevisionEntity = spoolRevisionBLL.Save(spoolRevisionEntity);
//Set the parent values so the next drawing will automatically be a child of the current one
spoolId = spoolEntity.SpoolId;
spoolRevisionNumber = spoolRevisionEntity.SpoolRevisionNumber;
}
catch (Exception ex)
{
failedObjectRow = failedObjectsTable.NewRow();
failedObjectRow["FailedUpdate"] = (string)row["SPOOL NO#"];
failedObjectRow["Reason"] = ex.Message.ToString();
failedObjectsTable.Rows.Add(failedObjectRow);
}
}
//Time to add the weld to the database
if (!(row["WELD NO#"].ToString().Equals("")))
{
try
{
//Create a Weld entity
Weld weldEntity = new Weld();
//Create references to the Weld BLL and WeldType BLL and WeldProcedure BLL
WeldBLLBase weldBLL = new WeldBLLBase();
WeldTypeBLLBase weldTypeBLL = new WeldTypeBLLBase();
WeldProcedureBLLBase weldProcedureBLL = new WeldProcedureBLLBase();
string weldType = (string)row["TYPE"];
//Do the TList thing again so that we can get welderId
TList<WeldType> weldTypeList = new TList<WeldType>();
weldTypeList = weldTypeBLL.GetByAbbreviation(weldType);
WeldType weldTypeEntity = new WeldType();
weldTypeEntity = weldTypeList[0];
int weldTypeId = weldTypeEntity.WeldTypeId;
//Get weldProcedureId
WeldProcedure weldProcedure = weldProcedureBLL.GetByProcedureName((string)row["PROCEDURE NO#"]);
int weldProcedureId = weldProcedure.WeldProcedureId;
//Grab revisionNumber from the excel file
int revisionNumber = (int)Int32.Parse(row["Revision"].ToString());
string welderId = (string)row["WELDER ID"];
double size = (double)Double.Parse(row["SIZE"].ToString());
string wall = (string)row["WALL"];
string weldTag = (string)row["WELD NO#"];
//Populate our Weld object called weldEntity
weldEntity = WMS.Services.WeldBLL.CreateWeld(weldTypeId, parentDrawingId, welderId, weldProcedureId, spoolId, spoolRevisionNumber, size, wall, "", System.DateTime.Today.Date, weldTag, parentDrawingId, parentRevisionNumber);
weldEntity = weldBLL.Save(weldEntity);
}
catch (Exception ex)
{
failedObjectRow = failedObjectsTable.NewRow();
failedObjectRow["FailedUpdate"] = (string)row["WELD NO#"].ToString();
failedObjectRow["Reason"] = ex.Message.ToString();
failedObjectsTable.Rows.Add(failedObjectRow);
}
}
}
failedObjects.Tables.Add(failedObjectsTable);
failedObjects.Tables[0].TableName = "FailedObjects";
return failedObjects;
}