Can anyone point me out how to get the datatable with selected rows from stored procedure?

Question

0.00/5 (No votes)

See more:

So I got 8 rows with column names from stored procedure. I read this as DataTable. However, I only want three columns with reference to employeeID.

C#

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Reflection.Emit;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Button = System.Windows.Forms.Button;
 
namespace CrossCrackApp
{
 
 
 
    public partial class Form1 : Form
    {
        public object DBManager { get; private set; }
 
        public string strConn = ConfigurationManager.AppSettings["CIM.ReportDataDB"];
 
        
        public int thresh_val = Convert.ToInt32(ConfigurationManager.AppSettings["thresh_val"]);
 
       
        public Form1()
        {
 
            InitializeComponent();
 
        }
 
 
        private void Form1_Load(object sender, EventArgs e)
 
        {
            display_cross_crack();             
            alarm_trigger();                  
            Show_Only_Respective_Lbl_Btn();   
            timer();                        
            lbl_timer();
            Console.WriteLine(thresh_val);
 
 
        }
 
 
        //This is Stored Procedure 1 called load_ooc()
        private DataTable load_ooc()
        {
 
            //Store procedure
 
            SqlConnection _con = new SqlConnection(strConn);
            DataTable rdr = new DataTable();
 
            try {
                _con.Open();
                lbl_error.Text = (_con is null) ? "Database not Connected" : "Database Connected";
                //create command
                SqlCommand cmd = _con.CreateCommand();
 
            //specify stored procedure to execute
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "LastHourCrossCrack";
 
            //execute command
           
            rdr.Load(cmd.ExecuteReader());
 
            //This is where the table is read I think so
            //Need To DO
            //Select the rows referenced to ID column
            //Then put it back to DataTable for the selected rows
            //return rdr now but need to change back to DataTable that only has selected rows
 
            //Trial & Errors
            //rdr.Select("")
            //if { rdr.Columns }
 
            }
            catch(Exception ex)
            {
                lbl_error.Text = ex.Message.ToString().Substring(0,100);
 
                lbl_error.ForeColor = Color.Red;
            }
            finally
            {
 
                if (_con == null)
                {
                    Console.WriteLine("Connection To Database is not succesful.");
                }
                
                _con.Dispose();
            }
            return rdr;
        }

What I have tried:

I have tried DataRow select and put it back to DataTable but I have got the error

Posted 23-Nov-22 19:00pm

MK_VEEE

Updated 23-Nov-22 20:00pm

OriginalGriff

v2

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2022-11-23T20:00:00

We have no idea what your stored procedure does, nor what parameters it might accept - so we can't help you with that at all.

But ... to request only rows that have a specific ID, you use a WHERE clause:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Age, Description FROM myTable WHERE ID = @ID", con))
        {
        cmd.Parameters.AddWithValue("@ID", myTextBox.Text);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int age = (int) reader["Age"];
                string desc = (string) reader["Description"];
                Console.WriteLine($"{age}\n{desc}");
                }
            }
        }
    }

Or for a datatable:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlDataAdapter da = new SqlDataAdapter("SELECT Age, Description FROM myTable WHERE ID = @ID", con))
        {
        da.SelectCommand.Parameters.AddWithValue("@ID", myTextBox.Text);
        DataTable dt = new DataTable();
        da.Fill(dt);
        myDataGridView.DataSource = dt;
        }
    }