Multiple inserts to different tables in SQL Server 2008

Question

I m writing a web service using C#, .NET framework 3.5 in Visual studio 2010. My questions might be basic but I had no luck in making it work.

I could successfully submit my data to SQL db via web service. What I would like to be able to do is while inserting a record/row in one table in SQL db, I would like to be able to insert data into another table let's say table B. Table B has 5 cols. It will get two of the column values from the previous table ( table A that gets inserted via a web service). One column in Table B is Primary key value, rest two columns will have static text values.

Here's the structure for table A

id int (primary key)
CustName varchar(45)
CustAddress varchar(45)
CustContacts varchar(45)
CustQuoteDate varchar(45)
CustLoginId varchar(45)

Table B structure:

id int (primary key)
OrderId varchar(45) NOT NULL //This will get value from table A's Primary key
OrderReqDate [varchar](45) NOT NULL //This will get value from table A's  CustQuotDate 
OrderStatus [varchar](45) NOT NULL // This will be a static value "Requested"
OrderGroup [varchar](45) NOT NULL // This will be a static value "GGGG"

So I tested out my query for second insertion:

INSERT INTO dbo.TableB(OrderId , currstatus, OrderReqDate, OrderGroup) 
SELECT ta.id, 'REQUESTED', ta.CustQuoteDate, 'GGGG' from dbo.TableA ta
where ta.CustLoginId = 'kenw1'

so for each newly added CustLoginId in TableA , the second table ie. TableB also gets a row insert with some fields from tableA.

My question is how/where to edit my web service code below to be able to do second insertion? Your time and effort in helping me out will be greatly appreciated. TIA.

Here's my web service:

[WebMethod(Description = "Adds a Customer Request Form data to the database [TableA].")]
public void AddCustRequestRecord(string CustName, string CustAddress, string custContacts, string custQuoteDate, string CustLoginId)
{
    //Static text values for columns in tableB
    string OrderStatus = "REQUESTED";
    string OrderGroup = "GGGG";

    string connectionString =
      ConfigurationManager.ConnectionStrings["myDatabase"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        DateTime now = DateTime.Now;
        string QuoteDt = now.ToString();

        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "INSERT INTO TableA "
          + "(CustName, CustAddress, custContacts, CustLoginId, custQuoteDate) "
          + "VALUES (@CustName, @CustAddress, @custContacts, @CustLoginId,'" + Quotedt + "')";

        cmd.Parameters.AddWithValue("CustName", CustName);
        cmd.Parameters.AddWithValue("CustAddress", CustAddress);
        cmd.Parameters.AddWithValue("custContacts", custContacts);
        cmd.Parameters.AddWithValue("CustLoginId", CustLoginId);
        cmd.Parameters.AddWithValue("custQuoteDate", custQuoteDate);

        cmd.Connection = conn;

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();                
        }
        catch
        {
            // Handle the error using your own preferred error-handling method
        }
        finally
        {
            conn.Close();
        }
    }
}

can you not use a trigger on the insertion to tableA to insert into B? — Chris
– Chris, Commented Aug 2, 2012 at 11:38
@Chris Moutray, I simply tried creating a second command object, second connection to have second insert. No errors but didn't make insert to my second table. — Nemo
– Nemo, Commented Aug 2, 2012 at 11:52
Review your logic on "newly added CustLoginId in TableA" unless that column has a unique constraint then you can have duplicate CustLoginId. For a repeated CustLoginId the second command will insert will more than just the newly created. If CustLoginId is unique then use it as the primary key. Otherwise you need to retrieve the id from the first insert. — paparazzo
– paparazzo, Commented Aug 2, 2012 at 12:48
@Blam, Thanks for your suggestions. Actually the data for tableA comes from a InfoPath form. InfoPath checks to make sure that user cannot fill out the form more than once with his/her CustLoginId. So I need not worry about to take care of it in my code. Thanks for some insights. — Nemo
– Nemo, Commented Aug 2, 2012 at 13:34

Steve · Accepted Answer · 2012-08-02 13:08:33Z

1

Honestly, the most appropriate way to do this would be to use a stored procedure. One, it is better for avoiding SQL injection and two it will do what you want with one connection and one call to the database and is more efficient. Let SQL Server do the work for you.

answered Aug 2, 2012 at 13:08

Steve

3992 silver badges7 bronze badges

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

1 Comment

Nemo Over a year ago

Thanks for your suggestion. I did it using a stored proc and calling it in my web service. Nicely done! had to struggle lil bit with escape characters in C# when doing in the code itself... let the burden fall onto SQL, it did a good job.. more importantly sql injection was also avoided. Ty. You guys ROCK!

Chris Moutray · Accepted Answer · 2012-08-02 11:57:51Z

1

Based on what you have you'll want to use one connection and a second command. Keep the connection open for both commands but make sure to use a transaction so that if there's an error both sql inserts are rolled back.

The SqlConnection.BeginTransaction found on MSDN shows a good example http://msdn.microsoft.com/en-us/library/86773566.aspx of how it should be coded.

answered Aug 2, 2012 at 11:57

Chris Moutray

18.4k7 gold badges49 silver badges66 bronze badges

Comments

paparazzo · Accepted Answer · 2012-08-02 18:10:23Z

1

Per comment from OP CustLoginID in Table A is unique. I suggest you change the data design to 3rd normal form. Use natural key and don't repeat columns.

Table A
CustLoginId varchar(45) (primary key)
CustName varchar(45)
CustAddress varchar(45)
CustContacts varchar(45)
CustQuoteDate varchar(45)

Table B 
id int (primary key)
CustLoginId varchar(45) NOT NULL 
OrderReqDate varchar(45) NOT NULL 
OrderStatus varchar(45) NOT NULL 
OrderGroup varchar(45) NOT NULL

Now you can just insert directly in to Table B as you don't need the ID from A. You already have the CustLoginId.

But I question the statement that LogonID is unique. That would imply you only have CustQuoteDate per LogonID. I suspect a customer can have more than one quote. Assuming a customer can have more than one quote this would be the design. And why is date a varchar? Since datatime is down to the millisecond you should not get PK violation on TableB. If you want to allow two quotedate the same for a LogonID then just remove the PK from Table B.

Table A
CustLoginId varchar(45) (primary key)
CustName varchar(45)
CustAddress varchar(45)
CustContacts varchar(45)


Table B 
CustLoginId varchar(45) (primary key) foreign key relation to Table A
CustQuoteDate datetime (primary key)
OrderReqDate varchar(45)   
OrderStatus varchar(45)  
OrderGroup varchar(45)

edited Aug 2, 2012 at 18:10

answered Aug 2, 2012 at 14:32

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

1 Comment

Nemo Over a year ago

Thanks for your input Blam. Will surely think about re-designing.

Pravin Pawar · Accepted Answer · 2012-08-02 11:37:43Z

0

There are multiple ways in which you can achieve it

1.You can use second command object(This command will use the same connection)to have another insert.

2.You can call a stored procedure where in you can handle multiple inserts & have access to recently inserted data in table A.

3.You can always use ORMs like Linq to SQL or EF.

4.Use trigger on Table A's Insert.

answered Aug 2, 2012 at 11:37

Pravin Pawar

2,5793 gold badges34 silver badges40 bronze badges

2 Comments

Nemo Over a year ago

Ty for your suggestions. I tried the very first one you suggested as that seems simplest one but used diff connection. Will that be a reason why my insertion didn't work?

Chris Moutray Over a year ago

@Nemo you really want to consider using one connection and make use of transactions

Collectives™ on Stack Overflow

Multiple inserts to different tables in SQL Server 2008

4 Answers 4

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related