Get information to combobox from store procedure in C#

Question

1.00/5 (1 vote)

See more:

I want to display the Employee Name from Store procedure in C# window form and to access the Employee ID in combobox. I wrote the Store Procedure to get the Employee Info

SQL

Create Procedure Get_Employee_Info
AS
Declare @Error Int

SELECT Employee_Id
,      EmployeeName
FROM   Employee

 SELECT @Error = @@error  
 RETURN @Error

What I have tried:

When I run the EXEC Get_Employee, I get the information and I don't know how to display the data to combobox.Please guide me.

Posted 3-Jun-18 18:35pm

Member 13338075

Updated 3-Jun-18 19:48pm

Add a Solution

2 solutions

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)

Manoj S Pant · Answer 1 · 2018-06-03T19:49:00

You have Store procedure "Get_employee_Info" in Database,
Now
First, you need to get the data from this stored procedure in C#.
Second, you need to Bind the combo box with the data table by setting "Datasource","DataMember" and "ValueMamber".

Steps 1: Get the data into C# from Store procedure:

Copy from this link: 
https://blogs.msmvps.com/deborahk/dal-retrieve-a-datatable-using-a-stored-procedure/

Public DataTable ExecuteDataTable(string storedProcedureName,  
                                     params SqlParameter[] arrParam)  
{ 
    DataTable dt = new DataTable();

    // Open the connection 
    using (SqlConnection cnn = new SqlConnection( 
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals; 
                                    Integrated Security=True")) 
    { 
        cnn.Open();

        // Define the command 
        using (SqlCommand cmd = new SqlCommand()) 
        { 
            cmd.Connection = cnn; 
            cmd.CommandType = CommandType.StoredProcedure; 
            cmd.CommandText = storedProcedureName;

            // Handle the parameters 
            if (arrParam != null) 
            { 
                foreach (SqlParameter param in arrParam) 
                    cmd.Parameters.Add(param); 
            }

            // Define the data adapter and fill the dataset 
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 
            { 
                da.Fill(dt); 
            } 
        } 
    } 
    return dt; 
}

Steps 2: Bind the data table which we get from Stored procedure to combo box.

// Get the data from SP.
DataTable empployeeTable = Dac.ExecuteDataTable("Get_Employee_Info", null);

 // bind employee data table into combo box.
 comboBoxEmployee.DataSource = empployeeTable;
 comboBoxEmployee.DisplayMember = "EmployeeName";
 comboBoxEmployee.ValueMember = "Employee_Id";

an0ther1 · Answer 2 · 2018-06-03T19:43:00

Solution 1

To do this you would bind the data as a DataTable to the control and then set the DisplayMember & ValueMember properties, the below should assist;

C#

DataTable dtEmployee;
using(SqlConnection connDb = new SqlConnection(ConnectionString))
{
    conn.Open();
    using(SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Get_Emplyee_Info";
        using(SqlDataAdapter adap = new SqlDataAdapter(cmd))
        {
            adap.Fill(dtEmployee);
        }
    }
    conn.Close();
}
comboBox1.DataSource = dtEmployee;
comboBox1.DisplayMember = "EmployeeName";
comboBox1.ValueMember = "Employee_Id";
// complete

Kind Regards

Posted 3-Jun-18 19:43pm

an0ther1

Comments

Member 13338075 4-Jun-18 4:11am

In my Employee class I wrote the code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.Sql;
using System.Data.SqlClient;
using DataAccess;

namespace Employee.class
{
    class Employee
    {
        DataOperation DataProcessor = new DataOperation();

        private static Employee objEmployee = null;
        const string SP_Get_Employee= "Get_Employee";


        private Employee()
        { }

        public static Township GetEmployee()
        {
            if (Employee.objEmployee == null) return Employee.objEmloyee = new Employee();
            else return Employee.objEmployee;
        }

        public DataTable GetAllEmployee()
        {
            DataTable dt = this.DataProcessor.GetDataTable(Employee.SP_Get_Employee);
            return dt;
        }

    }
}