public async Task TestTableViewParameter() { try { UnitOfWork unitOfWork = new UnitOfWork(); ListensoftwaredbContext dbContext = unitOfWork.generalLedgerRepository._dbContext; DataTable dt = new DataTable(); dt.Columns.Add("CustomerName", typeof(string)); dt.Columns.Add("FirstName", typeof(string)); dt.Columns.Add("LastName", typeof(string)); dt.Columns.Add("CompanyName", typeof(string)); dt.Columns.Add("Address_Line1", typeof(string)); dt.Columns.Add("Address_Line2", typeof(string)); dt.Columns.Add("City", typeof(string)); dt.Columns.Add("State", typeof(string)); dt.Columns.Add("Zipcode", typeof(string)); dt.Columns.Add("EmailText", typeof(string)); dt.Columns.Add("LoginEmail", typeof(bool)); dt.Columns.Add("Password", typeof(string)); dt.Rows.Add("customer name", "firstname", "lastname", "companyname" , "address_line1", "address_line2", "city", "state", "zipcode", "emailtext", true, "123"); List <SqlParameter> parameters = new List <SqlParameter>(); SqlParameter param1 = new SqlParameter(); param1.ParameterName = "@ParamHashTable"; param1.SqlDbType = SqlDbType.Structured; param1.TypeName = "dbo.AccountRegistrationTableType"; param1.Value = dt; parameters.Add(param1); //DataTable query = await dbContext.Database.SqlQuery<DataTable>("usp_CreateAccount @ParamHashTable", param1).SingleAsync(); IList <DataTable> query = await dbContext.SqlQuery <DataTable>(CommandType.Text, "usp_CreateAccount @ParamHashTable", parameters); //DataTable query = dbContext.Database.ExecuteSqlCommand("usp_createaccount @ParamHashTable"); //foreach (var item in query) //{ } } catch (Exception ex) { throw new Exception("TestTableViewParameter", ex); } }
public async Task <NextNumber> GetNextNumber(string nextNumberName) { try { /* * NextNumber nextNumber = await (from detail in _dbContext.NextNumber * where detail.NextNumberName == nextNumberName * select detail).FirstOrDefaultAsync<NextNumber>(); * * nextNumber.NextNumberValue = nextNumber.NextNumberValue + 1; * * base.UpdateObject(nextNumber); * _dbContext.SaveChanges(); * * return nextNumber; */ List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("@NextNumberName", nextNumberName)); IList <NextNumber> nextNumber = await _dbContext.SqlQuery <NextNumber>(CommandType.Text, "usp_GetNextNumber @NextNumberName", parameters); /* * SqlParameter param1 = new SqlParameter("@NextNumberName", nextNumberName); * //NextNumber nextNumber = await _dbContext.Database.SqlQuery<NextNumber>("usp_GetNextNumber @NextNumberName", param1).SingleAsync(); * * var command = _dbContext.Database.GetDbConnection().CreateCommand(); * * command.CommandType = System.Data.CommandType.StoredProcedure; * command.CommandText = "usp_GetNextNumber"; * command.Parameters.Add(param1); * * _dbContext.Database.OpenConnection(); * * * * var queryResults = command.ExecuteReader(); * NextNumber nextNumber = new NextNumber(); * while (queryResults.Read()) * { * nextNumber.NextNumberId = (long) queryResults["NextNumberId"]; * nextNumber.NextNumberName = queryResults["NextNumberName"].ToString(); * nextNumber.NextNumberValue = (long)queryResults["NextNumberValue"]; * * * * } */ return(nextNumber[0]); } catch (Exception ex) { throw new Exception(GetMyMethodName(), ex); } //foreach (NextNumber item in query) //{ // nextNumber=item; // } /* * NextNumber nextNumber = null; * long? currentNextNumberValue = 0; * using (DbContextTransaction scope = _dbListensoftwaredbContext.Database.BeginTransaction()) * { * //Lock the table during this transaction * nextNumber = await (from e in _dbListensoftwaredbContext.NextNumbers * where e.NextNumberName == nextNumberName * select e).FirstOrDefaultAsync<NextNumber>(); * * currentNextNumberValue = nextNumber.NextNumberValue; * nextNumber.NextNumberValue += 1; * _dbListensoftwaredbContext.NextNumbers.Attach(nextNumber); * _dbListensoftwaredbContext.Entry(nextNumber).State = EntityState.Modified; * _dbListensoftwaredbContext.SaveChanges(); * nextNumber.NextNumberValue = currentNextNumberValue??0; * * scope.Commit(); * } */ }