LINQ: Dynamic SQL Query within a dataset

Question

User-1587537398 posted

I have the following method that grabs a string, removes all spaces and add an OR for each word.

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;
 }

When I wanted to query my database I would simply do

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

Seems like I cannot run SQL Commands within a DataSet. I have been told to use LINQ, but I have no ideia how to achieve this.

Answer 1

User-1587537398 posted

After hours around this, I have come up 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;
        }

Answer 2

User1711366110 posted

After hours around this, I have come up with this solution.

   Glad to found the solution,
you can also do with "Like" operator as below :

private SqlCommand search(string searchParam, int searchOption)
        {
            ...
            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;
        }

--
with regards,
Edwin