public class AccountInfo
{
public string accountNumber { get; set; }
public string balance { get; set; }
public int interestRate { get; set; }
public string accountName { get; set; }
public string accountType { get; set; }
}
sir since scalar value returns only first column from the first row.I changed the code to test following a tutorial using ExecuteReader since i want all the objects.
public static string GetAccountInfo(string accountNumber)
{
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User
Id=postgres; " + "Password=pes;Database=pmc;");
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM
sms.get_accounts_info(@AccountNumber); ", conn);
command.Parameters.AddWithValue("@AccountNumber", accountNumber);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
Console.Write("{0}\t{1} \n", dr[0], dr[1]);
return dr.ToString();
}
That makes sense as you are writing to the console and still not populating AccountInfo except for assigning SqlDataReader name to the accountNumber property.
I suggest that you learn how to use the Visual Studio debugger to single step through the code so you can find these logic bugs.
Anyway, you function should look more like this... I don't know if this code will work as I can;t see your DB schema. Use the debugger to troubleshoot.
public static List<AccountInfo> GetAccountInfo(string accountNumber)
{
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User
Id=postgres; " + "Password=pes;Database=pmc;");
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM
sms.get_accounts_info(@AccountNumber); ", conn);
command.Parameters.AddWithValue("@AccountNumber", accountNumber);
NpgsqlDataReader dr = command.ExecuteReader();
List<AccountInfo> items = new List<AccountInfo>();
while (dr.Read())
{
AccountInfo item = new AccountInfo()
{
accountNumber = (string)dr[0];
balance = (string)dr[1];
interestRate = (int)dr[2];
accountName = (string)dr[3];
accountType = (string)dr[4];
}
items.Add(item);
}
return items;
}
It look like you have other logical errors. For example, interestRate is a decimal not an int and balance should also be a decimal as well.
Here is the whole scene sir.This is my plsql function :
CREATE OR REPLACE FUNCTION sms.get_accounts_info(IN account_number_ character varying)
RETURNS TABLE(account_number character varying, account_name text, product character varying, interest_rate numeric, balance money) AS
$BODY$
BEGIN
RETURN QUERY(
SELECT a.account_number,
c.customer_name,
p.deposit_product_name,
a.interest_rate::numeric, deposit.get_balance(account_number_)
FROM deposit.account_holders a
JOIN core.customers_view c ON a.customer_id = c.customer_id
JOIN core.deposit_products p ON a.deposit_product_id = p.deposit_product_id
WHERE a.account_number = $1
);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION sms.get_accounts_info(character varying)
OWNER TO postgres;
public class AccountInfo
{
public string accountNumber { get; set; }
public string balance { get; set; }
public int interestRate { get; set; }
public string accountName { get; set; }
public string accountType { get; set; }
}
In Business Layer - BusinessLayer.Api.AccountHolderApi.GetAccountInfo
public static string GetAccountInfo(string accountNumber)
{
return DatabaseLayer.Api.AccountHolderApi.GetAccountInfo(accountNumber);
}
And Database Layer
public static string GetAccountInfo(string accountNumber)
{
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=postgres; " + "Password=pes;Database=pmc;");
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber); ", conn);
command.Parameters.AddWithValue("@AccountNumber", accountNumber);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
Console.Write("{0}\t{1} \n", dr[0], dr[1]);
return dr.ToString();
}
I am using that Console.Write following a tutorial just to test if that works.though nothing showed up in console.i tried dr.ToString() because the message said CANNOT IMPLICITLY CONVERT TYPE NpgSql.NpgSqlDataReader to 'string' when i tried to return
just return dr
And the URI i am pointing to in postman
http://localhost:8080/v1/accounts/info
About debugging can we fire the debugger on postman send request?
I am glad expert like you is looking into my problem.
I am using that Console.Write following a tutorial just to test if that works.though nothing showed up in console.i tried dr.ToString() because the message said CANNOT IMPLICITLY CONVERT TYPE NpgSql.NpgSqlDataReader to 'string' when i tried to return
just return dr
You do NOT want to return a string. You want to return an object or a collection of objects as shown in my previous post.
At this point, it seems that you don't understand basic language constructs related to objects and populating objects. You need to understand how to populate an object and how to pass an object. Otherwise you will continue to struggle.
Keep in mind that you still have not provided the schema which is how the data is id defined in your database.
Member
29 Points
97 Posts
Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 06:24 AM|tarun02|LINK
Hello
I am learning asp.net webapi and trying to get values for different object.I have a function called
SELECT * FROM test.get_accounts_info('CS-01')which returns 5 columns.
- account_number [value- CS-001]
- account_name [Sam]
- product [Test]
- interest_rate [1]
- balance [1000]
Now in my controller i am passing an object of AccountInfo Class.
[HttpPost] [ActionName("info")] public IHttpActionResult GetAccountInfo([FromBody]AccountInfo accountinfo) { accountinfo.accountNumber = BusinessLayer.Api.AccountHolderApi.GetAccountInfo(accountinfo.accountNumber); return Ok(accountinfo); }The AccountInfo Class
public class AccountInfo { public string accountNumber { get; set; } public string balance { get; set; } public int interestRate { get; set; } public string accountName { get; set; } public string accountType { get; set; } }My Route
config.Routes.MapHttpRoute("test", "v1/{controller}/{action}");And the Db Layer code from where i am calling the function
` public static string GetAccountInfo(string accountNumber) { var sql = "SELECT * FROM test.get_accounts_info(@AccountNumber);"; using (var command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@AccountNumber", accountNumber); return Common.Conversion.TryCastString(DBOperations.GetScalarValue(command)); } }`Now in postman when i pass the value from the body like this
{"accountNumber": "CS-001"}In my response i get this
{ "accountNumber": "CS-0000001", "balance": null, "interestRate": 0, "accountName": null, "accountType": null }Where i am wrong?Any Help Please.
Participant
1360 Points
608 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 01:27 PM|JBetancourt|LINK
in your select you are using 'CS-01'
in your postman you are using 'CS-001'
is that it?
Please remember to click "Mark as Answer" the responsES that resolved your issue.
Member
29 Points
97 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 01:32 PM|tarun02|LINK
no sir
All-Star
30661 Points
11156 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 02:01 PM|mgebhard|LINK
First, you are passing a AccountInfo type with only the accountinfo property set.
The DB operation is returning a scalar value.
The scalar value replaces the original property value of "CS-001" with "CS-0000001". The object is passed back.
As far as I can tell, the code is working as written. What results are you expecting?
Member
29 Points
97 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 05:13 PM|tarun02|LINK
sir since scalar value returns only first column from the first row.I changed the code to test following a tutorial using ExecuteReader since i want all the objects.
public static string GetAccountInfo(string accountNumber) { NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=postgres; " + "Password=pes;Database=pmc;"); conn.Open(); NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber); ", conn); command.Parameters.AddWithValue("@AccountNumber", accountNumber); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) Console.Write("{0}\t{1} \n", dr[0], dr[1]); return dr.ToString(); }I am getting output as :
{ "accountNumber": "Npgsql.ForwardsOnlyDataReader", "balance": null, "interestRate": 0, "accountName": null, "accountType": null }All-Star
30661 Points
11156 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 06:00 PM|mgebhard|LINK
That makes sense as you are writing to the console and still not populating AccountInfo except for assigning SqlDataReader name to the accountNumber property.
I suggest that you learn how to use the Visual Studio debugger to single step through the code so you can find these logic bugs.
https://msdn.microsoft.com/en-us/library/y740d9d3.aspx
Anyway, you function should look more like this... I don't know if this code will work as I can;t see your DB schema. Use the debugger to troubleshoot.
public static List<AccountInfo> GetAccountInfo(string accountNumber) { NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=postgres; " + "Password=pes;Database=pmc;"); conn.Open(); NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber); ", conn); command.Parameters.AddWithValue("@AccountNumber", accountNumber); NpgsqlDataReader dr = command.ExecuteReader(); List<AccountInfo> items = new List<AccountInfo>(); while (dr.Read()) { AccountInfo item = new AccountInfo() { accountNumber = (string)dr[0]; balance = (string)dr[1]; interestRate = (int)dr[2]; accountName = (string)dr[3]; accountType = (string)dr[4]; } items.Add(item); } return items; }It look like you have other logical errors. For example, interestRate is a decimal not an int and balance should also be a decimal as well.
SqlDataReader reference
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader
C# programming guide as it pertains to objects and object creation.
https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/
Member
29 Points
97 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 06:11 PM|tarun02|LINK
Here is the whole scene sir.This is my plsql function :
CREATE OR REPLACE FUNCTION sms.get_accounts_info(IN account_number_ character varying) RETURNS TABLE(account_number character varying, account_name text, product character varying, interest_rate numeric, balance money) AS $BODY$ BEGIN RETURN QUERY( SELECT a.account_number, c.customer_name, p.deposit_product_name, a.interest_rate::numeric, deposit.get_balance(account_number_) FROM deposit.account_holders a JOIN core.customers_view c ON a.customer_id = c.customer_id JOIN core.deposit_products p ON a.deposit_product_id = p.deposit_product_id WHERE a.account_number = $1 ); END $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000; ALTER FUNCTION sms.get_accounts_info(character varying) OWNER TO postgres;And my controller :
[HttpPost] [ActionName("info")] public IHttpActionResult GetAccountInfo([FromBody]AccountInfo accountinfo) { accountinfo.accountNumber = BusinessLayer.Api.AccountHolderApi.GetAccountInfo(accountinfo.accountNumber); return Ok(accountinfo); }The AccountInfo Class
public class AccountInfo { public string accountNumber { get; set; } public string balance { get; set; } public int interestRate { get; set; } public string accountName { get; set; } public string accountType { get; set; } }In Business Layer - BusinessLayer.Api.AccountHolderApi.GetAccountInfo
public static string GetAccountInfo(string accountNumber) { return DatabaseLayer.Api.AccountHolderApi.GetAccountInfo(accountNumber); }And Database Layer
public static string GetAccountInfo(string accountNumber) { NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=postgres; " + "Password=pes;Database=pmc;"); conn.Open(); NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber); ", conn); command.Parameters.AddWithValue("@AccountNumber", accountNumber); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) Console.Write("{0}\t{1} \n", dr[0], dr[1]); return dr.ToString(); }I am using that Console.Write following a tutorial just to test if that works.though nothing showed up in console.i tried dr.ToString() because the message said CANNOT IMPLICITLY CONVERT TYPE NpgSql.NpgSqlDataReader to 'string' when i tried to return just return dr
And the URI i am pointing to in postman
About debugging can we fire the debugger on postman send request?
I am glad expert like you is looking into my problem.
Love from nepal.
Regards
All-Star
30661 Points
11156 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 06:28 PM|mgebhard|LINK
You do NOT want to return a string. You want to return an object or a collection of objects as shown in my previous post.
At this point, it seems that you don't understand basic language constructs related to objects and populating objects. You need to understand how to populate an object and how to pass an object. Otherwise you will continue to struggle.
Keep in mind that you still have not provided the schema which is how the data is id defined in your database.
Member
29 Points
97 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 09, 2017 06:58 PM|tarun02|LINK
Thank you sir.I am getting bald because of this.Thanks for your time.i will work hard.:D
Member
490 Points
282 Posts
Re: Retrieving more then one object with values from Db in asp.net webapi
Jun 12, 2017 06:44 AM|EvenMa|LINK
Hi tarun02,
That's so easy, here is the tutorial of Web API, you could learn how to use it by the following link.
https://www.asp.net/web-api
According to your description, I suggest you could return a object(that’s the AccountInfo which you defined) to instead the string.
See the following code:
(1) Method:GetAccountInfo()
public static AccountInfo GetAccountInfo(string accountNumber) { using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id = postgres; " + "Password = pes; Database = pmc; ")) { conn.Open(); NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber);", conn); command.Parameters.AddWithValue("@AccountNumber", accountNumber); var accountInfo = new AccountInfo(); using (NpgsqlDataReader dr = command.ExecuteReader()) { accountInfo.accountNumber = dr["account_number"].ToString(); accountInfo.balance = dr["balance"].ToString(); accountInfo.interestRate = Convert.ToInt32(dr["interest_rate"]); accountInfo.accountName = dr["interest_rate"].ToString(); //I couldn't find the property mapping column in reader of accountType accountInfo.accountType = ""; } return accountInfo; } }(2) Web API
[HttpPost] [ActionName("info")] public IHttpActionResult GetAccountInfo([FromBody]AccountInfo accountinfo) { return Ok(BusinessLayer.Api.AccountHolderApi.GetAccountInfo(accountinfo.accountNumber)); }If you have any other questions, please feel free to contact me any time.
Best Regards
Even