Create a table in SQL Server where the name is a variable in C#?

Question

Using Visual Studio and SSMS.

I have a form where a user registers a username and it's stored like this:

List<SqlParameter> sqlNewTable = new List<SqlParameter>();
sqlNewTable.Add(new SqlParameter("Username", txtUser.Text));
DAL.ExecSP("CreateUserCourses", sqlNewTable);

From there, can I create a stored procedure called CreateUserCourses in which it creates a new table where the users input (their username) is the name of a new table?

To sum it up, it's a test program where you view courses you've taken. The user makes an account, a table would be made based on their username, inside it would be a copy of the master Course list, then the user can select options to change it. I'm okay with knowing how to change things but can't figure out how to make a table in SSMS and name it using a variable in Visual Studio. — tryingtotryhard
– tryingtotryhard, Commented Apr 4, 2018 at 4:58
Did you do any research? social.msdn.microsoft.com/Forums/sqlserver/en-US/… — Jeremy Thompson
– Jeremy Thompson, Commented Apr 4, 2018 at 4:58
@tryingtotryhard this will make your life very very hard. I would recommend you to read up on relational databases before you go further with this approach. — danish
– danish, Commented Apr 4, 2018 at 5:00
@JeremyThompson Thank you. I tried googling what I could but I guess I'm just going about it wrong and wasn't googling the right way to go about it. :/ — tryingtotryhard
– tryingtotryhard, Commented Apr 4, 2018 at 5:03

TheGeneral · Accepted Answer · 2018-04-04 05:14:49Z

1

Sure you can, but why?

Supposing you have a User table and a Course table. Then just make a 3rd table which maps those tables together Called UserCourses. This is called a Many-to-Many (mapping table) and it will containing an ID of both the User, and Course and any other relevant information .

This will make your life a lot easier going forward

Many-to-many (data model)

A many-to-many relationship is a type of cardinality that refers to the relationship between two entities1 A and B in which A may contain a parent instance for which there are many children in B and vice versa.

For example, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors

Example

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     // mapping table

SQL queries could look like this

Getting all students for a class

SELECT s.student_id, last_name
FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
WHERE sc.class_id = X

Getting all classes for a student

SELECT c.class_id, name
FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
WHERE sc.student_id = Y

Entity framework queries could look like this

Getting all students for a class

var students = db.Students.Where(x => x.StudentClasses
                                       .Any(y => y.ClassId == 1);

Getting all classes for a student

var classes = db.classes.Where(x => x.StudentClasses
                                     .Any(y => y.StudentId == 1);

edited Apr 4, 2018 at 5:14

answered Apr 4, 2018 at 4:57

TheGeneral

82.1k9 gold badges111 silver badges151 bronze badges

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

1 Comment

tryingtotryhard Over a year ago

I'm really new to databases so I'm not sure how to do that... I'll look into that, thank you.

Collectives™ on Stack Overflow

Create a table in SQL Server where the name is a variable in C#?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related