CODEWITHSUNDEEP
c#sqlsearchdatagridviewmultiple-columns
Mokey
Mokey

Reputation: 215

C# & SQL Server : searching all database columns and populating datagrid

I am writing a a C# application, and I am stuck at searching the database and populating a data grid view. However I want to use this with command builder.

The issue is, I need the search to work across all columns in the database. I thought using OR and LIKE statements would do this. But instead I get either invalid syntax or no column name exists in the search.

Does anyone know a solution?

My current .cs:

private void btnSearchJob_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = (@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MTR_Database;Integrated Security=True");

        string selectQuery = "SELECT * FROM dbo.[" + cmbJobName.Text + "] WHERE ([Job Name] LIKE " +txtSearchJob.Text+ " OR [Manufacturer] LIKE " +txtSearchJob.Text+ ")";

        // DataAdapter
        myDA = new SqlDataAdapter(selectQuery, con);

        // SqlCommand
        SqlCommand myCMD = new SqlCommand(selectQuery, con);

        // DataAdapter to Command
        myDA.SelectCommand = myCMD;

        // Define Datatable
        myDT = new DataTable();

        // Command Builder (IS GOD!)
        SqlCommandBuilder cb = new SqlCommandBuilder(myDA);

        // Teach Command builder to be a boss!
        myDA.UpdateCommand = cb.GetUpdateCommand();
        myDA.InsertCommand = cb.GetInsertCommand();
        myDA.DeleteCommand = cb.GetDeleteCommand();

        // Fill the DataTable with DataAdapter information
        myDA.Fill(myDT);

        // Fill DataTable with Database Schema
        myDA.FillSchema(myDT, SchemaType.Source);

        // Bind The Data Table to the DataGrid
        dataGridView1.DataSource = myDT;

        // AutoSize Datagrid Rows and Colums to fit the Datagrid
        dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
        dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
    }
    // Catch Exception
    catch (Exception ex)
    {
        MessageBox.Show(this, ex.Message, "SQL ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

NOTE:

I am aware of using parameters, I am simply using this to see if it will work when I will add parameters later.

Upvotes: 0

Views: 1882

Answers (2)

percentum
percentum

Reputation: 153

I think you are missing ' in the query. Try this...

string selectQuery = "SELECT * FROM dbo.[" + cmbJobName.Text + "] WHERE ([Job Name] LIKE '" +txtSearchJob.Text+ "' OR [Manufacturer] LIKE '" +txtSearchJob.Text+ "')";

Upvotes: 1

Simon Price
Simon Price

Reputation: 3261

this is what I use to get everything back into a DataTable

 //'places the call to the system and returns the data as a datatable
    public  DataTable GetDataAsDatatable(List<SqlParameter> sqlParameters, string connStr, string storedProcName)
    {

        var dt = new DataTable();
        var sqlCmd = new SqlCommand();
        using (var sqlconn = new SqlConnection(connStr))
        {
            sqlCmd.Connection = sqlconn;
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = storedProcName;
            sqlCmd.CommandTimeout = 5000;
            foreach (var sqlParam in sqlParameters)
            {
                sqlCmd.Parameters.Add(sqlParam);
            }
            using (var sqlDa = new SqlDataAdapter(sqlCmd))
            {
                sqlDa.Fill(dt);
            }
        }

        sqlParameters.Clear();
        return dt;
    }

    //'places the call to the system and returns the data as a datatable
    public  DataTable GetDataAsDatatable(string connStr, string query)
    {

        var dt = new DataTable();
        var sqlCmd = new SqlCommand();
        using (var sqlconn = new SqlConnection(connStr))
        {
            sqlCmd.Connection = sqlconn;
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.CommandText = query;
            sqlCmd.CommandTimeout = 5000;
            using (var sqlDa = new SqlDataAdapter(sqlCmd))
            {
                sqlDa.Fill(dt);
            }
        }

        return dt;
    }

hopefully this is pretty self explanatory to you, however pass in a list of SQL Parameters, connection string, and the stored procedure name, or use the second one where you pass in the inline SQL and the connection string.

Upvotes: 1

Related Questions