C#

bwood2020 wrote:

Right now I have it selecting all just to get the dgv on Form 2 to populate. Later the query will include a Select Distinct. Do you think this is a good way to do this or is there a more efficient method to code this?

string Query = ("SELECT * FROM [" + dataGridView1 + "]");
DataTable Sds = new DataTable(Query);
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);

string Query = ("SELECT * FROM [" + dataGridView1 + "]");
sds = new DataTable(Query);  <============= Here is the change, get rid of 'DataTable Sds' and replace it with just 'sds'
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);

sds = new DataTable(Query);

string Query = ("SELECT * FROM [" + dataGridView1 + "]");
DataTable Sds = new DataTable(Query);
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);

the code from the first link (modified for your tables)
sds = ReaderToDataTable(reader);

//create new dataTable and add new top row for new headers
                              DataTable newDataTable = new DataTable();

                              foreach (DataColumn col in dFill.Columns)
                              {
                                    try
                                    {
                                          newDataTable.Columns.Add(new DataColumn(col.ColumnName));

                                    }
                                    catch (Exception)
                                    {

                                    }
                              }
                              foreach (DataRow row in dFill.Rows)
                              {
                                    string[] rowarr = new string[dFill.Columns.Count];

                                    for (int k = 0; k < dFill.Columns.Count; k++)
                                    {
                                          rowarr[k] = row[k].ToString();
                                    }
                                    newDataTable.Rows.Add(rowarr);

                              }


                              DataRow NewRow;
                              NewRow = newDataTable.NewRow();
                              newDataTable.Rows.InsertAt(NewRow, 0);

// bindingSource1.DataSource = newDataTable;
bindingSource1.DataSource = dFill;

private Form2 docsForm = null;
private Form2 DocsForm
{
    get
    {
        if (this.docsForm == null)
        {
            this.docsForm = new Form2();
        }

        return docsForm;
    }
}

private void btnShowDocs_Click(object sender, EventArgs e)
{
                // ************************************
                // Only if there are to be many Form2s
                // ************************************
    Form2 DocsForm = new Form2();
                // ************************************

                DocsForm.DataKey = this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
                DocsForm.Show();  // or .ShowDialog(), depends on whether need access to Form1 again before Form2 closes
}

public partial class Form2 : Form
{
    private bool dataDisplayed = false;

    public Form2()
    {
        InitializeComponent();
    }

    private void Form2_Load(object sender, EventArgs e)
    {
        this.LoadData();
    }

    private void LoadData()
    {
        // here will go the code to load the data into the datagrids

        this.dataDisplayed = true;
    }

    private string dataKey = string.Empty;
    public string DataKey
    {
        get
        {
            return this.dataKey;
        }

        set
        {
            if (this.dataKey != value)
            {
                this.dataKey = value;
                if (this.dataDisplayed)
                {
                    this.LoadData();
                }
            }
        }
    }
}

    private MappingsForm mappingsForm = null;
    private MappingsForm MappingsForm
    {
        get
        {
            if (this.mappingsForm == null)
            {
                this.mappingsForm = new MappingsForm();
            }

            return mappingsForm;
        }
    }

private void btnShowDocs_Click(object sender, EventArgs e)
{
        MappingsForm.DataKey = this.dataGridView1.Rows[0];
        MappingsForm.Show();
}

private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
    if (this.mappingsForm != null)
{
    this.mappingsForm.Dispose();
    this.mappingsForm = null;
}
}

public partial class MappingsForm : Form
{
    private bool dataDisplayed = false;

    public MappingsForm()
    {
        InitializeComponent();
    }

    private void MappingsForm_Load(object sender, EventArgs e)
    {
                if (this.dataKey != null)
                {
            this.LoadData();
                }
    }

    private void LoadData()
    {
        // this is part of your code, slightly modified
                for (int c = 0; c < this.dataKey.Columns.Count; c++)
                {
                    if(this.dataKey.Cells[c].Value.ToString() == "DocType")
                    {
                        //pass data to dgv1
                    }
                    else if (this.dataKey.Cells[c].Value.ToString() == "PurposeType") // else here to avoid unnecessary processing
                    {
                        //pass data to dgv2
                    }
                    else if (this.dataKey.Cells[c].Value.ToString() == "SomeOtherType") // else here to avoid unnecessary processing
                    {
                        // and so on
                    }

                    // This could also be written like this, so either have the ifs as above OR this
                    switch (this.dataKey.Cells[c].Value.ToString())
                    {
                        case "DocType":
                            //pass dataKey.Cells[c].Value to dgv1
                            break;
                        case "PurposeType":
                            //pass dataKey.Cells[c].Value to dgv2
                            break;
                        case "SomeOtherType":
                            // and so on
                            break;
                    }
                }
        this.dataDisplayed = true;
    }

    private DataGridViewRow dataKey = null;
    public DataGridViewRow DataKey
    {
        get
        {
            return this.dataKey;
        }

        set
        {
            if (this.dataKey != value)
        {
            this.dataKey = value;
            if (this.dataDisplayed)
            {
                this.LoadData();
            }
        }
        }
    }
}

SELECT * FROM TheThisTypeFile.csv
  WHERE DataType == "thisType"

Prop, Offer, Docs, Gtee, Figs
house, no, full, none, true
flat, true, part, complete, no
appt, no, no, yes, yes
bungalow, yes, full, no, no
mais, false, yes, no, comp

public partial class ListEditorForm : Form
{
    List<string> standardList = new List<string>();
    Dictionary<string, string> headers = new Dictionary<string, string>();
    DataSet headerDataSet = null;
    DataTable headerTable = null;
    MappingForm mappingForm = null;
    readonly string dataFile = "TestData.csv";

    FieldMappingDictionary docsDictionary = null;

    public ListEditorForm()
    {
        InitializeComponent();
        InitStandardList();
    }

    private void InitStandardList()
    {
        standardList.AddRange(new string[] { "Property", "Offer", "Documentation", "Figures", "Guarantee" });
    }

    private void ProcessMods()
    {
        // Create a copy of original table structure with
        // standardized column names**
        // load original file data into it
        // then do a series of updates using the docsDictionary, and the
        // other Dictionaries
        // STEP THROUGH THIS TO WATCH UPDATE STATEMENT BUILDING
        StringBuilder selectBuilder = null;
        foreach (string key in this.docsDictionary.Keys)
        {
            foreach (string val in this.docsDictionary[key])
            {
                selectBuilder = new StringBuilder();
                selectBuilder.Append("UPDATE <yourtablenamehere**> SET Documentation = ");
                selectBuilder.Append(key);
                selectBuilder.Append(" WHERE Documentation = ");
                selectBuilder.Append(val);

                string updateQuery = selectBuilder.ToString();

                // execute the query here
                // you know OleDbConnection, OleDbcommand etc.
                // then go round for next mod
            }
            // go round for next key
        }

        // then do same for next dictionary
    }

    private Dictionary<string, string> MakeHeaderDictionary()
    {
        Dictionary<string, string> result = new Dictionary<string, string>();
        foreach (DataRow row in this.headerTable.Rows)
        {
            result.Add(row[1].ToString(), row[0].ToString());
        }

        return result;
    }

    #region ListEditorForm PROPERTIES ...............................
    public Dictionary<string, string> Headers
    {
        get
        {
            return this.headers;
        }

        private set
        {
            this.headers = value;
        }
    }

    private MappingForm MappingForm
    {
        get
        {
            if (this.mappingForm == null)
            {
                this.mappingForm = new MappingForm();
            }

            return this.mappingForm;
        }
    }
    #endregion ListEditorForm PROPERTIES

    private void ListEditorForm_Load(object sender, EventArgs e)
    {
        string headerLine;
        using (StreamReader sr = new StreamReader(dataFile))
        {
            headerLine = sr.ReadLine();
        }

        headerDataSet = new DataSet("HeaderDataSet");
        headerTable = new DataTable("ClientHeaders");
        DataColumn newColumn = new DataColumn("ClientHeader", typeof(string));
        headerTable.Columns.Add(newColumn);
        newColumn = new DataColumn("StandardHeader", typeof(string));
        headerTable.Columns.Add(newColumn);

        DataRow newRow = null;
        foreach (string s in headerLine.Split(','))
        {
            newRow = headerTable.NewRow();
            newRow[0] = s;
            newRow[1] = "Property";
            headerTable.Rows.Add(newRow);
        }

        headerDataSet.Tables.Add(headerTable);

        DataGridViewComboBoxColumn cbCol =
            this.dataGridView1.Columns["standardColumn"] as DataGridViewComboBoxColumn;
        cbCol.DataSource = this.standardList;

        this.dataGridView1.AutoGenerateColumns = false;
        this.dataGridView1.Columns[0].DataPropertyName = "ClientHeader";
        this.dataGridView1.Columns[1].DataPropertyName = "StandardHeader";
        this.dataGridView1.DataSource = headerDataSet;
        this.dataGridView1.DataMember = "ClientHeaders";
    }

    private void btnGetValue_Click(object sender, EventArgs e)
    {
        this.headers = this.MakeHeaderDictionary();
        this.MappingForm.HeaderDictionary = this.headers;
        this.MappingForm.DataFile = this.dataFile;
        this.MappingForm.FormClosing += new FormClosingEventHandler(MappingForm_Closed);
        this.MappingForm.Show();
    }

    void MappingForm_Closed(object sender, FormClosingEventArgs e)
    {
        this.docsDictionary = this.MappingForm.DocumentsDictionary;
        // get rest of dictionaries here

        this.ProcessMods();
    }
}

public partial class MappingForm : Form
{
    List<string> standardDocumentList = new List<string>();
    FieldMappingDictionary documentsDictionary = new FieldMappingDictionary();

    private Dictionary<string, string> headerDictionary = null;
    private string dataFile;

    public MappingForm()
    {
        InitializeComponent();

        InitLookupLists();
    }

    private void InitLookupLists()
    {
        this.standardDocumentList.AddRange(new string[] { "Full", "Partial", "None" });
    }

    private void MappingForm_Load(object sender, EventArgs e)
    {
        DataGridView dgv = null;
        string headerString = null;

        foreach (string key in this.headerDictionary.Keys)
        {
            headerString = this.headerDictionary[key];
            switch (key)
            {
                case "Documentation":
                    dgv = this.dgvDocumentation;
                    break;
                case "Property":
                    dgv = null;
                    break;
                case "Offer":
                    dgv = null;
                    break;
                case "Figures":
                    dgv = null;
                    break;
                case "Guarantee":
                    dgv = null;
                    break;
            }

            if (dgv != null)
            {
                this.FillDatagrid(headerString, dgv);
            }
        }
    }

    DataTable dFill = null;
    private void FillDatagrid(string headerString, DataGridView dgv)
    {
        //Connect to csv file
        String comString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            Path.GetDirectoryName(Path.GetFullPath(this.dataFile)) + @";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1\""";

        String strSql = "SELECT DISTINCT " + headerString + " FROM [" + Path.GetFileName(this.dataFile) + "]";
        using (OleDbConnection conCSV = new OleDbConnection(comString))
        {
            conCSV.Open();
            OleDbCommand dbCommand = new OleDbCommand(strSql, conCSV);
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(dbCommand);
            dFill = new DataTable();
            dAdapter.Fill(dFill);
        }

        bindingSource1.DataSource = dFill;
        dgv.DataSource = bindingSource1;

        // Set up lookup for dgvDocumentation
        // only temporarilly here for demo
        DataGridViewComboBoxColumn lookupColumn = new DataGridViewComboBoxColumn();
        this.dgvDocumentation.Columns.Add(lookupColumn);
        lookupColumn.ValueType = typeof(string);
        lookupColumn.HeaderText = "Standard";
        lookupColumn.DataSource = this.standardDocumentList;

        for (int i = 0; i < dgv.Columns.Count; i++)
        {
            dgv.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
            dgv.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
        }
    }

    private void CreateDocsDictionary()
    {
        foreach (DataGridViewRow row in this.dgvDocumentation.Rows)
        {
            //DataGridViewComboBoxCell lookupCell = row.Cells[1] as DataGridViewComboBoxCell;
            if (row.Cells[0].Value != null)
            {
                this.documentsDictionary.Add(row.Cells[1].Value.ToString(), row.Cells[0].Value.ToString());
            }
        }
    }

    #region MappingForm PROPERTIES ...............................
    public Dictionary<string, string> HeaderDictionary
    {
        get
        {
            return this.headerDictionary;
        }

        set
        {
            this.headerDictionary = value;
        }
    }

    public string DataFile
    {
        get
        {
            return this.dataFile;
        }

        set
        {
            this.dataFile = value;
        }
    }

    public FieldMappingDictionary DocumentsDictionary
    {
        get
        {
            return this.documentsDictionary;
        }
    }
    #endregion MappingForm PROPERTIES

    private void btnOK_Click(object sender, EventArgs e)
    {
        this.CreateDocsDictionary();
        // then the others

        //when done close
        this.Close();
    }
}

public class FieldMappingDictionary : Dictionary<string, List<string>>
{
    public void Add(string key, string value)
    {
        // if the key is already in the list
        if (base.Keys.Contains(key))
        {
            // only do it if not already there
            if (!base[key].Contains(value))
            {
                // add the new value
                base[key].Add(value);
            }
        }
        else
        {
            // if the key ain't there, add it and a new values list
            List<string> list = new List<string>();
            list.Add(value);
            base.Add(key, list);
        }
    }
}