SQL Query from c# class

Question

I'm trying to modify my code by placing the SQL connections and queries into a C# class because currently all my .aspx.cs has connection strings with different types of queries with parameters

Like this:

string CS2 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection con2 = new SqlConnection(CS2))
{
    SqlCommand cmd2 = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = " + lblCedula.Text + "", con2);
    con2.Open();
    object labels = cmd2.ExecuteScalar();
    lblNombre.Text = labels.ToString();
}

As you notice I complete the query with lblCedula.Text so when I try to do this from a C# class it says lblCedula does not exist in this content.

How could I make a reference to the label I have in another page from the class?

This is my code in the C# class

public static List<Perfil> DeletePerfil()
{
    List<Perfil> listDelete = new List<Perfil>();

    string CS = ConfigurationManager.ConnectionStrings["DBCSATE"].ConnectionString;

    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = " + lblCedula.Text + "", con);
        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
             Perfil perfil1 = new Perfil();
             perfil1.perfil = Convert.ToInt32(rdr["perfil"]);
             perfil1.descripcion = rdr["descripcion"].ToString();

             listDelete.Add(perfil1);
        }
    }

    return listDelete;
}

Don't make a reference to the label, instead pass it's values as an argument to your function. Also you really need to use sql parameters to avoid sql injection. — juharr
– juharr, Commented Aug 3, 2016 at 13:45
And use parameter binding instead of simply concatenating the string. This is exactly the case where SQL injection is possible. — sstan
– sstan, Commented Aug 3, 2016 at 13:46
The label is what the user entered in a page before this page, so I need the reference to the label because the results dependes on what the user entered in a checkbox earlier — Hans
– Hans, Commented Aug 3, 2016 at 13:48
@HansAjani - that fine to accept what the user inputs, but make sure you sanitise the input first and use parameter binding. If you don't you will be open to SQL injection attacks. — sr28
– sr28, Commented Aug 3, 2016 at 13:49

Community · Accepted Answer · 2017-05-23 12:32:55Z

1

You can hand over the string by a method parameter.

Also please mention the use of the SqlParameter. It will prevent your Query from SQL Injection

public static List<Perfil> DeletePerfil(string numeroPersonal)
{

        using (SqlConnection con = new SqlConnection(CS))
        {

            SqlCommand cmd = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = @PersNo", con);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@PersNo";
            param.Value = numeroPersonal;
            cmd.Parameters.Add(param);

         ...
        }
}

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Aug 3, 2016 at 13:52

Mark

3,2792 gold badges38 silver badges55 bronze badges

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

Comments

Overhed · Accepted Answer · 2016-08-03 13:48:29Z

0

Like others have said, pass in lblCedula.Text as a string parameter to your DeletePerfil method:

public static List<Perfil> DeletePerfil(string numeroPersonal)
...

Then your call looks like this:

...
var deletedProfiles = YourClass.DeletePerfil(lblCedula.Text);
...

And like sstan's comment mentioned, make sure you use SQLParameters instead of using string concatenation to avoid a SQL Injection security hole.

answered Aug 3, 2016 at 13:48

Overhed

1,3241 gold badge14 silver badges42 bronze badges

3 Comments

Hans Over a year ago

Ok, i understand the theory now, But i still dont understand how or what i need to place in the query instead of the label Is it something like this? "public static List<Perfil> DeletePerfil(string numeroPersonal)"...... then "SqlCommand cmd = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = " + numeroPersonal + "", con);" ??

Overhed Over a year ago

As a direct replacement of your existing code, yes. What we're also suggesting (strongly) is that instead of using string concatenation, you generate your query string using SQL Parameters: software-security.sans.org/developer-how-to/…

Hans Over a year ago

Ok, thanks a lot! It worked... Let me try with the parameters now

Collectives™ on Stack Overflow

SQL Query from c# class

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related