Read xls file asp.net

2012-04-12T04:06:00.002-07:00

Read xls file through oledb connectivity

//xls connection object

OleDbConnection ocon = null;

//xls command object

OleDbCommand ocmd = null;

//xls data set object

DataSet ds = new DataSet();

//xls Data Adapter object

OleDbDataAdapter oadapter = new OleDbDataAdapter();

//Connection string path

String connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0";

//Data table to get sheet names as table names for select query

DataTable dt = null;

Try

{

//initializing connection object

ocon = new OleDbConnection(connectionstring);

// opens connection

ocon.Open();

// gets sheet names

dt = ocon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

// process select qyery

foreach(datarow row in dt.rows)

{

cmd = new OleDbCommand("SELECT * FROM [" + Convert.Tostring( drow["TABLE_NAME"]) , oledbConn);

oleda.SelectCommand = cmd;

oleda.Fill(ds);

}

Comments are requested from Blog users........

passing Data Table to SQL Server 2008

2012-04-12T01:42:00.003-07:00

It is very Easy for bulk upload or insert while passing Data Table to SQL Server 2008.

for these you need to follow the steps

from DB

Step 1: Create table type

like create type <table name> as table( -- your entities)

Example
create type testtype(id int)

Step 2: create procedure to consume your type

example

create procedure calling testprocedure
@testtypes as dbo.testtype
as
begin

-- do something with @testtypes

end

from code:

create parameter object or include commands parameter collection

this example I have created one parameter object and added structure to parameter object then i am passing the data table to procedure

parameter.ParameterName = "@ testtypes ";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
Parameter.Value = dsExport.Tables[0];

command.Parameters.Add(parameter);

Enjoy with Bulk update

Comments are requested from Blog users........

.net,sql, Java Script and Jquery help

Read xls file asp.net

passing Data Table to SQL Server 2008