CODEWITHSUNDEEP
t-sqlsharepointsharepoint-2007caml
OzymandiasII
OzymandiasII

Reputation: 58

SharePoint CAML Query to T-SQL

I was hoping to do convert a CAML query into T-SQL. For my SharePoint site, I also have a data warehouse (SQL Server 2005) which hosts the same data (it is populated through SPList EventReceivers: ItemAdded, ItemUpdated).

I am involved in a task to handle the growth of the lists and the speed of custom webparts that display list data. I am hoping instead to display our list contents using the data warehouse and then we are going to remove items from the SPLists where the workflow has been completed.

Currently, the user of the custom webpart that displays the SPList contents is allowed to select an SPView to display the content in the way they would like (i.e. filtering, sorting and only showing necessary columns). I would like to keep this functionality and was hoping to take the view's CAML query and convert it to T-SQL to query against the data warehouse.

e.g.

<Query>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="ContentType" />
        <Value Type="Text">My Content Type</Value>
      </Eq>
      <IsNotNull>
        <FieldRef Name="Description" />
      </IsNotNull>
    </Or>
  </Where>
  <Order>
    <FieldRef Name="Author" />
    <FieldRef Name="AuthoringDate" />
    <FieldRef Name="AssignedTo" Ascending="True" />
  </Order>
  <Group>
    <FieldRef Name="Title" />
  </Group>
</Query>

to

WHERE ContentType="My Content Type"
OR Description<>null
GROUPBY Title DESC
ORDERBY Author, AuthoringDate, AssignedTo ASC

Does anyone know how to do this? Or have an alternative suggestion that would solve this issue? I have found multiple solutions for converting T-SQL to CAML query, just not the other way around (i.e. http://yacamlqt.codeplex.com/ -- which is also where I retrieved the example from)

Thanks!

Upvotes: 3

Views: 5661

Answers (7)

bobzer
bobzer

Reputation: 99

thanks for your help, i need that too, your code was good but i improve it a little :

  • operator is null and isnotnull wasn't handle
  • a little bug about escaping with like operator

be careful the code doesn't escape the column name (you have to do it depending of your database engine)

here is the code in a static class:

//http://stackoverflow.com/questions/5834700/sharepoint-caml-query-to-t-sql
public static class CAMLtoSQL
{
    public static string ViewQueryToSqlWhere(string query)
    {
        string sqlWhere = string.Empty;
        XmlDocument xmlDoc = new XmlDocument();
        XmlNodeList nodeList;

        //Add <Query> around the SPView.Query since a valid XML document requires a single root element.
        //and SPView.Query doesn't.
        xmlDoc.LoadXml("<Query>" + query + "</Query>");

        nodeList = xmlDoc.GetElementsByTagName("Where");

        if (nodeList.Count == 1)
        {
            XmlNode nodeWhere = nodeList[0];

            if (nodeWhere.HasChildNodes) //Should Always be the case
            {
                StringBuilder sb = new StringBuilder();
                bool isSuccess = ProcessWhereNode(nodeWhere, ref sb);
                sqlWhere = sb.ToString();
            }
        }

        return sqlWhere;
    }
    private static bool ProcessWhereNode(XmlNode xmlNode, ref StringBuilder sb)
    {
        bool isSuccess = false;
        Stack<string> operatorStack = new Stack<string>();
        Queue<string> valueQueue = new Queue<string>();
        string previousOp = string.Empty;
        string strOperator = string.Empty;

        try
        {
            //Call a method to iterate "recursively" throught the nodes to get the values and operators.
            if (ProcessRecursiveWhereNode(xmlNode, "", "", ref operatorStack, ref valueQueue))
            {

                // For each operator adding parenthesis before starting 
                StringBuilder sbTmp = new StringBuilder();
                operatorStack.ToList().ForEach(x => sbTmp.Append("("));
                sb.Append(sbTmp.ToString());

                while (valueQueue.Count > 0)
                {
                    if (operatorStack.Count > 0)
                    {
                        strOperator = operatorStack.Pop();

                    }
                    else
                    {
                        strOperator = string.Empty;
                    }

                    sb.Append(valueQueue.Dequeue());

                    // After each logical operation closing parenthesis 
                    if (previousOp != string.Empty)
                        sb.Append(")");

                    if (strOperator != string.Empty)
                        sb.Append(" " + strOperator + " ");

                    previousOp = strOperator;
                }
            }
            isSuccess = true;
        }
        catch (Exception)
        {
            isSuccess = false;
        }

        return isSuccess;
    }

    private static bool ProcessRecursiveWhereNode(XmlNode xmlNode, string strOperatorValue, string strOperatorType, ref Stack<string> operatorStack, ref Queue<string> valueQueue)
    {
        bool isSuccess = false;
        string fieldName = string.Empty;
        string value = string.Empty;
        string thisIterationOperatorType = string.Empty;
        string thisIterationOperatorValue = string.Empty;

        try
        {
            XmlNodeList nodeList = xmlNode.ChildNodes;

            //Get Child node - Possible tags {<Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>, <IsNotNull>, <IsNull>, <FieldRef>, <Value>}
            foreach (XmlNode node in nodeList)
            {
                thisIterationOperatorType = string.Empty;
                thisIterationOperatorValue = string.Empty;

                //Check if it's one of these tag: <Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>, <IsNotNull>, <IsNull>
                thisIterationOperatorValue = GetOperatorString(node.Name, out thisIterationOperatorType);

                if (thisIterationOperatorType == "statement")
                    operatorStack.Push(thisIterationOperatorValue);

                //It's one of these tag: <Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>, <IsNotNull>, <IsNull>
                if (thisIterationOperatorValue != string.Empty)
                {
                    ProcessRecursiveWhereNode(node, thisIterationOperatorValue, thisIterationOperatorType, ref operatorStack, ref valueQueue);
                }
                else if (strOperatorType != "statement") //It is probably a <FieldRef> or <Value> tag.
                {
                    if (node.Name == "FieldRef")
                        fieldName = node.Attributes["Name"].Value.ToString();
                    else if (node.Name == "Value")
                        value = node.LastChild.Value.ToString();
                }
            }

            if ((strOperatorType == "value" && strOperatorValue != string.Empty && fieldName != string.Empty && value != string.Empty)
                ||
                (strOperatorType == "is" && strOperatorValue != string.Empty && fieldName != string.Empty))
            {
                // if contains a like we don't add the '
                if (strOperatorValue.Contains("LIKE"))
                    valueQueue.Enqueue(string.Format(strOperatorValue, fieldName, value));
                else
                    valueQueue.Enqueue(string.Format(strOperatorValue, fieldName, "'" + value + "'"));
            }

            isSuccess = true;
        }
        catch
        {
            isSuccess = false;
            throw;
        }

        return isSuccess;
    }

    private static string GetOperatorString(string tagName, out string operatorType)
    {
        string operatorString = string.Empty;

        switch (tagName)
        {
            case "Or":
                operatorString = "OR";
                operatorType = "statement";
                break;
            case "And":
                operatorString = "AND";
                operatorType = "statement";
                break;
            case "Eq":
                operatorString = "{0} = {1}";
                operatorType = "value";
                break;
            case "Neq":
                operatorString = "{0} != {1}";
                operatorType = "value";
                break;
            case "Gt":
                operatorString = "{0} > {1}";
                operatorType = "value";
                break;
            case "Lt":
                operatorString = "{0} < {1}";
                operatorType = "value";
                break;
            case "Geq":
                operatorString = "{0} >= {1}";
                operatorType = "value";
                break;
            case "Leq":
                operatorString = "{0} <= {1}";
                operatorType = "value";
                break;
            case "BeginsWith":
                operatorString = "{0} LIKE '{1}%'";
                operatorType = "value";
                break;
            case "Contains":
                operatorString = "{0} LIKE '%{1}%'";
                operatorType = "value";
                break;
            case "IsNotNull":
                operatorString = "{0} IS NOT NULL";
                operatorType = "is";
                break;
            case "IsNull":
                operatorString = "{0} IS NULL";
                operatorType = "is";
                break;

            default:
                operatorString = string.Empty;
                operatorType = string.Empty;
                break;
        }

        return operatorString;
    }
}

Upvotes: 0

Massimo Della Calce
Massimo Della Calce

Reputation: 416

i have checked the code posted by Francis with the following CAML:

<Where>
    <Or>
        <And>
            <Neq><FieldRef Name="F1" /><Value Type="Text">Yes</Value></Neq>
            <Neq><FieldRef Name="F2" /><Value Type="Text">Yes</Value></Neq>
        </And>
        <Eq><FieldRef Name="F3" /><Value Type="Text">Yes</Value></Eq>
    </Or>
</Where>

And it doesn't work... the result, in this case, will be: F1<>'Yes' AND ( F2<>'Yes' OR F3='Yes' ).

I made some fixes inside the following method:

private bool ProcessWhereNode(XmlNode xmlNode, ref StringBuilder sb) {
        bool isSuccess = false;
        Stack<string> operatorStack = new Stack<string>();
        Queue<string> valueQueue = new Queue<string>();
        string previousOp = string.Empty;
        string strOperator = string.Empty;

        try {
            //Call a method to iterate "recursively" throught the nodes to get the values and operators.
            if (ProcessRecursiveWhereNode(xmlNode, "", "", ref operatorStack, ref valueQueue)) {

                // For each operator adding parenthesis before starting 
                StringBuilder sbTmp = new StringBuilder();
                operatorStack.ToList().ForEach(x => sbTmp.Append("("));
                sb.Append(sbTmp.ToString());

                while (valueQueue.Count > 0) {
                    if (operatorStack.Count > 0) {
                        strOperator = operatorStack.Pop();

                    } else {
                        strOperator = string.Empty;
                    }

                    sb.Append(valueQueue.Dequeue());

                    // After each logical operation closing parenthesis 
                    if (previousOp != string.Empty)
                        sb.Append(")");

                    if (strOperator != string.Empty) 
                        sb.Append(" " + strOperator + " ");

                    previousOp = strOperator;
                }
            }
            isSuccess = true;
        } catch (Exception) {
            isSuccess = false;
        }

        return isSuccess;
    }

This will group the couple inside the parentheses ... and that's all

Upvotes: 0

Francis
Francis

Reputation: 361

I was also hoping to find a piece of code to convert CAML to SQL in order to build my own SQL Statement to access the data.

My main project is to build a SharePoint ribbon extension to export Lists (Internal and External) content to CSV and in the case of external lists, to be able to bypass the throttling limit (2000) imposed in External Content Types (BCS).

I'm using the information in the metada store and the secure store to build the connection string and access the Database directly.

When I needed to improve my code to include filters, I ended up building my own methods to get the "Where" part of a View Query and convert it to a SQL-Like Where statement:

Input:

In my case it's a SPView object but it can easily be converted to use a string instead. I'm extracting the "CAML" like query from it.

"<Where>
  <And>
    <Or>
      <Geq>
        <FieldRef Name=\"Microfilm\" />
        <Value Type=\"Text\">10</Value>
      </Geq>
      <Leq>
        <FieldRef Name=\"Microfilm\" />
        <Value Type=\"Text\">50</Value>
      </Leq>
    </Or>
    <BeginsWith>
      <FieldRef Name=\"Title\" />
      <Value Type=\"Text\">Ice</Value>
    </BeginsWith> 
  </And>
</Where>"

Ouput: 

"(Microfilm >= 10 OR Microfilm <= 50) AND Title LIKE 'Ice%'"

Here are the methods:

This method will extract the "Where" node from a view Query and pass it to a method to process it and return a SQL like statement.

    private static string ViewQueryToSqlWhere(SPView v)
    {
        string sqlWhere = string.Empty;
        XmlDocument xmlDoc = new XmlDocument();
        XmlNodeList nodeList;

        //Add <Query> around the SPView.Query since a valid XML document requires a single root element.
        //and SPView.Query doesn't.
        xmlDoc.LoadXml("<Query>" + v.Query + "</Query>");

        nodeList = xmlDoc.GetElementsByTagName("Where");

        if (nodeList.Count == 1)
        {
            XmlNode nodeWhere = nodeList[0];

            if (nodeWhere.HasChildNodes) //Should Always be the case
            {
                StringBuilder sb = new StringBuilder();
                bool isSuccess = ProcessWhereNode(nodeWhere, ref sb);
            }
        }

        return sqlWhere;
    }

This method will call another method to recursively go through all the nodes to get the values and operators within the View Query "Where" node. It will put round bracket around "OR" statements to conserve the operation priority.

    private static bool ProcessWhereNode(XmlNode xmlNode, ref StringBuilder sb)
    {
        bool isSuccess = false;
        Stack<string> operatorStack = new Stack<string>();
        Queue<string> valueQueue = new Queue<string>();
        string previousOp = string.Empty;
        string strOperator = string.Empty;

        try
        {
            //Call a method to iterate "recursively" throught the nodes to get the values and operators.
            if (ProcessRecursiveWhereNode(xmlNode, "", "", ref operatorStack, ref valueQueue))
            {
                while (valueQueue.Count > 0)
                {
                    if (operatorStack.Count > 0)
                    {
                        strOperator = operatorStack.Pop();

                        //Open bracket if it's an OR operator except if the previous one was also an OR.
                        if (strOperator == "OR" && previousOp != "OR")
                            sb.Append("(");
                    }
                    else
                    {
                        strOperator = string.Empty;
                    }

                    sb.Append(valueQueue.Dequeue());

                    //Close bracket if previous OP was an OR, and it's not followed by another one
                    if (previousOp == "OR" && strOperator != "OR")
                        sb.Append(")");

                    if (strOperator != string.Empty)
                    {
                        sb.Append(" " + strOperator + " ");
                    }

                    previousOp = strOperator;
                }
            }
        }
        catch (Exception ex)
        { }

        return isSuccess;
    }

This method does most of the work to go throught an iterate each nodes:

private static bool ProcessRecursiveWhereNode(XmlNode xmlNode, string strOperatorValue, string strOperatorType, ref Stack<string> operatorStack, ref Queue<string> valueQueue)
        {
            bool isSuccess = false;
            string fieldName = string.Empty;
            string value = string.Empty;
            string thisIterationOperatorType = string.Empty;
            string thisIterationOperatorValue = string.Empty;

            try
            {
                XmlNodeList nodeList = xmlNode.ChildNodes;

                //Get Child node - Possible tags {<Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>, <FieldRef>, <Value>}
                foreach (XmlNode node in nodeList)
                {
                    thisIterationOperatorType = string.Empty;
                    thisIterationOperatorValue = string.Empty;

                    //Check if it's one of these tag: <Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>
                    thisIterationOperatorValue = GetOperatorString(node.Name, out thisIterationOperatorType);

                    if (thisIterationOperatorType == "statement")
                        operatorStack.Push(thisIterationOperatorValue);

                    //It's one of these tag: <Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>
                    if (thisIterationOperatorValue != string.Empty)
                    {
                        ProcessRecursiveWhereNode(node, thisIterationOperatorValue, thisIterationOperatorType, ref operatorStack, ref valueQueue);
                    }
                    else //It is probably a <FieldRef> or <Value> tag.
                    {
                        if (node.Name == "FieldRef")
                            fieldName = node.Attributes["Name"].Value.ToString();
                        else if (node.Name == "Value")
                            value = node.LastChild.Value.ToString();
                    }
                }

                if (strOperatorType == "value" && strOperatorValue != string.Empty && fieldName != string.Empty && value != string.Empty)
                {
                    valueQueue.Enqueue(string.Format(strOperatorValue, fieldName, "'" + value + "'"));
                }

                isSuccess = true;
            }
            catch
            {
                isSuccess = false;
                throw;
            }

            return isSuccess;
        }

This last methods could probably been included in the recursive one but in my first iteration of building the code it made more sense to make a separate one and I kept it this way.

It simply gets some information on the operators and associates an operator string which will be used to construct the individual pieces of the SQL Where Statement.

static private string GetOperatorString(string tagName, out string operatorType)
{
    string operatorString = string.Empty;

    switch (tagName)
    {
        case "Or":
            operatorString = "OR";
            operatorType = "statement";
            break;
        case "And":
            operatorString = "AND";
            operatorType = "statement";
            break;
        case "Eq":
            operatorString = "{0} = {1}";
            operatorType = "value";
            break;
        case "Neq":
            operatorString = "{0} != {1}";
            operatorType = "value";
            break;
        case "Gt":
            operatorString = "{0} > {1}";
            operatorType = "value";
            break;
        case "Lt":
            operatorString = "{0} < {1}";
            operatorType = "value";
            break;
        case "Geq":
            operatorString = "{0} >= {1}";
            operatorType = "value";
            break;
        case "Leq":
            operatorString = "{0} <= {1}";
            operatorType = "value";
            break;
        case "BeginsWith":
            operatorString = "{0} LIKE '{1}%";
            operatorType = "value";
            break;
        case "Contains":
            operatorString = "{0} LIKE '%{1}%";
            operatorType = "value";
            break;
        default:
            operatorString = string.Empty;
            operatorType = string.Empty;
            break;
    }

    return operatorString;
}

I know it's not a full conversion tool but it's a start and for now it fits my need. I hope this will help someone and save them some valuable time.

Upvotes: 2

booriz
booriz

Reputation: 11

My very simple class convert from string sql to caml , for example: 

CSqlToCAML.TextSqlToCAML(sql);

sql = .... 

select id,evid_cislo,nazov,adresa,ulica,vec,datum_zal,datum_odos,ukoncene_dna  
from koresp  
where ((id_typ <= 3 or id_typ = 4) 
 and (datum_zal > datum_odos)) or (id > 21) 
order by nazov desc ,id asc

CAML output is .....

<Query>
<ViewFields>
 <FieldRef Name=" id" /><FieldRef Name="evid_cislo" /><FieldRef Name="nazov" />
 <FieldRef Name="adresa" /><FieldRef Name="ulica" />
 <FieldRef Name="vec" /><FieldRef Name="datum_zal" />
 <FieldRef Name="datum_odos" /><FieldRef Name="ukoncene_dna  " />
</ViewFields>
<Where>
 <Or>
  <Leq><FieldRef Name="id_typ" /><Value Type="Text">3</Value></Leq>
  <Eq><FieldRef Name="id_typ" /><Value Type="Text">4</Value></Eq>
 </Or>
 <Or>
  <Gt><FieldRef Name="datum_zal" /><Value Type="Text">datum_odos</Value></Gt>
 </Or>
 <Or>
  <Gt><FieldRef Name="id" /><Value Type="Text">21</Value></Gt>
 </Or>
</Where>
<OrderBy>
 <FieldRef Name="nazov" Ascending="FALSE" />
 <FieldRef Name="id" Ascending="TRUE" />
</OrderBy>
</Query>

Class src: 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace SPASMXServices.ISAPI
{
public static class CSqlToCAML
{


    public static string TextSqlToCAML(string query)
    {
        string ret = "";
        try
        {
            string[] grpsExpr = query.ToLower().Split(new string[] { "select","from","where","order by","having" }, StringSplitOptions.RemoveEmptyEntries);
            ret += TextSqlToCAML(getValueStrArr(grpsExpr, 0), 
                                 getValueStrArr(grpsExpr, 1), 
                                 getValueStrArr(grpsExpr, 2), 
                                 getValueStrArr(grpsExpr, 3), 
                                 getValueStrArr(grpsExpr, 4)
                                );
        }
        catch (Exception ex)
        {
            Log("CSqlToCAML.TextSqlToCAML() error: " + ex.Message);
        }
        return ret;
    }

    public static string TextSqlToCAML(string select, string from, string where, string orderby, string having)
    {
        string ret = "<Query>";
        try
        {
            ret += sqltocamlSelect(select);
            ret += sqltocamlWhere(where);
            ret += sqltocamlOrderBy(orderby);
        }
        catch (Exception ex)
        {
            Log("CSqlToCAML.TextSqlToCAML() error: " + ex.Message);
        }
        return ret + "</Query>";
    }


    private static string getValueStrArr(string[] strs, int index)
    {
        try
        {
            return strs[index];
        }
        catch
        {
            return "";
        }
    }

    private static string sqltocamlOrderBy(string _orderby)
    {
        string ret = "";
        try
        {
            ret += "<OrderBy>\n";
            string[] grpsExpr = _orderby.ToLower().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string expr in grpsExpr)
            {
                string val = expr.ToLower();
                string ascc = val.ToLower().Contains("asc") ? "TRUE" : val.ToLower().Contains("desc") ? "FALSE" : "TRUE";
                val = val.Replace("asc", "");
                val = val.Replace("desc", "");
                val = val.Trim();
                ret += string.Format("<FieldRef Name=\"{0}\" Ascending=\"{1}\" />\n", val,ascc).Trim();
            }
            ret += "</OrderBy>\n";
        }
        catch (Exception ex)
        {
            Log("CSqlToCAML.sqltocamlSelect() error: " + ex.Message);
        }
        return ret;
    }

    private static string sqltocamlSelect(string _select)
    {
        string ret = "";
        try
        {
            ret += "<ViewFields>\n";
            string[] grpsExpr = _select.ToLower().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string expr in grpsExpr)
            {
                ret += string.Format("<FieldRef Name=\"{0}\" />\n", expr).Trim(); 
            }
            ret += "</ViewFields>\n";
        }
        catch (Exception ex)
        {
            Log("CSqlToCAML.sqltocamlSelect() error: " + ex.Message);
        }
        return ret;
    }

    private static string sqltocamlWhere(string _where)
    {
        string ret = "", retAnd = "", retOr = "";
        try
        {
            /*
            •Eq = equal to  
            •Neq = not equal to 
            •BeginsWith = begins with 
            •Contains = contains 
            •Lt = less than 
            •Leq = less than or equal to
            •Gt = greater than 
            •Geq = greater than or equal to 
            •IsNull = is null 
            •IsNotNull = is not null
            */

            // "(id_typ = 3 or id_typ = 4) and (datum_zal > datum_odos) "
            ret += "<Where>\n";
            string[] grpsExpr = _where.ToLower().Split(new string[] { "(", ")"}, StringSplitOptions.RemoveEmptyEntries);
            foreach (string expr in grpsExpr)
            {

                if (expr.Contains("and"))
                {
                    retAnd = "";
                    foreach (string exp in expr.Split(new string[] { "and" }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        retAnd += expStr(exp);
                    }
                    if (retAnd.Length > 0)
                    {
                        ret += "<And>\n";
                        ret += retAnd;
                        ret += "</And>\n";
                    }
                }

                if (expr.Contains("or") != null)
                {
                    retOr = "";
                    foreach (string exp in expr.Split(new string[] { "or" }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        retOr += expStr(exp);
                    }
                    if (retOr.Length > 0)
                    {
                        ret += "<Or>\n";
                        ret += retOr;
                        ret += "</Or>\n";
                    }
                }
            }
            ret += "</Where>\n";
        }
        catch (Exception ex)
        {
            Log("CSqlToCAML.sqltocamlWhere() error: " + ex.Message);
        }
        return ret;
    }

    private static string expStr(string exp)
    {
        string ret = "";
        ret += propExp(exp, "=");
        ret += propExp(exp, "<>");
        ret += propExp(exp, "<");
        ret += propExp(exp, ">");
        ret += propExp(exp, "<=");
        ret += propExp(exp, ">=");
        ret += propExp(exp, "is null");
        ret += propExp(exp, "is not null");
        ret += propExp(exp, "in");
        ret += propExp(exp, "like");
        ret += propExp(exp, "between");
        return ret;
    }


    private static string propExp(string sExp, string op)
    {
        string ret = "", _op = "";
        try
        {
            if (!sExp.Contains(op))
                return "";
            sExp = sExp.Replace("'", " ");
            sExp = sExp.Replace("   "," ");
            sExp = sExp.Replace("  ", " ");                
            string[] _ops = sExp.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
            string[] _opx = sExp.Split(new string[] { op }, StringSplitOptions.RemoveEmptyEntries);

            if (_ops[1] != op)
                return "";

            string name, value;
            name = sExp.Split(new string[] { op }, StringSplitOptions.RemoveEmptyEntries)[0];
            value = sExp.Split(new string[] { op }, StringSplitOptions.RemoveEmptyEntries)[1];
            value = value.Trim();
            name = name.Trim();

            while(true)
            {

                if (sExp.Contains(op) && op == "<=")
                {
                    _op = "Leq";
                    break;
                }

                if (sExp.Contains(op) && op == ">=")
                {
                    _op = "Geq";
                    break;
                }

                if (sExp.Contains(op) && op == "=")
                {
                    _op = "Eq";
                    break;
                }

                if (sExp.Contains(op) && op == "<>")
                {
                    _op = "Eq";
                    break;
                }

                if (sExp.Contains(op) && op == "<>" && sExp.Contains("null"))
                {
                    _op = "IsNotNull";
                    break;
                }

                if (sExp.Contains(op) && op == "is not null")
                {
                    _op = "IsNotNull";
                    break;
                }

                if (sExp.Contains(op) && op == "is null")
                {
                    _op = "IsNull";
                    break;
                }

                if (sExp.Contains(op) && op == "<")
                {
                    _op = "Lt";
                    break;
                }

                if (sExp.Contains(op) && op == ">")
                {
                    _op = "Gt";
                    break;
                }
                break;
            }
            if (!string.IsNullOrEmpty(_op) && !string.IsNullOrEmpty(name))
                ret += string.Format("<{0}><FieldRef Name=\"{1}\" /><Value Type=\"Text\">{2}</Value></{0}>\n", _op, name, value);
        }
        catch (Exception ex)
        {
            Log("CSqlToCAML.propExp(" + sExp + ") error: " + ex.Message);
        }
        return ret;
    }


    private static void Log(string text)
    {
        //MessageBox.Show(text);
        LOG += string.Format("[{0} - {1};\n]", DateTime.Now, text);
    }


    public static string LOG;
}
}

(bob.)

Upvotes: 1

Sachin Zade
Sachin Zade

Reputation: 1

Microsoft supports SharePoint List data access through: 1. SharePoint Object Model – SPSite and SPWeb 2. Lists web service. Path to access is http://Your_Site/_vti_bin/lists.asmx

Any Add/Update/Delete/Select operations on the SharePoint List is done using any of the above 2 methods goes through the unmanaged COM component. This COM is responsible for establishing the connection with Content DB; applying Data Locks on the Tables and data retrieval. This COM component has its own Logic/Mechanism to apply Data Lock on the Content DB tables and Sharepoint developer does not have control over the Data Lock mechanism. If T-SQL statements (Add/Update/Delete/Select) are executed directly on the Content DB tables, this In-Build logic may break and can be resulted into unknown results or errors. Microsoft does not support direct T-SQL statement execution on Content DB tables.

Upvotes: 0

Eric Herlitz
Eric Herlitz

Reputation: 26297

It's possible if you use some sort of ado.net connector for SharePoint, have a look at http://www.bendsoft.com/net-sharepoint-connector/

It enables you to talk to SharePoint lists as if they where ordinary sql tables

In example to insert some data

public void SharePointConnectionExample1()
{
    using (SharePointConnection connection = new SharePointConnection(@"
                Server=mysharepointserver.com;
                Database=mysite/subsite
                User=spuser;
                Password=******;
                Authentication=Ntlm;
                TimeOut=10;
                StrictMode=True;
                RecursiveMode=RecursiveAll;
                DefaultLimit=1000;
                CacheTimeout=5"))
    {
        connection.Open();
        using (SharePointCommand command = new SharePointCommand("UPDATE `mytable` SET `mycolumn` = 'hello world'", connection))
        {
            command.ExecuteNonQuery();
        }
    }
}

Or to select list data to a DataTable

string query = "SELECT * FROM list";
conn = new SharePointConnection(connectionString);
SharePointDataAdapter adapter = new SharePointDataAdapter(query, conn);

DataTable dt = new DataTable();
adapter.Fill(dt);

Or using a helper method to fill a DataGrid

string query = "Select * from mylist.viewname";
DataGrid dataGrid = new DataGrid();
dataGrid.DataSource = Camelot.SharePointConnector.Data.Helper.ExecuteDataTable(query, connectionString);
dataGrid.DataBind();
Controls.Add(dataGrid);

Here is a webcast illustrating how to build a simple querybrowser for SharePoint, http://www.youtube.com/watch?v=HzKVTZEsL4Y

Hope it helps!

Upvotes: 0

brian brinley
brian brinley

Reputation: 2364

This is not technically an answer but I feel its needed. Your approach is a bit backwards. What it sounds to me like you are really trying to do is use sharepoint as a UI to your data warehouse. If that is the case I would switch your approach to one of several options outside the scope of this question.

Data access in sharepoint is only supported by microsoft through the object model, web services or the user interface. Any interaction outside this can result in a vast range of unsupported modes, anything from corrupt data, data locks, varying result sets, security access, etc...

What it sounds like really need is the BDC services. This would allow you to use the data warehouse for central storage and also provide you with the sharepoint native functionality of the list interactions.

I'm not sure which version you are using but if you REALLY want to hit the data directly there are a large amount of articles in the SQL community about accessing the data directly. There is also a codeplex project for LINQtoSharePoint http://linqtosharepoint.codeplex.com/ You can use this for object support that you can use LINQ to give you sql-esque type functionality.

Upvotes: 1

Related Questions