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);
}
}
public static string GetFormatType(TimeFormatType timeType)
{
string dataTime = "";
if (timeType == TimeFormatType.YearMonthDateTime)
{
dataTime = "yyyy/MM/dd H:mm:ss";
}
else if (timeType == TimeFormatType.YearMonthDate)
{
dataTime = "yyyyMMdd";
}
else if (timeType == TimeFormatType.YearSMonthSDate)
{
dataTime = "yyyy/MM/dd";
}
else if (timeType == TimeFormatType.YearMonthDateTimeChange)
{
dataTime = "yyyyMMdd tt H:mm:ss";
}
else if (timeType == TimeFormatType.YearSMonthSDateTimeChange)
{
dataTime = "yyyy/MM/dd tt H:mm:ss";
}
else
{
dataTime = "yyyyMM ";
}
return(dataTime);
}
/// <summary>
/// 格式化时间
/// </summary>
/// <param name="seconds"></param>
/// <param name="type"></param>
/// <returns></returns>
public static string FormatTime(long seconds, TimeFormatType type = TimeFormatType.Second)
{
switch (type)
{
case TimeFormatType.Second:
return FormatTimeToSecond(seconds);
case TimeFormatType.Minute:
return FormatTimeToMinute(seconds);
case TimeFormatType.Hour:
return FormatTimeToHour(seconds);
}
return seconds.ToString();
}
public static string ToStringMinuteWithoutS(this TimeSpan span, TimeFormatType formatType = TimeFormatType.Digital)
{
TimeSpan t = span.Add(TimeSpan.FromSeconds(1));
if (t.TotalSeconds < 0)
{
return("00");
}
else
{
var format = formatType == TimeFormatType.Digital? "{0:D2}": Localization.Get("MinuteTimeDetailWithoutSFormat");
return(string.Format(format, (int)t.TotalMinutes));
}
}
public static string ToStringDay(this TimeSpan span, TimeFormatType formatType = TimeFormatType.Digital)
{
TimeSpan t = span.Add(TimeSpan.FromSeconds(1));
if (t.TotalSeconds < 0)
{
return("0.00:00:00");
}
else
{
var format = formatType == TimeFormatType.Digital? "{0:D}.{1:D2}:{2:D2}:{3:D2}": Localization.Get("DayTimeDetailFormat");
return(string.Format(format, (int)t.TotalDays, t.Hours, t.Minutes, t.Seconds));
}
}
private static string GetTimeString(DateTime datetime, TimeFormatType timeFormat)
{
var retVal = string.Empty;
if (timeFormat == TimeFormatType.LongTime)
{
retVal = datetime.ToLongTimeString();
}
else if (timeFormat == TimeFormatType.ShortTime)
{
retVal = datetime.ToShortTimeString();
}
return(retVal);
}
public static string ToStringWithoutS(this TimeSpan span, TimeFormatType formatType = TimeFormatType.Digital)
{
double seconds = span.TotalSeconds;
if (seconds > 86400)
{
return(span.ToStringDayWithoutS(formatType));
}
else //if (seconds > 3600)
{
return(span.ToStringHourWithoutS(formatType));
}
//else
// return span.ToStringMinuteWithoutS (formatType);
}
/// <summary>
/// 格式化时间
/// </summary>
/// <param name="seconds"></param>
/// <param name="type"></param>
/// <returns></returns>
public static string FormatTime(long seconds, TimeFormatType type = TimeFormatType.Second)
{
switch (type)
{
case TimeFormatType.Second:
return(FormatTimeToSecond(seconds));
case TimeFormatType.Minute:
return(FormatTimeToMinute(seconds));
case TimeFormatType.Hour:
return(FormatTimeToHour(seconds));
}
return(seconds.ToString());
}
/// <summary>
/// 把[DateTime对象]转换为[Long]
/// </summary>
/// <param name="_dateTime">要转换的DateTime对象</param>
/// <param name="_timeFormatType">转换成什么格式？</param>
/// <returns>转换后的Long（如果为-1，就表示string转long出错）</returns>
public static long DateTimeToLong(DateTime _dateTime, TimeFormatType _timeFormatType)
{
try
{
//先把DateTime转化为String
string _dateTimeString = DateTimeToString(_dateTime, _timeFormatType);
//然后把String转化为Long
return(long.Parse(_dateTimeString));
}
catch (Exception e)
{
return(-1);
}
}
public static string ToString(this TimeSpan span, TimeFormatType formatType = TimeFormatType.Digital)
{
double seconds = span.TotalSeconds;
if (seconds > 86400)
{
return(span.ToStringDay(formatType));
}
else if (seconds > 3600)
{
return(span.ToStringHour(formatType));
}
else
{
return(span.ToStringMinute(formatType));
}
}
/// <summary>
/// 把[DateTime对象]转换为[string]
/// </summary>
/// <param name="_dateTime">要转换的DateTime对象</param>
/// <param name="_timeFormatType">转换成什么格式？</param>
/// <returns>转换后的string</returns>
public static string DateTimeToString(DateTime _dateTime, TimeFormatType _timeFormatType)
{
/* dateTime.ToString("yyyy/MM/dd HH:mm:ss fff");
* y代表年，M代表月，d代表日
* H代表时，m代表分，s代表秒
* f代表毫秒
* 如果有4个y(比如yyyy)，就代表年的数字是4位*/
//容器：转换后的string
string _string = "";
//判断格式的类型
switch (_timeFormatType)
{
//如果是[年.月.日]格式
case TimeFormatType.YearMonthDay:
_string = _dateTime.ToString("yyyy.MM.dd");
break;
//如果是[年.月.日 时:分]格式
case TimeFormatType.YearMonthDayHourMinute:
_string = _dateTime.ToString("yyyy.MM.dd HH:mm");
break;
//如果是[年/月/日 时:分:秒]格式
case TimeFormatType.YearMonthDayHourMinuteSecond:
_string = _dateTime.ToString("yyyy/MM/dd HH:mm:ss");
break;
//如果是[年 月 日 时 分 秒 毫秒]格式
case TimeFormatType.YearMonthDayHourMinuteSecondMillisecond:
_string = _dateTime.ToString("yyyyMMddHHmmss");
break;
//如果是[时:分:秒]格式
case TimeFormatType.HourMinuteSecond:
_string = _dateTime.ToString("HH:mm:ss");
break;
}
return(_string);
}
private static string _GetTimeValue(FieldInfo fieldInfo, int row, int level)
{
StringBuilder content = new StringBuilder();
TimeFormatType timeFormatType = TableAnalyzeHelper.GetTimeFormatType(fieldInfo.ExtraParam[LuaStruct.TimeToExportFormatKey].ToString());
switch (timeFormatType)
{
case TimeFormatType.FormatString:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append("\"").Append(((DateTime)(fieldInfo.Data[row])).ToString(fieldInfo.ExtraParam[LuaStruct.TimeToExportFormatKey].ToString())).Append("\"");
}
break;
}
case TimeFormatType.ReferenceTimeSec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - DateTimeValue.REFERENCE_DATE).TotalSeconds);
}
break;
}
default:
{
AppLog.LogErrorAndExit("错误：用_GetTimeValue函数导出lua文件的time型的TimeFormatType非法");
break;
}
}
return(content.ToString());
}
private static string _GetTimeValue(FieldInfo fieldInfo, int row, int level)
{
StringBuilder content = new StringBuilder();
TimeFormatType timeFormatType = TableAnalyzeHelper.GetTimeFormatType(fieldInfo.ExtraParam[AppValues.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_LUA_FORMAT].ToString());
switch (timeFormatType)
{
case TimeFormatType.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_TIME_TO_LUA_FORMAT].ToString())).Append("\"");
}
break;
}
case TimeFormatType.ReferenceTimeSec:
{
if (fieldInfo.Data[row] == null)
{
content.Append("nil");
}
else
{
content.Append(((DateTime)(fieldInfo.Data[row]) - AppValues.REFERENCE_DATE).TotalSeconds);
}
break;
}
default:
{
Utils.LogErrorAndExit("错误：用_GetTimeValue函数导出lua文件的time型的TimeFormatType非法");
break;
}
}
return(content.ToString());
}
public static string StringTimeFormated(TimeFormatType formatType, int seconds)
{
switch (formatType)
{
case TimeFormatType.None:
return(seconds + "");
break;
case TimeFormatType.MinuteNSecond:
TimeSpan timeSpan = TimeSpan.FromSeconds(seconds);
return(string.Format("{0}:{1}", timeSpan.Minutes, timeSpan.Seconds));
break;
}
return(seconds + "");
}
/// <summary>
/// 检查time型的格式定义
/// </summary>
public static bool CheckTimeDefine(string defineString, out string errorString)
{
defineString = defineString.Trim();
if (string.IsNullOrEmpty(defineString))
{
errorString = "未进行格式声明";
return(false);
}
TimeFormatType formatType = DateTimeValue.GetTimeFormatType(defineString);
if (formatType == TimeFormatType.FormatString)
{
// 检查time型的格式字符串声明，不允许出现代表年月日的y、M、d
List <string> errorInfo = new List <string>();
if (defineString.IndexOf('y') != -1)
{
errorInfo.Add("代表年的y");
}
if (defineString.IndexOf('M') != -1)
{
errorInfo.Add("代表月的M");
}
if (defineString.IndexOf('d') != -1)
{
errorInfo.Add("代表日的d");
}
if (errorInfo.Count > 0)
{
errorString = string.Format("time类型的格式定义中不允许出现以下与年月日相关的日期型格式定义字符：{0}", Utils.CombineString(errorInfo, "，"));
return(false);
}
}
errorString = null;
return(true);
}
/// <summary>
/// 解析time型数据的定义
/// </summary>
private static bool _AnalyzeTimeType(FieldInfo fieldInfo, TableInfo tableInfo, DataTable dt, int columnIndex, FieldInfo parentField, out int nextFieldColumnIndex, out string errorString)
{
//const string DefineTimeStartString = "time";// DEFINE_START_STRING
// 定义time型输入导出格式的key
//const string TimeInputParamKey = "input";// INPUT_PARAM_KEY
// const string TimeToExportParamKey = "toLua";// TO_LUA_PARAM_KEY
// const string TimeToExportParamKey = "toDatabase";// TO_DATABASE_PARAM_KEY
// 解析time型输入导出格式的声明
if (!DateTimeValue.DefineTimeStartString.Equals(fieldInfo.DataTypeString, StringComparison.CurrentCultureIgnoreCase))
{
int leftBracketIndex = fieldInfo.DataTypeString.IndexOf('(');
int rightBracketIndex = fieldInfo.DataTypeString.LastIndexOf(')');
if (leftBracketIndex == -1 && rightBracketIndex == -1)
{
errorString = "time型格式定义错误";
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
if (!(leftBracketIndex != -1 || rightBracketIndex > leftBracketIndex))
{
errorString = "time型格式定义错误，括号不匹配";
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
// 解析声明的时间格式
string defineString = fieldInfo.DataTypeString.Substring(leftBracketIndex + 1, rightBracketIndex - leftBracketIndex - 1).Trim();
if (string.IsNullOrEmpty(defineString))
{
errorString = "time型格式定义错误，若要声明时间格式就必须在括号中填写，否则不要加括号，本工具会采用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.CheckTimeDefine(paramValue, out errorString) == true)
{
fieldInfo.ExtraParam[paramKey] = paramValue;
}
else
{
paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
}
// switch (paramKey)
// {
// case DateTimeValue.TimeInputParamKey:
// {
// if (TableCheckHelper.CheckTimeDefine(paramValue, out errorString) == true)
// fieldInfo.ExtraParam[DateTimeValue.TimeInputFormat] = paramValue;
// else
// paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
// break;
// }
// //case LuaStruct.TimeToExportParamKey:
// // {
// // if (TableCheckHelper.CheckTimeDefine(paramValue, out errorString) == true)
// // fieldInfo.ExtraParam[LuaStruct.TimeToExportFormatKey] = paramValue;
// // else
// // paramDefineErrorStringBuilder.AppendFormat(ERROR_STRING_FORMAT, paramKey, paramValue, errorString);
// // break;
// // }
// //case MySQLStruct.TimeToExportParamKey:
// // {
// // if (TableCheckHelper.CheckTimeDefine(paramValue, out errorString) == true)
// // fieldInfo.ExtraParam[MySQLStruct.TimeToExportFormatKey] = 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("time型格式定义存在以下错误：\n{0}", paramDefineErrorString);
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
}
// 检查time型输入格式、导出至lua文件格式、导出至MySQL数据库格式是否都已声明，没有则分别采用config文件的默认设置
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.input.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toLua.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toLua.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toJson.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toJson.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toErlang.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toErlang.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toServerJson.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toServerJson.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toMySQL.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toMySQL.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toSQLITE.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toSQLITE.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
if (!fieldInfo.ExtraParam.ContainsKey(DateTimeTypeKey.toTxt.ToString()))
{
fieldInfo.ExtraParam[DateTimeTypeKey.toTxt.ToString()] = DateTimeValue.DefaultTimeInputFormat;
}
TimeFormatType timeFormatType = DateTimeValue.GetTimeFormatType((string)fieldInfo.ExtraParam[DateTimeTypeKey.input.ToString()]);
fieldInfo.Data = new List <object>();
// 记录非法数据的行号以及数据值（key：行号， value：数据值）
Dictionary <int, object> invalidInfo = new Dictionary <int, object>();
if (timeFormatType == TimeFormatType.FormatString)
{
// 用于对时间格式进行转换
DateTimeFormatInfo dateTimeFormat = new DateTimeFormatInfo();
dateTimeFormat.ShortTimePattern = (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 tempDateTime = Convert.ToDateTime(inputData, dateTimeFormat);
fieldInfo.Data.Add(new DateTime(DateTimeValue.REFERENCE_DATE.Year, DateTimeValue.REFERENCE_DATE.Month, DateTimeValue.REFERENCE_DATE.Day, tempDateTime.Hour, tempDateTime.Minute, tempDateTime.Second));
}
catch
{
invalidInfo.Add(row, inputData);
}
}
}
}
}
else if (timeFormatType == TimeFormatType.ReferenceTimeSec)
{
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();
uint inputIntValue = 0;
if (string.IsNullOrEmpty(inputData))
{
fieldInfo.Data.Add(null);
}
else if (uint.TryParse(inputData, out inputIntValue) == false)
{
invalidInfo.Add(row, inputData);
}
else
{
if (inputIntValue >= 86400)
{
invalidInfo.Add(row, inputData);
}
else
{
int hour = (int)inputIntValue / 60 / 60;
int remainingSecond = (int)inputIntValue - hour * 60 * 60;
int minute = remainingSecond / 60;
remainingSecond = remainingSecond - minute * 60;
DateTime dateTime = new DateTime(DateTimeValue.REFERENCE_DATE.Year, DateTimeValue.REFERENCE_DATE.Month, DateTimeValue.REFERENCE_DATE.Day, hour, minute, remainingSecond);
fieldInfo.Data.Add(dateTime);
}
}
}
}
}
else
{
errorString = "错误：用_AnalyzeTimeType函数处理非法的TimeFormatType类型";
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 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 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 GetDateAndTimeString(DateTime datetime, DateFormatType dateFormat, TimeFormatType timeFormat)
{
return($"{GetDateString(datetime, dateFormat)} {GetTimeString(datetime, timeFormat)}");
}
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);
}
}
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);
}
}
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());
}
public static string GetNow(TimeFormatType formatType = TimeFormatType.YearMonthDateTime)
{
return(DateTime.Now.ToString(GetFormatType(formatType)));
}
public static string GetNow(TimeFormatType getDataTimeType = TimeFormatType.YearMonthDay)
{
return(DateTime.Now.ToString(GetFormatType(getDataTimeType)));
}
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;
}
}
}
