1.默认EF生成的连接字符串比较的长和怪异,若想使用普通的连接字符串来连接EF,则可以通过创建分部类,并重写一个构造函数,在构造函数中通过动态拼接EntityConnectionString得到EF所需的连接字符串,具代实现代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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元素下的子元素是可变的)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
[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结果如下:
序列化代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
<?
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 实现指定列采取密码框模式显示与编辑,以及列绑定到复合属性(即:绑定到多层次属性),具体实现代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
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
; }
}
|
绑定到数据源:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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语句,以此实现:若同步的数据已存在,则更新,不存在,则插入。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
/// <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);
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
/// <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();
}
|
使用示例如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
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的值,示例代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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相同),示例代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
|
public
partial
class
TestService: ServiceBase
{
public
TestService()
{
serviceID = ConfigUtil.GetAppSettingValue(
"ServiceID"
);
if
(!
string
.IsNullOrWhiteSpace(serviceID))
{
this
.ServiceName =
"TestService_"
+ serviceID;
}
}
}
|
上述三步就完成了同一个服务程序安装成多个不同的WINDOWS服务进程,这个还是比较实用的哦!上述ConfigUtil是封装的一个配置文件读写帮助类,之前文章有介绍,后面也会发布一个更完整的ConfigUtil类。
本文转自 梦在旅途 博客园博客,原文链接: http://www.cnblogs.com/zuowj/p/6264711.html ,如需转载请自行联系原作者