Solved

Newbie needs help building a class in c#

Posted on 2009-05-07
17
231 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:mattfox77
  • 10
  • 4
  • 3
17 Comments
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24330038
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
 
LVL 12

Assisted Solution

by:williamcampbell
williamcampbell earned 100 total points
ID: 24330192
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
 
LVL 1

Author Comment

by:mattfox77
ID: 24330303
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24330416
mattfox77,

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

Author Comment

by:mattfox77
ID: 24330483
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
 
LVL 11

Accepted Solution

by:
srikanthreddyn143 earned 400 total points
ID: 24330549
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
 
LVL 1

Author Comment

by:mattfox77
ID: 24330563
WEll I solved the Row count syntax error myself
if (ds.Tables[0].Rows.Count > 0)
0
 
LVL 12

Expert Comment

by:williamcampbell
ID: 24330599
ExecuteNonQuery

ds.Tables[0].Rows.Count





0
 
LVL 12

Assisted Solution

by:williamcampbell
williamcampbell earned 100 total points
ID: 24330610
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString);
0
 
LVL 1

Author Comment

by:mattfox77
ID: 24330648
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
 
LVL 1

Author Comment

by:mattfox77
ID: 24330667
Thanks williamcampbell I see what I missed
0
 
LVL 1

Author Comment

by:mattfox77
ID: 24330669
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SumTotalSTEP"].ConnectionString);
           
works
0
 
LVL 1

Author Comment

by:mattfox77
ID: 24330685
only 1 error left! ExecuteNonQueery doesn;t work either...

ds = cmd.ExecuteNonQuery();

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

Author Closing Comment

by:mattfox77
ID: 31579184
Awesome! Exactly why I joined Experts Exchange! Thanks a million!
0
 
LVL 1

Author Comment

by:mattfox77
ID: 24330730
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
 
LVL 1

Author Comment

by:mattfox77
ID: 24331022
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
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24331065
Cool Matt!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question