CODEWITHSUNDEEP
c#asp.netsql-server
Amit Sinha
Amit Sinha

Reputation: 614

Save multiple rows in one table in one connection IN sqlserver

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.

Upvotes: 0

Views: 1592

Answers (4)

Amit Sinha
Amit Sinha

Reputation: 614

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 ";

Upvotes: 0

Elken
Elken

Reputation: 182

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

Upvotes: 0

user1711092
user1711092

Reputation:

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);
        }

Upvotes: 3

Rohit Vyas
Rohit Vyas

Reputation: 1969

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

Upvotes: 1

Related Questions