Microsoft Dynamics CRM 4 Plugin 知识总结

1.Plugin Demo:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;

namespace Www.company.Com.Crm40.Plugins
{
public class SaveMaintainApp:IPlugin
{
public void Execute(IPluginExecutionContext context)
{
CommonFunction cf = new CommonFunction();
cf.InitializeConfigFromXML();
cf.WriteLog("", "初始化", "Stage=" + context.Stage + ";MessageName=" + context.MessageName);
if (context.Stage == 10)
{
switch (context.MessageName)
{ 
case MessageName.Delete:
DoDeletePre(context, cf);
break;
}
}
if (context.Stage == 50)
{
switch (context.MessageName)
{
case MessageName.Create:
CrmUtil cu = new CrmUtil();
cu.LogFile += "company_test_new_maintain_app_PlugIn\\SaveMaintainApp";
DoCreatPost(context, cu);
break;
}
}
}
//创建
private void DoCreatPost(IPluginExecutionContext context, CrmUtil cu)
{
if (context.InputParameters.Properties.Contains(ParameterName.Target) && context.InputParameters.Properties[ParameterName.Target] is DynamicEntity)
{
DynamicEntity createEntity = (DynamicEntity)context.PostEntityImages["postImage"];

if (createEntity != null)
{
ICrmService crmService = (ICrmService)context.CreateCrmService(true);

#region 创建维修申请单的时，获取其部分信息
Guid newKeyId = Guid.Empty;
Guid defective_partsid = Guid.Empty; string defective_partsname = string.Empty;
decimal parts_price = 0M; decimal new_parts_amount = 0M;
string defective_parts_no = string.Empty;
Guid itransactioncurrencyid = Guid.Empty;
Guid vin = Guid.Empty; Guid distributor = Guid.Empty;

if (createEntity.Properties.Contains("new_maintain_appid"))
{
newKeyId = ((Key)createEntity.Properties["new_maintain_appid"]).Value;//获取维修申请单Id
cu.WriteLog("newKeyId:"+newKeyId);
}

if (createEntity.Properties.Contains("new_defective_parts"))
{
defective_partsid = ((Lookup)createEntity.Properties["new_defective_parts"]).Value;//获取部件规格型号id
defective_partsname = ((Lookup)createEntity.Properties["new_defective_parts"]).name;//获取部件规格信号的名称
cu.WriteLog("defective_partsid:" + defective_partsid);
cu.WriteLog("defective_partsname:" + defective_partsname);
}

if (createEntity.Properties.Contains("new_defective_parts_no"))
{
defective_parts_no = createEntity.Properties["new_defective_parts_no"].ToString();//祸首部件名称
}

if (createEntity.Properties.Contains("transactioncurrencyid"))
{
itransactioncurrencyid = ((Lookup)createEntity.Properties["transactioncurrencyid"]).Value;//获取货币的值
cu.WriteLog("itransactioncurrencyid:" + itransactioncurrencyid);
}

if (createEntity.Properties.Contains("new_vin"))
{
vin = ((Lookup)createEntity.Properties["new_vin"]).Value;//与 维修申请单 关联的 整车信息 的唯一标识符。
cu.WriteLog("vin:" + vin);
}

if (createEntity.Properties.Contains("new_distributor"))
{
distributor = ((Lookup)createEntity.Properties["new_distributor"]).Value;//获取经销商的值
cu.WriteLog("distributor:" + distributor);
}

#endregion

#region Create 计划更换配件明细

DynamicEntity creEntity = new DynamicEntity("new_pm_qa_old_detail");

if (newKeyId != null)
{
creEntity.Properties.Add(new LookupProperty("new_maintain_app", new Lookup("new_maintain_app", newKeyId)));
cu.WriteLog("newKeyId:" + newKeyId);
}

if (!string.IsNullOrEmpty(defective_partsid.ToString()))
{
creEntity.Properties.Add(new LookupProperty("new_parts",new Lookup("new_maintain_app",defective_partsid)));//添加配件规格型号
cu.WriteLog("defective_partsid:" + defective_partsid);
}

if (!string.IsNullOrEmpty(defective_parts_no))
{
creEntity.Properties.Add(new StringProperty("new_parts_name", defective_parts_no));//添加配件名称
cu.WriteLog("defective_parts_no:" + defective_parts_no);
}

if (creEntity.Properties.Contains("importsequencenumber"))
{
int importsequencenumbe = ((CrmNumber)creEntity.Properties["importsequencenumber"]).Value;
creEntity.Properties.Add(new CrmNumberProperty("importsequencenumber", new CrmNumber(importsequencenumbe)));//添加配件序列号
cu.WriteLog("importsequencenumbe:" + importsequencenumbe);
}

int change_old_parts = 1;//数量默认为1
creEntity.Properties.Add(new CrmNumberProperty("new_change_old_parts",new CrmNumber(change_old_parts)));//添加数量
cu.WriteLog("change_old_parts:" + change_old_parts);


//添加配件单价
decimal GetNew_parts_pric = cu.ParseDecimal(GetNew_parts_price(defective_partsid, cu));
cu.WriteLog("GetNew_parts_pric:" + GetNew_parts_pric);

decimal GetFirsts = cu.ParseDecimal(GetFirstsl(vin, cu));
cu.WriteLog("GetFirsts:" + GetFirsts);

decimal Geth = cu.ParseDecimal(Gethl(itransactioncurrencyid, cu));
cu.WriteLog("Geth:" + Geth);

parts_price = (GetNew_parts_pric / GetFirsts) * Geth;
cu.WriteLog("parts_price:" + parts_price);

creEntity.Properties.Add(new CrmMoneyProperty("new_parts_price", new CrmMoney(parts_price)));//添加配件价格


//添加结算总价
decimal zj = cu.ParseDecimal(GetJsxs(distributor, cu));
decimal dj = Math.Round(parts_price,2);
cu.WriteLog("dj:" + dj);
new_parts_amount = dj * 1 * zj;
creEntity.Properties.Add(new CrmMoneyProperty("new_parts_amount", new CrmMoney(new_parts_amount)));//添加总价格 
cu.WriteLog("new_parts_amount:" + new_parts_amount + "zj:" + zj);

//货币显示为美元($)
creEntity.Properties.Add(new LookupProperty("transactioncurrencyid", new Lookup("transactioncurrency", new Guid("A4419C79-BB1B-E111-88C2-001CC497CFFC"))));

try
{ 
Guid guid = crmService.Create(creEntity);//创建记录
cu.WriteLog("guid:" + guid);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
var msg = "DoCreatPost:" + (ex.Message + "." + ex.Detail.InnerText);
throw new Exception(msg);
}

#endregion
} 
}
}

#region 获取配件价格
/// <summary>
/// 获取配件价格
/// </summary>
/// <param name="itemsalespriceid">itemsalespriceid</param>
/// <param name="cu">cu</param>
/// <returns></returns>
private string GetNew_parts_price(Guid itemsalespriceid, CrmUtil cu)
{

StringBuilder strBuilder = new StringBuilder();

strBuilder.AppendLine("select New_salesprice from new_itemsalespriceExtensionBase where new_itemsalesprice=@new_itemsalesprice");

SqlCommand cmd = new SqlCommand(strBuilder.ToString());

cmd.Parameters.Add("@new_itemsalesprice", SqlDbType.UniqueIdentifier).Value = itemsalespriceid;

DataTable dt = cu.QueryBySql(cmd);

string strSaleprice = string.Empty;

if (dt != null && dt.Rows.Count > 0)
{
strSaleprice = dt.Rows[0][0].ToString();
}

return strSaleprice;
}
#endregion

#region 获取汇率
/// <summary>
/// 获取汇率
/// </summary>
/// <param name="transactioncurrency">transactioncurrency</param>
/// <param name="cu">cu</param>
/// <returns></returns>
private string Gethl(Guid transactioncurrency, CrmUtil cu)
{
StringBuilder strBuilder = new StringBuilder();

strBuilder.AppendLine("select exchangerate from TransactionCurrencyBase where statecode = 0 and transactioncurrencyid=@transactioncurrencyid");

SqlCommand cmd = new SqlCommand(strBuilder.ToString());

cmd.Parameters.Add("@transactioncurrencyid", SqlDbType.UniqueIdentifier).Value = transactioncurrency;

DataTable dt = cu.QueryBySql(cmd);

string strexchangerate = string.Empty;
if (dt != null && dt.Rows.Count > 0)
{
strexchangerate = dt.Rows[0][0].ToString();
}

return strexchangerate;

}
#endregion

#region 获取产品一级税率
/// <summary>
/// 获取产品一级税率
/// </summary>
/// <param name="vehicleid">vehicleid</param>
/// <param name="cu">cu</param>
/// <returns></returns>
private string GetFirstsl(Guid vehicleid, CrmUtil cu)
{
StringBuilder sbSql = new StringBuilder();

sbSql.AppendLine("select new_parts_exchange from new_product_first a inner join new_vehicle b on a.New_product_firstId = b.new_product_first where b.statecode = 0 and b.new_vehicleid =@vehicleid");

SqlCommand cmd = new SqlCommand(sbSql.ToString());
cmd.Parameters.Add("@vehicleid", SqlDbType.UniqueIdentifier).Value = vehicleid;
DataTable dt = cu.QueryBySql(cmd);

string strparts_exchange = string.Empty;

if (dt != null && dt.Rows.Count > 0)
{
strparts_exchange = dt.Rows[0][0].ToString();
}
return strparts_exchange;
}
#endregion

#region 获取结算系数
/// <summary>
/// 获取结算系数
/// </summary>
/// <param name="accountid">accountid</param>
/// <param name="cu">cu</param>
/// <returns></returns>
private string GetJsxs(Guid accountid, CrmUtil cu)
{
StringBuilder strB = new StringBuilder();

strB.AppendLine("select new_ratio from new_qa_parts_ratio a inner join account b on a.New_country = b.New_country where a.statecode = 0 and b.statecode = 0 and accountid=@accountid");

SqlCommand cmd = new SqlCommand(strB.ToString());
cmd.Parameters.Add("@accountid", SqlDbType.UniqueIdentifier).Value = accountid;
DataTable dt = cu.QueryBySql(cmd);

string str_ratio = string.Empty;

if (dt != null && dt.Rows.Count > 0)
{
str_ratio = dt.Rows[0][0].ToString();
}
return str_ratio;

}
#endregion


private void DoUpdatePre(IPluginExecutionContext context,CommonFunction cf)
{
if (context.InputParameters.Properties.Contains(ParameterName.Target) && context.InputParameters.Properties[ParameterName.Target] is DynamicEntity)
{ 
DynamicEntity entity = (DynamicEntity)context.InputParameters.Properties[ParameterName.Target];
DynamicEntity entityPre = (DynamicEntity)context.PreEntityImages["PreImage"];
Target_new_maintain_app new_maintain_app = new Target_new_maintain_app();
if (entity.Properties.Contains("new_status"))
{
new_maintain_app.new_status = ((Picklist)entity.Properties["new_status"]).Value;
}
//如果状态是提交
if(new_maintain_app.new_status==99)
{
if (entityPre.Properties.Contains("new_status"))
{
new_maintain_app.new_statusPre = ((Picklist)entityPre.Properties["new_status"]).Value;
}
//如果提交前状态是草稿
if (new_maintain_app.new_statusPre == 10)
{ 
if (entity.Properties.Contains("new_vin"))
{
new_maintain_app.new_vin = ((Lookup)entity.Properties["new_vin"]).Value;
}
else
{
new_maintain_app.new_vin = ((Lookup)entityPre.Properties["new_vin"]).Value;
}
if (entity.Properties.Contains("new_use_hours"))
{
new_maintain_app.new_use_hours = ((CrmNumber)entity.Properties["new_use_hours"]).Value;
}
else
{
new_maintain_app.new_use_hours = ((CrmNumber)entityPre.Properties["new_use_hours"]).Value;
}
if (entity.Properties.Contains("new_maintain_appid"))
{
new_maintain_app.new_maintain_appid = ((Key)entity.Properties["new_maintain_appid"]).Value;
}

//获取整车信息
GetVehicleInfoBySQL(new_maintain_app,cf);
cf.WriteLog("", "获取参数", "new_vin=" + new_maintain_app.new_vin.ToString() + ";new_use_hours=" + new_maintain_app.new_use_hours + ";new_maintain_appid=" + new_maintain_app.new_maintain_appid + ";new_vehicle_status=" + new_maintain_app.new_vehicle_status + ";new_pa_hours=" + new_maintain_app.new_pa_hours);
if (new_maintain_app.new_vehicle_status != 3 && new_maintain_app.new_vehicle_status != 4)
{
cf.WriteLog("", "报错", "此整车不是待销售状态或不在质保期内！");
throw new InvalidPluginExecutionException("此整车不是待销售状态或不在质保期内！");
}
if (new_maintain_app.new_use_hours > new_maintain_app.new_pa_hours)
{
cf.WriteLog("", "报错", "此整车已运行时数已超过质保运转时数！");
throw new InvalidPluginExecutionException("此整车已运行时数已超过质保运转时数！");
}
//获取配件总费用
GetSumPartsMoney(new_maintain_app, cf);

UpdatePrimaryEntity(new_maintain_app, entity);

}
}
}
}
//删除
private void DoDeletePre(IPluginExecutionContext context,CommonFunction cf)
{
DynamicEntity entityPre = (DynamicEntity)context.PreEntityImages["PreImage"];
if (entityPre.Properties.Contains("new_status"))
{
if (((Picklist)entityPre.Properties["new_status"]).Value != 10)
{
throw new Exception("已提交信息无法删除！");
}
}
}
//获取整车信息
private void GetVehicleInfoBySQL(Target_new_maintain_app new_maintain_app, CommonFunction cf)
{
try
{
StringBuilder sbVehicleSQL = new StringBuilder();
sbVehicleSQL.AppendLine("SELECT ISNULL(vehicle_EB.new_pa_hours,0) AS new_pa_hours,vehicle_EB.new_vehicle_status");
sbVehicleSQL.AppendLine("FROM dbo.New_vehicleExtensionBase vehicle_EB");
sbVehicleSQL.AppendLine("INNER JOIN New_vehicleBase vehicle_B ON vehicle_EB.New_vehicleId = vehicle_B.New_vehicleId");
sbVehicleSQL.AppendLine("WHERE vehicle_B.DeletionStateCode=0 AND vehicle_B.statecode=0");
sbVehicleSQL.AppendLine("AND vehicle_B.New_vehicleId=@vehicleId");

SqlCommand cmd = new SqlCommand(sbVehicleSQL.ToString());
cmd.Parameters.Add("@vehicleId", SqlDbType.UniqueIdentifier).Value = new_maintain_app.new_vin;

DataTable dt = cf.QueryBySQL(cmd);
if (dt.Rows.Count > 0)
{
new_maintain_app.new_pa_hours = (int)dt.Rows[0]["new_pa_hours"];
new_maintain_app.new_vehicle_status = (int)dt.Rows[0]["new_vehicle_status"];
}
}
catch (Exception ex)
{
throw new Exception("获取整车信息失败，详细信息" + ex.Message);
}
}
//获取配件总费用
private void GetSumPartsMoney(Target_new_maintain_app new_maintain_app, CommonFunction cf)
{
StringBuilder sbSumPartsSQL = new StringBuilder();
sbSumPartsSQL.AppendLine("SELECT SUM(ISNULL(new_parts_amount,0))");
sbSumPartsSQL.AppendLine("FROM new_pm_qa_old_detailBase detail_B");
sbSumPartsSQL.AppendLine("INNER JOIN dbo.New_pm_qa_old_detailExtensionBase detail_EB ON detail_B.New_pm_qa_old_detailId = detail_EB.New_pm_qa_old_detailId");
sbSumPartsSQL.AppendLine("WHERE New_maintain_app=@New_maintain_app");

SqlCommand cmd = new SqlCommand(sbSumPartsSQL.ToString());
cmd.Parameters.Add("@New_maintain_app", SqlDbType.UniqueIdentifier).Value = new_maintain_app.new_maintain_appid;

DataTable dt=cf.QueryBySQL(cmd);
new_maintain_app.new_parts_total = (decimal)dt.Rows[0][0];
}
//更新主实体
private void UpdatePrimaryEntity(Target_new_maintain_app new_maintain_app, DynamicEntity entity)
{
CrmMoneyProperty new_parts_total = new CrmMoneyProperty();
new_parts_total.Name = "new_parts_total";
new_parts_total.Value = new CrmMoney();
new_parts_total.Value.Value = new_maintain_app.new_parts_total;
entity.Properties.Add(new_parts_total);

CrmDateTimeProperty new_submit_date = new CrmDateTimeProperty();
new_submit_date.Name = "new_submit_date";
new_submit_date.Value = new CrmDateTime();
new_submit_date.Value.Value = DateTime.Now.ToString();
entity.Properties.Add(new_submit_date);
}

}
}

Target_new_maintain_app.cs:

using System;
using System.Collections.Generic;
using System.Text;

namespace Www.company.Com.Crm40.Plugins
{
class Target_new_maintain_app
{
public Guid new_maintain_appid;
public int new_status;
public int new_statusPre;
public Guid new_vin;
public int new_use_hours;
public int new_pa_hours;
public int new_vehicle_status;
public decimal new_parts_total;
}
}

CrmUtil.cs:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace Www.company.Com.Crm40.Plugins
{
public class CrmUtil
{
#region 定义变量和常量
const string CONFIG_FILE_PATH = @"C:\CRMExtensionConfig\testCRMExtensionConfig.xml";
public SqlConnection conn;
public string LogFile;
#endregion

public CrmUtil()
{
try
{
XmlDocument doc = new XmlDocument();
doc.Load(CONFIG_FILE_PATH);

XmlNode xmlnode = doc.SelectSingleNode("testCRMExtensionConfig/testPlugins");

//CRMOrganizationName = xmlnode.SelectSingleNode("CRMOrganizationName").InnerText;
//CRMServerHost = xmlnode.SelectSingleNode("CRMServerHost").InnerText;
//CRMServerPort = xmlnode.SelectSingleNode("CRMServerPort").InnerText;
//CRMDomainName = xmlnode.SelectSingleNode("CRMDomainName").InnerText;
//CRMAdminUserName = xmlnode.SelectSingleNode("CRMAdminUserName").InnerText;
//CRMAdminUserPassword = xmlnode.SelectSingleNode("CRMAdminUserPassword").InnerText;
LogFile = xmlnode.SelectSingleNode("CRMLogPath").InnerText;
string CRMDBConstr = xmlnode.SelectSingleNode("CRMSqlPath").InnerText;
conn = new SqlConnection(CRMDBConstr);
}
catch (Exception ex)
{
WriteLog("初始化参数失败：" + ex.Message);
}
}

#region 数据库相关操作
//获取数据库连接
public void GetConn()
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

//关闭的数据库连接
public void CloseConn()
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}

//数据库查询
public DataTable QueryBySql(SqlCommand cmd)
{
try
{
GetConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
cmd.Connection = conn;
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];

return dt;
}
catch
{
return null;
}
finally
{
CloseConn();
}
}

public string GetAllValueById(string strFieldName, Guid guidKeyId, string strEntityName, bool blnInnerJoinExtensionTable)
{
string strKeyIdName = strEntityName + "Id"; //主键字段名
string strBaseTableName = strEntityName + "Base"; //基础表名
string strExtensionBaseTableName = strEntityName + "ExtensionBase"; //扩展表名

StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT " + strFieldName + " FROM " + strBaseTableName);
if (blnInnerJoinExtensionTable)
{
sb.AppendLine("INNER JOIN " + strExtensionBaseTableName + " ON " + strBaseTableName + "." + strKeyIdName + "=" + strExtensionBaseTableName + "." + strKeyIdName + "");
}
sb.AppendLine("WHERE " + strBaseTableName + "." + strKeyIdName + "=@KeyId");

SqlCommand cmd = new SqlCommand(sb.ToString());
cmd.Parameters.Add("@KeyId", SqlDbType.UniqueIdentifier).Value = guidKeyId;

DataTable dt = QueryBySql(cmd);

string strValue = string.Empty;
if (dt != null && dt.Rows.Count > 0)
{
strValue = dt.Rows[0][0].ToString();
}

return strValue;
}

/// <summary>
/// 根据主键值查询表中状态
/// </summary>
/// <param name="intType">状态类型 1:记录状态(statecode) 2:记录删除标识(deletionstatecode)</param>
/// <param name="guidKeyId">主键的值</param>
/// <param name="strEntityName">实体名</param>
/// <param name="blnSpecialEntity">是否系统特殊实体，针对部分系统实体的状态字段为Disabled</param>
/// <returns>返回状态的值</returns>
public string GetValueById(int intType, Guid guidKeyId, string strEntityName, bool blnSpecialEntity)
{
string strFieldName = string.Empty;
string strKeyIdName = strEntityName + "Id"; //主键字段名
string strBaseTableName = strEntityName + "Base"; //基础表名

if (intType == 1)
{
if (blnSpecialEntity)
{
strFieldName = "isdisabled";
}
else
{
strFieldName = "statecode";
}
}
else if (intType == 2)
{
strFieldName = "deletionstatecode";
}

StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT " + strFieldName + " FROM " + strBaseTableName);
sb.AppendLine("WHERE " + strBaseTableName + "." + strKeyIdName + "=@KeyId");

SqlCommand cmd = new SqlCommand(sb.ToString());
cmd.Parameters.Add("@KeyId", SqlDbType.UniqueIdentifier).Value = guidKeyId;

DataTable dt = QueryBySql(cmd);

string strValue = string.Empty;
if (dt != null && dt.Rows.Count > 0)
{
strValue = dt.Rows[0][0].ToString();
}

return strValue;
}

/// <summary>
/// 根据主键值查询表中其它字段的值
/// </summary>
/// <param name="strFieldName">需要查询字段的名称</param>
/// <param name="guidKeyId">主键的值</param>
/// <param name="strEntityName">实体名</param>
/// <param name="blnInnerJoinExtensionTable">是否需要与扩展表进行联合查询(INNER JOIN),默认只查询基础表</param>
/// <returns>返回查询出的字段的值</returns>
public string GetValueById(string strFieldName, Guid guidKeyId, string strEntityName, bool blnInnerJoinExtensionTable)
{
return GetValueById(strFieldName, guidKeyId, strEntityName, blnInnerJoinExtensionTable, false);
}
/// <summary>
/// 根据主键值查询表中其它字段的值
/// </summary>
/// <param name="strFieldName">需要查询字段的名称</param>
/// <param name="guidKeyId">主键的值</param>
/// <param name="strEntityName">实体名</param>
/// <param name="blnInnerJoinExtensionTable">是否需要与扩展表进行联合查询(INNER JOIN),默认只查询基础表</param>
/// <param name="blnSpecialEntity">是否系统特殊实体，针对部分系统实体的状态字段为Disabled</param>
/// <returns>返回查询出的字段的值</returns>
public string GetValueById(string strFieldName, Guid guidKeyId, string strEntityName, bool blnInnerJoinExtensionTable, bool blnSpecialEntity)
{
string strKeyIdName = strEntityName + "Id"; //主键字段名
string strBaseTableName = strEntityName + "Base"; //基础表名
string strExtensionBaseTableName = strEntityName + "ExtensionBase"; //扩展表名

StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT " + strFieldName + " FROM " + strBaseTableName);
if (blnInnerJoinExtensionTable)
{
sb.AppendLine("INNER JOIN " + strExtensionBaseTableName + " ON " + strBaseTableName + "." + strKeyIdName + "=" + strExtensionBaseTableName + "." + strKeyIdName + "");
}
if (blnSpecialEntity)
{
sb.AppendLine("WHERE " + strBaseTableName + ".IsDisabled=0 AND " + strBaseTableName + ".DeletionStateCode=0");
}
else
{
sb.AppendLine("WHERE " + strBaseTableName + ".StateCode=0 AND " + strBaseTableName + ".DeletionStateCode=0");
}
sb.AppendLine("AND " + strBaseTableName + "." + strKeyIdName + "=@KeyId");

SqlCommand cmd = new SqlCommand(sb.ToString());
cmd.Parameters.Add("@KeyId", SqlDbType.UniqueIdentifier).Value = guidKeyId;

DataTable dt = QueryBySql(cmd);

string strValue = string.Empty;
if (dt != null && dt.Rows.Count > 0)
{
strValue = dt.Rows[0][0].ToString();
}

return strValue;
}
#endregion

#region 类型转换
//转换为整型
public int ParseInt(string strValue)
{
int intValue = 0;
try
{
intValue = int.Parse(strValue);
}
catch
{ }
return intValue;
}

//转为DateTime
public DateTime ParseDateTime(string strValue)
{
DateTime dateTimeValue = DateTime.Now;
try
{
dateTimeValue = DateTime.Parse(strValue);
}
catch
{ }
return dateTimeValue;
}

//把字符串转为decimal型
public decimal ParseDecimal(string strValue)
{
decimal dcmValue = 0;
try
{
dcmValue = decimal.Parse(strValue);
}
catch
{ }
return dcmValue;
}

//四舍五入,保留2位小数
public decimal RoundDecimal(string strValue)
{
return RoundDecimal(strValue, 2);
}
public decimal RoundDecimal(decimal dcmOldValue)
{
return RoundDecimal(dcmOldValue, 2);
}

//四舍五入,保留指定位小数
public decimal RoundDecimal(string strValue, int intPrecision)
{
decimal dcmValue = 0;
try
{
dcmValue = Math.Round(ParseDecimal(strValue), intPrecision, MidpointRounding.AwayFromZero);
}
catch
{ }
return dcmValue;
}
public decimal RoundDecimal(decimal dcmOldValue, int intPrecision)
{
decimal dcmValue = 0;
try
{
dcmValue = Math.Round(dcmOldValue, intPrecision, MidpointRounding.AwayFromZero);
}
catch
{ }
return dcmValue;
}
#endregion

//将日志文件写入指定的文件
public void WriteLog(string content)
{
System.IO.StreamWriter sr;
try
{
string logFilePath = LogFile + "_" + DateTime.Today.ToString("yyyy-MM-dd") + ".txt";

if (System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(logFilePath)) == false)
{
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(logFilePath));
}
if (System.IO.File.Exists(logFilePath))
{
sr = System.IO.File.AppendText(logFilePath);
}
else
{
sr = System.IO.File.CreateText(logFilePath);

}
sr.WriteLine(DateTime.Now.ToString("yyyy/MM/dd H:mm:ss ") + content);
sr.Close();
}
catch (Exception ex)
{
throw ex;
}
}
}
}

CommonFunction.cs:

using System;
using System.Collections.Generic;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;

using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.Sdk.Metadata;

namespace Www.company.Com.Crm40.Plugins
{
public class CommonFunction
{
/// <summary>
/// 参数定义
/// </summary>
private string functionCommonName = "company_test_Plugin_FailurePartsExecute";
const string CONFIG_FILE_PATH = @"C:\CRMExtensionConfig\testCRMExtensionConfig.xml";
private SqlConnection conn;
private string CRMSQLConnStr = string.Empty;

public string CRMServerHost = string.Empty;
public string CRMServerPort = string.Empty;
public string CRMOrganizationName = string.Empty;
public string CRMDomainName = string.Empty;
public string CRMUserName = string.Empty;
public string CRMUserPassword = string.Empty;

public CrmService CrmService = new CrmService();

/// <summary>
/// 从XML文件中初始化系统配置信息
/// </summary>
public void InitializeConfigFromXML()
{
XmlDocument doc = new XmlDocument();
doc.Load(CONFIG_FILE_PATH);

XmlNode xmlNode = doc.SelectSingleNode("testCRMExtensionConfig/testPlugins");

CRMServerHost = xmlNode.SelectSingleNode("CRMServerHost").InnerText;
CRMServerPort = xmlNode.SelectSingleNode("CRMServerPort").InnerText;
CRMOrganizationName = xmlNode.SelectSingleNode("CRMOrganizationName").InnerText;
CRMDomainName = xmlNode.SelectSingleNode("CRMDomainName").InnerText;
CRMUserName = xmlNode.SelectSingleNode("CRMAdminUserName").InnerText;
CRMUserPassword = xmlNode.SelectSingleNode("CRMAdminUserPassword").InnerText;
CRMSQLConnStr = xmlNode.SelectSingleNode("CRMSqlPath").InnerText;
}

/// <summary>
/// 初始化WebService
/// </summary>
public void InitializeCrmService()
{
try
{
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = CRMOrganizationName;
CrmService.Url = string.Format("http://{0}:{1}/MSCRMServices/2007/CrmService.asmx", CRMServerHost, CRMServerPort);
CrmService.Credentials = new System.Net.NetworkCredential(CRMUserName, CRMUserPassword, CRMDomainName);
CrmService.CrmAuthenticationTokenValue = token;
}
catch (Exception ex)
{
WriteLog("", "初始化WebService", ex.Message);
}
}

/// <summary>
/// 打开数据库链接
/// </summary>
public void OpenConn()
{
try
{
conn = new SqlConnection(CRMSQLConnStr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (Exception ex)
{
throw new Exception("开启数据库链接失败:" + ex.Message);
}
}

/// <summary>
/// 关闭数据库链接
/// </summary>
public void CloseConn()
{
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception ex)
{
throw new Exception("关闭数据库链接失败:" + ex.Message);
}
}

/// <summary>
/// SQL脚本查询
/// </summary>
/// <param name="cmd">执行命令</param>
/// <returns>返回表</returns>
public DataTable QueryBySQL(SqlCommand cmd)
{
try
{
OpenConn();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
cmd.Connection = conn;
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
return dt;
}
catch (Exception ex)
{
return null;
throw new Exception("SQL查询数据库失败：" + ex.Message);
}
finally
{
CloseConn();
}
}

/// <summary>
/// SQL脚本执行
/// </summary>
/// <param name="cmd">执行命令</param>
public void ExecuteBySQL(SqlCommand cmd)
{
string rValue = string.Empty;
int ReturnCode = 0;
string ReturnMessage = string.Empty;
try
{
OpenConn();

int intResult = 1;

cmd.Connection = conn;
cmd.CommandTimeout = 600;
intResult = cmd.ExecuteNonQuery();

if (intResult >= 1) ReturnCode = intResult;

if (ReturnCode >= 0) ReturnMessage = "执行成功";
}
catch (Exception ex)
{
ReturnCode = -10;
ReturnMessage = "ExecuteSql Exception:" + ex.Message;
throw new Exception("执行SQL语句失败:" + ex.Message);
}
finally
{
CloseConn();
}

}

/// <summary>
/// SQL存储过程执行
/// </summary>
/// <param name="cmd">命令</param>
/// <returns>生效记录数</returns>
public int ExecuteProcBySQL(SqlCommand cmd)
{
try
{
OpenConn();
cmd.Connection = conn;
//cmd.CommandTimeout = 600;
cmd.CommandType = CommandType.StoredProcedure;
int rValue = cmd.ExecuteNonQuery();
return rValue;
}
catch (Exception ex)
{
return 0;
throw new Exception("执行SQL失败:" + ex.Message);
}
finally
{
CloseConn();
}
}

/// <summary>
/// 日志记录
/// </summary>
/// <param name="functionName">方法名</param>
/// <param name="logStep">步骤</param>
/// <param name="logMessage">日志信息</param>
public void WriteLog(string functionName, string logStep, string logMessage)
{
try
{
if (string.IsNullOrEmpty(functionName))
{
functionName = functionCommonName;
}
//StringBuilder sbSql = new StringBuilder();
//sbSql.AppendLine("INSERT INTO company_CrmLog(FunctionName,LogStep,LogMessage) ");
//sbSql.AppendLine("VALUES (@FunctionName,@LogStep,@LogMessage)");

//SqlCommand cmd = new SqlCommand(sbSql.ToString());
//cmd.CommandType = CommandType.Text;
//cmd.Parameters.Add("@FunctionName", SqlDbType.NVarChar).Value = functionName;
//cmd.Parameters.Add("@LogStep", SqlDbType.NVarChar).Value = logStep;
//cmd.Parameters.Add("@LogMessage", SqlDbType.NVarChar).Value = logMessage;
//ExecuteBySQL(cmd);
string strSQL = "UP_company_WriteSystemLog";
SqlCommand cmd = new SqlCommand(strSQL);
cmd.Parameters.Add("@FunctionName", SqlDbType.NVarChar,100).Value = functionName;
cmd.Parameters.Add("@LogStep", SqlDbType.NVarChar,100).Value = logStep;
cmd.Parameters.Add("@LogMessage", SqlDbType.NVarChar,4000).Value = logMessage;
ExecuteProcBySQL(cmd);
}
catch (Exception ex)
{
throw new Exception("日志记录出错,详细信息:"+ex.Message);
}
}
}
}

2.该示例演示如何使用查询表达式检索其负责人的姓氏不是 Cannon 的所有客户

// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; 
token.OrganizationName = "AdventureWorksCycle";
 
CrmService service = new CrmService();
service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Create a column set holding the names of the columns to be retrieved.
ColumnSet cols = new ColumnSet();
cols.Attributes = new string [] {"name", "accountid"};

// Create the ConditionExpression.
ConditionExpression condition = new ConditionExpression();

// Set the condition to be when the account owner's last name is not Cannon.
condition.AttributeName = "lastname";
condition.Operator = ConditionOperator.Equal;
condition.Values = new string [] {"Cannon"};

// Build the filter that is based on the condition.
FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] {condition};

// Create a LinkEntity to link the owner's information to the account.
LinkEntity link = new LinkEntity();

// Set the LinkEntity properties.
link.LinkCriteria = filter;

// Set the linking entity to account.
link.LinkFromEntityName = EntityName.account.ToString();

// Set the linking attribute to owninguser.
link.LinkFromAttributeName = "owninguser";

// The attribute being linked to is systemuserid.
link.LinkToAttributeName = "systemuserid";

// The entity being linked to is systemuser.
link.LinkToEntityName = EntityName.systemuser.ToString();

// Create an instance of the query expression class.
QueryExpression query = new QueryExpression();

// Set the query properties.
query.EntityName = EntityName.account.ToString();
query.ColumnSet = cols;
query.LinkEntities = new LinkEntity[] {link};

// Create the request.
RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

// Set the request properties.
retrieve.Query = query;

// Execute the request.
RetrieveMultipleResponse retrieved2 = (RetrieveMultipleResponse) service.Execute(retrieve);

3.以下代码示例演示如何使用 FilterExpression，其中 city 为 Redmond，且 firstname 为 Joe 或 John。

// Create the query expression and set the entity to contact.
QueryExpression query = new QueryExpression();
query.EntityName = "contact";

// Create a condition where the first name equals Joe.
ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "firstname";
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new string[] { "Joe" }; 

// Create another condition where the first name equals John.
ConditionExpression condition2 = new ConditionExpression();
condition2 .AttributeName = "firstname";
condition2 .Operator = ConditionOperator.Equal;
condition2 .Values = new string[] { "John" }; 

// Create another condition where the city equals Redmond.
ConditionExpression condition3 = new ConditionExpression();
condition3 .AttributeName = "city";
condition3 .Operator = ConditionOperator.Equal;
condition3 .Values = new string[] { "Redmond" }; 

// Create a child filter to test for John OR Joe.
FilterExpression childFilter = new FilterExpression();
childFilter.FilterOperator = LogicalOperator.Or;
childFilter.Conditions = new ConditionExpression[] { condition1, condition2 };

// Create a parent filter to test for the city AND test for the child filter (first name).
FilterExpression topFilter = new FilterExpression();
topFilter.FilterOperator = LogicalOperator.And;
topFilter.Conditions = new ConditionExpression[] { condition3 };
topFilter.Filters = new FilterExpression[] { childFilter };

// Set the filter critera for the query to the complete filter expression.
query.Criteria = topFilter;

 4.单表查询

private DynamicEntity GetNewPrInfo(Guid NewPrID)//根据GUID查询某字段
{
    ColumnSet colSet = new ColumnSet(NewPrInfo.EntityName);
    colSet.AddColumn(NewPrInfo.AttributeName_Assigner);
    colSet.AddColumn(NewPrInfo.AttributeName_AssignNode);

    TargetRetrieveDynamic target = new TargetRetrieveDynamic();
    target.EntityId = NewPrID;
    target.EntityName = NewPrInfo.EntityName;

    RetrieveRequest request = new RetrieveRequest();
    request.ColumnSet = colSet;
    request.ReturnDynamicEntities = true;
    request.Target = target;

    RetrieveResponse response = (RetrieveResponse)this.crmService.Execute(request);
    DynamicEntity PromotionPe = (DynamicEntity)response.BusinessEntity;
    return PromotionPe;
}

5.返回多个实体

// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; 
token.OrganizationName = "AdventureWorksCycle";
 
CrmService service = new CrmService();
service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Create a column set holding the names of the columns to be retrieved.
ColumnSet cols = new ColumnSet();
cols.Attributes = new string [] {"name", "accountid"};

// Create the query object.
QueryByAttribute query = new QueryByAttribute();
query.ColumnSet = cols;
query.EntityName = EntityName.account.ToString();

// The query will retrieve all accounts whose address1_city is Sammamish.
query.Attributes = new string [] {"address1_city"};
query.Values = new string [] {"Sammamish"};

// Execute the retrieval.
BusinessEntityCollection retrieved = service.RetrieveMultiple(query);

 6.以下代码示例演示如何使用 FetchXML。第一条 FetchXML 语句检索所有客户。第二条语句检索姓氏不为 Cannon 的所有客户。

注意：不论哪种情况，登录用户的权限都会影响返回的记录集。Fetch 方法仅检索登录用户有读取访问权限的记录。

[C#]
// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; 
token.OrganizationName = "AdventureWorksCycle";
 
CrmService service = new CrmService();
service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Retrieve all accounts.
// Be aware that using all-attributes may adversely affect performance
// and cause unwanted cascading in subsequent updates.
// A best practice is to retrieve the least amount of data required.
string fetch1 = "<fetch mapping='logical'>";
fetch1 += "<entity name='account'><all-attributes/>";
fetch1 += "</entity></fetch>";

// Fetch the results.