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);
}