Ejemplo n.º 1
0
    public static void GetSPList(SqlString SPUrl, SqlString list_name)
    {
        SPWebService WebService = new SPWebService(SPUrl.ToString());
        SPList       MyList     = WebService.GetSPList(list_name.ToString());

        List <SqlMetaData> Cols = new List <SqlMetaData>();

        //
        // The logic is to iterate through each ColumDef from the SharePoint list and add a corresponding
        // column metadata for the result set to SQL Server.  Basically, this loop matches up data types
        // instead of having the SQL resultset have all columns with varchar (which probably would have been fine
        // for most purposes)
        //
        foreach (ColumnDef CD in MyList.GetColumnDefs())
        {
            switch (CD.ColType.ToUpper())
            {
            case "TEXT":
                Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.NVarChar, CD.ColLen));
                break;

            case "NUMBER":
                Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.Decimal, 18, 5));
                break;

            case "CURRENCY":
                Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.Money));
                break;

            case "DATETIME":
                Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.DateTime2));
                break;

            case "BOOLEAN":
                Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.Bit));
                break;

            default:
                Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.NVarChar, 4000));
                break;
            }
        }

        SqlPipe pipe = SqlContext.Pipe;

        SqlDataRecord rec = new SqlDataRecord(Cols.ToArray());

        pipe.SendResultsStart(rec);

        int      i;
        decimal  tempdec;
        DateTime tempdt;

        //
        // The logic here is similar to the columns above, but it is being done for each row
        // in the result set.  The list data comes out of the webservice as XML which I left as strings.
        // For each row, it iterates through each column and compares what the datatype is in SharePoint (ColumnDef)
        // and does a conversion for the resultset
        //

        foreach (var listrow in MyList.GetRows())
        {
            i = -1;
            foreach (ColumnDef CD in MyList.GetColumnDefs())
            {
                i++;
                tempdec = 0;
                switch (CD.ColType.ToUpper())
                {
                case "TEXT":
                    rec.SetSqlString(i, listrow[i]);
                    break;

                case "NUMBER":
                    decimal.TryParse(listrow[i], out tempdec);
                    rec.SetSqlDecimal(i, tempdec);
                    break;

                case "CURRENCY":
                    decimal.TryParse(listrow[i], out tempdec);
                    rec.SetSqlMoney(i, tempdec);
                    break;

                case "DATETIME":
                    if (!DateTime.TryParse(listrow[i], out tempdt))
                    {
                        tempdt = new DateTime(1900, 1, 1);
                    }
                    rec.SetSqlDateTime(i, tempdt);
                    break;

                case "BOOLEAN":
                    if (listrow[i].Equals("1"))
                    {
                        rec.SetSqlBoolean(i, true);
                    }
                    else
                    {
                        rec.SetSqlBoolean(i, false);
                    }
                    break;

                default:
                    rec.SetSqlString(i, listrow[i]);
                    break;
                }
            }
            pipe.SendResultsRow(rec);
        }
        pipe.SendResultsEnd();
    }