控制台程序实现利用CRM组织服务和SqlConnection对数据库中数据的增删改查操作
一、首先新建一个控制台程序。命名为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 = 0; 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() : "0"; 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, "1", new_month); } catch (Exception ex) { updateMessage(New_buget_name, new_typecode_name, "2", 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", 0); 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 = 0; switch (month) { case "1": intmonth =100000000; break; case "2": intmonth =100000001; break; case "3": intmonth =100000002; break; case "4": intmonth =100000003; break; case "5": intmonth =100000004; break; case "6": intmonth =100000005; break; case "7": intmonth =100000006; break; case "8": intmonth =100000007; break; case "9": intmonth =100000008; break; case "10": intmonth =100000009; break; case "11": intmonth =100000010; break; case "12": intmonth =100000011; break; } QueryByAttribute query = new QueryByAttribute("new_categorytate"); query.ColumnSet = new ColumnSet(); query.AddAttributeValue("statecode", 0); 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 = 0; try { conn.Open(); cmd.Connection = conn; i = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return i; } } }
【原创】控制台程序实现利用CRM组织服务和SqlConnection对数据库中数据的增删改查操作,古老的榕树,5-wow.com
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。