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...

Snake Xenzia

Hello friends did you remember the GAME you play on nokia 1100 , SNAKE XENZIA .Yeah the same you are thinking ,a snake eating food and go longer and longer till it dies. Why to play it on mobile when it is available on your pc ??? It is made in .net framework 4.0 .Just install the setup and play SNAKE XENZIA . Have Fun !! DOWNLOAD IT   HERE  . Related articles Rattlesnake Dreams "Classsic Snake Game In C"? Resident Evil 6 gamescom demo takes in a giant snake battle Snakes & ladders

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)         {   ...