Saturday, February 14, 2015

Retrieve SharePoint list/libraries data to sql server table using console application

Retrieve SharePoint list/libraries data to sql server table using console application


        Create a Library in Sharepoint say (lib_abc) & table in sql server



//Retrieving SharePoint Data to sql server database -
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Microsoft.SharePoint;
using System.Data;
using System.Data.SqlClient;


namespace CopySharepointToSqlUtility
{
class Program
{
static void Main(string[] args)
{
try
{
string siteurl = ConfigurationManager.AppSettings["SharePointSiteUrl"];
//string siteurl = http://127.0.0.1/abc"
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(siteurl))
{
using (SPWeb web = site.OpenWeb())
{
SPListCollection collSiteLists = web.Lists;

foreach (SPList oList in collSiteLists)
{

if (oList.BaseType.ToString().Equals("DOCUMENT LIBRARY"))
{
if (oList.EntityTypeName.Equals("lib_abc"))
{
SPListItemCollection collItem = oList.GetItems();
foreach (SPListItem item in collItem)
{
Console.WriteLine(string.Format("Fetching items for {0}", oList.EntityTypeName.ToString()));
SPListItem oItem = oList.Items.GetItemById(item.ID); //getiing all fields
Console.WriteLine(string.Format("Going for adding updated!! , {0}", oList.EntityTypeName.ToString()));
new AddUpdateClass().AddingUpdating(ref oItem, oList.EntityTypeName.ToString(), siteurl);

}

}

}
}
Console.WriteLine("end of execution , Please test things in sql db ");

}
}
});
}
catch (Exception ex) { Console.WriteLine(string.Format("something happen wrong ..! , {0}", ex.Message)); }
finally { Console.WriteLine("Please Press any key to exit.!"); Console.Read(); }
}

class AddUpdateClass
{
#region Method

public void AddingUpdating(ref SPListItem oItemParam, string entityTypeName, string siteurl)
{
try
{
#region Varibles

string name = string.Empty;

#endregion

#region Initilizing variables

name = Convert.ToString(oItemParam["TITLE"]);
string CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
#endregion

using (SqlConnection con = new SqlConnection(CONNECTIONSTRING))
{
using (SqlCommand cmd = new SqlCommand("AddTodb"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();
cmd.Parameters.AddWithValue("@Name", name);


sda.SelectCommand = cmd;
sda.Fill(dt);
if (dt.Rows.Count > 0)
{

}
}
}
}
Console.WriteLine(string.Format("Record updated/added for ,{0}", entityTypeName));



}

}
catch (Exception ex) { Console.WriteLine(string.Format("something happen wrong in adding/updating ..! , {0}", ex.Message)); }
}

#endregion
}

}




~Sunit
www.sunitkanyan.in
technocrats@sunitkanyan.in