Save multiple rows in one table in one connection IN sqlserver

Question

Scenario: Just consider i have a table having 3 column Id, QuestionId and optedAnswer. I have a form which consists n no of question and some options for answer. On save button i want to save data in database(in my table).

Problem: I want to save all the answers in one connection.

Step taken by me: I made a string having structure questionId:optedAnswwer | questionId : optedAnswer | and so on.... I wrote a procedure. started a loop. split the data based on ':' and '|' and saved data in one connection. But it is a rigorous task. Is there any way to save the data directly without using loop and split.

user1711092 · Accepted Answer · 2012-11-05 11:01:27Z

3

Save your each questionId and its OptedAnswer in Datatable and then insert your datatable to SQL table as below :

DataTable dataTable = null; // your data needs to be here
        try
        {
            ConnectionStringSettings mConString = ConfigurationManager.ConnectionStrings["SiteSqlServer"];

            // Optional truncating old table
            using (SqlConnection connection = new SqlConnection(mConString.ConnectionString))
            {
                connection.Open();
                // Delete old entries
                SqlCommand truncate = new SqlCommand("TRUNCATE TABLE MYTABLE", connection);
                truncate.ExecuteNonQuery();
            }

            SqlBulkCopy bulkCopy = new SqlBulkCopy(mConString.ConnectionString, SqlBulkCopyOptions.TableLock)
                                          {
                                              DestinationTableName = "dbo.MYTABLE",
                                              BatchSize = 100, //set your required size
                                              BulkCopyTimeout = 360
                                          };
            bulkCopy.WriteToServer(dataTable);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

answered Nov 5, 2012 at 11:01

user1711092

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

1 Comment

Amit Sinha Over a year ago

Thank you. I was aspecting same kind of answer.

Rohit Vyas · Accepted Answer · 2012-11-05 10:50:08Z

1

Create a XML string of all your queastion and ans and pass these xml string to sql and use the
sp_xml_preparedocument procedure these sql inbuilt proc that reads the XML.

you can get more information on Bulk INsert

answered Nov 5, 2012 at 10:50

Rohit Vyas

1,9793 gold badges19 silver badges28 bronze badges

Comments

Elken · Accepted Answer · 2012-11-05 13:08:55Z

0

I would also go for an xml-centric solution, prepare you data in an xmldocument, post that inte the database and use a SELECT from the xml as source for your insert eg:

declare @xml XML
DECLARE @docid int

set @xml = N'<?xml version="1.0" ?>
<Custs>
 <Cust>
  <name>Erik</name>
  <lastname>Stark</lastname>
 </Cust>
 <Cust>
  <name>Donald</name>
  <lastname>Duck</lastname>
 </Cust>
 <Cust>
  <name>Johnny</name>
  <lastname>Walker</lastname>
 </Cust>
</Custs>'

EXEC sp_xml_preparedocument @docid OUTPUT, @xml

SELECT    *
FROM  OPENXML (@docid, '/Custs/Cust',2)
            WITH (name varchar(50), lastname varchar(50))
exec sp_xml_removedocument @docid

answered Nov 5, 2012 at 13:08

Elken

1821 silver badge4 bronze badges

Comments

Mike · Accepted Answer · 2019-12-06 16:30:41Z

There is another way to save multiple data in one connection. Create a type of table like this

CREATE TYPE [dbo].[YourTypeName] AS TABLE(
    [Col1] [bigint] NULL,
    [Col2] [datetime] NULL,
    [Col3] [bigint] NULL,
    [Col4] [bigint] NULL,
    [Col5] [datetime] NULL
)

and then write a procedure like this....

CREATE PROCEDURE [dbo].YOURPROCEDURENAME]
( 
    @yourDataTableName YourTypeName READONLY
)
AS
    INSERT INTO TableName
    (Col1,
     Col2,                        
     Col3,
     Col4,
     Col5)
    SELECT CP.Val1,
           CP.Val2,
           CP.Val3,
           CP.Val4,
           CP.Val15)
   FROM @yourDataTableName  CP
GO

And then create a datatable in code behind and pass that datatable in prosedure like this...

SqlCommand cmd = new SqlCommand("YOURPROCEDURENAME");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@yourDataTableName", SqlDbType.Structured).Value = datatable;
cmd.Parameters[0].TypeName = "YourTypeName ";

Collectives™ on Stack Overflow

Save multiple rows in one table in one connection IN sqlserver

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related