/// <summary>
/// 得到数据表的数据类型
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static DataFieldTypeCollection GetDataStruct(DataTable dt)
{
DataFieldTypeCollection dtc = new DataFieldTypeCollection();
foreach (DataColumn dc in dt.Columns)
{
DataFieldType t = new DataFieldType();
t.FiledName = dc.ColumnName.Trim().Replace("\r", "").Replace("\n", "");
t.Length = dc.MaxLength;
t.FieldParameterName = $"@{t.FiledName}";
string dataTypeName = dc.DataType.FullName.ToLower();
if (dataTypeName.Contains("int"))
{
t.Type = "int";
}
else if (dataTypeName.Contains("datetime"))
{
t.Type = "datetime";
}
else
{
t.Type = "string";
}
dtc.Add(t);
}
return(dtc);
}
/// <summary>
/// 通过SQL语句获取所有列集合
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private DataFieldTypeCollection GetDataFieldCollection(string sql)
{
//1 获取表结构并不获取数据
DataTable table = null;
try
{
DateTime baseTime = DateTime.Now.AddYears(10).Date;
table = GetDataList(sql, baseTime, baseTime);
if (table == null || table.Columns.Count == 0)
{
throw new Exception($"创建SQLite数据库时，SQL语句[{sql}]查询数据库成功，但未获取到数据表列字段");
}
}
catch (Exception ex)
{
throw new Exception($"创建SQLite数据库时，通过SQL语句[{sql}]查询数据库出现错误,异常信息[{ex.Message}][{ex.StackTrace}]");
}
//2 获取表字段集合
DataFieldTypeCollection dataTypeCollection = SQLiteHelper.GetDataStruct(table);
return(dataTypeCollection);
}
/// <summary>
/// 构造Sqlite数据文件 SQL插入语句
/// </summary>
/// <param name="dataFieldTypes"></param>
/// <returns></returns>
public static string CreateInsertSql(DataFieldTypeCollection dataFieldTypes)
{
string fieldString = string.Join(",", dataFieldTypes.Select(item => item.FiledName));
string parameterNameString = string.Join(",", dataFieldTypes.Select(item => item.FieldParameterName));
string insertSql = $"insert into [mytable]({fieldString}) values({parameterNameString})";
return(insertSql);
}
public bool OutputDataToSQLite(string sql, DateTime op, DateTime ed, string sqliteFileFullpath, string connect_string = null)
{
DataFieldTypeCollection dataFieldTypes = GetDataFieldCollection(sql);
string sqliteConnString = SQLiteHelper.CreateSQLiteFileConnectionString(sqliteFileFullpath);
SQLiteHelper.CreateDataBase(sqliteConnString, sqliteFileFullpath, dataFieldTypes);
string insertSql = SQLiteHelper.CreateInsertSql(dataFieldTypes);
ExecuteFor(c =>
{
using (SQLiteConnection targetconn = new SQLiteConnection(sqliteConnString))
{
targetconn.Open();
using (SQLiteCommand targetcmd = new SQLiteCommand(insertSql, targetconn))
{
targetcmd.CommandText = insertSql;
c.QueryRawDataReader(r =>
{
foreach (DataFieldType type in dataFieldTypes)
{
if (type.Type.ToLower() == "datetime")
{
//日期数据为空 则默认为 1899-12-30
string dateTime = DateTime.Parse("1899-12-30").ToString("s");
if (!string.IsNullOrEmpty(r[type.FiledName].ToString()))
{
dateTime = Convert.ToDateTime(r[type.FiledName].ToString()).ToString("s");
}
targetcmd.Parameters.Add(new SQLiteParameter(type.FieldParameterName, dateTime));
}
else
{
targetcmd.Parameters.Add(new SQLiteParameter(type.FieldParameterName, r[type.FiledName].ToString()));
}
}
targetcmd.ExecuteNonQuery();
targetcmd.Parameters.Clear();
}, sql, new { StartTime = op, EndTime = ed });
targetcmd.Dispose();
}
targetconn.Close();
}
}, connect_string);
return(true);
}
/// <summary>
/// 创建数据库和表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="sqliteFileFullpath">数据库文件完整路径</param>
/// <param name="dts"></param>
public static void CreateDataBase(string connectionString, string sqliteFileFullpath, DataFieldTypeCollection dts)
{
if (CreateNewDatabase(sqliteFileFullpath) && dts != null)
{
StringBuilder sbCreateTableSql = new StringBuilder();
sbCreateTableSql.Append("CREATE TABLE mytable (");
foreach (DataFieldType dt in dts)
{
sbCreateTableSql.Append(CreateColumn(dt) + ",");
}
sbCreateTableSql.Remove(sbCreateTableSql.Length - 1, 1);
sbCreateTableSql.Append(")");
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(sbCreateTableSql.ToString(), conn);
command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
}
else
{
throw new Exception($"SQLite数据库创建失败![{connectionString}]");
}
}