Made this query in SQL Server Management Studio Express how to use it?

For my database I create a new query and wrote

select * from db开发者_JAVA百科name

using SQL Server Management Studio Express 2005. Saved it by name SQLQuery1.sql on the desktop.

Now I would like to call this query from C# code when an ASP.NET button is clicked and display the results in a gridview.

How do I call the query? Can I tell Visual Studio 2008 to please execute the query stored in 'sqlquery1.sql' ?

Where do I store the results so that I can bind them the display controls like a gridview, or traverse the data?

This is a website in C#, ASP.NET, Visual Studio 2008, and database is on the same computer using SQL Server 2005 Express

You cannot just execute a SQL script you stored on your computer's desktop from an ASP.NET website.

You can either:

• turn your query into a stored procedure in SQL Server, something like:

  CREATE PROCEDURE dbo.proc_MyQuery
  AS
     SELECT (list of columns) 
     FROM dbo.MyTable
     WHERE (condition)
  

  When you do this, you can create a SqlCommand in your C# code and call that stored procedure and retrieve the results back.

or:

• you can execute the query directly from your C# code by creating a SqlConnection and a SqlCommand object and running that SQL statement.

These are both absolutely basic ADO.NET features - you should find tons of learning resources online for this.

For instance:

• ADO.NET 101 - Data Access with ADO.NET
• ADO.NET 101: SQL Connection
• ADO.NET 101: SqlCommand / Your guide to using this ADO.NET object to execute SQL Server commands
• Using ADO.NET for beginners
• ADO.NET for Beginners - Part 1

Which ever way you go, you basically need to have a SqlConnection to your database, and then a SqlCommand to execute the query. If you want to store the data so you can both bind it to a Gridview as well as navigate it in code, you probably want to store it in a DataTable. So your code would look something like this:

DataTable resultTable = new DataTable();

using(SqlConnection con = new SqlConnection("your connection string here"))
{
    string sqlStmt = "SELECT (columns) FROM dbo.YourTable WHERE (condition)";

    using(SqlCommand cmd = new SqlCommand(sqlStmt, con))
    {
        SqlDataAdapter dap = new SqlDataAdapter(cmd);
        dap.Fill(resultTable);
    }
}

and then to bind to the gridview, you'd use something like:

myGridView.DataSource = resultTable;
myGridView.DataBind();

and to navigate the DataTable, you can step through its .Rows() collection of data rows.

In your server side event handler you will need to open the file from the desktop, read in the SQL, and then use the SQL to databind the grid.

This would depend on the identity that the website code runs under having read access to the desktop. By default it won't so you would need to give that identity (NETWORK_SERVICE?) permission. This could open up horrible security holes.

You would be better off moving the SQL somewhere more accessible, like web.config, or a file in the website directory.