| private CreateCommand ( string commandText, DbConnection, connection ) : DbCommand | ||
| commandText | string | |
| connection | DbConnection, | |
| return | DbCommand | |
public static StMDetail SpMDetailGetDataStructById(string detailid)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpMDetailGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@detailid";
param.Value = detailid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
//create object of structure
StMDetail details = new StMDetail();
//check there is row in table or not
if (table.Rows.Count > 0)
{ //create data reader
DataRow dr = table.Rows[0];
//store individually each field into object
details.detailid = Convert.ToInt32(dr["detailid"]);
details.masterid = Convert.ToInt32(dr["masterid"]);
details.detail = dr["detail"].ToString();
details.date = dr["date"].ToString();
details.Yid = Convert.ToString(dr["YMasterId"]);
details.Deptid = Convert.ToString(dr["DepartmentId"]);
details.StoreId = Convert.ToString(dr["StoreId"]);
details.BusId = dr["BusinessId"].ToString();
details.employeeid = Convert.ToInt32(dr["EmployeeId"]);
details.userid = Convert.ToInt32(dr["userid"]);
}
//return object with data
return(details);
}
//GET DATA FROM DepartmentMASTER TABLE BY MasterId[PRIMARY KEY]
public static DataTable SpWMasterGetDataByWeek(string week)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpWMasterGetDataByWeek";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@week";
param.Value = week;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
return(DataAccess.ExecuteSelectCommand(comm));
}
public static DataTable SpTacticMasterGetDataStructById(string MasterId)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpTacticMasterGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@MasterId";
param.Value = MasterId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
////create object of structure
//StTacticMaster details = new StTacticMaster();
////check there is row in table or not
//if (table.Rows.Count > 0)
//{ //create data reader
// DataRow dr = table.Rows[0];
// //store individually each field into object
// details.masterid = Convert.ToInt32(dr["masterid"]);
// details.businessid = Convert.ToInt32(dr["businessid"]);
// details.strategymasterid = Convert.ToInt32(dr["strategymasterid"]);
// details.mmasterid = Convert.ToInt32(dr["mmasterid"]);
// details.wmasterid = Convert.ToInt32(dr["wmasterid"]);
// details.employeeid = Convert.ToInt32(dr["employeeid"]);
// details.title = dr["title"].ToString();
// details.description = dr["description"].ToString();
// details.budgetedcost = Convert.ToDecimal(dr["budgetedcost"]);
// details.actualcost = Convert.ToDecimal(dr["actualcost"]);
// details.shortageexcess = Convert.ToDecimal(dr["shortageexcess"]);
//}
////return object with data
//return details;
return(table);
}
//GET DATA FROM DepartmentMASTER TABLE BY TaskInstructionId[PRIMARY KEY]
public static DataTable SpTaskInstructionGetDataByTaskId(string TaskId)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpTaskInstructionGetDataByTaskId";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@TaskId";
param.Value = TaskId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
return(DataAccess.ExecuteSelectCommand(comm));
}
//GET DATA FROM DepartmentMASTER TABLE BY deptid[PRIMARY KEY]
public static DataTable SpDocumentGetDataByRight(string userid)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpDocumentGetDataByRight";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@userid";
param.Value = userid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
return(DataAccess.ExecuteSelectCommand(comm));
}
public static DataTable SpStrategyEvaluationGetDataByMasterIdWithEmployeeName(string masterid)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpStrategyEvaluationGetDataByMasterIdWithEmployeeName";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@masterid";
param.Value = masterid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
return(DataAccess.ExecuteSelectCommand(comm));
}
public static StTaskAllocation SpTaskAllocationGetDataStructById(string TaskAllocationId)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpTaskAllocationGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@TaskAllocationId";
param.Value = TaskAllocationId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
//create object of structure
StTaskAllocation details = new StTaskAllocation();
//check there is row in table or not
if (table.Rows.Count > 0)
{ //create data reader
DataRow dr = table.Rows[0];
//store individually each field into object
details.taskallocationid = Convert.ToInt32(dr["taskallocationid"]);
details.taskid = Convert.ToInt32(dr["taskid"]);
details.employeeid = Convert.ToInt32(dr["employeeid"]);
details.eunitsalloted = Convert.ToInt32(dr["eunitsalloted"]);
details.unitsused = Convert.ToInt32(dr["unitsused"]);
details.otunitsused = Convert.ToInt32(dr["otunitsused"]);
details.taskallocationdate = dr["taskallocationdate"].ToString();
details.taskreport = dr["taskreport"].ToString();
details.supervisornote = dr["supervisornote"].ToString();
details.freeunits = Convert.ToInt32(dr["freeunits"]);
details.taskname = dr["taskname"].ToString();
}
//return object with data
return(details);
}
//ADD DATA IN DepartmentMASTER TABLE
public static Int32 SpLTGDetailAddData(string masterid, string detail, string date)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpLTGDetailAddData";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@masterid";
param.Value = masterid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@detail";
param.Value = detail;
param.DbType = DbType.String;
param.Size = 80000;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Date";
param.Value = date;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
comm.Parameters.Add(new SqlParameter("@DetailId", SqlDbType.Int));
comm.Parameters["@DetailId"].Direction = ParameterDirection.Output;
comm.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int));
comm.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
Int32 result = DatabaseCls1.ExecuteNonQueryep(comm);
result = Convert.ToInt32(comm.Parameters["@DetailId"].Value);
return(result);
}
public static StLTGEvaluation SpLTGEvaluationGetDataStructById(string EvaluationId)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpLTGEvaluationGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@EvaluationId";
param.Value = EvaluationId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
//create object of structure
StLTGEvaluation details = new StLTGEvaluation();
//check there is row in table or not
if (table.Rows.Count > 0)
{ //create data reader
DataRow dr = table.Rows[0];
//store individually each field into object
details.DepartmentId = Convert.ToInt32(dr["DepartmentId"]);
details.BusinessId = Convert.ToInt32(dr["BusinessID"]);
details.storeid = Convert.ToInt32(dr["Whid"]);
details.evaluationid = Convert.ToInt32(dr["EvaluationId"]);
details.masterid = Convert.ToInt32(dr["ObjectiveMasterId"]);
details.LtgId = Convert.ToInt32(dr["LTGMaterId"]);
details.employeeid = Convert.ToInt32(dr["employeeid"]);
details.evaluationnote = dr["evaluationnote"].ToString();
details.date = dr["date"].ToString();
details.statusid = Convert.ToInt32(dr["statusid"]);
details.percentage = Convert.ToDecimal(dr["percentage"]);
}
//return object with data
return(details);
}
/// <summary>
/// Returns all the Discounts in system
/// </summary>
/// <returns></returns>
public long GetDiscountAmount()
{
var command = DataAccess.CreateCommand(CommandType.Text);
command.CommandText =
"SELECT sum(Discount_Amt) FROM Counter_Discount";
DataTable dt = DataAccess.ExecuteSelectCommand(command);
long DiscountAmount = 0;
if (dt != null)
{
if (dt.Rows[0][0] != System.DBNull.Value)
{
DiscountAmount = dt.Rows[0].Field <long>(0);
}
}
return(DiscountAmount);
}
//GET DATA FROM DepartmentMASTER TABLE BY ProjectId[PRIMARY KEY]
public static DataTable SpProjectMasterGetDataByStatus(string status)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpProjectMasterGetDataByStatus";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@status";
param.Value = status;
param.DbType = DbType.String;
param.Size = 100;
comm.Parameters.Add(param);
// execute the stored procedure
return(DataAccess.ExecuteSelectCommand(comm));
}
/// <summary>
/// Returns all the Branchs in system
/// </summary>
/// <returns></returns>
public List <BranchSaleModel> GetAllBranchSales(long lngBranch_ID)
{
var command = DataAccess.CreateCommand(CommandType.Text);
command.CommandText =
"SELECT ID, Shop_ID, Branch_ID, Sale_Date, Modify_Date," +
"Previous_Amt, Sale_Amt_With_Comm, Commission_Amt, Sale_Amt_After_Comm, " +
"Deposit, Balance, Reserve1 FROM Branch_Sale_Summary";
if (lngBranch_ID != 0)
{
command.CommandText = command.CommandText + " WHERE Branch_ID = " + lngBranch_ID + " Order By Modify_Date Desc";
}
else
{
command.CommandText = command.CommandText + " WHERE Order By Modify_Date Desc";
}
return(Helper.ConvertDataTable <BranchSaleModel>(DataAccess.ExecuteSelectCommand(command)));
}
public int getMaxReserveIdData()
{
int id;
DataTable dt = new DataTable();
StringBuilder strQuery = new StringBuilder();
strQuery.Append("select ");
strQuery.Append("max(reserveID) as reserveID ");
strQuery.Append("from ReserveOrder");
using (DataAccess dac = new DataAccess())
{
dac.Open(Provider.MSSQL);
DbCommand cmd = dac.CreateCommand(strQuery.ToString());
cmd.CommandType = CommandType.Text;
DbDataAdapter da = dac.CreateDataAdapter(cmd);
da.Fill(dt);
}
id = int.Parse(dt.Rows[0]["reserveID"].ToString());
return(id);
}
/// <summary>
/// Returns all the Discounts in system
/// </summary>
/// <returns></returns>
public long GetExpensesAmount()
{
var command = DataAccess.CreateCommand(CommandType.Text);
command.CommandText =
"SELECT sum(Exp_Amt) FROM EXPENSES";
DataTable dt = DataAccess.ExecuteSelectCommand(command);
long ExpAmtAll = 0;
if (dt != null)
{
if (dt.Rows[0][0] != System.DBNull.Value)
{
ExpAmtAll = dt.Rows[0].Field <long>(0);
}
}
return(ExpAmtAll);
}
public static bool AdminDeleteProductReview(string reviewID)
{
DbCommand command = DataAccess.CreateCommand();
command.CommandText = "AdminDeleteProductReview";
command.Parameters.Add(NewParameter("@ReviewID", reviewID, DbType.Int32, -1, command));
int result = -1;
try
{
result = DataAccess.ExecuteNonQuery(command);
}
catch (Exception ex)
{
Utilities.LogError(ex);
}
return(result != -1);
}
public static DataTable SpMMasterGetDataByBusinessIdbyProjected(string businessid)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpMMasterGetDataByBusinessIdbyProjected";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@businessid";
param.Value = businessid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// execute the stored procedure
return(DataAccess.ExecuteSelectCommand(comm));
}
public static StTask SpTaskMasterGetDataStructById(string TaskId)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpTaskMasterGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@TaskId";
param.Value = TaskId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
//create object of structure
StTask details = new StTask();
//check there is row in table or not
if (table.Rows.Count > 0)
{ //create data reader
DataRow dr = table.Rows[0];
//store individually each field into object
details.taskid = Convert.ToInt32(dr["TaskId"]);
details.taskname = dr["taskname"].ToString();
details.projectid = Convert.ToInt32(dr["projectid"]);
details.estartdate = dr["estartdate"].ToString();
details.eenddate = dr["eenddate"].ToString();
details.eunitsalloted = Convert.ToInt32(dr["eunitsalloted"]);
details.astartdate = dr["astartdate"].ToString();
details.aenddate = dr["aenddate"].ToString();
details.percentage = Convert.ToDecimal(dr["percentage"]);
details.status = dr["status"].ToString();
}
//return object with data
return(details);
}
public static bool SpSubTypeUpdateData(string id, string name)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpSubTypeUpdateData";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@id";
param.Value = id;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@name";
param.Value = name;
param.DbType = DbType.String;
param.Size = 100;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DataAccess.ExecuteNonQuery(comm);
}
catch
{
// log errors if any
}
// result will be 1 in case of success
return(result != -1);
}
public List <BrandModel> GetUniueqBrandSize(string strLiqType, string strBrandName)
{
var command = DataAccess.CreateCommand(CommandType.Text);
command.CommandText =
"SELECT ID, Shop_ID, Category, Liq_Name, Box, Weight, Purchase_Rate, Sale_Rate, Branch_Commision, Reserve1 FROM Brands WHERE 1=1 ";
if (strLiqType.Length != 0)
{
command.CommandText += " AND UPPER(Category)='" + strLiqType.ToUpper() + "' ";
}
if (strBrandName.Length != 0)
{
command.CommandText += " AND UPPER(Liq_Name)='" + strBrandName.ToUpper() + "' ";
}
command.CommandText += " GROUP BY UPPER(Weight) ";
command.CommandText += " ORDER BY UPPER(Weight) ";
return(Helper.ConvertDataTable <BrandModel>(DataAccess.ExecuteSelectCommand(command)));
}
public static DataTable SpTacticDetailGetDataStructById(string DetailId)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpTacticDetailGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DetailId";
param.Value = DetailId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
return(table);
}
public static bool AdminAddProductImage(string productId, string path)
{
DbCommand command = DataAccess.CreateCommand();
command.CommandText = "AdminAddProductImage";
command.Parameters.Add(NewParameter("@ProductID", productId, DbType.Int32, -1, command));
command.Parameters.Add(NewParameter("@Path", path, DbType.String, 500, command));
int result = -1;
try
{
result = DataAccess.ExecuteNonQuery(command);
}
catch (Exception ex)
{
Utilities.LogError(ex);
}
return(result != -1);
}
/// <summary>
/// Login
/// </summary>
/// <param name="shopID">shop id</param>
/// <param name="shopname">Shop name</param>
/// <param name="username">User name</param>
/// <param name="password">password</param>
/// <returns>True if login is successful</returns>
public bool SetUser(Int64 shopID, string shopname, string username, string password)
{
var command = DataAccess.CreateCommand(CommandType.Text);
command.CommandText = "Select Id, FullName from Users where FullName = @username and Userpass = @password and Shop_ID=@shopID";
var parameter = command.CreateParameter();
parameter.ParameterName = "@username";
parameter.Value = username;
parameter.DbType = DbType.String;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@password";
parameter.Value = password;
parameter.DbType = DbType.String;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@shopID";
parameter.Value = shopID;
parameter.DbType = DbType.Int32;
command.Parameters.Add(parameter);
var result = DataAccess.ExecuteScalar(command);
if (string.IsNullOrEmpty(result))
{
return(false);
}
// authentication successful
IsAuthenticated = true;
Username = username;
User_ID = 1;
ShopName = shopname;
ShopId = shopID;
return(true);
}
public static void AddOrderItem(string ModelId, string OrderId, string Quantity, string Color, string Price)
{
DbCommand command = DataAccess.CreateCommand();
command.CommandText = "sp_AddNewOrderItem";
DbParameter param = command.CreateParameter();
param = command.CreateParameter();
param.ParameterName = "@ModelId";
param.Value = ModelId;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@OrderId";
param.Value = OrderId;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@Quantity";
param.Value = Quantity;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@Color";
param.Value = Color;
param.DbType = DbType.String;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@Price";
param.Value = Price;
param.DbType = DbType.Double;
command.Parameters.Add(param);
DataAccess.ExecuteSelectCommand(command);
}
public static int SpProjectEvaluationDeleteData(string evaluationid)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpProjectEvaluationDeleteData";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@evaluationid";
param.Value = evaluationid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
int result = -1;
result = DataAccess.ExecuteNonQuery(comm);
return(result);
}
//ADD DATA IN DepartmentMASTER TABLE
public static bool SpClassMasterAddData(string ClassName, string Description)
{
// get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpClassMasterAddData";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ClassName";
param.Value = ClassName;
param.DbType = DbType.String;
param.Size = 500;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Description";
param.Value = Description;
param.DbType = DbType.String;
param.Size = 500;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DataAccess.ExecuteNonQuery(comm);
}
catch
{
// log errors if any
}
// result will be 1 in case of success
return(result != -1);
}
public static bool CustomerSendMessage(string name, string surname, string email, string subject, string image, string text)
{
DbCommand commad = DataAccess.CreateCommand();
commad.CommandText = "CustomerSendMessage";
commad.Parameters.Add(NewParameter("@Name", name, DbType.String, 50, commad));
commad.Parameters.Add(NewParameter("@Surname", surname, DbType.String, 50, commad));
commad.Parameters.Add(NewParameter("@Email", email, DbType.String, 50, commad));
commad.Parameters.Add(NewParameter("@Subject", subject, DbType.String, 50, commad));
commad.Parameters.Add(NewParameter("@Image", image, DbType.String, 500, commad));
commad.Parameters.Add(NewParameter("@Text", text, DbType.String, 500, commad));
try
{
return(CatalogAccess.ExecuteNoneQuery(commad));
}
catch
{
return(false);
}
}
public static void OrderUpdate(OrderIfo o)
{
DbCommand cmd = DataAccess.CreateCommand();
cmd.CommandText = "AdminOrderUpdate";
cmd.Parameters.Add(CatalogAccess.NewParameter("@OrderID", o.OrderID.ToString(), DbType.Int32, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@DateCreated", o.DateCreated, DbType.DateTime, -1, cmd));
if (o.DateShipped.Trim() != "")
{
cmd.Parameters.Add(CatalogAccess.NewParameter("@DateShipped", o.DateShipped, DbType.DateTime, -1, cmd));
}
cmd.Parameters.Add(CatalogAccess.NewParameter("@Verified", o.Verified, DbType.Byte, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@Complated", o.Completed, DbType.Byte, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@Canceled", o.Canceled, DbType.Byte, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@Comments", o.Comments, DbType.String, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@CustomerName", o.CustomerName, DbType.String, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@ShippingAddress", o.ShippingAddress, DbType.String, -1, cmd));
cmd.Parameters.Add(CatalogAccess.NewParameter("@CustomerEmail", o.CustomeEmail, DbType.String, -1, cmd));
DataAccess.ExecuteNonQuery(cmd);
}
public static object load_goalble_list(string funName)
{
var rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row;
try
{
var command = DataAccess.CreateCommand();
command.CommandText = funName;
command.Connection.Open();
var adp = new SqlDataAdapter(command);
var ds = new DataSet();
adp.Fill(ds);
command.Connection.Close();
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable dt = ds.Tables[i];
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);
}
}
// return rows.ToList();
var serializer = new JavaScriptSerializer();
return(serializer.Serialize(rows));
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetAllProducts(string page, out int howManyPages)
{
DbCommand command = DataAccess.CreateCommand();
command.CommandText = "GetAllProducts";
DbParameter param;
param = command.CreateParameter();
param.ParameterName = "@DescriptionLenght";
param.Value = Configuration.ProductDescriptionLenght;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@PageNumber";
param.Value = page;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@ProductsPerPage";
param.Value = Configuration.ProductsPerPage;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "@HowManyProducts";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
command.Parameters.Add(param);
DataTable dt = DataAccess.ExecuteSelectedCommand(command);
int numberOfProduct = Int32.Parse(command.Parameters["@HowManyProducts"].Value.ToString());
howManyPages = (int)Math.Ceiling((double)numberOfProduct / (double)Configuration.ProductsPerPage);
return(dt);
}
public static StAccounts SpAccountMasterGetDataStructById(string AccountId)
{ // get a configured DbCommand object
DbCommand comm = DataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SpAccountMasterGetDataStructById";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@AccountId";
param.Value = AccountId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//execute procedure and store data in table
DataTable table = DataAccess.ExecuteSelectCommand(comm);
//create object of structure
StAccounts details = new StAccounts();
//check there is row in table or not
if (table.Rows.Count > 0)
{ //create data reader
DataRow dr = table.Rows[0];
//store individually each field into object
details.AccountId = Convert.ToInt32(dr["AccountnameId"]);
details.ClassId = Convert.ToInt32(dr["ClassId"]);
details.GroupId = Convert.ToInt32(dr["GroupId"]);
details.AccountName = dr["Name"].ToString();
details.Description = dr["Description"].ToString();
details.Balance = Convert.ToDecimal(dr["amount"]);
details.Date = dr["date"].ToString();
}
//return object with data
return(details);
}
