图片 3

多年来付出项目中用到的编码小技能汇总表明,开辟品种小手艺

1.暗中同意EF生成的连接字符串相比的长和新奇,若想行使普通的三番一次字符串来连接EF,则足以由此创办分门类,一碗水端平写二个布局函数,在构造函数中通过动态拼接EntityConnectionString获得EF所需的连年字符串,具代实今世码如下:

近来花费项目中用到的编码小工夫汇总表达,开荒品种小技术

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属性及德姆oDetail<T>类,德姆oDetail属性仅是为着生成detail成分节点,而子节点则由德姆oDetail<T>类来举办转移,德姆oDetail<T>是完成了IXmlSerializable接口,在XML类别化时,DemoDetail<T>类仅将body属性对应的T类型实例内容开展种类化(WriteRaw),而反连串化时,则先反连串化body属性对应的T类型实例,然后赋值给body属性,那也是抢眼之处,德姆oDetail<T>类本人并不曾真正参预到体系化中,故系列化的字符串也看不到德姆oDetail<T>类相关的因素,德姆oDetail<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

  

 

1.暗中认可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; }
    }

潜心上边代码中,必要关爱:德姆oDetail属性及德姆oDetail<T>类,德姆oDetail属性仅是为了生成detail元秋天点,而子节点则由德姆oDetail<T>类来拓宽转移,德姆oDetail<T>是完结了IXml塞里alizable接口,在XML系列化时,德姆oDetail<T>类仅将body属性对应的T类型实例内容开展连串化(WriteRaw),而反体系化时,则先反连串化body属性对应的T类型实例,然后赋值给body属性,这也是美妙之处,德姆oDetail<T>类本人并不曾真正到场到连串化中,故类别化的字符串也看不到德姆oDetail<T>类相关的因素,德姆oDetail<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方法,该方式逻辑相当的轻便,正是依据绑定的性质层级(.分隔)层层遍历获取属性的值,直到遍历完或为空时甘休,最后拿到的结果正是绑定的个性的值。最终促成的功力如下图示:

图片 2

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
HELPELX570 类的举办SQL命令的情势)

 

5.实现同一个WINDOWS SE福特ExplorerVICE程序
COPY多份,然后经过改动自定义的劳动ID(ServiceID卡塔尔配置项来促成:同一个服务程序安装成多个不等的WINDOWS服务进度:

5.1.创设二个WINDOWS服务项目,在ProjectInstaller设计器界面通过右键弹出美食指南选取安装程序(serviceProcessInstaller1、serviceInstaller1)、并安装好ServiceName、DisplayName、Description、Account等,如下图示:

图片 3

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类。

发表评论

电子邮件地址不会被公开。 必填项已用*标注