How to use sql query result in a web service?

Post Reply
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

How to use sql query result in a web service?

Post by Trebor29 » Thu Feb 16, 2012 11:49 pm

Hi Guys...

Im building an ASP.Net payment application to demonstrate application security but have hit an obstacle already!

I have created users (locally) and have bank accounts in a web service to simulate the bank as an external entity. Before the users payment is approved I want to check their account (on the web service) for sufficient funds before proceeding. The users Card number is pulled from a db and passed over (with the payment amount) to the bank (web service) and there another query takes place i.e. "gets AccountBalance from BankAccTable where CardNumber = cn"...

Im building this application in VS2010, and in the Query Builder this returns the correct result. However, I have only ever done something similar where I displayed the result in a Gridview, which works also... but now I need to do this programmatically, receiving the result in the web service so I can do the check and return an "insufficient funds" msg, or proceed to deduct the PaymentAmount and update the db record... (The amount will then be passed to another WS simulating the merchants bank account, to be update.. but thats later, one step at a time!).

I think I need to hold the result somewhere i.e. DataSet / DataTable, do what I need to do and then re-insert the updated Row, but everything Ive been trying isn't working! I keep getting the "TableName" not the query result! :roll:

PAYMENT.ASPX.CS

Code: Select all

 
protected void ButtonPayNow_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["UserConnectionString"].ConnectionString);
        con.Open();
        string cmdStr = "SELECT PaymentPIN FROM UserTable WHERE EmailAddress= '" + TextBoxPayEmail.Text + "'";
        SqlCommand accVerify = new SqlCommand(cmdStr, con);
        string payPIN = Convert.ToString(accVerify.ExecuteScalar());
        if (TextBoxPIN.Text == payPIN)
        {
            // TextBoxPayEmail.Text = "Success..!";

            // Retrieve users bank info.
            SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["UserBankDatabaseConnectionString"].ConnectionString);
            con2.Open();
            string cmdStr2 = "SELECT CardNumber FROM UserBankTable WHERE EmailAddress= '" + TextBoxPayEmail.Text +"'";
            SqlCommand CardNum = new SqlCommand(cmdStr2, con2);
            string cardNum = Convert.ToString(CardNum.ExecuteScalar());

            // Check bank for funds.
            BankHost.Service BankWS = new BankHost.Service();
            double payAmount = double.Parse(TextBoxPayAmount.Text);
            string balance = Convert.ToString(BankWS.checkAccBalance(cardNum, payAmount));
        }
        else
        {
            TextBoxPayEmail.Text = "Didnt work..!";
        }
BANK WEBSERVICE.CS

Code: Select all

  [WebMethod] // Check for funds
    public DataSet1.BankAccTableDataTable  checkAccBalance(string cn, double paySum)
    {
        DataSet1TableAdapters.BankAccTableTableAdapter ta = new DataSet1TableAdapters.BankAccTableTableAdapter();
        return ta.GetBalanceData(cn);
    }
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

This is my attempt at getting the "AccountBalance" but it didnt work, returns the same result "TableName" (BankAccTable).
BANK WEBSERVICE.CS

Code: Select all

  [WebMethod] // Check for funds
    public string checkAccBalance(string cn, double pa)
    {
        DataSet1TableAdapters.BankAccTableTableAdapter ta = new DataSet1TableAdapters.BankAccTableTableAdapter();
        
        string bal = ta.GetBalanceData(cn).ToString();

        return bal;
    }
Im not 100% sure what exactly is wrong, so its hard to put a search into Google to find the answer! :?

Any help will be much appreciated.. Than you!
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: How to use sql query result in a web service?

Post by Trebor29 » Fri Feb 17, 2012 3:38 am

Quick update:

I have now managed to get the "AccountBalance" and store it into a dataSet.. but I still cannot get my hands on the numerical (Type) value, in this case its 5000.00, to be able to deduct the payAmount and return the updated 'balance' to the db.

I have tried Convert.ToDouble() / double.Parse() / .ToString() but it doesnt like it!

Does anyone know how I can do this? Thank you!

Code: Select all

[WebMethod] // Check for funds
    public DataSet checkAccBalance(string cn, double paySum)
    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TheBankDBConnectionString"].ConnectionString);
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand("SELECT AccountBalance FROM BankAccTable WHERE CardNumber= '"+ cn +"'", con);
        adapter.Fill(ds, "BankAccTable");

        string balance = ds.ToString();
        if (double.Parse(balance) >= paySum)
        {
            double newBalance = double.Parse(balance) - paySum;

        }
        
        return ds;  
    }

Do I need to do a foreach?

Code: Select all

foreach (DataRow dr in DataSet ds)
{
     // There will only be one recored
       double balance = double.Parse(dr[0]); OR dr[0].ToDouble();  // Im just guessing now!  :!: 
}
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: How to use sql query result in a web service?

Post by Trebor29 » Fri Feb 17, 2012 5:16 pm

Anyone interested, this works:

double balance = Convert.ToDouble(ds.Tables[0].Rows[0]["AccountBalance"].ToString());
User avatar
Saman
Lieutenant Colonel
Lieutenant Colonel
Posts: 828
Joined: Fri Jul 31, 2009 10:32 pm
Location: Mount Lavinia

Re: How to use sql query result in a web service?

Post by Saman » Fri Feb 17, 2012 11:18 pm

Okay... good to see you got it sorted out and reported as well. Well done!!!
Try and try, one day you will fly ;)
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

Re: How to use sql query result in a web service?

Post by Trebor29 » Fri Feb 17, 2012 11:29 pm

And that day can't come soon enough....! :yahoo:
Post Reply

Return to “ASP & ASP.Net”