SAP NCO 连接.NET平台的MSSQL（流星程序集之十一）



MFC没有消亡，它化为了一缕烟，青云直上，在烟中你仿佛能看到一些形状，比如C#......

SAP在.NET平台提供了NCO3中间库。VS2003 , VS2008, VS2010 都可以使用。 

使用NCO3库，WIN平台应用程序可以很轻松的连接到SAP系统，调用ABAP function的接口功能，实现WIN平台同各种平台SAP的数据交互。

C#的winform程序，一边连接SAP ,一边用ADO.net实现数据库的交互非常简单。


 1. SAP官网service.sap.com/connectors下载NCO3库，主要为3个DLL , rscp4n,sapnco,sapnco_utils，官网还提供了NCo30APIDocumentation.chm 帮助文件，NCO3库里的所有类和方法说明都可以查到。
 
 2. VS2010 建立winform程序, 加入3个dll库，拖个button就可以了，下面代码实现从SAP系统function传3个参数，再读数据，然后放到MSSQL中：

        //写到中间MSSQL数据库表
                private void button2_Click(object sender, EventArgs e)
                {


                    Control.CheckForIllegalCrossThreadCalls = false;
                    Thread thread = new Thread(new ThreadStart(workthread));
                    thread.IsBackground = true;
                    thread.Start();

                }


        //服务线程执行
        private void workthread()
        {
            progressBar1.Value = 10;
            string ConnectionString = "Data Source=10.23.1.34;Initial Catalog=wccq;Persist Security Info=True;User ID=sa;Password=XXXXXX";   // 连接字符串，包含了连接参数
            SqlConnection conn = null; //  数据库连接对象。实现和数据库的连接
            SqlCommand cmd = null;
            // 创建一个连接
            conn = new SqlConnection(ConnectionString);

            // 打开数据库连接
            if (conn.State == ConnectionState.Open) conn.Close();

            conn.Open();          
            //清空中间表数据           
            cmd = new SqlCommand("delete from MID_304_INTERFACE", conn);
            cmd.ExecuteNonQuery();

            progressBar1.Value = 20;
            
            //提供必要的登录参数和获得RfcDestination对象对应到SAP系统中，你要调用的fm。   
            RfcDestination SapRfcDestination = RfcDestinationManager.GetDestination("PRD");

            //使用RfcDestination对象的repository属性创建一个IRfcFunction对象为fm提供调用   
            RfcRepository SapRfcRepository = SapRfcDestination.Repository;

            IRfcFunction BapiGetTable = SapRfcRepository.CreateFunction("ZFI_FM001");

            BapiGetTable.SetValue("BUKRS", comboBox1.Text);//传入参数
            BapiGetTable.SetValue("GJAHR", comboBox2.Text);//传入参数
            BapiGetTable.SetValue("MONAT", comboBox3.Text);//传入参数
            
            
            BapiGetTable.SetParameterActive(0, true);

            BapiGetTable.Invoke(SapRfcDestination);
            IRfcTable CompanyTable = BapiGetTable.GetTable("O_TAB");

            progressBar1.Value = 40;

          //  IRfcStructure RfcReturn = BapiGetTable.GetStructure("RETURN");


            //第二步
            //定义DataTable
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("LINE_ID", typeof(long));
            dt.Columns.Add("HFM_YEARS", typeof(string));
            dt.Columns.Add("HFM_PERIOD", typeof(string));
            dt.Columns.Add("HFM_VIEWS", typeof(string));
            dt.Columns.Add("HFM_ENTITY", typeof(string));
            dt.Columns.Add("HFM_ACCOUNTS", typeof(string));
            dt.Columns.Add("HFM_ICP", typeof(string));
            dt.Columns.Add("HFM_C1", typeof(string));
            dt.Columns.Add("HFM_C2", typeof(string));
            dt.Columns.Add("HFM_C3", typeof(string));
            dt.Columns.Add("HFM_C4", typeof(string));
            dt.Columns.Add("HFM_AMOUNT", typeof(decimal));
            dt.Columns.Add("ATTRIBUTE1", typeof(string));
            dt.Columns.Add("ATTRIBUTE2", typeof(string));
            dt.Columns.Add("ATTRIBUTE3", typeof(string));
            dt.Columns.Add("ATTRIBUTE4", typeof(string));
            dt.Columns.Add("ATTRIBUTE5", typeof(string));
            dt.Columns.Add("SEGMENT1", typeof(decimal));
            dt.Columns.Add("SEGMENT2", typeof(decimal));
            dt.Columns.Add("SEGMENT3", typeof(decimal));
            dt.Columns.Add("SEGMENT4", typeof(decimal));
            dt.Columns.Add("SEGMENT5", typeof(decimal));
            dt.Columns.Add("ODS_INSERT_DT", typeof(DateTime));
            dt.Columns.Add("ODS_UPDATE_DT", typeof(DateTime));
            dt.Columns.Add("DATASOURCE_NUM_ID", typeof(long));



                     
            // 遍历返回公司列表,填充控件   
            for (int i = 0; i < CompanyTable.RowCount; i++)
                {
                CompanyTable.CurrentIndex = i;


                DataRow row = dt.NewRow();

                row["LINE_ID"] = CompanyTable.GetLong("LINE_ID");
                row["HFM_YEARS"] = CompanyTable.GetString("HFM_YEARS");
                row["HFM_PERIOD"] = CompanyTable.GetString("HFM_PERIOD");
                row["HFM_VIEWS"] = CompanyTable.GetString("HFM_VIEWS");
                row["HFM_ENTITY"] = CompanyTable.GetString("HFM_ENTITY");
                row["HFM_ACCOUNTS"] = CompanyTable.GetString("HFM_ACCOUNTS");
                row["HFM_ICP"] = CompanyTable.GetString("HFM_ICP");
                row["HFM_C1"] = CompanyTable.GetString("HFM_C1");
                row["HFM_C2"] = CompanyTable.GetString("HFM_C2");
                row["HFM_C3"] = CompanyTable.GetString("HFM_C3");
                row["HFM_C4"] = CompanyTable.GetString("HFM_C4");
                row["HFM_AMOUNT"] = CompanyTable.GetDecimal("HFM_AMOUNT");
                row["ATTRIBUTE1"] = CompanyTable.GetString("ATTRIBUTE1");
                row["ATTRIBUTE2"] = CompanyTable.GetString("ATTRIBUTE2");
                row["ATTRIBUTE3"] = CompanyTable.GetString("ATTRIBUTE3");
                row["ATTRIBUTE4"] = CompanyTable.GetString("ATTRIBUTE4");
                row["ATTRIBUTE5"] = CompanyTable.GetString("ATTRIBUTE5");
                row["SEGMENT1"] = CompanyTable.GetDecimal("SEGMENT1");
                row["SEGMENT2"] = CompanyTable.GetDecimal("SEGMENT2");
                row["SEGMENT3"] = CompanyTable.GetDecimal("SEGMENT3");
                row["SEGMENT4"] = CompanyTable.GetDecimal("SEGMENT4");
                row["SEGMENT5"] = CompanyTable.GetDecimal("SEGMENT5");
                row["ODS_INSERT_DT"] = DateTime.ParseExact(CompanyTable.GetString("ODS_INSERT_DT"), "yyyy/MM/dd HH:mm:ss", null);
                row["ODS_UPDATE_DT"] = DateTime.ParseExact(CompanyTable.GetString("ODS_UPDATE_DT"), "yyyy/MM/dd HH:mm:ss", null);
                row["DATASOURCE_NUM_ID"] = CompanyTable.GetLong("DATASOURCE_NUM_ID");



               

                dt.Rows.Add(row);

                }



            progressBar1.Value = 60;
  
            //插入数据
            SqlBulkCopy sbc = new SqlBulkCopy(conn);
            sbc.DestinationTableName = "MID_304_INTERFACE";

            //指定源列和目标列之间的对应关系,不然 SqlBulkCopy 会按顺序乱填数据！      
            sbc.ColumnMappings.Add("LINE_ID", "LINE_ID");
            sbc.ColumnMappings.Add("HFM_YEARS", "HFM_YEARS");
            sbc.ColumnMappings.Add("HFM_PERIOD", "HFM_PERIOD");
            sbc.ColumnMappings.Add("HFM_VIEWS", "HFM_VIEWS");
            sbc.ColumnMappings.Add("HFM_ENTITY", "HFM_ENTITY");
            sbc.ColumnMappings.Add("HFM_ACCOUNTS", "HFM_ACCOUNTS");
            sbc.ColumnMappings.Add("HFM_ICP", "HFM_ICP");
            sbc.ColumnMappings.Add("HFM_C1", "HFM_C1");
            sbc.ColumnMappings.Add("HFM_C2", "HFM_C2");
            sbc.ColumnMappings.Add("HFM_C3", "HFM_C3");
            sbc.ColumnMappings.Add("HFM_C4", "HFM_C4");
            sbc.ColumnMappings.Add("HFM_AMOUNT", "HFM_AMOUNT");
            sbc.ColumnMappings.Add("ATTRIBUTE1", "ATTRIBUTE1");
            sbc.ColumnMappings.Add("ATTRIBUTE2", "ATTRIBUTE2");
            sbc.ColumnMappings.Add("ATTRIBUTE3", "ATTRIBUTE3");
            sbc.ColumnMappings.Add("ATTRIBUTE4", "ATTRIBUTE4");
            sbc.ColumnMappings.Add("ATTRIBUTE5", "ATTRIBUTE5");
            sbc.ColumnMappings.Add("SEGMENT1", "SEGMENT1");
            sbc.ColumnMappings.Add("SEGMENT2", "SEGMENT2");
            sbc.ColumnMappings.Add("SEGMENT3", "SEGMENT3");
            sbc.ColumnMappings.Add("SEGMENT4", "SEGMENT4");
            sbc.ColumnMappings.Add("SEGMENT5", "SEGMENT5");
            sbc.ColumnMappings.Add("ODS_INSERT_DT", "ODS_INSERT_DT");
            sbc.ColumnMappings.Add("ODS_UPDATE_DT", "ODS_UPDATE_DT");
            sbc.ColumnMappings.Add("DATASOURCE_NUM_ID", "DATASOURCE_NUM_ID");

            sbc.WriteToServer(dt);
            sbc.Close();  


            conn.Close();
            progressBar1.Value = 100;
            MessageBox.Show("处理完毕！");


 

            


            button3.PerformClick();

           
          

        }