protected void btnUpload_Click(object sender, EventArgs e)
{
int i = 0;
if (CSVUpload.HasFile)
{
string path = "";
path = Server.MapPath("File_Upload");
path = path + "\\" + CSVUpload.FileName;
if (File.Exists(path))
{
File.Delete(path);
CSVUpload.SaveAs(path);
}
else
{
CSVUpload.SaveAs(path);
}
StreamReader sr = new StreamReader(path);
string line = sr.ReadLine();
do
{
line = sr.ReadLine();
string mno = "", fnm = "", lnm = "", pin = "", gr = "", ct = "", defltgrp = "", prefix = "";
i++;
if (i == 1)
{
string sql = "select usrUserId, usrFirstName,usrCityId from UserMaster where usrMobileNo='" + Convert.ToString(Session["MobileNumber"]) + "'";
DataSet ds = new DataSet();
ds = cc.ExecuteDataset(sql);
string userId;
foreach (DataRow dr1 in ds.Tables[0].Rows)
{
userId = Convert.ToString(dr1["usrUserId"]);
}
}
if (line != null)
{
string[] ArrLine = line.Split(',');
mno = ArrLine[0].ToString();
fnm = ArrLine[1].ToString();
lnm = ArrLine[2].ToString();
pin = ArrLine[3].ToString();
gr = ArrLine[4].ToString();
ct = ArrLine[5].ToString();
prefix = ArrLine[6].ToString();
RegisterNewExcel1(mno, fnm, lnm, pin, gr, ct, prefix, defltgrp);
}
} while (line != null);
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
var strFPath = String.Empty;
var strExt = String.Empty;
if (CSVUpload.HasFile)
{
strFPath = Server.MapPath("~/Import/") + CSVUpload.FileName;
strExt = Path.GetExtension(CSVUpload.FileName);
CSVUpload.SaveAs(strFPath);
}
var lstColumns = new List <string>();
var strTableName = drpSystemEntity.SelectedValue; // table name
if (!string.IsNullOrEmpty(strFPath) && strExt.ToLower() == ".csv")
{
var sReader = new StreamReader(strFPath); // read csv file
string line = null;
var isHeader = true;
var lstErrors = new List <string>();
while ((line = sReader.ReadLine()) != null)
{
if (isHeader) // check for header column
{
lstColumns = line.ToLower().Split(new char[] { ',' }).ToList(); // parse the columns
if (string.IsNullOrEmpty(lstColumns[lstColumns.Count - 1])) // check whether last column is null/empty
{
lstColumns.RemoveAt(lstColumns.Count - 1);
}
isHeader = false;
}
else
{
var strSQL = String.Empty; // sql string
var lstValues = line.Split(new char[] { ',' }).ToList(); // parse the values
if (lstValues.Count > 0)
{
for (var i = 0; i < lstColumns.Count; i++) // to add parameters to the sql string with values
{
if (i == 0) // first column
{
strSQL = "@" + lstColumns[i] + " = '" + lstValues[i] + "'";
}
else if (i < lstValues.Count) // condition, if column count exceeding value count
{
strSQL += ", @" + lstColumns[i] + " = '" + lstValues[i] + "'";
}
}
//if (!lstColumns.Contains(strTableName.ToLower() + "id")) // check if primary column present or not. if not add it to query
// strSQL = "@" + strTableName.ToLower() + "id = NULL, " + strSQL;
strSQL += ", @auditid = " + SessionVariables.RequestProfile.AuditId; // add aduit id
strSQL = strTableName + "Insert " + strSQL; // add procedure name to the sql query
//Response.Write(strSQL + "</br>");
try
{
DBDML.RunSQL(strTableName + ".Insert", strSQL, "Default"); // execute sql query
}
catch (Exception ex)
{
lstErrors.Add("SQL: " + strSQL + ", Exception: " + ex.Message);
}
}
}
}
sReader.Close();
foreach (var strError in lstErrors)
{
Response.Write(strError + "<br/>");
}
}
else
{
throw new Exception("InValid file format (only .csv accepted)");
}
}