如何从其他SQL Server实例访问SharePoint 2010列表数据 [英] How to access SharePoint 2010 list data from other SQL Server Instance

问题描述

全部

我的一位客户需要访问SharePoint 2010列表数据并将其存储在SQL Server数据库中.

One of my client need to access SharePoint 2010 list data and store it in SQL server database.

1.有什么不同的方法?

1. What are the different method to do that?

2.使用Java脚本/jSript提取数据的最佳方法?

2. Best method to extract data using java script/jSript?

3.客户端不允许我们编写服务器端代码(无.wsp).

3. Client does not allow us to write server side code( no .wsp).

让我知道.

谢谢

Anurag

推荐答案

嗨阿努拉格，

以下是演示供您参考:

我们可以使用客户端对象模型(CSOM)来实现.

We can use client object model(CSOM) to achieve it.

我的自定义列表设计如下:

My custom list design as below:

SQL Server数据库中表的结构.

Structure of the table in SQL Server Database.

完整代码:

using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplicationCommon
{
    class Program
    {
        static void Main(string[] args)
        {

            //specify your site url
            string siteUrl = "http://wakaka:17710/sites/publish_site"; 
            ClientContext clientContext = new ClientContext(siteUrl);
            List list=clientContext.Web.Lists.GetByTitle("CustomList");
            FieldCollection fields = list.Fields;
            CamlQuery query=new CamlQuery();
            ListItemCollection items = list.GetItems(query);

            //load data
            clientContext.Load(list);
            clientContext.Load(fields);
            clientContext.Load(items);
            clientContext.ExecuteQuery();

            //create a dataTable which will insert into the database
            DataTable dataTable = new DataTable("table");

            //specify the columns
            string[] columnNameArr = { "Title", "Name", "Gender", "home" };

            //init the structure of dataTable
            foreach (string columnName in columnNameArr)
            {
                DataColumn dataColumn = new DataColumn(columnName);
                dataTable.Columns.Add(dataColumn);
            }

            //fill the dataTable
            foreach (ListItem item in items)
            {              
                DataRow dataRow = dataTable.NewRow();
                foreach (string columnName in columnNameArr)
                {

                    Object obj=item[columnName];                   
                    dataRow[columnName] = (item[columnName] == null ? "" : item[columnName].ToString());
                    
                }
                dataTable.Rows.Add(dataRow);
            }

            string connstr = "server=192.168.2.50; database=TestDB;;Integrated Security=True";
            string DestinationTableName = "TestDB.dbo.[Test1]";
            bool success= AddDataTableToDB(dataTable, connstr, DestinationTableName);
            Console.WriteLine(success);
            Console.ReadLine();
                                       
        }

        /// <summary>
        /// insert your dataTable into Sql Server
        /// </summary>
        /// <param name="source"></param>
        /// <param name="connstr"></param>
        /// <param name="DestinationTableName"></param>
        /// <returns></returns>
        public static bool AddDataTableToDB(DataTable source, string connstr, string DestinationTableName)
        {
            SqlTransaction tran = null;  
            try
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open(); 

                    //begin a transaction
                    using (tran = conn.BeginTransaction())
                    {
                        using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                        {
                            copy.DestinationTableName = DestinationTableName;    
                            copy.WriteToServer(source);                       
                            tran.Commit();                                        
                            return true;                                          
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (null != tran)
                    tran.Rollback();               
                return false; 
            }
        } 
       

    }
}

结果:

最诚挚的问候，

刘李

这篇关于如何从其他SQL Server实例访问SharePoint 2010列表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！