Use parametrized sql queries when SQLCommand used in separate class

Question

I am interesting to add parametrize sql queries in my ASP.net application. I have seen some good articles regarding Avoid SQL Injection.

string sql = string.Format("INSERT INTO [UserData] (Username, Password, Role, Membership, DateOfReg) VALUES (@Username, @Password, @Role, @Membership, @DateOfReg)");
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
        cmd.Parameters.AddWithValue("Username", usernameTB.Text);
        cmd.Parameters.AddWithValue("Password", passwordTB.Text);
        cmd.Parameters.AddWithValue("Role", roleTB.Text);
        cmd.Parameters.AddWithValue("Membership", membershipTB.Text);
        cmd.Parameters.AddWithValue("DateOfReg", dorTB.Text);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

find the Reference

However this way is not useful to me since I couple the DB connection to separate class since I have reuse it.

public class DBconnection{     
    public int insertQuery(String query) {



            int affectedRowCount = 0;
            SqlConnection conn = null;
            try{

                conn = new SqlConnection("Server=localhost;Database=master;UID=sa;PWD=sa;");
                SqlCommand cmd = new SqlCommand( query, conn );
                cmd.CommandType = CommandType.Text;

                conn.Open(  );
                affectedRowCount = cmd.ExecuteNonQuery(  );
                conn.Close(  );         

            } catch ( Exception e ){

                       String error = e.Message;

            }

            return affectedRowCount;
    }
}

Therefore I only use bellow code part to call above class and Insert values to DB.

 String SQLQuery1 = insert into Article values('" + Txtname.Text + "','" + TxtNo.Text + "','" + Txtdescription.Text + "' ,0)");
DBconnection dbConn = new DBconnection();
        SqlDataReader Dr = dbConn.insertQuery(SQLQuery1);

Please help me to use Parameterize sqlString to Avoid me Sql Injection. To use @name , @ No and @description without use Textbox inputs.

Answer 1

It's perfectly reasonable to do this, but have your class call back (lambda/delegate) out to get the parameters. This is a static method in a class which is called by various overloaded instance methods:

private static int SqlExec(string ConnectionString, string StoredProcName, Action<SqlCommand> AddParameters, Action<SqlCommand> PostExec)
        {
            int ret;
            using (var cn = new SqlConnection(ConnectionString))
            using (var cmd = new SqlCommand(StoredProcName, cn))
            {
                cn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                if (AddParameters != null)
                {
                    AddParameters(cmd);
                }

                ret = cmd.ExecuteNonQuery();

                if (PostExec != null)
                {
                    PostExec(cmd);
                }
            }
            return ret;
        }

Then, a usage example:

    public void Save()
    {
        Data.Connect().Exec("Project_Update", Cm =>
        {
            Cm.Parameters.AddWithValue("@ProjectID", ID);
            Cm.Parameters.AddWithValue("@PrimaryApplicantID", PrimaryApplicant.IdOrDBNull());
            Cm.Parameters.AddWithValue("@SecondaryApplicantID", SecondaryApplicant.IdOrDBNull());
            Cm.Parameters.AddWithValue("@ProjectName", ProjectName.ToDBValue());
        });
    }

It's also possible to do this with non-stored procedure calls.

In your case it would look like:

DBconnection.InsertQuery(
    "INSERT INTO [UserData]
        (Username, Password, Role, Membership, DateOfReg)
        VALUES (@Username, @Password, @Role, @Membership, @DateOfReg)"
    ,cmd => {
                cmd.Parameters.AddWithValue("Username", usernameTB.Text);
                cmd.Parameters.AddWithValue("Password", passwordTB.Text);
                cmd.Parameters.AddWithValue("Role", roleTB.Text);
                cmd.Parameters.AddWithValue("Membership", membershipTB.Text);
                cmd.Parameters.AddWithValue("DateOfReg", dorTB.Text);
            }
);

Which puts all your database stuff together the way you want and lets the DBconnection keep its internals isolated.