近期开发项目中用到的编码小技巧汇总说明

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 += "
" + XmlHelper.XmlSerialize(demo2, Encoding.UTF8);

            textBox1.Text += "
" + 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方法，该方法逻辑很简单，就是根据绑定的属性层级（.分隔）层层遍历获取属性的值，直到遍历完或为空时停止，最后得到的结果即是绑定的属性的值。最终实现的效果如下图示：

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}	 SendData_CustomerAuthorization 	 Succeed:{1}", DateTime.Now, stable.Rows.Count));

                                     return true;

                                 });
            }
            catch (Exception ex)
            {
                master.WriteMsg(master.lstErrorInfo, DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "	" + "SendData_CustomerAuthorization" + "	" + 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等，如下图示：

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};
", 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}; 
", 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类。