How to create a piece of C#.NET script to let MySql server execute a MySql Script?

Question

Suppose I have a myTest.sql scripts, which contains thousands of "create table blahblah" statements.

myTest.sql :

 CREATE TABLE A;
 CREATE TABLE A1;
 ....
 CREATE TABLE A1000;

What Im trying to achieve is that make an C# script to force MySql server EXECUTE the myTest.sql file, instead of doing

  using (MySqlConnection cn = new MySqlConnection(ConectionString))
  {
    MySqlCommand newCmd = new MySqlCommand("create statement here 1", cn);
    cn.Open();
    newCmd.ExecuteNonQuery();
    cn.Close();
  }

I dont want to repeat 1000 times or a for loop something like that. Thanks for all helps and please forgive my grammar problems.

AndyP · Accepted Answer · 2011-07-14 18:46:22Z

2

Could you load the myTest.sql file into a string and pass it to the MySqlCommand.

string myTestSql = IO.File.ReadAllText("myTest.sql");
...
MySqlCommand newCmd = new MySqlCommand(myTestSql, cn);

Should work as long as MySQL accepts commands separated by semicolons.

answered Jul 14, 2011 at 18:46

AndyP

361 bronze badge

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

1 Comment

user758360 Over a year ago

Thx Andy ! Which one should I use ? ExecuteNonQuery?

scwagner · Accepted Answer · 2011-07-14 18:42:54Z

0

You certainly don't have to open and close the connection every single time, but it would be the cleanest if you ran each of these one at a time and look at the result to ensure that the statement completed successfully. Unfortunately if you run a giant statement with 1000 statements and it fails, you don't have an easy way of determining which step(s) were successful and which have to be repeated.

answered Jul 14, 2011 at 18:42

scwagner

4,02523 silver badges16 bronze badges

1 Comment

user758360 Over a year ago

Thx scwagner ! Thats true .. But right now I dont have a better solution, besides table creatation statements .. I got other hundreds of create stored procedure statements ..

Tocco · Accepted Answer · 2011-07-14 18:52:25Z

0

Use this
You can do it using mysql command-line and shell with System.Process static methods if you want to use .net / c#.

answered Jul 14, 2011 at 18:52

Tocco

1,71311 silver badges14 bronze badges

3 Comments

user758360 Over a year ago

Thx Tocco. But I am not sure MySql Command-line is a mandatory install option.. I cant guarantee that every user installed mysql command-line

Tocco Over a year ago

I shouldn't know that is a Client application. Did you try to use @AndyP approach?

user758360 Over a year ago

Not yet .. wondering how to run "newCmd", like calling ExecuteNonQuery or the other..

Collectives™ on Stack Overflow

How to create a piece of C#.NET script to let MySql server execute a MySql Script?

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related