Query string in SQL command c#

Question

First of all.. excuse me for my bad English , i hope to be understood.

I'm regullar to work with LINQ , the SQL is new for me.

i'm trying to do the next thing: i have the next method on c#:

public string niceMethod() 
{ 
    SqlConnection connection = new SqlConnection("Data Source=*******;Integrated Security=False;"); 
    string commandtext = "SELECT bla FROM items WHERE main = 1"; 
    SqlCommand command = new SqlCommand(commandtext, connection); 
    connection.Open(); 
    string tDate = (string)command.ExecuteScalar(); 
    connection.Close(); 
    return tDate; 
}

I have page for example: items.aspx?nID=144

how can i do that the SELECT command will be with querystring and that will take the value

from the "items" table by the id (nID) that show on the address ?

The table have the design for example:id, title, bla, main.

Why don't you use some ORM? You could then use LINQ.

svick
– svick

2011-07-03 20:37:26 +00:00
Commented Jul 3, 2011 at 20:37 — svick
– svick, Commented Jul 3, 2011 at 20:37

p.campbell · Accepted Answer · 2011-07-03 20:35:02Z

6

Try something like this:

int nID = int.Parse(Request.QueryString["nID"].ToString());
niceMethod(nID);

public string niceMethod(int nID) 
{ 
   using (var conn = new SqlConnection("Data Source=server;Initial Catalog=blah;Integrated Security=False;"))
   using (var cmd = conn.CreateCommand())
   {
        conn.Open();
        cmd.CommandText = @"SELECT bla, id, title FROM items WHERE main = @nID"; 
        cmd.Parameters.AddWithValue("@nID", nID);
        string tDate = cmd.ExecuteScalar().ToString();             
        return tDate;
   }
 }

answered Jul 3, 2011 at 20:35

p.campbell

101k70 gold badges264 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Danpe · Accepted Answer · 2011-07-03 20:43:04Z

Try this:

Pay attention to the (Request.QueryString["nID"] ?? "0").ToString() it's really importent so you wont get exception when there is no query.

    public string niceMethod()
    {
        string tDate = "";
        string ID = (Request.QueryString["nID"] ?? "0").ToString(); // Get's the nID query, incase there is no query, returns 0.
        using (SqlConnection connection = new SqlConnection("Data Source=*******;Integrated Security=False;"))
        {
            string commandtext = "SELECT bla FROM items WHERE id=@ID"; //@ID Is a parameter
            SqlCommand command = new SqlCommand(commandtext, connection);
            command.Parameters.AddWithValue("@ID", ID); //Adds the ID we got before to the SQL command
            connection.Open();
            tDate = (string)command.ExecuteScalar();
        } //Connection will automaticly get Closed becuase of "using";
        return tDate;
    }

Collectives™ on Stack Overflow

Query string in SQL command c#

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related