2011年3月1日星期二

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" />



沒有留言:

發佈留言