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

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;
        }
    }

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

1 solution

Solution 1

Add your solution here

Preview 0

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

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us