C# Problem - Return null while reading Excel with OLEDB

While using OLEDB reading excel file, some cells return null even they got data on them.
Cause - The OLDEB driver will read the first 8 rows to determine the data type of each column.
If the first 8 rows of a column is numeric, but all the data is text from 9th row to the end. Then it can just read the first 8 rows data, all the others will return null.


Solution - Configure IMEX ( IMport EXport mode )
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)

Set "IMEX=1" to the Extended Properties

    e.g. Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Data.xls";Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"


If it still happening even configure IMEX, then we can edit the registry:
HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\
Set TypeGuessRows = 0 (Default = 8)

C# Oracle Transaction

Open Oracle connection:

OracleConnection oraConn;
string oraConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
oraConn = new OracleConnection(oraConnString);
oraConn.Open();
OracleCommand myCommand = oraConn.CreateCommand();

=============================================================

Begin transaction:

OracleTransaction trans = oraConn.BeginTransaction(IsolationLevel.ReadCommitted);

=============================================================

Usage:

try
{
    myCommand.Transaction = trans;

    myCommand.CommandText =  

    "INSERT INTO Dept values (50, 'TECHNOLOGY', 'DENVER')";
    myCommand.ExecuteNonQuery();
    myCommand.CommandText = 

    "INSERT INTO Dept values (60, 'ENGINEERING', 'KANSAS CITY')";
    myCommand.ExecuteNonQuery();

    trans.Commit();
}
catch (Exception e)
{
    trans.Rollback();
}

C# Export DataSet to Excel

    public void ExportDataSetToExcel(DataSet ds, string filename)
    {
        HttpResponse response = HttpContext.Current.Response;
        // first let's clean up the response.object
        response.Clear();
        response.Charset = "";
          
        // set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
          
        // create a string writer
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid
                DataGrid dg = new DataGrid();
                dg.DataSource = ds.Tables[0];
                dg.DataBind();
                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }
    }

C# Covert Excel Serial Date Number to DD/MM/YYYY

When I trying to import the excel to oracle, I encounter a problem when getting the date in a column.
It returns a number "44509.0". It is a serial date number and is the number of days since 1-1-1900.

DateTime.FromOADate(double.Parse(objDS.Tables[0].Rows[i][y].ToString())).ToShortDateString()

C# Confirm dialog for delete action

For the win application:

if (MessageBox.Show("It will delete the record, are you sure?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
    // further action
}

==============================================================

For the web application:

Response.Write("<script>alert('It will delete the record, are you sure?')</script>");

C# Upload Excel to Oracle

public partial class UploadExcel: System.Web.UI.Page
{
    public OracleConnection oraConn;
    public OracleCommand myCommand;

    protected void Page_Load(object sender, EventArgs e)
    {       
        string oraConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        oraConn = new OracleConnection(oraConnString);

        try
        {
            oraConn.Open();
            myCommand = new OracleCommand("SET DEFINE OFF", oraConn);
            myCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {           
        }
    }


    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string savePath = Server.MapPath("~/Upload/");
        if (FileUpload.HasFile)
        {

            string upFileName = FileUpload.FileName;
            savePath += upFileName;
            FileUpload.SaveAs(savePath);

        }
        string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + savePath + ";Extended Properties='Excel 8.0;HDR=NO'";
        OleDbConnection objConn = new OleDbConnection(strCon);
        string strCom = " SELECT * FROM [Sheet1$] ";
        objConn.Open();

        OleDbDataAdapter objCmd = new OleDbDataAdapter(strCom, objConn);
        DataSet objDS = new DataSet();
        objCmd.Fill(objDS);
        objConn.Close();


        string sqlCMD = "";


        for (int i = 1; i < objDS.Tables[0].Rows.Count; i++)
        {
            for (int y = 2; y < objDS.Tables[0].Columns.Count; y++)
            {
                sqlCMD = "INSERT INTO UPLOAD (field1, field2) VALUES('" + objDS.Tables[0].Rows[i][y].ToString() + "')";
            }
        }


        try
        {
            myCommand = new OracleCommand(sqlCMD, oraConn);
            myCommand.ExecuteNonQuery();
        }
        catch
        {
        }
    }
}


============================================================


<asp:FileUpload ID="FileUpload" runat="server" EnableTheming="True" Height="22px" />
<asp:Button ID="btnUpload" runat="server" Height="22px" OnClick="btnUpload_Click" Text="New Upload" Width="186px" />