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);
}
/// <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 SetNullableDateTime(this SqlDataRecord sdr, int index, DateTime?value)
{
if (value.HasValue)
{
sdr.SetSqlDateTime(index, value.GetValueOrDefault());
}
else
{
sdr.SetDBNull(index);
}
}
private static SqlDataRecord buildRecord(SqlMetaData[] metadata, EventLogEntry entry)
{
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetSqlInt32(0, entry.Index);
record.SetSqlInt64(1, entry.InstanceId);
record.SetSqlDateTime(2, entry.TimeWritten);
record.SetSqlString(3, entry.MachineName);
record.SetSqlString(4, entry.Source);
record.SetSqlString(5, entry.UserName);
record.SetSqlString(6, entry.Message);
return(record);
}
public void SqlRecordFillTest()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.Bit),
new SqlMetaData("col2", SqlDbType.TinyInt),
new SqlMetaData("col3", SqlDbType.VarBinary, 1000),
new SqlMetaData("col4", SqlDbType.NVarChar, 1000),
new SqlMetaData("col5", SqlDbType.DateTime),
new SqlMetaData("col6", SqlDbType.Float),
new SqlMetaData("col7", SqlDbType.UniqueIdentifier),
new SqlMetaData("col8", SqlDbType.SmallInt),
new SqlMetaData("col9", SqlDbType.Int),
new SqlMetaData("col10", SqlDbType.BigInt),
new SqlMetaData("col11", SqlDbType.Real),
new SqlMetaData("col12", SqlDbType.Decimal),
new SqlMetaData("col13", SqlDbType.Money),
new SqlMetaData("col14", SqlDbType.Variant)
};
SqlDataRecord record = new SqlDataRecord(metaData);
for (int i = 0; i < record.FieldCount; i++)
{
Assert.Equal($"col{i + 1}", record.GetName(i));
}
record.SetBoolean(0, true);
Assert.Equal(true, record.GetBoolean(0));
record.SetByte(1, 1);
Assert.Equal(1, record.GetByte(1));
byte[] bb1 = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
byte[] bb2 = new byte[5];
record.SetSqlBinary(2, new SqlBinary(new byte[0]));
record.SetBytes(2, 0, bb1, 0, 3);
record.SetBytes(2, 2, bb1, 6, 3);
// Verify the length of the byte array
Assert.Equal(5, record.GetBytes(2, 0, bb2, 0, 5));
Assert.Equal(5, record.GetBytes(2, 0, null, 0, 0));
byte[] expected = new byte[] { 1, 2, 7, 8, 9 };
Assert.Equal <byte>(expected, bb2);
char[] cb1 = new char[] { 'a', 'b', 'c', 'd', 'e', 'f', 'g' };
char[] cb2 = new char[5];
record.SetChars(3, 0, cb1, 0, 3);
record.SetChars(3, 2, cb1, 4, 3);
char[] expectedValue = new char[] { 'a', 'b', 'e', 'f', 'g' };
Assert.Equal(expectedValue.Length, record.GetChars(3, 0, cb2, 0, 5));
Assert.Equal <char>(expectedValue, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
record.SetString(3, "");
string xyz = "xyz";
record.SetString(3, "xyz");
Assert.Equal(xyz, record.GetString(3));
Assert.Equal(xyz.Length, record.GetChars(3, 0, cb2, 0, 5));
Assert.Equal(xyz, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
record.SetChars(3, 2, cb1, 4, 3);
Assert.Equal(5, record.GetChars(3, 0, cb2, 0, 5));
string interleavedResult = "xyefg";
Assert.Equal(interleavedResult, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
Assert.Equal(interleavedResult, record.GetString(3));
record.SetSqlDateTime(4, SqlDateTime.MaxValue);
Assert.Equal(SqlDateTime.MaxValue, record.GetSqlDateTime(4));
record.SetSqlDouble(5, SqlDouble.MaxValue);
Assert.Equal(SqlDouble.MaxValue, record.GetSqlDouble(5));
SqlGuid guid = new SqlGuid("F9168C5E-CEB2-4faa-B6BF-329BF39FA1E4");
record.SetSqlGuid(6, guid);
Assert.Equal(guid, record.GetSqlGuid(6));
record.SetSqlInt16(7, SqlInt16.MaxValue);
Assert.Equal(SqlInt16.MaxValue, record.GetSqlInt16(7));
record.SetSqlInt32(8, SqlInt32.MaxValue);
Assert.Equal(SqlInt32.MaxValue, record.GetSqlInt32(8));
record.SetSqlInt64(9, SqlInt64.MaxValue);
Assert.Equal(SqlInt64.MaxValue, record.GetSqlInt64(9));
record.SetSqlSingle(10, SqlSingle.MinValue);
Assert.Equal(SqlSingle.MinValue, record.GetSqlSingle(10));
record.SetSqlDecimal(11, SqlDecimal.Null);
record.SetSqlDecimal(11, SqlDecimal.MaxValue);
Assert.Equal(SqlDecimal.MaxValue, record.GetSqlDecimal(11));
record.SetSqlMoney(12, SqlMoney.MaxValue);
Assert.Equal(SqlMoney.MaxValue, record.GetSqlMoney(12));
// Try adding different values to SqlVariant type
for (int i = 0; i < record.FieldCount - 1; ++i)
{
object valueToSet = record.GetSqlValue(i);
record.SetValue(record.FieldCount - 1, valueToSet);
object o = record.GetSqlValue(record.FieldCount - 1);
if (o is SqlBinary)
{
Assert.Equal <byte>(((SqlBinary)valueToSet).Value, ((SqlBinary)o).Value);
}
else
{
Assert.Equal(valueToSet, o);
}
record.SetDBNull(record.FieldCount - 1);
Assert.Equal(DBNull.Value, record.GetSqlValue(record.FieldCount - 1));
record.SetDBNull(i);
Assert.Equal(DBNull.Value, record.GetValue(i));
}
}
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();
}
