Searching multiple SQL tables for data

Question

In my SQL database I have a table for every state in the USA. The user can create multiple pages in different states. Each table has a "UserID" column that will always match the logged in user. How do I search multiple tables for the "UserID"?

string sqlquery = "SELECT * FROM[all tables] WHERE @UserID ='" + userID.Text + "'";

SqlConnection conn1 = new SqlConnection(connString);
SqlCommand comm1 = new SqlCommand(sqlquery, conn1);
SqlDataReader DR1 = comm1.ExecuteReader();
if (DR1.Read())
{
Textbox1.Text = DR1.GetValue(0).ToString();
}

Two things, first of all I would suggest prepared statements over string concatenation as a method of preparing your sql command. Second, I don't know what RDBMS you're using but you're going to need some notion of JOINs this is not a C# or asp.net question but a pure SQL one. — Benjamin Gruenbaum
– Benjamin Gruenbaum, Commented Jan 31, 2013 at 23:02
fist question I will ask because I see the GetValue(0).ToString() call is what flavor of the database are you using..? is this SQLite? or Sql Server.., etc..? also you need to do your gathering and storage, into a List or other type of Collection.. I worked helping someone earlier using the GetValue() method and this yields errors just a heads up .. — MethodMan
– MethodMan, Commented Jan 31, 2013 at 23:04
Second thing that I have noticed is that why are you doing a Select * when all you are looking for is the userId, also where are you creating the comm1.Parameters.AddWithValues("@UserID", userId.Text) which by the way you should use a property and have proper validation otherwise you are setting yourself up for sql injection. The way the code / query stands I doubt that you will get the intended results — MethodMan
– MethodMan, Commented Jan 31, 2013 at 23:08

Dave Zych · Accepted Answer · 2013-01-31 23:21:27Z

8

I haven't seen your database schema, but I can already tell you need to refactor it. There's no need to have a table for each state, and the maintenance on that could be rough. What you should have is a table holding all of the states, and then another table with a reference to the State table that holds whatever information you want (your "pages")

CREATE TABLE States
(
    StateID int not null, --PK
    StateName nvarchar(32) not null
)

CREATE TABLE Pages
(
    PagesID int not null, --PK
    StateID int not null, --FK
    UserID int not null
    //Whatever other columns you need
)

Now, you can query the Pages table based on a specific Page, State or User

SELECT * FROM Pages WHERE UserID = (userId)
SELECT * FROM Pages WHERE StateID IN (1, 5, 20)
SELECT * FROM Pages WHERE PageID = (pageID)

answered Jan 31, 2013 at 23:21

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pacobart Over a year ago

So if I make a new table for the Page information, will one table be enough to hold over 10,000 entries? Also my other issue is the UserID is the primary key so how would I get multiple entries from the same User into the same table? Would I need to change my primary key to something else?

Dave Zych Over a year ago

I'll be able to give you more of an answer when I get home in an hour, but I'd create a User table, a Page table and a State table. Page would have a foreign key to User and State. If you read my comment to your question, yes, a single table can hold many, many more rows than 10,000. You'll want to look into indexes and performance optimizations, but that shouldn't be a huge concern right this second.

Brandon Moore · Accepted Answer · 2013-01-31 23:26:31Z

1

The right answer is that you need to change your database so that all this information is held in a single table. The easy answer is to take a few minutes to create a view that unions all the tables together so that you can access them as if they were all in one table.

The only difference in my answer from the others though is that I wouldn't union them in your c# code. I would just create an actual view on the database that unions them all together. The view will be simple (though long) and your code will be simple.

answered Jan 31, 2013 at 23:26

Brandon Moore

8,84015 gold badges69 silver badges124 bronze badges

1 Comment

Brandon Moore Over a year ago

Clarification: I meant all the states should be in one table... didn't mean everything should be in one table. Refer to Dave's answer for what the tables should look like.

mipe34 · Accepted Answer · 2013-01-31 23:31:03Z

If tables have the same columns I would go for something like. Using the sql UNION.

var statesTables = new[]{"NY, Texas, Oregano, Alabama}";

var qBuild = new StringBuilder();

qBuild.Append(string.Format("SELECT * FROM {0} WHERE UserId = @userId ", statesTables[0]));

for(int i=1;i<stateTables.Length;i++){
     qbuild.Append(string.Format("UNION SELECT * FROM {0} WHERE UserId = @userId ", statesTables[i]))
}

SqlConnection conn1 = new SqlConnection(connString);
SqlCommand comm1 = new SqlCommand(qBuild.ToString(), conn1);
comm1.Parameters.Add(new SqlParameter("userId", userId));

It will generate SQL:

SELECT * FROM NY WHERE UserId = @userId
UNION 
SELECT * FROM Texas WHERE UserId = @userId
UNION 
SELECT * FROM Oregano WHERE UserId = @userId
UNION 
SELECT * FROM Alabama WHERE UserId = @userId

If there are some different columns, replace * with column names tables have in common.

BUT, as other suggests, refactoring your DB schema would be the best!

Collectives™ on Stack Overflow

Searching multiple SQL tables for data

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related