/// <summary>
/// insert new row in the table
/// </summary>
/// <param name="businessObject">business object</param>
/// <returns>true of successfully insert</returns>
public Int32 Insert(clsShazam businessObject)
{
SqlCommand sqlCommand = new SqlCommand();
try
{
MainConnection.Close();
sqlCommand.Dispose();
sqlCommand = new SqlCommand();
sqlCommand.CommandText = "dbo.[sp_Digital_Shazam_Insert]";
sqlCommand.CommandType = CommandType.StoredProcedure;
// Use connection object of base class
sqlCommand.Connection = MainConnection;
//sqlCommand.CommandTimeout = 60;
sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID));
sqlCommand.Parameters.Add(new SqlParameter("@Week", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Week));
sqlCommand.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Year));
sqlCommand.Parameters.Add(new SqlParameter("@Position", SqlDbType.NVarChar, 400, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Position));
sqlCommand.Parameters.Add(new SqlParameter("@Artist", SqlDbType.NVarChar, 400, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Artist));
sqlCommand.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar, 400, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Title));
MainConnection.Open();
sqlCommand.ExecuteNonQuery();
businessObject.ID = (int)sqlCommand.Parameters["@ID"].Value;
return(businessObject.ID);
}
catch (Exception ex)
{
businessObject.ID = -1;
return(-1);
//throw new Exception("clsSongs::Insert::Error occured.", ex);
}
finally
{
MainConnection.Close();
sqlCommand.Dispose();
}
}
protected void btn_Click(object sender, EventArgs e)
{
CultureInfo cinfo = new CultureInfo("it-IT");
clsShazamFactory fac = new clsShazamFactory();
clsShazam Shazam = new clsShazam();
if (!String.IsNullOrEmpty(hfname.Value))
{
if (IsEnglish(hfname.Value))
{
if (filename.HasFile)
{
string FileName = Path.GetFileName(filename.PostedFile.FileName);
string Extension = Path.GetExtension(filename.PostedFile.FileName);
if (Extension == ".xls" || Extension == ".xlsx")
{
try
{
string FolderPath = Server.MapPath("~/ImportFiles");
if (!Directory.Exists(FolderPath))
{
Directory.CreateDirectory(FolderPath);
}
Guid abc = Guid.NewGuid();
string FilePath = FolderPath + "/" + abc.ToString();
if (System.IO.File.Exists(FilePath))
{
System.IO.File.Delete(FilePath);
}
filename.SaveAs(FilePath);
int Week = Convert.ToInt32(txtWeek.Text);
int Year = Convert.ToInt32(txtYear.Text);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=" + "No" + ";IMEX=2\"";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
if (Extension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=" + "No" + ";IMEX=2\"";
}
else if (Extension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=" + "No" + ";IMEX=2\"";
}
excelConnection = new OleDbConnection(excelConnectionString);
if (excelConnection.State == ConnectionState.Closed)
{
excelConnection.Open();
}
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds = getExcelRecords(excelConnectionString, excelConnection);
int totalcolumns = ds.Tables[0].Columns.Count;
if (totalcolumns > 0)
{
dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
try
{
Shazam.Week = Week;
Shazam.Year = Year;
Shazam.Position = string.IsNullOrEmpty(dr[0].ToString()) ? null : dr[0].ToString();
Shazam.Artist = string.IsNullOrEmpty(dr[1].ToString()) ? null : dr[1].ToString();
Shazam.Title = string.IsNullOrEmpty(dr[2].ToString()) ? null : dr[2].ToString();
if (!string.IsNullOrEmpty(Shazam.Artist) && !string.IsNullOrEmpty(Shazam.Title))
{
int ID = fac.Insert(Shazam);
}
}
catch (Exception ex)
{
}
}
Response.Redirect("Songs.aspx");
}
}
catch (Exception ex)
{
pnlError.Visible = true;
lblError.Text = ex.Message.ToString();// "Error occured! Please try again later.";
}
}
else
{
pnlError.Visible = true;
lblError.Text = "Upload only excel files.";
}
}
}
else
{
pnlError.Visible = true;
lblError.Text = "File name contains illegal characters";
}
}
else
{
pnlError.Visible = true;
lblError.Text = "Please select a file first";
}
}