/// <summary>
/// 根据数据库提供者和连接字符串创建数据库对象
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="dbProvider">数据库提供者</param>
/// <exception cref="DatabaseNotSupportedException">指定数据库不被支持</exception>
/// <returns>数据库对象</returns>
public static AbstractDatabase CreateDatabase(DbProviderFactory dbProvider, String connectionString)
{
DatabaseType dbType = DatabaseTypeHelper.InternalGetDatabaseType(dbProvider, connectionString);
switch (dbType)
{
case DatabaseType.Access:
return(new AccessDatabase(dbProvider, connectionString));
case DatabaseType.MySQL:
return(new MySQLDatabase(dbProvider, connectionString));
case DatabaseType.Oracle:
return(new OracleDatabase(dbProvider, connectionString));
case DatabaseType.SQLite:
return(new SQLiteDatabase(dbProvider, connectionString));
case DatabaseType.SqlServer:
return(new SqlServerDatabase(dbProvider, connectionString));
case DatabaseType.SqlServerCe:
return(new SqlServerCeDatabase(dbProvider, connectionString));
default:
throw new DatabaseNotSupportedException("This database is not supported!");
}
}
/// <summary>
/// Creates a new Cell object with the InlineString data type.
/// </summary>
private static Cell CreateTextCell(
object cellValue,
uint?styleIndex, bool isRelationshipValue = false)
{
var cell = new Cell
{
DataType = CellValues.InlineString
};
//apply the cell style if supplied
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
var inlineString = new InlineString( );
var t = new DocumentFormat.OpenXml.Spreadsheet.Text( );
if (cellValue != null)
{
t.Text = isRelationshipValue ? DatabaseTypeHelper.GetEntityXmlName(cellValue.ToString( )) : cellValue.ToString( );
}
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
/// <summary>
/// Get the formatted cell value for the different data types.
/// </summary>
private static string GetFormattedCellValue(object cellValue, DatabaseType type, bool isRelationshipType = false)
{
if (cellValue == null || cellValue is DBNull)
{
return("");
}
if (type is BoolType)
{
string result = ExportDataHelper.GetBooleanCellValue(( bool )cellValue);
return(result);
}
if (type is ChoiceRelationshipType || type is InlineRelationshipType || isRelationshipType)
{
string result = DatabaseTypeHelper.GetEntityXmlName(( string )cellValue);
return(result);
}
else
{
// TODO: UTC ??!!
string formatString = DatabaseTypeHelper.GetDisplayFormatString(type);
string result = string.Format(formatString, cellValue);
return(result);
}
}
/// <summary>
/// Get the strings for csv file for different data types.
/// </summary>
/// <param name="type">DatabaseType</param>
/// <param name="cellValue">string</param>
/// <param name="col">ReportColumn</param>
/// <returns>string</returns>
public static string GetCsvCellValue(DatabaseType type, string cellValue, ReportColumn col)
{
//convert cell value to its particular type
object value = DatabaseTypeHelper.ConvertFromString(type, cellValue);
if (cellValue == null)
{
return("");
}
if (type is BoolType)
{
return(GetBooleanCellValue(( bool )value));
}
if (type is AutoIncrementType)
{
return(DatabaseTypeHelper.ConvertToString(type, value, col.AutoNumberDisplayPattern));
}
if (type is DateType || type is DateTimeType)
{
return(cellValue);
}
if (type is TimeType)
{
return((( DateTime )value).ToUniversalTime( ).TimeOfDay.ToString( ));
}
if (type is StructureLevelsType)
{
return(GetStructureLevelCellValue(cellValue, false));
}
return(value.ToString( ));
}
public void TestDatabaseTypeDeserialization(string dataType, string value)
{
string json = string.Format(@"{{""dataType"":""{0}"",""value"":""{1}""}}", dataType, value);
using (var str = new StringReader(json))
{
var container = JSON.Deserialize <ObjectContainer>(str, Options);
DatabaseType databaseType = DatabaseTypeHelper.ConvertFromDisplayName(dataType);
string stringVal = container.Value;
object val = databaseType.ConvertFromString(stringVal);
if (val is DateTime)
{
DateTime val1 = ( DateTime )val;
DateTime val2 = DateTime.Parse(value);
Assert.AreEqual(val1, val2);
}
else
{
Assert.AreEqual(value.ToLowerInvariant( ), val.ToString( ).ToLowerInvariant( ));
}
}
}
public void ConvertToString_Decimal()
{
const decimal originalData = 8722387463.4562M;
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DecimalType, originalData);
Decimal convertedData = Decimal.Parse(stringData);
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertToString_Int32()
{
const int originalData = 476685;
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.Int32Type, originalData);
Int32 convertedData = Int32.Parse(stringData);
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertToString_Guid()
{
Guid originalData = Guid.NewGuid();
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.GuidType, originalData);
var convertedData = new Guid(stringData);
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertToString_Time()
{
var originalData = new TimeSpan(10, 34, 45);
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.TimeType, originalData);
TimeSpan convertedData = TimeSpan.Parse(stringData);
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertToString_Xml()
{
const string originalData = "<originalXml></originalXml>";
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.XmlType, originalData);
string convertedData = stringData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertToString_DateTime()
{
var originalData = new DateTime(2011, 5, 23, 5, 4, 3);
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DateTimeType, originalData);
DateTime convertedData = DateTime.Parse(stringData);
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertToString_String_Null()
{
const string originalData = null;
string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.StringType, originalData);
string convertedData = stringData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_Xml()
{
const string originalData = "<xml></xml>";
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.XmlType, originalData);
var convertedData = (string)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_String_Null()
{
const string originalData = null;
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.StringType, originalData);
var convertedData = (string)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_ConvertToString_String()
{
const string originalData = "string data";
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.StringType, originalData);
object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.StringType, objectData);
Assert.AreEqual(originalData, convertedStringData, "The converted data is invalid.");
}
public void ConvertFromString_Currency()
{
const decimal originalData = 74845739.345M;
string originalStringData = originalData.ToString(CultureInfo.InvariantCulture);
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.CurrencyType, originalStringData);
var convertedData = (Decimal)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_ConvertToString_Time()
{
var originalData = new TimeSpan(13, 4, 13);
string originalStringData = originalData.ToString("c");
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.TimeType, originalStringData);
object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.TimeType, objectData);
Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid.");
}
public void ConvertFromString_Decimal()
{
const decimal originalData = 83673838.3335M;
string originalStringData = originalData.ToString(CultureInfo.InvariantCulture);
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.DecimalType, originalStringData);
var convertedData = (Decimal)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_ConvertToString_Bool()
{
const bool originalData = true;
string originalStringData = originalData.ToString();
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.BoolType, originalStringData);
object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BoolType, objectData);
Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid.");
}
public void ConvertFromString_Guid()
{
Guid originalData = Guid.NewGuid();
string originalStringData = originalData.ToString();
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.GuidType, originalStringData);
var convertedData = (Guid)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_Date_NotCustomFormat()
{
var originalData = new DateTime(2011, 4, 4);
string originalStringData = originalData.ToString(CultureInfo.InvariantCulture);
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.DateType, originalStringData);
var convertedData = (DateTime)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_Int32()
{
const int originalData = 12387352;
string originalStringData = originalData.ToString(CultureInfo.InvariantCulture);
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.Int32Type, originalStringData);
var convertedData = (Int32)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_TimeLegacy()
{
var originalData = new TimeSpan(13, 4, 13);
string originalStringData = "05/02/2013 13:04:13";
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.TimeType, originalStringData);
var convertedData = (DateTime)objectData; // TODO: DateTime here, but TimeSpan elsewhere!!
Assert.AreEqual(originalData, convertedData.TimeOfDay, "The converted data is invalid.");
}
public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE EXTERNAL_NOTIFICATION_QUEUE SET
TRIES = TRIES + 1,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
public void ConvertFromString_DateTime_NotCustomFormat()
{
var originalData = new DateTime(2011, 4, 4, 4, 6, 7);
string originalStringData = originalData.ToUniversalTime().ToString("u");
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.DateTimeType, originalStringData);
var convertedData = (DateTime)objectData;
Assert.AreEqual(originalData, convertedData, "The converted data is invalid.");
}
public void ConvertFromString_ConvertToString_DateTime()
{
var originalData = new DateTime(2011, 4, 4, 4, 6, 7, DateTimeKind.Utc);
string originalStringData = originalData.ToString(DateTimeType.DateTimeFormatString);
object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.DateTimeType, originalStringData);
object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DateTimeType, objectData);
Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid.");
}
public static void DeleteSentNotifications(DbConnection connection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $"DELETE FROM SYSTEM_NOTIFICATION_QUEUE WHERE SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids, string lastExceptionMessage = null)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
TRIES = TRIES + 1,
LastExceptionMessage = @lastExceptionMessage,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids, lastExceptionMessage);
}
public static void UpdateSentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)},
LastExceptionMessage = NULL,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
/// <summary>
/// 初始化数据库类
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="dbProvider">数据库提供者</param>
internal Database(String connectionString, DbProviderFactory dbProvider)
{
this._connectionString = connectionString;
this._dbProvider = dbProvider;
this._dbtype = DatabaseTypeHelper.InternalGetDatabaseType(this._connectionString, this._dbProvider);
if (this._dbtype == DatabaseType.Unknown)
{
throw new DatabaseNotSupportException("This database is not supported!");
}
}
