Displaying results from a stored procedure into datagridview

Question

5.00/5 (1 vote)

See more:

is there any possible way of displaying a stored procedures results without knowing the parameters names or properties? Im doing a project which contains such a problem. i need to display the results of the procedure in a datagridview. First the user selects a database name, then selects a procedure from that database and clicks a button saying 'view results' i've done all that except the part about displaying the results. is there any possible way to so?...

if you need anymore information about my question, please ask and i will provide more information.

Thanks in advance...

I am using SQL Server 2008, Visual Studio 2010

Posted 1-Jun-12 23:24pm

D3m0n1CMoNkEy

Updated 1-Jun-12 23:28pm

v2

Add a Solution

3 solutions

Solution 1

Well, if you run the SP using a SqlCommand[^] etc. just bind the resulting DataTable[^] to the DataGridView[^] (assigning it to DataGridView.DataSource Property[^]).
The DataGridView should now automatically populate its columns and fill the rows with the results.
It will basically look like you ran the SP using SQL Server.
Example:

C#

// I am assuming you already got a DataTable, because you said you got the results.
DataTable dt = GetStoredProcResults();
DataGridView1.DataSource = dt;

And that's all there is to it :)

Edit:
Here is a rough example of how to get the data from a stored procedure:

C#

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "(local)";
builder.InitialCatalog = "Northwind";
builder.IntegratedSecurity = true;
using (SqlConnection conn = new SqlConnection(builder.ToString()))
{
   using (SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;
      // Think of a smart way to use any parameters.
      // For example create a dictionary of name/value
      // pairs and create parameters like this:
      // foreach (var pair in dict) {
      //    cmd.Parameters.AddWithValue(pair.Key, pair.Value); }
      using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
      {
         DataTable dt = new DataTable();
         adapter.Fill(dt);
         dataGridView1.DataSource = dt;
      }
   }
}

For more information see the following articles:
SqlConnection[^]
SqlCommand[^]
SqlDataAdapter[^]
Note that all of the above Classes Inherit from Interfaces and base classes. OleDb, Oracle and any other database stuff works using these Interfaces and baseclasses. If you know this stuff for Sql you know it for Oracle, Access, etc. :)

Posted 1-Jun-12 23:32pm

Sander Rossel

Updated 2-Jun-12 1:07am

v4

Comments

D3m0n1CMoNkEy 2-Jun-12 6:26am

I have not yet gotten the results of the stored procedure just the syntax of the procedure (the create proc, select * from, etc). The GetStoredProcResults() part of your code... where do you get it and what is in it?

geigy 31-Jul-13 4:32am

Thanks, guys. Your responses helped greatly when the standard docs failed to cover the topic. Particular thanks to Naerling and Tony.

Sander Rossel 2-Jun-12 7:08am

I have edited my answer. I hope it is clear to you now. I will not do all the work for you, but this should give you a very good start.

Solution 3

Private DataTable TestingSP() {
DataTable dt = new DataTable();
SqlCommand comando = new SqlCommand("USE DB_NAME EXEC dbo.SP_NAME @parameter1= @par1 , @parameter2 = @par2", U_STRING_CONNECTION);
comando.Parameters.Clear();
comando.Parameters.AddWithValue("@par1 ", TextBox1.Text);
comando.Parameters.AddWithValue("@par2", TextBox2.Text);
SqlDataAdapter adapter = new SqlDataAdapter(comando);
using (adapter);
adapter.Fill(dt);
End using;
return dt;
}

Posted 23-Jan-16 11:35am

Efrain Mejias Castillo

Updated 23-Jan-16 12:08pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tony Tom.k · Accepted Answer · 2012-06-02T01:16:00

C#

public DataTable Search()
       {
           DataTable lRowsAffected;
           _customQuery = new CustomQuery();
           SqlCommand lobjSqlCmd = new SqlCommand();
           SqlParameter lobjSqlParam = null;

           lobjSqlCmd.CommandText = "USP_TMSSearchStudio";
           lobjSqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

           lobjSqlParam = new SqlParameter("@Studio",System.Data.SqlDbType.VarChar);
           lobjSqlParam.Value = _strStudio;
           lobjSqlCmd.Parameters.Add(lobjSqlParam);

           lobjSqlParam = new SqlParameter("@Studio2",System.Data.SqlDbType.VarChar);
           lobjSqlParam.Value = _strStudio2;
           lobjSqlCmd.Parameters.Add(lobjSqlParam);


           lobjSqlParam = new SqlParameter("@ErrorDescription",System.Data.SqlDbType.VarChar);
           lobjSqlParam.Value = _strErrorDescription;
           lobjSqlCmd.Parameters.Add(lobjSqlParam);


           lobjSqlParam.Direction = ParameterDirection.Output;
           lRowsAffected = _customQuery.ExecuteQuery(lobjSqlCmd);
           if (lobjSqlCmd.Parameters["@ErrorDescription"].Value is DBNull)
               Error = "";

           else
               Error = (string)(lobjSqlCmd.Parameters["@ErrorDescription"].Value);
           return lRowsAffected;
       }

IN CustomQuery.cs yOU HAVE TO WRITE

C#

private DataTable _ExecuteQuery_SQL(SqlCommand pSQLCommand)
        {
            DataTable lDataTable = null;
            lDataTable = new DataTable();
            pSQLCommand.CommandTimeout = 1200;
            SqlDataAdapter lSqlDataAdopter = new SqlDataAdapter(pSQLCommand);
            lSqlDataAdopter.Fill(lDataTable);
            return lDataTable;
        }

In Gridview Form You Have to Write This Call and Call SP function

C#

DataTable dtsearch = new DataTable();
                dtsearch =Search();
                
                if (dtsearch.Rows.Count > 0)
                {
                    pnlStudioInfo.Visible = true;
                    gdvStudio.DataSource = dtsearch;
                    ViewState["file"] = dtsearch;
                    gdvStudio.DataBind();
                }