C# Update a document using the DocumentId in Sharepoint when you do not know the site collection

A problem arises when you want to update a SharePoint document's attributes using the document ID, when the document might have moved to a different site collection. Because, whereas the search service spans site collections, the data service does not. Therefore, you have to use the search service to locate the document's full path, then using the full path, you can determine where the data service for that document is.

 

Scenario:

 

If you have a permalink to a document, for example:

http://portal-test.sharepoint.nterprise.net:1700/hub/loaniq/_layouts/DocIdRedir.aspx?ID=6K3XF6XK5YEP-1-4724

You can get the document ID out of it by parsing it. In this case, it is 6K3XF6XK5YEP-1-4724

Its as simple as:

string docId = sharepointPermalink.Substring(sharepointPermalink.IndexOf("ID=") + 3);
Great. So go look for that document, and get the full path back.

Define a web reference (not a service reference) to search.asmx. In this case, it is

http://portal-test.sharepoint.nterprise.net:1700/hub/loaniq/_vti_bin/search.asmx

I'm calling it QuerySharepointLoanIq.It should add the following to your app.config file:

    <applicationSettings>

        <UpdateSharepointUsingDocId.Properties.Settings>
            <setting name="UpdateSharepointUsingDocId_QuerySharepointLoanIq_QueryService"
                serializeAs="String">
                <value>
http://portal-test.sharepoint.nterprise.net:1700/_vti_bin/search.asmx
                </value>
            </setting>
        </UpdateSharepointUsingDocId.Properties.Settings>
    </applicationSettings>



Now define a service reference (not a web reference) to the DataService. In this case it is
http://portal-test.sharepoint.nterprise.net:1700/hub/loaniq/_vti_bin/ListData.svc
I'm calling it DataServiceSharepointLoanIq.

Now code against it, (assuming there is a text control called textBoxPermalinkUrl on the form containing the permalink url)

 

string sharepointPermalink; //the document we start out with

string docId; //we pull the docId from the sharepointPermalink
string pathName; //QueryService uses the docId to locate the document, to give us the pull path
string dataServiceUrl;//We will parse the pathName to determine the location of the data service

QuerySharepointLoanIq.QueryService queryService; //the service that looks for the document

TheHubLoanIQFCBTDevDataContext dataService; //the service that updayes the document
System.Data.DataSet queryResults;//the dataset that receives the query results
 
queryService = new QuerySharepointLoanIq.QueryService();
queryService.Credentials = System.Net.CredentialCache.DefaultCredentials;
           
 
//this is the name of the document we are looking for
sharepointPermalink = textBoxPermalinkUrl.Text;
           
//extract the document id from the permalink
docId = sharepointPermalink.Substring(sharepointPermalink.IndexOf("ID=") + 3);
 
//Give the docId to the query service to get back the full name of the document
queryResults = queryService.QueryEx(GetXMLString(docId));
pathName = (string)queryResults.Tables[0].Rows[0]["path"];
 
//identify what part of the pathname is the site collection
//and from that, build the url of the DataService
dataServiceUrl = pathName.Substring(0, pathName.IndexOf("LoanIQDocumentsNV")) + "_vti_bin/listdata.svc";
 
//instantiate the dataService with the dataServiceUrl
dataService = new TheHubLoanIQFCBTDevDataContext(new Uri(dataServiceUrl));
dataService.Credentials = new System.Net.NetworkCredential(@"sharepointTester", "test my sharepoint", "nterprise");
 
//use the dataservice to grab the document, once again based on the document ID
LoanIQDocumentsNVItem document = dataService.LoanIQDocumentsNV.Where(i => i.DocumentIDValue == docId).FirstOrDefault();

//now change the attributes of the document, and commit the changes back to sharepoint.

document.ApprovalStatus = comboBoxApproval.SelectedItem.ToString();
dataService.UpdateObject(document);
dataService.SaveChanges();

 

// Note that this is done this way to provide clarity to the reader.

// The tabs have to be stripped out otherwise it breaks the query.

private string GetXMLString( string docId)

{
    string xml = string.Empty;

    xml+="<QueryPacket xmlns='urn:Microsoft.Search.Query'>";
    xml+="    <Query>";
    xml+="           <SupportedFormats>";
    xml+="                 <Format revision='1'>";
    xml+="                        urn:Microsoft.Search.Response.Document:Document";
    xml+="                 </Format>";
    xml+="           </SupportedFormats>";
    xml+="           <Context>";
    xml+="                 <QueryText language='en-US' type='STRING'>";
    xml+=  docId;
    xml+="                 </QueryText>";
    xml+="           </Context>";
    xml+="    </Query>";
    xml += "</QueryPacket>";

    xml=xml.Replace("\t",string.Empty);

    return xml;

}






 

How to get the datasource name from an Entity Framework in C#

If the entity framework model is in a different project, you have to add System.Data.Entity as a reference.
Assuming the name of the Entity Framework context is

"LIQBillsAndStatementsEntities"

then this is how you do it: Its hard.
 
textBoxStagingdataSource.Text = new LIQBillsAndStatementsEntities().Connection.DataSource;

Encryption Library - base 64

using System;
using System.Diagnostics.CodeAnalysis;
using System.IO;
using System.Security.Cryptography;
using System.Text;
 
namespace Applications.Common.Utilities
{
    public class Cryptography
    {
        private readonly byte[] _cryptVector = Encoding.ASCII.GetBytes("yolbutpddklczekf");
        private readonly byte[] _cryptKey = Encoding.ASCII.GetBytes("ivcsfemmnqejpxcnkbccebpmzzsuilxv");
 
        [SuppressMessage("Microsoft.Usage", "CA2202:Do not dispose objects multiple times")]
        public string EncryptString(string clearText)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                byte[] clearTextBytes = Encoding.UTF8.GetBytes(clearText);
                SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();
 
                using (CryptoStream cs = new CryptoStream(ms, rijn.CreateEncryptor(_cryptKey, _cryptVector),
                                                       CryptoStreamMode.Write))
                {
                    cs.Write(clearTextBytes, 0, clearTextBytes.Length);
                }
 
                return Convert.ToBase64String(ms.ToArray());
            }
        }
 
        [SuppressMessage("Microsoft.Usage", "CA2202:Do not dispose objects multiple times")]
        public string DecryptString(string encryptedText)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                byte[] encryptedTextBytes = Convert.FromBase64String(encryptedText);
                SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();
 
                using (CryptoStream cs = new CryptoStream(ms, rijn.CreateDecryptor(_cryptKey, _cryptVector),
                                                       CryptoStreamMode.Write))
                {
                    cs.Write(encryptedTextBytes, 0, encryptedTextBytes.Length);
                }
 
                return Encoding.UTF8.GetString(ms.ToArray());
            }
        }
    }
}

Read A DB2 Database table using C# - ODBC

Define the following Class:

using System;

using System.Data;

using System.Data.Odbc;

namespace ConnectToAS400

{

    public class Db2Access

    {

        #region Private members

        private readonly string _server;

        private readonly int _portNumber;

        private readonly string _database;

        private readonly string _userName;

        private readonly string _password;

        private readonly string _connectionString;

        #endregion

       

        #region Constructor

      

        public Db2Access(string server, int portNumber, string database, string userName, string password)

        {

            _server = server;

            _portNumber = portNumber;

            _database = database ;

            _userName = userName ;

            _password = password;

            _connectionString = String.Format("Driver={{IBM DB2 ODBC DRIVER}};Database={0};Hostname={1};Protocol=TCPIP;Port={2};Uid={3};Pwd={4};",

                                    _database,

                                    _server,

                                    _portNumber,

                                    _userName,

                                    _password);

        }

        #endregion

        #region Public methods

        public DataTable ReadDb2Table(string schemaName, string tableName, string filter)

        {

            try

            {

                DataTable db2Data = new DataTable();

                string query = String.Format("Select * from {0}.{1}", schemaName, tableName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = String.Format("{0} where {1}", query.Trim(), filter);

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        db2Data.Load(r);

                    }

                    c.Close();

                }

                return db2Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        #endregion

    }

}

The Code

        private void button2_Click(object sender, EventArgs e)

        {

            Db2Access thing = new Db2Access("MYSERVER", 50000, "MYDATABASE", @"MYDOMAIN\MYUSERNAME", "MYPASSWORD");

            DataTable db2Data = thing.ReadDb2Table("THESCHEMANAME", "THETABLENAME", "");

        }

Read I Series (AS/400)

How to call it:

IseriesAccess thing = new IseriesAccess(IseriesAccess.Environment.DEV, "username", "password",IseriesAccess.Architecture.Bit32);

DataTable as400Data = thing.ReadISeriesTable("the library", "the table", "1=1");

This is the library

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data.Odbc;

using System.Data;

namespace ConnectToAS400

{

    class IseriesAccess

    {

        #region Public constants and/or Enums

        /// <summary>

        /// ISeries flag which allows the restriction of the type of sql that can be executed

        /// </summary>

        private enum ConnType

        {

            AllowAnySQL = 0,

            AllowSelectAndCall=1,

            AllowSelectOnly=2

        }

        public enum Environment

        {

            DEV=1,

            FCB=2

        }

        public enum Architecture

        {

            Bit32=32,

            Bit64=64

        }

        #endregion

        #region Private members

        private string _pwd;

        private string _uid;

        private Environment _environment;

        private string _connectionString;

        private Architecture _architecture;

        #endregion

        #region Constructor

        /// <summary>

        /// Initializes ISeries object .

        /// </summary>

        /// <param name="environment">FCB or Dev</param>

        /// <param name="uid">The user under whose context the query will execute</param>

        /// <param name="password">The user's password</param>

        public IseriesAccess(Environment environment, string uid, string password, Architecture architecture )

        {

            _environment = environment;

            _uid = uid;

            _pwd = password;

            _architecture = architecture;

            _connectionString = "Driver={Client Access ODBC Driver (" + (int)_architecture + "-bit)};System=" + _environment.ToString() + ";CONNTYPE=" + ((int)ConnType.AllowSelectAndCall).ToString() + ";TRANSlATE=1;Uid=" + _uid + ";pwd=" + _pwd;

        }

        /// <summary>

        /// Initializes ISeries object .

        /// </summary>

        /// <param name="connectionString">The complete connectionstring. Note - if the ConnType attribute is not included inside the string, it will be appended to the end of the driver string with a value of 2. See IBM docs.</param>

        public IseriesAccess(string connectionString)

        {

            _connectionString = connectionString;

            if (connectionString.IndexOf("ConnType", StringComparison.CurrentCultureIgnoreCase) == -1)

            {

                _connectionString += "ConnType=" + ConnType.AllowSelectAndCall.ToString();

            }

        }

        #endregion

        /// <summary>

        /// Reads all columns from specified ISeries library and table using a specified filter

        /// </summary>

        /// <param name="libraryName">The ISeries Library (database)</param>

        /// <param name="fileName">The ISeries File (table)</param>

        /// <param name="filter">The where Clause. Do no include "Where".</param>

        /// <returns>A filled System.Data.DataTable</returns>

        public DataTable ReadISeriesTable(string libraryName, string fileName, string filter)

        {

            try

            {

                DataTable as400Data = new DataTable();

                string query = String.Format("Select * from {0}.{1}", libraryName, fileName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = query.Trim() + " where " + filter;

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        as400Data.Load(r);

                    }

                    c.Close();

                }

                return as400Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        /// <summary>

        /// Reads specific columns from specified ISeries library and table using a specified filter

        /// </summary>

        /// <param name="libraryName">The ISeries Library (database)</param>

        /// <param name="fileName">The ISeries File (table)</param>

        /// <param name="columnList">Comma-delimited list of columns to retrieve</param>

        /// <param name="filter">The where Clause. Do no include "Where".</param>

        /// <returns>A filled System.Data.DataTable</returns>

        public DataTable ReadISeriesTable(string libraryName, string fileName, string columnList, string filter)

        {

            try

            {

                DataTable as400Data = new DataTable();

                string query = String.Format("Select columnList from {0}.{1}", libraryName, fileName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = query.Trim() + " where " + filter;

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        as400Data.Load(r);

                    }

                    c.Close();

                }

                return as400Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}