控制台程序实现利用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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。