1 操作示例
stringdatasource="E:\\SQLite\\datasource.db";varsqliteDb=datasource.GetSqlSugarClient_SQLite();stringdatabase="test";stringuid="root";stringpwd="123456";varmysqlDb=database.GetSqlSugarClient_MySql(uid,pwd);sqliteDb.Table_SQLite_To_MySql(mysqlDb);
1.1 操作数据库扩展类
/// <summary>/// 操作数据库扩展类/// </summary>publicstaticclassExtensionTableStruct{/// <summary>/// 获取SQLite操作数据库对象/// </summary>publicstaticSqlSugarClientGetSqlSugarClient_SQLite(thisstringdatasource){stringconnStr=$"Data Source={datasource};";vardb=newSqlSugarClient(newConnectionConfig(){DbType=DbType.Sqlite,ConnectionString=connStr,// SQLite 文件路径IsAutoCloseConnection=true});returndb;}/// <summary>/// 获取MySql操作数据库对象/// </summary>publicstaticSqlSugarClientGetSqlSugarClient_MySql(thisstringdatabase,stringuid,stringpwd){varconnectionString=$"server=localhost;database={database};user id={uid};password={pwd};port=3306;CharSet=utf8mb4;SslMode=None;";vardb=newSqlSugarClient(newConnectionConfig(){DbType=DbType.MySql,ConnectionString=connectionString,IsAutoCloseConnection=true});returndb;}/// <summary>/// SQLite表结构转换为MySql表结构/// </summary>/// <param name="sqliteDb"></param>/// <param name="mysqlDb"></param>publicstaticvoidTable_SQLite_To_MySql(thisSqlSugarClientsqliteDb,SqlSugarClientmysqlDb){vartableNames=sqliteDb.GetSQLiteTableNames();foreach(vartableNameintableNames){varcolumns=sqliteDb.GetSQLileTableStruct(tableName);mysqlDb.CreateMySqlTable(tableName,columns);}}/// <summary>/// 获取SQLite所有表名称/// </summary>privatestaticList<string>GetSQLiteTableNames(thisSqlSugarClientdb){stringsql="SELECT name FROM sqlite_master "+"WHERE type='table' AND name NOT LIKE 'sqlite_%'";vartableDt=db.Ado.GetDataTable(sql);varlist=newList<string>();foreach(DataRowrowintableDt.Rows){list.Add(row[0].ToString());}returnlist;}/// <summary>/// 创建MySql表结构/// </summary>privatestaticvoidCreateMySqlTable(thisSqlSugarClientmysqlDb,stringtableName,List<MyColumnInfo>columns){List<string>fieldLines=newList<string>();List<string>keyLines=newList<string>();foreach(MyColumnInfocolincolumns){stringmysqlType="";if(col.Type=="integer"){mysqlType="INT";}elseif(col.Type=="int"){mysqlType="INT";}elseif(col.Type=="real"){mysqlType="DOUBLE";}elseif(col.Type=="text"){mysqlType="LONGTEXT";}elseif(col.Type=="blob"){mysqlType="LONGBLOB";}elseif(col.Type=="datetime"){mysqlType="DATETIME";}else{mysqlType="VARCHAR(255)";}// 字段名 + 类型stringline="`"+col.Name+"` "+mysqlType;// 非空if(!col.IsNullable){line+=" NOT NULL";}// 自增if(col.IsIdentity){line+=" AUTO_INCREMENT";}fieldLines.Add(line);// 主键if(col.IsPrimaryKey){keyLines.Add("PRIMARY KEY (`"+col.Name+"`)");}}// 拼接所有语句List<string>allLines=newList<string>();allLines.AddRange(fieldLines);allLines.AddRange(keyLines);stringsql="CREATE TABLE IF NOT EXISTS `"+tableName+"` (\n "+string.Join(",\n ",allLines)+"\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";// 执行建表mysqlDb.Ado.ExecuteCommand(sql);}/// <summary>/// 获取SQLite表结构/// </summary>privatestaticList<MyColumnInfo>GetSQLileTableStruct(thisSqlSugarClientdb,stringtableName){varlist=newList<MyColumnInfo>();vardt=db.Ado.GetDataTable($"PRAGMA table_info([{tableName}])");foreach(DataRowrowindt.Rows){varcol=newMyColumnInfo{Name=row["name"].ToString(),Type=row["type"].ToString().ToLower(),IsPrimaryKey=row["pk"].ToString()=="1",IsNullable=row["notnull"].ToString()=="0",};// SQLite 只有 INTEGER 主键才是自增col.IsIdentity=col.IsPrimaryKey&&col.Type=="integer";list.Add(col);}returnlist;}}
1.2 自定义字段结构
/// <summary>/// 自定义字段结构/// </summary>publicclassMyColumnInfo{publicstringName{get;set;}// 字段名publicstringType{get;set;}// 字段类型publicboolIsPrimaryKey{get;set;}// 是否主键publicboolIsNullable{get;set;}// 是否可空publicboolIsIdentity{get;set;}// 是否自增}
