How to avoid jam in windows application using sql server

Question

1.00/5 (2 votes)

See more:

Hi

I have created one business application in c#.net and I am using in server containing

multiple users. While creating the invoices by all the users once at a time it has been

taken more time and sometimes application getting not responding condition.

Note: When one user created on invoice no then automatically that will be incremented

saving the other user with incremented invoice no.

would you please suggest me how to overcome this problem.

My Code for creating Invoice

C#

{


               string strTran = "";
               strTran = cfs.getTRNumber("INV", cpnyid);
               ddl_Inv.Text = strTran;

               if (txt_Cus_Name.Text == "")
               {
                   MessageBox.Show("Please select Customer.", Properties.Settings.Default.ProductName.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                   txt_Cus_Name.Focus();
                   return;
               }
               else if (dataGridViewEx1.Rows.Count < 1)
               {
                   MessageBox.Show("Please add Products to save your order.", Properties.Settings.Default.ProductName.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                   return;
               }

               else
               {

                   //20150707dav
                   if (ddl_Inv.Text.Length < 9)
                   {
                   
                   sqlstr = "select INV_NO from " + tablename + " where INV_NO='" + ddl_Inv.Text + "' and Deleted=0 ";
                       if (cfs.checktrno(sqlstr) == false)
                   {

                       strTran = cfs.getTRNumber("INV", cpnyid);
                       //MessageBox.Show("Invoice: " + ddl_Inv.Text + " already existed in database." +
                       //    "\r\n[Updating Invoice With New " + strTran + " save again]", Properties.Settings.Default.ProductName.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                       ddl_Inv.Text = strTran;
                       if (Basic.Default.ismulno == false)
                       {
                           cmd.CommandText = "update rstockno set RS_INV_NO='" + Properties.Settings.Default.trno.ToString() + "' where ID='" + cpnyid + "'";
                           cmd.ExecuteNonQuery();
                           cn.Close();
                       }
                       if (CheckForPrice(dataGridViewEx1).Count > 0)
                       {
                           string price = "";
                           foreach (string str in CheckForPrice(dataGridViewEx1))
                           {
                               price += str + ",";
                           }
                           DialogResult result = MessageBox.Show("Price can not be '0.00'.Do you want to proceed? This will effect the costing", "Warning",
                                                 MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                           if (result == DialogResult.No)
                           {
                               return;
                           }
                           else
                           {
                               save();
                               txt_Quo_ExtRema.Enabled = true;
                               lsPrice();
                               lsPrice2();
                           }
                       }
                       //ddl_Inv.Text = "";
                   }
                   else
                   {
                       save();
                       lsPrice();
                       lsPrice2();
                       //txt_Quo_ExtRema.Enabled = true;
                   }
               }
           }
           catch { if(!dr.IsClosed==true)dr.Close(); }
           Properties.Settings.Default.RowCount = 0;

       }

In save method I have written insert query of invoice

Posted 16-Jul-15 18:53pm

TarunKumarSusarapu

Updated 16-Jul-15 19:43pm

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 17-Jul-15 1:05am

Well, drink your tea with jam away from your computer keyboard. :-)
—SA

TarunKumarSusarapu 17-Jul-15 1:22am

Dude this is not the joke can you please tell me how to optimise sql server database

Sergey Alexandrovich Kryukov 17-Jul-15 1:38am

Why?
—SA

PIEBALDconsult 17-Jul-15 1:18am

Hire a DBA.

TarunKumarSusarapu 17-Jul-15 1:22am

we don't have that much of resources to hire DBA Please tell me how to optimise database traffic

PIEBALDconsult 17-Jul-15 1:26am

No, I can't without studying your code and schema; no one can. And you haven't even indicated which database system you use so we can't even tell you what tools to use.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-16T19:28:00

Solution 1

From the description I suspect that you may have one or two main problems.

The first one is perhaps easier, lack of sufficient indexes. The database structure should be verified by a person who
- has the knowledge how a database is optimized
- has (or is provided) sufficient knowledge of the application behaciour in order to estimate common use cases for the tables.

The second one, which is quite probable, is that you have a locking problem. For example when the new invoice is created it sounds you get a new number form a table. Now if this table is locked for the duration of the invoice insertion you end up in a situation where this table becomes a bottleneck.

If this is the case, to overcome the problem you need to change the transaction behaviour of the program; Make the time of the lock being active significantly lower, or change the way you create a new number. This problem may require extensive changes in the application.

Posted 16-Jul-15 19:28pm

Wendelius

Comments

TarunKumarSusarapu 17-Jul-15 1:40am

I agree with you brother but here i have taken two different tables for Inserting and getting new invoice no. Here I need to change the transaction behaviour as you said or creating temp table or cache. How to create cache between sql and c#?

Wendelius 17-Jul-15 2:09am

I wouldn't create a cache or anything like that. Simply isolate the getting of the new invoice number to it's own transaction or let the database generate the value in which case you don't need the extra table at all.

TarunKumarSusarapu 17-Jul-15 2:22am

would you please elaborate it?

Wendelius 17-Jul-15 2:31am

What is the question you have in mind? Something concerning the transaction isolation or database generated key values or something else?

TarunKumarSusarapu 17-Jul-15 2:37am

what is the techniques to isolate the database at the front end?

Wendelius 17-Jul-15 2:41am

You don't isolate the database in the front end. What I mean is that you should probably have a transaction logic like
- start transaction
- fetch a key for the invoice
- commit/rollback the transaction
- start a new transaction
- insert invoice data (and other relevant data)
- commit/rollback transaction

TarunKumarSusarapu 17-Jul-15 2:46am

Same I did here but it has been taken more time to create invoice in the server for multiple users
at a single time clicking the save button