asked on

How can i read Griddata in a Method Defined NOT on the Form Containing the Grid

I have a c# application with a grid defined in a form in the User Interface Layer.

I need to loop through records in the grid in the Data Access Layer and save same to the database

The Gridname is dgvDetailsTable and the .Rows is flagged

See my Full Script below

public void CreateEducationForm(List<Tuple<string, string, string, string, string>> newTuples)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {
                using (var command = new NpgsqlCommand("public.speducation_writemasterdetail", conn))
                    //using (var command = new NpgsqlCommand("public.speducation_insert", conn))
                {

                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(value: new NpgsqlParameter("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn01 });
                    command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = GridVariables.GridColumn07 });
                    command.Parameters.Add(new NpgsqlParameter("empidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = ((int)GridVariables.GridColumn08) });
                    command.Parameters.Add(new NpgsqlParameter("empeduidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.InputOutput, Value = ((int)GridVariables.GridColumn09) });

                    NpgsqlParameter schoolname = new NpgsqlParameter("schoolnamex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(schoolname);
                    NpgsqlParameter startdate = new NpgsqlParameter("startdatex", NpgsqlTypes.NpgsqlDbType.Date) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(startdate);
                    NpgsqlParameter enddate = new NpgsqlParameter("enddatex", NpgsqlTypes.NpgsqlDbType.Date) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(enddate);
                    NpgsqlParameter yearsexp = new NpgsqlParameter("yearsexpx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(yearsexp);
                    NpgsqlParameter degreecode = new NpgsqlParameter("degreecodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(degreecode);

                        //foreach (Tuple<string, string, string, string, string> tuple in newTuples)
                    foreach (DataGridViewRow row in dgvDetailsTable.Rows)
                    { 
                        schoolname.Value = GridVariables.GridColumn02;
                        startdate.Value = Convert.ToDateTime(GridVariables.GridColumn03);
                        enddate.Value = Convert.ToDateTime(GridVariables.GridColumn04);
                        yearsexp.Value = Convert.ToInt32(GridVariables.GridColumn05);
                        degreecode.Value = GridVariables.GridColumn06;

                        int newvar = 0;
                        int newid = Convert.ToInt32(command.ExecuteScalar());
                        newvar = newid;
                    }
                }
            }
        }

Open in new window

The flagged error is shown below:

the line being flagged is foreach (DataGridViewRow row in dgvDetailsTable.Rows)

I know that the application is not seeing the datagrid defined in a different layer and intellisense suggest adding a reference

I have tried to add reference but cant see anything to add

How can i Read data in a Grid with a method defined at a different layer of the application

Thanks Olukay

Obadiah Christopher

You need to create a Datatable in the UI layer and then pass the datatable as input to the Data Access Layer.

i.e.
in your UI Layer, on click of button/or whatever action you need, loop through the datagridview and create a datatable using the data in the datagridview. Then pass this datatable as input to your data access layer method.

By design, DataAccess layer should not be the place where you loop through the grid.

Also, curious to know why do you need to loop through the grid data? Is it going to be editable?

Olukayode Oluwole

ASKER

I had a data table created in the UI layer with the code below under a button. And the code works

private void UpdateButton_Click_1(object sender, EventArgs e)
        {
            // Script here uses a Data Table to open up new lines fir inserts into a grid
            // How to Add a DataTable
            // https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/adding-columns-to-a-datatable

            EduIdValue.Text = "0";   // Use  to send zero in during insert before actual ID is returned from the Method

            DataTable dt = dgvDetailsTable.DataSource as DataTable;
            dt = new DataTable();

            dt.Columns.Add("staff_no", typeof(String));
            dt.Columns.Add("schoolname", typeof(String));
            dt.Columns.Add("startdate", typeof(DateTime));
            dt.Columns.Add("enddate", typeof(DateTime));
            dt.Columns.Add("yearsexp", typeof(Int32));
            dt.Columns.Add("degreecode", typeof(String));
            dt.Columns.Add("companycode", typeof(String));
            dt.Columns.Add("empid", typeof(Int32));
            DataColumn workCol = dt.Columns.Add("Empeduid", typeof(Int32));
            //  workCol.AllowDBNull = false;
            workCol.Unique = true;


            DataRow row = dt.NewRow();
            row[0] = StaffNoValue.Text;
            row[1] = string.Empty;
            // row[2] = Convert.ToDateTime(dateTimePicker1.Value);
            // row[3] = Convert.ToDateTime(dateTimePicker2.Value);
            row[4] = 0;
            row[5] = string.Empty;
            row[6] = CompanyCodeValue.Text;
            row[7] = Convert.ToInt32(IdValue.Text);
            row[8] = 0;
            dt.Rows.Add(row);

            dgvDetailsTable.DataSource = dt;
            dgvDetailsTable.AllowUserToAddRows = true;
            dgvDetailsTable.Columns[1].ReadOnly = false;
            dgvDetailsTable.Columns[4].ReadOnly = false;
            dgvDetailsTable.Columns[5].ReadOnly = false;
            dgvDetailsTable.Columns[6].Visible = false;

            UpdateButton.Enabled = false;
            DeleteButton.Enabled = false;

        }

Open in new window

How do i pass this to the method

You requested to know why I have to loop. Thats because i have multiple rows in my Grid
and i need to save all of them to the database

Grateful if you can tell me precisely how to pass this Datatable to the method which i already specified
in my original post.

Thanks

Olukay

Obadiah Christopher

Not sure why you have the tuple passed
public void CreateEducationForm(List<Tuple<string, string, string, string, string>> newTuples)

Can you do something like this?

public void CreateEducationForm(List<Tuple<string, string, string, string, string>> newTuples, DataTable dt)
{

}
and when calling the Data layer
CreateEducationForm(newTuples, dt);

Olukayode Oluwole

ASKER

You will observe i commented the Tuple calling line.

That was my first try before i realised it was wrong

In the new suggestion what would be the equivalent of foreach statement ie

foreach (DataGridViewRow row in dgvDetailsTable.Rows)

Olukay

Obadiah Christopher

foreach(DataRow row in dt.Rows)
{
       schoolname.Value = row["schoolname"].ToString();
}

Open in new window

Olukayode Oluwole

ASKER

Not sure I can pass dt to the method as a parameter see error below

I know its because where Iam calling GlobalConfig.CreateEducation Form and where i declared the datatable
are different methods.

dt was not available under intellisense even when i made the method public

You have both methods now

How should I proceed ?

Regards

Olukay

Obadiah Christopher

Declare the datatable at class level instead of inside the update button click.

DataTable dt = new DataTable();

Olukayode Oluwole

ASKER

Tried without luck.

I am trying to Add rows to the datatable and seam to have a syntax error (see attached)

By any chance are you on teamview so we can fix this in real time

Thanks

olukay

ASKER CERTIFIED SOLUTION

Obadiah Christopher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olukayode Oluwole

ASKER

Thanks for the DataTable example sent. I should be able to sort out the problem.

I will close this thread now.

Olukay

Olukayode Oluwole

ASKER

Very helpful. Thanks

Olukayode Oluwole

ASKER

Thanks