none
Run SQL command within a dataSet.xsd RRS feed

  • Question

  • I have the following code, thanks to Magnus (MM8) on another thread.

    string[] allWords = txtSearch.Text.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
    string sql = "SELECT Books.ISBN, Books.Title, Books.Tag, Books.Image, Books.photoType, Publishers.Name AS publisherName FROM Books INNER JOIN Publishers ON Books.codPublisher = Publishers.codPublisher WHERE ";
    using (SqlCommand command = new SqlCommand())
    {
    for (int i = 0; i < allWords.Length; ++i)
            {
                  if (i > 0)
                  sql += "OR ";
    
                  string paramName = "@param" + i.ToString();
                  sql += string.Format("(Books.Title LIKE {0}) ", paramName);
                  command.Parameters.AddWithValue(paramName, allWords[i] + "%");
             }
             command.CommandText = sql;
     }

    My project has a dataSet.xsd, with a table adapter's. When I want to query the database I just do something like:

    BooksTableAdapter tableAdapterBooks = new BooksTableAdapter();
    dataSetLibrary.BooksDataTable dataTableBooks;

    dataTableBooks = tableAdapterBooks.getDataByTitle(searchText);

    I don't know how can I use the command provided above to query the database.



    • Edited by Carlos Sota Tuesday, April 14, 2015 4:49 PM
    Tuesday, April 14, 2015 4:27 PM

Answers

  • After hours around this, I have came with this solution.

    private SqlConnection sqlConn = new SqlConnection();
    private System.Data.DataSet dataSet = new System.Data.DataSet();
    private System.Data.DataTable dataTable;
    private System.Data.DataRow dataRow;
    
    private SqlCommand search(string searchParam, int searchOption)
            {
                SqlCommand command = new SqlCommand();
                string sql;
                string[] allWords = searchParam.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                if (searchOption == 1)
                {
                    sql = "SELECT Livros.ISBN, Livros.Titulo, Livros.Tema, Livros.Resumo, Livros.Imagem, Livros.fotoTipo, Editoras.Nome AS nomeEditora FROM Livros INNER JOIN Editoras ON Livros.codEditora = Editoras.codEditora WHERE ";
                }
                else
                {
                    sql = "SELECT Livros.ISBN, Livros.Titulo, Livros.Tema, Livros.Resumo, Livros.Imagem, Livros.fotoTipo, Editoras.Nome AS nomeEditora FROM Livros INNER JOIN livrosAutores ON Livros.ISBN = livrosAutores.ISBN INNER JOIN Autores ON livrosAutores.idAutor = Autores.idAutor INNER JOIN Editoras ON Livros.codEditora = Editoras.codEditora WHERE ";
                }
                using (command)
                {
                    for (int i = 0; i < allWords.Length; ++i)
                    {
                        if (i > 0)
                        {
                            sql += "OR ";
                        }
    
                        if (searchOption == 1)
                        {
                            sql += string.Format("(Livros.Titulo LIKE '%{0}%') ", allWords[i]);
                        }
                        else
                        {
                            sql += string.Format("(Livros.Autor LIKE '%{0}%') ", allWords[i]);
                        }
                    }
                    command.CommandText = sql;
                }
                return command;
            }
    
    protected void Bind()
            {
                    sqlConn.ConnectionString = Properties.Settings.Default.BibliotecaConnectionString;
                    string connectionString = sqlConn.ConnectionString.ToString();
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(search(searchText, searchOption).CommandText, connectionString);
                    sqlDataAdapter.Fill(dataSet, "livrosTitulo");
                    dataTable = dataSet.Tables["livrosTitulo"];
                    dataGrid.DataContext = dataTable.DefaultView;
            }

    Thanks everyone for their help!

    Wednesday, April 15, 2015 12:56 PM

All replies

  • Hi Carlos Sota,

    As your BooksTableAdapter and BooksDataTable is customized, could you share the code of these customized classes, we could help you better.

    I suggest you filling the dataset with DataAdapte like below, and then use the queries in the dataset.

    #Loading Data Into a DataSet
    https://msdn.microsoft.com/en-us/library/bb399340%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    #Single-Table Queries (LINQ to DataSet)
    https://msdn.microsoft.com/en-us/library/bb386910(v=vs.110).aspx

    You could change the select condition in the sql command to query the database, or select the all data to dataset, then query in the DataSet by Linq. 

    If you have any other concern regarding this issue, please feel free to let me know.

    Best regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    • Edited by Youjun Tang Wednesday, April 15, 2015 6:54 AM
    Wednesday, April 15, 2015 6:54 AM
  • You cannot run a SQL command within a dataset. 

    There are three ways to do a query in a dataset. 

    1. Using the Select Method in a DataTable
    2. Using a DataView which gives in fact the same result but then dynamic
    3. Using Linq to DataSet for which Youjun gave you a link. 

    In fact it are all expressions. It is your choice which you use. 

    The Select and the DataView are limited, but that was exactly the reason why Linq was build.

    If you have a limited query then use 1 or 2, if it is difficult 3 (and a "Like" is never limited).


    Success
    Cor




    Wednesday, April 15, 2015 8:13 AM
  • The problem is that I have a textbox in window1.xaml, this textbox works as a searchbox. I have a select method that I created in my dataSet designer. The search works by book title and author, but there is a problem. Take for instance the book "Windows Server 2008 R2 Unleashed". If the user searches for "windows" or "server 2008" or "windows server" it works. But if the user searches for "windows 2008" it doesn't.

    I tried to solve the problem with

    string search;
    search = txtSearch.Text.Replace(" ", "%");

    But it didn't really worked, so I have the above method the create a dynamic SQL select. But now I really don't know what to do. I have looked into your suggestions but I'm more confused as ever.

    Wednesday, April 15, 2015 11:22 AM
  • After hours around this, I have came with this solution.

    private SqlConnection sqlConn = new SqlConnection();
    private System.Data.DataSet dataSet = new System.Data.DataSet();
    private System.Data.DataTable dataTable;
    private System.Data.DataRow dataRow;
    
    private SqlCommand search(string searchParam, int searchOption)
            {
                SqlCommand command = new SqlCommand();
                string sql;
                string[] allWords = searchParam.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                if (searchOption == 1)
                {
                    sql = "SELECT Livros.ISBN, Livros.Titulo, Livros.Tema, Livros.Resumo, Livros.Imagem, Livros.fotoTipo, Editoras.Nome AS nomeEditora FROM Livros INNER JOIN Editoras ON Livros.codEditora = Editoras.codEditora WHERE ";
                }
                else
                {
                    sql = "SELECT Livros.ISBN, Livros.Titulo, Livros.Tema, Livros.Resumo, Livros.Imagem, Livros.fotoTipo, Editoras.Nome AS nomeEditora FROM Livros INNER JOIN livrosAutores ON Livros.ISBN = livrosAutores.ISBN INNER JOIN Autores ON livrosAutores.idAutor = Autores.idAutor INNER JOIN Editoras ON Livros.codEditora = Editoras.codEditora WHERE ";
                }
                using (command)
                {
                    for (int i = 0; i < allWords.Length; ++i)
                    {
                        if (i > 0)
                        {
                            sql += "OR ";
                        }
    
                        if (searchOption == 1)
                        {
                            sql += string.Format("(Livros.Titulo LIKE '%{0}%') ", allWords[i]);
                        }
                        else
                        {
                            sql += string.Format("(Livros.Autor LIKE '%{0}%') ", allWords[i]);
                        }
                    }
                    command.CommandText = sql;
                }
                return command;
            }
    
    protected void Bind()
            {
                    sqlConn.ConnectionString = Properties.Settings.Default.BibliotecaConnectionString;
                    string connectionString = sqlConn.ConnectionString.ToString();
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(search(searchText, searchOption).CommandText, connectionString);
                    sqlDataAdapter.Fill(dataSet, "livrosTitulo");
                    dataTable = dataSet.Tables["livrosTitulo"];
                    dataGrid.DataContext = dataTable.DefaultView;
            }

    Thanks everyone for their help!

    Wednesday, April 15, 2015 12:56 PM
  • Although it is proposed by Magnus does your reply not fulfill the header of the question. Problem it leads by wrong perception by others.

    What you do is simply searching in a database and creating new datasets. 

    Around that are thousands of samples therefore on Internet. It is the standard way I used with framework 1.x and which many for instance Bonnie still prefer. 

    But it is not searching within a dataset.


    Success
    Cor

    Friday, April 17, 2015 10:26 AM
  • Hey Cor, thanks for mentioning me. I've been on vacation all last week, with no Internet access, so I've missed all the action here.

    I think that Carlos was just confused about the terminology ... many, many people are confused about DataSets and DataAccess ... especially since the Typed DataSets now have the TableAdapters generated with them, which further blurs the distinction. As you know Cor, DataSets and DataAccess are not the same thing, but perhaps that's how Carlos was thinking of it.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, April 22, 2015 5:02 AM