一、首先新建一个控制台程序。命名为TestCol。
二、打开App.config在里面加入，数据库和CRM连接字符串
<connectionStrings>
<add name="SqlServerConnString" connectionString="server=IP地址;database=数据库名称;uid=sa;pwd=密码"/>
<add name="CrmConnnectionString" connectionString="Url=http://IP地址/组织名;Username=用户名;Password=密码;Domain=域;"/>
</connectionStrings>
三、打开Program.cs写代码。主要代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Xrm.Client;//
using System.Data;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Xml;
using System.IO;
using System.Configuration;
using System.Threading.Tasks;namespace TestCol
{
public class Program
{
static string CRMConnectionPath = string.Empty; // CRM连接字符串
static SqlConnection sqlConnection = new SqlConnection(); // 中间库连接字符串
static void Main(string[] args)
{
InitializeConfig();//初始化链接
CrmConnection connection = CrmConnection.Parse(CRMConnectionPath);
using (CrmOrganizationServiceContext orgservice = new CrmOrganizationServiceContext(connection))
{
getNew_categorytate(orgservice);
}
} //逻辑方法
protected static void getNew_categorytate(IOrganizationService service)
{
string sqlstr = "select * from Check_Buget_Sumtable where new_message1=0";//所有未同步的数据
DataTable dt = new DataTable();
SQLExecuteData(sqlstr, sqlConnection,dt);
for (int i = ; i < dt.Rows.Count; i++)
{
string New_buget_name = !string.IsNullOrEmpty(dt.Rows[i]["New_buget_name"].ToString()) ? dt.Rows[i]["New_buget_name"].ToString() : string.Empty;//预算费用项名称
string new_expenseitem_name = !string.IsNullOrEmpty(dt.Rows[i]["new_expenseitem_name"].ToString()) ? dt.Rows[i]["new_expenseitem_name"].ToString() : string.Empty;//费用项目名称
string new_bedgetsheet_name = !string.IsNullOrEmpty(dt.Rows[i]["new_bedgetsheet_name"].ToString()) ? dt.Rows[i]["new_bedgetsheet_name"].ToString() : string.Empty;//预算期间名称
string new_bugetunit_name = !string.IsNullOrEmpty(dt.Rows[i]["new_bugetunit_name"].ToString()) ? dt.Rows[i]["new_bugetunit_name"].ToString() : string.Empty;//提交部门名称
string new_sort_name = !string.IsNullOrEmpty(dt.Rows[i]["new_sort_name"].ToString()) ? dt.Rows[i]["new_sort_name"].ToString() : string.Empty;//所属品类名称
string new_type_name = !string.IsNullOrEmpty(dt.Rows[i]["new_type_name"].ToString()) ? dt.Rows[i]["new_type_name"].ToString() : string.Empty;//品类名称
string new_typecode_name = !string.IsNullOrEmpty(dt.Rows[i]["new_typecode_name"].ToString()) ? dt.Rows[i]["new_typecode_name"].ToString() : string.Empty;//品类编码
string new_month = !string.IsNullOrEmpty(dt.Rows[i]["new_month"].ToString()) ? dt.Rows[i]["new_month"].ToString() : string.Empty;//月份
decimal new_usabled_buget = Convert.ToDecimal(dt.Rows[i]["new_usabled_buget"].ToString());//预计全年可用预算
decimal new_sum_buget = Convert.ToDecimal(dt.Rows[i]["new_sum_buget"].ToString());//累计实现预算
string new_message1 = !string.IsNullOrEmpty(dt.Rows[i]["new_message1"].ToString()) ? dt.Rows[i]["new_message1"].ToString() : ""; EntityCollection encols = getBuget(service, New_buget_name);
foreach (Entity item in encols.Entities)
{
EntityCollection encol = getCategorytate(service, new_typecode_name, new_type_name, item.Id, new_month);
foreach (Entity item1 in encol.Entities)
{
try
{
updateCategorytate(service, item1.Id, new_usabled_buget, new_sum_buget);
updateMessage(New_buget_name, new_typecode_name, "", new_month);
}
catch (Exception ex)
{
updateMessage(New_buget_name, new_typecode_name, "", new_month);
}
}
} }
} //修改预算统计表的message1的值为2
public static void updateMessage(string New_buget_name,string new_typecode_name,string num,string month)
{
string sql = string.Format("update Check_Buget_Sumtable set new_message1={0} where New_buget_name='{1}' and new_typecode_name='{2}' and new_month='{3}'", num, New_buget_name, new_typecode_name, month);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
int resultSet = SQLExecuteQuery(sqlConnection, cmd);
} //查出预算费用项
protected static EntityCollection getBuget(IOrganizationService service, string New_buget_name)
{
QueryByAttribute query = new QueryByAttribute("new_buget");
query.ColumnSet = new ColumnSet("new_expenseitem", "new_bedgetsheet", "new_bugetunit", "new_sort");
query.AddAttributeValue("statecode", );
query.AddAttributeValue("new_name", New_buget_name);//预算费用项名称
EntityCollection encols = service.RetrieveMultiple(query);
return encols;
} //查品类率表
protected static EntityCollection getCategorytate(IOrganizationService service, string new_sn,string new_name,
Guid new_buget,string month)
{
int intmonth = ;
switch (month)
{
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
case "":
intmonth =;
break;
}
QueryByAttribute query = new QueryByAttribute("new_categorytate");
query.ColumnSet = new ColumnSet();
query.AddAttributeValue("statecode", );
query.AddAttributeValue("new_sn", new_sn);//产品品类编号
query.AddAttributeValue("new_name", new_name);//产品品类名称
query.AddAttributeValue("new_buget", new_buget);
query.AddAttributeValue("new_bugetmonth", intmonth); EntityCollection encols = service.RetrieveMultiple(query);
return encols;
} //更新品类率表的new_expectedannualbudget【预计全年可用预算】和new_cumulativeactualbudget【累计实现预算】
protected static void updateCategorytate(IOrganizationService service,Guid new_categorytateid,
decimal new_usabled_buget, decimal new_sum_buget)
{
Entity updateEntity = new Entity("new_categorytate");
if (new_categorytateid != Guid.Empty)
{
updateEntity[updateEntity.LogicalName+"id"] = new_categorytateid;
updateEntity["new_expectedannualbudget"] = new Money(new_usabled_buget);
updateEntity["new_cumulativeactualbudget"] = new Money(new_sum_buget);
service.Update(updateEntity);
}
} //初始化连接信息
protected static void InitializeConfig()
{
sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerConnString"].ToString());
CRMConnectionPath = ConfigurationManager.ConnectionStrings["CrmConnnectionString"].ToString();
} //查询数据方法
protected static void SQLExecuteData(string CommandText, SqlConnection conn, DataTable dataTable)
{
DateTime a = DateTime.Now;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(CommandText, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
}
catch { }
finally
{
conn.Close();
}
} // 插入、更新、删除数据
protected static int SQLExecuteQuery(SqlConnection conn, SqlCommand cmd)
{
DateTime a = DateTime.Now;
int i = ;
try
{
conn.Open();
cmd.Connection = conn;
i = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return i;
} }
}
