108 lines
3.9 KiB
C#
108 lines
3.9 KiB
C#
using Knoks.Framework.Extentions;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
|
|
namespace Knoks.Framework.DataAccess
|
|
{
|
|
public static class DbMetaData
|
|
{
|
|
private const string storedProcedureParamsQuery =
|
|
@"SELECT SPECIFIC_NAME,PARAMETER_MODE,PARAMETER_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
|
|
FROM INFORMATION_SCHEMA.PARAMETERS P
|
|
INNER JOIN sys.objects AS SO
|
|
ON object_name(SO.OBJECT_ID) = P.SPECIFIC_NAME
|
|
WHERE SO.type = 'P'
|
|
ORDER BY SPECIFIC_NAME ASC,ORDINAL_POSITION ASC";
|
|
|
|
public static Dictionary<string, List<ParamMetaData>> LoadParamMetaDataSet(string connectionString)
|
|
{
|
|
using (var conn = new SqlConnection(connectionString))
|
|
using (var cmd = new SqlCommand(storedProcedureParamsQuery, conn))
|
|
{
|
|
conn.Open();
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
var procParams = new List<ParamMetaData>();
|
|
var procName = string.Empty;
|
|
var resultParams = new Dictionary<string, List<ParamMetaData>>(StringComparer.OrdinalIgnoreCase);
|
|
|
|
while (reader.Read())
|
|
{
|
|
var param = ParamMetaData.LoadFromReader(reader);
|
|
|
|
if (param.ProcName != procName)
|
|
{
|
|
if (procName != string.Empty)
|
|
{
|
|
resultParams.Add(procName, procParams);
|
|
procParams = new List<ParamMetaData>();
|
|
}
|
|
|
|
procName = param.ProcName;
|
|
}
|
|
|
|
procParams.Add(param);
|
|
}
|
|
|
|
resultParams.Add(procName, procParams);
|
|
|
|
return resultParams;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public class ParamMetaData
|
|
{
|
|
public string ProcName { get; private set; }
|
|
public string ParamName { get; private set; }
|
|
public ParameterDirection Direction { get; private set; }
|
|
public SqlDbType DataType { get; private set; }
|
|
public int? Size { get; private set; }
|
|
|
|
public SqlParameter GetSqlParameter(object value)
|
|
{
|
|
return new SqlParameter(ParamName, DataType)
|
|
{
|
|
Value = value ?? DBNull.Value,
|
|
Size = Size ?? 0,
|
|
Direction = Direction
|
|
};
|
|
}
|
|
|
|
public static ParamMetaData LoadFromReader(IDataReader reader)
|
|
{
|
|
return new ParamMetaData
|
|
{
|
|
ProcName = reader["SPECIFIC_NAME"].ToString(),
|
|
ParamName = reader["PARAMETER_NAME"].ToString(),
|
|
Direction = ConvertToDirection(reader["PARAMETER_MODE"].ToString()),
|
|
DataType = ConvertToDataType(reader["DATA_TYPE"].ToString()),
|
|
Size = Helper.GetNullableInt32(reader["CHARACTER_MAXIMUM_LENGTH"])
|
|
};
|
|
}
|
|
|
|
private static SqlDbType ConvertToDataType(string dataTypeStr)
|
|
{
|
|
if (dataTypeStr == "table type") return SqlDbType.Structured;
|
|
|
|
return dataTypeStr.ToEnum<SqlDbType>();
|
|
}
|
|
|
|
private static ParameterDirection ConvertToDirection(string value)
|
|
{
|
|
switch (value)
|
|
{
|
|
case "IN": return ParameterDirection.Input;
|
|
case "OUT": return ParameterDirection.Output;
|
|
case "INOUT": return ParameterDirection.InputOutput;
|
|
case "ReturnValue": return ParameterDirection.ReturnValue;
|
|
default: throw new InvalidCastException($"Cast error. String value '{ value ?? "[null]" }' to { typeof(ParameterDirection) } type");
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|