我如何从我的数据库中读取数据 [英] how can i read data from my database

问题描述

我这里的问题是，当我从打开的对话框中选择一个文件时，我能够显示代码中描述的内容，但是我似乎可以打开除此文件之外的任何其他文件.例如.我打开文件"A"，显示其内容后尝试打开文件"B"，但无法打开.该代码在下面详细说明.

The problem i have here is that when i select a file from the the open dialog box, i am able to display is contents as described in the code but i can seem to open any other file besides this one file. for example. i open file ''A'', after displaying its contents i try to open file ''B'' but it wont open. the code is detailed below.

OpenFileDialog dlg = new OpenFileDialog();
DialogResult dlgrslt = dlg.ShowDialog();

String connection, sql;

if (dlgrslt == DialogResult.OK)
{

    connection = Properties.Settings.Default.cvmanagerConnectionString;

    filename = dlg.FileName;


                    sql = "select A.Fname, A.Lname, A.dept, A.role, A.rating, A.notes, B.firstdegA, B.firstdegB, C.secdegA, C.secdegB, D.thirddegA, D.thirddegB, E.dipA, E.dipB, F.hndA, F.hndB, G.phone_one from apps as A left join firstdeg as B on A.FileLoc = B.FileLoc left join secdeg as C on A.FileLoc = C.FileLoc left join thirddeg as D on A.FileLoc = D.FileLoc left join dip as E on A.FileLoc = E.FileLoc left join hnd as F on A.FileLoc = F.FileLoc left join contacts as G on A.FileLoc = G.FileLoc where A.FileLoc = ''" + filename + "''";

    MessageBox.Show(sql);
    SqlDataReader reader = null;
    SqlConnection conn = new SqlConnection(connection);
    SqlCommand cmd = new SqlCommand(sql, conn);

    try
    {
        conn.Open();
        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {

                tbappname.Text = reader["Fname"].ToString() + " " + reader["Lname"].ToString();
                tbfirstdegA.Text = reader["firstdegA"].ToString();
                tbfirstdegB.Text = reader["firstdegB"].ToString();

                tbsecdegA.Text = reader["secdegA"].ToString();
                tbsecdebB.Text = reader["secdegB"].ToString();

                tbthirddegA.Text = reader["thirddegA"].ToString();
                tbthirddegB.Text = reader["thirddegB"].ToString();

                tbdipA.Text = reader["dipA"].ToString();
                tbdipB.Text = reader["dipB"].ToString();

                tbhndA.Text = reader["hndA"].ToString();
                tbhndB.Text = reader["hndB"].ToString();

                tbcontact.Text = reader["phone_one"].ToString();

                plcmntdept.Text = reader["dept"].ToString();
                plcmntrole.Text = reader["role"].ToString();
                plcmntrating.Text = reader["rating"].ToString();
                plcmntnotes.Text = reader["notes"].ToString();


            }
        }

        plcmntedit.Enabled = true;

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        reader.Close();
        conn.Close();
    }
}

推荐答案

在您的查询中似乎需要注意以下几点:

1.您不需要从每个表中选择FileLoc，但是如果选择这样做，则需要为其应用别名...

It looks like there are a couple things to look at in your query:

1. You do not need to select FileLoc from each table, but if you choose to, then you need to apply an alias to it...

sql = "select A.FileLoc, A.Fname, A.Lname, A.dept, A.role, A.rating, A.notes, B.FileLoc AS FileLocB, B.firstdegA, B.firstdegB, C.FileLoc AS FileLocC, C.secdegA, C.secdegB, ..."

...这样它就具有唯一的名称.

2.您的表通过内部联接进行联接，这意味着，如果列出的表(A，B，C， D，E，F和G).如果甚至其中一个表都没有匹配的FileLoc列，那么将不返回任何行.您可以考虑改用LEFT JOIN.这将使您可以检索部分信息...

... so that it has a unique name.

2. Your tables are joined with inner joins, which means that the query will only return rows if there is a match in ALL the tables listed (A,B,C,D,E,F, and G). If even one of the tables does not have a matching FileLoc column then no rows will be returned. You might consider using LEFT JOIN instead. This will allow you to retrieve partial information...

sql = "... from apps as A left join firstdeg as B on A.FileLoc = B.FileLoc left join secdeg as C on A.FileLoc = C.FileLoc ..."

希望这对您有所帮助.

Hope this helps.

首先，您的问题没有任何意义(至少对我而言).您谈论的是打开文件，但是在该代码中的任何地方都没有打开文件(如

First of all, your question doesn''t make any sense (at least not to me). You talk about opening files, but nowhere in that code are you opening a file (as in

using (StreamReader sr = new StreamReader("TestFile.txt"))
{
     string line;
     // Read and display lines from the file until the end of
     // the file is reached.
     while ((line = sr.ReadLine()) != null)
     {
          Console.WriteLine(line);
     }
}

您正在选择数据库记录.

其次，您不需要在Select中包括A.FileLoc，B.FileLoc，C.FileLoc等.您根本不使用它们.

最后，我不确定您如何获得任何信息.您调用reader.Read()两次.我假设您的数据库每个文件应该只有一条记录.因此，这就是您在逻辑上要做的事情.

您设置了SQL Select.
您设置了SQLDataReader.
您执行DataReader.
您阅读了第一条记录.
您创建名字，姓氏和全名变量.
您阅读了下一条记录.

*目前您已经在第二个记录中.

现在，您填写所有的TextBoxes.

如果只希望返回一行，则需要删除while(reader.Read()).

而且，作为供您参考，您知道，除了使用内存来保存名字，姓氏和全名之外，您还可以轻松地编写以下内容:

You''re selecting a database record.

Secondly, you don''t need to include A.FileLoc, B.FileLoc, C.FileLoc, etc... in your Select. You''re not using them at all.

Finally, I''m not sure how you''re getting any information at all. You call reader.Read() twice. I assume that your database should only have one record for each file. So, here''s what you are doing logically.

You set up your SQL Select.
You set up the SQLDataReader.
You execute the DataReader.
You read the first record.
You create firstname, lastname and fullname variables.
You read the next record.

*At this point you are already on the second record.

Now you fill in all of your TextBoxes.

If you are only expecting one row to be returned, you need to get rid of the while(reader.Read()).

And as an FYI, you are aware that instead of using memory to hold firstname, lastname and fullname, you could also just as easily write:

tbappname.Text = reader["Fname"].ToString() + " " + reader["Lname"].ToString();

这篇关于我如何从我的数据库中读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！