Question Using CSV file as database

[FrankyT]

Hi, I'm quite a beginner and I need help with CSV database. The problem is that I need to use CSV file as database to load data from it. For example: I'll write a code to the textbox and it will compare that code in csv file and load what that code stands for to the listbox. Using streamreader if it's possible.
Thanks.:nevreness:
PS: My english is horrible

 

[FrankyT]

i'm using this code for filling datatable with data in datagridview, but it seems like after debugging and fill some value to textbox it make me a new blank table

            DataColumn[] dcs = new DataColumn[] { };

            foreach (DataGridViewColumn c in dataGridView1.Columns)
            {
                DataColumn dc = new DataColumn();
                dc.ColumnName = c.Name;
               
                tbl.Columns.Add(dc);
            }

            foreach (DataGridViewRow r in dataGridView1.Rows)
            {
                DataRow drow = tbl.NewRow();

                foreach (DataGridViewCell cell in r.Cells)
                {
                    drow[cell.OwningColumn.Name] = cell.Value;
                }

                tbl.Rows.Add(drow);

 

[wim sturkenboom]

i'm using this code for filling datatable with data in datagridview, but it seems like after debugging and fill some value to textbox it make me a new blank table

I'm not quite sure if I understand what you mean. Your code does not show anything related to typing in a textbox; it just shows some code to add data (columns and rows) to a datatable.

Is this when you try to filter?

What I notice is that you have duplicate columns. This makes me think that tbl is bound to the datagridview; if that is the case, you should not modify tbl unless you load another csv file.

The below extract from your code does duplicate the columns

foreach (DataGridViewColumn c in dataGridView1.Columns)
{
    DataColumn dc = new DataColumn();
    dc.ColumnName = c.Name;

    tbl.Columns.Add(dc);
}

The reason is that tbl already has those columns and next you add them again.

And this probably does the same for the datarows

foreach (DataGridViewRow r in dataGridView1.Rows)
{
    DataRow drow = tbl.NewRow();
 
    foreach (DataGridViewCell cell in r.Cells)
    {
        drow[cell.OwningColumn.Name] = cell.Value;
    }
 
    tbl.Rows.Add(drow);
    }

 

[FrankyT]

The fuction of last code I posted was for filling data from dataGridView to DataTable, but it doesn't work because it makes me a new blank table. What I need is to somehow modify the code to fill data to DataTable.

 

[wim sturkenboom]

It does not make a blank table; on my system it happily populates a new table. Below code is a minimally modified version of yours.

        private DataTable copy()
        {
            DataTable tbl2 = new DataTable();
            foreach (DataGridViewColumn c in dgvDatabaze.Columns)
            {
                DataColumn dc = new DataColumn();
                dc.ColumnName = c.Name;
                tbl2.Columns.Add(dc);
            }

            foreach (DataGridViewRow r in dgvDatabaze.Rows)
            {
                DataRow drow = tbl2.NewRow();

                foreach (DataGridViewCell cell in r.Cells)
                {
                    drow[cell.OwningColumn.Name] = cell.Value;
                }

                tbl2.Rows.Add(drow);
            }

            return tbl2;
        }

Note: if tbl is the table that is bound to the datagridview (and it's global), don't use that. It's the reason why my example uses tbl2.

 

[FrankyT]

after i put some value code to textbox for find it in table, it wrote me "Cannot find ckod collum".

DataView dv = new System.Data.DataView(tbl, "Ckod='" + eancodetextBox1.Text + "'", "Ckod asc", System.Data.DataViewRowState.CurrentRows);

 

[FrankyT]

This is how it looks.

var parsedData = new List<string[]>();
var sr = new StreamReader(@textBox8.Text);
string line;
while ((line = sr.ReadLine()) != null)
{
string[] row = line.Split(',');
parsedData.Add(row);
}




dataGridView1.ColumnCount = 3;
for (int i = 0; i < 3; i++)
{
var sb = new StringBuilder(parsedData[0]);


dataGridView1.Columns.Name = sb.ToString();
}


foreach (string[] row in parsedData)
{
dataGridView1.Rows.Add(row);
}
dataGridView1.Rows.Remove(dataGridView1.Rows[0]);



DataColumn[] dcs = new DataColumn[] { };
foreach (DataGridViewColumn c in dataGridView1.Columns)
{
DataColumn dc = new DataColumn();
dc.ColumnName = c.Name;


tbl2.Columns.Add(dc);
}


foreach (DataGridViewRow r in dataGridView1.Rows)
{
DataRow drow = tbl2.NewRow();


foreach (DataGridViewCell cell in r.Cells)
{
drow[cell.OwningColumn.Name] = cell.Value;
}


tbl2.Rows.Add(drow);


}

DataView dv = new System.Data.DataView(tbl2, "Ckod='" + eancodetextBox1.Text + "'", "Ckod asc", System.Data.DataViewRowState.CurrentRows);
if (dv.FindRows(eancodetextBox1.Text).Length != 0)
{
dataGridView1.DataSource = tbl2;
if (dv.FindRows(eancodetextBox1.Text).Length == 1)
{
jmenotextBox2.Text = dataGridView1.Rows[0].Cells["Nazev"].Value.ToString();
cenatextBox3.Text = dataGridView1.Rows[0].Cells["Cena"].Value.ToString();
}
else
{
jmenotextBox2.Text = "--";
cenatextBox3.Text = "--";
MessageBox.Show("Multiple matches");
}
}
else
{
dataGridView1.DataSource = tbl2;
jmenotextBox2.Text = "";
cenatextBox3.Text = "";
}

 

[wim sturkenboom]

There will be a few posts to solve the problem. The first one describes the reading of a file into a datatable; it's based on your code but stores the information in a datatable instead of a datagridview. It also contains a bugfix; you never close the file once you're finished with the reading of the file.

        /// <summary>
        /// load data from csv file into datatable
        /// </summary>
        /// <param name="strFilename">filename of csv file</param>
        /// <returns>datatable</returns>
        private DataTable loadData(string strFilename)
        {
            DataTable tbl = new DataTable();

            // read the file
            var parsedData = new List<string[]>();
            var sr = new StreamReader(strFilename);
            string line;
            while ((line = sr.ReadLine()) != null)
            {
                string[] row = line.Split(',');
                parsedData.Add(row);
            }

            // YOU NEVER CLOSED THE FILE !!
            // close the file
            sr.Close();

            // create the data columns in the datatable
            for (int cnt = 0; cnt < parsedData[0].Length; cnt++)
            {
                var sb = new StringBuilder(parsedData[0][cnt]);
                tbl.Columns.Add(sb.ToString());
            }

            // populate the rows with data
            foreach (string[] row in parsedData)
            {
                tbl.Rows.Add(row);
            }

            // the first row was the header row, remove it
            tbl.Rows.RemoveAt(0);
            return tbl;
        }

the method will take a filename as the input, read the file like you did and return a datatable.

You can call this method when you load a file (I think your right top button does that). Below is a button click event handler

        /// <summary>
        /// button click event to load a csv file
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLoaddata_Click(object sender, EventArgs e)
        {
            DataTable tblX = loadData(tbFilename.Text);
            dgvDatabaze.DataSource = tblX;
        }

tbFilename is, as far as I understand your code, textBox8.

This is not perfect yet!

In the next post, I will suggest some modifications to the above.

 

[wim sturkenboom]

The modified loaddata() method that does some error handling as well. It now takes an additional parameter that is used to store an error message. It also returns null if an error occurred so the caller can check.

        /// <summary>
        /// load data from csv file into datatable
        /// </summary>
        /// <param name="strFilename">filename of csv file</param>
        /// <param name="errmsg">return error message</param>
        /// <returns>null on error, else datatable</returns>
        private DataTable loadData(string strFilename, out string errmsg)
        {
            errmsg = "";

            // check if a filename was specified
            if (String.IsNullOrWhiteSpace(strFilename))
            {
                errmsg = "Invalid filename";
                return null;
            }

            // you can add more checks here
            // e.g. check if file exists

            // done with checking

            DataTable tbl = new DataTable();

            // read the file
            var parsedData = new List<string[]>();
            try
            {
                using (StreamReader sr = new StreamReader(strFilename))
                {
                    string line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        string[] row = line.Split(',');
                        parsedData.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                errmsg = ex.Message;
                return null;
            }

            // create the data columns in the datatable
            for (int cnt = 0; cnt < parsedData[0].Length; cnt++)
            {
                var sb = new StringBuilder(parsedData[0][cnt]);
                tbl.Columns.Add(sb.ToString());
            }

            // populate the rows with data
            foreach (string[] row in parsedData)
            {
                tbl.Rows.Add(row);
            }

            // the first row was the header row, remove it
            tbl.Rows.RemoveAt(0);
            return tbl;
        }

The first change is to check if a filename is indeed specified. If not, the error message is set and the method returns null.

Except for closing the file, it's also a good habit to get rid of the streamreader. The difference with the previous code is the embedding of the reading of the file in a using block; this takes care of the closing and disposing of the streamreader.

The last change is the embedding of the reading in a try/catch; any errors that might occur (and in the previous version would crash the application), are handled, the error message is copied and if an error occurs, null is returned.

The button click event handler now requires a small change to cater for this.

        /// <summary>
        /// button click event to load a csv file
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLoaddata_Click(object sender, EventArgs e)
        {
            // variable to hold error message
            string errormessage;

            // clear the datagrid
            // this is to make sure that on errors we don't display incorrect data
            dgvDatabaze.DataSource = null;

            try
            {
                // read the data
                DataTable tblX = loadData(tbFilename.Text, out errormessage);
                // check for error
                if (tblX == null)
                {
                    MessageBox.Show(errormessage);
                    return;
                }
                // no error, link datatable to datagridview
                dgvDatabaze.DataSource = tblX;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Unexpected error while loading data");
            }
        }

Fist step is to remove the datasource from the datagridview. Next we populate a datatable (tblX) using the loadData() method; this method now takes an additional parameter to store the error message. The return value is checked; if it's null, a popup shows the error message.

The whole block is (again) embedded in a try/catch. Reason is that exceptions can still occur in loadData() while adding the data to the table (after the actual reading).

This should give you a solid foundation to read the file and that does not give you unexpected surprises. In the next post, I will describe the filtering.

 

[wim sturkenboom]

And the filtering part. It takes into account Jim's earlier comment about the default view.

        /// <summary>
        /// text changed event in for EAN textbox
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tbEAN_TextChanged(object sender, EventArgs e)
        {
            try
            {
                // get the underlaying datasource
                DataTable tbl = (DataTable)dgvDatabaze.DataSource;
                if (tbl == null)
                {
                    MessageBox.Show("No data loaded");
                    return;
                }

                // get the default view
                DataView dv = tbl.DefaultView;
                // set filter
                dv.RowFilter = tbl.Columns[0].ColumnName + "='" + tbEAN.Text + "'";

                // find row matching the text
                // if there are rows matching the filter
                if (dv.FindRows(tbEAN.Text).Length != 0)
                {
                    // if there is only one result, we'll populate the other textboxes
                    if (dv.FindRows(tbEAN.Text).Length == 1)
                    {
                        tbJmeno.Text = dgvDatabaze.Rows[0].Cells["Nazev"].Value.ToString();
                        tbCena.Text = dgvDatabaze.Rows[0].Cells["Cena"].Value.ToString();
                    }
                    // if there are multiple rows
                    else
                    {
                        // indicate inconclusive result in textboxes
                        tbJmeno.Text = "--";
                        tbCena.Text = "--";
                        // popup a message
                        MessageBox.Show("Multiple matches");
                    }
                }
                // no match
                else
                {
                    // clear the textboxes
                    tbJmeno.Text = "";
                    tbCena.Text = "";

                    // show the complete data set instead of nothing
                    dv.RowFilter = "";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Unexpected error while filtering");
            }
        }

First of all, the complete code is (again) embedded in a try/catch and an error message is displayed when an unexpected error occurs.

The first step is to retrieve the underlaying datatable. If you forgot to load a file first, the underlaying datatable is null and the program will popup a message.
Next step is to retrieve the default view of the datatable and set a filter based on the textbox (it's now called tbEAN).

Next we find the matching rows; if there are any, we check if there is one or if there are more. If one, we'll populate the other textboxes with the data from the datagrid, else we indicate in the textboxes that the resulting data is inconclusive and display a popup.
If there are no results, we'll clear the filter of the table's defaultview so the complete dataset is still visible. In my previous code from a few days ago, the datagridview would be empty as long as there is no matching data (which might make it difficult for a user to type the EAN).

There are two changes to the button click event handler. The first one sets a sortorder for the underlaying datatable of the datagridview; this is required for the above filtering and prevents changes in the sortorder on the first filter action. The second change is that we clear the tbEAN textbox; as a result, when a new file is loaded, the textbox textchange event is triggered and the filter is removed from the underlaying datatable.

        /// <summary>
        /// button click event to load a csv file
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLoaddata_Click(object sender, EventArgs e)
        {
            // variable to hold error message
            string errormessage;

            // clear the datagrid
            dgvDatabaze.DataSource = null;

            try
            {
                // read the data
                DataTable tblX = loadData(tbFilename.Text, out errormessage);
                // check for error
                if (tblX == null)
                {
                    MessageBox.Show(errormessage);
                    return;
                }
                // no error, set sortorder on first column and link datatable to datagridview
                tblX.DefaultView.Sort = tblX.Columns[0].ColumnName;
                dgvDatabaze.DataSource = tblX;

                // clear the EAN textbox so we do not get unsuspected filtering
                tbEAN.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Unexpected error while loading data");
            }
        }

Note that in both pieces of code, there is no hardcoded columnname for filtering and sorting. Both now use the name of the first column in the datatable.

 

[wim sturkenboom]

Note that I do not want to enforce my ideas on you. The provided code gives you a working example (you more than likely have to change some names in it) that you can use as a guideline and will not be too difficult to incorporate in / replace your existing code. It also shows some, what I think are, good practices.

Hope it helps.

Question:
Out of curiosity, what is the language in the screen shots?

 

[FrankyT]

Note that I do not want to enforce my ideas on you. The provided code gives you a working example (you more than likely have to change some names in it) that you can use as a guideline and will not be too difficult to incorporate in / replace your existing code. It also shows some, what I think are, good practices.

Hope it helps.

Question:
Out of curiosity, what is the language in the screen shots?

I really appreciate your help, i'll try manage it . The text in sceen shots is in Czech .