mysql 表数据转到SqlServer

最新推荐文章于 2024-07-28 03:27:16 发布

灰鸟会灰

最新推荐文章于 2024-07-28 03:27:16 发布

阅读量1k

点赞数

分类专栏： c# sql

本文链接：https://blog.csdn.net/svtme/article/details/75079470

版权

c# 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

sql

6 篇文章 0 订阅

订阅专栏

DBConnInfo conn1 = new DBConnInfo() { dbName = "it9520_std_v33", dbType = DBMSType.MySQL, host = "192.168.2.1", user = "root", pwd = "123
var db1 = new DbOperation(conn1);
DBConnInfo conn2 = new DBConnInfo() { dbName = "it9520_std_v33", dbType = DBMSType.SqlServer, host = "192.168.2.2", user = "sa", pwd = "123", port = "1433" };
var db2 = new DbOperation(conn2);
var dtTables = db1.GetDataTable("select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+db1.SqlConn.Database+"' and table_type='base table'");
foreach (DataRow dr_TbName in dtTables.Rows)
{
string tbName = dr_TbName[0].ToString();
var dt = new DataTable();
// "exec sp_MShelpcolumns t_testiteminfo";

var ada = db1.GetAdapter(db1.SqlConn, "select * from "+tbName+" where 1=2");
ada.FillSchema(dt, System.Data.SchemaType.Mapped);
dt.TableName = tbName;

if (db2.ExistTable(dt.TableName))
db2.ExecuteSqlTrans("Drop table " + dt.TableName);
StringBuilder createcmd = new StringBuilder("Create table " + dt.TableName + "(");
foreach (DataColumn dc in dt.Columns)
{
createcmd.Append(dc.ColumnName).Append(" ").Append(DbOperation.GetTableColumnType(dc, conn2.dbType));

createcmd.Append(" ");
createcmd.Append(dc.AllowDBNull ? "NULL" : "NOT NULL");

if (dc.AutoIncrement)
createcmd.Append(" IDENTITY(").Append(dc.AutoIncrementSeed).Append(",").Append(dc.AutoIncrementStep).Append(")");
if (dc.DefaultValue != DBNull.Value)
createcmd.Append(" default ").Append("'" + dc.DefaultValue + "'");

if (dc != dt.Columns[dt.Columns.Count - 1])
createcmd.Append(",");
createcmd.AppendLine();
}
var ks = dt.PrimaryKey;
if (ks.Length > 0)
{
createcmd.Append(",PRIMARY KEY (").Append(string.Join(",", ks.Select(p => p.ColumnName))).Append(")");
}
createcmd.Append(")");
if (db2.ExecuteSqlTrans(createcmd.ToString()) !=-2)
{
if (dt.TableName.ToLower().EndsWith("result"))
continue;
db2.UpdateDataTable("select * from " + dt.TableName, t =>
{
var dt1 = db1.GetDataTable("select * from " + tbName);
foreach (DataRow r in dt1.Rows)
t.Rows.Add(r.ItemArray);
});
}

}



public static string GetTableColumnType(DataColumn col, DBMSType db)
{
string result = "varchar";
string sDbType = col.DataType.Name;
if (db == DBMSType.SqlServer)
switch (sDbType)
{
case "String":
if (col.MaxLength < 0 || col.MaxLength > 8000)
result = "text";
else
result += ("(" + col.MaxLength) + ")";
break;
case "SByte":
case "Byte":
case "Boolean":
result = "tinyint";
break;
case "Int16":
case "UInt16":
result = "smallint";
break;
case "Int32":
case "UInt32":
result = "int";
break;
case "Int64":
case "UInt64":
result = "bigint";
break;
case "Single":
result = "float";
break;
case "Decimal":
result = "decimal(18,6)";
break;
case "Double":
result = "decimal(18,6)";
break;
case "DateTime":
case "MySqlDateTime":
result = "datetime";
break;
case "Byte[]":
return "binary";

default:
result += "(8000)";
break;
}
return result;
}