/// <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>
/// 构造Access和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);
}
/// <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}]");
}
}
/// <summary>
/// 创建Access库
/// </summary>
/// <param name="connectionString"></param>
/// <param name="accessFileFullpath"></param>
/// <param name="dts"></param>
public static void CreateDataBase(string connectionString, string accessFileFullpath, DataFieldTypeCollection dts)
{
CatalogClass cat = null;
try
{
cat = new CatalogClass();
cat.Create(connectionString);
}
catch (Exception ex)
{
throw new Exception($"创建Access数据库失败,路径[{accessFileFullpath}],异常信息:[{ex.Message}]");
}
#region 新建表
try
{
TableClass tbl = new TableClass();
tbl.ParentCatalog = cat;
tbl.Name = "mytable";
foreach (DataFieldType dt in dts)
{
//增加一个文本字段
ColumnClass col2 = new ColumnClass();
col2.ParentCatalog = cat;
col2.Name = dt.FiledName;
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
switch (dt.Type.ToLower())
{
case "string":
col2.Type = ADOX.DataTypeEnum.adLongVarWChar;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adLongVarWChar, 16);
break;
case "datetime":
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adDate, dt.Length);
break;
case "int":
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adInteger, dt.Length);
break;
}
}
//把表加入数据库(非常重要)
cat.Tables.Append(tbl);
//转换为ADO连接,并关闭
(cat.ActiveConnection as ADODB.Connection).Close();
cat.ActiveConnection = null;
cat = null;
}
catch (Exception ex)
{
throw new Exception($"创建Access数据库表失败,路径[{accessFileFullpath}],异常信息:[{ex.Message}]");
}
#endregion
}
/// <summary>
/// 创建参数列表
/// </summary>
/// <param name="dataFieldTypes"></param>
/// <returns></returns>
public static List <string> CreateParameterNameList(DataFieldTypeCollection dataFieldTypes)
{
return(dataFieldTypes.Select(item => "@" + item.FiledName).ToList());
}
