public override List<DataType> GetUserDefinedTableTypeColumns(String name)
{
List<DataType> l = new List<DataType>();
DataType c = null;
using (Database db = this.CreateDatabase())
{
var reader = db.ExecuteReader(this.QueryBuilder.GetUserDefinedTypeColumns(name));
while (reader.Read())
{
c = new DataType();
c.Name = reader.GetString(0);
c.Ordinal = l.Count;
c.DbType = this.CreateDbType(reader["ColumnType"]);
c.UdtTypeName = reader.GetString(6);//UdtTypeName
if (reader[4] != DBNull.Value) c.Length = reader.GetInt16(2);
if (reader[5] != DBNull.Value) c.Precision = reader.GetByte(3);
if (reader[6] != DBNull.Value) c.Scale = reader.GetByte(4);
c.AllowNull = reader.GetBoolean(5);
l.Add(c);
}
}
return l;
}
public DataType(String name, DataType dataType)
{
this.SetProperty(dataType);
this.Name = name;
}
public void SetProperty(DataType dataType)
{
this.Name = dataType.Name;
this.Ordinal = dataType.Ordinal;
this.DbType = dataType.DbType;
this.Length = dataType.Length;
this.Precision = dataType.Precision;
this.Scale = dataType.Scale;
this.AllowNull = dataType.AllowNull;
this.UdtTypeName = dataType.UdtTypeName;
this.EnumName = dataType.EnumName;
this.EnumValues = dataType.EnumValues;
}
public override void SetResultSetsList(StoredProcedure sp, Dictionary<String, Object> values)
{
List<StoredProcedureResultSetColumn> resultSetsList = new List<StoredProcedureResultSetColumn>();
StoredProcedureResultSetColumn resultSets = null;
List<DataTable> schemaDataTableList = new List<DataTable>();
DataType c = null;
var cm = CreateTestSqlCommand<SqlCommand>(sp, values);
//UserDefinedTableType
foreach (var item in sp.Parameters.Where(el => el.DbType.SqlServerDbType == SqlServer2012DbType.Structured))
{
var dt = cm.Parameters[item.Name].Value as DataTable;
var udt = this.GetUserDefinedTableType(item.UserTableTypeName);
foreach (var column in udt.Columns)
{
dt.Columns.Add(new DataColumn(column.Name, column.GetClassNameType().ToType()));
}
var oo = new Object[udt.Columns.Count];
for (int i = 0; i < udt.Columns.Count; i++)
{
oo[i] = udt.Columns[i].CreateParameter().Value;
}
dt.Rows.Add(oo);
}
//処理の実行によってデータの変更などの副作用が起きないようにRollBackする。
using (var db = this.CreateDatabase())
{
try
{
db.Open();
db.BeginTransaction(IsolationLevel.ReadUncommitted);
using (IDataReader r = db.ExecuteReader(cm))
{
var schemaDataTable = r.GetSchemaTable();
if (schemaDataTable == null) return;
schemaDataTableList.Add(schemaDataTable);
//TableNameSelectAllストアドの場合はスキップ
if (String.IsNullOrEmpty(sp.TableName) == true ||
sp.Name.EndsWith("SelectAll") == false)
{
while (r.NextResult())
{
schemaDataTableList.Add(r.GetSchemaTable());
}
}
}
if (db.OnTransaction == true)
{
db.RollBackTransaction();
}
}
catch
{
if (db.OnTransaction == true)
{
db.RollBackTransaction();
}
throw;
}
}
if (schemaDataTableList.Count == 0) return;
Int32 index = 0;
foreach (var schemaDataTable in schemaDataTableList)
{
if (index == 0)
{
resultSets = new StoredProcedureResultSetColumn("ResultSet");
}
else
{
resultSets = new StoredProcedureResultSetColumn("ResultSet" + index);
}
for (var i = 0; i < schemaDataTable.Rows.Count; i++)
{
var row = schemaDataTable.Rows[i];
c = new DataType();
c.Name = row["ColumnName"].ToString();
c.Ordinal = resultSets.Columns.Count;
c.DbType = this.CreateDbType(row["ProviderType"]);
if (c.DbType.IsUdt() == true)
{
var tp = row["DataType"] as TypeInfo;
switch (tp.FullName)
{
case "Microsoft.SqlServer.Types.SqlGeometry": c.UdtTypeName = "geometry"; break;
case "Microsoft.SqlServer.Types.SqlGeography": c.UdtTypeName = "geography"; break;
case "Microsoft.SqlServer.Types.SqlHierarchyId": c.UdtTypeName = "hierarchyid"; break;
default: throw new InvalidOperationException();
}
}
if (c.DbType.IsStructured() == true)
{
throw new NotImplementedException();
}
c.Length = AppEnvironment.Settings.TypeConverter.ToInt32(row["ColumnSize"]) ?? c.Length;
c.Precision = AppEnvironment.Settings.TypeConverter.ToInt32(row["NumericPrecision"]) ?? c.Precision;
if (c.DbType.CanDeclarePrecisionScale() == true ||
c.DbType.CanDeclareScale() == true)
{
c.Scale = AppEnvironment.Settings.TypeConverter.ToInt32(row["NumericScale"]) ?? c.Scale;
if (c.Scale.HasValue == false)
{
c.Scale = AppEnvironment.Settings.TypeConverter.ToInt32(row["DateTimeScale"]) ?? c.Scale;
}
}
resultSets.Columns.Add(c);
}
resultSetsList.Add(resultSets);
index += 1;
}
foreach (var item in resultSetsList)
{
sp.ResultSets.Add(item);
}
}
public SqlInputParameter(String name, DataType column)
{
this.ParameterDirection = ParameterDirection.Input;
this.SetProperty(column);
this.Name = name;
}