Display data from database using stored procedure C#

Question

I've been tasked with creating a console application that displays the null values of a job-input system (a program that employees use to declare which client they're working on, etc).

All I've been told is that there is a stored procedure called spr_Reports_JobsBreakdown4 with these parameters:

@user nvarchar(20)
@datefrom datetime
@dateto datetime

I somehow have to create a console application that uses this stored procedure to display the null values, meaning that someone forgot to do the job input. All I've managed so far is to connect to the database..

I've watched and read tutorials but none seemed to help.

I'm new to C#..help ?!?

This is the code I used to connect to the database.

SqlConnection sqlConnection = new SqlConnection("Data Source=DEFIANT\\SQL2012; Initial Catalog=HRD_MIS_Jobs2009; User ID=id; Password=password");

try
{
    sqlConnection.Open();
    Console.WriteLine("successful connection");
}
catch (Exception ex)
{
    Console.WriteLine(ex);
}

Very droll, I've already done so but to no avail. I'd appreciate some solid help. — David Micallef, Jun 24 '15 at 08:23
@DavidMicallef can you post the code you wrote to connect to the database? — sblandin, Jun 24 '15 at 08:26
This sounds more like the job of a report than a console app. Sql Server Reporting Services would do this easily (assuming it was available to you). — Chris Latta, Jun 24 '15 at 08:30
possible duplicate of [How to execute a stored procedure within C# program](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) — Palanikumar, Jun 24 '15 at 08:33
@Oluwafemi.. big lie? I'm new to C# and could barely understand a thing of the tutorials. If you've got any help, I'd appreciate it. ChrisLätta. They've insisted on a console application :/ — David Micallef, Jun 24 '15 at 08:33
Can you run this stored procedure in SQL Server Management Studio? What output do you get? What rows does this stored procedure return? — marc_s, Jun 24 '15 at 08:45
@Oluwafemi... I had. Read. marc_s, I hadn't. But I'll give it a go. Cheers! — David Micallef, Jun 24 '15 at 08:55

score 1 · Answer 1 · answered Jun 24 '15 at 08:50

You basically need something like this:

set up your connection to the database
define the command to execute stored procedure
define and set values for the parameters
then iterate over the result set return by the stored procedure
do something useful with the data returned....

Code like this:

string connectionString = "Data Source=DEFIANT\\SQL2012; Initial Catalog=HRD_MIS_Jobs2009; User ID=id; Password=password";
string storedProcedureName = "dbo.spr_Reports_JobsBreakdown4";

// establish connection to DB, define command to execute stored procedure
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(storedProcedureName, conn))
{
    try
    {
        // set type of command to stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // define parameters
        cmd.Parameters.Add("@user", SqlDbType.NVarChar, 20);
        cmd.Parameters.Add("@datefrom", SqlDbType.DateTime);
        cmd.Parameters.Add("@dateto", SqlDbType.DateTime);

        conn.Open();
        Console.WriteLine("successful connection");

        // set parameter values
        cmd.Parameters["@user"].Value = ......;
        cmd.Parameters["@datefrom"].Value = ......;
        cmd.Parameters["@dateto"].Value = ......;

        // execute stored procedure, handle return values
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
             while (reader.Read())
             {
                 // handle your data here.....
             }

             reader.Close();
        }

        conn.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
}

sblandin · Answer 2 · 2015-06-24T09:02:09.800

OK, next step, after you have connected to your database you need a way to issue it a command. You can do something like this:

//Create a command object
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Text = "spr_Reports_JobsBreakdown4";
cmd.Connection = sqlConnection;

//Create its parameters
SqlParameter param1 = new SqlParameter("@user", SqlDbType.NVarChar);
param1.value = "someusername";

SqlParameter param2 = new SqlParameter("@datefrom", SqlDbType.DateTime);
param2.value = DateTime.Now - 1; //this should result in yesterday date/time

SqlParameter param3 = new SqlParameter("@datefto", SqlDbType.DateTime);
param3.value = DateTime.Now;

//Add parameters to the command
cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);

//Execute your command this way if the procedure returns just a single value
var res = cmd.ExecuteScalar();

//... Or else use a reader
using (SqlDataReader reader = cmd.ExecuteReader())
{
     while (reader.Read())
     {
         // you can read specific items from your reader with
         var col1Content = reader("col1");
     }

     reader.Close();
}

I hope it is enough to point you in the right direction for further research.

@ArunprasanthKV You are right I'll edit my post integrating part of marc_s answer — sblandin, Jun 24 '15 at 08:57

Peter Campbell · Answer 3 · 2015-06-24T09:08:20.473

In app.config

 <add name="DataSource" providerName="System.Data.SqlClient" connectionString="[connection string details]"/>
  </connectionStrings>

Then in data access class

 string user;
    datetime dateFrom;
    datetime DateTo;

   //populate parameters...

   ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["DataSource"];
     try
     {
         using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
         {
             conn.Open();
             using (SqlCommand cmd = new SqlCommand("spr_Reports_JobsBreakdown4 ", conn))
             {
                 cmd.CommandType = CommandType.StoredProcedure;

                 cmd.Parameters.Add(new SqlParameter("@user", user));
                 cmd.Parameters.Add(new SqlParameter("@datefrom", dateFrom));
                 cmd.Parameters.Add(new SqlParameter("@dateto", dateTo));

                 reader = cmd.ExecuteReader();

                 while (reader.Read())
                 {
                    //use reader to read in each returned row
                 }

                 reader.close();
             }
         }
      }
      catch(Exception ex){
         //handled exception
      }

Display data from database using stored procedure C#

3 Answers3