How Can I search a table based on words inside a text file?

Question

0.00/5 (No votes)

See more:

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:

SQL

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,

Posted 2-Jul-15 5:23am

KatsuneShinsengumi

Add a Solution

Comments

virusstorm 2-Jul-15 12:31pm

Do you need to do this via SQL? Could you simply parse the file using something like C# or VB.NET and search the table that way?

The OPENROWSET wasn't really designed for what you are trying to do in this example.

KatsuneShinsengumi 2-Jul-15 12:45pm

Do you have sample code for c#? Something I can follow on? Because I haven't tried c# before.

virusstorm 2-Jul-15 12:46pm

I can give you some sample code to do this. Give me about an hour to put something together and I'll post it as a solution.

KatsuneShinsengumi 5-Jul-15 23:44pm

Thanks man.

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)

virusstorm · Accepted Answer · 2015-07-02T08:05:00

Assume you have a table like this:

SQL

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:

C#

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.