private static bool _CreateTable(string tableName, TableInfo tableInfo, out string errorString)
{
// 生成在创建数据表时所有字段的声明
StringBuilder fieldDefineStringBuilder = new StringBuilder();
foreach (FieldInfo fieldInfo in GetAllDatabaseFieldInfo(tableInfo))
{
// 在这里并不对每种本工具的数据类型是否能导出为指定的MySQL数据类型进行检查（比如本工具中的string型应该导出为MySQL中的文本类型如varchar，而不应该导出为数值类型）
if (fieldInfo.DataType == DataType.Date)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_DATABASE_FORMAT].ToString();
DateFormatType toDatabaseFormatType = TableAnalyzeHelper.GetDateFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到MySQL中的数据类型错误，不允许为time型，如果仅需要时分秒部分，请在Excel中将该字段在本工具中的数据类型改为time型", fieldInfo.FieldName, Utils.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == DateFormatType.ReferenceDateSec || toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到MySQL中的形式为距1970年的时间（{2}），但所填写的导出到MySQL中的格式为时间型的{3}，请声明为MySQL中的数值型", fieldInfo.FieldName, Utils.GetExcelColumnName(fieldInfo.ColumnSeq + 1), toDatabaseFormatDefine, fieldInfo.DatabaseFieldType);
return(false);
}
}
}
else if (fieldInfo.DataType == DataType.Time)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_DATABASE_FORMAT].ToString();
TimeFormatType toDatabaseFormatType = TableAnalyzeHelper.GetTimeFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到MySQL中的数据类型错误，不允许为datetime或date型，如果需要年月日部分，请在Excel中将该字段在本工具中的数据类型改为date型", fieldInfo.FieldName, Utils.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec && fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到MySQL中的形式为距0点的秒数（#sec），但所填写的导出到MySQL中的格式为时间型的time，请声明为MySQL中的数值型", fieldInfo.FieldName, Utils.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
}
fieldDefineStringBuilder.AppendFormat("`{0}` {1} COMMENT '{2}',", fieldInfo.DatabaseFieldName, fieldInfo.DatabaseFieldType, fieldInfo.Desc);
}
string createTableSql = string.Format(_CREATE_TABLE_SQL, _CombineDatabaseTableFullName(tableName), fieldDefineStringBuilder.ToString(), tableInfo.GetKeyColumnFieldInfo().DatabaseFieldName);
try
{
MySqlCommand cmd = new MySqlCommand(createTableSql, _conn);
cmd.ExecuteNonQuery();
errorString = null;
return(true);
}
catch (MySqlException exception)
{
errorString = exception.Message;
return(false);
}
}
/// <summary>
/// Convert enumeration to string
/// </summary>
public static string ToString(DateFormatType value)
{
// Switch through the langauges
switch (value)
{
case DateFormatType.DayMonthYear:
{
return DayMonthYear;
}
case DateFormatType.DayYearMonth:
{
return DayYearMonth;
}
case DateFormatType.MonthDayYear:
{
return MonthDayYear;
}
case DateFormatType.MonthYearDay:
{
return MonthYearDay;
}
case DateFormatType.YearDayMonth:
{
return YearDayMonth;
}
case DateFormatType.YearMonthDay:
{
return YearMonthDay;
}
}
// Unknown value
throw new Exception("Unrecognized date format type \"" + value.ToString() + "\"");
}
public static string ToString(this DateTime date, DateFormatType type)
{
switch (type)
{
case DateFormatType.YYYYMMDD:
return(date.ToString("yyyyMMdd"));
case DateFormatType.YYYY_D_MM_D_DD:
return(date.ToString("yyyy.MM.dd"));
case DateFormatType.YYYY_S_MM_S_DD:
return(date.ToString("yyyy/MM/dd"));
case DateFormatType.YYYYMMDDHHMMSS:
return(date.ToString("yyyyMMddHHmmss"));
case DateFormatType.YYYYMMDD_HHMMSS:
return(date.ToString("yyyyMMdd HHmmss"));
case DateFormatType.YYYY_H_MM_H_DD:
return(date.ToString("yyyy-MM-dd"));
case DateFormatType.ORIGIN_DATE:
return(date.ToString());
}
return(string.Empty);
}
/// <summary>Format given militar date to given format date</summary>
/// <param name="sDate">Militar date (8 digits string)</param>
/// <param name="objInputDateFormatType">Input string date format pattern</param>
/// <param name="objOutputDateFormatType">Output string date format pattern</param>
/// <param name="cSeparationChar">Separation character</param>
/// <returns>Formatted date string</returns>
public String FormatMilitarDateToFormattedDate(String sDate, DateFormatType objInputDateFormatType, DateFormatType objOutputDateFormatType, char cSeparationChar)
{
try
{
String sTimeFormatted = "";
if (sDate.Length != 8)
{
return(sDate);
}
switch (objInputDateFormatType)
{
case DateFormatType.ddmmyyyy:
case DateFormatType.ddMMyyyy:
switch (objOutputDateFormatType)
{
case DateFormatType.ddmmyyyy:
case DateFormatType.ddMMyyyy:
case DateFormatType.DEFAULT:
sTimeFormatted = sDate.Substring(0, 2) + cSeparationChar + sDate.Substring(2, 2) + cSeparationChar + sDate.Substring(4, 4);
break;
case DateFormatType.yyyymmdd:
case DateFormatType.yyyyMMdd:
sTimeFormatted = sDate.Substring(4, 4) + cSeparationChar + sDate.Substring(2, 2) + cSeparationChar + sDate.Substring(0, 2);
break;
case DateFormatType.NONE:
return(sDate);
}
break;
case DateFormatType.yyyymmdd:
case DateFormatType.yyyyMMdd:
case DateFormatType.DEFAULT:
switch (objOutputDateFormatType)
{
case DateFormatType.ddmmyyyy:
case DateFormatType.ddMMyyyy:
sTimeFormatted = sDate.Substring(6, 2) + cSeparationChar + sDate.Substring(4, 2) + cSeparationChar + sDate.Substring(0, 4);
break;
case DateFormatType.yyyymmdd:
case DateFormatType.yyyyMMdd:
case DateFormatType.DEFAULT:
sTimeFormatted = sDate.Substring(0, 4) + cSeparationChar + sDate.Substring(4, 2) + cSeparationChar + sDate.Substring(6, 2);
break;
case DateFormatType.NONE:
return(sDate);
}
break;
case DateFormatType.NONE:
return(sDate);
}
return(sTimeFormatted);
}
catch { return(sDate); }
}
/// <summary>
/// Method transform date into format that needed
/// </summary>
/// <param name="date">Target date</param>
/// <param name="ftype">Format type</param>
/// <returns>Formated date</returns>
public static string FormatDate(DateTime date, DateFormatType ftype)
{
string result = string.Empty;
switch (ftype)
{
case DateFormatType.OnlyNumbers:
result = date.ToString("dd.MM.yyyy");
break;
case DateFormatType.OnlyNumbersShort:
result = date.ToString("dd.MM.yy");
break;
case DateFormatType.WithWords:
result = date.ToString("dd MMMM yyyy");
break;
case DateFormatType.WithWordsShort:
result = date.ToString("dd MMM yyyy");
break;
case DateFormatType.USNumbers:
result = date.ToString("MM.dd.yyyy");
break;
case DateFormatType.USWords:
result = date.ToString("MMMM dd, yyyy");
break;
case DateFormatType.ChinaNumber:
result = date.ToString("yyyy.MM.dd");
break;
case DateFormatType.DateWithTime:
result = date.ToString("F");
break;
case DateFormatType.OnlyTime:
result = date.ToString("T");
break;
case DateFormatType.DateWithTimeShort:
result = date.ToString("G");
break;
}
return(result);
}
/// <summary>
/// Adds or overrides filter with the specified name and DateTime value.
/// </summary>
/// <param name="name">The filter name.</param>
/// <param name="value">The filter DateTime value.</param>
/// <param name="formatType">Short (only date) and long (date and time).</param>
protected void AddFilter(string name, DateTime value, DateFormatType formatType)
{
switch (formatType)
{
case DateFormatType.Short:
{
_filters[name] = value.ToString("yyyy-MM-dd");
break;
}
case DateFormatType.Long:
{
_filters[name] = value.ToString("yyyy-MM-dd'T'HH:mm:ss.fff'Z'");
break;
}
}
}
/// <summary>
/// 检查date型的输入格式定义
/// </summary>
public static bool CheckDateInputDefine(string defineString, out string errorString)
{
defineString = defineString.Trim();
if (string.IsNullOrEmpty(defineString))
{
errorString = "未进行格式声明";
return(false);
}
DateFormatType formatType = DateTimeValue.GetDateFormatType(defineString);
if (!(formatType == DateFormatType.FormatString || formatType == DateFormatType.ReferenceDateMsec || formatType == DateFormatType.ReferenceDateSec || formatType == DateFormatType.DataTable))
{
errorString = "定义的date格式类型错误";
return(false);
}
errorString = null;
return(true);
}
/// <summary>
/// Inflate from stream
/// </summary>
public override bool Inflate(Stream source)
{
// Reset inflation size
InflationSize = 0;
// NOTE: state ID is skipped because it is read by the construction factory
// Read the value
byte[] value = InflateValue(source);
// Read the first byte
DateFirst = value[0]; // VSTS# 1023839 - OIPI documentation for session state #2 (datefirst) is incorrect
// Read the second byte
DateFormat = _ToEnum(value[1]);
// Inflation is complete
return(true);
}
/// <summary>
/// Inflate from stream
/// </summary>
public override bool Inflate(Stream source)
{
// Reset inflation size
InflationSize = 0;
// NOTE: state ID is skipped because it is read by the construction factory
// Read the value
byte[] value = InflateValue(source);
// Read the first byte
DateFirst = value[0]; // VSTS# 1023839 - OIPI documentation for session state #2 (datefirst) is incorrect
// Read the second byte
DateFormat = _ToEnum(value[1]);
// Inflation is complete
return true;
}
/// <summary>
/// 检查date型的输入格式定义
/// </summary>
public static bool CheckDateInputDefine(string defineString, out string errorString)
{
defineString = defineString.Trim();
if (string.IsNullOrEmpty(defineString))
{
errorString = "未进行格式声明";
return(false);
}
DateFormatType formatType = TableAnalyzeHelper.GetDateFormatType(defineString);
if (!(formatType == DateFormatType.FormatString || formatType == DateFormatType.ReferenceDateMsec || formatType == DateFormatType.ReferenceDateSec))
{
errorString = "不属于合法的date型输入格式类型";
return(false);
}
errorString = null;
return(true);
}
/// <summary>
/// 检查date型导出至MySQL数据库的格式定义
/// </summary>
public static bool CheckDateToDatabaseDefine(string defineString, out string errorString)
{
defineString = defineString.Trim();
if (string.IsNullOrEmpty(defineString))
{
errorString = "未进行格式声明";
return(false);
}
DateFormatType formatType = DateTimeValue.GetDateFormatType(defineString);
if (!(formatType == DateFormatType.FormatString || formatType == DateFormatType.ReferenceDateMsec || formatType == DateFormatType.ReferenceDateSec))
{
errorString = "不属于合法的date型导出至MySQL数据库的格式类型";
return(false);
}
errorString = null;
return(true);
}
/// <summary>
/// Convert enum to wire format
/// </summary>
private byte _ToValue(DateFormatType value)
{
switch (value)
{
case DateFormatType.MonthDayYear:
{
return(1);
}
case DateFormatType.DayMonthYear:
{
return(2);
}
case DateFormatType.YearMonthDay:
{
return(3);
}
case DateFormatType.YearDayMonth:
{
return(4);
}
case DateFormatType.MonthYearDay:
{
return(5);
}
case DateFormatType.DayYearMonth:
{
return(6);
}
default:
{
throw new Exception("Unrecognized date format " + value.ToString());
}
}
}
public static string GetDateString(DateTime datetime, DateFormatType dateFormat)
{
var format = string.Empty;
if (dateFormat == DateFormatType.Year)
{
format = "yyyy年MM月";
}
else if (dateFormat == DateFormatType.Month)
{
format = "MM月dd日";
}
else if (dateFormat == DateFormatType.Day)
{
format = "yyyy-MM-dd";
}
else if (dateFormat == DateFormatType.Chinese)
{
format = "yyyy年M月d日";
}
return(datetime.ToString(format));
}
/// <summary>
/// Convert enumeration to string
/// </summary>
public static string ToString(DateFormatType value)
{
// Switch through the langauges
switch (value)
{
case DateFormatType.DayMonthYear:
{
return(DayMonthYear);
}
case DateFormatType.DayYearMonth:
{
return(DayYearMonth);
}
case DateFormatType.MonthDayYear:
{
return(MonthDayYear);
}
case DateFormatType.MonthYearDay:
{
return(MonthYearDay);
}
case DateFormatType.YearDayMonth:
{
return(YearDayMonth);
}
case DateFormatType.YearMonthDay:
{
return(YearMonthDay);
}
}
// Unknown value
throw new Exception("Unrecognized date format type \"" + value.ToString() + "\"");
}
private static NSerializer GetSerializerAs(FieldFormatterType type, FieldAccessType accessType,
DateFormatType dateFormatType, bool withNulls)
{
var currentDirectory = Directory.GetParent(Directory.GetCurrentDirectory());
var config = new Dictionary <string, object>
{
{ ConfigurationManager.MetadataDirKey, Path.Combine(currentDirectory.Parent?.Parent?.ToString(), "Resources") },
{ ConfigurationManager.IncludeNullValuesKey, withNulls },
{ ConfigurationManager.FieldFormattingMethodKey, type.ToString() },
{ ConfigurationManager.FieldAccessTypeKey, accessType.ToString() },
{ ConfigurationManager.DateFormatKey, dateFormatType.ToString() }
};
var configurationProvider = new MockConfigurationProvider(config);
var environment = new MockEnvironment();
var cache = new MockCache();
return(NSerializerBuilder.Build()
.WithCache(cache)
.WithConfigurationProvider(configurationProvider)
.WithEnvironment(environment)
.Get());
}
/// <summary>
/// Datetime을 포멧에 따라 문자열로 반환한다.
/// </summary>
/// <param name="date"></param>
/// <param name="format"></param>
/// <returns></returns>
public static string ToDateString(this DateTime date, DateFormatType format)
{
switch (format)
{
case DateFormatType.YYYY_MM_DD:
return date.ToString("yyyy-MM-dd");
case DateFormatType.YYYYMMDD:
return date.ToString("yyyyMMdd");
case DateFormatType.HHMMSS:
return date.ToString("HHmmss");
case DateFormatType.YYYY_S_MM_S_DD:
return date.ToString("yyyy/MM/dd");
case DateFormatType.YYYYMMDDHHMMSS:
return date.ToString("yyyyMMddHHmmss");
case DateFormatType.YYYY_C_MM_C_DD:
return date.ToString("yyyy:MM:dd");
case DateFormatType.YYYYMM:
return date.ToString("yyyyMM");
case DateFormatType.PARSE_DATE:
return date.ToString();
default:
return date.ToLongDateString();
}
}
public static string ToDateString(this string dateString, DateFormatType format)
{
if (dateString.IsNullOrEmpty()) return string.Empty;
if (dateString.length < 10) throw new NotImlmentException("not convert string format.");
if (dateString.IndexOf('-') <= 0)
{
dateString = dateString.Substring(0, 4) + "-" + dateString.Substring(4, 2) + "-" + dateString.Substring(6, 2);
}
if (dateString.IndexOf('.') <= 0) {
dateString = dateString.Substring(0, 4) + "-" + dateString.Substring(4, 2) + "-" + dateString.Substring(6, 2);
}
switch (format)
{
case DateFormatType.YYYY_MM_DD:
return DateTime.Parse(dateString).ToString("yyyy-MM-dd");
case DateFormatType.YYYYMMDD:
return DateTime.Parse(dateString).ToString("yyyyMMdd");
case DateFormatType.HHMMSS:
return DateTime.Parse(dateString).ToString("HHmmss");
case DateFormatType.YYYY_S_MM_S_DD:
return DateTime.Parse(dateString).ToString("yyyy/MM/dd");
case DateFormatType.YYYYMMDDHHMMSS:
return DateTime.Parse(dateString).ToString("yyyyMMddHHmmss");
case DateFormatType.YYYY_C_MM_C_DD:
return DateTime.Parse(dateString).ToString("yyyy:MM:dd");
case DateFormatType.YYYYMM:
return DateTime.Parse(dateString).ToString("yyyyMM");
case DateFormatType.PARSE_DATE:
return DateTime.Parse(dateString).ToString();
default:
throw new NotImlmentException("not support convert format.");
}
}
private static bool _CreateTable(string tableName, TableInfo tableInfo, string comment, Export export, out string errorString)
{
// 将主键列作为key生成
FieldInfo keyColumnField = tableInfo.GetKeyColumnFieldInfo();
if (keyColumnField.DatabaseFieldName == null)
{
AppLog.Log("主键未设置，已忽略导入数据库！", ConsoleColor.Yellow);
errorString = null;
return(true);
}
// 生成在创建数据表时所有字段的声明
StringBuilder fieldDefineStringBuilder = new StringBuilder();
foreach (FieldInfo fieldInfo in GetAllDatabaseFieldInfo(tableInfo))
{
// 在这里并不对每种本工具的数据类型是否能导出为指定的MySQL数据类型进行检查（比如本工具中的string型应该导出为MySQL中的文本类型如varchar，而不应该导出为数值类型）
if (fieldInfo.DataType == DataType.Date)
{
//string toDatabaseFormatDefine = fieldInfo.ExtraParam[DateToExportFormatKey].ToString();
string toDatabaseFormatDefine = export.DateToExportFormat;
DateFormatType toDatabaseFormatType = DateTimeValue.GetDateFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到MySQL中的数据类型错误，不允许为time型，如果仅需要时分秒部分，请在Excel中将该字段在本工具中的数据类型改为time型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == DateFormatType.ReferenceDateSec || toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到MySQL中的形式为距1970年的时间（{2}），但所填写的导出到MySQL中的格式为时间型的{3}，请声明为MySQL中的数值型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), toDatabaseFormatDefine, fieldInfo.DatabaseFieldType);
return(false);
}
}
}
else if (fieldInfo.DataType == DataType.Time)
{
//string toDatabaseFormatDefine = fieldInfo.ExtraParam[TimeToExportFormatKey].ToString();
string toDatabaseFormatDefine = export.TimeToExportFormat;
TimeFormatType toDatabaseFormatType = DateTimeValue.GetTimeFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到MySQL中的数据类型错误，不允许为datetime或date型，如果需要年月日部分，请在Excel中将该字段在本工具中的数据类型改为date型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec && fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到MySQL中的形式为距0点的秒数（#sec），但所填写的导出到MySQL中的格式为时间型的time，请声明为MySQL中的数值型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
}
fieldDefineStringBuilder.AppendFormat("`{0}` {1} COMMENT '{2}',", fieldInfo.DatabaseFieldName, fieldInfo.DatabaseFieldType, fieldInfo.Desc);
}
string createTableExtraParam = AppValues.ConfigData.ContainsKey(createDatabaseTableExtraParam) ? AppValues.ConfigData[createDatabaseTableExtraParam] : string.Empty;
string createTableSql = string.Format(createTableSQL, _CombineDatabaseTableFullName(tableName), fieldDefineStringBuilder.ToString(), tableInfo.GetKeyColumnFieldInfo().DatabaseFieldName, comment, createTableExtraParam);
try
{
MySqlCommand cmd = new MySqlCommand(createTableSql, conn);
cmd.ExecuteNonQuery();
errorString = null;
return(true);
}
catch (MySqlException exception)
{
errorString = exception.Message;
return(false);
}
}
private static bool _InsertData(string tableName, TableInfo tableInfo, Export export, out string errorString)
{
List <FieldInfo> allDatabaseFieldInfo = GetAllDatabaseFieldInfo(tableInfo);
// 生成所有字段名对应的定义字符串
List <string> fileNames = new List <string>();
foreach (FieldInfo fieldInfo in allDatabaseFieldInfo)
{
fileNames.Add(string.Format("`{0}`", fieldInfo.DatabaseFieldName));
}
string fieldNameDefineString = Utils.CombineString(fileNames, ", ");
bool isWriteNullForEmptyString = export.IsExportNullString;
// 逐行生成插入数据的SQL语句中的value定义部分
StringBuilder valueDefineStringBuilder = new StringBuilder();
int count = tableInfo.GetKeyColumnFieldInfo().Data.Count;
if (count > 0)
{
for (int i = 0; i < count; ++i)
{
List <string> values = new List <string>();
foreach (FieldInfo fieldInfo in allDatabaseFieldInfo)
{
if (fieldInfo.Data[i] == null)
{
values.Add("NULL");
}
else if (fieldInfo.DataType == DataType.Date)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[DateTimeTypeKey.toMySQL.ToString()].ToString();
//string toDatabaseFormatDefine = export.DateToExportFormat;
DateFormatType toDatabaseFormatType = DateTimeValue.GetDateFormatType(toDatabaseFormatDefine);
if (toDatabaseFormatType == DateFormatType.FormatString)
{
// 注意MySQL中的时间型，datetime和time型后面可用括号进行具体设置，date型没有
// MySQL中的date型插入数据时不允许含有时分秒，否则会报错，故这里强制采用MySQL默认的yyyy-MM-dd格式插入
if (fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(defaultExportDateFormat)));
}
else if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(DateTimeValue.APP_DEFAULT_DATE_FORMAT)));
}
// date型导出到MySQL中的其他数据类型字段如varchar，采用声明的指定格式
else
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(toDatabaseFormatDefine)));
}
}
else if (toDatabaseFormatType == DateFormatType.ReferenceDateSec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - DateTimeValue.REFERENCE_DATE).TotalSeconds));
}
else if (toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - DateTimeValue.REFERENCE_DATE).TotalMilliseconds));
}
else
{
errorString = "date型导出至MySQL的格式定义非法";
AppLog.LogErrorAndExit(errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Time)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[DateTimeTypeKey.toMySQL.ToString()].ToString();
//string toDatabaseFormatDefine = export.TimeToExportFormat;
TimeFormatType toDatabaseFormatType = DateTimeValue.GetTimeFormatType(toDatabaseFormatDefine);
if (toDatabaseFormatType == TimeFormatType.FormatString)
{
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(DateTimeValue.APP_DEFAULT_TIME_FORMAT)));
}
else
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(toDatabaseFormatDefine)));
}
}
else if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - DateTimeValue.REFERENCE_DATE).TotalSeconds));
}
else
{
errorString = "time型导出至MySQL的格式定义非法";
AppLog.LogErrorAndExit(errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Bool)
{
bool inputData = (bool)fieldInfo.Data[i];
// 如果数据库用bit数据类型表示bool型，比如要写入true，SQL语句中的1不能加单引号
if (fieldInfo.DatabaseFieldType.Equals("bit", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.StartsWith("bit(", StringComparison.CurrentCultureIgnoreCase))
{
if (inputData == true)
{
values.Add("1");
}
else
{
values.Add("0");
}
}
else
{
// 如果数据库用tinyint(1)数据类型表示bool型，比如要写入true，SQL语句中可以写为'1'或者不带单引号的true
if (inputData == true)
{
values.Add("'1'");
}
else
{
values.Add("'0'");
}
}
}
else if (fieldInfo.DataType == DataType.Json)
{
// json型直接向数据库写入原始json字符串，但需要对\进行转义
values.Add(string.Format("'{0}'", fieldInfo.JsonString[i]).Replace("\\", "\\\\"));
}
else if (fieldInfo.DataType == DataType.MapString)
{
// mapString型也直接写入原始mapString数据字符串，并对\进行转义
values.Add(string.Format("'{0}'", fieldInfo.JsonString[i]).Replace("\\", "\\\\"));
}
// 这里需要自行处理数据库中某些数据类型（如datetime）中不允许插入空字符串的情况，以及用户设置的string型中空单元格导出至数据库的形式
else if (string.IsNullOrEmpty(fieldInfo.Data[i].ToString()))
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase))
{
values.Add("NULL");
}
else if (fieldInfo.DataType == DataType.String && isWriteNullForEmptyString == true)
{
values.Add("NULL");
}
else
{
values.Add(string.Format("'{0}'", fieldInfo.Data[i].ToString()));
}
}
else
{
// 注意对\进行转义
values.Add(string.Format("'{0}'", fieldInfo.Data[i].ToString()).Replace("\\", "\\\\"));
}
}
valueDefineStringBuilder.AppendFormat("({0}),", Utils.CombineString(values, ","));
}
// 去掉末尾多余的逗号
string valueDefineString = valueDefineStringBuilder.ToString();
valueDefineString = valueDefineString.Substring(0, valueDefineString.Length - 1);
string insertSqlString = string.Format(insertDataSQL, _CombineDatabaseTableFullName(tableName), fieldNameDefineString, valueDefineString);
// 执行插入操作
try
{
MySqlCommand cmd = new MySqlCommand(insertSqlString, conn);
int insertCount = cmd.ExecuteNonQuery();
if (insertCount < count)
{
errorString = string.Format("需要插入{0}条数据但仅插入了{1}条", count, insertCount);
return(false);
}
else
{
errorString = null;
return(true);
}
}
catch (MySqlException exception)
{
errorString = exception.Message;
return(false);
}
}
else
{
errorString = null;
return(true);
}
}
private static string GetDateAndTimeString(DateTime datetime, DateFormatType dateFormat, TimeFormatType timeFormat)
{
return($"{GetDateString(datetime, dateFormat)} {GetTimeString(datetime, timeFormat)}");
}
private static void _GetOneFieldTxtContent(FieldInfo fieldInfo, Export export, List <StringBuilder> rowContentList)
{
int rowCount = fieldInfo.Data.Count;
switch (fieldInfo.DataType)
{
case DataType.Int:
case DataType.Long:
case DataType.Float:
case DataType.String:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
// 先增加与上一字段间的分隔符
stringBuilder.Append(export.ExportSpaceString);
// 再生成本行对应的内容
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(fieldInfo.Data[row].ToString().Replace("\n", "\\n"));
}
}
break;
}
case DataType.Lang:
case DataType.TableString:
case DataType.MapString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
// 先增加与上一字段间的分隔符
stringBuilder.Append(export.ExportSpaceString);
// 再生成本行对应的内容
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(fieldInfo.Data[row].ToString().Replace("\n", "\\n"));
}
}
break;
}
case DataType.Bool:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
if ((bool)fieldInfo.Data[row] == true)
{
stringBuilder.Append("true");
}
else
{
stringBuilder.Append("false");
}
}
}
break;
}
case DataType.Json:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(fieldInfo.JsonString[row]);
}
}
break;
}
case DataType.Date:
{
DateFormatType dateFormatType = DateTimeValue.GetDateFormatType(fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()].ToString());
string exportFormatString = null;
// 若date型声明toLua的格式为dateTable，则按input格式进行导出
if (dateFormatType == DateFormatType.DataTable)
{
dateFormatType = DateTimeValue.GetDateFormatType(fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()].ToString());
exportFormatString = fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()].ToString();
}
else
{
exportFormatString = fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()].ToString();
}
switch (dateFormatType)
{
case DateFormatType.FormatString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row])).ToString(exportFormatString));
}
}
break;
}
case DateFormatType.ReferenceDateSec:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalSeconds);
}
}
break;
}
case DateFormatType.ReferenceDateMsec:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalMilliseconds);
}
}
break;
}
default:
{
AppLog.LogErrorAndExit("用_GetOneFieldTxtContent函数导出txt文件的date型的DateFormatType非法");
break;
}
}
break;
}
case DataType.Time:
{
TimeFormatType timeFormatType = DateTimeValue.GetTimeFormatType(fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()].ToString());
switch (timeFormatType)
{
case TimeFormatType.FormatString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row])).ToString(fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()].ToString()));
}
}
break;
}
case TimeFormatType.ReferenceTimeSec:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalSeconds);
}
}
break;
}
default:
{
AppLog.LogErrorAndExit("错误：用_GetOneFieldTxtContent函数导出txt文件的time型的TimeFormatType非法");
break;
}
}
break;
}
case DataType.Array:
case DataType.Dict:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(export.ExportSpaceString);
if ((bool)fieldInfo.Data[row] == false)
{
stringBuilder.Append("-1");
}
}
break;
}
default:
{
AppLog.LogErrorAndExit(string.Format("_GetOneFieldTxtContent函数中未定义{0}类型数据导出至txt文件的形式", fieldInfo.DataType));
break;
}
}
}
private static string _GetDateValue(FieldInfo fieldInfo, int row, int level)
{
StringBuilder content = new StringBuilder();
DateFormatType dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString());
switch (dateFormatType)
{
case DateFormatType.FormatString:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append("\"").Append(((DateTime)(fieldInfo.Data[row])).ToString(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString())).Append("\"");
}
break;
}
case DateFormatType.ReferenceDateSec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE_LOCAL).TotalSeconds);
}
break;
}
case DateFormatType.ReferenceDateMsec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE_LOCAL).TotalMilliseconds);
}
break;
}
case DateFormatType.DataTable:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
double totalSeconds = ((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE_LOCAL).TotalSeconds;
content.Append("os.date(\"!*t\", ").Append(totalSeconds).Append(")");
}
break;
}
default:
{
Utils.LogErrorAndExit("错误：用_GetDateValue函数导出lua文件的date型的DateFormatType非法");
break;
}
}
return(content.ToString());
}
private static string _GetDateValue(FieldInfo fieldInfo, int row)
{
StringBuilder content = new StringBuilder();
DateFormatType dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString());
string exportFormatString = null;
// 若date型声明toLua的格式为dateTable，则按input格式进行导出
if (dateFormatType == DateFormatType.DataTable)
{
dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_INPUT_FORMAT].ToString());
exportFormatString = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_INPUT_FORMAT].ToString();
}
else
{
exportFormatString = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString();
}
switch (dateFormatType)
{
case DateFormatType.FormatString:
{
if (fieldInfo.Data[row] == null)
{
content.Append("null");
}
else
{
content.Append("\"").Append(((DateTime)(fieldInfo.Data[row])).ToString(exportFormatString)).Append("\"");
}
break;
}
case DateFormatType.ReferenceDateSec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("null");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE).TotalSeconds);
}
break;
}
case DateFormatType.ReferenceDateMsec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("null");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE).TotalMilliseconds);
}
break;
}
default:
{
Utils.LogErrorAndExit("错误：用_GetDateValue函数导出json文件的date型的DateFormatType非法");
break;
}
}
return(content.ToString());
}
/// <summary>
/// 解析date型数据的定义
/// </summary>
private static bool _AnalyzeDateType(FieldInfo fieldInfo, TableInfo tableInfo, DataTable dt, int columnIndex, FieldInfo parentField, out int nextFieldColumnIndex, out string errorString)
{
// 解析date型输入导出格式的声明
if (!DateTimeValue.DefineDateStartString.Equals(fieldInfo.DataTypeString, StringComparison.CurrentCultureIgnoreCase))
{
int leftBracketIndex = fieldInfo.DataTypeString.IndexOf('(');
int rightBracketIndex = fieldInfo.DataTypeString.LastIndexOf(')');
if (leftBracketIndex == -1 && rightBracketIndex == -1)
{
errorString = "date型格式定义错误";
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
if (!(leftBracketIndex != -1 || rightBracketIndex > leftBracketIndex))
{
errorString = "date型格式定义错误，括号不匹配";
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
// 解析声明的时间格式
string defineString = fieldInfo.DataTypeString.Substring(leftBracketIndex + 1, rightBracketIndex - leftBracketIndex - 1).Trim();
if (string.IsNullOrEmpty(defineString))
{
errorString = "date型格式定义错误，若要声明时间格式就必须在括号中填写，否则不要加括号，本工具会采用config配置文件中设置的默认时间格式";
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
// 通过|分隔各个时间参数
string[] defineParams = defineString.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
StringBuilder paramDefineErrorStringBuilder = new StringBuilder();
const string ERROR_STRING_FORMAT = "配置项\"{0}\"设置的格式\"{1}\"错误：{2}\n";
foreach (string defineParam in defineParams)
{
// 通过=分隔参数项的key和value
string[] paramKeyAndValue = defineParam.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
if (paramKeyAndValue.Length != 2)
{
paramDefineErrorStringBuilder.AppendFormat("配置参数\"{0}\"未正确用=分隔配置项的key和value\n", defineParam);
continue;
}
string paramKey = paramKeyAndValue[0].Trim();
string paramValue = paramKeyAndValue[1].Trim();
errorString = null;
if (!Enum.IsDefined(typeof(DateTimeTypeKey), paramKey))
{
paramDefineErrorStringBuilder.AppendFormat("存在非法配置项key\"{0}\"\n", paramKey);
}
else if (TableCheckHelper.CheckDateInputDefine(paramValue, out errorString) == true)
{
fieldInfo.ExtraParam[paramKey] = paramValue;
}
else
{
paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
}
//switch (paramKey)
//{
// case DateTimeValue.DateInputParamKey:
// {
// if (TableCheckHelper.CheckDateInputDefine(paramValue, out errorString) == true)
// fieldInfo.ExtraParam[DateTimeValue.DateInputFormat] = paramValue;
// else
// paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
// break;
// }
//case LuaStruct.DateToExportParamKey:
// {
// if (TableCheckHelper.CheckDateToLuaDefine(paramValue, out errorString) == true)
// fieldInfo.ExtraParam[LuaStruct.DateToExportFormatKey] = paramValue;
// else
// paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
// break;
// }
//case MySQLStruct.DateToExportParamKey:
// {
// if (TableCheckHelper.CheckDateToDatabaseDefine(paramValue, out errorString) == true)
// fieldInfo.ExtraParam[MySQLStruct.DateToExportFormatKey] = paramValue;
// else
// paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
// break;
// }
// default:
// {
// paramDefineErrorStringBuilder.AppendFormat("存在非法配置项key\"{0}\"\n", paramKey);
// break;
// }
//}
}
string paramDefineErrorString = paramDefineErrorStringBuilder.ToString();
if (!string.IsNullOrEmpty(paramDefineErrorString))
{
errorString = string.Format("date型格式定义存在以下错误：\n{0}", paramDefineErrorString);
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
}
// 检查date型输入格式、导出至lua文件格式、导出至MySQL数据库格式是否都已声明，没有则分别采用config文件的默认设置
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.input.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toLua.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toLua.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toJson.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toJson.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toErlang.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toErlang.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toServerJson.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toServerJson.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toMySQL.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toMySQL.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toSQLITE.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toSQLITE.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toTxt.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toTxt.ToString()] = DateTimeValue.DefaultDateInputFormat;
}
DateFormatType dateFormatType = DateTimeValue.GetDateFormatType((string)fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()]);
fieldInfo.Data = new List <object>();
// 记录非法数据的行号以及数据值（key：行号， value：数据值）
Dictionary <int, object> invalidInfo = new Dictionary <int, object>();
if (dateFormatType == DateFormatType.FormatString)
{
// 用于对时间格式进行转换
DateTimeFormatInfo dateTimeFormat = new DateTimeFormatInfo();
dateTimeFormat.ShortDatePattern = (string)fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()];
for (int row = ExcelTableSetting.DataFieldDataStartRowIndex; row < dt.Rows.Count; ++row)
{
// 如果本行该字段的父元素标记为无效则该数据也标为无效
if (parentField != null && (bool)parentField.Data[row - ExcelTableSetting.DataFieldDataStartRowIndex] == false)
{
fieldInfo.Data.Add(null);
}
else
{
string inputData = dt.Rows[row][columnIndex].ToString().Trim();
// 忽略未填写的数据
if (string.IsNullOrEmpty(inputData))
{
fieldInfo.Data.Add(null);
}
else
{
try
{
DateTime dateTime = Convert.ToDateTime(inputData, dateTimeFormat);
fieldInfo.Data.Add(dateTime);
}
catch
{
invalidInfo.Add(row, inputData);
}
}
}
}
}
else if (dateFormatType == DateFormatType.ReferenceDateSec)
{
for (int row = ExcelTableSetting.DataFieldDataStartRowIndex; row < dt.Rows.Count; ++row)
{
// 如果本行该字段的父元素标记为无效则该数据也标为无效
if (parentField != null && (bool)parentField.Data[row - ExcelTableSetting.DataFieldDataStartRowIndex] == false)
{
fieldInfo.Data.Add(null);
}
else
{
string inputData = dt.Rows[row][columnIndex].ToString().Trim();
ulong inputLongValue = 0;
if (string.IsNullOrEmpty(inputData))
{
fieldInfo.Data.Add(null);
}
else if (ulong.TryParse(inputData, out inputLongValue) == false)
{
invalidInfo.Add(row, inputData);
}
else
{
DateTime dateTime = DateTimeValue.REFERENCE_DATE.AddSeconds((double)inputLongValue);
fieldInfo.Data.Add(dateTime);
}
}
}
}
else if (dateFormatType == DateFormatType.ReferenceDateMsec)
{
for (int row = ExcelTableSetting.DataFieldDataStartRowIndex; row < dt.Rows.Count; ++row)
{
// 如果本行该字段的父元素标记为无效则该数据也标为无效
if (parentField != null && (bool)parentField.Data[row - ExcelTableSetting.DataFieldDataStartRowIndex] == false)
{
fieldInfo.Data.Add(null);
}
else
{
string inputData = dt.Rows[row][columnIndex].ToString().Trim();
ulong inputLongValue = 0;
if (string.IsNullOrEmpty(inputData))
{
fieldInfo.Data.Add(null);
}
else if (ulong.TryParse(inputData, out inputLongValue) == false)
{
invalidInfo.Add(row, inputData);
}
else
{
DateTime dateTime = DateTimeValue.REFERENCE_DATE.AddMilliseconds((double)inputLongValue);
fieldInfo.Data.Add(dateTime);
}
}
}
}
else
{
errorString = "错误：用_AnalyzeDateType函数处理非法的DateFormatType类型";
AppLog.LogErrorAndExit(errorString);
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
if (invalidInfo.Count > 0)
{
StringBuilder invalidDataInfo = new StringBuilder();
invalidDataInfo.AppendFormat("以下行中的数据无法按指定的输入格式（{0}）进行读取\n", fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()]);
foreach (var item in invalidInfo)
{
invalidDataInfo.AppendFormat("第{0}行，错误地填写数据为\"{1}\"\n", item.Key + 1, item.Value);
}
errorString = invalidDataInfo.ToString();
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
else
{
errorString = null;
nextFieldColumnIndex = columnIndex + 1;
return(true);
}
}
private static void _GetOneFieldCsvContent(FieldInfo fieldInfo, List <StringBuilder> rowContentList)
{
int rowCount = fieldInfo.Data.Count;
switch (fieldInfo.DataType)
{
case DataType.Int:
case DataType.Long:
case DataType.Float:
case DataType.String:
case DataType.Lang:
case DataType.TableString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
// 先增加与上一字段间的分隔符
stringBuilder.Append(AppValues.ExportCsvSplitString);
// 再生成本行对应的内容
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(fieldInfo.Data[row].ToString());
}
}
break;
}
case DataType.Bool:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
if ((bool)fieldInfo.Data[row] == true)
{
stringBuilder.Append("true");
}
else
{
stringBuilder.Append("false");
}
}
}
break;
}
case DataType.Json:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(fieldInfo.JsonString[row]);
}
}
break;
}
case DataType.MapString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(fieldInfo.JsonString[row]);
}
}
break;
}
case DataType.Date:
{
DateFormatType dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString());
string exportFormatString = null;
// 若date型声明toLua的格式为dateTable，则按input格式进行导出
if (dateFormatType == DateFormatType.DataTable)
{
dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_INPUT_FORMAT].ToString());
exportFormatString = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_INPUT_FORMAT].ToString();
}
else
{
exportFormatString = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString();
}
switch (dateFormatType)
{
case DateFormatType.FormatString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row])).ToString(exportFormatString));
}
}
break;
}
case DateFormatType.ReferenceDateSec:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE_LOCAL).TotalSeconds);
}
}
break;
}
case DateFormatType.ReferenceDateMsec:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE_LOCAL).TotalMilliseconds);
}
}
break;
}
default:
{
Utils.LogErrorAndExit("用_GetOneFieldCsvContent函数导出csv文件的date型的DateFormatType非法");
break;
}
}
break;
}
case DataType.Time:
{
TimeFormatType timeFormatType = TableAnalyzeHelper.GetTimeFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_LUA_FORMAT].ToString());
switch (timeFormatType)
{
case TimeFormatType.FormatString:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row])).ToString(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_LUA_FORMAT].ToString()));
}
}
break;
}
case TimeFormatType.ReferenceTimeSec:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if (fieldInfo.Data[row] != null)
{
stringBuilder.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE).TotalSeconds);
}
}
break;
}
default:
{
Utils.LogErrorAndExit("错误：用_GetOneFieldCsvContent函数导出csv文件的time型的TimeFormatType非法");
break;
}
}
break;
}
case DataType.Array:
case DataType.Dict:
{
for (int row = 0; row < rowCount; ++row)
{
StringBuilder stringBuilder = rowContentList[row];
stringBuilder.Append(AppValues.ExportCsvSplitString);
if ((bool)fieldInfo.Data[row] == false)
{
stringBuilder.Append("-1");
}
}
break;
}
default:
{
Utils.LogErrorAndExit(string.Format("_GetOneFieldCsvContent函数中未定义{0}类型数据导出至csv文件的形式", fieldInfo.DataType));
break;
}
}
}
private static bool _InsertData(string tableName, TableInfo tableInfo, out string errorString)
{
List <FieldInfo> allDatabaseFieldInfo = GetAllDatabaseFieldInfo(tableInfo);
// 生成所有字段名对应的定义字符串
List <string> fileNames = new List <string>();
foreach (FieldInfo fieldInfo in allDatabaseFieldInfo)
{
fileNames.Add(string.Format("{0}", _CombineDatabaseTableFullName(fieldInfo.DatabaseFieldName)));
}
string fieldNameDefineString = Utils.CombineString(fileNames, ", ");
// 用户是否配置该表中string型字段中的空单元格导出至SQLite中为NULL，默认为空字符串
bool isWriteNullForEmptyString = tableInfo.TableConfigData2 != null && tableInfo.TableConfigData2.ContainsKey(SQLiteStruct.CONFIG_NAME_EXPORT_DATABASE_WRITE_NULL_FOR_EMPTY_STRING) && tableInfo.TableConfigData2[SQLiteStruct.CONFIG_NAME_EXPORT_DATABASE_WRITE_NULL_FOR_EMPTY_STRING].Count > 0 && "true".Equals(tableInfo.TableConfigData2[SQLiteStruct.CONFIG_NAME_EXPORT_DATABASE_WRITE_NULL_FOR_EMPTY_STRING][0], StringComparison.CurrentCultureIgnoreCase);
// 逐行生成插入数据的SQL语句中的value定义部分
StringBuilder valueDefineStringBuilder = new StringBuilder();
int count = tableInfo.GetKeyColumnFieldInfo().Data.Count;
if (count > 0)
{
for (int i = 0; i < count; ++i)
{
List <string> values = new List <string>();
foreach (FieldInfo fieldInfo in allDatabaseFieldInfo)
{
if (fieldInfo.Data[i] == null)
{
values.Add("NULL");
}
else if (fieldInfo.DataType == DataType.Date)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[SQLiteStruct.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_DATABASE_FORMAT].ToString();
DateFormatType toDatabaseFormatType = TableAnalyzeHelper.GetDateFormatType(toDatabaseFormatDefine);
if (toDatabaseFormatType == DateFormatType.FormatString)
{
// 注意SQLite中的时间型，datetime和time型后面可用括号进行具体设置，date型没有
// SQLite中的date型插入数据时不允许含有时分秒，否则会报错，故这里强制采用SQLite默认的yyyy-MM-dd格式插入
if (fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(SQLiteStruct.APP_DEFAULT_ONLY_DATE_FORMAT)));
}
else if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(SQLiteStruct.APP_DEFAULT_DATE_FORMAT)));
}
// date型导出到SQLite中的其他数据类型字段如varchar，采用声明的指定格式
else
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(fieldInfo.ExtraParam[SQLiteStruct.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_DATABASE_FORMAT].ToString())));
}
}
else if (toDatabaseFormatType == DateFormatType.ReferenceDateSec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - DateTimeValue.REFERENCE_DATE).TotalSeconds));
}
else if (toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - DateTimeValue.REFERENCE_DATE).TotalMilliseconds));
}
else
{
errorString = "date型导出至SQLite的格式定义非法";
AppLog.LogErrorAndExit(errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Time)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[SQLiteStruct.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_DATABASE_FORMAT].ToString();
TimeFormatType toDatabaseFormatType = TableAnalyzeHelper.GetTimeFormatType(toDatabaseFormatDefine);
if (toDatabaseFormatType == TimeFormatType.FormatString)
{
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(SQLiteStruct.APP_DEFAULT_TIME_FORMAT)));
}
else
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(fieldInfo.ExtraParam[SQLiteStruct.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_DATABASE_FORMAT].ToString())));
}
}
else if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - DateTimeValue.REFERENCE_DATE).TotalSeconds));
}
else
{
errorString = "time型导出至SQLite的格式定义非法";
AppLog.LogErrorAndExit(errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Bool)
{
bool inputData = (bool)fieldInfo.Data[i];
// 如果数据库用bit数据类型表示bool型，比如要写入true，SQL语句中的1不能加单引号
if (fieldInfo.DatabaseFieldType.Equals("bit", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.StartsWith("bit(", StringComparison.CurrentCultureIgnoreCase))
{
if (inputData == true)
{
values.Add("1");
}
else
{
values.Add("0");
}
}
else
{
// 如果数据库用tinyint(1)数据类型表示bool型，比如要写入true，SQL语句中可以写为'1'或者不带单引号的true
if (inputData == true)
{
values.Add("'1'");
}
else
{
values.Add("'0'");
}
}
}
else if (fieldInfo.DataType == DataType.Json)
{
// json型直接向数据库写入原始json字符串，但需要对\进行转义
values.Add(string.Format("'{0}'", fieldInfo.JsonString[i]).Replace("\\", "\\\\"));
}
else if (fieldInfo.DataType == DataType.MapString)
{
// mapString型也直接写入原始mapString数据字符串，并对\进行转义
values.Add(string.Format("'{0}'", fieldInfo.JsonString[i]).Replace("\\", "\\\\"));
}
// 这里需要自行处理数据库中某些数据类型（如datetime）中不允许插入空字符串的情况，以及用户设置的string型中空单元格导出至数据库的形式
else if (string.IsNullOrEmpty(fieldInfo.Data[i].ToString()))
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase))
{
values.Add("NULL");
}
else if (fieldInfo.DataType == DataType.String && isWriteNullForEmptyString == true)
{
values.Add("NULL");
}
else
{
values.Add(string.Format("{0}", fieldInfo.Data[i].ToString()));
}
}
else
{
// 注意对\进行转义
values.Add(string.Format("'{0}'", fieldInfo.Data[i].ToString()).Replace("\\", "\\\\"));
}
}
valueDefineStringBuilder.AppendFormat("({0}),", Utils.CombineString(values, ","));
}
// 去掉末尾多余的逗号
string valueDefineString = valueDefineStringBuilder.ToString();
valueDefineString = valueDefineString.Substring(0, valueDefineString.Length - 1);
string insertSqlString = string.Format(_INSERT_DATA_SQL, tableName, fieldNameDefineString, valueDefineString);
// 执行插入操作
try
{
SQLiteCommand cmd = new SQLiteCommand(insertSqlString, _conn);
int insertCount = cmd.ExecuteNonQuery();
if (insertCount < count)
{
errorString = string.Format("需要插入{0}条数据但仅插入了{1}条", count, insertCount);
return(false);
}
else
{
errorString = null;
return(true);
}
}
catch (SQLiteException exception)
{
errorString = exception.Message;
return(false);
}
}
else
{
errorString = null;
return(true);
}
}
/// <summary>
/// Convert enum to wire format
/// </summary>
private byte _ToValue(DateFormatType value)
{
switch (value)
{
case DateFormatType.MonthDayYear:
{
return 1;
}
case DateFormatType.DayMonthYear:
{
return 2;
}
case DateFormatType.YearMonthDay:
{
return 3;
}
case DateFormatType.YearDayMonth:
{
return 4;
}
case DateFormatType.MonthYearDay:
{
return 5;
}
case DateFormatType.DayYearMonth:
{
return 6;
}
default:
{
throw new Exception("Unrecognized date format " + value.ToString());
}
}
}
private static string _GetCsClassFieldDefine(FieldInfo fieldInfo)
{
StringBuilder stringBuilder = new StringBuilder();
switch (fieldInfo.DataType)
{
case DataType.Int:
case DataType.Long:
case DataType.Float:
case DataType.Bool:
case DataType.String:
{
stringBuilder.Append(fieldInfo.DataType.ToString().ToLower());
break;
}
case DataType.Lang:
{
stringBuilder.Append("string");
break;
}
case DataType.Json:
{
stringBuilder.Append("LitJson.JsonData");
break;
}
case DataType.Array:
{
stringBuilder.Append("List<");
stringBuilder.Append(_GetCsClassFieldDefine(fieldInfo.ChildField[0]));
stringBuilder.Append(">");
break;
}
case DataType.Dict:
{
// 如果dict型下属各字段都是同一种类型，则Dictionary的值类型可以具体指定，否则只能设为通用的Object
bool isSameChildDataType = true;
DataType firstDataType = fieldInfo.ChildField[0].DataType;
foreach (FieldInfo childFieldInfo in fieldInfo.ChildField)
{
if (childFieldInfo.DataType != firstDataType)
{
isSameChildDataType = false;
break;
}
}
if (isSameChildDataType == true)
{
stringBuilder.Append("Dictionary<string, ");
stringBuilder.Append(_GetCsClassFieldDefine(fieldInfo.ChildField[0]));
stringBuilder.Append(">");
}
else
{
stringBuilder.Append("Dictionary<string, object>");
}
break;
}
case DataType.Date:
{
DateFormatType dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_LUA_FORMAT].ToString());
if (dateFormatType == DateFormatType.ReferenceDateSec)
{
stringBuilder.Append("int");
}
else if (dateFormatType == DateFormatType.ReferenceDateMsec)
{
stringBuilder.Append("long");
}
else
{
stringBuilder.Append("DateTime");
}
break;
}
case DataType.Time:
{
TimeFormatType timeFormatType = TableAnalyzeHelper.GetTimeFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_LUA_FORMAT].ToString());
if (timeFormatType == TimeFormatType.ReferenceTimeSec)
{
stringBuilder.Append("int");
}
else
{
stringBuilder.Append("DateTime");
}
break;
}
case DataType.TableString:
{
if (fieldInfo.TableStringFormatDefine.KeyDefine.KeyType == TableStringKeyType.Seq)
{
stringBuilder.Append("List<");
switch (fieldInfo.TableStringFormatDefine.ValueDefine.ValueType)
{
case TableStringValueType.True:
{
stringBuilder.Append("bool");
break;
}
case TableStringValueType.Table:
{
stringBuilder.Append("Dictionary<string, object>");
break;
}
case TableStringValueType.DataInIndex:
{
string valueDataTypeString = _GetCsClassTableStringDataType(fieldInfo.TableStringFormatDefine.ValueDefine.DataInIndexDefine.DataType);
if (valueDataTypeString == null)
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件中tableString型字段的seq型key的dataInIndex型value的数据类型非法");
}
else
{
stringBuilder.Append(valueDataTypeString);
}
break;
}
default:
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件中tableString型字段的seq型key的value类型非法");
break;
}
}
stringBuilder.Append(">");
}
else if (fieldInfo.TableStringFormatDefine.KeyDefine.KeyType == TableStringKeyType.DataInIndex)
{
stringBuilder.Append("Dictionary<");
// key
string keyDataTypeString = _GetCsClassTableStringDataType(fieldInfo.TableStringFormatDefine.KeyDefine.DataInIndexDefine.DataType);
if (keyDataTypeString == null)
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件中tableString型字段的dataInIndex型key的数据类型非法");
}
else
{
stringBuilder.Append(keyDataTypeString);
}
stringBuilder.Append(" ,");
// value
switch (fieldInfo.TableStringFormatDefine.ValueDefine.ValueType)
{
case TableStringValueType.True:
{
stringBuilder.Append("bool");
break;
}
case TableStringValueType.Table:
{
stringBuilder.Append("Dictionary<string, object>");
break;
}
case TableStringValueType.DataInIndex:
{
string valueDataTypeString = _GetCsClassTableStringDataType(fieldInfo.TableStringFormatDefine.KeyDefine.DataInIndexDefine.DataType);
if (valueDataTypeString == null)
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件中tableString型字段的dataInIndex型key的dataInIndex型value的数据类型非法");
}
else
{
stringBuilder.Append(valueDataTypeString);
}
break;
}
default:
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件中tableString型字段的dataInIndex型key的value类型非法");
break;
}
}
stringBuilder.Append(">");
}
else
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件中tableString型字段的key非法");
}
break;
}
case DataType.MapString:
{
stringBuilder.Append("LitJson.JsonData");
break;
}
default:
{
Utils.LogErrorAndExit("用_GetCsClassFieldDefine函数导出csv对应C#类文件的字段DataType非法");
break;
}
}
return(stringBuilder.ToString());
}
private static string _GetDateValue(FieldInfo fieldInfo, int row, int level)
{
StringBuilder content = new StringBuilder();
DateFormatType dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[LuaStruct.DateToExportFormatKey].ToString());
switch (dateFormatType)
{
case DateFormatType.FormatString:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append("\"").Append(((DateTime)(fieldInfo.Data[row])).ToString(fieldInfo.ExtraParam[LuaStruct.DateToExportFormatKey].ToString())).Append("\"");
}
break;
}
case DateFormatType.ReferenceDateSec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalSeconds);
}
break;
}
case DateFormatType.ReferenceDateMsec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalMilliseconds);
}
break;
}
case DateFormatType.DataTable:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
double totalSeconds = ((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalSeconds;
content.Append("os.date(\"!*t\", ").Append(totalSeconds).Append(")");
}
break;
}
default:
{
AppLog.LogErrorAndExit("错误：用_GetDateValue函数导出lua文件的date型的DateFormatType非法");
break;
}
}
return(content.ToString());
}
private static string _GetDateValue(FieldInfo fieldInfo, int row)
{
StringBuilder content = new StringBuilder();
DateFormatType dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[LuaStruct.DateToExportFormatKey].ToString());
string exportFormatString = null;
// 若date型声明toLua的格式为dateTable，则按input格式进行导出
if (dateFormatType == DateFormatType.DataTable)
{
dateFormatType = TableAnalyzeHelper.GetDateFormatType(fieldInfo.ExtraParam[DateTimeValue.DateInputFormat].ToString());
exportFormatString = fieldInfo.ExtraParam[DateTimeValue.DateInputFormat].ToString();
}
else
{
exportFormatString = fieldInfo.ExtraParam[LuaStruct.DateToExportFormatKey].ToString();
}
switch (dateFormatType)
{
case DateFormatType.FormatString:
{
if (fieldInfo.Data[row] == null)
{
content.Append("null");
}
else
{
content.Append("\"").Append(((DateTime)(fieldInfo.Data[row])).ToString(exportFormatString)).Append("\"");
}
break;
}
case DateFormatType.ReferenceDateSec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("null");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE_LOCAL).TotalSeconds);
}
break;
}
case DateFormatType.ReferenceDateMsec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("null");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE_LOCAL).TotalMilliseconds);
}
break;
}
default:
{
AppLog.LogErrorAndExit("错误：用_GetDateValue函数导出json文件的date型的DateFormatType非法");
break;
}
}
return(content.ToString());
}
private static bool _InsertData(string tableName, TableInfo tableInfo, out string errorString)
{
List <FieldInfo> allDatabaseFieldInfo = GetAllDatabaseFieldInfo(tableInfo);
// 生成所有字段名对应的定义字符串
List <string> fileNames = new List <string>();
foreach (FieldInfo fieldInfo in allDatabaseFieldInfo)
{
fileNames.Add(string.Format("`{0}`", fieldInfo.DatabaseFieldName));
}
string fieldNameDefineString = Utils.CombineString(fileNames, ", ");
// 逐行生成插入数据的SQL语句中的value定义部分
StringBuilder valueDefineStringBuilder = new StringBuilder();
int count = tableInfo.GetKeyColumnFieldInfo().Data.Count;
for (int i = 0; i < count; ++i)
{
List <string> values = new List <string>();
foreach (FieldInfo fieldInfo in allDatabaseFieldInfo)
{
if (fieldInfo.Data[i] == null)
{
values.Add("NULL");
}
else if (fieldInfo.DataType == DataType.Date)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_DATABASE_FORMAT].ToString();
DateFormatType toDatabaseFormatType = TableAnalyzeHelper.GetDateFormatType(toDatabaseFormatDefine);
if (toDatabaseFormatType == DateFormatType.FormatString)
{
// 注意MySQL中的时间型，datetime和time型后面可用括号进行具体设置，date型没有
// MySQL中的date型插入数据时不允许含有时分秒，否则会报错，故这里强制采用MySQL默认的yyyy-MM-dd格式插入
if (fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(AppValues.APP_DEFAULT_ONLY_DATE_FORMAT)));
}
else if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(AppValues.APP_DEFAULT_DATE_FORMAT)));
}
// date型导出到MySQL中的其他数据类型字段如varchar，采用声明的指定格式
else
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_DATABASE_FORMAT].ToString())));
}
}
else if (toDatabaseFormatType == DateFormatType.ReferenceDateSec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - AppValues.REFERENCE_DATE).TotalSeconds));
}
else if (toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - AppValues.REFERENCE_DATE).TotalMilliseconds));
}
else
{
errorString = "date型导出至MySQL的格式定义非法";
Utils.LogErrorAndExit(errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Time)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_DATABASE_FORMAT].ToString();
TimeFormatType toDatabaseFormatType = TableAnalyzeHelper.GetTimeFormatType(toDatabaseFormatDefine);
if (toDatabaseFormatType == TimeFormatType.FormatString)
{
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(AppValues.APP_DEFAULT_TIME_FORMAT)));
}
else
{
values.Add(string.Format("'{0}'", ((DateTime)(fieldInfo.Data[i])).ToString(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_DATABASE_FORMAT].ToString())));
}
}
else if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec)
{
values.Add(string.Format("'{0}'", ((DateTime)fieldInfo.Data[i] - AppValues.REFERENCE_DATE).TotalSeconds));
}
else
{
errorString = "time型导出至MySQL的格式定义非法";
Utils.LogErrorAndExit(errorString);
return(false);
}
}
// 这里需要自行处理向数据库中某些数据类型如datetime的列不允许插入空字符串的情况
else if (string.IsNullOrEmpty(fieldInfo.Data[i].ToString()))
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase))
{
values.Add("NULL");
}
else
{
values.Add(string.Format("'{0}'", fieldInfo.Data[i].ToString()));
}
}
else if (fieldInfo.DataType == DataType.Bool)
{
string inputData = fieldInfo.Data[i].ToString();
// 如果数据库用tinyint(1)数据类型表示bool型，比如要写入true，SQL语句中可以写为'1'或者不带单引号的true
if ("true".Equals(inputData, StringComparison.CurrentCultureIgnoreCase))
{
values.Add("'1'");
}
else if ("false".Equals(inputData, StringComparison.CurrentCultureIgnoreCase))
{
values.Add("'0'");
}
else
{
values.Add(string.Format("'{0}'", fieldInfo.Data[i].ToString()));
}
}
else
{
values.Add(string.Format("'{0}'", fieldInfo.Data[i].ToString()));
}
}
valueDefineStringBuilder.AppendFormat("({0}),", Utils.CombineString(values, ","));
}
// 去掉末尾多余的逗号
string valueDefineString = valueDefineStringBuilder.ToString();
valueDefineString = valueDefineString.Substring(0, valueDefineString.Length - 1);
string insertSqlString = string.Format(_INSERT_DATA_SQL, _CombineDatabaseTableFullName(tableName), fieldNameDefineString, valueDefineString);
// 执行插入操作
try
{
MySqlCommand cmd = new MySqlCommand(insertSqlString, _conn);
int insertCount = cmd.ExecuteNonQuery();
if (insertCount < count)
{
errorString = string.Format("需要插入{0}条数据但仅插入了{1}条");
return(false);
}
else
{
errorString = null;
return(true);
}
}
catch (MySqlException exception)
{
errorString = exception.Message;
return(false);
}
}
public static String GET_FORMAT(DataTypeNames dateTypeName,
[Literal] DateFormatType formatType)
{
throw new NotSupportedException();
}
