记录一次从mysql到sql的数据库迁移

使用的是c#+SqlSugar。

1.获取表数据

  /// <summary>
  /// 获取数据库访问对象
  /// </summary>
  /// <returns></returns>
  public SqlSugarClient GetSqlSugar()
  {
      SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
      {
          DbType = dbtype,
          ConnectionString = string.Format("server={0};Database={1};Uid={2};Pwd={3}", dbIp, dbName, dbUser, dbPasswork),
          IsAutoCloseConnection = true
      });
      return db;
  }
/// <summary>
/// 获取表数据
/// </summary>
/// <returns></returns>
public List<DbTableInfo> GetTableInfo()
{
    var sql = GetSqlSugar();
    List<DbTableInfo> viewinfo = sql.DbMaintenance.GetTableInfoList(false);
    sql.Close();
    sql.Dispose();
    return viewinfo;
}

2.获取列　　

 /// <summary>
 /// 获取列
 /// </summary>
 /// <param name="tablename"></param>
 /// <returns></returns>
 public List<DbColumnInfo> GetColinfo(string tablename)
 {
     var sql = GetSqlSugar();
    return sql.DbMaintenance.GetColumnInfosByTableName(tablename);
 }

3.遍历表数据添加到sql库

 /// <summary>
 /// 创建表
 /// </summary>
 /// <param name="db"></param>
 /// <param name="tablename"></param>
 /// <param name="tableDescription"></param>
 /// <param name="classname"></param>
 public void CreateClass(string tablename, string tableDescription, string classname, List<DbColumnInfo> cols, SqlSugarClient db = null)
 {
     if (db == null)
     {
         db = GetSqlSugar();
     }
     var typeBilder = db.DynamicBuilder().CreateClass(classname, new SugarTable() { TableName = tablename, TableDescription = tableDescription });

     foreach (var item in cols)
     {
         Type type1 = null;
         if (item.DataType == "bigint")
         {
             type1 = typeof(long);
         }
         if (item.DataType == "datetime")
         {
             type1 = typeof(DateTime);
         }
         if (item.DataType == "bit")
         {
             type1 = typeof(bool);
         }
         if (item.DataType == "double")
         {
             type1 = typeof(double);
         }
         if (item.DataType == "float")
         {
             type1 = typeof(float);
         }
         if (item.DataType == "text" || item.DataType == "varchar")
         {
             type1 = typeof(string);
         }
         if (item.DataType == "int"|| item.DataType == "tinyint")
         {
             type1 = typeof(int);
         }
         typeBilder.CreateProperty(item.DbColumnName, type1, new SugarColumn() 
         { 
             IsPrimaryKey = item.IsPrimarykey, 
             IsIdentity = item.IsIdentity,
             Length=item.Length,
             IsNullable=item.IsNullable,
             
         });
     }


     //创建类
     var type = typeBilder.BuilderType();//想缓存有typeBilder.WithCache

     //创建表
     db.CodeFirst.InitTables(type); 
     
 }

4.获取视图

  /// <summary>
  /// 获取视图信息
  /// </summary>
  public List<DbTableInfo> GetViewInfo()
  {
      var sql = GetSqlSugar();
      List<DbTableInfo> viewinfo = sql.DbMaintenance.GetViewInfoList(false);
      sql.Close();
      sql.Dispose();
      return viewinfo;
  }

5.获取视图创建代码，执行视图创建代码创建视图

 //视图列表
  var viewinfo = dba.GetViewInfo();
//数据库连接1
  var sql = dba.GetSqlSugar();
//数据库连接2
  var sql2 = dbb.GetSqlSugar();
  int index = 1;
  foreach (var item in viewinfo)
  {
      var dst = sql.Ado.GetDataTable("show create view " + item.Name);
      var sqlstr = dst.Rows[0][1].ToString();
      try
      {
          sqlstr = sqlstr.Replace("ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER ", "");
          sqlstr = sqlstr.Replace("`", "");
          sqlstr += ";";
          sql2.Ado.ExecuteCommand(sqlstr);
      }
      catch (Exception ex)
      {
          File.Create(item.Name).Close() ;
          File.WriteAllText(item.Name,sqlstr);
      }
      //var dst2= sql.Ado.SqlQuerySingle("show create view " + item.Name);
      Console.WriteLine(index + "-"+ viewinfo.Count);
      index++;
  }

6.数据迁移

数据迁移就是连接a查询连接b插入。