Solved

Newbie needs help building a class in c#

Posted on 2009-05-07
17
223 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:srikanthreddyn143
Comment Utility
mattfox77,

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

Author Comment

by:mattfox77
Comment Utility
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
Comment Utility
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
Comment Utility
WEll I solved the Row count syntax error myself
if (ds.Tables[0].Rows.Count > 0)
0
 
LVL 12

Expert Comment

by:williamcampbell
Comment Utility
ExecuteNonQuery

ds.Tables[0].Rows.Count





0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Assisted Solution

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

Author Comment

by:mattfox77
Comment Utility
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
Comment Utility
Thanks williamcampbell I see what I missed
0
 
LVL 1

Author Comment

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

Author Comment

by:mattfox77
Comment Utility
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
Comment Utility
Awesome! Exactly why I joined Experts Exchange! Thanks a million!
0
 
LVL 1

Author Comment

by:mattfox77
Comment Utility
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
Comment Utility
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
Comment Utility
Cool Matt!!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now