Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Newbie needs help building a class in c#

Posted on 2009-05-07
17
Medium Priority
?
248 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 400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 1600 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 400 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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)…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

886 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