NOTHING BUT MY LEARNING AND EXPEREINCE WITH ASP.NET/C#: EXCEL SHEET UPLOAD IN ORACLE DATABASE WITH DATE CONDITIONS

EXCEL SHEET UPLOAD IN ORACLE DATABASE WITH DATE CONDITIONS

Hello Friends Today we will see how to upload excel sheet in oracle database where USER select the date range and only data present in date range gets inserted.

IF data in date range is already present then user need to delete the data and insert data into database(with some additional data).

SO first WE will see how to upload EXCEL data in ORACLE database..................................

CODE DESIGN:

<strong><span style="color: #0000ff">Call Center Excel

File Upload</span></strong></td>

</tr>

From Date:</td>

<asp:TextBox ID="txtFromDate" runat="server" Columns="12"></asp:TextBox>

<asp:Button ID="btnFromDate" runat="server" Font-Bold="True" Text="31" OnClick="btnFromDate_Click" /> 

<asp:Calendar ID="calFromDate" runat="server" BackColor="#FFFFCC"

BorderColor="#FFCC66" Font-Names="Verdana" Font-Size="8pt" ForeColor="#663399"

Height="200px" Width="220px"

OnSelectionChanged="calFromDate_SelectionChanged" BorderWidth="1px"

DayNameFormat="Shortest" ShowGridLines="True">

<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt"

ForeColor="#FFFFCC" />

</asp:Calendar>

</td>

</tr>

To Date:</td>

<asp:TextBox ID="txtToDate" runat="server" Columns="12"></asp:TextBox>

<asp:Button ID="btnTodate" runat="server" Font-Bold="True" Text="31" OnClick="btnTodate_Click" />

<asp:Calendar ID="calToDate" runat="server" BackColor="#FFFFCC"

BorderColor="#FFCC66" Font-Names="Verdana" Font-Size="8pt" ForeColor="#663399"

Height="200px" Width="220px"

OnSelectionChanged="calToDate_SelectionChanged" BorderWidth="1px"

DayNameFormat="Shortest" ShowGridLines="True">

<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt"

ForeColor="#FFFFCC" />

</asp:Calendar>

</td>

</tr>

Browse File:</td>

<asp:FileUpload ID="flUpld" runat="server" Width="400px" /></td>

</tr>

</td>

 <asp:Button ID="btnUload" runat="server" OnClick="btnUload_Click" Text="Upload" Width="104px" ToolTip="Click Here to upload Exel file." />

<asp:CheckBox ID="chkDelReupld" runat="server" Text="Delete Existing Record" TextAlign="Left" />

</tr>

</td>

<asp:Label ID="lblDataUploadedMessage" runat="server" Font-Bold="False" Font-Size="9pt"

ForeColor="Blue"></asp:Label>

<br />

</td>

</tr>

</table>

A simple two calender are given to select FROM DATE AND TO DATE and an simple FILE UPLOAD control of asp is used to select uploaded file.

THE CODE BEHIND:

//FIRST WE NEED TO GET RECORD FROM EXCEL SHEET TO DATATABLE

And this is done by:

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

AND

DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

protected void Page_Load(object sender, EventArgs e)

{

this.SmartNavigation = true;

if (this.IsPostBack == false)

{

calFromDate.Visible = false ;

calToDate.Visible = false;

chkDelReupld.Visible = false;

}

btnUload .Attributes.Add("onClick", "if(!ValidationBeforeSave()) return false ;");

// fillMaxDate();

}

//BELOW METHODS ARE USED FOR CALENDER CONTROL OPENING AND CLOSING...SIMPLE //U WILL GET IT

protected void btnFromDate_Click(object sender, EventArgs e)

{

calFromDate.Visible = true;

calToDate.Visible = false;

}

protected void btnTodate_Click(object sender, EventArgs e)

{

calFromDate.Visible = false;

calToDate.Visible = true ;

}

protected void calFromDate_SelectionChanged(object sender, EventArgs e)

{

calToDate.Visible = false;

txtFromDate.Text = calFromDate.SelectedDate.ToString ("dd-MMM-yyyy");

calFromDate.Visible = false;

}

protected void calToDate_SelectionChanged(object sender, EventArgs e)

{

calFromDate.Visible = false;

txtToDate.Text = calToDate.SelectedDate.ToString("dd-MMM-yyyy");

calToDate.Visible = false;

}

protected void btnUload_Click(object sender, EventArgs e)

{

DateTime dtFDate = Convert.ToDateTime(txtFromDate.Text );

DateTime dtTDate = Convert.ToDateTime(txtToDate.Text);

if (dtFDate > dtTDate)

{

txtFromDate.Text = "";

Response.Write("<script>alert('From Date can not be greater then to date.')</script>");

return;

}

try

{

string connectionString = "";

if (flUpld.HasFile)

{

string fileName = Path.GetFileName(flUpld.PostedFile.FileName);

string fileExtension = Path.GetExtension(flUpld.PostedFile.FileName);

string fileLocation = Server.MapPath("~/App_Data/" + fileName);

flUpld.SaveAs(fileLocation);

//Check whether file extension is xls or xslx 12

if (fileExtension == ".xls")

{

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

}

else if (fileExtension == ".xlsx")

{

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

} //Create OleDB Connection and OleDb Command 23

OleDbConnection con = new OleDbConnection(connectionString);

OleDbCommand cmd = new OleDbCommand();

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = con;

OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);

DataTable dtExcelRecords = new DataTable();

con.Open();

DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

//NOW THE SHEET NAME IS REQUIRED FROM WHICH DATA NEED TO BE TAKEN FROM //EXCEL.HERE WE HAVE TAKEN DEFAULT Sheet i.e SHEET1

string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();

cmd.CommandText = "SELECT * FROM [Sheet1$]";

dAdapter.SelectCommand = cmd;

dAdapter.Fill(dtExcelRecords);

con.Close();

// THE IS DELETE CHECKBOX IS MADE TO DELETE THE DATE ANGE RECORDS SO IF CHECKBOX IS CHECKED THE DELETE COMMAND IS FIRED

if (chkDelReupld.Checked == true)

{

string strCmdDelete = "DELETE FROM Sampletable WHERE trunc(DATETIME1) BETWEEN '" + txtFromDate.Text + "' AND '" + txtToDate.Text + "'";

DeleteExistingRecors(strCmdDelete);

}

OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["dbCon"].ToString());

String strCmd = "SELECT COUNT(*) AS cnt FROM Sampletable WHERE trunc(DATETIME1) BETWEEN '" + txtFromDate .Text + "' AND '" + txtToDate.Text + "'";

OracleCommand oracmd = new OracleCommand(strCmd, conn);

OracleDataAdapter ora_adapter_li = new OracleDataAdapter(oracmd);

DataSet ds = new DataSet();

ora_adapter_li.Fill(ds);

if (Convert.ToInt32(ds.Tables[0].Rows[0]["cnt"].ToString()) > 0 && chkDelReupld .Checked ==false )

{

Response.Write("<script>alert('[" + ds.Tables[0].Rows[0][0].ToString() + "] Record Found in Database Do You want to delete and re upload. ')</script>");

chkDelReupld.Visible = true ;

}

else

{

SaveRecords(dtExcelRecords, fileName);

fillMaxDate();

}

catch (Exception Ex)

{

if (Ex.Message.Contains("'Sheet1$' is not a valid name") == true)

{

Response.Write("<script>alert('Invalid selected Excel File Or WorkSheet [Sheet 1] does not found.')</script>");

}

finally

{

//con.Close();

}

//NOW LETS WRITE FUNCTOIN TO SAVE RECORD IN DATABASE

public void SaveRecords(DataTable dtExcelRecords, string getExcelSheetName)

{

try

{

string oradb = ConfigurationManager.ConnectionStrings["dbCon"].ToString();

OracleConnection conn = new OracleConnection(oradb); // C#

if (conn.State == ConnectionState.Open)

{

}

else

{

conn.Open();

}

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

int i = 0;

int intUploadCnt = 0;

int intNotUpldCnt = 0;

int intOutOfDateRange = 0;

for (i = 0; i <= dtExcelRecords.Rows.Count - 1; i++)

{

try

{

if(dtExcelRecords.Rows[i]["Date"].ToString()!="")

{

if (Convert.ToDateTime(txtFromDate.Text)<= Convert.ToDateTime(dtExcelRecords.Rows[i]["Date"].ToString()) && Convert.ToDateTime(txtToDate.Text) >= Convert.ToDateTime(dtExcelRecords.Rows[i]["Date"].ToString()))

{

string dates = Convert.ToDateTime(dtExcelRecords.Rows[i]["Date"].ToString()).ToString("dd-MMM-yyyy");

string time = Convert.ToDateTime(dtExcelRecords.Rows[i]["Time"].ToString()).ToString("HH:mm:ss");

DateTime dt = Convert.ToDateTime(dates + " " + time);

string day = dtExcelRecords.Rows[i]["Day"].ToString();

string name = dtExcelRecords.Rows[i]["Name "].ToString();

string phonenumber = dtExcelRecords.Rows[i]["Phone Number"].ToString();

string DISTRICT = dtExcelRecords.Rows[i]["DISTRICT"].ToString();

String Query = "insert into agentcalllog(DATETIME1, MONTH, DAY, NAME, PHONE_NUMBER, DISTRICT, UPLOADEDBY,UPLOADEDDATE, UPLOADEDFILE)";

Query += "values(TO_DATE('" + dt.ToString("dd-MMM-yyyy HH:mm:ss") + "','DD-MON-YYYY HH24:MI:SS'),'" + dt.Month + "','" + day + "',";

Query += "'" + name + "','" + phonenumber + "','" + DISTRICT + "','USER', sysdate,'" + getExcelSheetName + "')";

cmd.CommandText = Query;

int rowsUpdated = cmd.ExecuteNonQuery();

intUploadCnt = intUploadCnt + 1;

}

else

{

intOutOfDateRange = intOutOfDateRange + 1;

}

catch (Exception ex)

{

intNotUpldCnt = intNotUpldCnt + 1;

}

conn.Close();

Response.Write("<script>alert('[" + intUploadCnt + "] Record uploaded successfully . [" + intOutOfDateRange + "] Record not found in selected date range in Excel File. [" + intNotUpldCnt + "] Record not uploaded. ')</script>");

chkDelReupld.Visible = false;

}

catch (Exception Ex)

{

}

// NOW TO DELETE THE EXISTING RECORDS:

void DeleteExistingRecors(string strQuery)

{

try

{

string oradb = ConfigurationManager.ConnectionStrings["dbCon"].ToString();

OracleConnection conn = new OracleConnection(oradb); // C#

if (conn.State == ConnectionState.Open)

{

}

else

{

conn.Open();

}

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = strQuery;

int rowsUpdated = cmd.ExecuteNonQuery();

conn.Close();

}

catch (Exception ex)

{

}

///////////////////////////////////////////////////////////////////////////

COMMON ERRORS MADE AND PROBLEMS

1.BEWARE THE VERSION OF THE VISUAL STUDIO IN WHICH YOU ARE BUILDING THE APPLICATION.

Some time what happens while building the application in 64 bit version and while hosting the application on iis it give the registry error for the :

Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

FIRST CHECK AND TRY THIS................................

The driver is valid when you've installed Microsoft Office 2007. So please check whether it has been installed first. If you haven't installed Microsoft Office 2007, you can try to save the database down to version 2003 and use Jet.OleDb.4.0 provider - assume you've installed Microsoft Office 2003.

But when you check for the dll/exe its present on your PC

SOLUTION:

The problems comes in the application hosting environment.i.e the iis is not configured to run a 32 bit application on 64 bit iis or vice versa.

1.GO in application pool IN INETMGR(i.e your IIS)

2.click on the application/website hosted and chose ADVANCE SETTING FROM RIGHT PANEL.

3.IN GENERAL MENU PUT Enable 32 bit applications=TRUE

HENCE YOUR PROBLEM SOVED ..............................So JUST COPY AND (!!!!UNDERSTAND.......) AND GET AMAZED ON YOUR INTELLIGENCEIF ANY ERROR OR SUGGESTION IS ,ALWAYS WELCOME....................................................................THANKS FOR BEARING..............................................................................................................................

NOTHING BUT MY LEARNING AND EXPEREINCE WITH ASP.NET/C#

Pages

Popular Posts

Sunday, 18 August 2013

EXCEL SHEET UPLOAD IN ORACLE DATABASE WITH DATE CONDITIONS

No comments :

Post a Comment

PIYUSH RANA Introooo....

Feedjit

FB

GLOBE RECORDS COUNT