CODEWITHSUNDEEP
c#sql
aneena
aneena

Reputation: 197

Query giving only one result

My query is giving one result only i.e. one word for one URL when it should run for every entry in dt. Here dt is DataTable and contains many rows, url_given is a String. temp is table containing the words to be matched. Can anybody please tell me what is wrong with my query? wordcount is the name of the table. url_String, word and count are columns.

The purpose of the query is to get the URLs matched with given URL and their words which are in the list which is temp.

 foreach (DataRow row in dt.Rows)
 {
     // read item
     url_given = row["url_String"].ToString();
     String qrystring = "select url_String,word,count from wordcount where url_String='" + url_given + "' and word in (select * from temp) ";
     dt1 = db.searchandorder(qrystring);
     // searchandorder is a call to a function that establishes the db connections and passes the query to the data adapter.
 }

Here is my searchandorder function:

public DataTable searchandorder (String sql)
{
    conn.Open();
    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    da.Fill( dt);
    conn.Close();
    Console.Write("table coloumns" + dt.Columns.ToString());
    return dt;
}

Upvotes: 0

Views: 131

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127543

Your problem comes from the fact you loop overwriting dt1 every time. You need to merge the results not replace them, but I can't say how you would do that without seeing db.searchandorder.

Also you have a very dangerous sql injection attack. If url_given was '; drop table wordcount; -- what would be the query the program will execute on the server? You need to use parameters with your data adapter.

EDIT: So, now that you shown your code, here is how you fix it. Instead of making a new DataTable every single time you pass you make one outside of the loop and pass in the same table every time. TableAdapter does not clear out the table before it fills by default.

private YourFunction(DataTable dt)
{
    DataTable dt1 = new DataTable();

    foreach (DataRow row in dt.Rows)
     {
         // read item
         url_given = row["url_String"].ToString();

         String qrystring = "select url_String,word,count from wordcount where url_String='" + url_given + "' and word in (select * from temp) ";
         db.searchandorder(qrystring, dt1);
         // searchandorder is a call to a function that establishes the db connections and passes the query to the data adapter.
     }

    DoSomthingWithResults(dt1);
}

public void searchandorder (String sql, DataTable dt)
{
    conn.Open();

    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
    da.Fill( dt);


    conn.Close();
    Console.Write("table coloumns" + dt.Columns.ToString());

    return dt;
}

Now, that is the bare minimum to make your program work. There are a LOT of other bad practices you are doing.

  • Don't reuse a connection: Your conn object should be created inside searchandorder and be inside a using statement. Don't worry about making "too many connection", .NET will use connection pooling and reuse old connections.

  • Use using statments: Anything that implements IDisposable should be in a using statement (unless the item is being returned)

  • Follow naming standards: There is a set of standards C# code follows, your class searcandhorder should be named SerchAndOrder.

  • Use Parameters: You are vulnerable to a SQL injection attack use parameters for your data adapters. Not only does it improve safety, it makes your program faster by allowing SQL server to cache the query execution plan.

Here is a version of your program with those fixes applied.

private YourFunction(DataTable dt)
{
    DataTable dt1 = new DataTable();

    foreach (DataRow row in dt.Rows)
     {
         // read item
         url_given = row["url_String"].ToString();
         var parameter = new SqlParameter("@urlGiven", SqlDbType.VarChar, url_given.Length);
         parameter.Value = url_given;

         String qrystring = "select url_String,word,count from wordcount where url_String=@urlGiven and word in (select * from temp) ";
         db.searchandorder(qrystring, dt1, parameter);
         // searchandorder is a call to a function that establishes the db connections and passes the query to the data adapter.
     }

    DoSomthingWithResults(dt1);
}

public void SearchAndOrder (String sql, DataTable dt, params SqlParameter[] parameters)
{
    using(var conn = new SqlConnection(_connectionString))
    using(var da = new SqlDataAdapter(sql, conn))
    {
        da.SelectCommand.Parameters.AddRange(parameters);
        conn.Open();
        da.Fill(dt);
    }

    Console.Write("table coloumns" + dt.Columns.ToString());

    return dt;
}

Upvotes: 3

Related Questions