文章出處
文章列表
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="pkWhere"></param>
/// <param name="mapDestColNames"></param>
/// <returns></returns>
public string BuildInsertOrUpdateToDestTableSql(string destTableName, string tempdestTableName, string[] pkWhereColNames, object mapDestColNames)
{
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.Substring(3);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET {1} FROM {0} told INNER JOIN {2} tnew ON {3} " + Environment.NewLine,
destTableName, updateColNames, tempdestTableName, pkWhereSql);
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());
}
}
同步原理如下：
1.定義好查詢源服務器的需要同步的表（一般表中我們定義一個用于是否同步的標識字段，如：TranFlag Bit類型，0表示新數據，未同步，1表示已同步）；
2.查詢源服務器的需要同步的表的記錄（一般是TranFlag=0的記錄），利用SqlDataAdapter+SqlCommandBuilder 裝載Dataset,目的是后續可以利用SqlDataAdapter直接生成更新命令并執行；
3.利用insert into從目的服務器的將被同步的表復制結構產生一個臨時表，表名一般是：#temp_目的服務器的將被同步表名 ，這樣臨時表與實體表的結構完全一致；
4.實例化一個SqlBulkCopy，并建立源服務器的需要同步的表字段與目的臨時表字段的映射，然后執行跨服務器傳輸；
5.利用 BuildInsertOrUpdateToDestTableSql 方法 ，生成 目的服務器的將被同步的表 與 臨時表的插入與更新SQL語句（現在在同一個庫了，想怎么用SQL語句均可）
6.為確保一致性，故外層還需包裹事務SQL語句，若還需加入其它處理SQL，可以加在begin tran ... commit代碼塊中即可，最后執行SQL語句：gExecCommand（ClsDatabase.gExecCommand是一個SQLDB HELPER 類的執行SQL命令的方法）
文章列表
全站熱搜
留言列表
