How to peform a query in a mdf database?

[doomfrawen]

Hello friends! I'm writing a program that is connected to a mdf database and i need to query the database, but I have tried several ways to do it but without success :( !

This is my code:

string Query = null;
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            con.Open();
            Query = txtSearch.Text;
            SqlCommand comm = new SqlCommand("SELECT SUCURSAL_PRODUCTO.SUCURSAL_idSucursal, SUCURSAL_PRODUCTO.PRODUCTO_idProducto, SUCURSAL_PRODUCTO.cantidadStock, PRODUCTO.idProducto, PRODUCTO.titulo, PRODUCTO.autor, PRODUCTO.precio, SUCURSAL_PRODUCTO.cantidadStock AS Expr1," + 
                         "SUCURSAL_PRODUCTO.SUCURSAL_idSucursal AS Expr2 " +
                         "FROM SUCURSAL_PRODUCTO INNER JOIN PRODUCTO ON SUCURSAL_PRODUCTO.PRODUCTO_idProducto = PRODUCTO.idProducto "+
                        "WHERE (SUCURSAL_PRODUCTO.SUCURSAL_idSucursal = 2)", con);
            //SqlCommand comm = new SqlCommand("SELECT * FROM PRODUCTO", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(dt);
            //lstData.DataContext = dt.DefaultView;
             Query = comm.ExecuteScalar() as string;
            //Query = (string)comm.ExecuteScalar();
            lstData.DataContext = Query;

The main query works if i execute it from a button(but that displays all the data) that's why i need to filter the results on my app, the data is populated on a listView called lstData.

If you need anything else please let me now.

P.S. I'm using WPF 2010

Thanks in advanced.

[kvprajapati]

DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(dt);
            lstData.DataContext = dt;

[doomfrawen]

Thx for your quick response, but i was wondering if there is a way to filter the results with a textbox, i mean if the user writes on the textbox the listview will be populated with the filter or string that was typed on it. For example if the user writes the name of a group of music, the listview will be populated with only that music group instead of all the music groups like my query does

[kvprajapati]

>if there is a way to filter the results with a textbox

To filter result use DataView.

DataTable dt = new DataTable();
 SqlDataAdapter da = new SqlDataAdapter(comm);
 da.Fill(dt);
 DataView dv=dt.DefaultView;
 dv.RowFilter="columnName='" + TextBox1.Text  + "'";
 lstData.DataContext = dv;

[doomfrawen]

Wow, thank you so much that really helped me a lot.
I just modified my code a little bit to make it work, I'll put it here if anyone needs it someday.

By the way is it possible to use the method startsWith to query the database? I mean the rowfilter worked but the string needs to be the same as in the database so i tried to use sartswith but without success.

Here's the code:

private void btnSearch_Click(object sender, RoutedEventArgs e)
        {
            string Query = txtSearch.Text;
           
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            con.Open();
            SqlCommand comm = new SqlCommand("SELECT SUCURSAL_PRODUCTO.SUCURSAL_idSucursal, SUCURSAL_PRODUCTO.PRODUCTO_idProducto, SUCURSAL_PRODUCTO.cantidadStock, PRODUCTO.idProducto, PRODUCTO.titulo, PRODUCTO.autor, PRODUCTO.precio, SUCURSAL_PRODUCTO.cantidadStock AS Expr1," +
                               "SUCURSAL_PRODUCTO.SUCURSAL_idSucursal AS Expr2 " +
                               "FROM SUCURSAL_PRODUCTO INNER JOIN PRODUCTO ON SUCURSAL_PRODUCTO.PRODUCTO_idProducto = PRODUCTO.idProducto " +
                               "WHERE SUCURSAL_PRODUCTO.SUCURSAL_idSucursal = 2", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(dt);
            DataView dv = dt.DefaultView;
            Debug.WriteLine("Debug Information-Product Starting> ");
            Debug.Indent();
            Debug.WriteLine("Textbox string= "+ Query);
            Debug.Unindent();
            Debug.Flush();
                lstData.ItemsSource = dt.DefaultView;

                if (Query.StartsWith(Query))
                {
                    dv.RowFilter = "titulo = '" + Query + "'";
                }
                lstData.ItemsSource = dv.Table.DefaultView;
            con.Close();
        }

Thanks in advanced!

[doomfrawen]

Finally I could make it work as it should be working. Thanks for your help, here's the code:

Debug.Flush();
                lstData.ItemsSource = dt.DefaultView;

                if (Query.StartsWith(Query))
                {
                    dv.RowFilter = "titulo LIKE '%" + Query + "%'";
                }
                lstData.ItemsSource = dv.Table.DefaultView;
            con.Close();