Skip to main content

.net to compare csv file with data from database(sql server) .net

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;

        }

Comments

Popular posts from this blog

show image in asp.net form from database

In order to show image from database to webform first add an image control in webpage.   <asp:Image ID="imgStudentImage" runat="server" Height="157px" Width="160px" /> Then add new webform to read image from database for example displayimage.aspx and in code behind model write the following code. public string str = //your string     public SqlConnection conn;     public SqlCommand sql = new SqlCommand();     public SqlDataReader dr;     public string strSQL;     protected void Page_Load(object sender, EventArgs e)     {         conn = new SqlConnection(str);         conn.Open();         long id = Convert.ToInt64(Request.QueryString["mId"]);         strSQL = "";         strSQL = strSQL + " SELECT snap from S...

Export Doc,Access,Image,CSV,Excel,Pdf,XML,HTML,Text,Print of Gridview in Asp.net

First import itextsharp.dll in the solution and use three namespces (basically for pdf) using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html.simpleparser;          Then Use the following code :- on each button  click  protected void Page_Load(object sender, EventArgs e)         {             if (!Page.IsPostBack)             {                 BindGridDetails(GridView1);             }         }                 protected DataTable BindGridDetails(GridView GridView1)         {   ...

Advent of code 2022 day 22 part 1

  function main(input, input1) {     let grid = input.split( '\n' );     grid.shift();     // only in big input     for ( let i = 0 ; i < 100 ; i++) {         grid[i] = '                                                  ' + grid[i];     }     //console.log(grid[100])     grid = grid.map(x => x.split( '' ))     // find first left top allowed     let temppos = - 1 ;     grid[ 0 ].forEach((element, i) => {         if (temppos == - 1 && element == '.' ) {             temppos = i;         }     });     let initpos = new pos(temppos, 0 )     // console.log(initpos)     // make grid equal shape     let...