Loading...

Tuesday, January 1, 2013

Linqpad To Query Office 365 Odata REST connection(ListData.svc)

1)Download the Linqpad @
2)Install SPClient from here
3)Add required dlls And Import namespaces  as follows
In Linqpad,Ribbon-->Query-->Queryfeatuters

In Query window (Of Linqpad) ,Select C# Program  to execute this source code, when prompted for input enter values as shown in Switch case
(Modify _spUrl variable to your SharePoint online site)

void Main()
{
    while (true) 
    {
        var result = LINQPad.Util.ReadLine("command:");
        switch (result)
        {
            case "auth":
                Office365.Auth(Config.spUrl);
            break;
            case "getlists":
                GetLists();
            break;
            case "getlistitems":
                GetListItems();
            break;
            case "getschema":
                GetSchema();
            break;
            case "createpopulatelist":
                CreatePopulateList();
            break;
            case "odata":
                OData();
            break;
        }
    }
}
class Config 
{
    public static string spUrl { get {
        if(_spUrl == "")
            _spUrl = Util.ReadLine("spUrl:");
        return _spUrl;
    } set {
        _spUrl = value;
    } }
    private static string _spUrl = "https://swaticloud.sharepoint.com";
}
void OData()
{
    var wc = new System.Net.WebClient();
    wc.Headers.Add("Cookie", CookieMonster.DoInternetGetCookieEx(Config.spUrl).GetCookieHeader(new Uri(Config.spUrl)));
    wc.DownloadString(Config.spUrl + "/_vti_bin/listdata.svc").Dump();
}
// // via: [Using the SharePoint Foundation 2010 Managed Client Object Model](http://msdn.microsoft.com/en-us/library/ee857094.aspx)
static ClientContext GetContext()
{
    var context = new ClientContext(Config.spUrl);
    context.Credentials = CredentialCache.DefaultCredentials;
    context.ExecutingWebRequest += new EventHandler<WebRequestEventArgs>(delegate(object sender, WebRequestEventArgs e){
        e.WebRequestExecutor.WebRequest.CookieContainer = CookieMonster.DoInternetGetCookieEx(Config.spUrl);
    });
    return context;
}
void GetLists()
{
    var context = GetContext();
    var results = context.LoadQuery(context.Web.Lists.Include(list => list.Title, list => list.Id));
    context.ExecuteQuery();
    results.ToList().ForEach(x => {
        x.Title.Dump();
    }); 
}
void GetListItems()
{
    var context = GetContext();
    var list = context.Web.Lists.GetByTitle("Contact");
    var camlQuery = new CamlQuery();
    //camlQuery.ViewXml = @"<View><Query><Where><Eq><FieldRef Name='Category'/><Value Type='Text'>Development</Value></Eq></Where></Query><RowLimit>100</RowLimit></View>";
    camlQuery.ViewXml = @"<View />";
    var listItems = list.GetItems(camlQuery);
    context.Load(listItems);
    //context.Load(listItems, items =>  items.Include(
    //  item => item["Name"]
    //));
    context.ExecuteQuery();
    listItems.ToList().ForEach(x => {
        Console.WriteLine(x["Name"]);
    });
}
void GetSchema()
{
    var context = GetContext();
    var list = context.Web.Lists.GetByTitle("Contact");
    context.Load(list);
    var fields = list.Fields;
    context.Load(fields);
    context.ExecuteQuery();
    fields.ToList().ForEach(f => {
        var e = XElement.Parse(f.SchemaXml);
        if((string)e.Attribute("FromBaseType") != "TRUE") 
        {
            e.Attributes("ID").Remove();
            e.Attributes("SourceID").Remove();
            e.Attributes("ColName").Remove();
            e.Attributes("RowOrdinal").Remove();
            e.Attributes("StaticName").Remove();
            Console.WriteLine(e);
        }
    });
}
void CreatePopulateList()
{
    ClientContext clientContext = GetContext();
    Web site = clientContext.Web;

    // Create a list.
    ListCreationInformation listCreationInfo = new ListCreationInformation();
    listCreationInfo.Title = "Client API Test List";
    listCreationInfo.TemplateType = (int)ListTemplateType.GenericList;
    List list = site.Lists.Add(listCreationInfo);

    // Add fields to the list.
    Field field1 = list.Fields.AddFieldAsXml(@"
        <Field Type='Choice' DisplayName='Category' Format='Dropdown'>
            <Default>Specification</Default>
            <CHOICES>
              <CHOICE>Specification</CHOICE>
              <CHOICE>Development</CHOICE>
              <CHOICE>Test</CHOICE>
              <CHOICE>Documentation</CHOICE>
            </CHOICES>
        </Field>
        ", true, AddFieldOptions.DefaultValue);
    Field field2 = list.Fields.AddFieldAsXml(@"
        <Field Type='Number' DisplayName='Estimate'/>
        ", true, AddFieldOptions.DefaultValue);

    // Add some data.
    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
    ListItem listItem = list.AddItem(itemCreateInfo);
    listItem["Title"] = "Write specs for user interface.";
    listItem["Category"] = "Specification";
    listItem["Estimate"] = "20";
    listItem.Update();

    listItem = list.AddItem(itemCreateInfo);
    listItem["Title"] = "Develop proof-of-concept.";
    listItem["Category"] = "Development";
    listItem["Estimate"] = "42";
    listItem.Update();

    listItem = list.AddItem(itemCreateInfo);
    listItem["Title"] = "Write test plan for user interface.";
    listItem["Category"] = "Test";
    listItem["Estimate"] = "16";
    listItem.Update();

    listItem = list.AddItem(itemCreateInfo);
    listItem["Title"] = "Validate SharePoint interaction.";
    listItem["Category"] = "Test";
    listItem["Estimate"] = "18";
    listItem.Update();

    listItem = list.AddItem(itemCreateInfo);
    listItem["Title"] = "Develop user interface.";
    listItem["Category"] = "Development";
    listItem["Estimate"] = "18";
    listItem.Update();

    clientContext.ExecuteQuery();
}
class Office365 
{
    public static void Auth(string url) 
    {
        app app = new app();
        Thread.Sleep(2000);
        app.w.Dispatcher.BeginInvoke(new Action(delegate(){
            var dp = new System.Windows.Controls.DockPanel { LastChildFill = true };
            var wb = new System.Windows.Controls.WebBrowser();
            System.Windows.Controls.DockPanel.SetDock(wb, System.Windows.Controls.Dock.Top);
            dp.Children.Add(wb);
            app.w.Content = dp;
            wb.LoadCompleted += new System.Windows.Navigation.LoadCompletedEventHandler(delegate(object sender, System.Windows.Navigation.NavigationEventArgs e){
                //Console.WriteLine(CookieMonster.DoInternetGetCookieEx(Config.spUrl).GetCookieHeader(new Uri(Config.spUrl)));
            });
            wb.Navigate(url);
        }));
    }
    public class app 
    {
        public System.Windows.Application a;
        public System.Windows.Window w;
        public app() 
        {
            ThreadStart myThreadDelegate = new ThreadStart(this.Start);
            Thread myThread = new Thread(myThreadDelegate);
            myThread.SetApartmentState(ApartmentState.STA);
            myThread.Start();
        }
        public void Start() 
        {
            a = new System.Windows.Application();
            w = new System.Windows.Navigation.NavigationWindow();
            a.MainWindow = w;
            a.MainWindow.Show();
            a.Run();
        }
    }
}


class CookieMonster
{
    [DllImport("wininet.dll", EntryPoint = "InternetGetCookieExW", CharSet = CharSet.Unicode, SetLastError = true, ExactSpelling = true)]
    static extern bool InternetGetCookieEx([In] string Url, [In] string cookieName, [Out] StringBuilder cookieData, [In, Out] ref uint pchCookieData, uint flags, IntPtr reserved); 
    public static CookieContainer DoInternetGetCookieEx(string url)
    {
        var cookies = new CookieContainer();
        var cookieData = new StringBuilder();
        uint dataSize = 0;
        uint INTERNET_COOKIE_HTTPONLY = 0x00002000;
        var hresult1 = InternetGetCookieEx(url, null, cookieData, ref dataSize, INTERNET_COOKIE_HTTPONLY, IntPtr.Zero);
        cookieData = new StringBuilder((int)dataSize);
        var hresult2 = InternetGetCookieEx(url, null, cookieData, ref dataSize, INTERNET_COOKIE_HTTPONLY, IntPtr.Zero);
        //if (!hresult2)
        //  throw new Win32Exception(Marshal.GetLastWin32Error());
        if (cookieData.Length > 0)
            cookies.SetCookies(new Uri(url), cookieData.ToString().Replace(';', ','));
        return cookies;
    }
    [DllImport("ieframe.dll", SetLastError = true)]
    public static extern int IEGetProtectedModeCookie([In] [MarshalAsAttribute(UnmanagedType.LPWStr)] string lpszURL, [In] [MarshalAsAttribute(UnmanagedType.LPWStr)] string lpszCookieName, [MarshalAsAttribute(UnmanagedType.LPWStr)] StringBuilder pszCookieData, ref uint pcchCookieData, uint dwFlags );
    // HRESULT IEGetProtectedModeCookie(LPCWSTR lpszURL, LPCWSTR lpszCookieName, LPWSTR pszCookieData, DWORD *pcchCookieData, DWORD dwFlags );
    public static CookieContainer DoIEGetProtectedModeCookie(string url)
    {
        var cookies = new CookieContainer();
        var cookieData = new StringBuilder();
        uint datasize = 0;
        uint INTERNET_COOKIE_HTTPONLY = 0x00002000;
        var hresult1 = IEGetProtectedModeCookie(url, null, cookieData, ref datasize, INTERNET_COOKIE_HTTPONLY);
        cookieData = new StringBuilder((int)datasize);
        var hresult2 = IEGetProtectedModeCookie(url, null, cookieData, ref datasize, INTERNET_COOKIE_HTTPONLY);
        //if (hresult2 != 0)
        //  throw new Win32Exception(Marshal.GetLastWin32Error());
        cookieData.Dump();
        if (cookieData.Length > 0)
            cookies.SetCookies(new Uri(url), cookieData.ToString().Replace(';', ','));
        return cookies;
    }
}

2 comments:

pholpar said...

Thanks for sharing this code. You can read an alternative solution (utilizing Fiddler) in my blog:
http://pholpar.wordpress.com/2013/03/08/accessing-office-365-rest-services-using-linqpad/

Swati Jain said...

Thank you Pholpar !!