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 <MySqlCommand>(sp, values); //処理の実行によってデータの変更などの副作用が起きないようにRollBackする。 using (var db = this.CreateDatabase()) { try { db.Open(); db.BeginTransaction(IsolationLevel.ReadCommitted); 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) { throw new InvalidOperationException(); } if (c.DbType.IsStructured() == true) { throw new InvalidOperationException(); } 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 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); } }