excecuting file containing sql script of a database

deepa

how to execute a text file, which contains sql script of a database.
I need to execute this file in my code.. i tried this function:
private void ExecSqlFileProcess(String fileName)
{
if (! File.Exists(fileName))
{
MessageBox.Show ("Specified .SQL File Not Found.","Net Fare Database Upgrade Tool",MessageBoxButtons.OK);
return;
}
using (StreamReader sr = new StreamReader(fileName))
{
String line,line1;
String sqlLine= "" ;

while ((line = sr.ReadLine()) != null)
{
line1 = line.Trim();
int compareResult = String.Compare(line1, "GO", true, CultureInfo.InvariantCulture);

if(compareResult != 0)
sqlLine += line + Environment.NewLine ;
//sqlLine += line + c.ToString() ;
else
{
if(sqlLine != "")
{
try
{
int i = SqlHelper.ExecuteNonQuery(connectionString,CommandType.Text,"USE "+DatabaseName+" "+sqlLine);
}
catch(Exception Ex)
{
MessageBox.Show (Ex.Message.ToString(),"Net Fare Database Upgrade Tool",MessageBoxButtons.OK ,MessageBoxIcon.Error );;
sqlLine = "";
}
}
sqlLine = "";
}
}//While
}//Using
}

but i got the following errors for all stored procedure scripts..but it dropped the stored procedure..it only did not create it..following error comes:
'Create Procedure' must be first statement in the query batch.
Must declare variable @IATACodeName, @IATADescription..etc..

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anwar Buchoo20y ago
Another thing from a performance view:

Avoid using something like :

sqlLine += line + Environment.NewLine ;

especially within loops because each time the += or line + Environment.NewLine is evaluated, another string object is created in memory. Consequently, if this is inside a loop consisting of many iterations, you will find that a lot of memory will be consumed.

Istead, use the StringBuilder class to concatenate your strings like:

         StringBuilder digits = new StringBuilder();

         for(int i = 0; i < 100; i++)
         {
            digits.Append(i.ToString(currentCulture));
         }

Rgds.
0
Anwar Buchoo20y ago
Try someting like:

Process.Start("isql.exe", @"C:\myPath\myFile.sql");

Rgds.

NB:You can, additionally, generate a batch file (.bat) which will in turn make the call to the DB.
0
Binu Subi20y ago
Just want to add,
Take a look into Process class (instead of Shell() ) if you want to execute an application command (like isql with file as param )

Cheers!
0
Gokul Tyagi20y ago
Hi,

You can use iSql utility to execute as SQL script file.

Syntax

isql
    [-?] |
    [-L] |
    [
        {
            {-U login_id [-P password]}
            | –E
        }
        [-S server_name] [-H wksta_name] [-d db_name]
        [-l time_out] [-t time_out] [-h headers]
        [-s col_separator] [-w column_width] [-a packet_size]
        [-e] [-x max_text_size]
        [-c cmd_end] [-q "query"] [-Q "query"]
        [-n] [-m error_level] [-r {0 | 1}]
        [-i input_file] [-o output_file] [-p]
        [-b] [-O]

0

excecuting file containing sql script of a database

Syntax