none
How to generate invoice by fetching from database c#

    Question

  • Helllo experts, I am doing an app which will generate invoices by fetching from database SQL.
    Here see What I did :

    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[5] {
                            new DataColumn("ProductId", typeof(string)),
                            new DataColumn("Product", typeof(string)),
                            new DataColumn("Price", typeof(int)),
                            new DataColumn("Quantity", typeof(int)),
                            new DataColumn("Total", typeof(int))});
    dt.Rows.Add(101, "Sun Glasses", 200, 5, 1000);
    dt.Rows.Add(102, "Jeans", 400, 2, 800);
    dt.Rows.Add(103, "Trousers", 300, 3, 900);
    dt.Rows.Add(104, "Shirts", 550, 2, 1100);

    So here As you can see we have Product, Price of the product , the quantity and the total (Price * Quantity)
    So my aim is for example 
     
    Invoice 1 I want inside it the total price of 1 500 

    so here my need is that how can we fetch from database so that it will automatically fill the right amount that i need.

    INVOICE  1 : TOTAL 1 500 USD 

    Product1 :  Shirts - 550 -2 - 1100
    Product2:  Jeans - 400 - 1- 400
    Total = 1500 (1100+400)
    And it  automatically removes the quantity of products taken in the database.

    Is it possible ?

    N.B: the products are from the stock !

    Thank you.

    Monday, February 5, 2018 9:44 PM

All replies

  • Hello,

    Add a DataColumn with an Expression property to take quantity * Price to get the total for a row. Then you can get the sum for all rows via lambda e.g.

    dt.AsEnumerable().Select(row => row.Field<int>("ExpressionColName")).Sum()

    I wrote the above w/o Visual Studio but should be fine. "ExpressionColName" is the name of the column which you add that has the Expression as mentioned above. This column would be added prior to populating data from the SQL database table or even afterwards. 

    Hopefully this makes sense.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 5, 2018 10:01 PM
    Moderator
  • the rows are added randomly. I give only the amount and the invoice is filling automatically and randomly using the stock.
    Monday, February 5, 2018 10:19 PM
  • the rows are added randomly. I give only the amount and the invoice is filling automatically and randomly using the stock.

    Why would random have anything to do with a invoice in regards to using a DataColumn Expression? The Total column would be the column with the Expression. Random or not random this will work unless there is something you have not mentioned.

    If there was more than one invoice then you would need to have a primary key to identify the invoice which you don't currently have from what I see.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 5, 2018 10:54 PM
    Moderator
  • Okay, I have Visual Studio available now.

    The following uses TeamBaseLibrary as a foundation for some of the work, it's located in this MSDN code sample.

    For the code that follows I'm using the product table from Microsoft NorthWind database. An important field is UnitsInStock, this permits us to know what's available, if we hit ReorderLevel (this field indicates it's time to do a reorder) value then we not only subtract if there are enough items for the current order we also know it's time to reorder this product.

    Here is the form code for a start up. a quantity is entered, we check the UnitsInStock, if there are enough we then proceed to update the UnitsInStock (which at this stage is not completed, that is up to you if you want to go this routes even).

    using System;
    using System.Data;
    using System.Windows.Forms;
    using SqlDataOperations;
    
    namespace WindowsFormsApp1
    {
        public partial class DemoForm : Form
        {
            public DemoForm()
            {
                InitializeComponent();
                Shown += DemoForm_Shown;
            }
    
            private Operations ops = new Operations();
            private BindingSource bsProducts = new BindingSource();
    
            private void DemoForm_Shown(object sender, EventArgs e)
            {
                bsProducts.DataSource = ops.LoadProducts();
                listBox1.DataSource = bsProducts;
                listBox1.DisplayMember = "ProductName";
    
            }
            private void button1_Click(object sender, EventArgs e)
            {
                int qty = 0;
                if (int.TryParse(textBox1.Text,out qty))
                {
                    var id = ((DataRowView)bsProducts.Current).Row.Field<int>("ProductId");
                    if (ops.ProductAvailable(id,qty))
                    {
                        if (ops.UpdateProduct(id, qty))
                        {
                            // add current item to a container such as 
                            // a DataTable
                        }
                    }
                    else
                    {
                        MessageBox.Show("No");
                    }
                }
            }
        }
    }
    

    Then here is a running start at the data operations

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using TeamBaseLibrary;
    
    namespace SqlDataOperations
    {
        public class Operations : BaseSqlServerConnections
        {
            public Operations()
            {
                DefaultCatalog = "NorthWindDemo";
            }
            public DataTable LoadProducts()
            {
                mHasException = false;
    
                var dt = new DataTable();
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                    {
                        try
                        {
                            cmd.CommandText = "SELECT ProductID,ProductName,UnitPrice FROM dbo.Products;";
    
                            cn.Open();
    
                            dt.Load(cmd.ExecuteReader());
                            dt.Columns["ProductID"].ColumnMapping = MappingType.Hidden;
    
                       }
                        catch (Exception ex)
                        {
                            mHasException = true;
                            mLastException = ex;
                        }
                    }
                }
    
                return dt;
            }
            public bool UpdateProduct(int pIdentifier, int pQty)
            {
                /*
                 * Here you would subtract the UnitsInStock by pQty
                 */
                return true;
            }
            public bool ProductAvailable(int pIdentifier, int pQty)
            {
                mHasException = false;
                bool available = false;
    
                using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                    {
                        try
                        {
                            cmd.CommandText = "SELECT UnitsInStock FROM dbo.Products WHERE ProductID = @Id;";
                            cmd.Parameters.AddWithValue("@id", pIdentifier);
    
                            cn.Open();
    
                            var availableQty = Convert.ToInt16(cmd.ExecuteScalar());
    
                            if ((availableQty - pQty) < 0)
                            {
                                available = false;
                            }
                            else
                            {
                                available = true;
                            }
                        }
                        catch (Exception ex)
                        {
                            mHasException = true;
                            mLastException = ex;
                        }
                    }
                }
    
                return available;
            }
        }
    }
    

    In closing

    I've given you a solid foundation yet there are holes in it (may be)

    Let's say there is multiple invoices done at once by different clerks and both at the same time order the same product there can be an issue in that both get to order but there is not enough stock. This means more logic is needed e.g. locking the item in the database for said units until the invoice has been completed.

    There are other ways to do this but that other methods are not any easier unless you know Entity Framework then there would be less lines of code but still the same logic to contend with.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 6, 2018 12:37 AM
    Moderator
  • Thank you , I will work on it ! 
    Friday, February 9, 2018 8:39 PM
  • DataTable here is not already in the invoice.
    This is in the stock and now I will use it to store it in the invoice because of that I said I give the total amount and automatically it will fill the invoice with the right items , quantity and price according to the total amount
    Friday, February 9, 2018 8:53 PM
    • I give one amount and I get a random invoice using the stock available ! 

    Friday, February 9, 2018 9:09 PM