How to execute 100 insert statement using c# [Resolved]

Posted by Allemahesh under C# on 7/22/2013 | Points: 10 | Views : 817 | Status : [Member] [MVP] | Replies : 2
I have 100 rows of data. Now I want to insert these 100 rows using insert statement. But I want to use only one connection string. How to do this?




Responses

Posted by: Kmandapalli on: 7/23/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Hi,

Suppose you have data in your excel sheet which contains 1500 rows and you want to save this data into database.

In your design page put a file upload control and a button.
Then in your code behind page call this method in your button click event:

public void Insert(HttpPostedFileBase AmazonUpload)
{
if (AmazonUpload.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || AmazonUpload.FileName.EndsWith(".xslx", StringComparison.OrdinalIgnoreCase))
{
var fileName = Path.GetFileName(AmazonUpload.FileName);
var path = Path.Combine(Server.MapPath("~/App_Data/"), fileName);
AmazonUpload.SaveAs(path);
var excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName) + ";Extended Properties=Excel 12.0;");
OleDbConnection objOlecon = new OleDbConnection();
objOlecon.ConnectionString = excelConnectionString;
objOlecon.Open();
OleDbDataAdapter objOleDa = new OleDbDataAdapter("Select * from [Amazon-orders$]", objOlecon);
DataTable objdt = new DataTable();
objOleDa.Fill(objdt);
SqlConnection objsqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString);
objsqlCon.Open();
SqlCommand cmd = new SqlCommand("ImportOrders", objsqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AmazonOrdersTemp", objdt);
cmd.ExecuteNonQuery();
objsqlCon.Close();
ViewData["message"] = "Records Imported Successfully";
}
}

ImportOrders Query is retrieving data from Datable and storing it into another table:

CREATE PROCEDURE ImportOrders
(
@AmazonOrdersTemp AmazonOrderType READONLY
)
AS
BEGIN

INSERT INTO AmazonOrders
SELECT *
FROM @AmazonOrdersTemp

END

Please Mark as Answer if satisfied.............

Thank You,
Kavya Shree M.


Kavya Shree Mandapalli

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Phagu007 on: 7/23/2013 [Member] Starter | Points: 25

Up
0
Down
BEGIN TRANSACTION
GO
USE AdWorks;
GO
CREATE TABLE dbo.mycom
(
id_num int ID(100, 5),
com_name nvarchar(100)
)
GO
INSERT mycompanies (com_name)
VALUES ( 'New Bike Store');
GO

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response