public void LoadData(string xmlFileName, string prjId, string tcId, out IDataModel globalData, out IDataModel testData, out System.Data.DataTable controlInfo) { XElement root = XmlHelper.LoadXml(xmlFileName); var prj = XmlHelper.GetElementsByCondition(root, XmlTemplateStruct.ProjectNode, XmlTemplateStruct.ProjectIdProperty, prjId).ToList()[0]; if (null != prj) { var target = prj.Element(XmlTemplateStruct.GlobalDataNode); globalData = new DataModel(TransformNode2DataTable(XmlTemplateStruct.GlobalDataNode, target).Rows[0]); target = prj.Element(XmlTemplateStruct.TestDataNode); var collection = XmlHelper.GetElementsByCondition(target, XmlTemplateStruct.TestDataChildNode, XmlTemplateStruct.TestCaseIdProperty, tcId); if (null != collection) { testData = new DataModel(TransformNode2DataTable(XmlTemplateStruct.TestDataChildNode, collection).Rows[0]); } else throw new System.Exception("Not found node " + XmlTemplateStruct.TestDataChildNode + " under " + XmlTemplateStruct.TestDataNode + " which " + XmlTemplateStruct.TestCaseIdProperty + " = " + tcId); target = prj.Element(XmlTemplateStruct.ControlInfoNode); collection = target.Elements(XmlTemplateStruct.ControlInfoChildNode); controlInfo = TransformNode2DataTable(XmlTemplateStruct.ControlInfoChildNode, collection); } else throw new System.Exception("Not found node " + XmlTemplateStruct.ProjectNode + " which " + XmlTemplateStruct.ProjectIdProperty + " = " + prjId); }
public void LoadData(string xlsFileName, string prjId, string tcId, out IDataModel globalData, out IDataModel testData, out DataTable controlInfo) { globalData = null; testData = null; controlInfo = null; string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + xlsFileName + "; Extended Properties='Excel 8.0;IMEX=1';"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { if (connection.State != ConnectionState.Open) connection.Open(); var tb = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); using (OleDbCommand command = new OleDbCommand()) { foreach (DataRow row in tb.Rows) { var dt = new DataTable(); string conditionColumn = XlsGlobalDataStruct.ProjectId, comparedValue = prjId;//"'" + prjId + "'"; string sheetname = row["TABLE_NAME"].ToString(); command.Connection = connection; if (sheetname.Contains(XlsGlobalDataStruct.TableOrSheet) || sheetname.Contains(XlsControlInfoStruct.TableOrSheet)) { command.CommandText = "SELECT * FROM [" + sheetname + "] where " + conditionColumn + " = " + comparedValue; } else if (sheetname.Contains(XlsTestDataStruct.TableOrSheet)) { string andConditionString = XlsTestDataStruct.TestCaseId + " = " + tcId;//'" + tcId + "'"; command.CommandText = "SELECT * FROM [" + sheetname + "] where " + conditionColumn + " = " + comparedValue + " AND " + andConditionString; } else throw new System.Exception("All sheets should call " + XlsGlobalDataStruct.TableOrSheet + ", " + XlsTestDataStruct.TableOrSheet + ", or " + XlsControlInfoStruct.TableOrSheet + ".Please check sheet name again!"); using (OleDbDataAdapter adapter = new OleDbDataAdapter()) { adapter.SelectCommand = command; adapter.Fill(dt); dt.TableName = sheetname.Replace("$", ""); if (sheetname.Contains(XlsGlobalDataStruct.TableOrSheet)) { var dr = FillDataRow(dt); globalData = new DataModel(dr); } else if (sheetname.Contains(XlsTestDataStruct.TableOrSheet)) { var dr = FillDataRow(dt); testData = new DataModel(dr); } else if (sheetname.Contains(XlsControlInfoStruct.TableOrSheet)) { controlInfo = dt; } } } } } }