I am having problem reading data from excel (It contains more that 30,000 rows) using C#. When i test in local system it works fine i.e. it copies all the rows from excel, but when i deploy and run in production server it only reads around 12,000 rows).
I have also tried using BulkCopy, but does the same. Does it have to do anything with webserver(my webserver is windows server 2008)?
Please help me on this. Thanks
protected void ImportFromExcelToTempCompetitionTable(string filename)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + filename + ";" + "Extended Properties=Excel 12.0";
OleDbDataAdapter cmdOle;
cmdOle = new OleDbDataAdapter("SELECT [StringColA], [StringColB], [NumberCol1], [NumberCol2] from [XYZSheet]", strConn);
try
{
Boolean blnSuccess = false;
DataSet ds = null;
ds = new DataSet();
cmdOle.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
//*****************
using (cnn = new SqlConnection(util.GetConnectionString()))
{
string sqlQuery = "INSERT INTO tbl_XYZ(StringColA, StringColB, NumberCol1,NumberCol2) values (@StringColA, @StringColB, @NumberCol1, @NumberCol2)";
cmd = new SqlCommand(sqlQuery, cnn);
cmd.CommandType = CommandType.Text;
SqlParameter StringColA= new SqlParameter("@StringColA", SqlDbType.VarChar, 500);
StringColA.Value = dr["StringColA"].ToString();
cmd.Parameters.Add(StringColA);
SqlParameter StringColB= new SqlParameter("@StringColB", SqlDbType.VarChar, 500);
StringColB.Value = dr["StringColB"].ToString();
cmd.Parameters.Add(StringColB);
SqlParameter NumberCol1= new SqlParameter("@NumberCol1", SqlDbType.Decimal, 25);
NumberCol1.Value = dr["NumberCol1"].ToString();
cmd.Parameters.Add(NumberCol1);
SqlParameter NumberCol2= new SqlParameter("@NumberCol2", SqlDbType.Decimal, 25);
NumberCol2.Value = dr["NumberCol2"].ToString();
cmd.Parameters.Add(NumberCol2);
try
{
cnn.Open();
cmd.ExecuteNonQuery();
blnSuccess = true;
}
catch (Exception ex)
{
}
finally
{
if (blnSuccess)
{
cmd.Dispose();
cnn.Close();
}
}
}
//*****************
}
}
}
catch (Exception ex)
{
}
finally
{
}
}
