execute sql file using c sharp

Question

 

hi All,

Do you know of a way I can execute an SQL file after connecting to the database

 

/*

try

{

Conn.Open();

Sql = "SELECT top 10 cst_sort_name_dn FROM co_customer (NOLOCK) WHERE cst_sort_name_dn like 'a%' ";

Cmd = new SqlCommand(Sql, Conn);

try

{

Reader = Cmd.ExecuteReader();

while (Reader.Read())

{

LblResult.Text +=Reader.GetString(0) + "\n";

}

}

catch (SqlException ae)

{

MessageBox.Show(ae.Message.ToString());

}

}

 

*/

 

I know that the code above work for a sql string.  However, i'm trying to open an *.sql file that have around 300, 000 characters and from testing, the string is too big if i open the file and read it line by line.  is there an alternative for line SqlCommand(Sql, Conn) where Sql is the .sql file?  Please help.  Thank you in advance.

Answer 1

kicking it up.  Alright, i found that string can store as much as 42, 000 character.  i can break the file into 7-8 strings but this won't resolve the problem permanently.  Does any one know how to go around this problem?  Please don't hesitate to reply. 

 

Answer 2

Is the string too big when you try to append the text?  If this is a performance issue, you may want to try the StringWriter class.  It takes a StringBuilder in the constructor, and is much faster than concatenating strings.

 

Code Snippet

Stream inStream = File.Open("myfile.sql", FileMode.Open);

StreamReader reader = new StreamReader(inStream);

StringBuilder builder = new StringBuilder();

StringWriter writer = new StringWriter(builder);

writer.Write(reader.ReadToEnd());

reader.Close();

writer.Close();

 

string sqltext = builder.ToString();

 

 

Answer 3

 

Wow. 

 

Looks like you would do better breaking your sql code up into smaller chunks.

Answer 4

you are the man.  I test with your code to find out the length of the sqlext string and it has 300, 000 characters.  I'll try to print it now to see if it is the same as the sql file.  Thank man.  I hope this works.

 

Answer 5

hey David,

I want to thank you for helping me with this.  I create a new file and write sqlext into it and then compare it with the original sql file and they both are identical.  thanks a lot man.

 

Answer 6

My pleasure!