public void Create()
{
var x = System.Configuration.ConfigurationManager.
ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(x);
DataBase.SqlParm sqlParm = new DataBase.SqlParm();
con.Open();
SqlTransaction t1;
t1 = con.BeginTransaction();
try
{
var querry = @"insert into SaleInvoice(Date,InvoiceTypeId,LocationId,PartyId,TotalAmount)values(@dt,@tid,@loc,@pid,@cb);select SCOPE_IDENTITY()
";
sqlParm.Add("dt", Date);
sqlParm.Add("tid", InvoiceTypeId);
sqlParm.Add("loc", LocationId);
sqlParm.Add("pid", PartyId);
sqlParm.Add("cb", CalculateBill());
var cmd = getsqlcommand(querry, sqlParm.GetParmList(), con, t1);
SaleInvoiceId = int.Parse(cmd.ExecuteScalar().ToString());
InsertList(con, t1);
t1.Commit();
}
catch (Exception)
{
t1.Rollback();
}
con.Close();
}
private void InsertList(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
var querry = @"
INSERT INTO [dbo].[SaleInvoiceDetail]
([SaleInvoiceId]
,[ItemId]
,[Qty]
,[SalePrice]
,[Total])
VALUES
(@SaleInvoiceId,@ItemId,@Qty,@SalePrice,@Total)";
foreach (var x in SaleInvoiceDetails)
{
var parm = new DataBase.SqlParm();
parm.Add("@SaleInvoiceId", SaleInvoiceId);
parm.Add("@ItemId", x.ItemId);
parm.Add("@Qty", x.Qty);
parm.Add("@SalePrice", x.SalePrice);
parm.Add("@Total", (Decimal)x.Qty * x.SalePrice);
getsqlcommand(querry, parm.GetParmList(), sqlConnection, sqlTransaction).ExecuteNonQuery();
}
}
private void UpdateList(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
var newitems = new List <SaleInvoiceDetail>();
var querry = @"UPDATE [dbo].[SaleInvoiceDetail]
SET
[ItemId] = @ItemId
,[Qty] = @Qty
,[SalePrice] = @SalePrice
,[Total] = @Total
WHERE SaleInvoiceId=@SaleInvoiceId and SaleInvoiceDetailId=@SaleInvoiceDetailId";
var querry2 = "delete from SaleInvoiceDetail where SaleInvoiceDetailId=@SaleInvoiceDetailId and SaleInvoiceId=@SaleInvoiceId";
foreach (var x in SaleInvoiceDetails)
{
var parm = new DataBase.SqlParm();
if (x.Isdeleted)
{
parm.Add("@SaleInvoiceId", SaleInvoiceId);
parm.Add("@SaleInvoiceDetailId", x.SaleInvoiceDetailId);
getsqlcommand(querry2, parm.GetParmList(), sqlConnection, sqlTransaction).ExecuteNonQuery();
}
else if (x.SaleInvoiceDetailId == 0)
{
newitems.Add(x);
}
else
{
parm.Add("@ItemId", x.ItemId);
parm.Add("@Qty", x.Qty);
parm.Add("@SalePrice", x.SalePrice);
parm.Add("@Total", (Decimal)x.Qty * x.SalePrice);
parm.Add("@SaleInvoiceId", SaleInvoiceId);
parm.Add("@SaleInvoiceDetailId", x.SaleInvoiceDetailId);
getsqlcommand(querry, parm.GetParmList(), sqlConnection, sqlTransaction).ExecuteNonQuery();
}
}
if (newitems.Count > 0)
{
InsertList(newitems, sqlConnection, sqlTransaction);
newitems = null;
}
}
public void Update()
{
string querry = @"UPDATE [dbo].[SaleInvoice]
SET[Date] =@dt
,[InvoiceTypeId] =@tid
,[LocationId] = @loc
,[PartyId] = @pid
,[TotalAmount] = @cb
WHERE SaleInvoiceId = @id";
var x = System.Configuration.ConfigurationManager.
ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(x);
DataBase.SqlParm sqlParm = new DataBase.SqlParm();
con.Open();
SqlTransaction t1;
t1 = con.BeginTransaction();
try
{
sqlParm.Add("dt", Date);
sqlParm.Add("tid", InvoiceTypeId);
sqlParm.Add("loc", LocationId);
sqlParm.Add("pid", PartyId);
sqlParm.Add("id", SaleInvoiceId);
sqlParm.Add("cb", CalculateBill());
var cmd = getsqlcommand(querry, sqlParm.GetParmList(), con, t1);
cmd.ExecuteNonQuery();
UpdateList(con, t1);
t1.Commit();
}
catch (Exception)
{
t1.Rollback();
}
con.Close();
}