Code :
try
{
if (flupload.HasFile == true)
{
lblerror.Text = "";
string depositdate = "";
DataTable filedt = new DataTable();
string path = Path.GetExtension(flupload.PostedFile.FileName);
if (path == ".csv")
{
string FileName = Path.GetFileName(flupload.PostedFile.FileName);
StreamReader vdr = new StreamReader(flupload.FileContent);
string filecontent = vdr.ReadToEnd();
if (!filecontent.Contains("Deposit Number : "))
{
DisplayAJAXMessage("Invalid File as it does not contains Deposit Number !!");
}
else
{
string depositnumbersubstring = filecontent.Substring(filecontent.IndexOf("Deposit Number : ") + 17);
string depositnumber=depositnumbersubstring.Substring(0,depositnumbersubstring.IndexOf("\"")).Trim();
long temp;
bool isNumber = long.TryParse(depositnumber, out temp);
if (isNumber && depositnumber.Length == 19)
{
}
else
{
depositnumbersubstring = filecontent.Substring(filecontent.IndexOf("Deposit Number : ") + 17);
depositnumber = depositnumbersubstring.Substring(0, depositnumbersubstring.IndexOf(",")).Trim();
}
//string depositnumber = filecontent.Substring(filecontent.IndexOf("Deposit Number : ") + 17, 19); // checks for deposit number
string depositdatesubstring = filecontent.Substring(filecontent.IndexOf("Deposit Date : ") + 19);
depositdate = depositdatesubstring.Substring(0, 10).Trim();
isNumber = long.TryParse(depositnumber, out temp);
if (isNumber && depositnumber.Length==19)
{
if (depositdate != "" && ValidateDate(depositdate))
{
if (checkexistanceofdepositnumber(depositnumber) == "1") // check for deposit number validation by its presence in db
{
if (checkalreadyuseddepositnumber(depositnumber) == "0") // check for already entry there for that deposit number
{
filldatatablewithdepositnumber(depositnumber);
filecontent = filecontent.Replace("\",\"", ",");
filecontent = filecontent.Replace("\"", "");
if (filecontent.IndexOf("SrNo") != -1)
{
filecontent = filecontent.Substring(filecontent.IndexOf("SrNo"), (filecontent.Length - 1) - filecontent.IndexOf("SrNo"));
DataTable final = new DataTable();
final = convertcsvtodatatable(filecontent);
filedt = convertcsvtodatatabledemo(filecontent);
final.Columns.RemoveAt(0); // remove serial number column
filedt.Columns.RemoveAt(0);
//depositnumberdatafromdatabase
DataTable errortable = new DataTable();
string checkforerror = CompareTwoDataTable(final, depositnumberdatafromdatabase);
if (checkforerror != "")
{
lblerror.Text = checkforerror;
}
else
{
//update it in datatbase
SqlConnection connection = new SqlConnection(str);
SqlCommand cmd = new SqlCommand();
connection.Open();
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("lccode", typeof(string)));
table.Columns.Add(new DataColumn("RegistrationNumber", typeof(string)));
table.Columns.Add(new DataColumn("Name", typeof(string)));
table.Columns.Add(new DataColumn("DDNumber", typeof(string)));
table.Columns.Add(new DataColumn("DDDate", typeof(string)));
table.Columns.Add(new DataColumn("DraweeBankName", typeof(string)));
table.Columns.Add(new DataColumn("DraweeLocation", typeof(string)));
table.Columns.Add(new DataColumn("PickUpLocation", typeof(string)));
table.Columns.Add(new DataColumn("DepositNumber", typeof(string)));
table.Columns.Add(new DataColumn("DDAmount", typeof(string)));
table.Columns.Add(new DataColumn("DepositDate", typeof(string)));
foreach (DataRow row in filedt.Rows)
{
DataRow vdt = table.NewRow();
vdt["lccode"] = row[0].ToString();
vdt["RegistrationNumber"] = row[1].ToString();
vdt["Name"] = row[2].ToString();
table.Rows.Add(vdt);
}
if (table.Rows.Count <= 0)
{
lblerror.Text = "Empty file !!";
table.Clear();
}
else
{
string XMLData = "";
table.TableName = "reregistrationdepositslip";
using (StringWriter sw = new StringWriter())
{
table.WriteXml(sw, false);
XMLData = ParseXpathString(sw.ToString());
}
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "*********proc name *********";
cmd.Parameters.Add("XMLData", SqlDbType.Xml);
cmd.Parameters["XMLData"].Value = XMLData;
cmd.Parameters.Add("@RetVal", SqlDbType.VarChar, 4000);
cmd.Parameters["@RetVal"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (cmd.Parameters["@RetVal"].Value.ToString() != "Success")
{
lblerror.Text = cmd.Parameters["@RetVal"].Value.ToString();
connection.Close();
}
else
{
lblerror.Text = "Uploaded Successfully !!";
connection.Close();
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
// end of update
}
}
else
{
lblerror.Text = "Either File is tampered or invalid File";
}
}
else
{
lblerror.Text = "Deposit Number Already Submitted!!";
}
}
else
{
lblerror.Text = "Invalid Deposit Number !!";
}
}
else
{
lblerror.Text = "Invalid Deposit Date. It shold be in a format MM/DD/YYYY !!";
}
}
else
{
lblerror.Text = "Invalid Deposit Number !!";
}
}
}
}
}
catch(Exception ex)
{
lblerror.Text = ex.Message.ToString();
}
public string CompareTwoDataTable(DataTable dt1, DataTable dt2)
{
string bothequal = "";
if (dt1.Columns.Count == 9 && dt2.Columns.Count == 9)
{
dt1.Columns.RemoveAt(4); // remove time column
dt2.Columns.RemoveAt(4); // remove time column
int dt1rows = dt1.Rows.Count;
int dt2rows = dt2.Rows.Count;
int dt1columns = dt1.Columns.Count;
int dt2columns = dt2.Columns.Count;
int secondcount = 0;
int firstcount = 0;
ArrayList firstarray = new ArrayList(); // from centre and can contain duplicate
ArrayList second = new ArrayList(); // to fill table from database
ArrayList first = new ArrayList(); // to fill table from from file upload by bank
if ((dt1rows <= dt2rows) && (dt1columns == dt2columns)) // fill arraylist from file to compare
{
foreach (DataRow vdr in dt1.Rows)
{
string rowvalue = "";
for (int h = 0; h < dt1columns; h++)
{
if (h == 3)
{
int ddnumber = Convert.ToInt32(vdr[h].ToString()); // to avoid extra zeros in front
rowvalue += ddnumber.ToString() + "---";
}
else
{
rowvalue += vdr[h].ToString() + "---";
}
}
firstarray.Add(rowvalue);
}
// remove duplicates if any present from file
foreach (string aString in firstarray)
{
if (!first.Contains(aString))
{
first.Add(aString);
}
}
firstcount = first.Count;
// fill arraylist from database to compare
foreach (DataRow vdr in dt2.Rows)
{
string rowvalue = "";
for (int h = 0; h < dt1columns; h++)
{
if (h == 3)
{
int ddnumber = Convert.ToInt32(vdr[h].ToString()); // to avoid extra zeros in front
rowvalue += ddnumber.ToString() + "---";
}
else
{
rowvalue += vdr[h].ToString() + "---";
}
}
second.Add(rowvalue);
}
secondcount = second.Count;
}
if (firstcount.ToString() == "0")
{
bothequal = "Either File is tampered or invalid File or Empty File !!";
}
else if (firstarray.Count.ToString() != firstcount.ToString())
{
bothequal = "File Contains Duplicate Rows or File is tempered !!";
}
else
{
// check for content same
if (firstcount.ToString() != "0" && secondcount.ToString() != "0")
{
foreach (string ss in first)
{
if (!second.Contains(ss))
{
ArrayList errorrow = new ArrayList(); // record errored row from file as it mismatch
string errorstring = ss.Replace("---", "$");
char[] splitter = { '$' };
errorrow.AddRange(errorstring.Split(splitter));
bothequal = "Centre Code: " + errorrow[0].ToString() + " with Registration Number " + errorrow[1] + " ,Student Name : " + errorrow[2].ToString() + " ,DD No.: " + errorrow[3].ToString() + " is invalid or does not match with Deposit Number or File is tempered";
break;
}
}
}
else
{
bothequal = "Either File is tampered or invalid File";
}
}
}
else
{
bothequal = "Either File is tampered or invalid File";
}
return bothequal;
}
public DataTable convertcsvtodatatable(string csvfilename)
{
string[] Lines = csvfilename.Split(Environment.NewLine.ToCharArray());
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0) - 1; i++)
{
Fields = Lines[i].Split(new char[] { ',' });
if (Fields.Length > 5)
{
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
Row[f] = Fields[f];
dt.Rows.Add(Row);
}
}
return dt;
}
public DataTable convertcsvtodatatabledemo(string csvfilename)
{
string[] Lines = csvfilename.Split(Environment.NewLine.ToCharArray());
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
Fields = Lines[i].Split(new char[] { ',' });
if (Fields.Length > 5)
{
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
Row[f] = Fields[f];
dt.Rows.Add(Row);
}
}
return dt;
}
public string checkalreadyuseddepositnumber(string depositnumber)
{
string query = "IF EXISTS(SELECT 'x' FROM dbo.OdlStudentReRegistrationDepositSlipEntryfromHdfc WHERE DepositNumber=@depositnumber ) SELECT '1' ELSE SELECT '0'";
SqlConnection vconn = new SqlConnection(str);
vconn.Open();
SqlCommand vadap = new SqlCommand(query, vconn);
vadap.Parameters.AddWithValue("@depositnumber", depositnumber);
string returnvalue = vadap.ExecuteScalar().ToString();
vconn.Close();
return returnvalue;
}
public string checkexistanceofdepositnumber(string depositnumber)
{
string query = "IF EXISTS(SELECT 'x' FROM dbo.OdlStudentReRegistration WHERE DepositNumber=@depositnumber ) SELECT '1' ELSE SELECT '0'";
SqlConnection vconn = new SqlConnection(str);
vconn.Open();
SqlCommand vadap = new SqlCommand(query, vconn);
vadap.Parameters.AddWithValue("@depositnumber", depositnumber);
string returnvalue = vadap.ExecuteScalar().ToString();
vconn.Close();
return returnvalue;
}
try
{
if (flupload.HasFile == true)
{
lblerror.Text = "";
string depositdate = "";
DataTable filedt = new DataTable();
string path = Path.GetExtension(flupload.PostedFile.FileName);
if (path == ".csv")
{
string FileName = Path.GetFileName(flupload.PostedFile.FileName);
StreamReader vdr = new StreamReader(flupload.FileContent);
string filecontent = vdr.ReadToEnd();
if (!filecontent.Contains("Deposit Number : "))
{
DisplayAJAXMessage("Invalid File as it does not contains Deposit Number !!");
}
else
{
string depositnumbersubstring = filecontent.Substring(filecontent.IndexOf("Deposit Number : ") + 17);
string depositnumber=depositnumbersubstring.Substring(0,depositnumbersubstring.IndexOf("\"")).Trim();
long temp;
bool isNumber = long.TryParse(depositnumber, out temp);
if (isNumber && depositnumber.Length == 19)
{
}
else
{
depositnumbersubstring = filecontent.Substring(filecontent.IndexOf("Deposit Number : ") + 17);
depositnumber = depositnumbersubstring.Substring(0, depositnumbersubstring.IndexOf(",")).Trim();
}
//string depositnumber = filecontent.Substring(filecontent.IndexOf("Deposit Number : ") + 17, 19); // checks for deposit number
string depositdatesubstring = filecontent.Substring(filecontent.IndexOf("Deposit Date : ") + 19);
depositdate = depositdatesubstring.Substring(0, 10).Trim();
isNumber = long.TryParse(depositnumber, out temp);
if (isNumber && depositnumber.Length==19)
{
if (depositdate != "" && ValidateDate(depositdate))
{
if (checkexistanceofdepositnumber(depositnumber) == "1") // check for deposit number validation by its presence in db
{
if (checkalreadyuseddepositnumber(depositnumber) == "0") // check for already entry there for that deposit number
{
filldatatablewithdepositnumber(depositnumber);
filecontent = filecontent.Replace("\",\"", ",");
filecontent = filecontent.Replace("\"", "");
if (filecontent.IndexOf("SrNo") != -1)
{
filecontent = filecontent.Substring(filecontent.IndexOf("SrNo"), (filecontent.Length - 1) - filecontent.IndexOf("SrNo"));
DataTable final = new DataTable();
final = convertcsvtodatatable(filecontent);
filedt = convertcsvtodatatabledemo(filecontent);
final.Columns.RemoveAt(0); // remove serial number column
filedt.Columns.RemoveAt(0);
//depositnumberdatafromdatabase
DataTable errortable = new DataTable();
string checkforerror = CompareTwoDataTable(final, depositnumberdatafromdatabase);
if (checkforerror != "")
{
lblerror.Text = checkforerror;
}
else
{
//update it in datatbase
SqlConnection connection = new SqlConnection(str);
SqlCommand cmd = new SqlCommand();
connection.Open();
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("lccode", typeof(string)));
table.Columns.Add(new DataColumn("RegistrationNumber", typeof(string)));
table.Columns.Add(new DataColumn("Name", typeof(string)));
table.Columns.Add(new DataColumn("DDNumber", typeof(string)));
table.Columns.Add(new DataColumn("DDDate", typeof(string)));
table.Columns.Add(new DataColumn("DraweeBankName", typeof(string)));
table.Columns.Add(new DataColumn("DraweeLocation", typeof(string)));
table.Columns.Add(new DataColumn("PickUpLocation", typeof(string)));
table.Columns.Add(new DataColumn("DepositNumber", typeof(string)));
table.Columns.Add(new DataColumn("DDAmount", typeof(string)));
table.Columns.Add(new DataColumn("DepositDate", typeof(string)));
foreach (DataRow row in filedt.Rows)
{
DataRow vdt = table.NewRow();
vdt["lccode"] = row[0].ToString();
vdt["RegistrationNumber"] = row[1].ToString();
vdt["Name"] = row[2].ToString();
table.Rows.Add(vdt);
}
if (table.Rows.Count <= 0)
{
lblerror.Text = "Empty file !!";
table.Clear();
}
else
{
string XMLData = "";
table.TableName = "reregistrationdepositslip";
using (StringWriter sw = new StringWriter())
{
table.WriteXml(sw, false);
XMLData = ParseXpathString(sw.ToString());
}
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "*********proc name *********";
cmd.Parameters.Add("XMLData", SqlDbType.Xml);
cmd.Parameters["XMLData"].Value = XMLData;
cmd.Parameters.Add("@RetVal", SqlDbType.VarChar, 4000);
cmd.Parameters["@RetVal"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (cmd.Parameters["@RetVal"].Value.ToString() != "Success")
{
lblerror.Text = cmd.Parameters["@RetVal"].Value.ToString();
connection.Close();
}
else
{
lblerror.Text = "Uploaded Successfully !!";
connection.Close();
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
// end of update
}
}
else
{
lblerror.Text = "Either File is tampered or invalid File";
}
}
else
{
lblerror.Text = "Deposit Number Already Submitted!!";
}
}
else
{
lblerror.Text = "Invalid Deposit Number !!";
}
}
else
{
lblerror.Text = "Invalid Deposit Date. It shold be in a format MM/DD/YYYY !!";
}
}
else
{
lblerror.Text = "Invalid Deposit Number !!";
}
}
}
}
}
catch(Exception ex)
{
lblerror.Text = ex.Message.ToString();
}
public string CompareTwoDataTable(DataTable dt1, DataTable dt2)
{
string bothequal = "";
if (dt1.Columns.Count == 9 && dt2.Columns.Count == 9)
{
dt1.Columns.RemoveAt(4); // remove time column
dt2.Columns.RemoveAt(4); // remove time column
int dt1rows = dt1.Rows.Count;
int dt2rows = dt2.Rows.Count;
int dt1columns = dt1.Columns.Count;
int dt2columns = dt2.Columns.Count;
int secondcount = 0;
int firstcount = 0;
ArrayList firstarray = new ArrayList(); // from centre and can contain duplicate
ArrayList second = new ArrayList(); // to fill table from database
ArrayList first = new ArrayList(); // to fill table from from file upload by bank
if ((dt1rows <= dt2rows) && (dt1columns == dt2columns)) // fill arraylist from file to compare
{
foreach (DataRow vdr in dt1.Rows)
{
string rowvalue = "";
for (int h = 0; h < dt1columns; h++)
{
if (h == 3)
{
int ddnumber = Convert.ToInt32(vdr[h].ToString()); // to avoid extra zeros in front
rowvalue += ddnumber.ToString() + "---";
}
else
{
rowvalue += vdr[h].ToString() + "---";
}
}
firstarray.Add(rowvalue);
}
// remove duplicates if any present from file
foreach (string aString in firstarray)
{
if (!first.Contains(aString))
{
first.Add(aString);
}
}
firstcount = first.Count;
// fill arraylist from database to compare
foreach (DataRow vdr in dt2.Rows)
{
string rowvalue = "";
for (int h = 0; h < dt1columns; h++)
{
if (h == 3)
{
int ddnumber = Convert.ToInt32(vdr[h].ToString()); // to avoid extra zeros in front
rowvalue += ddnumber.ToString() + "---";
}
else
{
rowvalue += vdr[h].ToString() + "---";
}
}
second.Add(rowvalue);
}
secondcount = second.Count;
}
if (firstcount.ToString() == "0")
{
bothequal = "Either File is tampered or invalid File or Empty File !!";
}
else if (firstarray.Count.ToString() != firstcount.ToString())
{
bothequal = "File Contains Duplicate Rows or File is tempered !!";
}
else
{
// check for content same
if (firstcount.ToString() != "0" && secondcount.ToString() != "0")
{
foreach (string ss in first)
{
if (!second.Contains(ss))
{
ArrayList errorrow = new ArrayList(); // record errored row from file as it mismatch
string errorstring = ss.Replace("---", "$");
char[] splitter = { '$' };
errorrow.AddRange(errorstring.Split(splitter));
bothequal = "Centre Code: " + errorrow[0].ToString() + " with Registration Number " + errorrow[1] + " ,Student Name : " + errorrow[2].ToString() + " ,DD No.: " + errorrow[3].ToString() + " is invalid or does not match with Deposit Number or File is tempered";
break;
}
}
}
else
{
bothequal = "Either File is tampered or invalid File";
}
}
}
else
{
bothequal = "Either File is tampered or invalid File";
}
return bothequal;
}
public DataTable convertcsvtodatatable(string csvfilename)
{
string[] Lines = csvfilename.Split(Environment.NewLine.ToCharArray());
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0) - 1; i++)
{
Fields = Lines[i].Split(new char[] { ',' });
if (Fields.Length > 5)
{
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
Row[f] = Fields[f];
dt.Rows.Add(Row);
}
}
return dt;
}
public DataTable convertcsvtodatatabledemo(string csvfilename)
{
string[] Lines = csvfilename.Split(Environment.NewLine.ToCharArray());
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
Fields = Lines[i].Split(new char[] { ',' });
if (Fields.Length > 5)
{
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
Row[f] = Fields[f];
dt.Rows.Add(Row);
}
}
return dt;
}
public string checkalreadyuseddepositnumber(string depositnumber)
{
string query = "IF EXISTS(SELECT 'x' FROM dbo.OdlStudentReRegistrationDepositSlipEntryfromHdfc WHERE DepositNumber=@depositnumber ) SELECT '1' ELSE SELECT '0'";
SqlConnection vconn = new SqlConnection(str);
vconn.Open();
SqlCommand vadap = new SqlCommand(query, vconn);
vadap.Parameters.AddWithValue("@depositnumber", depositnumber);
string returnvalue = vadap.ExecuteScalar().ToString();
vconn.Close();
return returnvalue;
}
public string checkexistanceofdepositnumber(string depositnumber)
{
string query = "IF EXISTS(SELECT 'x' FROM dbo.OdlStudentReRegistration WHERE DepositNumber=@depositnumber ) SELECT '1' ELSE SELECT '0'";
SqlConnection vconn = new SqlConnection(str);
vconn.Open();
SqlCommand vadap = new SqlCommand(query, vconn);
vadap.Parameters.AddWithValue("@depositnumber", depositnumber);
string returnvalue = vadap.ExecuteScalar().ToString();
vconn.Close();
return returnvalue;
}
Comments
Post a Comment