搜索数据库表C＃ [英] Search database tables C#

问题描述

嗨！



我做了一个小项目，用一个表项创建了一个数据库。在表格中我有以下列：

OrderID

CustomerID

EmployeeID

OrderDate

必需日期

发货日期

ShipVia

运费

发货名称

发货地址

ShipCity

ShipRegion

ShipPostalCode

ShipCountry



第一项任务是在屏幕上显示数据（控制台应用程序），我这样做了。



第二项任务是使用客户ID搜索运输数据并在屏幕上显示发货日期。



到目前为止我做了什么？

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?

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:

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?

推荐答案

因为这显然是一个功课我不会给你完整的代码。我会给你一些提示，这些提示可以帮助你自己做。



尽快过滤数据是一种很好的做法。在这种情况下，您应该在数据库级别按客户筛选记录。这限制了需要从数据库传输到应用程序的数据量，对您而言，处理这些记录的工作也较少。这适用于行和列。如果要求您提供发货日期，那么这正是您应该选择的。 SELECT * 被视为不良做法 [ ^ ]原因。因此，您的选择应如下所示：

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:

SELECT ShippedDate FROM ShippingTable WHERE CustomerId = @customerId

在此查询中 @customerId 是一个参数。您需要了解如何添加参数 [ ^ ]到您的数据库命令。您正在将结果数据加载到 DataTable 中，因此您可能想要探索数据适配器 [ ^ ]让您的工作更轻松。



最后一步是打印加载的数据。好吧，你已经有了这个代码。您需要做的是将其提取到一个方法，以便您可以重复使用它。

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.

这篇关于搜索数据库表C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！