0

I'm developing a desktop application in C# with a SQL Server 2005 backend.

I want to insert rows into two tables, in table1 one row will be saved and in table2 more than 100 rows will save at a time.

I want that while saving is going on and between it if power fails or due to any reason program terminates then that incomplete transaction should not saved in the database; but if transaction completes successfully, then it should be saved to the database.

I'm using Connection.BeginTransaction() methods

 clsData dSave = new clsData();
 dSave.Open(); // 
 System.Data.SqlClient.SqlTransaction tr = dSave.Connection.BeginTransaction();

clsData is a class in which some variables are declared like SqlConnection, SqlCommand, SqlAdapter, a var called Connection is declared in this class and a method Open() is declared in this class which opens connection.

Difficulty is that after some 10-20 commands, tr.Connection suddenly becomes null, where as my connnection is still open.

Anybody can help me about this problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Haider Ali Wajihi
  • 2,756
  • 7
  • 51
  • 82

1 Answers1

3
  • Use a stored procedure call
  • Use XML or table valued parameters to pass in the data
  • Manage the transaction in SQL Server

Don't make 100+ call to the database server in a client side transaction

gbn
  • 422,506
  • 82
  • 585
  • 676
  • thank you for your valuable feeb back but my problem is that i cant use stored procedure, because my application is desktop application and we doesn't want to publish our code, from which user can understand our datastructure – Haider Ali Wajihi Aug 23 '11 at 08:38
  • @Haider Ali: All code can be reversed engineered. Anyway, do it how you want: you didn't give enough information for us to help you. I told you how to do it *properly* – gbn Aug 24 '11 at 07:04