public static IList <Tuple <string, string, string> > FindInAdInfos(string keyWord) { if (string.IsNullOrEmpty(keyWord)) { return(null); } string filePath = SystemDirectory.WorkDirectory + "\\AllAdInfos.xml"; //if (System.IO.File.Exists(filePath)) //{ // if (MessageForm.ShowYesNo("已存在信息配置文件,是否重新生成?")) // { // System.IO.File.Delete(filePath); // } //} if (!System.IO.File.Exists(filePath)) { ExportAllToXmlFile(filePath); } var dts = ExcelXmlHelper.ReadExcelXml(filePath, true); IList <Tuple <string, string, string> > ret = new List <Tuple <string, string, string> >(); foreach (var dt in dts) { foreach (System.Data.DataRow row in dt.Rows) { for (int i = 0; i < dt.Columns.Count; ++i) { var v = row[i]; if (v != null) { var s = v.ToString(); if (s.Contains(keyWord)) { ret.Add(new Tuple <string, string, string>(dt.TableName, dt.Columns[i].ColumnName, s)); } } } } } return(ret); }
/// <summary> /// 按照文件内容,通过主键删除数据 /// </summary> /// <param name="fileName"></param> public static void DeleteFromXmlFile(string fileName) { DisableFKConstraint(s_adPreTableName); IList <MyDbCommand> cmds = new List <MyDbCommand>(); IList <System.Data.DataTable> list = ExcelXmlHelper.ReadExcelXml(fileName, true); foreach (System.Data.DataTable i in list) { foreach (System.Data.DataRow row in i.Rows) { System.Data.Common.DbCommand sql = GenerateDeleteCommand(i.TableName, row); System.Data.DataTable dt = DbHelper.Instance.ExecuteDataTable(sql); } } DbHelper.Instance.UpdateTransaction(cmds); EnableFKConstraint(s_adPreTableName); }
/// <summary> /// 把给定文件的数据按照不同数据名文件名导入到数据库中 /// </summary> /// <param name="fileName"></param> /// <param name="tryImport"></param> public static void ImportFromXmlFile(string fileName, bool tryImport) { DisableFKConstraint(s_adPreTableName); IList <MyDbCommand> cmds = new List <MyDbCommand>(); IList <System.Data.DataTable> list = ExcelXmlHelper.ReadExcelXml(fileName, true); foreach (System.Data.DataTable i in list) { foreach (System.Data.DataRow row in i.Rows) { System.Data.Common.DbCommand sqlSelect = GenerateSelectCountCommand(i.TableName, row); System.Data.Common.DbCommand sqlInsert = GenerateInsertCommand(i.TableName, row); System.Data.Common.DbCommand sqlUpdate = GenerateUpdateCommand(i.TableName, row); StringBuilder sb = new StringBuilder(); sb.Append("IF (("); sb.Append(sqlSelect.CommandText); sb.Append(") = 1) BEGIN "); sb.Append(sqlUpdate.CommandText); sb.Append(" END "); sb.Append(" ELSE BEGIN "); sb.Append(sqlInsert.CommandText); sb.Append(" END "); System.Data.Common.DbCommand sql = DbHelper.Instance.Database.GetSqlStringCommand(sb.ToString()); foreach (System.Data.Common.DbParameter para in sqlSelect.Parameters) { System.Data.Common.DbParameter parameter = DbHelper.Instance.Database.DbProviderFactory.CreateParameter(); parameter.ParameterName = para.ParameterName; parameter.Value = para.Value; sql.Parameters.Add(parameter); } foreach (System.Data.Common.DbParameter para in sqlInsert.Parameters) { System.Data.Common.DbParameter parameter = DbHelper.Instance.Database.DbProviderFactory.CreateParameter(); parameter.ParameterName = para.ParameterName; parameter.Value = para.Value; sql.Parameters.Add(parameter); } foreach (System.Data.Common.DbParameter para in sqlUpdate.Parameters) { System.Data.Common.DbParameter parameter = DbHelper.Instance.Database.DbProviderFactory.CreateParameter(); parameter.ParameterName = para.ParameterName; parameter.Value = para.Value; sql.Parameters.Add(parameter); } cmds.Add(new MyDbCommand(sql, ExpectedResultTypes.Special, "1")); //System.Data.DataTable dt = DbHelper.Instance.ExecuteDataTable(sql); //if (dt.Rows[0][0].ToString() == "0") //{ // sql = GenerateInsertCommand(i.TableName, row); // cmds.Add(new MyDbCommand(sql, ExpectedResultTypes.Special, "1")); //} //else //{ // sql = GenerateUpdateCommand(i.TableName, row); // cmds.Add(new MyDbCommand(sql, ExpectedResultTypes.Special, "1")); //} if (tryImport) { break; } } } DbHelper.Instance.UpdateTransaction(cmds); EnableFKConstraint(s_adPreTableName); }