Using .NET SqlTypes with MySQL

Question

I'm writing an application that reads from a MySQL database and migrates data into a SQL Server database. I've chosen to write this in C# because of it's built in SQL structures and functionality. I was wondering, would it be possible to use the System.Data.SqlTypes.* structures in my classes where data from MySql will be read into?

ie.

// Pseudo code
IDataReader reader = /* { return DB reader } */;

while (reader.Read())
{
  SqlString str = (SqlString) reader["someVarCharField"];
  SqlBoolean b = (SqlBoolean) reader["someTinyIntField"];
}

Will this work or is there a better way of doing this?

I did something very similar a few years ago, and from what I recall I just used the built-in types. No need for the Sql specific types. — user1231231412
– user1231231412, Commented Jan 9, 2012 at 20:41
The only problem is converting TinyINT to Boolean (built-in) because there is no conversion from a string of "1" to true. I'm wondering if there is a simpler way to do this. — MarkP
– MarkP, Commented Jan 9, 2012 at 20:42
I did use the .net types, like string, long, boolean etc. In my case, for boolean i used Ctype in vb .net. => Ctype("1", boolean) — Esselans
– Esselans, Commented Jan 9, 2012 at 20:45
@MarkP You can use the Convert.ToBoolean() method. dotnetperls.com/convert-int-bool — user1231231412
– user1231231412, Commented Jan 9, 2012 at 20:59

Lloyd · Accepted Answer · 2012-01-09 21:00:02Z

2

If you know the MySqlDbType it is arguably best practice to simply do an Explicit conversion to a .Net type then use the correct SqlDbType on inserting the data into SqlServer. This removes any issues with casting, null references etc.

string myStringField = reader["someVarCharField"].ToString();
bool myBoolField = reader.GetBoolean("booleanField");
etc....

edited Jan 9, 2012 at 21:00

answered Jan 9, 2012 at 20:54

Lloyd

2,9422 gold badges23 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stephen Gilboy · Accepted Answer · 2012-01-09 21:09:18Z

I was thinking what if you just did something like, psudeo code

inside Sql Class...

while(reader.Read())
{
   int id = int.Parse(reader[0].ToString());
   string name = reader[1];
   bool active = false;
   if(int.Parse(reader[2].ToString()) == 1)
      active = true;

   SqlServerDBInsert(id, name, active);
}

... The insert

SQLServerDBInsert(int id, string name, bool active)
{

   string query = "INSERT INTO MyTable(ID, Name, Active) VALUES(@id, @name, @active)";
   using(SqlCommand cmd = new SqlCommand(query, conn))
   {
     SqlParamter param = new SqlParameter();
     param.ParameterName = "@id";
     param.Value = id;
     param.SqlDbType = SqlDbType.Int;
     cmd.Parameters.Add(param);
     ....
   }
}

I'm not sure about having a connection open to MySQL and SQLServer at the same time so there is that to be worried about.

Collectives™ on Stack Overflow

Using .NET SqlTypes with MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related