How to convert a function into a class to be used from any page

Question

I have the following function which is accessible from one of my asp.net page:

/* QUERY TO RUN FROM ANY FUNCTION */
public void runQuery()
{
    strMainSql = @"SELECT
                CT.OBJECTID 'Object ID'
        FROM HSI.RMOBJECTINSTANCE1224 CT
        WHERE CT.ACTIVESTATUS = 0 AND CT.OBJECTID = '" + s + "'";

    using (SqlConnection scConn = new SqlConnection(strConn))
    {
        scConn.Open();
        using (SqlCommand scComm = new SqlCommand(strMainSql, scConn))
        {
            sdrRead = scComm.ExecuteReader();
            while (sdrRead.Read())
            {
                /* CAN BE USED IN OTHER PAGES */
                strTaskName = sdrRead[1].ToString();
                strTaskDetail = sdrRead[2].ToString();
                strTaskStartDate = sdrRead[3].ToString();
                strIdentifier = sdrRead[4].ToString();
                strStatus = sdrRead[5].ToString();
                strDueDate = sdrRead[6].ToString();
                strIssueDate = sdrRead[7].ToString();
                strCompleted = sdrRead[8].ToString();
                strNotes = sdrRead[9].ToString();
                strProvider = sdrRead[10].ToString();
                strService = sdrRead[11].ToString();
                strCheckedDate = sdrRead[12].ToString();
                strCheckedStatus = sdrRead[13].ToString();
                strCheckedUser = sdrRead[14].ToString();
                strClient = sdrRead[15].ToString();

                hfMemoID.Value = sdrRead[16].ToString();
                hfObjectID.Value = sdrRead[0].ToString();

                break;
            }
        }
    }
    /* SPECIFIC TO THE PAGE ONLY */
    lblUser.Text = strCheckedUser;
    lblDateTime.Text = strCheckedDate;
    lblTaskName.Text = strTaskName;
    lblClient.Text = strClient;
    lblID.Text = strIdentifier;
    lblSvc.Text = strService;
    lblProvider.Text = strProvider;
    lblStat.Text = strStatus;
    lblDueDate.Text = strDueDate;
    lblDetail.Text = strTaskDetail;
    lblTaskIssue.Text = strIssueDate;
    lblStartDate.Text = strTaskStartDate;
    lblCompleted.Text = strCompleted;
}

The question I have is, if I have to use the above function in multiple pages, instead of having a multiple copies of the same function which might lead to issue later on when updating, how do I make it into a class by itself so I can call it from any page and get the value from the SQL query?

Answer 1

What you can do is expose the results from the query as properties and then use the properties in the ASPX page.

    using System.Data.SqlClient;

    namespace MyNamespace
    {
        public class Task
        {

            public string strTaskName { get; set; }
            public string strTaskDetail { get; set; }
            public string strTaskStartDate { get; set; }
            public string strIdentifier { get; set; }
            public string strStatus { get; set; }
            public string strDueDate { get; set; }
            public string strIssueDate { get; set; }
            public string strCompleted { get; set; }
            public string strNotes { get; set; }
            public string strProvider { get; set; }
            public string strService { get; set; }
            public string strCheckedDate { get; set; }
            public string strCheckedStatus { get; set; }
            public string strCheckedUser { get; set; }
            public string strClient { get; set; }

            // you need to define properties for the appropriate datatype on these
            //hfMemoID
            //hfObjectID

            public string strConn { get; set; }

            public void Load(string objectid)
            {
                var strMainSql = @"SELECT
                    CT.OBJECTID 'Object ID'
            FROM HSI.RMOBJECTINSTANCE1224 CT
            WHERE CT.ACTIVESTATUS = 0 AND CT.OBJECTID = '" + objectid + "'";

                using (SqlConnection scConn = new SqlConnection(strConn))
                {
                    scConn.Open();
                    using (SqlCommand scComm = new SqlCommand(strMainSql, scConn))
                    {
                        var sdrRead = scComm.ExecuteReader();
                        while (sdrRead.Read())
                        {
                            /* CAN BE USED IN OTHER PAGES */
                            this.strTaskName = sdrRead[1].ToString();
                            this.strTaskDetail = sdrRead[2].ToString();
                            this.strTaskStartDate = sdrRead[3].ToString();
                            this.strIdentifier = sdrRead[4].ToString();
                            this.strStatus = sdrRead[5].ToString();
                            this.strDueDate = sdrRead[6].ToString();
                            this.strIssueDate = sdrRead[7].ToString();
                            this.strCompleted = sdrRead[8].ToString();
                            this.strNotes = sdrRead[9].ToString();
                            this.strProvider = sdrRead[10].ToString();
                            this.strService = sdrRead[11].ToString();
                            this.strCheckedDate = sdrRead[12].ToString();
                            this.strCheckedStatus = sdrRead[13].ToString();
                            this.strCheckedUser = sdrRead[14].ToString();
                            this.strClient = sdrRead[15].ToString();

                            // 
                            //hfMemoID.Value = sdrRead[16].ToString();
                            //hfObjectID.Value = sdrRead[0].ToString();

                            break;
                        }
                    }
                }
            }
        }
    }

In the code behind use the class to load the data and then set the controls using the properties

    private MyNamespace.Task Task = new MyNamespace.Task();

    protected void Page_Load(object sender, EventArgs e)
    {

        Task.strConn = "my connection string.";
        Task.Load("task id to load");

        // set the value into the controls.
        lblUser.Text = Task.strCheckedUser;
        lblDateTime.Text = Task.strCheckedDate;
        lblTaskName.Text = Task.strTaskName;
        lblClient.Text = Task.strClient;
        lblID.Text = Task.strIdentifier;
        lblSvc.Text = Task.strService;
        lblProvider.Text = Task.strProvider;
        lblStat.Text = Task.strStatus;
        lblDueDate.Text = Task.strDueDate;
        lblDetail.Text = Task.strTaskDetail;
        lblTaskIssue.Text = Task.strIssueDate;
        lblStartDate.Text = Task.strTaskStartDate;
        lblCompleted.Text = Task.strCompleted;
    }