Migrate MySQL To SQL Server – Wellcode software

This page illustrates the strongness of the wellcode dbms tool and shows how to migrate a MySQL database to MS SQL Server including indexes and constraints. The Example uses the dbms tool to generate and create the schema and a part of WellFrame library to dynamically import data.

Conditions to start
- MySQL ODBC Driver or MySQL.Net are installed on your machine
- An empty database on MS SQL Server is created and is ready for import.
Start the migration
- Download the migrate tool example from here
- run the executable and enter the connection strings for
  - MySQL database and
  - the destination MS SQL Server databases.
- Test the connections and start the migration

To download the example project including all dependencies click here. The Visual Studio project uses .Net Framework 4.5.

Please keep in mind, that the odbc driver has to be the same binary bit number as the programm it self. If you use 64bit driver, you must run the 46bit copy of import tool. To import from a 32bit mysql driver use the 32bit executables (see bin64 and bin32 directories in the example).

You can avoid using the ODBC Driver by changing the source code and replacing “OdbConnection” with the .Net Connection of MySQL Driver “MySqlConnection“.

Here is the migrating source code for illustration:

using Wellcode.Dbms;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wellcode.Basics;
using Wellcode.Frame;
using System.Data.Common;

namespace Wellcode.DbmsConverter
{
    public class MySQLToMSSQLExporter
    {
        public delegate void ActionEventHandler(MySQLToMSSQLExporter sender, RdbmsActionEventArgs e);
        public event ActionEventHandler Action;

        public string MySQLConnectionString = "DSN=mysql";
        public bool MySQLOverODBC = true;
        public bool MySQLOverNet = false;
        public bool SchemaOnly = false;
        public string MsConnectionString = "";
        public string SchemaFileName = "mysql-schema.xml";

        protected DynManager Manager = new DynManager();

        public MySQLToMSSQLExporter(string mySqlCn, string msCn)
        {
            this.MySQLConnectionString = mySqlCn;
            this.MsConnectionString = msCn;
        }
        
        public bool Migrate(ref string errorString)
        {
            try
            {
                var my = new OdbcConnector(this.MySQLConnectionString);
                var ms = new DbConnector(this.MsConnectionString, TransactionLevel.NoTransaction);

                // create the basic schema definition from mysql database 
                // (blank table/columns definitions)
                FireAction("Creating Schema ...");
                var mydbms = new MySQLSchemaReader().CreateSchema(my, true);

                // apply schema to destination ms sql database
                FireAction("transfer schema to the destination database ...");
                var controller = new RdbmsController();
                controller.Model = mydbms;
                //controller.SetSchemaFile(this.SchemaFileName);
                controller.ConnectionString = this.MsConnectionString;
                controller.Action += controller_Action;
                controller.Upgrade(UpgradeMode.Upgrade);

                FactoryDbms.SetStandardModel(mydbms); // prevent reading dbms model from database

                // import all tables with columns
                for (int j = 0; false == this.SchemaOnly && j < mydbms.Tables.Count && mydbms.Tables[j].Columns.Count > 0; j++)
                {
                    var table = mydbms.Tables[j];

                    FireAction("Transfer Data to table " + table.Name + " ...");

                    // delete all existing contents
                    var cmd = ms.CreateCommand("DELETE FROM " + table.Name);
                    cmd.ExecuteNonQuery();

                    // use the entity model to import data
                    DynFactory t = new DynFactory(this.Manager);
                    t.IsChangeLogEnabled = false;
                    t.TableName = table.Name;

                    // dummy primary key
                    t.Fields = new string[1];
                    t.Fields[0] = table.Columns[0].Name;
                    
                    var ds = my.GetResult("SELECT * FROM " + table.Name);
                    var cnt = ds.Rows.Count;
                    var num = 0;
                    foreach (DataRow r in ds.Rows)
                    {
                        var entity = new WebEntity();
                        entity.AquireEntityManager += entity_AquireEntityManager;
                        for (int i = 0; i < ds.Columns.Count; i++)
                        {
                            var o = r[i];
                            if (DBNull.Value.Equals(o))
                                o = null;
                            entity.Add(ds.Columns[i].ColumnName, o, ds.Columns[i].DataType);
                        }

                        // write to ms sql database and protocoll errors
                        try
                        {
                            t.SaveEntity(ms, entity);
                        }
                        catch (Exception e)
                        {
                            FireAction("Error: " + e.ToString());
                        }
                        num++;
                        if (num % 100 == 0)
                        {
                            FireAction(string.Format("{0}: {1} / {2} imported...\r\n{3} von {4} tables imported", table.Name, num, cnt, j, mydbms.Tables.Count));
                        }
                    }
                }

                FireAction("Reset the assigned model for future usage.");
                FactoryDbms.ResetModel();

                FireAction("Import done.");

                // create foreigh keys now and rest of dbms structure now
                FireAction("create foreign keys after import data ....");

                mydbms = new MySQLSchemaReader().CreateSchema(my, false);
                controller.Model = mydbms;
                controller.Action -= controller_Action;
                controller.Upgrade(UpgradeMode.Upgrade);

                return true;
            }
            catch (Exception ex)
            {
                errorString = ex.ToString();
            }
            return false;
        }

        void entity_AquireEntityManager(WebEntity sender, EntityManagerEventArgs e)
        {
            e.Manager = this.Manager;
        }

        void controller_Action(RdbmsController sender, RdbmsActionEventArgs e)
        {
            if (e.Action == ActionMode.Init)
                e.Wrapper.UpgradeForeignKeys = false;
        }

        void FireAction(string action)
        {
            if (this.Action == null)
                return;

            var e = new RdbmsActionEventArgs();
            e.Comments = action;
            this.Action(this, e);
        }


        public bool TestMySQLConnectionString(ref string errorString)
        {
            return this.TestConnectionString<OdbcConnection>(this.MySQLConnectionString, ref errorString);
        }

        public bool TestMsConnectionString(ref string errorString)
        {
            return this.TestConnectionString<SqlConnection>(this.MsConnectionString, ref errorString);
        }

        protected bool TestConnectionString<Vendor>(string connStr, ref string errorString)
            where Vendor : DbConnection, new() 
        {
            try
            {
                var cn = new Vendor();
                cn.ConnectionString = connStr;
                cn.Open();
                cn.Close();

                return true;
            }
            catch (Exception ex)
            {
                errorString = ex.ToString();
            }
            return false;
        }
    }

    public class MySQLSchemaReader
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="my"></param>
        /// <param name="basicSchema">Creates schema without indexes, constraints, identities, etc.</param>
        /// <returns></returns>
        public DatabaseModel CreateSchema(OdbcConnector my, bool basicSchema)
        {
            // the datamodel of mySQL database
            var mydbms = new Wellcode.Dbms.DatabaseModel();

            var myDbs = my.GetResult("SELECT database() AS SCHEMANAME");
            var myDbName = myDbs.Rows[0][0].ToString();

            var tables = my.GetResult("SHOW TABLES");
            foreach (DataRow row in tables.Rows)
            {
                var table = new Wellcode.Dbms.Table();
                table.Name = (string)row[0];
                mydbms.Tables.Add(table);

                // add columns to the table
                var columns = my.GetResult("SHOW COLUMNS FROM " + table.Name);
                foreach (DataRow myCol in columns.Rows)
                {
                    var msCol = new Wellcode.Dbms.Column();

                    msCol.Name = (string)myCol["Field"];
                    SetColumnType((string)myCol["Type"], msCol);
                    msCol.IS_NULLABLE = myCol["Null"].ToString();

                    // default value
                    var defvalue = myCol["Default"].ToString();
                    if (false == basicSchema && !string.IsNullOrEmpty(defvalue) && Zeichen.Ne(defvalue, "NULL"))
                    {
                        msCol.COLUMN_DEFAULT = "'" + defvalue + "'";
                        msCol.DEFAULT_CONSTRAINT_NAME = "DF_" + table.Name + "_" + msCol.Name;
                    }

                    // identity field
                    if (false == basicSchema && Zeichen.Eq(myCol["Extra"].ToString(), "auto_increment"))
                    {
                        msCol.IS_IDENTITY = true;
                        msCol.INCREMENT_VALUE = 1;
                        msCol.SEED_VALUE = 1;
                        msCol.LAST_VALUE = 1;

                        // auto-inkrement doesn't have default values
                        msCol.COLUMN_DEFAULT = null;
                        msCol.DEFAULT_CONSTRAINT_NAME = null;
                    }
                    table.Columns.Add(msCol);
                }

                // when creatring basic data, ignore rest
                if (basicSchema)
                {
                    continue;
                }

                // add indexes to table
                var indexes = my.GetResult("SHOW INDEXES FROM " + table.Name);
                foreach (DataRow myIdx in indexes.Rows)
                {
                    var myKey = myIdx["Key_name"].ToString();

                    var idx = table.GetConstraint(myKey);

                    var isPrimary = false;
                    if (idx == null && Zeichen.Eq(myKey, "PRIMARY"))
                    {
                        if (table.PrimaryKey == null)
                        {
                            table.PrimaryKey = new Wellcode.Dbms.TableConstraint();
                            table.PrimaryKey.Name = "PK_" + table.Name;
                        }
                        idx = table.PrimaryKey;
                        isPrimary = true;
                    }
                    else if (idx == null)
                    {
                        idx = new Wellcode.Dbms.Index();
                        idx.Name = myKey;
                    }
                    var col = new Wellcode.Dbms.ConstraintColumn();
                    col.Name = myIdx["Column_name"].ToString();
                    idx.Columns.Add(col);

                    if (isPrimary) // make sure, that primary key fields are not null
                    {
                        var tcol = table.GetColumn(col.Name);
                        if (tcol != null)
                            tcol.IS_NULLABLE = "NO";
                    }
                }

                // foreign constraints
                var myForeigns = my.GetResult(string.Format(@"
                    select ku.constraint_schema, ku.constraint_name, ku.TABLE_NAME, ku.COLUMN_NAME
                         , ku.REFERENCED_TABLE_NAME, ku.REFERENCED_COLUMN_NAME, cs.DELETE_RULE, cs.UPDATE_RULE
                      from information_schema.key_column_usage ku
                      left join information_schema.referential_constraints cs  
                        on ku.table_name = cs.table_name 
                       and ku.constraint_name = cs.constraint_name
                       and ku.constraint_schema = cs.constraint_schema
                     WHERE ku.REFERENCED_TABLE_NAME IS NOT NULL 
                       AND ku.TABLE_NAME = '{0}'
                       AND ku.constraint_schema = '{1}'", table.Name, myDbName));
                foreach (DataRow myForeign in myForeigns.Rows)
                {
                    var name = myForeign["CONSTRAINT_NAME"].ToString();
                    var reftable = myForeign["REFERENCED_TABLE_NAME"].ToString();
                    if (string.IsNullOrEmpty(reftable)) // it's an index
                        continue;

                    var msForeign = (ForeignKey)table.GetConstraint(name);
                    if (msForeign == null)
                    {
                        msForeign = new Wellcode.Dbms.ForeignKey()
                        {
                            Name = name,
                            ForeignTable = reftable,
                            DELETE_RULE = myForeign["DELETE_RULE"].ToString(),
                            UPDATE_RULE = myForeign["UPDATE_RULE"].ToString(),
                        };
                        table.ForeignKeys.Add(msForeign);
                    }
                    msForeign.Columns.Add(new Wellcode.Dbms.ConstraintColumn()
                    {
                        Name = myForeign["COLUMN_NAME"].ToString(),
                    });
                }
            }

            return mydbms;
        }

        private void SetColumnType(string type, Wellcode.Dbms.Column msCol)
        {
            int len = 0;

            // type in format "type(len)"
            if (type.IndexOf("(") > 0)
            {
                var arr = type.Split(new char[] { '(', ')' });
                len = Convert.ToInt32(arr[1]);
                type = arr[0];
            }

            var rettype = type;

            if (Zeichen.Eq(type, "decimal"))
                rettype = "money";

            else if (Zeichen.Eq(type, "tinytext"))
            {
                rettype = "varchar";
                len = 255;
            }

            else if (Zeichen.Eq(type, "text"))
            {
                rettype = "varchar";
                len = -1;
            }

            else if (Zeichen.Eq(type, "MEDIUMTEXT"))
            {
                rettype = "varchar";
                len = -1;
            }

            else if (Zeichen.Eq(type, "longtext"))
            {
                rettype = "varchar";
                len = -1;
            }

            if (Zeichen.Ne(rettype, "char") && Zeichen.Ne(rettype, "varchar"))
                len = 0;

            msCol.CHARACTER_MAXIMUM_LENGTH = len;
            msCol.DATA_TYPE = rettype;
        }
    }

    public class OdbcConnector
    {
        public OdbcConnector(string connstr)
        {
            this.CreateConnection(connstr);
        }

        OdbcConnection Connection;

        public OdbcConnection CreateConnection(string connstr)
        {
            this.Connection = new OdbcConnection(connstr);
            this.Connection.Open();
            return this.Connection;
        }

        public OdbcCommand CreateCommand(string sql)
        {
            var item = this.Connection.CreateCommand();
            item.CommandText = sql;
            return item;
        }

        public DataTable GetResult(string sql)
        {
            var ds = new DataTable();
            var adapter = new OdbcDataAdapter(this.CreateCommand(sql));
            adapter.Fill(ds);
            return ds;
        }
    }

    public class DynFactory
        : WebEntityFactory<WebEntity, WebEntityList<WebEntity>>
    {
        public DynFactory(DynManager manager)
        {
            this.Manager = manager;
        }

        public override WebEntity CreateEntity()
        {
            throw new NotImplementedException();
        }
    }

    public class DynManager
        : EntityManager
    {

    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

using Wellcode.Dbms;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Odbc;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Wellcode.Basics;

using Wellcode.Frame;

using System.Data.Common;

namespace Wellcode.DbmsConverter

{

public class MySQLToMSSQLExporter

{

public delegate void ActionEventHandler(MySQLToMSSQLExporter sender, RdbmsActionEventArgs e);

public event ActionEventHandler Action;

public string MySQLConnectionString = "DSN=mysql";

public bool MySQLOverODBC = true;

public bool MySQLOverNet = false;

public bool SchemaOnly = false;

public string MsConnectionString = "";

public string SchemaFileName = "mysql-schema.xml";

protected DynManager Manager = new DynManager();

public MySQLToMSSQLExporter(string mySqlCn, string msCn)

{

this.MySQLConnectionString = mySqlCn;

this.MsConnectionString = msCn;

}

public bool Migrate(ref string errorString)

{

try

{

var my = new OdbcConnector(this.MySQLConnectionString);

var ms = new DbConnector(this.MsConnectionString, TransactionLevel.NoTransaction);

// create the basic schema definition from mysql database

// (blank table/columns definitions)

FireAction("Creating Schema ...");

var mydbms = new MySQLSchemaReader().CreateSchema(my, true);

// apply schema to destination ms sql database

FireAction("transfer schema to the destination database ...");

var controller = new RdbmsController();

controller.Model = mydbms;

//controller.SetSchemaFile(this.SchemaFileName);

controller.ConnectionString = this.MsConnectionString;

controller.Action += controller_Action;

controller.Upgrade(UpgradeMode.Upgrade);

FactoryDbms.SetStandardModel(mydbms); // prevent reading dbms model from database

// import all tables with columns

for (int j = 0; false == this.SchemaOnly && j < mydbms.Tables.Count && mydbms.Tables[j].Columns.Count > 0; j++)

{

var table = mydbms.Tables[j];

FireAction("Transfer Data to table " + table.Name + " ...");

// delete all existing contents

var cmd = ms.CreateCommand("DELETE FROM " + table.Name);

cmd.ExecuteNonQuery();

// use the entity model to import data

DynFactory t = new DynFactory(this.Manager);

t.IsChangeLogEnabled = false;

t.TableName = table.Name;

// dummy primary key

t.Fields = new string[1];

t.Fields[0] = table.Columns[0].Name;

var ds = my.GetResult("SELECT * FROM " + table.Name);

var cnt = ds.Rows.Count;

var num = 0;

foreach (DataRow r in ds.Rows)

{

var entity = new WebEntity();

entity.AquireEntityManager += entity_AquireEntityManager;

for (int i = 0; i < ds.Columns.Count; i++)

{

var o = r[i];

if (DBNull.Value.Equals(o))

o = null;

entity.Add(ds.Columns[i].ColumnName, o, ds.Columns[i].DataType);

}

// write to ms sql database and protocoll errors

try

{

t.SaveEntity(ms, entity);

}

catch (Exception e)

{

FireAction("Error: " + e.ToString());

}

num++;

if (num % 100 == 0)

{

FireAction(string.Format("{0}: {1} / {2} imported...\r\n{3} von {4} tables imported", table.Name, num, cnt, j, mydbms.Tables.Count));

}

FireAction("Reset the assigned model for future usage.");

FactoryDbms.ResetModel();

FireAction("Import done.");

// create foreigh keys now and rest of dbms structure now

FireAction("create foreign keys after import data ....");

mydbms = new MySQLSchemaReader().CreateSchema(my, false);

controller.Model = mydbms;

controller.Action -= controller_Action;

controller.Upgrade(UpgradeMode.Upgrade);

return true;

}

catch (Exception ex)

{

errorString = ex.ToString();

}

return false;

}

void entity_AquireEntityManager(WebEntity sender, EntityManagerEventArgs e)

{

e.Manager = this.Manager;

}

void controller_Action(RdbmsController sender, RdbmsActionEventArgs e)

{

if (e.Action == ActionMode.Init)

e.Wrapper.UpgradeForeignKeys = false;

}

void FireAction(string action)

{

if (this.Action == null)

return;

var e = new RdbmsActionEventArgs();

e.Comments = action;

this.Action(this, e);

}

public bool TestMySQLConnectionString(ref string errorString)

{

return this.TestConnectionString<OdbcConnection>(this.MySQLConnectionString, ref errorString);

}

public bool TestMsConnectionString(ref string errorString)

{

return this.TestConnectionString<SqlConnection>(this.MsConnectionString, ref errorString);

}

protected bool TestConnectionString<Vendor>(string connStr, ref string errorString)

where Vendor : DbConnection, new()

{

try

{

var cn = new Vendor();

cn.ConnectionString = connStr;

cn.Open();

cn.Close();

return true;

}

catch (Exception ex)

{

errorString = ex.ToString();

}

return false;

}

public class MySQLSchemaReader

{

/// <summary>

///

/// </summary>

/// <param name="my"></param>

/// <param name="basicSchema">Creates schema without indexes, constraints, identities, etc.</param>

/// <returns></returns>

public DatabaseModel CreateSchema(OdbcConnector my, bool basicSchema)

{

// the datamodel of mySQL database

var mydbms = new Wellcode.Dbms.DatabaseModel();

var myDbs = my.GetResult("SELECT database() AS SCHEMANAME");

var myDbName = myDbs.Rows[0][0].ToString();

var tables = my.GetResult("SHOW TABLES");

foreach (DataRow row in tables.Rows)

{

var table = new Wellcode.Dbms.Table();

table.Name = (string)row[0];

mydbms.Tables.Add(table);

// add columns to the table

var columns = my.GetResult("SHOW COLUMNS FROM " + table.Name);

foreach (DataRow myCol in columns.Rows)

{

var msCol = new Wellcode.Dbms.Column();

msCol.Name = (string)myCol["Field"];

SetColumnType((string)myCol["Type"], msCol);

msCol.IS_NULLABLE = myCol["Null"].ToString();

// default value

var defvalue = myCol["Default"].ToString();

if (false == basicSchema && !string.IsNullOrEmpty(defvalue) && Zeichen.Ne(defvalue, "NULL"))

{

msCol.COLUMN_DEFAULT = "'" + defvalue + "'";

msCol.DEFAULT_CONSTRAINT_NAME = "DF_" + table.Name + "_" + msCol.Name;

}

// identity field

if (false == basicSchema && Zeichen.Eq(myCol["Extra"].ToString(), "auto_increment"))

{

msCol.IS_IDENTITY = true;

msCol.INCREMENT_VALUE = 1;

msCol.SEED_VALUE = 1;

msCol.LAST_VALUE = 1;

// auto-inkrement doesn't have default values

msCol.COLUMN_DEFAULT = null;

msCol.DEFAULT_CONSTRAINT_NAME = null;

}

table.Columns.Add(msCol);

}

// when creatring basic data, ignore rest

if (basicSchema)

{

continue;

}

// add indexes to table

var indexes = my.GetResult("SHOW INDEXES FROM " + table.Name);

foreach (DataRow myIdx in indexes.Rows)

{

var myKey = myIdx["Key_name"].ToString();

var idx = table.GetConstraint(myKey);

var isPrimary = false;

if (idx == null && Zeichen.Eq(myKey, "PRIMARY"))

{

if (table.PrimaryKey == null)

{

table.PrimaryKey = new Wellcode.Dbms.TableConstraint();

table.PrimaryKey.Name = "PK_" + table.Name;

}

idx = table.PrimaryKey;

isPrimary = true;

}

else if (idx == null)

{

idx = new Wellcode.Dbms.Index();

idx.Name = myKey;

}

var col = new Wellcode.Dbms.ConstraintColumn();

col.Name = myIdx["Column_name"].ToString();

idx.Columns.Add(col);

if (isPrimary) // make sure, that primary key fields are not null

{

var tcol = table.GetColumn(col.Name);

if (tcol != null)

tcol.IS_NULLABLE = "NO";

}

// foreign constraints

var myForeigns = my.GetResult(string.Format(@"

select ku.constraint_schema, ku.constraint_name, ku.TABLE_NAME, ku.COLUMN_NAME

, ku.REFERENCED_TABLE_NAME, ku.REFERENCED_COLUMN_NAME, cs.DELETE_RULE, cs.UPDATE_RULE

from information_schema.key_column_usage ku

left join information_schema.referential_constraints cs

on ku.table_name = cs.table_name

and ku.constraint_name = cs.constraint_name

and ku.constraint_schema = cs.constraint_schema

WHERE ku.REFERENCED_TABLE_NAME IS NOT NULL

AND ku.TABLE_NAME = '{0}'

AND ku.constraint_schema = '{1}'", table.Name, myDbName));

foreach (DataRow myForeign in myForeigns.Rows)

{

var name = myForeign["CONSTRAINT_NAME"].ToString();

var reftable = myForeign["REFERENCED_TABLE_NAME"].ToString();

if (string.IsNullOrEmpty(reftable)) // it's an index

continue;

var msForeign = (ForeignKey)table.GetConstraint(name);

if (msForeign == null)

{

msForeign = new Wellcode.Dbms.ForeignKey()

{

Name = name,

ForeignTable = reftable,

DELETE_RULE = myForeign["DELETE_RULE"].ToString(),

UPDATE_RULE = myForeign["UPDATE_RULE"].ToString(),

};

table.ForeignKeys.Add(msForeign);

}

msForeign.Columns.Add(new Wellcode.Dbms.ConstraintColumn()

{

Name = myForeign["COLUMN_NAME"].ToString(),

});

}

return mydbms;

}

private void SetColumnType(string type, Wellcode.Dbms.Column msCol)

{

int len = 0;

// type in format "type(len)"

if (type.IndexOf("(") > 0)

{

var arr = type.Split(new char[] { '(', ')' });

len = Convert.ToInt32(arr[1]);

type = arr[0];

}

var rettype = type;

if (Zeichen.Eq(type, "decimal"))

rettype = "money";

else if (Zeichen.Eq(type, "tinytext"))

{

rettype = "varchar";

len = 255;

}

else if (Zeichen.Eq(type, "text"))

{

rettype = "varchar";

len = -1;

}

else if (Zeichen.Eq(type, "MEDIUMTEXT"))

{

rettype = "varchar";

len = -1;

}

else if (Zeichen.Eq(type, "longtext"))

{

rettype = "varchar";

len = -1;

}

if (Zeichen.Ne(rettype, "char") && Zeichen.Ne(rettype, "varchar"))

len = 0;

msCol.CHARACTER_MAXIMUM_LENGTH = len;

msCol.DATA_TYPE = rettype;

}

public class OdbcConnector

{

public OdbcConnector(string connstr)

{

this.CreateConnection(connstr);

}

OdbcConnection Connection;

public OdbcConnection CreateConnection(string connstr)

{

this.Connection = new OdbcConnection(connstr);

this.Connection.Open();

return this.Connection;

}

public OdbcCommand CreateCommand(string sql)

{

var item = this.Connection.CreateCommand();

item.CommandText = sql;

return item;

}

public DataTable GetResult(string sql)

{

var ds = new DataTable();

var adapter = new OdbcDataAdapter(this.CreateCommand(sql));

adapter.Fill(ds);

return ds;

}

public class DynFactory

: WebEntityFactory<WebEntity, WebEntityList<WebEntity>>

{

public DynFactory(DynManager manager)

{

this.Manager = manager;

}

public override WebEntity CreateEntity()

{

throw new NotImplementedException();

}

public class DynManager

: EntityManager

{

}

Limitations
This is just an example of how things can work. Not all the mysql data types are mapped to the data types of ms sql server.

Leave a Reply Cancel reply