近期开发项目中用到的编码小技巧汇总说明
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 += "\r\n" + XmlHelper.XmlSerialize(demo2, Encoding.UTF8);
textBox1.Text += "\r\n" + 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}\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等，如下图示：
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};\r\n", 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}; \r\n", 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类。
