public static System.Data.DataTable ConvertToSystemDataTable(SqlConnection connection, DataTable dataTable)
{
ThrowIf.Null(connection, "connection");
ThrowIf.Null(dataTable, "dataTable");
System.Data.DataTable convertedDataTable = GetConvertedSystemDataTable(connection, dataTable);
foreach (DataRow dataRow in dataTable.Rows)
{
var row = convertedDataTable.NewRow();
for (int i = 0; i < convertedDataTable.Columns.Count; i++)
{
// manage managed to sql conversions
row[i] = ConvertToDatabaseValue(dataRow[i]);
}
convertedDataTable.Rows.Add(row);
}
return(convertedDataTable);
}
/// <summary>
/// Gets system data table contains schema definition only.
/// </summary>
/// <param name="connection">The SQL connection object.</param>
/// <param name="dataTable">The object as a <see cref="Data.Types.DataTable"/>.</param>
/// <returns>The system data table contains schema definition.</returns>
private static System.Data.DataTable GetConvertedSystemDataTable(SqlConnection connection, DataTable dataTable)
{
if (cachedEmptySystemTables.ContainsKey(dataTable.TableName))
{
return(cachedEmptySystemTables[dataTable.TableName].Clone());
}
System.Data.DataTable systemDataTable;
using (var command = connection.CreateCommand())
{
systemDataTable = new System.Data.DataTable(dataTable.TableName);
systemDataTable.Locale = CultureInfo.InvariantCulture;
command.Parameters.AddWithValue("@schemaName", SqlServerDatabaseProvider.CrtDatabaseSchemaName);
command.Parameters.AddWithValue("@tableName", systemDataTable.TableName);
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM [crt].[GetUdttColumns](@schemaName, @tableName)";
using (var dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
var type = SqlTypeHelper.GetClrType((string)dataReader["TABLE_TYPE_COL_DATATYPE"]);
var name = (string)dataReader["TABLE_TYPE_COL_NAME"];
var dataColumn = new System.Data.DataColumn(name, type);
systemDataTable.Columns.Add(dataColumn);
}
}
}
}
cachedEmptySystemTables[dataTable.TableName] = systemDataTable;
return(systemDataTable.Clone());
}