//To add data to the Invoice Table public bool InsertPaymentDetails(PaymentDetailsEntity objPay) { objCommand.Parameters.Clear(); bool ii = false; try { objCommand.CommandText = "sp_InsertPaymentRecord"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@transactionId", SqlDbType.VarChar).Value = objPay.TransactionId; objCommand.Parameters.Add("@invoiceId", SqlDbType.Int).Value = objPay.InvoiceId; objCommand.Parameters.Add("@clientName", SqlDbType.VarChar).Value = objPay.ClientName; objCommand.Parameters.Add("@paymentType", SqlDbType.VarChar).Value = objPay.PaymentType; objCommand.Parameters.Add("@currencyType", SqlDbType.VarChar).Value = objPay.CurrencyType; objCommand.Parameters.Add("@amount", SqlDbType.Decimal).Value = objPay.Amount; objCommand.Parameters.Add("@paymentStatus", SqlDbType.VarChar).Value =objPay.PaymentStatus; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); ii = DLCommand.ExecuteNonQuery(DLConfig); } catch (Exception) { throw; } return ii; }
public DataTable getAllNonDeletedInvoiceRecs(string cName, out int count) { try { objCommand.Parameters.Clear(); objCommand.CommandText = "sp_GetAllInvoiceRecords"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@ClientName", SqlDbType.VarChar, 10).Value = cName; SqlParameter totCount = objCommand.Parameters.Add("@totalRecs", SqlDbType.Int); totCount.Direction = ParameterDirection.Output; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType); count = Convert.ToInt32(totCount.Value); if (ds.Tables[0].Rows.Count != 0) { return ds.Tables[0]; } else { return null; } } catch (Exception) { throw; } }
public object ExecuteBatchUpdate(ConfigSettings configuration, DataSet ds) { Connection(configuration); ((IDbCommand)configuration.DBCommand).Connection = GenericConnection; switch (configuration.DataStore) { case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break; case DataProviderType.OleDb: GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break; case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break; } GenericDataAdapter.UpdateCommand = (IDbCommand)configuration.DBCommand; int i = GenericDataAdapter.Update(ds); GenericConnection.Close(); GenericConnection.Dispose(); return (object)i; }
public bool DeleteInvoiceRecs(string invoiceIds) { objCommand.Parameters.Clear(); bool ii = false; try { objCommand.CommandText = "sp_DeleteInvoiceRecords"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@invoiceIds", SqlDbType.VarChar).Value = invoiceIds; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); ii = DLCommand.ExecuteNonQuery(DLConfig); } catch (Exception) { throw; } return ii; }
public bool ExecuteNonQuery(ConfigSettings configuration) { try { Connection(configuration); ((IDbCommand)configuration.DBCommand).Connection = GenericConnection; ((IDbCommand)configuration.DBCommand).ExecuteNonQuery(); GenericConnection.Close(); return true; } catch (Exception Ex) { ////To add additional information in the exception //NameValueCollection colDataInfo = new NameValueCollection(); //colDataInfo.Add("Method","Unable to execute non query"); //BaseApplicationException exBase = new BaseApplicationException(Ex.Message,Ex); ////Addiding additional information to the exception //exBase.AdditionalInformation = colDataInfo; //throw exBase; return false; } }
public DataTable getAllNonDeletedInvoiceRecs_Pagewise(string cName, string orderBy, string dir, int pageIndex, int pageSize, out int count) { try { objCommand.Parameters.Clear(); objCommand.CommandText = "sp_GetAllNonDeletedInvoiceRecords_Pagewise"; objCommand.CommandType = CommandType.StoredProcedure; if(cName.ToLower()=="--select--") objCommand.Parameters.Add("@ClientName", SqlDbType.VarChar, 50).Value = DBNull.Value; else objCommand.Parameters.Add("@ClientName", SqlDbType.VarChar, 50).Value = cName; objCommand.Parameters.Add("@orderby", SqlDbType.VarChar, 50).Value = orderBy; objCommand.Parameters.Add("@dir", SqlDbType.VarChar, 50).Value = dir; objCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex; objCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize; SqlParameter totCount = objCommand.Parameters.Add("@totalRecs", SqlDbType.Int); totCount.Direction = ParameterDirection.Output; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType); count = Convert.ToInt32(totCount.Value); //if (ds.Tables[0].Rows.Count != 0) //{ return ds.Tables[0]; //} //else { return null; } } catch (Exception) { throw; } }
public bool UpdateInvoiceRecByPaidType(int invoiceId, string PaidVia) { objCommand.Parameters.Clear(); bool status = false; try { objCommand.CommandText = "sp_UpdateInvoiceRecordByPaidType"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@invoiceId", SqlDbType.Int).Value = invoiceId; objCommand.Parameters.Add("@PaidVia", SqlDbType.VarChar).Value = PaidVia; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); status = DLCommand.ExecuteNonQuery(DLConfig); } catch (Exception) { throw; } return status; }
//To add data to the Invoice Table public bool InsertInvoiceRec(InvoiceDetailsEntity objInvoice, out int PKid) { objCommand.Parameters.Clear(); bool ii = false; try { objCommand.CommandText = "sp_InsertInvoiceRecord"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@invoiceId", SqlDbType.VarChar).Value = objInvoice.InvoiceNo; objCommand.Parameters.Add("@invoiceDate", SqlDbType.DateTime).Value = objInvoice.InvoiceDate; objCommand.Parameters.Add("@clientName", SqlDbType.VarChar).Value = objInvoice.ClientName; objCommand.Parameters.Add("@clientAddress", SqlDbType.VarChar).Value = objInvoice.ClientAddress; objCommand.Parameters.Add("@emailAddress", SqlDbType.VarChar).Value = objInvoice.EmailAddress; objCommand.Parameters.Add("@NoOfEmails", SqlDbType.Int).Value = objInvoice.NoOfEmails; objCommand.Parameters.Add("@discountAmount", SqlDbType.Decimal).Value = objInvoice.DiscountAmount; objCommand.Parameters.Add("@taxPercentage", SqlDbType.Decimal).Value = objInvoice.TaxPercentage; objCommand.Parameters.Add("@taxAmount", SqlDbType.Decimal).Value = objInvoice.TaxAmount; objCommand.Parameters.Add("@priceRate", SqlDbType.Decimal).Value = objInvoice.PriceRate; objCommand.Parameters.Add("@subAmount", SqlDbType.Decimal).Value = objInvoice.SubAmount; objCommand.Parameters.Add("@totalAmount", SqlDbType.Decimal).Value = objInvoice.TotalAmount; objCommand.Parameters.Add("@note", SqlDbType.VarChar).Value = objInvoice.Note; objCommand.Parameters.Add("@status", SqlDbType.VarChar).Value = objInvoice.Status; objCommand.Parameters.Add("@startDate", SqlDbType.DateTime).Value = objInvoice.StartDate; objCommand.Parameters.Add("@endDate", SqlDbType.DateTime).Value = objInvoice.EndDate; objCommand.Parameters.Add("@description", SqlDbType.VarChar).Value = objInvoice.Descripation; objCommand.Parameters.Add("@OutPKid", SqlDbType.Int).Direction = ParameterDirection.Output; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); ii = DLCommand.ExecuteNonQuery(DLConfig); PKid = Convert.ToInt32(objCommand.Parameters["@OutPKid"].Value); } catch (Exception) { throw; } return ii; }
public bool InsertInvoiceDescription(List<InvoiceDetailsEntity> ListobjInvoice) { objCommand.Parameters.Clear(); bool ii = false; try { DataTable dtDesc = new DataTable("TableType_InsertInvoiceDesc"); dtDesc.Columns.Add("InvoiceId", typeof(int)); dtDesc.Columns.Add("InvoiceNo", typeof(string)); dtDesc.Columns.Add("ClientName", typeof(string)); dtDesc.Columns.Add("Description", typeof(string)); dtDesc.Columns.Add("SubAmount", typeof(decimal)); for (int i = 0; i < ListobjInvoice.Count; i++) { dtDesc.Rows.Add(ListobjInvoice[i].InvoiceId, ListobjInvoice[i].InvoiceNo, ListobjInvoice[i].ClientName, ListobjInvoice[i].Descripation, ListobjInvoice[i].SubAmount); } objCommand.CommandText = "sp_InsertInvoiceDescription"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@DT_Table", dtDesc); DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); ii = DLCommand.ExecuteNonQuery(DLConfig); } catch (Exception) { throw; } return ii; }
//Get From Level4 List public DataTable getSuperAdminUserName() { try { objCommand.Parameters.Clear(); objCommand.CommandText = "sp_getSuperAdminUserName"; objCommand.CommandType = CommandType.StoredProcedure; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType); if (ds.Tables[0].Rows.Count != 0) { return ds.Tables[0]; } else { return null; } } catch (Exception) { throw; } }
public int getLastInvoiceID() { objCommand.Parameters.Clear(); try { objCommand.CommandText = "sp_GetInvoiceId"; objCommand.CommandType = CommandType.StoredProcedure; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); int Count = (int)DLCommand.ExecuteQuery(DLConfig, ReturnType.ScalarType); return Count; } catch (Exception) { throw; } }
public DataTable getInvoiceRec(int invoiceId) { try { objCommand.Parameters.Clear(); objCommand.CommandText = "sp_GetInvoiceRecord"; objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@invoiceId", SqlDbType.Int).Value = invoiceId; DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql); DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType); if (ds.Tables[0].Rows.Count != 0) { return ds.Tables[0]; } else { return null; } } catch (Exception) { throw; } }
public object ExecuteQuery(ConfigSettings configuration, ReturnType returnType) { try { Connection(configuration); ((IDbCommand)configuration.DBCommand).Connection = GenericConnection; object obj = null; switch (returnType) { case ReturnType.DataSetType: DSQueryReturn = new DataSet(); switch (configuration.DataStore) { case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break; case DataProviderType.OleDb: GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break; case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break; } GenericDataAdapter.Fill(DSQueryReturn); GenericConnection.Close(); GenericConnection.Dispose(); obj = DSQueryReturn; break; case ReturnType.DataReaderType: obj = ((IDbCommand)configuration.DBCommand).ExecuteReader(CommandBehavior.CloseConnection); break; case ReturnType.ScalarType: obj = ((IDbCommand)configuration.DBCommand).ExecuteScalar(); GenericConnection.Close(); GenericConnection.Dispose(); break; case ReturnType.XmlDocumentType: switch (configuration.DataStore) { case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break; case DataProviderType.OleDb : GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break; case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break; } GenericDataAdapter.Fill(DSQueryReturn); XmlDataDocument XmlDDoc = new XmlDataDocument(DSQueryReturn); obj = XmlDDoc; GenericConnection.Close(); GenericConnection.Dispose(); break; default: throw new ApplicationException("Paramter Type are Incorrect"); break; } return obj; } catch (ApplicationException Ex) { throw Ex; } catch (Exception Ex) { ////To add additional information in the exception //NameValueCollection colDataInfo = new NameValueCollection(); //colDataInfo.Add("Method","Unable to execute query"); //BaseApplicationException exBase = new BaseApplicationException(Ex.Message,Ex); ////Addiding additional information to the exception //exBase.AdditionalInformation = colDataInfo; //throw exBase; throw Ex; } finally { GenericConnection.Close(); GenericConnection.Dispose(); } }
private void Connection(ConfigSettings configuration) { try { switch (configuration.DataStore) { case DataProviderType.Sql: GenericConnection = new SqlConnection(configuration.ConnectionString); break; case DataProviderType.Odbc: GenericConnection = new OdbcConnection(configuration.ConnectionString); break; case DataProviderType.OleDb: GenericConnection = new OleDbConnection(configuration.ConnectionString); break; } GenericConnection.Open(); } catch (Exception Ex) { ////To add additional information in the exception //NameValueCollection colDataInfo = new NameValueCollection(); //colDataInfo.Add("ConnectionString",GenericConnection.ConnectionString); //BaseApplicationException exBase = new BaseApplicationException(Ex.Message,Ex); ////Adding additional information to the exception //exBase.AdditionalInformation = colDataInfo; //throw exBase; } }