Question Using CSV file as database

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
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
 

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
I strongly suggest that you do NOT re-invent the wheel. You can look at the TextFieldParser class or at the multitude of dedicated csv readers in C# on the web. CSV is not a matter of simply using commas as a separator.

You can use the below sample csv data to test your functionality. It has two records with all he goodies of csv (including a record with a field that contains two lines)

C#:
record 1,"a,b","c""d","line1
line 2"
record 2,x\y\z,x/y/z,empty ;)

My approach to your problem would be:
Read file into datatable
Do your search using DataRowCollection.Find Method (Object) (System.Data) or DataRowCollection.Find Method (Object[]) (System.Data).
 

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
i was able to upload the data from CSV file to datagridview, but i have no idea how to filter those data back to textbox.
This is a code:
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][i]);
                
                dataGridView1.Columns[i].Name = sb.ToString();
            }


            foreach (string[] row in parsedData)
            {
                dataGridView1.Rows.Add(row);
            }
            dataGridView1.Rows.Remove(dataGridView1.Rows[0]);
 
Last edited by a moderator:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,151
Location
Sydney, Australia
Programming Experience
10+
i have no idea how to filter those data back to textbox.
What does that actually mean? Please post a FULL and CLEAR explanation of the issue. Assume that too many words is better than not enough. Do you mean that, when the user selects a row in the grid, you want to display the fields of that row in a number of TextBoxes? If so then say that. We can't read your mind so can only go by what you post. If you don't post clearly then we have to guess and that means we might guess wrong.
 

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
Sorry,
i'll give an example: In CSV file i have 3 collums. 1 stand for eancode, 1 for name and the last for price. So I i have 3 textboxes with the same meanings. The code i need is how to input to one of those textboxes the information (by keyboard) and it'll automaticly filter in datagridview and show me the row and fill others texboxes.
Like if I fill nametextbox: Banana, it'll automaticly show in eancodetexbox: 3662226262 and in pricetextbox: ?1.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,151
Location
Sydney, Australia
Programming Experience
10+
Sorry,
i'll give an example: In CSV file i have 3 collums. 1 stand for eancode, 1 for name and the last for price. So I i have 3 textboxes with the same meanings. The code i need is how to input to one of those textboxes the information (by keyboard) and it'll automaticly filter in datagridview and show me the row and fill others texboxes.
Like if I fill nametextbox: Banana, it'll automaticly show in eancodetexbox: 3662226262 and in pricetextbox: ?1.
That's not even filtering. You haven't really thought this through or else you're still not explaining it very well. For a start, what if the value you enter matches more than row? Do you want partial matches or just full matches?
 

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
Maybe i'm bad with english + beginner at C# and i don't really understand what do you mean by partial matches or just full matches. So i added images what i really need. (second one is result, images was modified in paint app)
First procedure.jpgResult.jpg
 
Last edited:

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
For this to work, you need to use a datatable to hold your data. The below declares a datatable that will hold your data; it is available to every method in the class.
        public partial class Form1 : Form
    {
        DataTable tbl = new DataTable();
...
...


This simulates your csv reading; you need to populate the datatable and not the datagridview. Next the datatable is set to be the datasource of the datagridview. I've just hard coded some values in the datatable. At the end of the Form_Load, the table is bound to the datagridview.
        private void FormMain_Load(object sender, EventArgs e)
        {
                        tbl.Columns.Add("ckod");
            tbl.Columns.Add("Nazev");
            tbl.Columns.Add("Cena");


            tbl.Rows.Add("1234", "Mattoni", "9.90");
            tbl.Rows.Add("1235", "HousKa", "4.90");
            tbl.Rows.Add("9876", "Vicezmne", "5.50");
            tbl.Rows.Add("9875", "Vracene", "-27.00");
            tbl.Rows.Add("9875", "Poep", "27.00");

            dgvDatabaze.DataSource = tbl;

}



The below is an event handler; every time the text in the textbox changes, it will be called. It sets up a filter on the datatable (not on the datagridview).

If the filter has a result, it will show the matching rows by using the dataview as a datasource for the datagridview. Only if there is one row, it will populate the other textboxes with the corresponding data, else it will display '--' and popup a warning. I've implemented the latter as it's very easy to make mistakes when creating csv files and ending up with duplicate ean codes.

If there is no result, it will use the original datatable as the datasource of the datagridview.
                private void tbCarovi_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new System.Data.DataView(tbl, "ckod='" + tbCarovi.Text + "'", "ckod asc", System.Data.DataViewRowState.CurrentRows);
            if (dv.FindRows(tbCarovi.Text).Length != 0)
            {
                dgvDatabaze.DataSource = dv;
                if (dv.FindRows(tbCarovi.Text).Length == 1)
                {
                    tbJmeno.Text = dgvDatabaze.Rows[0].Cells["Nazev"].Value.ToString();
                    tbCena.Text = dgvDatabaze.Rows[0].Cells["Cena"].Value.ToString();
                }
                else
                {
                    tbJmeno.Text = "--";
                    tbCena.Text = "--";
                    MessageBox.Show("Multiple matches");
                }
            }
            else
            {
                dgvDatabaze.DataSource = tbl;
                tbJmeno.Text = "";
                tbCena.Text = "";
            }
        }


I hope this gets you on the way.

Note: please try to give your controls sensible names from the start (seeing that you have 'DatagridView1') . DatagridView1 or TextBox1 or Button1 works if there is only one, but if there is a multitude, it will become confusing.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,151
Location
Sydney, Australia
Programming Experience
10+
The below is an event handler; every time the text in the textbox changes, it will be called. It sets up a filter on the datatable (not on the datagridview).

If the filter has a result, it will show the matching rows by using the dataview as a datasource for the datagridview. Only if there is one row, it will populate the other textboxes with the corresponding data, else it will display '--' and popup a warning. I've implemented the latter as it's very easy to make mistakes when creating csv files and ending up with duplicate ean codes.

If there is no result, it will use the original datatable as the datasource of the datagridview.
                private void tbCarovi_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new System.Data.DataView(tbl, "ckod='" + tbCarovi.Text + "'", "ckod asc", System.Data.DataViewRowState.CurrentRows);
            if (dv.FindRows(tbCarovi.Text).Length != 0)
            {
                dgvDatabaze.DataSource = dv;
                if (dv.FindRows(tbCarovi.Text).Length == 1)
                {
                    tbJmeno.Text = dgvDatabaze.Rows[0].Cells["Nazev"].Value.ToString();
                    tbCena.Text = dgvDatabaze.Rows[0].Cells["Cena"].Value.ToString();
                }
                else
                {
                    tbJmeno.Text = "--";
                    tbCena.Text = "--";
                    MessageBox.Show("Multiple matches");
                }
            }
            else
            {
                dgvDatabaze.DataSource = tbl;
                tbJmeno.Text = "";
                tbCena.Text = "";
            }
        }


I hope this gets you on the way.

Note: please try to give your controls sensible names from the start (seeing that you have 'DatagridView1') . DatagridView1 or TextBox1 or Button1 works if there is only one, but if there is a multitude, it will become confusing.
It's not a good idea to keep creating new DataViews and rebinding all the time. Every DataTable is already associated with a DataView which can be accessed from its DefaultView property. In fact, when you bind a DataTable, it's the DeafultView that actually supplies the data. That's how you can sort a DataGridView bound to a DataTable, even though the DataTable itself can't be sorted. To filter the data in the grid you should simply set the RowFilter property of that DefaultView.
 

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
It's not a good idea to keep creating new DataViews and rebinding all the time. Every DataTable is already associated with a DataView which can be accessed from its DefaultView property. In fact, when you bind a DataTable, it's the DeafultView that actually supplies the data. That's how you can sort a DataGridView bound to a DataTable, even though the DataTable itself can't be sorted. To filter the data in the grid you should simply set the RowFilter property of that DefaultView.
Thanks for that information; I forgot about the default view. Can you elaborate what is bad about rebinding? Slow? Memory usage?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,151
Location
Sydney, Australia
Programming Experience
10+
Can you elaborate what is bad about rebinding? Slow? Memory usage?
It removes everything from the grid and then puts new stuff in. Sorting and filtering in place keeps the same rows in the grid the whole time.
 

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
For this to work, you need to use a datatable to hold your data. The below declares a datatable that will hold your data; it is available to every method in the class.
        public partial class Form1 : Form
    {
        DataTable tbl = new DataTable();
...
...


This simulates your csv reading; you need to populate the datatable and not the datagridview. Next the datatable is set to be the datasource of the datagridview. I've just hard coded some values in the datatable. At the end of the Form_Load, the table is bound to the datagridview.
        private void FormMain_Load(object sender, EventArgs e)
        {
                        tbl.Columns.Add("ckod");
            tbl.Columns.Add("Nazev");
            tbl.Columns.Add("Cena");


            tbl.Rows.Add("1234", "Mattoni", "9.90");
            tbl.Rows.Add("1235", "HousKa", "4.90");
            tbl.Rows.Add("9876", "Vicezmne", "5.50");
            tbl.Rows.Add("9875", "Vracene", "-27.00");
            tbl.Rows.Add("9875", "Poep", "27.00");

            dgvDatabaze.DataSource = tbl;

}

Really thanks for your reply.
If I understand, well the data in the DataTable are not directly imported from CSV file, but I have to write code collums.add and row.add? If it is so, so every time I want to add new data to CSV file I'll have to rewrite the code?
I'm sorry if I got it wrong, but what I really need is that when I want to add new data to a CSV file so I would not have to modify the code again.
 

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
If it is so, so every time I want to add new data to CSV file I'll have to rewrite the code?
You've got it wrong ;) Only if the format of the CSV changes (number of columns), you'll have to rewrite the code.

The adding of the rows in my example was just an example to show how you can add rows to the datatable; you can use the method that you already created; only thing is that you'll have to place the records in a datatable and not directly in the grid.

But have a look at Jim's comment to find a better way I haven't had time to check that out.
 
Last edited:

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
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);

blank.jpg
 
Last edited by a moderator:

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
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);
    }

 
Last edited:

FrankyT

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
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

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
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

Member
Joined
Oct 1, 2014
Messages
10
Programming Experience
1-3
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);
 
Last edited:
Top Bottom