C# getting results from SQL Server database to show up in a combo box via class

Question

I am very new to C# so please forgive my ignorance. I have a SQL Server table and I am attempting to get select select query results from that table into a form combobox in C#.

What I am attempting to do is to create a class that will execute a stored procedure on the database side and my stumbling block is how to integrate that class into the code so the results will show up in a drop down in C#.

Here's what I have so far in C#. Your help is much appreciated.

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

namespace MedicalOffice
{
    public class DBAIdSelect
    {
        public void SelectPractice()
        {
            using (SqlConnection cn = new SqlConnection())
            {
                cn.ConnectionString = GetConnectionString();
                cn.Open(); 

                using (SqlCommand cmd = new SqlCommand("SelectPracticeID"))
                {
                    cmd.Connection = cn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private string GetConnectionString()
        {
            string conString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
            return conString;
        }
    }
 }

SQL Server table:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Practices]
(
    [PracticeID] [int] IDENTITY(1,1) NOT NULL,
    [PracticeName] [varchar](50) NULL,
    [Address1] [varchar](50) NULL,
    [Address2] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [State] [char](2) NULL,
    [Zip] [varchar](10) NULL,
    [IsActive] [bit] NULL,
    [DateCreated] [date] NULL 
        CONSTRAINT [DF_Practices_DateCreated]  DEFAULT (getdate()),
    [CreatedBy] [int] NULL,
    [DateModified] [date] NULL,
    [DateModifiedBy] [int] NULL,

    CONSTRAINT [PK_Practices] 
        PRIMARY KEY CLUSTERED ([PracticeID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

and stored procedure:

create proc [dbo].[SelectPracticeID]
as
    select PracticeID
    from dbo.Practices
GO

Answer 1

I would suggest creating DTO's/Simple Poco's for your data.

This "way" takes a little more effort....BUT your object model will be clean and nice and maintainable.

DataTables were good in 2003. It isn't 2003 anymore. The below is "poor man's ORM", but is easily migratable to Entity-Framework or NHibernate down the road. Datatables, Datasets, especially untyped ones, are not easily migrated.

[Serializable]
public partial class Practice
{
    public int PracticeKey { get; set; }                   
    public string PracticeName { get; set; }                   
}

[Serializable]
public class PracticeCollection : ICollection<Practice>
{
}   

internal static class PracticeDefaultLayout
{
    public static readonly int PRACTICE_KEY = 0;
    public static readonly int PRACTICENAME = 1;

}


public class PracticeSerializer
{   

    public PracticeCollection SerializeCollection(IDataReader dataReader)
    {
        Practice item = new Practice();
        PracticeCollection returnCollection = new PracticeCollection();
        try
        {

            int fc = dataReader.FieldCount;//just an FYI value

            int counter = 0;//just an fyi of the number of rows

            while (dataReader.Read())
            {

                if (!(dataReader.IsDBNull(PracticeSearchResultsLayouts.PRACTICE_KEY)))
                {
                    item = new Practice() { PracticeKey = dataReader.GetInt32(PracticeSearchResultsLayouts.PRACTICE_KEY) };

                    if (!(dataReader.IsDBNull(PracticeSearchResultsLayouts.PRACTICENAME)))
                    {
                        item.PracticeName = dataReader.GetString(PracticeSearchResultsLayouts.PRACTICENAME);
                    }


                    returnCollection.Add(item);
                }

                counter++;
            }

            return returnCollection;

        }
        //no catch here... see  http://blogs.msdn.com/brada/archive/2004/12/03/274718.aspx
        finally
        {
            if (!((dataReader == null)))
            {
                try
                {
                    dataReader.Close(); /* very important */ /* note, if your datareader had MULTIPLE resultsets, you would not close it here */
                }
                catch
                {
                }
            }
        }
    }
}








namespace MedicalOffice
{
    public class PracticeDataLayer
    {
        public ICollection<Practice> GetAllPractices()
        {
            using (SqlConnection cn = new SqlConnection())
            {
                cn.ConnectionString = GetConnectionString();
                cn.Open(); 

                using (SqlCommand cmd = new SqlCommand("SelectPracticeID"))
                {
                    cmd.Connection = cn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    IDataReader idr = cmd.ExecuteReader();
                    return new PracticeSerializer().SerializeCollection(idr);
                    ////  idr.Close(); /* very important, currently the serializer closes it..so commented out here */  /* note, if your datareader had MULTIPLE resultsets, you would close the datareader AFTER you used all the resultsets */
                }
            }
        }

        private string GetConnectionString()
        {
            string conString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
            return conString;
        }
    }
 }  

Answer 2

You can use this class whenever you want to call a stored procedure and it returns a DataTable :

class myclass
{
     public DataTable SelectData(string proc, SqlParameter[] param)
     {
            DataTable Table = new DataTable();
            SqlCommand Cmd = new SqlCommand();
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.CommandText = proc;
            Cmd.Connection = Acces.Connection;

            if (param != null)
                for (int i = 0; i < param.Length; i++)
                {
                    Cmd.Parameters.Add(param[i]);
                }

            SqlDataAdapter Adapter = new SqlDataAdapter(Cmd);
            Adapter.Fill(Table);

            return Table;
        }
     }

So whenever you want to use any stored procedure that returns a result set, use it , and if you want to execute any data :

public void ExecuteData(string proc, SqlParameter[] param)
{
    SqlCommand Cmd = new SqlCommand();
    Cmd.CommandType = CommandType.StoredProcedure;
    Cmd.CommandText = proc;
    Cmd.Connection = Acces.Connection;

    if (param != null)
    {
        Cmd.Parameters.AddRange(param);
    }

    Cmd.ExecuteNonQuery();
}

Make those two functions in your class and whenever you want to call the class to return you some data or execute some data like Insert,Update, Delete ...

You just need to call

function("Stored_Proc_Name", Parameters);

Example :

I want to get A select procedure like yours:

  myclass classs = new myclass();
  DataTable Table = new DataTable();
  Table  = classs.SelectData("SelectPracticeID",null); //=cause there is no paramters in your stored proc 

So the Table will hold the full informations that has been sent by your database