now i want to create datatable from datset ds or these value can iserted into any table ... please help me guys.. i want to create a tabel from returned dataset ds ;
itsss works for me thanks very much ... but there is problem in my code ... when i m generating columns name from Excel ,its just bulkcopying on table which all cloumns are varchar .
// get data columns
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
By this its just taking only columns which type is string . how can i do this weather its varchar or int .
Bytheway weather u replied it or not ... still god bless ya ..bcoz u solved my last problem .
sssstud
Member
40 Points
53 Posts
i have imported my excel into dataset ds,
Feb 29, 2012 08:04 AM|LINK
now i want to create datatable from datset ds or these value can iserted into any table ... please help me guys.. i want to create a tabel from returned dataset ds ;
string Patfilename = System.Web.HttpContext.Current.Server.MapPath("Book1.xls");
protected void Page_Load(object sender, EventArgs e)
{
GetExcel(Patfilename);
}
public DataSet GetExcel(string fileName)
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
Range oRng;
try
{
// creat a Application object
oXL = new ApplicationClass();
// get WorkBook object
oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
// get WorkSheet object
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
DataSet ds = new DataSet();
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
// get data columns
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
//string colString = sb.ToString().Trim();
//string[] colArray = colString.Split(':');
// get data in cell
for (int i = 1; i <= iValue; i++)
{
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
string strValue = oRng.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}
return ds;
}
catch (Exception ex)
{
Label1.Text = "Error: ";
Label1.Text += ex.Message.ToString();
return null;
}
}
ramanselva
Contributor
2064 Points
324 Posts
Re: i have imported my excel into dataset ds,
Feb 29, 2012 08:13 AM|LINK
//First create a connection string to destination database string connectionString; connectionString = YourConnectionStringand Initial Catalog=TestSMODatabase"; //Open a connection with destination database; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); //Open bulkcopy connection. using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection)) { //Set destination table name //to table previously created. bulkcopy.DestinationTableName = "dbo.TestTable"; try { bulkcopy.WriteToServer(ds.Tables["dtExcel"]); } catch (Exception ex) { Console.WriteLine(ex.Message); } connection.Close(); } }This can be beneficial to other community members reading the thread.
Regards,
Rama Selvam M.
sssstud
Member
40 Points
53 Posts
Re: i have imported my excel into dataset ds,
Feb 29, 2012 10:16 AM|LINK
itsss works for me thanks very much ... but there is problem in my code ... when i m generating columns name from Excel ,its just bulkcopying on table which all cloumns are varchar .
// get data columns
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
By this its just taking only columns which type is string . how can i do this weather its varchar or int .
Bytheway weather u replied it or not ... still god bless ya ..bcoz u solved my last problem .
ramanselva
Contributor
2064 Points
324 Posts
Re: i have imported my excel into dataset ds,
Feb 29, 2012 11:10 AM|LINK
Hi, First you defined the table with schemathen start to insert table DataTable table = new DataTable(); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime));This can be beneficial to other community members reading the thread.
Regards,
Rama Selvam M.