none
how to pass n:n relationship data to sql server using c# code behind.

    Question

  • Hello All Expert,

    I am a newbie here, need to know

    How to pass the N:N data to the sql server .

    e.g. i have a registration form for the candidate which consists of some text boxes and some dropdown and one gridview.

    Gridview contains EmployeeSkills.

    My query is to pass the data to the Db from c#.net.

    In the database i have multiple tables and One N:N relationship table for EmployeeSkill,

    can anyone provide me some helpful reference on the same.

    Wednesday, April 5, 2017 8:38 AM

Answers

  • i want to use ado.net from the code behind.

    Steps:

    1) Save the candidate using a SqlCommand. I presume that you are already doing this in other parts of your application, so you already know how to create the SqlConnection, as well as configure, parameterize and execute the SqlCommand. I also presume that the tblCandidate has a primary key named IdCandidate and that it is an Identity field so it will be generated at the DB (I asked you before but you never answered about how your key was generated).

    Saving the row and then retrieving the ID can be done in several ways, but it is very common to add a select at the end of the insert so it can be retrieved in one operation. Therefore you will need an insert similar to this one:

    "Insert into TblCandidate(list of fields except Id) Values (list of paramenters); select scope_identity();"

    Run it with .ExecuteScalar so that you recover the key.

    2) Get all the keys for the Skills selected for the candidate. If you loaded the skills into a table that you are presenting on screen, then you already have those keys, which you recovered from the database when you read the rows in order to present them. For simplicity, I will refer to the corresponding column as SkillId, since you didn't mention how it is named in the database.

    3) Repeat in a loop: for each of the skills insert a row into the intermediate table, by using a SqlCommand to send this instruction with .ExecuteNonQuery:

    "Insert into TblCandidateSkill(CandidateId, SkillId) values (@p1, @p2)".

    Obviously, parameter p1 will receive the value that you retrieved in step 1, and parameter p2 on each iteration of the loop will receive one of the values that you retrieved in step 2.

    Friday, April 7, 2017 1:56 PM
    Moderator

All replies

  • From the point of view of the SQL Server, inserting the N:N data means making three separate insertions into the three tables. For each candidate that you want to store, you will need an Insert into the Candidate table, and save the primary key of the candidate. For each Skill, you will need an Insert in the Skills table, and save the Primary Key (or retrieve it from the table if it is already loaded with all the possible skills -- you probably already have this if you are showing them in a grid). And then, for each connection between a candidate and a skill, you need to insert one row into the intermediate table that contains the keys for the candidate and the skill.

    The second part would be how to generate all those Inserts from C#. That would depend on the data access technology that you are using. If you are using plain ADO.NET, then just write the INSERTs and send them with an ExecuteNonQuery using a SdlCommand. If you are using Entity Framework, then create the instance of the Employee entity, and use the Add method on its Skills property to add each skill. If you configured your EF model correctly, all the necessary inserts (including the intermediate table) will be generated automatically for you when you invoke SaveChanges.

    Wednesday, April 5, 2017 6:56 PM
    Moderator
  • Please post questions related to ASP.NET in the ASP.NET forums.

    If you are using Entity Framework then we can move your thread to the appropriate forum.

    Wednesday, April 5, 2017 8:18 PM
    Moderator
  • Hello Albert,

    do i need to pass data to the sql server at ones or first create candidate and then retrive primary key id in c# code and then for each skillset insert data into the third relation table.

    Moreover , i confused about how to pass n:n composite keys into the database.

    i mean for input parameter what value to be send.

    do i need to send a string array from front end or something different.

    i am more stuck on this point.

    Thursday, April 6, 2017 8:44 AM
  • But WHAT database access technology are you using? The answer varies hugely depending on what you use. If you use EF, then you don't "do" anything with the database, you just configure your hierarchy of entities in memory and EF sends the appropriate inserts with the appropriate keys to the database. But if you use ADO.NET, you have to generate the individual Inserts, and retrieve the keys if they are autogenerated (or decide on an algorithm to generate the unique values if this is not done by the database), and then send all the individual INSERTs (NOT an array, you'd have to build the whole SQL queries). So, as you see, depending on your client technology and the database configuration, there are large variations on how you would do things. It would be best if you gave details about things such as your client db technology and the configuration of your tables.
    Thursday, April 6, 2017 3:30 PM
    Moderator
  • Hello Alberto,

    Tables are as below.

    1. TblCandidate

    2.TblSkill

    3TblCandidateSkill (relation table with pk from both above tables.)

    i want to use ado.net from the code behind.

    now let me know how can i achieve this functionality.

    Friday, April 7, 2017 10:26 AM
  • @EmpAnsar

    Your question is in fact nowhere C#. 

    It is database knowledge and then how to use that in ASP.Net

    If is very related what kind of technology you use at the C# side, but also what technology you use at the Server side. 

    Some persons who start do never set a relation in the DataBase Server, but use it as MS Office. 

    Therefore first try to understand relations on the Server side, if you use than the handling of the relations is completely done by the technology you use, which can be from as Microsoft parts be:

    1. TableManagers;
    2. Linq to SQL;
    3. Linq to Entities;

    But many more. 

    A good forum for relations in a database? The scope of the SQL transaction guys is more focused on that, therefore maybe is that a good forum to ask this question.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql


    Success
    Cor


    Friday, April 7, 2017 1:09 PM
  • i want to use ado.net from the code behind.

    Steps:

    1) Save the candidate using a SqlCommand. I presume that you are already doing this in other parts of your application, so you already know how to create the SqlConnection, as well as configure, parameterize and execute the SqlCommand. I also presume that the tblCandidate has a primary key named IdCandidate and that it is an Identity field so it will be generated at the DB (I asked you before but you never answered about how your key was generated).

    Saving the row and then retrieving the ID can be done in several ways, but it is very common to add a select at the end of the insert so it can be retrieved in one operation. Therefore you will need an insert similar to this one:

    "Insert into TblCandidate(list of fields except Id) Values (list of paramenters); select scope_identity();"

    Run it with .ExecuteScalar so that you recover the key.

    2) Get all the keys for the Skills selected for the candidate. If you loaded the skills into a table that you are presenting on screen, then you already have those keys, which you recovered from the database when you read the rows in order to present them. For simplicity, I will refer to the corresponding column as SkillId, since you didn't mention how it is named in the database.

    3) Repeat in a loop: for each of the skills insert a row into the intermediate table, by using a SqlCommand to send this instruction with .ExecuteNonQuery:

    "Insert into TblCandidateSkill(CandidateId, SkillId) values (@p1, @p2)".

    Obviously, parameter p1 will receive the value that you retrieved in step 1, and parameter p2 on each iteration of the loop will receive one of the values that you retrieved in step 2.

    Friday, April 7, 2017 1:56 PM
    Moderator