不是通用的方法，只是提供一个思路。
实体类：
/// <summary>
/// 体例数据实体类
/// </summary>
public class RulesLayout
{
/// <summary>
/// 体例元数据行业代码
/// </summary>
public string hydm { get; set; }
/// <summary>
/// 体例元数据行业名称
/// </summary>
public string hymc { get; set; }
/// <summary>
/// 体例元数据主体代码
/// </summary>
public string ztdm { get; set; }
/// <summary>
/// 体例元数据主体名称
/// </summary>
public string ztmc { get; set; }
/// <summary>
/// 体例元数据结构代码
/// </summary>
public string jgdm { get; set; }
/// <summary>
/// 体例元数据结构名称
/// </summary>
public string jgmc { get; set; }
/// <summary>
/// 体例元数据结构名称同义词
/// </summary>
public string jgmctyc { get; set; }
/// <summary>
/// 体例元数据代码
/// </summary>
public string ysjdm { get; set; }
/// <summary>
/// 体例元数据名称
/// </summary>
public string ysjmc { get; set; }
/// <summary>
/// 体例元数据名称同义词
/// </summary>
public string ysjmctyc { get; set; }
/// <summary>
/// 属性
/// </summary>
public string sx { get; set; }
/// <summary>
/// 状态
/// </summary>
public string zt { get; set; }
}
数据库操作类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections;
using System.Data;
namespace DbProvider
{
/// <summary>
/// 体例数据操作类
/// </summary>
public static class StyleData
{
public static string constr = " 数据库连接字符串";
static OracleConnection con=null;
static OracleTransaction tran = null;
static OracleCommand cmd = null;
/// <summary>
/// 执行一个sql语句，返回第一个值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetOneValue(string sql)
{
using (con = new OracleConnection(constr))
{
con.Open();
cmd = new OracleCommand(sql, con);
return cmd.ExecuteOracleScalar();
}
}
/// <summary>
/// 执行一个sql语句
/// </summary>
/// <param name="sql"></param>
public static int ExecSql(string sql)
{
using (con= new OracleConnection(constr))
{
con.Open();
cmd = new OracleCommand(sql, con);
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行sql语句获取datareader,读取完毕后，必须关闭datareader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static OracleDataReader GetReader(string sql)
{
OracleDataReader dr = null;
con = new OracleConnection(constr);
con.Open();
OracleCommand cmd= new OracleCommand(sql, con);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
/// <summary>
/// 在事务中执行多个sql语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static bool ExeclSqlList(List<string>sql)
{
try
{
con = new OracleConnection(constr);
tran = cmd.Transaction;
con.Open();
con.BeginTransaction();
foreach (string s in sql)
{
cmd = new OracleCommand(s, con, tran);
cmd.ExecuteNonQuery();
}
tran.Commit();
tran.Dispose();
return true;
}
catch
{
tran.Rollback();
tran.Dispose();
return false;
}
}
}
}
业务实现类：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; using System.Data.OleDb; using System.Data.OracleClient; using System.Windows.Forms; using System.Drawing; namespace DbProvider { /// <summary> ///体例元数据业务辅助类 /// </summary> public class StyleDataAssist { /// <summary> /// 创建数据库辅助表，并把表名添加到t_system_data记录 /// </summary> /// <returns></returns> public ArrayList CreateSubsidiaryTable() { ArrayList tbl = new ArrayList(); string str = StyleData.GetOneValue("select max(num) from t_system_data").ToString(); int i = 0; if (String.IsNullOrEmpty(str)) { i = 1; } else { i += 1; } string vocation = "t_rvl_style_vocation" + i.ToString(); string main = "t_rvl_style_main" + i.ToString(); string frame = "t_rvl_style_frame" + i.ToString(); string metadata = "t_rvl_style_metadata" + i.ToString(); //创建辅助表 string sqlvoc = "create table " + vocation + " as select * from t_rvl_style_vocation where 1=1"; string sqlmain = "create table " + main + " as select * from t_rvl_style_main where 1=1"; string sqlfrm = "create table " + frame + " as select * from t_rvl_style_frame where 1=1 "; string sqldat = "create table " + metadata + " as select * from t_rvl_style_metadata where 1=1 "; //将辅助表的信息插入到t_system_data表记录 string names = vocation + "," + main + "," + frame + "," + metadata; string insert = "insert into t_system_data(tablenames,chagetime,num) values('" + names + "','" + DateTime.Now.ToLongDateString() + "'," + i + ")"; List<string> sql = new List<string>(); sql.Add(sqlvoc); sql.Add(sqlmain); sql.Add(sqlfrm); sql.Add(sqldat); if (StyleData.ExeclSqlList(sql)) { tbl.Add(vocation); tbl.Add(main); tbl.Add(frame); tbl.Add(metadata); } return tbl; } /// <summary> /// 从数据库中获取体例元数据 /// </summary> /// <param name="where"></param> /// <returns></returns> public List<RulesLayout> GetStyleData(string where) { List<RulesLayout> List = new List<RulesLayout>(); RulesLayout rule = null; StringBuilder sql = new StringBuilder(); sql.Append("select vc.vocation_code as 行业代码,vc.vocation_name as 行业名称,"); sql.Append(" main.code as 主体代码,main.caption as 主体名称, "); sql.Append(" frame.frame_code as 结构代码, frame.frame_name as 结构名称, frame.frame_name_synonyms as 结构名称同义词, "); sql.Append("data.code as 元数据代码, data.caption as 元数据名称, data.synonyms as 元数据名称同义词,"); sql.Append(" data.attribute as 属性 "); sql.Append(" from t_rvl_style_vocation vc"); sql.Append(" inner join t_rvl_style_main main on vc.vocation_code = main.vocation_code "); sql.Append(" inner join t_rvl_style_frame frame on main.id = frame.main_id "); sql.Append(" inner join t_rvl_style_metadata data on frame.id = data.frame_id where 1=1 "); sql.Append(where); sql.Append(" order by vc.vocation_code "); try { OracleDataReader dr = StyleData.GetReader(sql.ToString()); while (dr.Read()) { rule = new RulesLayout(); rule.hydm = dr["行业代码"].ToString(); rule.hymc = dr["行业名称"].ToString(); rule.ztdm = dr["主体代码"].ToString(); rule.ztmc = dr["主体名称"].ToString(); rule.jgdm = dr["结构代码"].ToString(); rule.jgmc = dr["结构名称"].ToString(); rule.jgmctyc = dr["结构名称同义词"].ToString(); rule.ysjdm = dr["元数据代码"].ToString(); rule.ysjmc = dr["元数据名称"].ToString(); rule.ysjmctyc = dr["元数据名称同义词"].ToString(); rule.sx = dr["属性"].ToString(); List.Add(rule); } dr.Close(); dr.Dispose(); return List; } catch (Exception ex) { return null; } } /// <summary> /// 读取excel数据 /// </summary> /// <param name="filename"></param> /// <returns></returns> public List<RulesLayout> ReadExcel(string filename,string where) { List<RulesLayout> List = new List<RulesLayout>(); RulesLayout rule = null; string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;IMEX=1'"; OleDbConnection Conn = new OleDbConnection(strCon); string strCom = "SELECT distinct * FROM [属性$] where 1=1 "+where; Conn.Open(); OleDbCommand cmd = null; string refmsg = ""; bool result = CheckExcel(Conn, cmd, ref refmsg); if (1 == 1) { cmd = new OleDbCommand(strCom, Conn); OleDbDataReader dr; dr = cmd.ExecuteReader(); while (dr.Read()) { rule = new RulesLayout(); rule.hydm = dr["体例元数据行业代码"].ToString(); rule.hymc = dr["体例元数据行业名称"].ToString(); rule.ztdm = dr["体例元数据主体代码"].ToString(); rule.ztmc = dr["体例元数据主体名称"].ToString(); rule.jgdm = dr["体例元数据结构代码"].ToString(); rule.jgmc = dr["体例元数据结构名称"].ToString(); rule.jgmctyc = dr["体例元数据结构名称同义词"].ToString(); rule.ysjdm =dr["体例元数据代码"].ToString(); rule.ysjmc =dr["体例元数据名称"].ToString(); rule.ysjmctyc =dr["体例元数据名称同义词"].ToString(); rule.sx = dr["属性"].ToString(); List.Add(rule); } Conn.Close(); Conn.Dispose(); return List; } else { Conn.Close(); Conn.Dispose(); return null; } } /// <summary> /// 检查excel数据的准确性 /// </summary> /// <param name="con"></param> /// <param name="cmd"></param> /// <param name="msg"></param> /// <returns></returns> private bool CheckExcel(OleDbConnection con, OleDbCommand cmd, ref string msg) { StringBuilder sql = new StringBuilder(); StringBuilder Msg = new StringBuilder(); sql.Append("SELECT * FROM [属性$] where 体例元数据行业代码<>'' "); sql.Append("or 体例元数据行业名称<>'' or 体例元数据主体代码<>'' "); sql.Append("or 体例元数据主体名称<>'' or 体例元数据结构代码<>'' or 体例元数据结构名称<>''"); cmd = new OleDbCommand(sql.ToString(), con); OleDbDataReader dr; dr = cmd.ExecuteReader(); Msg.Append("excel 数据错误:"); if (dr.Read()) { Msg.Append("体例元数据行业代码:" + dr["体例元数据行业代码"].ToString()); Msg.Append("体例元数据行业名称:" + dr["体例元数据行业名称"].ToString()); Msg.Append("体例元数据主体代码:" + dr["体例元数据主体代码"].ToString()); Msg.Append("体例元数据主体名称:" + dr["体例元数据主体名称"].ToString()); Msg.Append("体例元数据结构代码:" + dr["体例元数据结构代码"].ToString()); Msg.Append("体例元数据结构名称:" + dr["体例元数据结构名称"].ToString()); Msg.Append("\r\n"); } if (Msg.ToString() != "excel 数据错误:") { msg = Msg.ToString(); return false; } else { msg = ""; return true; } } /// <summary> /// 比较excel的数据和数据库的数据,获取差异数据 /// </summary> /// <param name="file">来自文件的数据</param> /// <param name="data">来自数据库原来的数据</param> /// <returns></returns> public List<RulesLayout> CompareDataList(List<RulesLayout> file, List<RulesLayout> data) { List<RulesLayout> Comp = new List<RulesLayout>(); // List<RulesLayout> datacopy = data; RulesLayout rule ; #region 没有发生改变的 Comp = GetSameDataInFile(file, data); List<RulesLayout> sameindata = GetSameDataInData(file, data); #endregion // 从file,data里面移除2者相同的数据，减少下方的遍历次数 foreach (RulesLayout r in Comp) { file.Remove(r); } foreach (RulesLayout r in sameindata) { data.Remove(r); } #region 更改了的 int fnum = file.Count; for (int f = 0; f < fnum; f++) { bool isAdd = true;//是否是新增数据 RulesLayout fuleRule = file[f]; for (int d = 0; d < data.Count; d++) { RulesLayout dataRule = data[d]; bool isquery = (fuleRule.hydm == dataRule.hydm) && (fuleRule.ztdm == dataRule.ztdm) && (fuleRule.jgdm == dataRule.jgdm) && (fuleRule.ysjdm == dataRule.ysjdm); if (isquery) { rule = CompareRule(fuleRule, dataRule); Comp.Add(rule); //发生更改了的数据在原数据库实体类集合里面删除 data.RemoveAt(d); isAdd = false; } } if (isAdd) { fuleRule.zt = "新增"; Comp.Add(fuleRule); } } #endregion #region 获取删除的数据 var deletedata = data; foreach (RulesLayout r in deletedata) { r.zt = "删除"; Comp.Add(r); } #endregion return Comp.OrderBy(m => m.zt).ToList(); } /// <summary> /// 获取修改过的字段信息 /// </summary> /// <param name="file"></param> /// <param name="data"></param> /// <returns></returns> private RulesLayout CompareRule(RulesLayout file, RulesLayout data) { StringBuilder gg = new StringBuilder(); #region 比较2个实体类的每个字段，如果不同，就记录下字段名字 //并最后更新下来自文本的实体类的状态属性 if (file.hydm != data.hydm) { // gg.Append("行业代码,"); gg.Append("hydm,"); } if (file.hymc != data.hymc) { // gg.Append("行业名称,"); gg.Append("hymc,"); } if (file.ztdm != data.ztdm) { //gg.Append("主体代码,"); gg.Append("ztdm,"); } if (file.ztmc != data.ztmc) { //gg.Append("主体名称,"); gg.Append("ztmc,"); } if (file.jgdm != data.jgdm) { //gg.Append("结构代码,"); gg.Append("jgdm,"); } if (file.jgmc != data.jgmc) { // gg.Append("结构名称,"); gg.Append("jgmc,"); } if (file.jgmctyc != data.jgmctyc) { //gg.Append("结构名称同义词,"); gg.Append("jgmctyc,"); } if (file.ysjdm != data.ysjdm) { //gg.Append("元数据代码,"); gg.Append("ysjdm,"); } if (file.ysjmc != data.ysjmc) { //gg.Append("元数据名称,"); gg.Append("ysjmc,"); } if (file.ysjmctyc != data.ysjmctyc) { // gg.Append("元数据名称同义词,"); gg.Append("ysjmctyc,"); } if (file.sx != data.sx) { // gg.Append("属性,"); gg.Append("sx,"); } #endregion file.zt = gg.Append("修改").ToString(); return file; } /// <summary> /// 获取File相同的数据 /// </summary> /// <param name="File"></param> /// <param name="Data"></param> /// <returns></returns> private List<RulesLayout> GetSameDataInFile(List<RulesLayout> File, List<RulesLayout> Data) { var same = from f in File join d in Data on new { hydm= f.hydm, hymc=f.hymc, ztdm=f.ztdm, ztmc=f.ztmc, jgdm=f.jgdm, jgmc=f.jgmc, gjmctyc=f.jgmctyc, ysjdm=f.ysjdm, ysjmc=f.ysjmc, ysjmctyc=f.ysjmctyc, sx=f.sx } equals new { hydm = d.hydm, hymc = d.hymc, ztdm = d.ztdm, ztmc = d.ztmc, jgdm = d.jgdm, jgmc = d.jgmc, gjmctyc = d.jgmctyc, ysjdm = d.ysjdm, ysjmc = d.ysjmc, ysjmctyc = d.ysjmctyc, sx = d.sx } select f; return same.ToList(); } /// <summary> /// 获取File相同的数据 /// </summary> /// <param name="File"></param> /// <param name="Data"></param> /// <returns></returns> private List<RulesLayout> GetSameDataInData(List<RulesLayout> File, List<RulesLayout> Data) { var same = from f in File join d in Data on new { hydm = f.hydm, hymc = f.hymc, ztdm = f.ztdm, ztmc = f.ztmc, jgdm = f.jgdm, jgmc = f.jgmc, gjmctyc = f.jgmctyc, ysjdm = f.ysjdm, ysjmc = f.ysjmc, ysjmctyc = f.ysjmctyc, sx = f.sx } equals new { hydm = d.hydm, hymc = d.hymc, ztdm = d.ztdm, ztmc = d.ztmc, jgdm = d.jgdm, jgmc = d.jgmc, gjmctyc = d.jgmctyc, ysjdm = d.ysjdm, ysjmc = d.ysjmc, ysjmctyc = d.ysjmctyc, sx = d.sx } select d; return same.ToList(); } /// <summary> /// datagridview变色并且发生改变的数据在ToolTipText中显示出改变前的 /// </summary> public void DataGridViewAddColor(DataGridView dataGridView1) { string contr = StyleData.constr; using (OracleConnection con = new OracleConnection(contr)) { con.Open(); OracleCommand cmd = null; int count = dataGridView1.RowCount; #region 遍历改色赋值 for (int i = 0; i < count; i++) { if (dataGridView1.Rows[i].Cells["zt"].Value != null) { string statue = dataGridView1.Rows[i].Cells["zt"].Value.ToString(); if (statue == "新增") { dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.Yellow; } else if (statue == "删除") { dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.Red; } else { string[] cells = statue.Split(','); for (int a = 0; a < cells.Length - 1; a++) { dataGridView1.Rows[i].Cells[cells[a]].Style.BackColor = Color.Green; cmd = new OracleCommand(GetAlterSql(cells[a]), con); cmd.Parameters.Add(":hydm", OracleType.NVarChar); cmd.Parameters.Add(":ztdm", OracleType.NVarChar); cmd.Parameters.Add(":jgdm", OracleType.NVarChar); cmd.Parameters.Add(":ysjdm", OracleType.NVarChar); cmd.Parameters[":hydm"].Value=dataGridView1.Rows[i].Cells["hydm"].Value; cmd.Parameters[":ztdm"].Value = dataGridView1.Rows[i].Cells["ztdm"].Value; cmd.Parameters[":jgdm"].Value = dataGridView1.Rows[i].Cells["jgdm"].Value; cmd.Parameters[":ysjdm"].Value = dataGridView1.Rows[i].Cells["ysjdm"].Value; dataGridView1.Rows[i].Cells[cells[a]].ToolTipText ="数据库原值：\r\n" +cmd.ExecuteScalar().ToString(); } } } } #endregion con.Close(); } } public string GetAlterSql(string column) { StringBuilder sql = new StringBuilder(); sql.Append("select "); switch (column) { case "hymc": sql.Append("vc.vocation_code "); break; case "ztmc": sql.Append(" main.caption "); break; case "jgmc": sql.Append(" frame.frame_name "); break; case "jgmctyc": sql.Append(" frame.frame_name_synonyms "); break; case "ysjmc": sql.Append(" data.caption "); break; case "ysjmctyc": sql.Append(" data.synonyms "); break; case "sx": sql.Append(" data.attribute "); break; default: return "未知错误"; } sql.Append(" from t_rvl_style_vocation vc"); sql.Append(" inner join t_rvl_style_main main on vc.vocation_code = main.vocation_code "); sql.Append(" inner join t_rvl_style_frame frame on main.id = frame.main_id "); sql.Append(" inner join t_rvl_style_metadata data on frame.id = data.frame_id where "); sql.Append(" vc.vocation_code=:hydm and main.code =:ztdm and frame.frame_code=:jgdm "); sql.Append(" and data.code=:ysjdm "); return sql.ToString(); } } }
转载于:https://www.cnblogs.com/liuruitao/p/4184508.html
今天的文章两个实体型之间的三种联系_实体类和数据库字段不对应分享到此就结束了，感谢您的阅读。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/88208.html
