SQL data in DataGridView

Question

Hey guys, I have an application that I want to display some data from a sql db in a DataGridView... I have the data displayed in the DataGridView now but here are my questions... How can I use custom header titles because I don't want the SQL column titles to be used for the DataGridView column titles. Also, I want it so that when a user double-clicks on a row, it will open the filePath value from that row... I would also like to be able to specify the width of the columns. Here is how the tables are layed out.

SQL Table: Row_ID (I don't want displayed in grid), PartNumber, CMMNumber, CreatedOn, FilePath, RacfId, currTime

Data grid view desired format: Part Number, CMM Number, Created On, Path, User ID, Viewed On

Current code -- This gets the data (all, including Row_ID) and uses the sql column names as the names for the data grid view columns, it also just uses default column widths.

private void NewAlert_Load(object sender, EventArgs e)
        {
            string connString = "Server=FRXSQLDEV;Database=MyDB;User Id=ID;Password=Password;";

            string query = "SELECT * FROM CMMReports WHERE RacfId IS NULL;";

            SqlDataAdapter dAdapter = new SqlDataAdapter(query, connString);

            SqlCommandBuilder cBuilder = new SqlCommandBuilder(dAdapter);

            DataTable dTable = new DataTable();

            dAdapter.Fill(dTable);



            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();

            //set the BindingSource DataSource
            bSource.DataSource = dTable;

            //set the DataGridView DataSource
            dgView.DataSource = bSource;

            dAdapter.Update(dTable);



        }

Answer 1

If you want to use Custom titles etc. First of all create a new datatable, and give the names. Be Careful about typeof()

DataTable dt = new DataTable();

        dt.Columns.Add("Title 1",typeof(int));
        dt.Columns.Add("Title 2", typeof(string));
        dt.Columns.Add("Title 3", typeof(DateTime));
        dt.Columns.Add("Title 4", typeof(bool));
        dt.AcceptChanges();

Get all data which comes from SQL and create object for them in

foreach(var item in yourSqlData)

object[] row = new object[] 
        { 
        item.data1.ToString(), // string columns
        Convert.ToInt32(item.data2), // int columns
        Convert.ToDateTime(item.data3), // datetime column
        Convert.ToBoolean(item.data4) // bool column
        };
        dt.Rows.Add(row);

and bind dt to your gridView

Answer 2

For hiding you columns ("Row_ID") You can use

dgView.Columns[0].Visible = false;

For setting header text

dgView.Columns[1].HeaderText = "PartNumber";