public string DoSavePurchaseInvoiceInRemote(DsPurchase.PURINVMAINRow mainrow, DsPurchase.PURINVDETAILDataTable purinvdetail) { var dm = new DataManager(); string retval="0"; SqlTransaction tran = null; //var possql = new SqlAsyncConnectionString(); var cn = new SqlConnection(_constr); try { cn.Open(); tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted); var ds = new DsPurchase(); var dt = ds.PURINVMAIN; var dtdetail = ds.PURINVDETAIL; var taPurMain = new PURINVMAINTableAdapter(); var taPurDetail = new PURINVDETAILTableAdapter(); var taapar = new LedgersTableAdapters.APARMAINTableAdapter(); var taledger = new LedgersTableAdapters.LEDGERSTableAdapter(); taPurMain.Connection.ConnectionString = _constr; taPurMain.FillByPurid(dt, mainrow.PURINVID); // Assaign Transaction taPurMain.AssignConnection(cn, tran); taPurDetail.AssignConnection(cn, tran); taapar.AssignConnection(cn, tran); taledger.AssignConnection(cn, tran); DsPurchase.PURINVMAINRow dr = null; if (dt.Rows.Count == 0) { dr = (DsPurchase.PURINVMAINRow) dt.NewRow(); dr.PURINVID = mainrow.PURINVID; dr.USERID = mainrow.USERID; dr.COMPANY = mainrow.COMPANY; dr.UDATE = mainrow.UDATE; if (!mainrow.IsEDATENull()) dr.EDATE = mainrow.EDATE; if (!mainrow.IsEUSERIDNull()) dr.EUSERID = mainrow.EUSERID; dr.SUPID = mainrow.SUPID; dr.SUPNAME = mainrow.SUPNAME; dr.INVDATE = mainrow.INVDATE; dr.INVNO = mainrow.INVNO; dr.DUEDAYS = mainrow.DUEDAYS; dr.SPDISCOUNT = mainrow.SPDISCOUNT; dr.WHID = mainrow.WHID; dr.ADJVALUE = mainrow.ADJVALUE; dr.NETVALUE = mainrow.NETVALUE; dr.HOLD = mainrow.HOLD; dt.Rows.Add(dr); taPurMain.Update(dt); //LEDGER taapar.Insert("S", dr.SUPID, mainrow.UDATE, dr.INVDATE, mainrow.PURINVID, dr.INVNO, dr.NETVALUE, 0, dr.NETVALUE, Utils.Company, 1, Utils.Userid, 0); taledger.Insert("S", dr.SUPID, DateTime.Now.Date, dr.INVDATE, mainrow.PURINVID, dr.INVNO, 0,dr.NETVALUE, 1, Utils.Company, Utils.Userid, "Purchase Invoice"); foreach (DsPurchase.PURINVDETAILRow ditemdetarow in purinvdetail) { var newdetrow = dtdetail.NewRow(); foreach (DataColumn dc in purinvdetail.Columns) { if (!dc.ReadOnly) { newdetrow[dc.ColumnName] = ditemdetarow[dc.ColumnName]; } } dtdetail.Rows.Add(newdetrow); } taPurDetail.Update(dtdetail); } else { dr = dt.FindByPURINVID(mainrow.PURINVID); var cmd = "UPDATE [PURINVMAIN] SET SUPID = '" + dr.SUPID + "' ,SUPNAME = '" + dr.SUPNAME + "', INVNO = '" + dr.INVNO + "' ,INVDATE = '" + dr.INVDATE.ToString("yyyy-MM-dd HH:mm:ss") + "', DUEDAYS] = " + dr.DUEDAYS + " WHERE PURINVID ='" + dr.PURINVID + "'"; var sqlcmd2 = new SqlCommand(); sqlcmd2.Connection = cn; sqlcmd2.Transaction = tran; sqlcmd2.CommandText = cmd; cmd = "update APARMAIN set REFNO = '" + dr.SUPID + "',INVOICEDATE ='" + dr.INVDATE.ToString("yyyy-MM-dd HH:mm:ss") + "',invoiceno= '" + dr.INVNO + "' where SOURCEID=1 and TYPE='S' AND VOUCHERNO='" + dr.PURINVID + "'"; var sqlcmd = new SqlCommand(); sqlcmd.Connection = cn; sqlcmd.Transaction = tran; sqlcmd.CommandText = cmd; sqlcmd.ExecuteNonQuery(); cmd = "update LEDGERS set REFNO = '" + dr.SUPID + "',INVOICEDATE = '" + dr.INVDATE.ToString("yyyy-MM-dd HH:mm:ss") + "' ,invoiceno= '" + dr.INVNO + "' where SORUCE =1 and LEDGERTYPE='S' AND VOUCHERNO= '" + dr.PURINVID + "'"; var sqlcmd1 = new SqlCommand(); sqlcmd1.Connection = cn; sqlcmd1.Transaction = tran; sqlcmd1.CommandText = cmd; sqlcmd1.ExecuteNonQuery(); } tran.Commit(); retval = "1"; } catch (Exception ex) { if (cn != null) { if (tran != null) tran.Rollback(); } retval = ex.ToString(); } return retval; }