CODEWITHSUNDEEP
c#
user10578683
user10578683

Reputation:

How to search for a specific column in SQL Table

In my applications I have several columns named 1 ,2 ,3 ... 8. I have a search box in my Windows form App that the user can search for column 1 or 2 etc and then it needs to display that information in my datagridview.

At the moment I have this code which searches for a column name MAT but it also needs to search for a column with specific column name.

So for example:

in my text file I have "test" and "3" it then reads the first word in the text file and places it in the text box which then searches automatically for the column named "MAT". In the second text box it places the "3", now this needs to search for column 3. Like I said in the text file it could be anything from 1 - 8 (test 1 or test 2 or test 3 .... etc)

Here is my code I have at the moment:

private void latestMatTextBox_TextChanged(object sender, EventArgs e)
    {
        using (var conn = new SqlConnection())
        {

            conn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\AddMatDB.mdf;Integrated Security=True";
            conn.Open();

            using (var adapter = new SqlDataAdapter("SELECT GetDate() as Event, Owner, Holder, MAT, Location FROM TBLMat where MAT like @search", conn))

            {
                adapter.SelectCommand.Parameters.Add("search", SqlDbType.NVarChar).Value =
            "%" + latestMatTextBox.Text + "%";
                adapter.Fill(dataTable);
                if (dataTable.Columns.Count > 0)
                {
                    dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns[0] };
                }

                mainAMSDataGrid.DataSource = dataTable;
                mainAMSDataGrid.Visible = true;
                mainAMSDataGrid.Sort(this.mainAMSDataGrid.Columns[0], ListSortDirection.Descending);


                conn.Close();
            }
        }

So the above code brings up the "MAT" Column (which works great). I would also need to specify a column like "Event" which is not in my table to display "Name" which will then show the searched column information in.

Hope I have made sense here :-)

Thanks

Upvotes: 2

Views: 133

Answers (1)

T.S.
T.S.

Reputation: 19340

You need to build a dynamic sql. I am going to steal and give credit to @jdweng for this -

In your form, you need to fill one or more combo boxes with names of the columns you have. so if you fill your data table using 

Select * from TBLMat where 1=0

This will get you empty table. Now you can do this 

string[] columns = dataTable.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();

You can also do select * from information_schema.COLUMNS WHERE ...

Once you have your combo(s) filled, you need to build a list of where conditions

list.Add(cbocol.SelectedItem.ToString() + " = " & GenerateParameterAndAddToCommand(txtVal.Text)) // this method should output @1, @2, etc

Then you compile your where and attach to rest of query

var sql = @"SELECT GetDate() as Event, Owner, Holder, MAT, Location 
             FROM TBLMat 
             where " + string.Join(" AND ", list)

And you can select operator - Like, =, <> etc. You can select AND\OR. You will have to make that part dynamic also in this case

Upvotes: 1

Related Questions