/// <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()); }