Get collection of data from database through SQL class?

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 2023 Views - Last Post: 28 April 2015 - 06:48 AM Rate Topic: -----

#1 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Get collection of data from database through SQL class?

Posted 26 April 2015 - 03:49 AM

Hi Guys

In my WPF application i have a SQL class which i am trying to use to store all of my SQL queries so far this is working fine for when i want to return one piece of data like this:

public string GetEnquiry_ContactName(int EnquiryID)
        {
            string ContactName = null;

            //Try to do the following if not then show the error message.
            try
            {
                //Create the SQL Connection
                SqlConnection cn = new SqlConnection(Models.M_GlobalVariables.GetConnection);

                //Open the SQL connection.
                cn.Open();

                //Get the ContactName for the selected EnquiryID
                using (SqlCommand cmd = new SqlCommand("select e.ContactName 'ContactName' from Enquiry e where e.EnquiryID = @EnquiryID", cn))
                {
                    cmd.Parameters.AddWithValue("@EnquiryID", EnquiryID);
                    
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        ContactName = dr["ContactName"].ToString();
                    }

                    dr.Close();
                }

                //Close the SQL connection
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + Environment.NewLine + ex);
            }

            return ContactName;
        }


But if i want to return a collection of data like this:

public ObservableCollection<CompanyNames> GetCompanyNames ()
        {
            public class CompanyNames
            {
                public string CompanyName { get; set; }
                public CompanyNames(string name)
                {
                    CompanyName = name;
                }
            }

            private ObservableCollection<CompanyNames> _List_CompanyNames = new ObservableCollection<CompanyNames>();
            public ObservableCollection<CompanyNames> List_CompanyNames
            {
                get { return _List_CompanyNames; }
                set
                {
                    _List_CompanyNames = value;
                    //onpropertychanged("List_CompanyNames");
                }
            }

            private void Populate_ComboBox_CompanyNames()
            {
                try
                {
                    //Create the SQL Connection
                    SqlConnection cn = new SqlConnection(Models.M_GlobalVariables.GetConnection);

                    //Open the SQL connection.
                    cn.Open();

                    using (SqlCommand cmd = new SqlCommand("select distinct CompanyName from Enquiry e where e.CompanyName  is not null and e.Rejected is null and e.Confirmed is not null", cn))
                    {
                        SqlDataReader dr = cmd.ExecuteReader();

                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                string _CompanyName = dr["CompanyName"].ToString();
                                List_CompanyNames.Add(new CompanyNames(_CompanyName));
                            }
                            MessageBox.Show("Clients retrieved successfully.");

                        }
                        else
                        {
                            MessageBox.Show("There are no clients that match your description.");
                        }

                        dr.Close();
                    }

                    //Close the SQL connection
                    cn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + Environment.NewLine + ex);
                }
            }

            return List_CompanyNames;
        }


It does not work and i get 'Not all code paths return a value' and ''List_CompanyNames' is a 'property' but is used like a 'type''.

But if i have it in my ViewModel like this:

//Code for the 'Company Names' dropdown box
        public class CompanyNames
        {
            public string CompanyName { get; set; }
            public CompanyNames(string name)
            {
                CompanyName = name;
            }
        }

        private ObservableCollection<CompanyNames> _List_CompanyNames = new ObservableCollection<CompanyNames>();
        public ObservableCollection<CompanyNames> List_CompanyNames
        {
            get { return _List_CompanyNames; }
            set
            {
                _List_CompanyNames = value;
                onpropertychanged("List_CompanyNames");
            }
        }

        private void Populate_ComboBox_CompanyNames()
        {
            try
            {
                //Create the SQL Connection
                SqlConnection cn = new SqlConnection(M_GlobalVariables.GetConnection);

                //Open the SQL connection.
                cn.Open();

                using (SqlCommand cmd = new SqlCommand("select distinct CompanyName from Enquiry e where e.CompanyName  is not null and e.Rejected is null and e.Confirmed is not null", cn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();

                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            string _CompanyName = dr["CompanyName"].ToString();
                            List_CompanyNames.Add(new CompanyNames(_CompanyName));
                        }
                        MessageBox.Show("Clients retrieved successfully.");

                        //Disable the 'Get Clients' button.
                        Demo_GetClients_Enabled = false;
                    }
                    else
                    {
                        MessageBox.Show("There are no clients that match your description.");
                    }

                    dr.Close();
                }

                //Close the SQL connection
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + Environment.NewLine + ex);
            }
        }


It works absolutely fine but the whole point of my SQL class is to have all my SQL code in that class instead of some of it in the ViewModel. Can somebody help me and tell me what i am doing wrong please?

Thank You

Is This A Good Question/Topic? 0
  • +

Replies To: Get collection of data from database through SQL class?

#2 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 05:24 AM

Quote

It does not work and i get 'Not all code paths return a value' and ''List_CompanyNames' is a 'property' but is used like a 'type''.


If you fix your use of "List_CompanyNames" that should take care of the other error.

But you have a bigger problem going on here and that's your design in general.
You should not have a method in your C# code behind that populates your GUI

Quote

 private void Populate_ComboBox_CompanyNames()
...
List_CompanyNames.Add(new CompanyNames(_CompanyName));


This is just very bad, and not very WPF. It looks a lot like you are using old WinForms concepts with WPF. There's not much point trying to move to WPF if you aren't going to update your design/architecture concepts to match. Things like a ListView should have their .ItemSource bound to a Dependency Property and NOT hand filled he way you are.

There are a few WPF tutorials in my signature block. Start with the "WPF for WinForms developers" to help you transition away from these bad habits.

This post has been edited by tlhIn`toq: 26 April 2015 - 05:27 AM

Was This Post Helpful? 0
  • +
  • -

#3 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 10:43 AM

I have gone through loads of tutorials on here to learn WPF but I can't find one that mentions getting collections of data to bind to something in the view like a combo box or data grid.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 11:08 AM

For a collection to ComboBox or ListView, you mind to the .ItemsSource property of the control. Not really worth an entire tutorial to mention which property you bind to.

How you get your collection is up to you and your design. I strongly recommend using something like Entity Framework to do the mapping between your C# objects and your relational database. You're a C# developer: Just deal with C# objects and let E.F. do the grunt work with the database. That way you don't have to write and manage the Data Access Layer. In other words, don't re-invent the wheel. There is an entire framework already in existence for doing that; why would you build it all over?

http://blogs.msdn.co...f-entities.aspx
Was This Post Helpful? 0
  • +
  • -

#5 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 11:25 AM

If I do chose to use 'Entiry Framework' will I have to completely change my program? For example will my SQL code that I quoted in my first post for getting one item, will that still work or will I have to change that as well?

And how would I get started with 'Entity Framework'?
Was This Post Helpful? 0
  • +
  • -

#6 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 12:24 PM

Yes your SQL will change because you won't be doing that any more. The Entity Framework code will be handling that. You'll only have to deal with C# objects.

You have lots that you need to change regardless. Sorry. But a lot of your underlying design shows problems.
  • You logic requires an operator to be present even for the little things like getting a list of names. If you ever want to expand on this code you're screwed. The little tiny method for getting one piece of data shouldn't throw up MessageBoxes. Your UI can state if there was a problem, and *IF* there is an operator they can see that: Like on a status bar message. But don't block operations waiting for operator actions. Especially something that might get looped 1,000 times a second.
  • You have SQL calls that aren't parametrized and that's bad.
  •  using (SqlCommand cmd = new SqlCommand("select distinct CompanyName from Enquiry e where e.CompanyName  is not null and e.Rejected is null and e.Confirmed is not null", cn))
    

  • Not to mention the hard-coded table and column names meaning you can't make changes to your database without breaking your C#
  • Looks like your properties are all CLR properties and not Dependency Properties which makes me wonder if they work in your your WPF UI.
  • Your C# code behind is directly enable/disableing GUI controls instead of using command is enabled design patterns. So most likely you're not using commands in your GUI but instead events such as Button.Click.
    45	                        //Disable the 'Get Clients' button.
    46	                        Demo_GetClients_Enabled = false;
    

  • That is also a side effect which is bad. A method should do one thing. But your Populate_ComboBox_CompanyNames method also has the side affect of disabling some other different Demo_GetClients control. That's not its job. Its job is to populate. Besides, you shouldn't even have a method for populating the control: Its content should be the result of a binding to a Dependency Property in the ViewModel.
  • You're doing a LOT of extra work. Why the list of company names that you get over and over? Just make a list of Company objects, and display the name. You get the Companies one time, then use whatever property is needed when you need it.


Frankly my suggestion to you would be to put this more complex program aside. You need to work on the basics of good WPF/MVVM design before complicating it all with a bunch of database interaction. Right now it looks like you're trying to take one semester of WinForms schooling and apply it to a semi-advanced WPF need. That's kind of like taking your first month of lawn mower engine repair and apply it to designing a new model of Ferrari.

This post has been edited by tlhIn`toq: 26 April 2015 - 12:24 PM

Was This Post Helpful? 0
  • +
  • -

#7 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 12:40 PM

View PosttlhIn`toq, on 26 April 2015 - 08:24 PM, said:

[*]You have SQL calls that aren't parametrized and that's bad.
[*]
 using (SqlCommand cmd = new SqlCommand("select distinct CompanyName from Enquiry e where e.CompanyName  is not null and e.Rejected is null and e.Confirmed is not null", cn))


That query is not using user input at all so as far as i am aware does not need to be parameterised.

View PosttlhIn`toq, on 26 April 2015 - 08:24 PM, said:

[*]Your C# code behind is directly enable/disableing GUI controls instead of using command is enabled design patterns. So most likely you're not using commands in your GUI but instead events such as Button.Click.
45	                        //Disable the 'Get Clients' button.
46	                        Demo_GetClients_Enabled = false;


That is a varaible which is binded to a buttons enabled status. Which as far as i am aware is correct.

View PosttlhIn`toq, on 26 April 2015 - 08:24 PM, said:

Frankly my suggestion to you would be to put this more complex program aside. You need to work on the basics of good WPF/MVVM design before complicating it all with a bunch of database interaction. Right now it looks like you're trying to take one semester of WinForms schooling and apply it to a semi-advanced WPF need. That's kind of like taking your first month of lawn mower engine repair and apply it to designing a new model of Ferrari.



Now i am not restarting this program again because i have already restarted it several months ago because i was doing it in WPF without MVVM. So now that i am using MVVM i am not prepared to scratch it and start again instead i will fix this program if only you guys can help me to work out how please.

This post has been edited by danbywinby: 26 April 2015 - 12:41 PM

Was This Post Helpful? 0
  • +
  • -

#8 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 01:10 PM

Parametrized queries are not just about variable data coming from the user. They also help keep you from being vulnerable to SQL injection attacks.

Quote

That is a varaible which is binded to a buttons enabled status. Which as far as i am aware is correct.

Its bad design. That's the point I'm trying to make. The button should enact a command. The button will be enabled or disabled if the command itself is enabled or disabled. But you don't go around directly changing the .Enabled property of the button: That's bad design. Its how you had to do it back in WinForms but not how we do things in WPF.

Here's an example. Here is a Pallet for a painting program I'm working on. The pallet shows all the open jobs that are currently loaded.
Attached Image

Notice the Archive button. That button is only enabled when there is a job selected
Now look at the XAML for the button. No click event subscription. No name applied. No assignment to the .IsEnabled property.
            <Button Margin="0,0,5,0"
                    Command="{Binding DeleteSelectedJobCommand}"
                    Content="Archive"/>

The button is assigned to a command. That's all. When the command is available the button will be enabled. This is the point to commands. You can assign the command to a button, a menu item and 20 other places without having to micro-manage all of them for enable/disable.

All the commands are in a command class. You can then attach to them from any of your views: A window, a UserControl, whatever.

Heres the C# for that same command. It consists of:
  • A command (field and public property)
  • A bool to say if it can or cannot be executed
  • A method to execute when the command is raised.
That's it.

Notice the read-only bool property for whether or not the DeleteSelectedJob command can be executed. It returns whether or not there is a job selected. That's it. If there is a job selected then you can choose to delete it. If nothing is selected then you can't choose to delete it. Simple and easy to read. And your UI will disable/enable the corresponding controls for you.
        #region DeleteSelectedJobCommand
        private readonly DelegateCommand<PhotoJob> _DeleteSelectedJobCommand;

        public bool CanDeleteSelectedJob(PhotoJob param)
        {
            return SelectedJob != null;
        }

        public DelegateCommand<PhotoJob> DeleteSelectedJobCommand
        {
            get { return _DeleteSelectedJobCommand; }
        }

        public void DeleteSelectedJob(PhotoJob UnusedParameter)
        {
            //TODO Archive(save) the job before removal

            JobsCollection[JobsCollection.IndexOf(SelectedJob)] = new PhotoJob();
            try
            {
                while (JobsCollection.Last().IsEmptySlot) JobsCollection.Remove(JobsCollection.Last());
                //When we archive we keep the slot available, unless it is the end slot
                //We delete the end slot if its empty.  If the new end slot is empty
                //we delete that too.  So with 10 slots 1-9 can be empty if 10 has
                //a job.  When 10 is archived 1-9 will cascade to emptiness as well
                //This is behvior for this particular program and not a requirement
                //of commands and MVVM in general. Its what this customer wanted.
            }
            catch (Exception)
            {

            }
        }
        #endregion DeleteSelectedJobCommand


Does that help? Can you see how nice it would be to not have to micro-manage the .IsEnabled status of countless controls? And how nice it is to not have your C# tightly bound to the GUI? You can attach to the command from anyplace you like and not have to write dozens of lines of C# to support it.
Was This Post Helpful? 0
  • +
  • -

#9 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 01:46 PM

That does indeed look a lot nicer. I will agree with that.

But how are you able to bind to it from the view? I thought you could only bind from the view to the view model?
Was This Post Helpful? 0
  • +
  • -

#10 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 02:02 PM

In this case the command and the method being carried out when it is executed are in the ViewModel. Which for this example make sense since the ViewModel is the layer between the view and however many models are needed for that view.

So this view can raise a command to tell the ViewModel: "Archive this object. I (the view) don't know what you're doing to do; just do it"

The ViewModel then does its job, which might include telling various Models (objects) or collections to do something, such as
  • JobsEntity: Delete x. You figure out if that includes talking to a database or not, I don't know what you do.
  • SelectedJob you're now null.
  • SoundClass: PlayAbeep.
  • UserAuditTrail: Make a note that the user deleted this thing


But you can bind to commands from just about anywhere. For example, try binding a menu option to the ApplicationCommands.Exit.

Its not illegal. That's how you're supposed to raise the command for closing your program. But that command doesn't exist in your ViewModel does it? You're just going to handle it in your view model.
Was This Post Helpful? 0
  • +
  • -

#11 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 02:10 PM

Ok I'll try changing my program to use commands then.

Do you have or know of a tutorial for this?
Was This Post Helpful? 0
  • +
  • -

#12 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 26 April 2015 - 02:49 PM

There's a ton of them. Just google "C# WPF commands tutorial"
Was This Post Helpful? 0
  • +
  • -

#13 danbywinby   User is offline

  • D.I.C Regular

Reputation: 3
  • View blog
  • Posts: 305
  • Joined: 06-January 13

Re: Get collection of data from database through SQL class?

Posted 27 April 2015 - 06:11 AM

Ok after going through loads of tutorials on binding commands i found that most of them worked on bing the "IsEnabled" seperately to the "Command". But i did find this:

http://stackoverflow...-wpf-using-mvvm

And after reading it i have ended up with this in my viewmodel:

public ICommand GetClientsList_Command
        {
            get { return new Code_DelegateCommand(Execute_GetClientsList_Command, CanExecute_GetClientsList_Command); }
        }

        private bool CanExecute_GetClientsList_Command(object context)
        {
            if (Demo_CompanyName == null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void Execute_GetClientsList_Command(object context)
        {
            Populate_ComboBox_CompanyNames();
        }


And this in my view for the button:

<Button Grid.Column="2" Grid.Row="3" Margin="10" FontSize="12" Command="{Binding Path=GetClientsList_Command}">Get Clients</Button>


But the code for enabling/disabling the button is not working. And as far as i can see it will only work if i bind to it seperately as seperate variable or am i doing something wrong?
Was This Post Helpful? 0
  • +
  • -

#14 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: Get collection of data from database through SQL class?

Posted 27 April 2015 - 06:59 AM

Clearly this is just a portion of your code, as would be expected.
I'm going to continue to paste in what I'm doing from the same example I pasted in earlier, just for consistency.

Someplace you're assigning your command, right? Like in the constructor for your ViewModel.
            _DeleteSelectedLayerCommand = new DelegateCommand<LayerObj>(DeleteSelectedLayer, CanDeleteSelectedLayer);
As you can see we are supplying the Command, and the method that determines if it can execute.


Your bool that says whether or not the command can be run:
private bool CanExecute_GetClientsList_Command(object context)
is a method. You realize that, right? Like any method it has to be called. It doesn't just magically run itself.


In my program where I grabbed this example from its all about the SelectedJob. When the SelectedJob changes we call the methods that determine if certain commands (related to the SelectedJob) can be run. Notice line 13 below where we tell the command to raise an event for the CanExecute... This is not saying whether the command can or cannot execute. Its just saying that CanExecute has changed and thus everyone that uses that command should check. That will cause the method to evaluate. Its kind of like a property and a method had a baby. Like a property you're going to say it has changed so everyone that cares should get the new value. But in this case the get is really the execution of the method that returns a bool.
        public PhotoJob SelectedJob
        {
            get { return (PhotoJob)GetValue(SelectedJobProperty); }
            set
            {
                // ReSharper disable once PossibleUnintendedReferenceComparison
                if (value == SelectedJob) return; //Don't pass on changes that aren't actually new values
                SetValue(SelectedJobProperty, value);
                NotifyPropertyChanged("SelectedJob");
                NotifyPropertyChanged("JobsCollection");

                //Update whether or not the related commands can be executed
                _DeleteSelectedJobCommand.RaiseCanExecuteChanged(); // <---  Look here
                _CenterHereCommand.RaiseCanExecuteChanged();
                _ToggleAlphaCommand.RaiseCanExecuteChanged();
                _ToggleVideoCommand.RaiseCanExecuteChanged();
                _AddClipartCommand.RaiseCanExecuteChanged();
            }
        }



My guess is that your command and the check if it can be executed work, but aren't being called to update. You just need to decide where to place these .RaiseCanExecutedChanged() calls. A little common sense goes a long way here. For example calls for commands related to a document like 'CanSave', 'CanClose' etc. can be placed in the IsDirty property. When the IsDirty marker changes is a natural place to update whether or not you can close the document because you don't want to close it if it hasn't yet been saved.

So you have to decide when its appropriate to update your UI with respect to the CanGetClients command. I'm thinking this would be when a new user logs into the program: Maybe they don't have the rights to get the client list. What other points in the work flow would be good for this? When a new estimate is being created? Its your program so only you really know.

TIP: You can also clean up some of your code to be less... First year student. Wordy. 10 lines can become 2 real fast.
06	        private bool CanExecute_GetClientsList_Command(object context)
07	        {
08	           if (Demo_CompanyName == null)
09	            {
10	                return true;
11	            }
12	            else
13	            {
14	                return false;
15	            }
16	        }

can become
private bool CanExecute_GetClientsList_Command(object context)
{
   return Demo_CompanyName == null;
}
Not only is is easier to read but its a lot less screen real estate, so you can see more of your program at one time; letting you see more of the entire logic at once.

This post has been edited by tlhIn`toq: 27 April 2015 - 07:02 AM

Was This Post Helpful? 0
  • +
  • -

#15 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Get collection of data from database through SQL class?

Posted 27 April 2015 - 09:10 AM

Quote

That query is not using user input at all so as far as i am aware does not need to be parameterised.



Quote

Parametrized queries are not just about variable data coming from the user. They also help keep you from being vulnerable to SQL injection attacks.


I'd just like to clear up a misconception here. Parameters are in fact useful for user input, because SQL Injection is only possible when a query includes user input. Without user input, there's no way to actually inject anything into the SQL statement. Therefore, if your query only includes literal or hard-coded arguments, there's no need to parameterize it. That would be like doing var str = string.Format("{0}", "My String");.

Now, there are reasons to parameterize even when not dealing with user input. For instance, filling a varbinary field is significantly easier with a parameter than trying to include it in the SQL command string.

But when dealing with user input, parameters should be considered 'a must'.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2