public static DataTable ConvertDataReaderToDataTable(DbDataReader reader)
{
try
{
DataTable table = new DataTable();
int fieldCount = reader.FieldCount;
for (int fieldIndex = 0; fieldIndex < fieldCount; ++fieldIndex)
{
table.Columns.Add(reader.GetName(fieldIndex), reader.GetFieldType(fieldIndex));
}
table.BeginLoadData();
object[] rowValues = new object[fieldCount];
while (reader.Read())
{
reader.GetValues(rowValues);
table.LoadDataRow(rowValues, true);
}
reader.Close();
table.EndLoadData();
return table;
}
catch (Exception ex)
{
throw new Exception("DataReader转换为DataTable时出错!", ex);
}
}
public static DataTable CreateTable(DbDataReader reader)
{
DataTable table = new DataTable();
for (int i = 0; i < reader.FieldCount; i++)
{
DataColumn column = new DataColumn(reader.GetName(i), reader.GetFieldType(i));
table.Columns.Add(column);
}
table.AcceptChanges();
return table;
}
static int GetFieldType(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
System.Data.Common.DbDataReader obj = (System.Data.Common.DbDataReader)ToLua.CheckObject(L, 1, typeof(System.Data.Common.DbDataReader));
int arg0 = (int)LuaDLL.luaL_checknumber(L, 2);
System.Type o = obj.GetFieldType(arg0);
ToLua.Push(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
public void WriteColumns2File(DbDataReader reader, string file)
{
if (reader == null)
return;
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(file))
{
for (int i = 0; i < reader.VisibleFieldCount; i++)
{
string index = (i + 1).ToString("D2");
string columnName = reader.GetName(i).ToUpper();
string type = reader.GetFieldType(i).Name;
string comment = "说明";
writer.WriteLine(string.Format("{0}\t|\t{1}\t|\t{2}\t|\t{3}", index, columnName, type, comment));
}
}
}
private FeatureDataTable CreateTableFromReader(DbDataReader reader, int geomIndex)
{
var res = new FeatureDataTable {TableName = Table};
for (var c = 0; c < geomIndex; c++)
{
var fieldType = reader.GetFieldType(c);
if (fieldType == null)
throw new Exception("Unable to retrieve field type for column " + c);
res.Columns.Add(DequoteIdentifier(reader.GetName(c)), fieldType);
}
return res;
}
private static ColumnMetadata[] GetTableMetadata(DbDataReader reader)
{
var columnMetadata = new ColumnMetadata[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
columnMetadata[i] =
new ColumnMetadata(
reader.GetName(i), reader.GetDataTypeName(i), reader.GetFieldType(i));
}
return columnMetadata;
}
public override bool BulkInsert(string schemaName, string tableName, DbDataReader reader, Action<long> callback)
{
// Read the first row to get the information on the schema
if (reader.Read()) {
var existingTable = GetDbSchema(schemaName, tableName);
var readerTable = new DbTable(tableName, schemaName);
var pgTypes = new List<NpgsqlDbType>();
for (var i = 0; i < reader.FieldCount; i++) {
var columnName = reader.GetName(i);
var columnType = reader.GetFieldType(i);
var column = new DbColumn(columnName, MungType.Get(columnType));
readerTable.Columns.Add(column);
var type = PostgresqlTypeConverter.Get(column.MungType);
if (type == null) {
throw new Exception($"Unable to load Postgres type for type: {column.MungType.Code}, Column: {column.ColumnName}");
}
pgTypes.Add(type.PostgresqlDbType);
}
if (existingTable == null) {
CreateTable(readerTable);
} else {
ModifySchema(existingTable, readerTable);
}
var table = GetDbSchema(schemaName, tableName);
var rowCount = 0L;
using (var cn = _connectionFactory()) {
using (var writer = cn.BeginBinaryImport(CopyCommand(table))) {
do {
writer.StartRow();
for (var i = 0; i < table.Columns.Count; i++) {
var col = table.Columns[i];
var val = reader[col.ColumnName];
if (val == DBNull.Value) {
writer.WriteNull();
} else {
writer.Write(reader[col.ColumnName], pgTypes[i]);
}
}
if (callback != null) {
callback(rowCount);
}
rowCount++;
} while (reader.Read());
}
}
}
return true;
}
private IEnumerable<MapPlan> BuildMapPlans(GroupResult<PosName> groupedName, DbDataReader dataReader, PocoData pocoData, List<PocoMember> pocoMembers)
{
// find pocomember by property name
var pocoMember = pocoMembers.FirstOrDefault(x => IsEqual(groupedName.Item, x.Name));
if (pocoMember == null)
{
yield break;
}
if (groupedName.SubItems.Any())
{
var memberInfoType = pocoMember.MemberInfoData.MemberType;
if (memberInfoType.IsAClass() || pocoMember.IsDynamic)
{
var children = PocoDataBuilder.IsDictionaryType(memberInfoType)
? CreateDynamicDictionaryPocoMembers(groupedName.SubItems, pocoData)
: pocoMember.PocoMemberChildren;
var subPlans = groupedName.SubItems.SelectMany(x => BuildMapPlans(x, dataReader, pocoData, children)).ToArray();
yield return (reader, instance) =>
{
var newObject = pocoMember.IsList ? pocoMember.Create(dataReader) : (pocoMember.GetValue(instance) ?? pocoMember.Create(dataReader));
var shouldSetNestedObject = false;
foreach (var subPlan in subPlans)
{
shouldSetNestedObject |= subPlan(reader, newObject);
}
if (shouldSetNestedObject)
{
if (pocoMember.IsList)
{
var list = pocoMember.CreateList();
list.Add(newObject);
newObject = list;
}
pocoMember.SetValue(instance, newObject);
return true;
}
return false;
};
}
}
else
{
var destType = pocoMember.MemberInfoData.MemberType;
var defaultValue = MappingHelper.GetDefault(destType);
var converter = GetConverter(pocoData, pocoMember.PocoColumn, dataReader.GetFieldType(groupedName.Key.Pos), destType);
yield return (reader, instance) => MapValue(groupedName, reader, converter, instance, pocoMember.PocoColumn, defaultValue);
}
}
public override bool BulkInsert(string schemaName, string tableName, DbDataReader reader, Action<long> eachRow)
{
using (var cn = _connectionFactory()) {
using (SqlBulkCopy copy = new SqlBulkCopy(cn)) {
var existingTable = GetDbSchema(schemaName, tableName);
var readerTable = new DbTable(tableName, schemaName);
for (var i = 0; i < reader.FieldCount; i++) {
var columnName = reader.GetName(i);
var columnType = reader.GetFieldType(i);
var column = new DbColumn(columnName, MungType.Get(columnType));
readerTable.Columns.Add(column);
}
if (existingTable == null) {
CreateTable(readerTable);
}
var table = GetDbSchema(schemaName, tableName);
for (var i = 0; i < reader.FieldCount; i++) {
var column = reader.GetName(i);
var sourceOrdinal = i;
var destinationOrdinal = table.Columns.FindIndex(x => x.ColumnName == column);
if (destinationOrdinal == -1) {
var msg = string.Format("Unable to resolve column mapping, column: {0} was not found in destination table {1}",
column,
table.TableName
);
throw new Exception(msg);
}
copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, destinationOrdinal));
}
copy.DestinationTableName = string.Format("[{0}].[{1}]", table.SchemaName, table.TableName);
copy.BatchSize = 1000;
copy.NotifyAfter = 1;
copy.SqlRowsCopied += (sender, e) => {
eachRow(e.RowsCopied);
};
copy.BulkCopyTimeout = 9999999;
copy.WriteToServer(reader);
}
}
return true;
}
/// <summary>
/// DataReader转换为Json
/// </summary>
/// <param name="dataReader">DataReader对象</param>
/// <returns>Json字符串</returns>
public static string ToJson(DbDataReader dataReader)
{
string jsonString = "[";
while (dataReader.Read())
{
jsonString += "{";
for (int i = 0; i < dataReader.FieldCount; i++)
{
jsonString += "\"" + ToJson(dataReader.GetName(i)) + "\":";
Type type = dataReader.GetFieldType(i);
if (type == typeof(DateTime))
{
if (!dataReader.IsDBNull(i))
{
DateTime dt = dataReader.GetDateTime(i);
jsonString += "\"" + ToJson(dt.ToString("yyyy-MM-dd HH:mm:ss")) + "\",";
}
else
{
jsonString += "\"\",";
}
}
else if (type == typeof(string))
{
jsonString += "\"" + ToJson(dataReader[i].ToString()) + "\",";
}
else if (type == typeof(Int32) || type == typeof(Int64) || type == typeof(Double) || type == typeof(Decimal))
{
if (!dataReader.IsDBNull(i))
{
jsonString += ToJson(dataReader[i].ToString()) + ",";
}
else
{
jsonString += "\"\",";
}
}
else
{
throw new Exception("未知类型:" + type.ToString());
//jsonString += ToJson(dataReader[i].ToString()) + ",";
}
}
jsonString = DeleteLast(jsonString) + "},";
}
dataReader.Close();
return DeleteLast(jsonString) + "]";
}
public override void Init(DbDataReader dataReader, PocoData pocoData)
{
_converter = GetConverter(pocoData, null, dataReader.GetFieldType(0), pocoData.Type) ?? (x => x);
base.Init(dataReader, pocoData);
}
private static Entity GetSchemaEntity(DbDataReader reader, string entitySet, string entityKind)
{
reader.Read();
var entity = new Entity();
entity.AddProperty(DataLoaderConstants.PropNameEntitySet, entitySet);
entity.AddProperty(DataLoaderConstants.PropNameEntityKind, entityKind);
for (int i = 0; i < reader.FieldCount; ++i)
{
entity.AddProperty(reader.GetName(i), reader.GetFieldType(i).ToString());
}
return entity;
}
private static void HandleDbNullValues(DbDataReader reader,
object targetObject,
PropertyInfo propertyInfo,
string fieldName)
{
var propertyType = propertyInfo.PropertyType;
var isNullable = (Nullable.GetUnderlyingType(propertyType) != null);
if (isNullable)
{
propertyInfo.SetValue(targetObject, null, null);
}
else
{
Type targetObjectType = reader.GetFieldType(fieldName);
throw new NullableFieldTypeException(fieldName, propertyType, targetObjectType);
}
}
/// <summary>
/// DataReader 转 List
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
public List<Dictionary<string, dynamic>> ConvertDataReaderToDictList(DbDataReader dataReader)
{
var result = new List<Dictionary<string, dynamic>>();
if (dataReader != null && dataReader.HasRows)
{
while (dataReader.Read())
{
var dict = new Dictionary<string, dynamic>();
for (var i = 0; i < dataReader.FieldCount; i++)
{
try
{
var key = dataReader.GetName(i);
var val = Convert.ChangeType(dataReader[i], dataReader.GetFieldType(i));
if (!string.IsNullOrWhiteSpace(key))
dict.Add(key, val);
}
catch
{
}
}
result.Add(dict);
}
}
return result;
}
