如何根据文本文件中的单词搜索表格？ [英] How Can I search a table based on words inside a text file?

问题描述

嘿伙计们，



我一直在尝试使用我在文本文件中的值搜索表格，到目前为止这就是我所拥有的：

Hey guys,

I've been trying to Search a table using the values I have on a text file, so far this is what I've got:

SELECT Username
FROM [TestEnvironment].[dbo].[TestComputerNameTable]
WHERE OPENROWSET (BULK 'C:\Users\svcacct.sepsql\Documents\SQL Scripts\usernames.txt', SINGLE_CLOB) MyFyle

我现在没有到达任何地方。



谢谢，

I'm not getting anywhere right now.

Thanks,

推荐答案

假设您有这样一个表：

Assume you have a table like this:

CREATE TABLE dbo.Users
(
	Id INT NOT NULL,
	UserName VARCHAR(1000) NOT NULL,
	CONSTRAINT [PK_Users] PRIMARY KEY ([Id])
);

假设你有一个看起来像这样的文本文件（管道分隔）：

And assume you have a text file that looks like this (pipe delimited):

UserName|FirstName|LastName
testuser1|Test|User 1
testuser2|Test|User 2
testuser3|Test|User 3

以下代码将在表格中搜索文件中的每个用户：

The following code will search the table for each user in the file:

string commandText = "SELECT * FROM dbo.Users WHERE UserName = @userName;";

using (StreamReader streamReader = new StreamReader(@"C:\Users.txt"))
{
    using (SqlConnection sqlConnection = new SqlConnection("data source=localhost;initial catalog=Scratch;integrated security=True;"))
    {
        using (SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection))
        {
            sqlConnection.Open();

            do
            {
                var line = streamReader.ReadLine();
                var userName = line.Split('|')[0];

                sqlCommand.Parameters.AddWithValue("@userName", userName);

                SqlDataReader dataReader = sqlCommand.ExecuteReader();

                while (dataReader.Read())
                {
                    //do something with the data
                }

                sqlCommand.Parameters.Clear();

            } while (!streamReader.EndOfStream);


            sqlConnection.Close();
        }
    }
}

请记住，这是十几种方式之一实现你想要做的事。

Keep in mind, this is one of a dozen ways to achieve what you are trying to do.

这篇关于如何根据文本文件中的单词搜索表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！