public static DALDataTable GetData(string tableName, string sortIndex, string sortOrder, long pageIndex, long pageSize, string filterQuery, DALISGDatabaseType databaseType)
{
var dataTable =
new DALDataTable
{
Name = tableName,
Columns = new List<DALDataTableColumn>(),
Rows = new List<DALDataTableRow>()
};
using (var context = new ISGOutputEntities(Database.GetEFConnectionString(databaseType)))
{
var metaDatas = context.TableMetaData.Where(f => string.Equals(f.tableName, tableName, StringComparison.CurrentCultureIgnoreCase)).ToList();
if (metaDatas.Any())
{
foreach (var metaData in metaDatas)
{
dataTable.Columns.Add(
new DALDataTableColumn
{
Name = metaData.columnName,
IsNullable = (metaData.columnIsNullable.HasValue && metaData.columnIsNullable == 1),
Length = metaData.columnLength,
Precision = metaData.columnPrecision,
Scale = metaData.columnScale,
TypeName = metaData.columnTypeName,
ParentTable = tableName,
IsPrimaryKey = (metaData.isPrimaryKey == 1)
});
}
sortOrder = (!string.IsNullOrEmpty(sortOrder)) ? sortOrder : "ASC";
sortIndex = (!string.IsNullOrEmpty(sortIndex) && dataTable.Columns.Any(x => string.Equals(x.Name, sortIndex, StringComparison.CurrentCultureIgnoreCase))) ? sortIndex : "OSC";
var startingRowNum = pageIndex * (pageSize - 1) + 1;
var endingRowNum = (pageIndex + 1) * pageSize;
var sql = new StringBuilder("");
sql.Append("SELECT * FROM ");
sql.AppendFormat("(SELECT ROW_NUMBER() OVER (ORDER BY {0} {1}) AS RowNum, * FROM {2} {5}) AS Result WHERE RowNum >= {3} AND RowNum <= {4} ORDER BY {0} {1}", sortIndex, sortOrder, tableName, startingRowNum, endingRowNum, filterQuery);
var connection = (SqlConnection)context.Database.Connection;
var dataRows = connection.Query(sql.ToString()).ToList();
foreach (var row in dataRows)
{
var dataRow =
new DALDataTableRow
{
Cells = new List<DALDataTableCell>()
};
var rowId = new StringBuilder(tableName);
foreach (var column in dataTable.Columns)
{
var value = (((IDictionary<string, object>)row)[column.Name]);
if (column.IsPrimaryKey && value != null)
{
var newId = Regex.Replace(value.ToString(), "[^0-9a-zA-Z]+", "");
rowId.Append(newId);
}
dataRow.Cells.Add(
new DALDataTableCell
{
ColumnName = column.Name,
Value = (value != null) ? value.ToString() : string.Empty
});
}
dataRow.RowId = rowId.ToString();
dataTable.Rows.Add(dataRow);
}
}
else
{
dataTable = null;
}
}
return dataTable;
}
public static void BulkInsert(DALDataTable dalDataTable, DALISGDatabaseType databaseType)
{
var insertTable = new DataTable(dalDataTable.Name);
foreach (var column in dalDataTable.Columns)
{
var dataColumn =
new DataColumn(column.Name)
{
AllowDBNull = column.IsNullable,
DataType = Type.GetType(column.TypeName)
};
if (string.Equals(column.TypeName, typeof(string).ToString(), StringComparison.CurrentCultureIgnoreCase))
{
dataColumn.MaxLength = column.Length;
}
insertTable.Columns.Add(dataColumn);
}
foreach (var row in dalDataTable.Rows)
{
var newRow = insertTable.NewRow();
foreach (var column in dalDataTable.Columns)
{
var columnName = column.Name;
var cell = row.Cells.FirstOrDefault(x => string.Equals(x.ColumnName, columnName, StringComparison.CurrentCultureIgnoreCase));
if (cell == null)
{
continue;
}
if (!insertTable.Columns[columnName].AllowDBNull && (string.IsNullOrEmpty(cell.Value)))
{
var type = insertTable.Columns[columnName].DataType;
if (type == typeof(string))
{
newRow[columnName] = string.Empty;
}
if (type == typeof(DateTime))
{
newRow[columnName] = new DateTime(1753, 1, 1);
}
else if (type.IsValueType)
{
newRow[columnName] = Activator.CreateInstance(type);
}
}
else if (string.IsNullOrEmpty(cell.Value))
{
newRow[columnName] = DBNull.Value;
}
else
{
var columnType = Type.GetType(column.TypeName) ?? typeof(string);
if (columnType == typeof(decimal))
{
newRow[columnName] = decimal.Parse(cell.Value, NumberStyles.Any, null);
}
else if (columnType == typeof(long))
{
newRow[columnName] = long.Parse(cell.Value, NumberStyles.Any, null);
}
else if (columnType == typeof(double))
{
newRow[columnName] = double.Parse(cell.Value, NumberStyles.Any, null);
}
else if (columnType == typeof(int))
{
newRow[columnName] = int.Parse(cell.Value, NumberStyles.Any, null);
}
else if (columnType == typeof(short))
{
newRow[columnName] = short.Parse(cell.Value, NumberStyles.Any, null);
}
else
{
newRow[columnName] = Convert.ChangeType(cell.Value, columnType);
}
}
}
insertTable.Rows.Add(newRow);
}
var connectionString = Database.GetConnectionString(databaseType);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var bulkCopy = new SqlBulkCopy(connection))
{
foreach (DataColumn column in insertTable.Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
var preppedTable = (insertTable.Rows.Cast<DataRow>().Where(row => row.ItemArray.Any(field => !(field is DBNull)))).CopyToDataTable();
bulkCopy.DestinationTableName = insertTable.TableName;
bulkCopy.WriteToServer(preppedTable);
}
}
}
