private static void sendEachRecordOfData(SqlDataReader dataReader, SqlMetaData[] meta)
{
while (dataReader.Read())
{
SqlContext.Pipe.SendResultsRow(createRecordPopulatedWithData(dataReader, meta));
}
}
protected static IEnumerable<SqlDataRecord> GenerateMetaPropertyTable(MetaObject o)
{
var metaFields = new List<SqlDataRecord>();
try
{
SqlMetaData[] metaData = new SqlMetaData[2];
metaData[0] = new SqlMetaData("FieldName", SqlDbType.VarChar, 30);
metaData[1] = new SqlMetaData("FieldValue", SqlDbType.VarChar, -1);
foreach (KeyValuePair<string, JToken> prop in o.MetaPropertiesObject)
{
SqlDataRecord record = new SqlDataRecord(metaData);
record.SetString(0, prop.Key);
// coming from the DB the value will be an object representing the field, with a "Value" key
// coming from the client the value will be a single value
var value = prop.Value.SelectToken("Value") ?? prop.Value;
if (value.Type == JTokenType.Null)
{
record.SetDBNull(1);
}
else
{
record.SetString(1, value.ToString());
}
metaFields.Add(record);
}
}
catch (Exception e)
{
}
return metaFields;
}
private static SqlDataRecord createRecordPopulatedWithData(SqlDataReader dataReader, SqlMetaData[] meta)
{
SqlDataRecord rec = new SqlDataRecord(meta);
object[] recordData = new object[dataReader.FieldCount];
dataReader.GetSqlValues(recordData);
rec.SetValues(recordData);
return rec;
}
public static void HelloWorld()
{
Microsoft.SqlServer.Server.SqlMetaData columnInfo
= new Microsoft.SqlServer.Server.SqlMetaData("Column1", SqlDbType.NVarChar, 12);
SqlDataRecord greetingRecord
= new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo });
greetingRecord.SetString(0, "Hello world!");
SqlContext.Pipe.Send(greetingRecord);
}
private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] coerceToString)
{
SqlMetaData[] metaDataResult = new SqlMetaData[dt.Columns.Count];
coerceToString = new bool[dt.Columns.Count];
for (int index = 0; index < dt.Columns.Count; index++)
{
DataColumn column = dt.Columns[index];
metaDataResult[index] = SqlMetaDataFromColumn(column, out coerceToString[index]);
}
return metaDataResult;
}
/// <summary>Extracts the data table column meta data.</summary>
/// <param name="dt">The dt.</param>
/// <param name="useToString">[out] The coerce to string.</param>
/// <returns>The extracted data table column meta data.</returns>
private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] useToString)
{
var metaDataResult = new SqlMetaData[dt.Columns.Count];
useToString = new bool[dt.Columns.Count];
for (var index = 0; index < dt.Columns.Count; index++)
{
var column = dt.Columns[index];
metaDataResult[index] = SqlMetaDataFromColumn(column, out useToString[index]);
}
return metaDataResult;
}
public static void GerarEventoAuditoria(this DbContext context, int codigoTipoEventoAuditoria, Func<int> idUsuarioFunc, params ParametroEvento[] parametrosEvento)
{
string siglaSistemaPermisys = ConfigurationManager.AppSettings["Permisys.SiglaSistema"];
string siglaModuloPermisys = ConfigurationManager.AppSettings["Permisys.SiglaModulo"];
if (string.IsNullOrWhiteSpace("Permisys.SiglaSistema") || string.IsNullOrWhiteSpace("Permisys.SiglaModulo")) {
throw new Exception("As configurações \"Permisys.SiglaSistema\" e \"Permisys.SiglaModulo\" são obrigatórias.");
}
if (idUsuarioFunc == null) {
throw new Exception("A função de obtenção do ID do usuário é obrigatória.");
}
int idUsuarioPermisys = idUsuarioFunc();
List<SqlDataRecord> parametros = null;
if (parametrosEvento != null && parametrosEvento.Count() > 0) {
parametros = new List<SqlDataRecord>();
SqlMetaData[] rowMetadata = new SqlMetaData[] {
new SqlMetaData("ORDEM", SqlDbType.Int),
new SqlMetaData("NOME", SqlDbType.VarChar, 20),
new SqlMetaData("VALOR", SqlDbType.VarChar, 100)
};
foreach (var parametro in parametrosEvento) {
if (string.IsNullOrWhiteSpace(parametro.Nome) || string.IsNullOrWhiteSpace(parametro.Valor)) {
throw new Exception("O \"Nome\" e \"Valor\" são obrigatórios para todos os parâmetros.");
}
SqlDataRecord row = new SqlDataRecord(rowMetadata);
row.SetInt32(0, parametro.Ordem);
row.SetString(1, parametro.Nome);
row.SetString(2, parametro.Valor);
parametros.Add(row);
}
}
context.Database.ExecuteSqlCommand("LOGSYS.SP_GERAR_EVENTO_AUDITORIA @SIGLA_SISTEMA_PERMISYS, @SIGLA_MODULO_PERMISYS, @ID_USUARIO_PERMISYS, @CODIGO_TIPO_EVENTO_AUDITORIA, @PARAMETROS_EVENTO",
new object[] {
new SqlParameter("SIGLA_SISTEMA_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaSistemaPermisys },
new SqlParameter("SIGLA_MODULO_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaModuloPermisys },
new SqlParameter("ID_USUARIO_PERMISYS", SqlDbType.Int) { Value = idUsuarioPermisys },
new SqlParameter("CODIGO_TIPO_EVENTO_AUDITORIA", SqlDbType.Int) { Value = codigoTipoEventoAuditoria },
new SqlParameter("PARAMETROS_EVENTO", SqlDbType.Structured) { TypeName = "LOGSYS.LOGSYS_LISTA_PARAMETROS", Value = parametros }
}
);
}
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;
}
private static SqlMetaData[] createMetaDataForResultset(SqlDataReader dataReader)
{
DataTable schema = dataReader.GetSchemaTable();
LinkedList<DataRow> columns = getDisplayedColumns(schema);
SqlMetaData[] meta = new SqlMetaData[columns.Count];
int columnCount = 0;
foreach (DataRow column in columns)
{
meta[columnCount] = createSqlMetaDataForColumn(column);
columnCount++;
}
return meta;
}
public static void AccountantPagesCounterIncrementor(string columnName)
{
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
string txtCommand = string.Format(@"UPDATE [dbo].[AccountantPagesCounters] SET {0}=({0} + 1) WHERE Id=1;"
+ "SELECT {0} FROM [dbo].[AccountantPagesCounters] WHERE Id=1;", columnName);
SqlCommand cmd = new SqlCommand(txtCommand, connection);
int sheetNumber = (int)cmd.ExecuteScalar();
SqlMetaData sqlMetaData = new SqlMetaData("AccountantPageNumber", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(sqlMetaData);
record.SetInt32(0, sheetNumber);
SqlContext.Pipe.Send(record);
}
}
private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool coerceToString)
{
coerceToString = false;
SqlMetaData sql_md = null;
Type clrType = column.DataType;
string name = column.ColumnName;
switch (Type.GetTypeCode(clrType))
{
case TypeCode.Boolean: sql_md = new SqlMetaData(name, SqlDbType.Bit); break;
case TypeCode.Byte: sql_md = new SqlMetaData(name, SqlDbType.TinyInt); break;
case TypeCode.Char: sql_md = new SqlMetaData(name, SqlDbType.NVarChar, 1); break;
case TypeCode.DateTime: sql_md = new SqlMetaData(name, SqlDbType.DateTime); break;
case TypeCode.DBNull: throw InvalidDataTypeCode(TypeCode.DBNull);
case TypeCode.Decimal: sql_md = new SqlMetaData(name, SqlDbType.Decimal, 18, 0); break;
case TypeCode.Double: sql_md = new SqlMetaData(name, SqlDbType.Float); break;
case TypeCode.Empty: throw InvalidDataTypeCode(TypeCode.Empty);
case TypeCode.Int16: sql_md = new SqlMetaData(name, SqlDbType.SmallInt); break;
case TypeCode.Int32: sql_md = new SqlMetaData(name, SqlDbType.Int); break;
case TypeCode.Int64: sql_md = new SqlMetaData(name, SqlDbType.BigInt); break;
case TypeCode.SByte: throw InvalidDataTypeCode(TypeCode.SByte);
case TypeCode.Single: sql_md = new SqlMetaData(name, SqlDbType.Real); break;
case TypeCode.String: sql_md = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
break;
case TypeCode.UInt16: throw InvalidDataTypeCode(TypeCode.UInt16);
case TypeCode.UInt32: throw InvalidDataTypeCode(TypeCode.UInt32);
case TypeCode.UInt64: throw InvalidDataTypeCode(TypeCode.UInt64);
case TypeCode.Object:
sql_md = SqlMetaDataFromObjectColumn(name, column, clrType);
if (sql_md == null)
{
// Unknown type, try to treat it as string;
sql_md = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
coerceToString = true;
}
break;
default: throw UnknownDataType(clrType);
}
return sql_md;
}
public virtual void SetValue(int ordinal, object value)
{
EnsureSubclassOverride();
SqlMetaData metaData = GetSqlMetaData(ordinal);
ExtendedClrTypeCode typeCode = MetaDataUtilsSmi.DetermineExtendedTypeCodeForUseWithSqlDbType(
metaData.SqlDbType, false /* isMultiValued */, value, metaData.Type, SmiVersion);
if (ExtendedClrTypeCode.Invalid == typeCode)
{
throw ADP.InvalidCast();
}
if (SmiVersion >= SmiContextFactory.KatmaiVersion)
{
ValueUtilsSmi.SetCompatibleValueV200(_eventSink, _recordBuffer, ordinal, GetSmiMetaData(ordinal), value, typeCode, 0, 0, null);
}
else
{
ValueUtilsSmi.SetCompatibleValue(_eventSink, _recordBuffer, ordinal, GetSmiMetaData(ordinal), value, typeCode, 0);
}
}
public static SqlMetaData InferFromValue(object value, String name)
{
if (value == null)
throw ADP.ArgumentNull("value");
SqlMetaData smd;
if (value is Boolean) smd = new SqlMetaData(name, SqlDbType.Bit);
else if (value is Byte) smd = new SqlMetaData(name, SqlDbType.TinyInt);
else if (value is Char) smd = new SqlMetaData(name, SqlDbType.NVarChar, 1);
else if (value is DateTime) smd = new SqlMetaData(name, SqlDbType.DateTime);
else if (value is DBNull) throw ADP.InvalidDataType("DBNull");
else if (value is Decimal)
{
// use logic inside SqlDecimal to infer precision and scale.
SqlDecimal sd = new SqlDecimal((Decimal)value);
smd = new SqlMetaData(name, SqlDbType.Decimal, sd.Precision, sd.Scale);
}
else if (value is Double) smd = new SqlMetaData(name, SqlDbType.Float);
else if (value is Int16) smd = new SqlMetaData(name, SqlDbType.SmallInt);
else if (value is Int32) smd = new SqlMetaData(name, SqlDbType.Int);
else if (value is Int64) smd = new SqlMetaData(name, SqlDbType.BigInt);
else if (value is SByte) throw ADP.InvalidDataType("SByte");
else if (value is Single) smd = new SqlMetaData(name, SqlDbType.Real);
else if (value is String)
{
long maxLen = ((String)value).Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxUnicode < maxLen)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen);
}
else if (value is UInt16) throw ADP.InvalidDataType("UInt16");
else if (value is UInt32) throw ADP.InvalidDataType("UInt32");
else if (value is UInt64) throw ADP.InvalidDataType("UInt64");
else if (value is System.Byte[])
{
long maxLen = ((System.Byte[])value).Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxBinary < maxLen)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.VarBinary, maxLen);
}
else if (value is System.Char[])
{
long maxLen = ((System.Char[])value).Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxUnicode < maxLen)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen);
}
else if (value is System.Guid)
smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
else if (value is System.Object)
smd = new SqlMetaData(name, SqlDbType.Variant);
else if (value is SqlBinary)
{
long maxLen;
SqlBinary sb = ((SqlBinary)value);
if (!sb.IsNull)
{
maxLen = sb.Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxBinary < maxLen)
maxLen = Max;
}
else
maxLen = sxm_rgDefaults[(int)SqlDbType.VarBinary].MaxLength;
smd = new SqlMetaData(name, SqlDbType.VarBinary, maxLen);
}
else if (value is SqlBoolean)
smd = new SqlMetaData(name, SqlDbType.Bit);
else if (value is SqlByte)
smd = new SqlMetaData(name, SqlDbType.TinyInt);
else if (value is SqlDateTime)
smd = new SqlMetaData(name, SqlDbType.DateTime);
else if (value is SqlDouble)
smd = new SqlMetaData(name, SqlDbType.Float);
else if (value is SqlGuid)
smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
else if (value is SqlInt16)
smd = new SqlMetaData(name, SqlDbType.SmallInt);
else if (value is SqlInt32)
smd = new SqlMetaData(name, SqlDbType.Int);
else if (value is SqlInt64)
smd = new SqlMetaData(name, SqlDbType.BigInt);
else if (value is SqlMoney)
smd = new SqlMetaData(name, SqlDbType.Money);
else if (value is SqlDecimal)
{
byte bPrec;
byte scale;
SqlDecimal sd = (SqlDecimal)value;
if (!sd.IsNull)
{
bPrec = sd.Precision;
scale = sd.Scale;
}
else
{
bPrec = sxm_rgDefaults[(int)SqlDbType.Decimal].Precision;
scale = sxm_rgDefaults[(int)SqlDbType.Decimal].Scale;
}
smd = new SqlMetaData(name, SqlDbType.Decimal, bPrec, scale);
}
else if (value is SqlSingle)
smd = new SqlMetaData(name, SqlDbType.Real);
else if (value is SqlString)
{
SqlString ss = (SqlString)value;
if (!ss.IsNull)
{
long maxLen = ss.Value.Length;
if (maxLen < 1) maxLen = 1;
if (maxLen > x_lServerMaxUnicode)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen, ss.LCID, ss.SqlCompareOptions);
}
else
{
smd = new SqlMetaData(name, SqlDbType.NVarChar, sxm_rgDefaults[(int)SqlDbType.NVarChar].MaxLength);
}
}
else if (value is SqlChars)
{
long maxLen;
SqlChars sch = (SqlChars)value;
if (!sch.IsNull)
{
maxLen = sch.Length;
if (maxLen < 1) maxLen = 1;
if (maxLen > x_lServerMaxUnicode)
maxLen = Max;
}
else
maxLen = sxm_rgDefaults[(int)SqlDbType.NVarChar].MaxLength;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen);
}
else if (value is SqlBytes)
{
long maxLen;
SqlBytes sb = (SqlBytes)value;
if (!sb.IsNull)
{
maxLen = sb.Length;
if (maxLen < 1) maxLen = 1;
else if (x_lServerMaxBinary < maxLen) maxLen = Max;
}
else
maxLen = sxm_rgDefaults[(int)SqlDbType.VarBinary].MaxLength;
smd = new SqlMetaData(name, SqlDbType.VarBinary, maxLen);
}
else if (value is SqlXml)
smd = new SqlMetaData(name, SqlDbType.Xml);
else if (value is TimeSpan)
smd = new SqlMetaData(name, SqlDbType.Time, 0, InferScaleFromTimeTicks(((TimeSpan)value).Ticks));
else if (value is DateTimeOffset)
smd = new SqlMetaData(name, SqlDbType.DateTimeOffset, 0, InferScaleFromTimeTicks(((DateTimeOffset)value).Ticks));
else
throw ADP.UnknownDataType(value.GetType());
return smd;
}
/// <summary>
/// Returns metadata for given dictionary object
/// </summary>
/// <remarks>
/// Each key in dictionary object represents a column
/// </remarks>
/// <param name="source">Dictionary to be used for metada</param>
/// <returns>List of metada (columns)</returns>
private SqlMetaData[] GetMetada(IDictionary<string, object> source)
{
SqlMetaData[] meta = new SqlMetaData[source.Keys.Count];
for (int i = 0; i < source.Keys.Count; i++)
{
var keyName = source.Keys.ElementAt(i);
meta[i] = GetMetada(keyName, source[keyName]);
}
return meta;
}
private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)
{
SqlMetaData sql_md = null;
if (clrType == typeof(System.Byte[]) || clrType == typeof(SqlBinary) || clrType == typeof(SqlBytes) ||
clrType == typeof(System.Char[]) || clrType == typeof(SqlString) || clrType == typeof(SqlChars))
sql_md = new SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength);
else if (clrType == typeof(System.Guid))
sql_md = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
else if (clrType == typeof(System.Object))
sql_md = new SqlMetaData(name, SqlDbType.Variant);
else if (clrType == typeof(SqlBoolean))
sql_md = new SqlMetaData(name, SqlDbType.Bit);
else if (clrType == typeof(SqlByte))
sql_md = new SqlMetaData(name, SqlDbType.TinyInt);
else if (clrType == typeof(SqlDateTime))
sql_md = new SqlMetaData(name, SqlDbType.DateTime);
else if (clrType == typeof(SqlDouble))
sql_md = new SqlMetaData(name, SqlDbType.Float);
else if (clrType == typeof(SqlGuid))
sql_md = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
else if (clrType == typeof(SqlInt16))
sql_md = new SqlMetaData(name, SqlDbType.SmallInt);
else if (clrType == typeof(SqlInt32))
sql_md = new SqlMetaData(name, SqlDbType.Int);
else if (clrType == typeof(SqlInt64))
sql_md = new SqlMetaData(name, SqlDbType.BigInt);
else if (clrType == typeof(SqlMoney))
sql_md = new SqlMetaData(name, SqlDbType.Money);
else if (clrType == typeof(SqlDecimal))
sql_md = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);
else if (clrType == typeof(SqlSingle))
sql_md = new SqlMetaData(name, SqlDbType.Real);
else if (clrType == typeof(SqlXml))
sql_md = new SqlMetaData(name, SqlDbType.Xml);
else
sql_md = null;
return sql_md;
}
public static SqlMetaData InferFromValue(object value, string name)
{
if (name == null)
{
throw new ArgumentNullException("name can not be null");
}
if (value == null)
{
throw new ArgumentException("value can not be null");
}
SqlMetaData sqlMetaData = null;
switch (value.GetType().ToString())
{
case "System.Boolean":
sqlMetaData = new SqlMetaData(name, SqlDbType.Bit);
break;
case "System.Byte":
sqlMetaData = new SqlMetaData(name, SqlDbType.Binary);
break;
case "System.Byte[]":
sqlMetaData = new SqlMetaData(name, SqlDbType.VarBinary);
break;
case "System.Char":
sqlMetaData = new SqlMetaData(name, SqlDbType.Char);
break;
case "System.Char[]":
sqlMetaData = new SqlMetaData(name, SqlDbType.VarChar);
break;
case "System.DateTime":
sqlMetaData = new SqlMetaData(name, SqlDbType.DateTime);
break;
case "System.Decimal":
sqlMetaData = new SqlMetaData(name, SqlDbType.Decimal);
break;
case "System.Double":
sqlMetaData = new SqlMetaData(name, SqlDbType.Float);
break;
case "System.Guid":
sqlMetaData = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
break;
case "System.Int16":
sqlMetaData = new SqlMetaData(name, SqlDbType.SmallInt);
break;
case "System.Int32":
sqlMetaData = new SqlMetaData(name, SqlDbType.Int);
break;
case "System.Int64":
sqlMetaData = new SqlMetaData(name, SqlDbType.BigInt);
break;
case "System.Single":
sqlMetaData = new SqlMetaData(name, SqlDbType.Real);
break;
case "System.String":
sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar);
break;
case "System.Object":
default:
sqlMetaData = new SqlMetaData(name, SqlDbType.Variant);
break;
}
return(sqlMetaData);
}
internal static bool IsCompatible(SmiMetaData firstMd, SqlMetaData secondMd)
{
return (((((firstMd.SqlDbType == secondMd.SqlDbType) && (firstMd.MaxLength == secondMd.MaxLength)) && ((firstMd.Precision == secondMd.Precision) && (firstMd.Scale == secondMd.Scale))) && (((firstMd.CompareOptions == secondMd.CompareOptions) && (firstMd.LocaleId == secondMd.LocaleId)) && ((firstMd.Type == secondMd.Type) && (firstMd.SqlDbType != SqlDbType.Structured)))) && !firstMd.IsMultiValued);
}
public static SqlMetaData InferFromValue(object value, String name) {
if (value == null)
throw ADP.ArgumentNull("value");
SqlMetaData smd = null;
Type dataType = value.GetType();
switch (Type.GetTypeCode(dataType))
{
case TypeCode.Boolean: smd = new SqlMetaData(name, SqlDbType.Bit); break;
case TypeCode.Byte: smd = new SqlMetaData(name, SqlDbType.TinyInt); break;
case TypeCode.Char: smd = new SqlMetaData(name, SqlDbType.NVarChar, 1 ); break;
case TypeCode.DateTime: smd = new SqlMetaData(name, SqlDbType.DateTime); break;
case TypeCode.DBNull: throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Decimal:
{ // Add brackets in order to contain scope declare local variable "sd"
// use logic inside SqlDecimal to infer precision and scale.
SqlDecimal sd = new SqlDecimal((Decimal)value);
smd = new SqlMetaData(name, SqlDbType.Decimal, sd.Precision, sd.Scale);
}
break;
case TypeCode.Double: smd = new SqlMetaData(name, SqlDbType.Float); break;
case TypeCode.Empty: throw ADP.InvalidDataType(TypeCode.Empty);
case TypeCode.Int16: smd = new SqlMetaData(name, SqlDbType.SmallInt); break;
case TypeCode.Int32: smd = new SqlMetaData(name, SqlDbType.Int); break;
case TypeCode.Int64: smd = new SqlMetaData(name, SqlDbType.BigInt); break;
case TypeCode.SByte: throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Single: smd = new SqlMetaData(name, SqlDbType.Real); break;
case TypeCode.String:
{
long maxLen = ((String)value).Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxUnicode < maxLen)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen);
}
break;
case TypeCode.UInt16: throw ADP.InvalidDataType(TypeCode.UInt16);
case TypeCode.UInt32: throw ADP.InvalidDataType(TypeCode.UInt32);
case TypeCode.UInt64: throw ADP.InvalidDataType(TypeCode.UInt64);
case TypeCode.Object:
if (dataType == typeof(System.Byte[]))
{
long maxLen = ((System.Byte[])value).Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxBinary < maxLen)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.VarBinary, maxLen);
}
else if (dataType == typeof(System.Char[]))
{
long maxLen = ((System.Char[])value).Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxUnicode < maxLen)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen);
}
else if (dataType == typeof(System.Guid))
smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
else if (dataType == typeof(System.Object))
smd = new SqlMetaData(name, SqlDbType.Variant);
else if (dataType == typeof(SqlBinary))
{
long maxLen;
SqlBinary sb = ((SqlBinary) value);
if (!sb.IsNull)
{
maxLen = sb.Length;
if (maxLen < 1) maxLen = 1;
if (x_lServerMaxBinary < maxLen)
maxLen = Max;
}
else
maxLen = sxm_rgDefaults[(int)SqlDbType.VarBinary].MaxLength;
smd = new SqlMetaData(name, SqlDbType.VarBinary, maxLen);
}
else if (dataType == typeof(SqlBoolean))
smd = new SqlMetaData(name, SqlDbType.Bit);
else if (dataType == typeof(SqlByte))
smd = new SqlMetaData(name, SqlDbType.TinyInt);
else if (dataType == typeof(SqlDateTime))
smd = new SqlMetaData(name, SqlDbType.DateTime);
else if (dataType == typeof(SqlDouble))
smd = new SqlMetaData(name, SqlDbType.Float);
else if (dataType == typeof(SqlGuid))
smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
else if (dataType == typeof(SqlInt16))
smd = new SqlMetaData(name, SqlDbType.SmallInt);
else if (dataType == typeof(SqlInt32))
smd = new SqlMetaData(name, SqlDbType.Int);
else if (dataType == typeof(SqlInt64))
smd = new SqlMetaData(name, SqlDbType.BigInt);
else if (dataType == typeof(SqlMoney))
smd = new SqlMetaData(name, SqlDbType.Money);
else if (dataType == typeof(SqlDecimal))
{
byte bPrec;
byte scale;
SqlDecimal sd = (SqlDecimal) value;
if (!sd.IsNull)
{
bPrec = sd.Precision;
scale = sd.Scale;
}
else
{
bPrec = sxm_rgDefaults[(int)SqlDbType.Decimal].Precision;
scale = sxm_rgDefaults[(int)SqlDbType.Decimal].Scale;
}
smd = new SqlMetaData(name, SqlDbType.Decimal, bPrec, scale);
}
else if (dataType == typeof(SqlSingle))
smd = new SqlMetaData(name, SqlDbType.Real);
else if (dataType == typeof(SqlString))
{
SqlString ss = (SqlString)value;
if (!ss.IsNull)
{
long maxLen = ss.Value.Length;
if (maxLen < 1) maxLen = 1;
if (maxLen > x_lServerMaxUnicode)
maxLen = Max;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen, ss.LCID, ss.SqlCompareOptions);
}
else
{
smd = new SqlMetaData(name, SqlDbType.NVarChar, sxm_rgDefaults[(int)SqlDbType.NVarChar].MaxLength);
}
}
else if (dataType == typeof(SqlChars))
{
long maxLen;
SqlChars sch = (SqlChars)value;
if (!sch.IsNull)
{
maxLen = sch.Length;
if (maxLen < 1) maxLen = 1;
if (maxLen > x_lServerMaxUnicode)
maxLen = Max;
}
else
maxLen = sxm_rgDefaults[(int)SqlDbType.NVarChar].MaxLength;
smd = new SqlMetaData(name, SqlDbType.NVarChar, maxLen);
}
else if (dataType == typeof(SqlBytes))
{
long maxLen;
SqlBytes sb = (SqlBytes)value;
if (!sb.IsNull)
{
maxLen = sb.Length;
if (maxLen < 1) maxLen = 1;
else if (x_lServerMaxBinary < maxLen) maxLen = Max;
}
else
maxLen = sxm_rgDefaults[(int)SqlDbType.VarBinary].MaxLength;
smd = new SqlMetaData(name, SqlDbType.VarBinary, maxLen);
}
else if (dataType == typeof(SqlXml))
smd = new SqlMetaData(name, SqlDbType.Xml);
else if (dataType == typeof(TimeSpan))
smd = new SqlMetaData(name, SqlDbType.Time, 0, InferScaleFromTimeTicks(((TimeSpan)value).Ticks));
else if (dataType == typeof(DateTimeOffset))
smd = new SqlMetaData(name, SqlDbType.DateTimeOffset, 0, InferScaleFromTimeTicks(((DateTimeOffset)value).Ticks));
else
throw ADP.UnknownDataType(dataType);
break;
default: throw ADP.UnknownDataTypeCode(dataType, Type.GetTypeCode(dataType));
}
return smd;
}
private static void SendTable(SqlDataReader reader, TRowSetMap Map)
{
//SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
DataTable LDataTable = reader.GetSchemaTable();
SqlDataRecord WriteRecord;
List<TFieldAlias> Fields = new List<TFieldAlias>();
TFieldAlias Field;
string FieldName;
int FieldCount = reader.FieldCount, WriteFieldCount = 0;
int i;
SqlMetaData[] WriteFields;
if(Map.Fields.Length > 0)
{
WriteFields = new SqlMetaData[0];
foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
{
i = FieldMap.IndexOf('=');
if(i >= 0)
{
Field.Name = FieldMap.Substring(0, i);
FieldName = FieldMap.Substring(i + 1);
}
else
{
Field.Name = FieldMap;
FieldName = FieldMap;
}
for(i = 0; i < FieldCount; i++)
{
if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
break;
}
if((i < 0) || (i >= FieldCount))
throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
Field.FieldIndex = i;
Fields.Add(Field);
Array.Resize(ref WriteFields, ++WriteFieldCount);
//WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
}
}
else
{
WriteFields = new SqlMetaData[FieldCount];
for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
}
WriteRecord = new SqlDataRecord(WriteFields);
try
{
SqlContext.Pipe.SendResultsStart(WriteRecord);
Object[] values = new Object[FieldCount];
while (reader.Read())
{
reader.GetValues(values);
if(Map.Fields.Length > 0)
{
for(i = 0; i < WriteFieldCount; i++)
WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
}
else
{
WriteRecord.SetValues(values);
}
SqlContext.Pipe.SendResultsRow(WriteRecord);
}
}
finally
{
SqlContext.Pipe.SendResultsEnd();
}
}
private SqlDataRecord GetDbRecord(SqlMetaData[] metadata, object value)
{
SqlDataRecord record = new SqlDataRecord(metadata);
SetDbRecord(record, value, 0);
return record;
}
// Convert SqlMetaData instance to an SmiExtendedMetaData instance.
internal static SmiExtendedMetaData SqlMetaDataToSmiExtendedMetaData(SqlMetaData source)
{
// now map everything across to the extended metadata object
string typeSpecificNamePart1 = null;
string typeSpecificNamePart2 = null;
string typeSpecificNamePart3 = null;
if (SqlDbType.Xml == source.SqlDbType)
{
typeSpecificNamePart1 = source.XmlSchemaCollectionDatabase;
typeSpecificNamePart2 = source.XmlSchemaCollectionOwningSchema;
typeSpecificNamePart3 = source.XmlSchemaCollectionName;
}
else if (SqlDbType.Udt == source.SqlDbType)
{
throw ADP.DbTypeNotSupported(SqlDbType.Udt.ToString());
}
return new SmiExtendedMetaData(source.SqlDbType,
source.MaxLength,
source.Precision,
source.Scale,
source.LocaleId,
source.CompareOptions,
source.Name,
typeSpecificNamePart1,
typeSpecificNamePart2,
typeSpecificNamePart3);
}
// Convert SqlMetaData instance to an SmiExtendedMetaData instance.
internal static SmiExtendedMetaData SqlMetaDataToSmiExtendedMetaData(SqlMetaData source)
{
// now map everything across to the extended metadata object
string typeSpecificNamePart1 = null;
string typeSpecificNamePart2 = null;
string typeSpecificNamePart3 = null;
if (SqlDbType.Xml == source.SqlDbType)
{
typeSpecificNamePart1 = source.XmlSchemaCollectionDatabase;
typeSpecificNamePart2 = source.XmlSchemaCollectionOwningSchema;
typeSpecificNamePart3 = source.XmlSchemaCollectionName;
}
else if (SqlDbType.Udt == source.SqlDbType)
{
// Split the input name. UdtTypeName is specified as single 3 part name.
// NOTE: ParseUdtTypeName throws if format is incorrect
string typeName = source.ServerTypeName;
if (null != typeName)
{
string[] names = SqlParameter.ParseTypeName(typeName, true /* isUdtTypeName */);
if (1 == names.Length)
{
typeSpecificNamePart3 = names[0];
}
else if (2 == names.Length)
{
typeSpecificNamePart2 = names[0];
typeSpecificNamePart3 = names[1];
}
else if (3 == names.Length)
{
typeSpecificNamePart1 = names[0];
typeSpecificNamePart2 = names[1];
typeSpecificNamePart3 = names[2];
}
else
{
throw ADP.ArgumentOutOfRange(nameof(typeName));
}
if ((!string.IsNullOrEmpty(typeSpecificNamePart1) && TdsEnums.MAX_SERVERNAME < typeSpecificNamePart1.Length) ||
(!string.IsNullOrEmpty(typeSpecificNamePart2) && TdsEnums.MAX_SERVERNAME < typeSpecificNamePart2.Length) ||
(!string.IsNullOrEmpty(typeSpecificNamePart3) && TdsEnums.MAX_SERVERNAME < typeSpecificNamePart3.Length))
{
throw ADP.ArgumentOutOfRange(nameof(typeName));
}
}
}
return(new SmiExtendedMetaData(source.SqlDbType,
source.MaxLength,
source.Precision,
source.Scale,
source.LocaleId,
source.CompareOptions,
null,
source.Name,
typeSpecificNamePart1,
typeSpecificNamePart2,
typeSpecificNamePart3));
}
private static IEnumerable<SqlDataRecord> BuildTableValueParameter(IEnumerable<string> list, SqlMetaData[] tvpDefinition)
{
var includeList = new List<SqlDataRecord>();
foreach (var item in list)
{
var rec = new SqlDataRecord(tvpDefinition);
rec.SetValue(0, item);
includeList.Add(rec);
}
return includeList;
}
internal static SmiExtendedMetaData SqlMetaDataToSmiExtendedMetaData(SqlMetaData source)
{
string xmlSchemaCollectionName = null;
string xmlSchemaCollectionOwningSchema = null;
string xmlSchemaCollectionDatabase = null;
if (SqlDbType.Xml == source.SqlDbType)
{
xmlSchemaCollectionDatabase = source.XmlSchemaCollectionDatabase;
xmlSchemaCollectionOwningSchema = source.XmlSchemaCollectionOwningSchema;
xmlSchemaCollectionName = source.XmlSchemaCollectionName;
}
else if (SqlDbType.Udt == source.SqlDbType)
{
string serverTypeName = source.ServerTypeName;
if (serverTypeName != null)
{
string[] strArray = SqlParameter.ParseTypeName(serverTypeName, true);
if (1 == strArray.Length)
{
xmlSchemaCollectionName = strArray[0];
}
else if (2 == strArray.Length)
{
xmlSchemaCollectionOwningSchema = strArray[0];
xmlSchemaCollectionName = strArray[1];
}
else
{
if (3 != strArray.Length)
{
throw ADP.ArgumentOutOfRange("typeName");
}
xmlSchemaCollectionDatabase = strArray[0];
xmlSchemaCollectionOwningSchema = strArray[1];
xmlSchemaCollectionName = strArray[2];
}
if (((!ADP.IsEmpty(xmlSchemaCollectionDatabase) && (0xff < xmlSchemaCollectionDatabase.Length)) || (!ADP.IsEmpty(xmlSchemaCollectionOwningSchema) && (0xff < xmlSchemaCollectionOwningSchema.Length))) || (!ADP.IsEmpty(xmlSchemaCollectionName) && (0xff < xmlSchemaCollectionName.Length)))
{
throw ADP.ArgumentOutOfRange("typeName");
}
}
}
return new SmiExtendedMetaData(source.SqlDbType, source.MaxLength, source.Precision, source.Scale, source.LocaleId, source.CompareOptions, source.Type, source.Name, xmlSchemaCollectionDatabase, xmlSchemaCollectionOwningSchema, xmlSchemaCollectionName);
}
public static SqlMetaData InferFromValue (object value, string name)
{
if (name == null)
throw new ArgumentNullException ("name can not be null");
if (value == null)
throw new ArgumentException ("value can not be null");
SqlMetaData sqlMetaData = null;
switch (value.GetType ().ToString ()) {
case "System.Boolean":
sqlMetaData = new SqlMetaData (name, SqlDbType.Bit);
break;
case "System.Byte":
sqlMetaData = new SqlMetaData (name, SqlDbType.Binary);
break;
case "System.Byte[]":
sqlMetaData = new SqlMetaData (name, SqlDbType.VarBinary);
break;
case "System.Char":
sqlMetaData = new SqlMetaData (name, SqlDbType.Char);
break;
case "System.Char[]":
sqlMetaData = new SqlMetaData (name, SqlDbType.VarChar);
break;
case "System.DateTime":
sqlMetaData = new SqlMetaData (name, SqlDbType.DateTime);
break;
case "System.Decimal":
sqlMetaData = new SqlMetaData (name, SqlDbType.Decimal);
break;
case "System.Double":
sqlMetaData = new SqlMetaData (name, SqlDbType.Float);
break;
case "System.Guid":
sqlMetaData = new SqlMetaData (name, SqlDbType.UniqueIdentifier);
break;
case "System.Int16":
sqlMetaData = new SqlMetaData (name, SqlDbType.SmallInt);
break;
case "System.Int32":
sqlMetaData = new SqlMetaData (name, SqlDbType.Int);
break;
case "System.Int64":
sqlMetaData = new SqlMetaData (name, SqlDbType.BigInt);
break;
case "System.Single":
sqlMetaData = new SqlMetaData (name, SqlDbType.Real);
break;
case "System.String":
sqlMetaData = new SqlMetaData (name, SqlDbType.NVarChar);
break;
case "System.Object":
default:
sqlMetaData = new SqlMetaData (name, SqlDbType.Variant);
break;
}
return sqlMetaData;
}
// compare SmiMetaData to SqlMetaData and determine if they are compatible.
static internal bool IsCompatible(SmiMetaData firstMd, SqlMetaData secondMd)
{
return firstMd.SqlDbType == secondMd.SqlDbType &&
firstMd.MaxLength == secondMd.MaxLength &&
firstMd.Precision == secondMd.Precision &&
firstMd.Scale == secondMd.Scale &&
firstMd.CompareOptions == secondMd.CompareOptions &&
firstMd.LocaleId == secondMd.LocaleId &&
firstMd.SqlDbType != SqlDbType.Structured && // SqlMetaData doesn't support Structured types
!firstMd.IsMultiValued; // SqlMetaData doesn't have a "multivalued" option
}
public static void xp_getfiledetails(string filePath)
{
//pipe to sql server
SqlPipe pipe = SqlContext.Pipe;
if (File.Exists(filePath))
{
//try and open the requested file
FileInfo file;
try
{
file = new FileInfo(filePath);
}
catch (Exception e)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch
// ReSharper restore EmptyGeneralCatchClause
{ }
//if I don't re-throw here I get errors below
throw (e);
}
//Build retrun record
SqlMetaData alternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);
SqlMetaData size = new SqlMetaData("Size", SqlDbType.BigInt);
SqlMetaData creationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);
SqlMetaData creationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);
SqlMetaData lastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);
SqlMetaData lastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);
SqlMetaData lastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);
SqlMetaData lastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);
SqlMetaData attributes = new SqlMetaData("Attributes", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(new[] {
alternateName,
size,
creationDate,
creationTime,
lastWrittenDate,
lastWrittenTime,
lastAccessedDate,
lastAccessedTime,
attributes});
//try to add data to the retrun record
try
{
record.SetString(0, file.Name);
record.SetInt64(1, file.Length);
record.SetString(2, file.CreationTime.ToString("yyyyMMdd"));
record.SetString(3, file.CreationTime.ToString("HHmmss"));
record.SetString(4, file.LastWriteTime.ToString("yyyyMMdd"));
record.SetString(5, file.LastWriteTime.ToString("HHmmss"));
record.SetString(6, file.LastAccessTime.ToString("yyyyMMdd"));
record.SetString(7, file.LastAccessTime.ToString("HHmmss"));
record.SetInt32(8, (int)file.Attributes);
}
catch (Exception)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
//send record back to sql server
try
{
pipe.Send(record);
}
catch (Exception e)
{
throw (e);
}
}
else
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
}
internal static bool IsCompatible(SmiMetaData firstMd, SqlMetaData secondMd)
{
return(((((firstMd.SqlDbType == secondMd.SqlDbType) && (firstMd.MaxLength == secondMd.MaxLength)) && ((firstMd.Precision == secondMd.Precision) && (firstMd.Scale == secondMd.Scale))) && (((firstMd.CompareOptions == secondMd.CompareOptions) && (firstMd.LocaleId == secondMd.LocaleId)) && ((firstMd.Type == secondMd.Type) && (firstMd.SqlDbType != SqlDbType.Structured)))) && !firstMd.IsMultiValued);
}
private SqlDataRecord GetDbRecord(SqlMetaData[] metadata, IDictionary<string, object> source)
{
SqlDataRecord record = new SqlDataRecord(metadata);
foreach (var item in source)
{
for (int i = 0; i < metadata.Length; i++)
{
if (item.Key == metadata[i].Name)
{
SetDbRecord(record, item.Value, i);
break;
}
}
}
return record;
}
private DataTable GetSkylineTable(String strQuery, String strOperators, int numberOfRecords, bool isIndependent, string strConnection, string strProvider, int sortType, int upToLevel)
{
ArrayList resultCollection = new ArrayList();
ArrayList resultstringCollection = new ArrayList();
string[] operators = strOperators.Split(';');
DataTable dtResult = new DataTable();
DbProviderFactory factory = DbProviderFactories.GetFactory(strProvider);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection();
if (connection != null)
{
connection.ConnectionString = strConnection;
try
{
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
selectCommand.CommandText = strQuery;
if (dap != null)
{
dap.SelectCommand = selectCommand;
DataTable dt = new DataTable();
dap.Fill(dt);
//trees erstellen mit n nodes (n = anzahl tupels)
//int[] levels = new int[dt.Rows.Count];
List<int> levels = new List<int>();
// Build our record schema
List<SqlMetaData> outputColumns = Helper.BuildRecordSchema(dt, operators, dtResult);
//Add Level column
SqlMetaData outputColumnLevel = new SqlMetaData("Level", SqlDbType.Int);
outputColumns.Add(outputColumnLevel);
dtResult.Columns.Add("level", typeof(int));
SqlDataRecord record = new SqlDataRecord(outputColumns.ToArray());
if (isIndependent == false)
{
SqlContext.Pipe.SendResultsStart(record);
}
int iMaxLevel = 0;
List<object[]> listObjects = Helper.GetItemArraysAsList(dt);
foreach (object[] dbValuesObject in listObjects)
{
//Check if window list is empty
if (resultCollection.Count == 0)
{
// Build our SqlDataRecord and start the results
levels.Add(0);
iMaxLevel = 0;
AddToWindow(dbValuesObject, operators, resultCollection, resultstringCollection, record, isIndependent, levels[levels.Count - 1], dtResult);
}
else
{
//Insert the new record to the tree
bool bFound = false;
//Start wie level 0 nodes (until uptolevels or maximum levels)
for (int iLevel = 0; iLevel <= iMaxLevel && iLevel < upToLevel; iLevel++)
{
bool isDominated = false;
for (int i = 0; i < resultCollection.Count; i++)
{
if (levels[i] == iLevel)
{
long?[] result = (long?[])resultCollection[i];
string[] strResult = (string[])resultstringCollection[i];
//Dominanz
/*if (Helper.IsTupleDominated(operators, result, strResult, dbValuesObject))
{
//Dominated in this level. Next level
isDominated = true;
break;
}*/
}
}
//Check if the record is dominated in this level
if (isDominated == false)
{
levels.Add(iLevel);
bFound = true;
break;
}
}
if (bFound == false)
{
iMaxLevel++;
if (iMaxLevel < upToLevel)
{
levels.Add(iMaxLevel);
AddToWindow(dbValuesObject, operators, resultCollection, resultstringCollection, record, isIndependent, levels[levels.Count - 1], dtResult);
}
}
else
{
AddToWindow(dbValuesObject, operators, resultCollection, resultstringCollection, record, isIndependent, levels[levels.Count - 1], dtResult);
}
}
}
}
if (isIndependent == false)
{
SqlContext.Pipe.SendResultsEnd();
}
}
catch (Exception ex)
{
//Pack Errormessage in a SQL and return the result
string strError = "Fehler in prefSQL_MultipleSkylineBNL: ";
strError += ex.Message;
if (isIndependent)
{
Debug.WriteLine(strError);
}
else
{
SqlContext.Pipe.Send(strError);
}
}
finally
{
connection.Close();
}
}
return dtResult;
}
/// <summary>SQL meta data from column.</summary>
/// <exception cref="Exception">Throw an exception when an invalid or unsupported type is found.</exception>
/// <param name="column">The column.</param>
/// <param name="useToString">[out] The coerce to string.</param>
/// <returns>A SqlMetaData.</returns>
private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool useToString)
{
useToString = false;
SqlMetaData sqlMetaData;
var clrType = column.DataType;
var typeCode = Type.GetTypeCode(clrType);
var name = column.ColumnName;
switch (typeCode)
{
case TypeCode.Boolean:
sqlMetaData = new SqlMetaData(name, SqlDbType.Bit);
break;
case TypeCode.Byte:
sqlMetaData = new SqlMetaData(name, SqlDbType.TinyInt);
break;
case TypeCode.Char:
sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar, 1);
break;
case TypeCode.DateTime:
sqlMetaData = new SqlMetaData(name, SqlDbType.DateTime);
break;
case TypeCode.Decimal:
sqlMetaData = new SqlMetaData(name, SqlDbType.Decimal, 18, 0);
break;
case TypeCode.Double:
sqlMetaData = new SqlMetaData(name, SqlDbType.Float);
break;
case TypeCode.Int16:
sqlMetaData = new SqlMetaData(name, SqlDbType.SmallInt);
break;
case TypeCode.Int32:
sqlMetaData = new SqlMetaData(name, SqlDbType.Int);
break;
case TypeCode.Int64:
sqlMetaData = new SqlMetaData(name, SqlDbType.BigInt);
break;
case TypeCode.Single:
sqlMetaData = new SqlMetaData(name, SqlDbType.Real);
break;
case TypeCode.String:
sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
break;
case TypeCode.Object:
sqlMetaData = SqlMetaDataFromObjectColumn(name, column, clrType);
if (sqlMetaData == null)
{
sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
useToString = true;
}
break;
case TypeCode.DBNull:
case TypeCode.Empty:
case TypeCode.SByte:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
throw new Exception(string.Format(ExceptionMessage.Unsupported_SqlMetaData_TypeCode, typeCode));
default:
throw new Exception(string.Format(ExceptionMessage.Unsupported_SqlMetaData_Type, clrType));
}
return sqlMetaData;
}
public static void FileStringSplitToTable(
SqlString fileName, SqlString delimiter)
{
string[] delimiters = new string[] { delimiter.Value };
using (System.IO.StreamReader sr = new System.IO.StreamReader(new System.IO.FileStream(
fileName.Value, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read)))
{
string str;
SqlMetaData[] sqlMetadatas = null;
bool fFirst = true;
while ((str = sr.ReadLine()) != null)
{
string[] tokens = str.Split(delimiters, StringSplitOptions.None);
if (sqlMetadatas == null)
{
sqlMetadatas = new SqlMetaData[tokens.Length];
for (int iToken = 0; iToken < tokens.Length; iToken++)
{
sqlMetadatas[iToken] = new SqlMetaData("Field_" + iToken, System.Data.SqlDbType.NVarChar, -1);
}
}
#region Output fields
SqlDataRecord record = new SqlDataRecord(sqlMetadatas);
int i;
for (i = 0; i < tokens.Length; i++)
{
record.SetString(i, tokens[i]);
}
for (; i < sqlMetadatas.Length; i++) // add NULLs if need be.
{
record.SetDBNull(i);
}
if (fFirst)
{
SqlContext.Pipe.SendResultsStart(record);
fFirst = false;
}
SqlContext.Pipe.SendResultsRow(record);
#endregion
}
SqlContext.Pipe.SendResultsEnd();
}
}
private static void AddCompilationInfo(Type itemType)
{
var members = itemType.GetMembers();
var props =
members.Where(m => m.MemberType == MemberTypes.Property || m.MemberType == MemberTypes.Field)
.ToArray();
var coercionParameter = new SqlParameter();
var metaData = new SqlMetaData[props.Length];
for (int index = 0; index < props.Length; index++)
{
var prop = props[index];
if (index > 0)
{
coercionParameter.ResetDbType();
coercionParameter.ResetSqlDbType();
}
switch (props[index].MemberType)
{
case MemberTypes.Property:
coercionParameter.DbType = CommandManager.GetDbType(((PropertyInfo)prop).PropertyType) ??
DbType.Object;
break;
case MemberTypes.Field:
coercionParameter.DbType = CommandManager.GetDbType(((FieldInfo)prop).FieldType) ??
DbType.Object;
break;
}
metaData[index] = new SqlMetaData(prop.Name, coercionParameter.SqlDbType);
}
var bodyExpressions = new List<Expression>();
var metaDataParameter = Expression.Parameter(typeof(SqlMetaData[]), "metaData");
var itemParameter = Expression.Parameter(typeof(object), "item");
var castedItemParameter = Expression.Variable(itemType, "castedItem");
var recordVariable = Expression.Variable(typeof(SqlDataRecord), "record");
bodyExpressions.Add(Expression.Assign(recordVariable,
Expression.New(SqlDataRecordConstructorInfo, metaDataParameter)));
bodyExpressions.Add(Expression.Assign(castedItemParameter,
Expression.Convert(itemParameter, itemType)));
for (int index = 0; index < props.Length; index++)
{
var mi = props[index];
switch (mi.MemberType)
{
case MemberTypes.Property:
bodyExpressions.Add(Expression.Call(recordVariable, SetValueMethodInfo,
new Expression[]
{
Expression.Constant(index, typeof (int)),
Expression.Convert(Expression.Property(castedItemParameter, itemType, mi.Name),
typeof (object))
}));
break;
case MemberTypes.Field:
bodyExpressions.Add(Expression.Call(recordVariable, SetValueMethodInfo,
new Expression[]
{
Expression.Constant(index, typeof (int)),
Expression.Convert(Expression.Field(castedItemParameter, itemType, mi.Name),
typeof (object))
}));
break;
}
}
bodyExpressions.Add(recordVariable);
var body = Expression.Block(new[] { recordVariable, castedItemParameter }, bodyExpressions);
var lambda = Expression.Lambda<Func<SqlMetaData[], object, SqlDataRecord>>(body, metaDataParameter,
itemParameter);
CompiledFuncs.Add(itemType.AssemblyQualifiedName, new DelegateInfo { Func = lambda.Compile(), MetaData = metaData });
}
internal static SqlMetaData GetPartialLengthMetaData(SqlMetaData md)
{
if (md.IsPartialLength == true)
{
return md;
}
if (md.SqlDbType == SqlDbType.Xml)
ThrowInvalidType(); //Xml should always have IsPartialLength = true
if (md.SqlDbType == SqlDbType.NVarChar || md.SqlDbType == SqlDbType.VarChar ||
md.SqlDbType == SqlDbType.VarBinary)
{
SqlMetaData mdnew = new SqlMetaData(md.Name, md.SqlDbType, SqlMetaData.Max, 0, 0, md.LocaleId,
md.CompareOptions, null, null, null, true
);
return mdnew;
}
else
return md;
}
/// <summary>
/// Retrieves file from database as sql recordset in raw encrypted and compressed form
/// </summary>
/// <param name="fileId">file id</param>
public static void ExtractFileToRecordEncryptedCompressed(long fileId)
{
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader sqlReader;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
SqlCommand sqlCmd = new SqlCommand("RetrieveFile", cn) { CommandType = CommandType.StoredProcedure };
sqlCmd.Parameters.Add("@Id", SqlDbType.BigInt);
sqlCmd.Parameters[0].Value = fileId;
try
{
sqlReader = sqlCmd.ExecuteReader();
}
catch (Exception e)
{
pipe.Send("Failed to retrieve data");
pipe.Send(e.Message);
throw;
}
if (sqlReader != null)
if (sqlReader.HasRows)
{
sqlReader.Read();
string fileName = (string)sqlReader.GetSqlString(0);
int origionalFileSize = (int)sqlReader.GetSqlInt64(3);
int storageType = sqlReader.GetByte(8);
MemoryStream sqlDataStream = new MemoryStream(origionalFileSize);
const int length = 4096;
byte[] fileBlob = new byte[length];
int startPoint = 0;
long retval = sqlReader.GetBytes(10, startPoint, fileBlob, 0, length);
sqlDataStream.Write(fileBlob, 0, (int)retval);
while (retval == length)
{
startPoint += length;
retval = sqlReader.GetBytes(10, startPoint, fileBlob, 0, length);
sqlDataStream.Write(fileBlob, 0, (int)retval);
}
sqlReader.Close();
sqlDataStream.Seek(0, SeekOrigin.End);
SqlMetaData fileNameInfo = new SqlMetaData("OrigionalFileName", SqlDbType.NVarChar, 255);
SqlMetaData fileBlobInfo = new SqlMetaData("FileData", SqlDbType.Image);
// Create a new record with the column metadata.
SqlDataRecord record = new SqlDataRecord(new[]
{
fileNameInfo,
fileBlobInfo,
});
// Set the record fields.
record.SetString(0, fileName);
//if it is encrypted decrypt it.
if (storageType == 4)
{
record.SetBytes(1, 0, sqlDataStream.GetBuffer(), 0, (int)sqlDataStream.Position);
pipe.Send(record);
}
else
{
pipe.Send("File not encrypted and compressed");
}
}
else
{
pipe.Send("Invalid FileId");
}
}
}
