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

    }
}