/// <summary>
/// 把现有数据库中的所有配置保存到数据文件中(单个文件)
/// </summary>
/// <param name="filePath"></param>
public static void ExportAllToXmlFile(string filePath)
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(filePath, false);
ExcelXmlHelper.WriteExcelXmlHead(sw);
foreach (string s in s_adTables)
{
ExcelXmlHelper.WriteExcelXml(Feng.Data.DbHelper.Instance.ExecuteDataTable("SELECT * FROM " + s), sw, s);
}
ExcelXmlHelper.WriteExcelXmlTail(sw);
sw.Close();
}
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);
}
private void btnExportYwsj_Click(object sender, EventArgs e)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.RestoreDirectory = true;
dlg.Filter = "Excel Xml(*.xml)|*.xml";
//saveFileDialog1.Title = "保存";
string today = System.DateTime.Today.ToString("yyyy-MM-dd");
today = "2010-01-28";
string whereSql = "(A.Created >= '" + today + "' OR A.Updated >= '" + today + "')";
string[] sqls = new string[] {
"SELECT A.* FROM 财务_费用实体 A WHERE (A.费用实体类型 = 11 OR A.费用实体类型 = 15 OR A.费用实体类型 = 45) AND " + whereSql,
"SELECT B.* FROM 业务备案_普通票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_进口票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_内贸出港票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_进口其他业务票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT A.* FROM 业务备案_普通箱 A WHERE " + whereSql,
"SELECT B.* FROM 业务备案_进口箱 B INNER JOIN 业务备案_普通箱 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_内贸出港箱 B INNER JOIN 业务备案_普通箱 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_进口其他业务箱 B INNER JOIN 业务备案_普通箱 A ON A.ID = B.ID AND " + whereSql,
"SELECT A.* FROM 业务过程_进口票_转关标志 A WHERE " + whereSql,
"SELECT B.* FROM 业务过程_进口票_转关 B INNER JOIN 业务过程_进口票_转关标志 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务过程_进口票_清关 B INNER JOIN 业务过程_进口票_转关标志 A ON A.ID = B.ID AND " + whereSql,
"SELECT A.* FROM 参数备案_币制 A WHERE " + whereSql,
"SELECT A.* FROM 参数备案_人员单位 A WHERE " + whereSql,
"SELECT A.* FROM 参数备案_箱型 A WHERE " + whereSql,
"SELECT A.* FROM 信息_角色用途 A WHERE " + whereSql,
"SELECT A.* FROM 信息_业务类型 A WHERE " + whereSql
};
string[] dbTables = new string[] {
"财务_费用实体",
"业务备案_普通票",
"业务备案_进口票",
"业务备案_内贸出港票",
"业务备案_进口其他业务票",
"业务备案_普通箱",
"业务备案_进口箱",
"业务备案_内贸出港箱",
"业务备案_进口其他业务箱",
"业务过程_进口票_转关标志",
"业务过程_进口票_转关",
"业务过程_进口票_清关",
"参数备案_币制",
"参数备案_人员单位",
"参数备案_箱型",
"信息_角色用途",
"信息_业务类型"
};
if (dlg.ShowDialog() == DialogResult.OK)
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(dlg.FileName, false, Encoding.UTF8);
ExcelXmlHelper.WriteExcelXmlHead(sw);
if (dbTables.Length != sqls.Length)
{
throw new ArgumentException("length should be equal!");
}
for (int i = 0; i < sqls.Length; ++i)
{
ExcelXmlHelper.WriteExcelXml(Feng.Data.DbHelper.Instance.ExecuteDataTable(sqls[i]), sw, dbTables[i]);
}
ExcelXmlHelper.WriteExcelXmlTail(sw);
sw.Close();
}
}
public string GenerateDate(DateTime dateBegin, DateTime dateEnd, int type, int cliendId)
{
string d1 = dateBegin.ToString("yyyy-MM-dd");
string d2 = dateEnd.AddDays(1).ToString("yyyy-MM-dd");
string whereSql = "((A.Created >= '" + d1 + "' OR A.Updated >= '" + d1 + "') AND (A.Created <= '" + d2 + "' OR A.Updated <= '" + d2 + "') AND ClientId = " + cliendId + ")";
string whereSql2 = "(A.Created >= '" + d1 + "' AND A.Created <= '" + d2 + "')";
if (type == 1)
{
string[] sqls = new string[] {
"SELECT A.ID, A.Version, A.编号, A.费用实体类型, A.Submitted, A.CreatedBy, A.Created, A.UpdatedBy, A.Updated, A.ClientId, A.OrgId, A.IsActive FROM 财务_费用实体 A WHERE (A.费用实体类型 = 11 OR A.费用实体类型 = 15 OR A.费用实体类型 = 45) AND " + whereSql,
"SELECT B.ID, B.货代自编号, B.委托时间, B.委托人, B.提单号, B.代表性箱号, B.合同号, B.报检号, B.报关单号, B.船公司, B.船名航次, B.箱量, B.标箱量, B.货物类别, B.件数, B.单价, B.总重量, B.内部备注, B.对上备注, B.对下备注, B.允许应收对账 FROM 业务备案_普通票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_进口票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_内贸出港票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_进口其他业务票 B INNER JOIN 财务_费用实体 A ON A.ID = B.ID AND " + whereSql,
"SELECT A.* FROM 业务备案_普通箱 A WHERE " + whereSql,
"SELECT B.* FROM 业务备案_进口箱 B INNER JOIN 业务备案_普通箱 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_内贸出港箱 B INNER JOIN 业务备案_普通箱 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务备案_进口其他业务箱 B INNER JOIN 业务备案_普通箱 A ON A.ID = B.ID AND " + whereSql,
"SELECT A.* FROM 业务过程_进口票_转关标志 A WHERE " + whereSql,
"SELECT B.* FROM 业务过程_进口票_转关 B INNER JOIN 业务过程_进口票_转关标志 A ON A.ID = B.ID AND " + whereSql,
"SELECT B.* FROM 业务过程_进口票_清关 B INNER JOIN 业务过程_进口票_转关标志 A ON A.ID = B.ID AND " + whereSql,
"SELECT A.* FROM 参数备案_币制 A WHERE " + whereSql,
"SELECT A.* FROM 参数备案_人员单位 A WHERE " + whereSql,
"SELECT A.* FROM 参数备案_箱型 A WHERE " + whereSql,
"SELECT A.* FROM 信息_角色用途 A WHERE " + whereSql,
"SELECT A.* FROM 信息_费用类别 A WHERE " + whereSql/*,
* "SELECT A.* FROM SD_AuditLogRecord A WHERE " + whereSql2*/
};
string[] dbTables = new string[] {
"财务_费用实体",
"业务备案_普通票",
"业务备案_进口票",
"业务备案_内贸出港票",
"业务备案_进口其他业务票",
"业务备案_普通箱",
"业务备案_进口箱",
"业务备案_内贸出港箱",
"业务备案_进口其他业务箱",
"业务过程_进口票_转关标志",
"业务过程_进口票_转关",
"业务过程_进口票_清关",
"参数备案_币制",
"参数备案_人员单位",
"参数备案_箱型",
"信息_角色用途",
"信息_费用类别",
/*"SD_AuditLogRecord"*/ };
string fileName = cliendId + "-" + type + "-" + d1 + "-" + d2 + ".xml";
System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName, false, Encoding.UTF8);
ExcelXmlHelper.WriteExcelXmlHead(sw);
if (dbTables.Length != sqls.Length)
{
throw new ArgumentException("length should be equal!");
}
for (int i = 0; i < sqls.Length; ++i)
{
ExcelXmlHelper.WriteExcelXml(Feng.Data.DbHelper.Instance.ExecuteDataTable(sqls[i]), sw, dbTables[i]);
}
ExcelXmlHelper.WriteExcelXmlTail(sw);
sw.Close();
string zipFileName = System.IO.Path.ChangeExtension(fileName, ".zip");
CompressionHelper.ZipFromFile(fileName, zipFileName);
return(zipFileName);
}
else
{
throw new ArgumentException("Invalid type!");
}
}
