0

Currently I am running SQL queries like this:

SqlConnection conn = new SqlConnection(SQLCONNECTION);
var databases = new List<string>();

using (var comm = new SqlCommand(SQLQUERY, conn))
{
    conn.Open();

    using (var reader = comm.ExecuteReader())
    {
        while (reader.Read())
            databases.Add(reader.GetString(0));
    }
    conn.Close();
}

But now I want to run an SQL Query like this (I have tested it in SQL Server Management Studio):

USE DATABASE
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

How do I run a multiline query like this via the C# methods I've been using?

derekantrican
  • 1,891
  • 3
  • 27
  • 57
  • you can return multiple selects into a DataSet and it would access the datatables in a for loop so if you have 4 queries for example you would access the datatables as `DataSet.Tables[I]` which would be `DataSet.Tables[0], DataSet.Tables[1]....etc` – MethodMan May 31 '16 at 16:17
  • Why not change your connection string to point at the desired DB? – juharr May 31 '16 at 16:17
  • Did you checked http://stackoverflow.com/questions/8936665/sql-statement-in-multiple-lines-throwing-error already? – Fruchtzwerg May 31 '16 at 16:20
  • You could use a stored procedure instead of SQL strings in your code. – Christian Phillips May 31 '16 at 16:23

2 Answers2

1

Your DATABASE name should be part of the connection string (SQLCONNECTION) see www.connectionstrings.com for examples.

You could build a System.Text.StringBuilder to Append lines to the SQLQUERY string. Although building SQL in C# code like this is bad practice. Perhaps you could call a Stored Procedure instead?

Rodders
  • 2,425
  • 2
  • 20
  • 34
0

I read it into a string from a file:

string queryHolder = File.ReadAllText("FileNameGoesHere.txt");
using (var comm = new SqlCommand(queryHolder, conn))

That way you don't have to recompile code can just externally change

FirebladeDan
  • 1,069
  • 6
  • 14