How to retrieve the records if I have .SQL script file using ADO.net

[Kiran]

Hi,
I need to retrieve records from SQL 2000 datbase using
ADO.Net
I have a huge select statement in .sql script file I need
to run it to get the records How Can I run it and get the
records

Can you guide me

 

[bspann]

Kiran,

I think what you are wanting to do is create a Stored Procedure in SQL
Server 2000. Then call the sp using ADO.NET. After ADO.NET gets the data,
it will be up to you as to how you would like to display the information
that is returned.

So the first thing to do is create the stored procedure.

Brian

 

[Kiran]

Brian,
Thanks for the reply.

I do not have the option to create a stored proc.
All I have is *.sql script file with a big Select
statement in it.
I tried to read the file and pass that sql statement to
the sqlCommand object but due to the comments in the sql
file, command object is failing to execute when I call
command.CommandText = strSql;
command.CommandTimeout = 0;
SqlDataReader myReader = command.ExecuteReader();

but if I copy the contents of the .sql file and run in
query analyser window it runs.

I am wondering if I can pass the .sql file name to
retrieve the recordset object.

Thanks
Kiran

 

[bspann]

I don't understand why you do not have access to create a Stored Procedure
but you have access to run queries? Creating a view would be better that
passing SQL from your application to the database engine. Anyway, here is a
thought.

You will need to write a method that strips out the comments. Use a
StringBuilder object to create your SQL statement. This is a better
resource than appending strings together. Anyway, this should keep you busy
for a while. Also, look at the Regex object. That might come in handy.

Brian

 

[Kiran]

Hi Brian,
Other applications generate these script files so we
do not want to create thousands of stored procedures.
I tried to strip off the comments in the file and append
SQL statement in the file using stringBuilder it worked.
Thanks for your input.

It would be a Nice to have feature if some ADO oject
accepts *.sql script file and returns the recordset object.

Have a great day you Microsoft geeks

Thanks
Kiran