Search database tables C#

Question

0.00/5 (No votes)

See more:

Hi!

I have done a small project where I made a database with one table entry. In the table I have the following columns:
OrderID
CustomerID
EmployeeID
OrderDate
RequiredDate
ShippedDate
ShipVia
Freight
ShipName
ShipAdress
ShipCity
ShipRegion
ShipPostalCode
ShipCountry

The first task was to display the data on screen (console application), which I did.

The second task is to "search for shipping data using Customer's ID and display the shipped date on screen.

What have I done so far?

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;

namespace Module5
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionStr = @"Data Source=C:\Users\Elvis\Desktop\C#\Projects\Module5\Module5\Shipping.sdf";
            SqlCeConnection connection = new SqlCeConnection(connectionStr);
            SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM ShippingTable", connection);
            SqlCeDataAdapter adapt = new SqlCeDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapt.Fill(ds, "ShippingTable");

            foreach (DataTable dt in ds.Tables)
            {
                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn column in dt.Columns)
                    {
                        Console.WriteLine(row[column]);
                    }
                }
            }

            SqlCeConnection con1 = new SqlCeConnection(connectionStr);
            con1.Open();
            SqlCeCommand cmd1 = new SqlCeCommand("SELECT * FROM ShippingTable", con1);
            SqlCeDataReader dr = cmd1.ExecuteReader();

            Console.WriteLine("Please enter shipping data");
            int input = Convert.ToInt32(Console.ReadLine());

   while (dr.Read())
            {
                int database = Convert.ToInt32(dr[1]);
                Console.WriteLine(dr[1]);
                
                if (input == database)
                {
                    Console.WriteLine("");
                }
            }
        }
    }
}

Now I was told that this code would work for the "search"-function:

C#

while (dr.Read())
{
    int database = Convert.ToInt32(dr[1]);
    Console.WriteLine(dr[1]);

    if (input == database)
    {
        Console.WriteLine("");

But I cannot get it to work. Am I doing something wrong?

Posted 10-Nov-14 9:09am

Member 11222325

Updated 10-Nov-14 9:58am

Tomas Takac

v3

Add a Solution

Comments

Kornfeld Eliyahu Peter 10-Nov-14 15:17pm

"cannot get it to work" is a bit poor to define the problem...
There are any errors? If not how the outcome is different from what you have expected?
Please add details...

Member 11222325 10-Nov-14 16:09pm

Hi!

1. The first task was to print the information from the tables on screen, which I did.

2. The second task is to search for shipping data using Customer's ID and display the shipped date on screen. I'm supposed to add another chunk of code in the existing one so I can search for a Customer ID inside my Console Application!

3. So far I haven't gotten anywhere regarding Task 2. The teacher sent me that chunk of code, since the course is distance based it takes a couple of days sometimes to get an answer.

The code is WORKING and it's compiling just fine, I just need to add a SEARCH-function as mentioned above!

Tomas Takac 10-Nov-14 16:44pm

We have a pretty good idea of what you are trying to do. The question is what have you done and where are you stuck?

Member 11222325 10-Nov-14 17:14pm

Hi Tomas!

I am stuck at the last task which is to "search for shipping data using CustomerID and display shipped date on screen". This is where I'm currently stuck!

I tried to implement a search function by adding this chunk of code:
while (dr.Read())
{
int database = Convert.ToInt32(dr[1]);
Console.WriteLine(dr[1]);

if (input == database)
{
Console.WriteLine("");

But this didn't work. So I'm asking if the code above is correct? If not, where did I go wrong?

I'm new to C# but I've managed to write the code above. I just need help with this last task, please.

Tomas Takac 10-Nov-14 17:33pm

You read a number from console, then go through all the records in the table comparing that number to the value stored in the 2nd column. If you find a match you print out an empty line. Do you think this is how it should work? Frankly, it appears you don't understand the code you wrote a bit. Let me ask you a couple of questions: How can you be sure there is customer id in the 2nd column? Is it necessary to select all rows from the table? Why are you printing out an empty line when you should be printing shipped date?

Member 11222325 10-Nov-14 17:42pm

Hi!

As I stated, I'm new to C# and I'm doing my best. I'm quite aware that it's not supposed to work like that.

I actually do understand the code that I wrote, and I know that it's also wrong, which in turn lead me to this site.

The table looks like this "OrderID", "CustomerID", etc. Therefore, I figured that the CustomerID should be in the second column. Which may, or may not be wrong?

The reason to why I am printing an empty line is cause I was unsure if the code was correct or not.

How should the code look instead? And where did I go wrong? Any explanation would be great.

Afzaal Ahmad Zeeshan 10-Nov-14 15:30pm

You were told? So that above code isn't yours?

In that case you need to go and ask for help to the guy who gave you this code and told you it will work. Smack it in his head and tell him to compile it or die! -_-

Member 11222325 10-Nov-14 17:25pm

Merhaba Afzaai!

No, this is incorrect. The code above is mine, the last specific chunk of code is not mine.

I am trying to implement a function in my console application where I can:
"Search for shipping data using CustomerID and display shipped date on screen."

Could you perhaps help me?

Hakan COBAN 10-Nov-14 15:54pm

I do not understand the problem, but it is better if you use the BindingSource.

Member 11222325 10-Nov-14 17:27pm

Hi Hakan!

The last task which I have to do is:
"Search for shipping data using CustomerID and display shipped date on screen."

And this is where I'm currently at. The code in it's current state will display 1,2,3 when I input anything.

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)

Tomas Takac · Accepted Answer · 2014-11-10T12:30:00

As this is clearly a homework I will not give you complete code. I will give you a couple of hints instead which should help you to do it yourself.

It is a good practice to filter data as soon as possible. In this case you should filter records by customer at the database level. This limits the amount of data that needs to be transferred from database to your application and for you there is then less work in processing those records too. That applies to both rows and columns. If you are asked to provide shipped date then this is exactly what you should be selecting. SELECT * is considered a bad practice[^] for several reasons. So your select should look like this:

SQL

SELECT ShippedDate FROM ShippingTable WHERE CustomerId = @customerId

In this query @customerId is a parameter. You need to learn how to add parameters[^] to your database command. You are loading the resulting data into a DataTable so you may want to explore data adapters[^] to make your job easier.

Final step is printing the loaded data. Well, you already have that code. What you need to do is extract it to a method so you can reuse it.