asp.net - How to use variable in Sql?

Question

I have a problem. How to use variable in SQL question? This code doesn't work:

   cmd.CommandText = "select Id, Name from tblFiles WHERE email = 'CurrentUser'";
   cmd.CommandText = "select Id, Name from tblFiles WHERE email = '+CurrentUser'";
   cmd.CommandText = "select Id, Name from tblFiles WHERE email = '$CurrentUser'";
   cmd.CommandText = "select Id, Name from tblFiles WHERE email =' +CurrentUser'";
   cmd.CommandText = "select Id, Name from tblFiles WHERE email = CurrentUser";

I must download from database date where email is CurrentUser = User.Identity.Name.

 private void BindGrid()
    {
        string CurrentUser = User.Identity.Name;
        string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {

            if (CurrentUser != null)
            {
             using (SqlCommand cmd = new SqlCommand())
             {
                GridView GridView1 = LoginView3.FindControl("GridView1") as GridView;
                cmd.CommandText = "select Id, Name from tblFiles WHERE email = 'CurrentUser'";
                cmd.Connection = con;
                con.Open();
                GridView1.DataSource = cmd.ExecuteReader();
                GridView1.DataBind();
                con.Close();
             }
         }
     }
 }

Try this piece of code cmd.CommandText = string.Format("select Id, Name from tblFiles WHERE email = '{0}'", CurrentUser); — Karthick NS
– Karthick NS, Commented Sep 15, 2014 at 11:55
@KarthickNS the only way that doesn't get your site hacked... — Marc Gravell
– Marc Gravell, Commented Sep 15, 2014 at 12:02

David Pilkington · Accepted Answer · 2014-09-15 11:54:12Z

4

You need to make use of Parameters

    cmd.CommandText = "select Id, Name from tblFiles WHERE email = @CurrentUser";
    cmd.Parameters.Add("@CurrentUser", SqlDbType.NVarChar);
    cmd.Parameters["@CurrentUser"].Value = User.Identity.Name;

answered Sep 15, 2014 at 11:54

David Pilkington

13.6k3 gold badges43 silver badges75 bronze badges

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

Comments

karlosos · Accepted Answer · 2014-09-15 12:15:32Z

To do what you want you should use parameters. You can read more about them [here].(http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx)

private void BindGrid()
{
    var CurrentUser = User.Identity.Name;
    string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {

        if (CurrentUser != null)
        {
        using (SqlCommand cmd = new SqlCommand())
            {
                GridView GridView1 = LoginView3.FindControl("GridView1") as GridView;
                cmd.CommandText = "select Id, Name from tblFiles WHERE email = @CurrentUser";
                cmd.Parameters.Add("@CurrentUser", SqlDbType.NVarChar);
                cmd.Parameters["@CurrentUser"].Value = User.Identity.Name;
                cmd.Connection = con;
                con.Open();
                GridView1.DataSource = cmd.ExecuteReader();
                GridView1.DataBind();
                con.Close();

            }
        }
    }
}

Marc Gravell · Accepted Answer · 2014-09-15 12:00:33Z

1

As is noted: parameters are the way to go here. But it doesn't need to be hard - you can use tools like "dapper" to make it painless:

string email = User.Identity.Name;
var row = con.Query("select Id, Name from tblFiles WHERE email = @email",
    new { email }).SingleOrDefault();
if(row != null) {
    int id = row.Id;
    string name = row.Name;
    // ...
}

(here Query creates an IEnumerable<dynamic> based on the columns, and SingleOrDefault selects exactly 0 or 1 rows). All the other methods you would want (ExecuteReader, ExecuteScalar, multi-grid handling, etc) are all there too.

You can also use Query<User> to populate a User by matching columns to fields/properties.

answered Sep 15, 2014 at 12:00

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

mybirthname · Accepted Answer · 2014-09-15 11:58:56Z

0

cmd.CommandText = "select Id, Name from tblFiles WHERE email = @CurrentUser";    
cmd.Parameters.AddWithValue("@CurrentUser", User.Identity.Name);

This should add parameters to your command.

answered Sep 15, 2014 at 11:58

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

Collectives™ on Stack Overflow

asp.net - How to use variable in Sql?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related