asked on

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

srikanthreddyn143

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

SOLUTION

williamcampbell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattfox77

ASKER

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.

srikanthreddyn143

mattfox77,

It is just the skeleton, there might be syntax errors check it out

mattfox77

ASKER

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?

ASKER CERTIFIED SOLUTION

srikanthreddyn143

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattfox77

ASKER

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

williamcampbell

ExecuteNonQuery

ds.Tables[0].Rows.Count

SOLUTION

williamcampbell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattfox77

ASKER

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);

mattfox77

ASKER

Thanks williamcampbell I see what I missed

mattfox77

ASKER

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

works

mattfox77

ASKER

only 1 error left! ExecuteNonQueery doesn;t work either...

ds = cmd.ExecuteNonQuery();

Error 6 Cannot implicitly convert type 'int' to 'System.Data.DataSet'

mattfox77

ASKER

Awesome! Exactly why I joined Experts Exchange! Thanks a million!

mattfox77

ASKER

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.

mattfox77

ASKER

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

srikanthreddyn143

Cool Matt!!!