This is the code for excel sheet uploadt to datbase and updated to datbase...
protected void cmdexcelsheetupload_Click(object sender, EventArgs e)
{
{
{
string strExcelPath = "";
AllUtilities.ImageUtilities oIU = new AllUtilities.ImageUtilities();
try
{
if (txtFile.HasFile)
strExcelPath = txtFile.PostedFile.FileName.Trim();
string strNewFilename = strExcelPath.ToString();
strNewFilename = oIU.ExtractFilename(strNewFilename);
string strSaveLocation = Server.MapPath("../Excelsheet/") + strNewFilename;
txtFile.SaveAs(strSaveLocation);
string path = Server.MapPath("../Excelsheet/3ymmember.xls");
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes"";";
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select Name, Address,ContactNo,Email,MemDetail,Reg_No,startDate,endDate FROM [3ymmember$]", connection);
connection.Open();
if (RadioButtonList1.SelectedIndex == 0)
{
using (DbDataReader dr = command.ExecuteReader())
{
string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "CommiteeMemberFull";
bulkCopy.WriteToServer(dr);
Label1.Text = "Users has been added successfully.";
}
}
}
else if (RadioButtonList1.SelectedIndex == 1)
{
OleDbDataReader odr = command.ExecuteReader();
string Name = "";
string Address = "";
string ContactNo = "";
string Email = "";
string MemDetail = "";
string Reg_No = "";
string startDate = "";
string endDate = "";
while (odr.Read())
{
Name = valid(odr, 0);
Address = valid(odr, 1);
ContactNo = valid(odr, 2);
Email = valid(odr, 3);
MemDetail = valid(odr, 4);
Reg_No = valid(odr, 5);
insertdataintosql(Name, Address, ContactNo, Email, MemDetail, Reg_No,startDate, endDate);
}
connection.Close();
}
else
{
Label1.Text = "Please Select One Opration!";
}
}
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
Label1.Visible = true;
}
}
}
}
This is the method.... for insertion...
public void insertdataintosql(string Name, string Address, string ContactNo, string Email, string MemDetail, string Reg_No, string startDate, string endDate)
{
string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(sqlConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Update CommiteeMemberFull SET Name=@Name, Address=@Address,ContactNo=@ContactNo,Email=@Email,MemDetail=MemDetail ,startDate=startDate,endDate=endDate where Reg_No=@Reg_No";
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address;
cmd.Parameters.Add("@ContactNo", SqlDbType.NVarChar).Value = ContactNo;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Email;
cmd.Parameters.Add("@MemDetail", SqlDbType.NVarChar).Value = MemDetail;
cmd.Parameters.Add("@Reg_No", SqlDbType.NVarChar).Value = Reg_No;
cmd.Parameters.Add("@startDate", SqlDbType.NVarChar).Value = startDate;
cmd.Parameters.Add("@endDate", SqlDbType.NVarChar).Value = endDate;
cmd.CommandType = CommandType.Text;
connection.Open();
cmd.Connection = connection;
cmd.ExecuteNonQuery();
connection.Close();
Label1.Text = "Excel Sheet Updated successfuly!";
}
protected string valid(OleDbDataReader myreader, int stval)
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}<pre>
<pre lang="cs">private string ReturnExtension(string fileExtension)
{
switch (fileExtension)
{
case ".htm":
case ".html":
case ".log":
return "text/HTML";
case ".txt":
return "text/plain";
case ".doc":
return "application/ms-word";
case ".tiff":
case ".tif":
return "image/tiff";
case ".asf":
return "video/x-ms-asf";
case ".avi":
return "video/avi";
case ".zip":
return "application/zip";
case ".xls":
case ".csv":
return "application/vnd.ms-excel";
case ".gif":
return "image/gif";
case ".jpg":
case "jpeg":
return "image/jpeg";
case ".bmp":
return "image/bmp";
case ".wav":
return "audio/wav";
case ".mp3":
return "audio/mpeg3";
case ".mpg":
case "mpeg":
return "video/mpeg";
case ".rtf":
return "application/rtf";
case ".asp":
return "text/asp";
case ".pdf":
return "application/pdf";
case ".fdf":
return "application/vnd.fdf";
case ".ppt":
return "application/mspowerpoint";
case ".dwg":
return "image/vnd.dwg";
case ".msg":
return "application/msoutlook";
case ".xml":
case ".sdxl":
return "application/xml";
case ".xdp":
return "application/vnd.adobe.xdp+xml";
default:
return "application/octet-stream";
}
}