您的位置:威尼斯官方网站 > 威尼斯正规官网 > DataEntities是切实可行的EF上下文对象

DataEntities是切实可行的EF上下文对象

发布时间:2019-11-27 17:29编辑:威尼斯正规官网浏览(184)

    1.默认EF生成的连接字符串比较的长和怪异,若想使用普通的连接字符串来连接EF,则可以通过创建分部类,并重写一个构造函数,在构造函数中通过动态拼接EntityConnectionString得到EF所需的连接字符串,具代实现代码如下:

        public partial class DataEntities
        {
            private static ConcurrentDictionary<string, string> entityConnStrings = new ConcurrentDictionary<string, string>();
    
            public DataEntities(string connName)
                : base(BuildEntityConnectionString(connName))
            {
    
            }
    
            private static string BuildEntityConnectionString(string connName)
            {
    
                if (!entityConnStrings.ContainsKey(connName))
                {
                    var connStrSetting = System.Configuration.ConfigurationManager.ConnectionStrings[connName];
    
                    EntityConnectionStringBuilder entityConnStrBuilder = new EntityConnectionStringBuilder();
                    entityConnStrBuilder.Provider = connStrSetting.ProviderName;
                    entityConnStrBuilder.ProviderConnectionString = EncryptUtility.DesDecrypt("XXXXX", connStrSetting.ConnectionString);
                    entityConnStrBuilder.Metadata = "res://*/Data.csdl|res://*/Data.ssdl|res://*/Data.msl";
                    string entityConnString = entityConnStrBuilder.ToString();
                    entityConnStrings.AddOrUpdate(connName, entityConnString, (key, value) => entityConnString);
                }
                return entityConnStrings[connName];
            }
        }
    

    注意上面的类是一个分部类:partial,同时BuildEntityConnectionString方法是一个静态方法,在BuildEntityConnectionString方法中ProviderConnectionString = EncryptUtility.DesDecrypt("XXXXX", connStrSetting.ConnectionString);是关键,我这里是对config中的连接字符串 也都进行了加密,故此处我需要解密,若无这个需求可以直接:ProviderConnectionString =connStrSetting.ConnectionString即可。后续实例化EF上下文对象时,请使用:DataEntities(string connName)这个构造涵数即可,DataEntities是具体的EF上下文对象,大家的EF上下文类名均可能不相同。

    2.支持一个通用对象的XML序列化(即:一个类中有可变类型属性成员,需要不同的序列结果及生成不同的序列元素名称),具体实现代码如下:

    一个需要被序列化成XML的类:其中要求生成的XML元素detail必需有子元素,且子元素名称及子元素内部属性根据类型的不同而不同(即:detail元素下的子元素是可变的)

        [XmlRootAttribute("master")]
        public class DemoMaster<T> where T : class
        {
            [XmlElement("attr")]
            public string DemoAttr { get; set; }
    
            [XmlElement("detail")]
            public DemoDetail<T> DemoDetail { get; set; } //关键点在这里,该属性元素为:detail,但其子元素根据T不同而不同
    
        }
        public class DemoDetail<T> : IXmlSerializable where T : class
        {
            public T body { get; set; }
    
            public System.Xml.Schema.XmlSchema GetSchema()
            {
                return null;
            }
    
            public void ReadXml(System.Xml.XmlReader reader)
            {
                string bodyStr = reader.ReadInnerXml();
                this.body = XmlHelper.XmlDeserialize<T>(bodyStr, Encoding.UTF8);
            }
    
            public void WriteXml(System.Xml.XmlWriter writer)
            {
                writer.WriteRaw(XmlHelper.XmlSerialize(this.body, Encoding.UTF8, true));
            }
        }
    
        [XmlTypeAttribute("list-a", AnonymousType = false)]
        public class DemoDetailA
        {
            public string Apro1 { get; set; }
    
            public string Apro2 { get; set; }
    
            public string Apro3 { get; set; }
        }
    
        [XmlTypeAttribute("list-b", AnonymousType = false)]
        public class DemoDetailB
        {
            public string Bpro1 { get; set; }
    
            public string Bpro2 { get; set; }
    
            public string Bpro3 { get; set; }
        }
    
        [XmlTypeAttribute("list-c", AnonymousType = false)]
        public class DemoDetailC
        {
            public string Cpro1 { get; set; }
    
            public string Cpro2 { get; set; }
    
            public string Cpro3 { get; set; }
        }
    

    注意上面代码中,需要关注:DemoDetail属性及DemoDetail<T>类,DemoDetail属性仅是为了生成detail元素节点,而子节点则由DemoDetail<T>类来进行生成,DemoDetail<T>是实现了IXmlSerializable接口,在XML序列化时,DemoDetail<T>类仅将body属性对应的T类型实例内容进行序列化(WriteRaw),而反序列化时,则先反序列化body属性对应的T类型实例,然后赋值给body属性,这也是巧妙之处,DemoDetail<T>类本身并没有真正参与到序列化中,故序列化的字符串也看不到DemoDetail<T>类相关的元素,DemoDetail<T>类仅仅是一个XML序列化格式生成的中介。序列化的XML结果如下:

    序列化代码:

                var demo1 = new DemoMaster<DemoDetailA>()
                {
                    DemoAttr = "demo1",
                    DemoDetail = new DemoDetail<DemoDetailA>() { body = new DemoDetailA() { Apro1 = "demoA1", Apro2 = "demoA2", Apro3 = "demoA3" } }
                };
    
                var demo2 = new DemoMaster<DemoDetailB>()
                {
                    DemoAttr = "demo2",
                    DemoDetail = new DemoDetail<DemoDetailB>() { body = new DemoDetailB() { Bpro1 = "demoB1", Bpro2 = "demoB2", Bpro3 = "demoB3" } }
                };
    
                var demo3 = new DemoMaster<DemoDetailC>()
                {
                    DemoAttr = "demo3",
                    DemoDetail = new DemoDetail<DemoDetailC>() { body = new DemoDetailC() { Cpro1 = "demoC1", Cpro2 = "demoC2", Cpro3 = "demoC3" } }
                };
    
                textBox1.Text = XmlHelper.XmlSerialize(demo1, Encoding.UTF8);
    
                textBox1.Text += "rn" + XmlHelper.XmlSerialize(demo2, Encoding.UTF8);
    
                textBox1.Text += "rn" + XmlHelper.XmlSerialize(demo3, Encoding.UTF8);
    

    序列化的XML:

    <?xml version="1.0" encoding="utf-8"?>
    <master>
        <attr>demo1</attr>
        <detail><list-a>
        <Apro1>demoA1</Apro1>
        <Apro2>demoA2</Apro2>
        <Apro3>demoA3</Apro3>
    </list-a></detail>
    </master>
    
    <?xml version="1.0" encoding="utf-8"?>
    <master>
        <attr>demo2</attr>
        <detail><list-b>
        <Bpro1>demoB1</Bpro1>
        <Bpro2>demoB2</Bpro2>
        <Bpro3>demoB3</Bpro3>
    </list-b></detail>
    </master>
    
    <?xml version="1.0" encoding="utf-8"?>
    <master>
        <attr>demo3</attr>
        <detail><list-c>
        <Cpro1>demoC1</Cpro1>
        <Cpro2>demoC2</Cpro2>
        <Cpro3>demoC3</Cpro3>
    </list-c></detail>
    </master>
    

    3.winform DataGridView 实现指定列采取密码框模式显示与编辑,以及列绑定到复合属性(即:绑定到多层次属性),具体实现代码如下:

                dataGridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(dataGridView1_CellFormatting);
                dataGridView1.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dataGridView1_EditingControlShowing);
    
    
            public string EvaluateValue(object obj, string property)
            {
                string retValue = string.Empty;
                string[] names = property.Split('.');
    
                for (int i = 0; i < names.Count(); i++)
                {
                    try
                    {
                        var prop = obj.GetType().GetProperty(names[i]);
                        var result = prop.GetValue(obj, null);
                        if (result != null)
                        {
                            obj = result;
                            retValue = result.ToString();
                        }
                        else
                        {
                            break;
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
    
                return retValue;
            }
    
    
            private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
            {
    
                if (dataGridView1.Columns[e.ColumnIndex].DataPropertyName.Contains("."))
                {
                    e.Value = EvaluateValue(dataGridView1.Rows[e.RowIndex].DataBoundItem, dataGridView1.Columns[e.ColumnIndex].DataPropertyName);
                }
    
    
                if (dataGridView1.Columns[e.ColumnIndex].Name == "KeyCode")
                {
                    if (e.Value != null && e.Value.ToString().Length > 0)
                    {
                        e.Value = new string('*', e.Value.ToString().Length);
                    }
                }
            }
    
            private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
            {
                int i = this.dataGridView1.CurrentCell.ColumnIndex;
                bool usePassword = false;
                if (dataGridView1.Columns[i].Name == "KeyCode")
                {
                    usePassword = true;
                }
                TextBox txt = e.Control as TextBox;
                if (txt != null)
                {
                    txt.UseSystemPasswordChar = usePassword;
                }
            }
    
    //示例:绑定的源数据类定义
        public class DemoBindClass
        {
            public string Attr { get; set; }
    
            public string KeyCode { get; set; }
    
            public DemoDetailA Detail { get; set; }
        }
    
    
        public class DemoDetailA
        {
            public string Apro1 { get; set; }
    
            public string Apro2 { get; set; }
    
            public string Apro3 { get; set; }
    
            public DemoDetailB DetailChild { get; set; }
        }
    
    
        public class DemoDetailB
        {
            public string Bpro1 { get; set; }
    
            public string Bpro2 { get; set; }
    
            public string Bpro3 { get; set; }
        }
    

    绑定到数据源:

                var demo = new[] {
                    new DemoBindClass()
                        {
                            Attr = "demo",
                            KeyCode="a123456789b",
                            Detail = new DemoDetailA()
                            {
                                Apro1 = "demoA1",
                                Apro2 = "demoA2",
                                Apro3 = "demoA3",
                                DetailChild = new DemoDetailB()
                                {
                                    Bpro1 = "demoB1",
                                    Bpro2 = "demoB2",
                                    Bpro3 = "demoB3"
                                }
                            }
                        }
                };
    
    
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = demo;
    

    实现指定列采取密码框模式显示与编辑,以及列绑定到复合属性均需要订阅DataGridView的CellFormatting及EditingControlShowing事件,并在其中写转换当前Cell的Value,实现列绑定到复合属性,关键点在:EvaluateValue方法,该方法逻辑很简单,就是根据绑定的属性层级(.分隔)层层遍历获取属性的值,直到遍历完或为空时停止,最后得到的结果即是绑定的属性的值。最终实现的效果如下图示:

    图片 1

    4.利用BCP(sqlbulkcopy)来实现两个不同数据库之间进行数据差异传输(即:数据同步)

    TransferBulkCopy作用:实现两个不同数据库之间进行数据差异传输,BuildInsertOrUpdateToDestTableSql作用:根据目的表及临时表生成更新与插入记录的SQL语句,以此实现:若同步的数据已存在,则更新,不存在,则插入。

            /// <summary>
            /// 通用数据传输方法(采用SqlBulkCopy快速批量插入,然后再进行处理)
            /// </summary>
            /// <param name="sourceSelectSql"></param>
            /// <param name="sourceConn"></param>
            /// <param name="destTableName"></param>
            /// <param name="destConn"></param>
            /// <param name="colMapFunc"></param>
            /// <param name="lastSaveAction"></param>
            public void TransferBulkCopy(string sourceSelectSql, SqlConnection sourceConn, string destTableName, SqlConnection destConn, Func<DataTable, Dictionary<string, string>> colMapFunc,
                                        Func<string, DataTable, SqlConnection, SqlConnection, bool> lastSaveAction, bool closeConnection = true)
            {
                DataTable srcTable = new DataTable();
                SqlDataAdapter srcAdapter = new SqlDataAdapter(sourceSelectSql, sourceConn);
                srcAdapter.AcceptChangesDuringUpdate = false;
                SqlCommandBuilder srcCmdBuilder = new SqlCommandBuilder(srcAdapter);
                srcAdapter.Fill(srcTable);
    
                if (srcTable != null && srcTable.Rows.Count > 0)
                {
                    string tempDestTableName = "#temp_" + destTableName;
                    ClsDatabase.gExecCommand(destConn, string.Format("select top 0 * into {0}  from  {1}", tempDestTableName, destTableName), false);
                    List<string> mapDestColNameList = new List<string>();
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(destConn))
                    {
                        sqlBulkCopy.DestinationTableName = tempDestTableName;
                        foreach (var map in colMapFunc(srcTable))
                        {
                            sqlBulkCopy.ColumnMappings.Add(map.Key, map.Value);
                            mapDestColNameList.Add(map.Value);
                        }
                        sqlBulkCopy.WriteToServer(srcTable);
                    }
                    srcTable.ExtendedProperties.Add(MapDestColNames_String, mapDestColNameList);
                    bool needUpdate = lastSaveAction(tempDestTableName, srcTable, destConn, sourceConn);
    
                    if (needUpdate)
                    {
                        if (srcTable.Columns.Contains("TranFlag"))
                        {
                            foreach (DataRow row in srcTable.Rows)
                            {
                                row["TranFlag"] = true;
                            }
                        }
                        srcAdapter.Update(srcTable);
                    }
    
                }
    
                if (closeConnection)
                {
                    DisposeConnections(sourceConn, destConn);
                }
            }
    

     

            /// <summary>
            /// 生成同步插入及更新目的表SQL语句
            /// </summary>
            /// <param name="destTableName"></param>
            /// <param name="tempdestTableName"></param>
            /// <param name="pkWhereColNames"></param>
            /// <param name="mapDestColNames"></param>
            /// <param name="sqlType">0=生成INSERT与UPDATE;1=生成UPDATE语句;2=生成INSERT语句</param>
            /// <returns></returns>
            public string BuildInsertOrUpdateToDestTableSql(string destTableName, string tempdestTableName, string[] pkWhereColNames, object mapDestColNames, int sqlType = 0)
            {
                var mapDestColNameList = mapDestColNames as List<string>;
                string updateColNames = null;
                foreach (string col in mapDestColNameList)
                {
                    if (!pkWhereColNames.Contains(col, StringComparer.OrdinalIgnoreCase))
                    {
                        updateColNames += string.Format(",{0}=tnew.{0}", col);
                    }
                }
                updateColNames = updateColNames.Substring(1);
                string insertColNames = string.Join(",", mapDestColNameList);
    
                string pkWhereSql = null;
                foreach (string col in pkWhereColNames)
                {
                    pkWhereSql += string.Format(" and told.{0}=tnew.{0} ", col);
                }
                pkWhereSql = pkWhereSql.Trim().Substring(3);
    
                StringBuilder sqlBuilder = new StringBuilder();
    
                if (sqlType == 0 || sqlType == 1)
                {
                    sqlBuilder.AppendFormat("UPDATE {0}  SET {1} FROM {0} told INNER JOIN {2} tnew ON {3}  " + Environment.NewLine,
                                            destTableName, updateColNames, tempdestTableName, pkWhereSql);
                }
    
                if (sqlType == 0 || sqlType == 2)
                {
                    sqlBuilder.AppendFormat("INSERT INTO {0}({1}) SELECT {1} FROM {2} tnew WHERE NOT EXISTS(SELECT 1 FROM {0} told WHERE {3}) " + Environment.NewLine,
                                            destTableName, insertColNames, tempdestTableName, pkWhereSql);
    
                }
    
                return sqlBuilder.ToString();
    
            }
    

      

     

    使用示例如下:

            public void SendData_CustomerAuthorization()
            {
                try
                {
                    SqlConnection obConnLMS1 = new SqlConnection(master.connLMSStr);
                    SqlConnection obConnWEB1 = new SqlConnection(master.connWEBStr);
    
                    string selectSql = @"SELECT TOP {0} Id,Phone,Mac,IsSet,LastLoginTime,PCName,TranFlag FROM TWEB_CustomerAuthorization WHERE TranFlag=0 ORDER BY Id ";
    
                    selectSql = string.Format(selectSql, master.batchSize);
    
                    master.TransferBulkCopy(selectSql, obConnWEB1,
                                    "TB_CustomerAuthorization", obConnLMS1,
                                     (stable) =>
                                     {
                                         var colMaps = new Dictionary<string, string>();
                                         foreach (DataColumn col in stable.Columns)
                                         {
                                             if (!col.ColumnName.Equals("TranFlag", StringComparison.OrdinalIgnoreCase))
                                             {
                                                 colMaps.Add(col.ColumnName, col.ColumnName);
                                             }
                                         }
                                         return colMaps;
                                     },
                                     (tempTableName, stable, destConn, srcConn) =>
                                     {
                                         StringBuilder saveSqlBuilder = new StringBuilder("begin tran" + Environment.NewLine);
    
                                         string IUSql = master.BuildInsertOrUpdateToDestTableSql("TB_CustomerAuthorization", tempTableName, new[] { "Id" }, stable.ExtendedProperties[master.MapDestColNames_String]);
                                         saveSqlBuilder.Append(IUSql);
    
                                         saveSqlBuilder.AppendLine("commit");
    
                                         ClsDatabase.gExecCommand(destConn, saveSqlBuilder.ToString());
    
                                         master.WriteMsg(master.lstSended, string.Format("上传时间:{0:yyyy-MM-dd HH:mm}t SendData_CustomerAuthorization t Succeed:{1}", DateTime.Now, stable.Rows.Count));
    
                                         return true;
    
                                     });
                }
                catch (Exception ex)
                {
                    master.WriteMsg(master.lstErrorInfo, DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "t" + "SendData_CustomerAuthorization" + "t" + ex.Message.ToString());
                }
            }
    

    同步原理如下:

    4.1.定义好查询源服务器的需要同步的表(一般表中我们定义一个用于是否同步的标识字段,如:TranFlag Bit类型,0表示新数据,未同步,1表示已同步);

    4.2.查询源服务器的需要同步的表的记录(一般是TranFlag=0的记录),利用SqlDataAdapter+SqlCommandBuilder 装载Dataset,目的是后续可以利用SqlDataAdapter直接生成更新命令并执行;

    4.3.利用insert into从目的服务器的将被同步的表复制结构产生一个临时表,表名一般是:#temp_目的服务器的将被同步表名 ,这样临时表与实体表的结构完全一致;

    4.4.实例化一个SqlBulkCopy,并建立源服务器的需要同步的表字段与目的临时表字段的映射,然后执行跨服务器传输;

    4.5.利用 BuildInsertOrUpdateToDestTableSql 方法 ,生成  目的服务器的将被同步的表 与 临时表的插入与更新SQL语句(现在在同一个库了,想怎么用SQL语句均可)  

    4.6.为确保一致性,故外层还需包裹事务SQL语句,若还需加入其它处理SQL,可以加在begin tran  ... commit代码块中即可,最后执行SQL语句:gExecCommand(ClsDatabase.gExecCommand是一个SQLDB HELPER 类的执行SQL命令的方法)

     

    5.实现同一个WINDOWS SERVICE程序 COPY多份,然后通过更改自定义的服务ID(ServiceID)配置项来实现:同一个服务程序安装成多个不同的WINDOWS服务进程:

    5.1.创建一个WINDOWS服务项目,在ProjectInstaller设计器界面通过右键弹出菜单选择安装程序(serviceProcessInstaller1、serviceInstaller1)、并设置好ServiceName、DisplayName、Description、Account等,如下图示:

    图片 2

    5.2.在ProjectInstaller构造函数中增加从CONFIG文件中读取自定义的服务ID(ServiceID)配置项的值,然后将ServiceID拼加到预设的ServiceName后面,以便实际根据ServiceID能够安装成不同ServiceID后缀的服务进程,关键点在于改变ServiceName,另一个关键点是从CONFIG文件中获取ServiceID,由于安装时,传统的方式无法正常读取到CONFIG,只能通过Assembly.GetExecutingAssembly().Location 来获取当前执行的程序集的路径再拼成CONFIG文件路径,最后读出ServiceID的值,示例代码如下:

        public partial class ProjectInstaller : System.Configuration.Install.Installer
        {
            public ProjectInstaller()
            {
                InitializeComponent();
    
                string assyLocation = System.Reflection.Assembly.GetExecutingAssembly().Location;
                string assyCfgPath = assyLocation + ".config";
                string installServiceLogPath = Path.Combine(Path.GetDirectoryName(assyLocation), "InstallServiceLog.log");
    
                string serviceID = ConfigUtil.GetAppSettingValueForConfigPath("ServiceID", assyCfgPath);
    
                System.IO.File.AppendAllText(installServiceLogPath, string.Format("[{0:yyyy-MM-dd HH:mm:ss}] ServiceAssembly ConfigPath:{1};rn", DateTime.Now, assyCfgPath));
    
                if (!string.IsNullOrWhiteSpace(serviceID))
                {
                    this.serviceInstaller1.DisplayName = "TestService_" + serviceID;
                    this.serviceInstaller1.ServiceName = "TestService_" + serviceID;
                }
    
                System.IO.File.AppendAllText(installServiceLogPath, string.Format("[{0:yyyy-MM-dd HH:mm:ss}] ProjectInstaller.ProjectInstaller() ->ServiceID:{1},ServiceName:{2}; rn", DateTime.Now, serviceID, this.serviceInstaller1.ServiceName));
            }
        }
    

    5.3.在服务类的构造函数中同样增加从CONFIG中读取自定义的服务ID(ServiceID)配置项的值,然后将ServiceID拼加到预设的ServiceName后面(注意应与上述ProjectInstaller中指定的ServiceName相同),示例代码如下:  

    public partial class TestService: ServiceBase
    {
         public TestService()
        {
              serviceID = ConfigUtil.GetAppSettingValue("ServiceID");
                if (!string.IsNullOrWhiteSpace(serviceID))
                {
                    this.ServiceName = "TestService_" + serviceID;
                }
        }
    
    }
    

     上述三步就完成了同一个服务程序安装成多个不同的WINDOWS服务进程,这个还是比较实用的哦!上述ConfigUtil是封装的一个配置文件读写帮助类,之前文章有介绍,后面也会发布一个更完整的ConfigUtil类。

    本文由威尼斯官方网站发布于威尼斯正规官网,转载请注明出处:DataEntities是切实可行的EF上下文对象

    关键词:

上一篇:则 整个排序输出完成

下一篇:没有了