/// <summary>
/// Returns an Enumerator to insert format the data for a SQL Database
/// </summary>
/// <returns></returns>
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("PointName", SqlDbType.VarChar, 400),
new SqlMetaData("PointGuid", SqlDbType.UniqueIdentifier),
new SqlMetaData("PointType", SqlDbType.VarChar, 400),
new SqlMetaData("ItemReference", SqlDbType.VarChar, 400),
new SqlMetaData("IsReliable", SqlDbType.Bit),
new SqlMetaData("TimeStamp", SqlDbType.DateTime),
new SqlMetaData("Units", SqlDbType.VarChar, 100),
new SqlMetaData("Value", SqlDbType.VarChar, 400));
foreach (var desc in this)
{
sdr.SetSqlString(0, desc.PointName);
sdr.SetSqlGuid(1, desc.PointGuid);
sdr.SetSqlString(2, desc.PointType);
sdr.SetSqlString(3, desc.ItemReference);
sdr.SetSqlBoolean(4, desc.IsReliable);
sdr.SetSqlDateTime(5, desc.TimeStamp);
sdr.SetSqlString(6, desc.Units);
sdr.SetSqlString(7, desc.Value.ToString());
yield return(sdr);
}
}
public static void SetBoolean(this SqlDataRecord record, int index, bool?value)
{
if (value == null)
{
record.SetDBNull(index);
}
else
{
record.SetSqlBoolean(index, value ?? false);
}
}
private static void BindFile(FileInfo _file, SqlDataRecord record)
{
record.SetSqlDateTime(0, _file.Exists ? _file.CreationTime : SqlDateTime.Null);
record.SetSqlDateTime(1, _file.Exists ? _file.LastAccessTime : SqlDateTime.Null);
record.SetSqlDateTime(2, _file.Exists ? _file.LastWriteTime : SqlDateTime.Null);
record.SetSqlBoolean(3, _file.Exists);
record.SetString(4, _file.Name);
record.SetString(5, _file.DirectoryName);
record.SetString(6, _file.Extension);
record.SetSqlInt64(7, _file.Exists ? _file.Length : SqlInt64.Null);
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sqlRow = new SqlDataRecord(
new SqlMetaData("Uri", SqlDbType.VarChar, 128),
new SqlMetaData("Status", SqlDbType.VarChar, 10),
new SqlMetaData("IsPartiallySupported", SqlDbType.Bit));
foreach (ResourceSearchParameterStatus status in this)
{
sqlRow.SetString(0, status.Uri.OriginalString);
sqlRow.SetString(1, status.Status.ToString());
sqlRow.SetSqlBoolean(2, status.IsPartiallySupported);
yield return(sqlRow);
}
}
public static IEnumerable <SqlDataRecord> CreateSqlDataRecord(this IEnumerable <Tuple <int, string, bool> > records)
{
var metaData = new SqlMetaData[3];
metaData[0] = new SqlMetaData("IntegerID", System.Data.SqlDbType.Int);
metaData[1] = new SqlMetaData("Code", System.Data.SqlDbType.VarChar);
metaData[2] = new SqlMetaData("IsSelected", System.Data.SqlDbType.Bit);
foreach (var item in records)
{
var sqldataRecord = new SqlDataRecord(metaData);
sqldataRecord.SetInt32(0, item.Item1);
sqldataRecord.SetSqlString(1, item.Item2);
sqldataRecord.SetSqlBoolean(2, item.Item3);
yield return(sqldataRecord);
}
}
private static void SetBitToDataRecord(ref SqlDataRecord record, int index, bool?value)
{
try
{
if (value.HasValue)
{
record.SetSqlBoolean(index, value.Value);
}
else
{
record.SetDBNull(index);
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
}
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();
}
