Override query in sql string possible? C#

Currently I’m working with my local MSSQL database and when I make a connection all works good. However that is not the question I’m having right now; I want to make my code cleaner and I don’t want to have duplicated code or almost duplicated code.

For now I’m working with one large class that holds all the methods to selecting, creating, updating and/or deleting an user. But I think it can be writen down better with an override string that rides over the sql string inside the code.

Only thing is that I’m (for now) a complete noob and have no idea how to accomplish this… please help? As an example I’ve set the two regions, might change them to classes, below.

        #region Select Data from Database
        public DataTable Select()
        {
            // static method to connect to database
            SqlConnection conn = new SqlConnection(myconnstring);
            // to hold the data from database
            DataTable dt = new DataTable();
            try
            {
                // sql query to get date from database
                String sql = "SELECT * FROM tbl_users";
                // for executing command
                SqlCommand cmd = new SqlCommand(sql, conn);
                // getting data from database
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                // database connection open 
                conn.Open();
                // fill data in datatable
                adapter.Fill(dt); 
            }
            catch(Exception ex)
            {
                // throw message if any error accures
                MessageBox.Show(ex.Message); 
            }
            finally
            {
                // closing connection 
                conn.Close();
            }
            // return value in datatable
            return dt; 
        }
        #endregion


#region Search User on Database using KeyWords
        public DataTable Search(string keywords)
        {
            // static method to connect to database
            SqlConnection conn = new SqlConnection(myconnstring);
            // to hold the data from database
            DataTable dt = new DataTable();
            try
            {
                // sql query to get date from database
                String sql = "SELECT * FROM tbl_users WHERE id LIKE '%"+keywords+"%' OR first_name LIKE '%"+keywords+"%' OR last_name LIKE '%"+keywords+"%' OR username LIKE '%"+keywords+"%'";
                // for executing command
                SqlCommand cmd = new SqlCommand(sql, conn);
                // getting data from database
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                // database connection open 
                conn.Open();
                // fill data in datatable
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                // throw message if any error accures
                MessageBox.Show(ex.Message);
            }
            finally
            {
                // closing connection 
                conn.Close();
            }
            // return value in datatable
            return dt;
        }
        #endregion

​x
 
        #region Select Data from Database        public DataTable Select()        {            // static method to connect to database            SqlConnection conn = new SqlConnection(myconnstring);            // to hold the data from database            DataTable dt = new DataTable();            try            {                // sql query to get date from database                String sql = "SELECT * FROM tbl_users";                // for executing command                SqlCommand cmd = new SqlCommand(sql, conn);                // getting data from database                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                // database connection open                 conn.Open();                // fill data in datatable                adapter.Fill(dt);             }            catch(Exception ex)            {                // throw message if any error accures                MessageBox.Show(ex.Message);             }            finally            {                // closing connection                 conn.Close();            }            // return value in datatable            return dt;         }        #endregion​​#region Search User on Database using KeyWords        public DataTable Search(string keywords)        {            // static method to connect to database            SqlConnection conn = new SqlConnection(myconnstring);            // to hold the data from database            DataTable dt = new DataTable();            try            {                // sql query to get date from database                String sql = "SELECT * FROM tbl_users WHERE id LIKE '%"+keywords+"%' OR first_name LIKE '%"+keywords+"%' OR last_name LIKE '%"+keywords+"%' OR username LIKE '%"+keywords+"%'";                // for executing command                SqlCommand cmd = new SqlCommand(sql, conn);                // getting data from database                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                // database connection open                 conn.Open();                // fill data in datatable                adapter.Fill(dt);            }            catch (Exception ex)            {                // throw message if any error accures                MessageBox.Show(ex.Message);            }            finally            {                // closing connection                 conn.Close();            }            // return value in datatable            return dt;        }        #endregion​

Answer

Step 1. Read https://xkcd.com/327/ and whatever solution you go with fix id LIKE '%"+keywords+"%'

I encourage you to research an object mapper, like Dapper which will make your methods return types (e.g. User) and not raw DataTables. An ORM can help pushing you into the pit of success.

As for reuse you can notice that your methods that do SELECT look very similar so you could make a helper method DataTable ExecuteSelect(string sql) which you could reuse from your Search and Select methods.

You really must fix this '%"+keywords+"%' issue. SQL injection is no joke.

Advertisement

Answer