C# | SQL | Change string concatenation to parameters

Question

In a previous question on this website, someone told me I shouldn't use string concatenation, but I have no clue how. Because I really want to understand this I thought I should throw the question on this site to get answers from you programming gods.

This is the code from my data layer:

namespace DAL
{
    public class Class2
    {
    }
    public class ClsDataLayer
    {
        SqlConnection SqlConn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=3tireexample;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        public void InsertData(string _name, string _city, string _email)
        {
            SqlDataAdapter SqlAdp = new SqlDataAdapter("Insert into Usermst values ('" + _name + "','" + _city + "','" + _email + "')", SqlConn);
            DataTable DT = new DataTable();
            SqlAdp.Fill(DT);
        }
        public object SelectData()
        {
            SqlDataAdapter SqlAdp = new SqlDataAdapter("Select * from Usermst", SqlConn);
            DataTable DT = new DataTable();
            SqlAdp.Fill(DT);
            return DT;
        }
        public void DeleteData(string _name, string _city, string _email)
        {
            SqlDataAdapter SqlAdp = new SqlDataAdapter("Delete top 1  from UserMst, SqlConn);
            DataTable DT = new DataTable();
            SqlAdp.Fill(DT);
        }


    }
}

The business layer:

namespace BLL
{
    public class Class1
    {
    }
    public class CLSBussLayer
    {
        ClsDataLayer objDal = new ClsDataLayer();
        public void InsertUser(string _name, string _city, string _email)
        {
            objDal.InsertData(_name, _city, _email);
        }
        public object SelectUser()
        {
            return objDal.SelectData();
        }
        public void DeleteUser(string _name, string _city, string _email)
        {
            objDal.DeleteData(_name, _city, _email);
        }
    }
}

My user interface layer:

namespace Individueel_projectV2
{
    public partial class Pagina1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnsave_Click(object sender, EventArgs e)
        {
            CLSBussLayer objBLL = new CLSBussLayer();
            objBLL.InsertUser(txtname.Text, txtcity.Text, txtemail.Text);

            GridView1.DataSource = objBLL.SelectUser();
            GridView1.DataBind();

        }

        protected void btndelete_Click(object sender, EventArgs e)
        {
            CLSBussLayer objBLL = new CLSBussLayer();
            objBLL.DeleteUser(txtname.Text, txtcity.Text, txtemail.Text);

            GridView1.DataSource = objBLL.SelectUser();
            GridView1.DataBind();
        }
    }
}

How do I change this? I don't ask for you to rewrite my whole code, but just a little piece so I can see what I must do. It also would be nice to explain a little why you do what you do (simple please) so I can learn from this. Thanks in advance!

Answer 1

You got an answer to your main question. I can show you some more improvements that you can make use of.

1. In your DAL, you can have the varables in common, outside the methods, so that those doesn't need to be defined again.
2. The text you use, put in a variable, and use as CommandText
3. Instead of hard coding table names etc (which are business rules), make them generic, so that, they can accept the prepared commandText.
4. In the front end, keep a method as DataBind(), to keep the code which is used in multiple times
5. Instead of having individual data elements, make the BLL class with the properties, that need and whenever data is paasing, instantiate the class with appropriate parameters and pass it.
6. You need to handle exceptions (may be you need to log those)
7. You may not need to assign the DT value, to the adapter in Insert / Delete methods.

Answer 2

How

See SqlCommand.Parameters for more information on how to do parameter binding. For example in your case you would do

string commandText = "Insert into Usermst values (@name, @city, @email)";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@name", SqlDbType.Varchar);
    command.Parameters["@name"].Value = name;

Since you are using SqlDataAdapter

SqlAdp.Parameters.Add("@name",
        SqlDbType.NVarChar, "Josse", "Name");

Why

• To Prevent SQL Injection
• Parameter binding also improves performance since DB engine can use and execute the cached query instead parsing the query again which would happen if you use string concatenation.