Click here to Skip to main content
15,915,019 members
Please Sign up or sign in to vote.
1.22/5 (2 votes)
See more:
How to insert data into a sql server from data table via stored procedure
Posted

Read this Link:

http://stackoverflow.com/questions/23166121/how-to-insert-data-into-a-sql-data-table-via-stored-procedure"

"http://www.codeproject.com/Articles/412802/Sending-a-DataTable-to-a-Stored-Procedure"
 
Share this answer
 
v2
 
Share this answer
 
C#
public void  InsertDataTable(DataTable dt)
        {
            Int64 result = 0;
            using (SqlConnection con = new SqlConnection(connection))
            {
                con.Open();
                SqlCommand command = con.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = con.BeginTransaction("PortingConnection");

                // Must assign both transaction object and con 
                // to Command object for a pending local transaction
                command.Connection = con;
                command.Transaction = transaction;

                try
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction))
                        {
                            bulkCopy.DestinationTableName = "DatabaseTable";
                            bulkCopy.BatchSize = 5000;
                            bulkCopy.WriteToServer(dt);
                            bulkCopy.Close();

                        }

                        transaction.Commit();

                                   

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
            }



        }
 
Share this answer
 
First Create Type For parameter

SQL
CREATE TYPE DtType AS TABLE
(
    SNO INT, Name VARCHAR(50), Address VARCHAR(max)
)


Procedure to insert Data
Create  PROCEDURE spSaveStudentData
@DataTableDetails DtType READONLY
AS
BEGIN
   
            
            --To insert the new records in the table
            INSERT INTO StudentDetails(Sno, Name, Address) 

            SELECT SNO, Name, Address FROM @DataTableDetails 
        
    END
GO
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900