public static string LoadVoucher(string VoucherCode, string VoucherTypeCode, string Month, string Year, string BranchCode) { DBClass db = new DBClass("Accounts"); EditTransactionData etd = new EditTransactionData(); db.Con.Open(); db.Com.CommandText = String.Format("SELECT VoucherCode,VoucherTypeCode,BranchCode,CONVERT(varchar,[Date],105) AS [Date],Naration,Currency,ExchangeRate,ProjectCode FROM Vouchers WHERE VoucherCode={0} AND VoucherTypeCode='{1}' AND Month='{2}' AND Year='{3}' AND BranchCode='{4}'", VoucherCode, VoucherTypeCode, Month, Year, BranchCode); SqlDataReader sdr = db.Com.ExecuteReader(); if (sdr.HasRows) { while (sdr.Read()) { etd.VoucherCode = sdr[0].ToString(); etd.VoucherTypeCode = sdr[1].ToString(); etd.BranchCode = sdr[2].ToString(); etd.Date = sdr[3].ToString(); etd.Narration = sdr[4].ToString(); etd.Currency = sdr[5].ToString(); etd.ExchangeRate = sdr[6].ToString(); etd.ProjectCode = sdr[7].ToString(); } } if (sdr.IsClosed == false) { sdr.Close(); } db.Com.CommandText = String.Format("SELECT A.Accounts AS AccountCode,A.Note AS LineDescription,A.Debit/V.ExchangeRate AS Debit,A.Credit/V.ExchangeRate AS Credit,B.Description AS Description FROM VoucherDetail AS A INNER JOIN Vouchers AS V ON V.VoucherCode=A.VoucherCode INNER JOIN Account AS B ON A.Accounts=B.AccountCode WHERE A.VoucherCode={0} AND A.VoucherTypeCode='{1}' AND A.Month='{2}' AND A.Year='{3}' AND A.BranchCode='{4}'", VoucherCode, VoucherTypeCode, Month, Year, BranchCode); SqlDataAdapter da = new SqlDataAdapter(db.Com); DataTable dt = new DataTable(); da.Fill(dt); JavaScriptSerializer serializer = new JavaScriptSerializer(); List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >(); Dictionary <string, object> row; foreach (DataRow dr in dt.Rows) { row = new Dictionary <String, Object>(); foreach (DataColumn col in dt.Columns) { row.Add(col.ColumnName, dr[col]); } rows.Add(row); } etd.Rows = rows; db.Con.Close(); db = null; return(serializer.Serialize(etd)); }
public static string LoadVoucher(string VoucherCode, string VoucherTypeCode, string Month, string Year, string BranchCode) { DBClass db = new DBClass("Accounts"); EditTransactionData etd = new EditTransactionData(); db.Con.Open(); db.Com.CommandText = String.Format("SELECT VoucherCode,VoucherTypeCode,BranchCode,CONVERT(varchar,[Date],105) AS [Date],Naration,Currency,ExchangeRate,ProjectCode FROM Vouchers WHERE VoucherCode={0} AND VoucherTypeCode='{1}' AND Month='{2}' AND Year='{3}' AND BranchCode='{4}'", VoucherCode, VoucherTypeCode, Month, Year, BranchCode); SqlDataReader sdr = db.Com.ExecuteReader(); if(sdr.HasRows) { while (sdr.Read()) { etd.VoucherCode = sdr[0].ToString(); etd.VoucherTypeCode = sdr[1].ToString(); etd.BranchCode = sdr[2].ToString(); etd.Date = sdr[3].ToString(); etd.Narration = sdr[4].ToString(); etd.Currency = sdr[5].ToString(); etd.ExchangeRate = sdr[6].ToString(); etd.ProjectCode = sdr[7].ToString(); } } if (sdr.IsClosed == false) sdr.Close(); db.Com.CommandText = String.Format("SELECT A.Accounts AS AccountCode,A.Note AS LineDescription,A.Debit/V.ExchangeRate AS Debit,A.Credit/V.ExchangeRate AS Credit,B.Description AS Description FROM VoucherDetail AS A INNER JOIN Vouchers AS V ON V.VoucherCode=A.VoucherCode INNER JOIN Account AS B ON A.Accounts=B.AccountCode WHERE A.VoucherCode={0} AND A.VoucherTypeCode='{1}' AND A.Month='{2}' AND A.Year='{3}' AND A.BranchCode='{4}'", VoucherCode, VoucherTypeCode, Month, Year, BranchCode); SqlDataAdapter da = new SqlDataAdapter(db.Com); DataTable dt = new DataTable(); da.Fill(dt); JavaScriptSerializer serializer = new JavaScriptSerializer(); List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); Dictionary<string, object> row; foreach (DataRow dr in dt.Rows) { row = new Dictionary<String, Object>(); foreach (DataColumn col in dt.Columns) { row.Add(col.ColumnName, dr[col]); } rows.Add(row); } etd.Rows = rows; db.Con.Close(); db = null; return serializer.Serialize(etd); }