Use Sqlcommand instead

Question

User364663285 posted

Hi,
Further to the following example, can there be an example to use one SqlCommand to retrieve from database, instead of directly putting values to the product list?
https://forums.asp.net/t/2121515.aspx?Is+Gridview+Listview+a+good+option+to+this

Answer 1

User1068175894 posted

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        connection.Open();

        SqlCommand command = new SqlCommand(queryString, connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}", reader[0]));
        }
    }
}

Answer 2

User364663285 posted

Hi,
What can be the way to convert such codes

            return new List<ProductData>()
            {
                new ProductData() { Id = 1, ProductName = "Product 1", ProductPrice = 100 },
                new ProductData() { Id = 2, ProductName = "Product 2", ProductPrice = 200 },
                new ProductData() { Id = 3, ProductName = "Product 3", ProductPrice = 300 },
                new ProductData() { Id = 4, ProductName = "Product 4", ProductPrice = 400 },
                new ProductData() { Id = 5, ProductName = "Product 5", ProductPrice = 500 }
            };

into the same one using one SqlCommand?

Answer 3

User-1509636757 posted

Please check below modification in WebMethod on how you can connect to database, write query to fetch columns from a table and get DataTable with product data and returning a List.

Please also note that the example is a very basic one, and you may require to add connection string in your web.config (Please post if you need any further assistance). Also, I would encourage not to write inline SQL Query with SqlCommand but to use Sql Stored Procedure and use it with SqlCommand to fetch (or add/modify/delete data):

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Services;

namespace WebApp
{
    public partial class WebForm179 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        [WebMethod]
        public static List<ProductData> GetProductList()
        {
            List<ProductData> productList = new List<ProductData>();
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
            {
                using (SqlCommand sqlCmd = new SqlCommand("SELECT Id, ProductName, ProductPrice FROM ProductTable", sqlConn))
                {
                    sqlConn.Open();
                    sqlCmd.CommandType = CommandType.Text;
                    SqlDataAdapter sqlAd = new SqlDataAdapter(sqlCmd);
                    DataTable sqlDt = new DataTable();
                    sqlAd.Fill(sqlDt);
                    sqlConn.Close();
                    if (null != sqlDt)
                    {
                        productList = sqlDt
                                        .AsEnumerable()
                                        .Select(dataRow => new ProductData
                                        {
                                            Id = dataRow.Field<Int32>("Id"),
                                            ProductName = dataRow.Field<string>("ProductName"),
                                            ProductPrice = dataRow.Field<decimal>("ProductPrice"),
                                        })
                                        .ToList();
                    }
                }
            }
            return productList;
        }
    }

    public class ProductData
    {
        public int Id { get; set; }

        public string ProductName { get; set; }

        public decimal ProductPrice { get; set; }
    }
}

Reference links:

1. c# - How can I easily convert DataReader to List<T>?
2. c# - Convert DataSet to List
3. asp.net - Call a stored procedure with parameter in c#

Answer 4

User1068175894 posted

private static void CreateCommand(string connectionString)
{
    string queryString = "Select Id,ProductName, ProductPrice from ProductData";
    var products = new List<ProductData>();
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        connection.Open();

        SqlCommand command = new SqlCommand(queryString, connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            products.Add(new ProductData{
               Id = Int.Parse(reader[0].ToString());
               ProductName = reader[1].ToString();
               ProductPrice = Decimal.Parse(reader[2].ToString());
            })
            Console.WriteLine(String.Format("{0}", reader[0]));
        }
    }
    return products;
}

Answer 5

User364663285 posted

Hi Kausha,
Any advice to that I get "undefined" to price column on web page, with such codes?

            ...
                                        .Select(dataRow => new HouseData
                                        {
                                            house_name = dataRow.Field<string>("house_name"),
                                            address1 = dataRow.Field<string>("address1"),
                                            address2 = dataRow.Field<string>("address2"),
                                            price = dataRow.Field<decimal>("price"),
                                            price_curr = dataRow.Field<string>("price_curr"),
                                        })
                                        .ToList();
                                        ...

Answer 6

User-1509636757 posted

Any advice to that I get "undefined" to price column on web page, with such codes?

I do not see "undefined" anywhere on that page. If you are facing this, I suggest please run this (SELECT house_name, address1, address2, price, price_curr FROM house_tab) SQL Query in SQL Management Studio against your database and see if you are getting null value for price field on that record where you are getting undefined on page?

If this is true then you should check for undefined first before using the value for generating html. Here is an example:

$.each(response.d, function (index, house) {
                html += "<dl><dt>" + (typeof house.house_name != "undefined" ? house.house_name : "") + "</dt>";
                html += "<dd>" + (typeof house.address1 != "undefined" ? house.address1 : "") + "</dd>";
                html += "<dd>" + (typeof house.address2 != "undefined" ? house.address2 : "") + "</dd>";
                html += "<dd>" + (typeof house.price != "undefined" ? house.price : "") + "</dd>";
                html += "<dd>" + (typeof house.price_curr != "undefined" ? house.price_curr : "") + "</dd>";
                html += "</dl> ";
            });
            ...

Answer 7

User364663285 posted

Hi Kausha,
Can we further put more details to this thread, to show one picture file to each record and the picture file is also from database? I can have one new thread for this!

Answer 8

User-1509636757 posted

Logically, member should have a new thread per issue, with complete information. This will help to isolate issues as well as for the sake of future readers that may come with similar issues.