Newbie needs help building a class in c#

I have a web app I am writing that needs to re-use code to retrieve values from DB, evaluate them , and return the result.

But,  I am new to creating classes in c# - I've read the tutorials but am lost where to start...looking for some assistance to meet my deadline end of today.


REquirements:
Accept strings UserID and ActCode
use values to query DB given SELECT query and return EndDt, CompletionStatus from DB record

here's the logic of what I need to do.  I have a working example of the code
If CompletionStatus = 1
Compare Today's date to EndDt + 12 months
if Today > EndDt +12 months set status = expired
else
Compare today to EndDt + 11Months
if Today > endDt + 11months set status = Pending
else
today < EndDt + 11months set status = Valid


sdsTestCert.SelectParameters["UserID"].DefaultValue = sUserID;
             sdsTestCert.SelectParameters["ActCode"].DefaultValue = sBankID;
             
            //Get User Status for Bank Certification
             DataView dvBank = (DataView)sdsTestCert.Select(DataSourceSelectArguments.Empty);
             foreach (DataRowView drvBank in dvBank)
             {
                 //Capture values from dataset returned for user/Activity
                 lblUserName.Text = drvBank["Usr_Name"].ToString();
                 lblCompletionStatus.Text = drvBank["CompletionStatus"].ToString();
                 sCompletionStatus = drvBank["CompletionStatus"].ToString();
                 sSuccessStatus = drvBank["Success"].ToString();
                 sStartDate = drvBank["StartDt"].ToString();
                 sEndDate = drvBank["EndDt"].ToString();
            }
            try
            {
                iCompletionStatus = int.Parse(sCompletionStatus);
            }
            catch
            {
                iCompletionStatus = 0;
            }
            try
            {
                EndDt = DateTime.Parse(sEndDate);
            }
            catch
            {
            }
 
 DateTime dtPendBank = EndDt.AddMonths(11);
            DateTime dtCertBank = EndDt.AddYears(1);
            lbldtCert.Text = dtCertBank.ToString();
            lbldtPend.Text = dtPendBank.ToString();
            lblEndDt.Text = EndDt.ToString();
            lblToday.Text = Today.ToString();
 
            //is EndDt greater than dtCert?  if so Certification has expired
            if (Today > dtCertBank)
            {
                //Certification has expired so show red Status image
                sbtnBank.ImageUrl = "~/images/status_red_24.gif";
            }
            else
            {
                //Certification has not expired so check if it is pending expiration
                if (Today > dtPendBank)
                {
                    //EndDt is greater than dtPend (but less than dtCert) so Certification is within 30 days of expiring so show yellow status image
                    sbtnBank.ImageUrl = "~/images/status_yellow_24.gif";
                }
                else
                {
                    //Certification has not expired, and is not pending expiration,  so it is valid
                    sbtnBank.ImageUrl = "~/images/status_green_24.gif";
                }
            }

Open in new window

LVL 1
mattfox77Asked:
Who is Participating?
 
srikanthreddyn143Connect With a Mentor Commented:
use this

public class Test
{
 public void TestMethod(String sUserid, string sAcctCd)
{
string status;
string sSQL = "SELECT ENDDT,COMPLETIONSTATUS FROM DB WHERE USERID = '" + sUserid + "' AND ACCTCD = '" + sAcctCd + "'";
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString);
SqlCommand cmd = new SqlCommand(sSQL,Conn);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;
adapter.Fill(ds)
If (ds.Tables[0].Rows.Count > 0 )
{
 DateTime dt =ds.Tables[0].Rows[0]["ENDDT"];
 If (ds.Tables[0].Rows[0]["COMPLETIONSTATUS"] == 1 )
{
   If (Now.Date > dt.AddMonths(12))
{
  status = "EXPIRED";
}
else
{
   If (Now.Date > dt.AddMonths(11))
{
  status = "PENDING";
}
else
{
status = "VALID";
}
}
} 
 
}
 
 
}    
}

Open in new window

0
 
srikanthreddyn143Commented:
It is just test code

public class Test
{
 public void TestMethod(String sUserid, string sAcctCd)
{
string status;
string sSQL = "SELECT ENDDT,COMPLETIONSTATUS FROM DB WHERE USERID = '" + sUserid + "' AND ACCTCD = '" + sAcctCd + "'";
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sSQL,Conn);
DataSet ds = new DataSet();
ds = cmd.ExecuteQuery();
If (ds.Tables[0].Rows[0].Count > 0 )
{
 DateTime dt =ds.Tables[0].Rows[0]["ENDDT"];
 If (ds.Tables[0].Rows[0]["COMPLETIONSTATUS"] == 1 )
{
   If (Now.Date > dt.AddMonths(12))
{
  status = "EXPIRED";
}
else
{
   If (Now.Date > dt.AddMonths(11))
{
  status = "PENDING";
}
else
{
status = "VALID";
}
}
} 
 
}
 
 
}    
}

Open in new window

0
 
williamcampbellConnect With a Mentor Commented:
All C# is made up of classes there is no Code outside a class. So the code you have shown is already in a class.

What you need to do is take that code and make a new class?
In that case right click on your project and select "Add Class"
Now you can take sriks code and flesh out the class

Once the class is done then you can call it

 Test mytestClass = new Test ();
 if ( mytestClass != null )
{
    mytestClass.TestMethod ( "wc", "12345" );
}

Let us know if you need more help
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mattfox77Author Commented:
Thanks srikanthreddyn143! It looks like you provided exactly what I needed
I am using your test template to build my class and will let you know the results or any questiosn I have

Yes williamcampbell I am aware that c# is made up of classes.  You are correct I want to build a new class so I can reuse it for multiple values.  I thought I had explained that much - srikanthreddyn143 apparently understood my need.

Still,  I did call myself a newbie so thanks for following up with the calling code and basics review - good reference in case I forget.

I will post my results when im done and assign points.


0
 
srikanthreddyn143Commented:
mattfox77,

It is just the skeleton, there might be syntax errors check it out
0
 
mattfox77Author Commented:
Ok first problem:  when I use the line
ds = cmd.ExecuteQuery();
compile error
Error      7      'System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteQuery'      

I also get a compile error on this line
if (ds.Tables[0].Rows[0].Count > 0)
Error      8      'System.Data.DataRow' does not contain a definition for 'Count'      


also,  I need to use a Connectionstring that is defined in my web.config.  I am using this code

SqlConnection Conn = ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString;

and I get the error

Error      6      Cannot implicitly convert type 'string' to 'System.Data.SqlClient.SqlConnection'      

any ideas?

0
 
mattfox77Author Commented:
WEll I solved the Row count syntax error myself
if (ds.Tables[0].Rows.Count > 0)
0
 
williamcampbellCommented:
ExecuteNonQuery

ds.Tables[0].Rows.Count





0
 
williamcampbellConnect With a Mentor Commented:
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString);
0
 
mattfox77Author Commented:
I think I fixed the connection string by creating a string from the web.config

string ConnString = ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString;
            SqlConnection Conn = new SqlConnection(ConnString);
0
 
mattfox77Author Commented:
Thanks williamcampbell I see what I missed
0
 
mattfox77Author Commented:
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString);
           
works
0
 
mattfox77Author Commented:
only 1 error left! ExecuteNonQueery doesn;t work either...

ds = cmd.ExecuteNonQuery();

Error      6      Cannot implicitly convert type 'int' to 'System.Data.DataSet'      
0
 
mattfox77Author Commented:
Awesome! Exactly why I joined Experts Exchange! Thanks a million!
0
 
mattfox77Author Commented:
Srik,  your last post of reworked code using the SQLDataAdapter did the trick!!

Thanks a lot to you,  and also to williamcampbell for the additional input and review.

0
 
mattfox77Author Commented:
here's the finished working code - I ended up returning an int value to make swiotch statement easier

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
namespace ShelterSTEPPortal
{
    public class CertStatus
    {
        public int GetCertStatusByCode(string sUserID, string sActCode)
        {
            int status;
            string sSQL = "SELECT TBL_TMX_Attempt.CompletionStatus, TBL_TMX_Attempt.EndDt, TBL_TMX_Attempt.Success, TBL_TMX_Attempt.Score FROM TBL_TMX_Activity INNER JOIN iwc_Usr INNER JOIN tblEmp ON iwc_Usr.Usr_EmpFK = tblEmp.Emp_PK INNER JOIN TBL_TMX_Attempt ON tblEmp.Emp_PK = TBL_TMX_Attempt.EmpFK ON TBL_TMX_Activity.Activity_PK = TBL_TMX_Attempt.ActivityFK LEFT OUTER JOIN TBL_TMX_ActCBT ON TBL_TMX_Activity.Activity_PK = TBL_TMX_ActCBT.ActivityFK WHERE (iwc_Usr.Usr_Name = '" + sUserID + "') AND (TBL_TMX_Activity.Code = '" + sActCode + "') AND (TBL_TMX_Attempt.CurrentAttemptInd = 1)";
           SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sSQL, Conn);
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DateTime dt = DateTime.Parse(ds.Tables[0].Rows[0]["EndDt"].ToString());
                if (ds.Tables[0].Rows[0]["CompletionStatus"].ToString() == "1")
                {
                    //If Today's Date is greater than the Attempt EndDate + 12Months,  
                    //then it has been at least a year since the activity was completed - Certification is expired
                    if (DateTime.Now.Date > dt.AddMonths(12))
                    {
                        status = 3;
                    }
                    else
                    {
                        //Certification has not expired but if todays date is greater than Attempt EndDt + 11 months
                        //then Certification is pending expiration
                        if (DateTime.Now.Date > dt.AddMonths(11))
                        {
                            status = 2;
                        }
                            //If Today's Date is less than Attempt EndDT + 11months then certifcation is still valid
                        else
                        {
                            status = 1;
                        }
                    }
                }
                else
                    //Attempt has not been completed
                {
                    status = 0;
                }
            }
            else
            {
                //dataset returned 0 rows - No record for user for his certification
                status = -1;
            }
            return status;
        }
    }
}
 
c# codebehind:
             CertStatus BankStatus = new CertStatus();
             if (BankStatus != null)
             {
               StatusBank = BankStatus.GetCertStatusByCode(sUserID, sBankID);
             }
             switch (StatusBank)
             {
                     //No Attempt record for user for this activity when StatusBank = -1
                 case -1:
                     sbtnBank.ImageUrl = "~/images/status_grey_24.gif";
                     break;
                 //Attempt has not been completed when StatusBank = 0
                 case 0:
                     sbtnBank.ImageUrl = "~/images/status_red_24.gif";
                     break;
                     //Attempt is Valid when StatusBank = 1
                 case 1:
                     sbtnBank.ImageUrl = "~/images/status_green_24.gif";
                     break;
                 //Attempt is pending Expiration when StatusBank = 2
                 case 2:
 
                     sbtnBank.ImageUrl = "~/images/status_yellow_24.gif";
                     break;
                 //Attempt has expired when StatusBank = 3
                 case 3:
                     sbtnBank.ImageUrl = "~/images/status_red_24.gif";
                     break;
                     
             }

Open in new window

0
 
srikanthreddyn143Commented:
Cool Matt!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.