|
I've written lots of small applications where I work, developing the databases in SQL Server, which is my forte, and the front ends in ASP.NET. When I've been doing it all myself it's been easy because, as the application developer, I know the databases and have no trouble writing ad-hoc queries in the application or, more often, calling Stored Procedures that I've written, knowing what parameters are required, and so on.
But now I'm embarking on a much larger project, for which we'll be hiring developers to write the application. I'm just writing the database. My plan was to write well-documented Stored Procedures to perform every operation required on the database - inserting, updating, deleting, retrieving records, and so on, and then letting the developers just call these procedures from the application without them needing to know the database's structure, effectively creating an API. If I have time, I also intended to write web services as an intermediate level for them to use, so that they call these services rather than the Stored Procedures. I don't know how much benefit there is to doing this for the core .NET application, but since we intend to also create mobile apps down the line, the same services could be used for those as well.
This morning I've been reading about CRUD and writing RESTful services and so on (not completely understanding it all), and now I'm starting to wonder if I'm missing something. I want to set up an API, so to speak, that will seem sensible, easy to work with and perhaps even familiar to our experienced developers after they are recruited, but since this is the first time I've worked in this way I'm not really sure what they'll be expecting. I want to work in line with best practice to deliver a successful project and an excellent product. Does my proposed plan fit this requirement, or should I be doing something else?
EDIT: Maybe I'd get more useful replies if this was in the Web Development thread? Can a mod move it?
|
|
|
|
|
Design and Architecture would probably be a better forum but the same people will look at it as here...
If you are confident at designing the data structure and getting it right then I would suggest doing everything up to the WCF/Service layer. However as you sound like a particularly advanced power user I would probably look at getting a senior dev to work with you on the database and service and when that is in place get the UI devs on board to complete the application.
Be prepared to change your design as dictated by the UI, forget the crap about agile you may hear, the best apps are built by people who recognise a design error early and fix it NOW.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the tips. I definitely agree that being flexible, as far as possible, and changing things in response to design challenges that arise is important.
|
|
|
|
|
phil2415 wrote: This morning I've been reading about CRUD and writing RESTful services and so on (not completely understanding it all)
Rest applies to the application server not to the database. The application server deals with a data model where objects in that often but not always map to tables in the database. It that 'not always' part that means that the application server should have its own data model and then map that to the database.
The stored procedures provide the access layer to the database. Far as I am concerned they (stored procs) server the same purpose as any other application layer so I am in favor of them. Additionally I consider the permissions possible with them a win.
phil2415 wrote: I'm not really sure what they'll be expecting.
First thing of course is that someone, somewhere needs to come up with a data model. Doesn't need to be formal but a team (more than one person) can't work without a division of labor and so they need to target a data model and so do you. You should of course be prepared to participate in that process. You might even be the sole person given that you have experience both in database and application development.
Your role other than that would be in insuring that the data model is not going to impose problems such as performance. Then normal DDL development with any additional database tests (archive, back up, etc) that would normally be identified in any production system.
Depending on the team/project you might also be in charge of writing the application side abstraction layer - the thing that interfaces with the stored proc.
Of course one potential problem with all of the above is that one of the incoming people already has an idea how the database should be done and it doesn't jive with your view.
|
|
|
|
|
Thanks for the advice. I'm already devising the data model for the database. My idea is that before the developers even arrive, the bulk of the database will be written and SPs in place for handling fundamental operations. I've no doubt that changes will need to be made once the development team starts, but hopefully the basic foundations should be fairly sound.
|
|
|
|
|
I agree with developing up through the Web Service part as an API so the application developers don't need to even know there's a database involved.
<YouProbablyAlreadyKnowThis>
Be sure that at the API level the methods are written as high-level application operations, rather than as specific database operations.
E.g. if a transaction requires two INSERTs and an UPDATE, those details should be hidden in one method rather than requiring the application developers to know that they need to call three methods and in what order.
</YouProbablyAlreadyKnowThis>
|
|
|
|
|
Love the <youprobablyalreadyknowthis> tag, I might have to steal that some time! All the more pleased with it given that I actually did know it!
|
|
|
|
|
Hi,
I have expiry_date field. I am using nthis code to get expired this month list.
SELECT contracts.contract_id, contract_types.type_name, contracts.contract_name, contracts.supplier, contracts.contact_name, contracts.contact_number, contracts.expiry_date, contracts.reminder FROM contracts
JOIN contract_types ON contract_types.type_id = contracts.type
WHERE MONTH(contracts.expiry_date) = MONTH(NOW()) AND YEAR(contracts.expiry_date) = YEAR(NOW());
Now, I would like to get list of expiring next month. How can I do this? what if current month is 12? then Next Month will be 1 and Year will also be changed?
Kindly advise
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
this solved my problem.
WHERE MONTH(contracts.expiry_date) = MONTH(DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH)) AND YEAR(contracts.expiry_date) = YEAR(DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH));
Thank you so much
Technology News @ www.JassimRahma.com
|
|
|
|
|
This may be easier to read and you won't need to do calculations on years:
WHERE DATEDIFF(mm,contracts.expiry_date,DATE_ADD(NOW(), INTERVAL 1 MONTH)) = 1
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hm, depends on the details of his requirements.
If I see it correctly, your query will select all data within one month from now. His question looked like he needed the data of next calendar month.
|
|
|
|
|
Try the query out - you will find that it selects the data from next calendar month 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 13-Aug-14 5:22am.
|
|
|
|
|
Have a look at the DATEDIFF function.
E.g.
select datediff(mm,getdate(),getdate()+180)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 12-Aug-14 4:15am.
|
|
|
|
|
Hi, i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.
We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.
EG Existing groups :
GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998
If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.
Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')
Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)
Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)
Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')
This gives us 6 unassigned tasks and a uneven group assignment
GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2
This means the new tasks will be assigned like this
TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3
Can anyone help?
Thanks
Dan
|
|
|
|
|
Insert new tasks like this:
insert into jobs
select top(1)
@TaskName,
groupid
from jobs
group by groupid
order by count(*),groupid
|
|
|
|
|
Good Day All
i have a Following Query
1) Query 1
DECLARE @FINALVAUE VARCHAR(MAX) = (Convert(decimal,@CAUSATIVE_FACTORS_ID) - 1) + Convert(decimal,@LU_PERCENTAGE_FAILURE_IN_SAMPLE_ID) + Convert(decimal,@LU_REPORTING_HISTORY_ID)
PRINT @FINALVAUE
The Calculation is like this
2) Query 2
DECLARE @FINAL VARCHAR(MAX)
SET @FINAL= (4-1) + 0.08 + 0.2
SELECT @FINAL
The first Query i am retrieving values from the Variables which are varchar and converting to decimal and doing the calculations. and the second Query i am just taking the value as is. i used this to debug in trying to solve this problem. My problem here in the first Query in the @FINALVAUE variable the answer is "3" and in the second Query the answer is "3.28"
, i want the first Query to display the same value which is "3.28"
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
CONVERT(DECIMAL, value) will convert without decimal palces  ...
Use something like CONVERT(DECIMAL(4, 2), value)...
http://msdn.microsoft.com/en-us/library/ms187746.aspx[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thanks that saved the day.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
I'm trying to consolidate 3 operations into 1
Not sure if it's possible, but it's seems like it is.
I want to select some data, and take the data from 1 table and update it to another table.
Her's what I have. It's kind of a soup at the moment, experienenting with different ideas.
In my Create Table, I can't figure out how to use those value in the update in place of the parameters.
Perhaps I should just use the line in Update, the select for CardLabel.
DECLARE @CardID INT;
SET @CardID = 645;
CREATE TABLE #CCInfo
(
Label VarChar(40)
, CardBrand VarChar(80)
, CardExpMonth VarChar(80)
, CardExpYear VarChar(80)
, CardHolder_FirstName VarChar(80)
, CardHolder_LastName VarChar(80)
, CardNum VarChar(80)
, CardPhoneNum VarChar(20)
)
INSERT INTO #CCInfo
(
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
)
SELECT
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
FROM CardInfoPerm
WHERE CardID=@CardID
UPDATE
OrderInfo
SET
CardID=@CardID
, Cardlabel= (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=Card_
, CardExpMonth=@CardExpMonth
, CardExpYear=@CardExpYear
, CardHolder_FirstName=@CardHolder_FirstName
, CardHolder_LastName=@CardHolder_LastName
, CardNum=@CardNum
, CardPhoneNum=@CardPhoneNum
, PaymentInfo=@CardID
, ApprovalCode=''
, AuthorizationID=''
, CheckID=''
, CheckLabel=''
, CheckType=''
, CheckName=''
, BankName=''
, CheckRoutingNum=''
, CheckAccountNum=''
WHERE OrderNum='CA-2054'
|
|
|
|
|
I understand what it means, I just don't know how to go about getting the scalar to be recognized in Update.
The first part works, just having trouble with the update.
DECLARE @CardID INT, @OrderNumber VarChar(40);
SET @CardID = 645;
SET @OrderNumber = 'CA-2054';
DECLARE @CCInfo TABLE
(
Label VarChar(40)
, CardBrand VarChar(80)
, CardExpMonth VarChar(80)
, CardExpYear VarChar(80)
, CardHolder_FirstName VarChar(80)
, CardHolder_LastName VarChar(80)
, CardNum VarChar(80)
, CardPhoneNum VarChar(20)
);
INSERT @CCInfo
(
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
)
SELECT
Label
, CardBrand
, CardExpMonth
, CardExpYear
, CardHolder_FirstName
, CardHolder_LastName
, CardNum
, CardPhoneNum
FROM CardInfoPerm
WHERE CardID=@CardID
UPDATE
OrderInfo
SET
CardID=@CardID
, Cardlabel=@CCInfo.Label
, CardBrand=@CCInfo.CardBrand
, CardExpMonth=@CCInfo.CardExpMonth
, CardExpYear=@CCInfo.CardExpYear
, CardHolder_FirstName=@CCInfo.CardHolder_FirstName
, CardHolder_LastName=@CCInfo.CardHolder_LastName
, CardNum=@CCInfo.CardNum
, CardPhoneNum=@CCInfo.CardPhoneNum
, PaymentInfo=@CardID
, ApprovalCode=''
, AuthorizationID=''
, CheckID=''
, CheckLabel=''
, CheckType=''
, CheckName=''
, BankName=''
, CheckRoutingNum=''
, CheckAccountNum=''
WHERE OrderNum=@OrderNumber
|
|
|
|
|
Separate the statements with semicolons?
|
|
|
|
|
I went for the later in this function, but found out later that I could of just declared and set all the card data first
and then run the UPDATE using the variables.
So I ended up with this, I shortened it to keep it more brief
UPDATE " & _
OrderInfo " & _
SET
CardID=@CardID
, Cardlabel=(SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=(SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpMonth=(SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpYear=(SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID)
WHERE OrderNum=@OrderNum"
But later the next day I ended up writing something that does everything in one shot, far beyond I could imagine that I can do. So I modified the above to this below
DECLARE " & _
@CardLabel VarChar(80)
, @CardBrand VarChar(80)
, @CardExpMonth VarChar(80)
, @CardExpYear VarChar(80)
, @New_OrderNumber VarChar(80);
SET @CardLabel = (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardBrand = (SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpMonth = (SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpYear = (SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID);
IF EXISTS(SELECT * FROM OrderInfo WHERE OrderNum=@OrderNum)
BEGIN
UPDATE
END
ELSE
BEGIN
INSERT
END
Then, I was able to do more,
delete previous cart items,
generate a new order number,
copy the cart contents over
return the new order number
all in one shot now, use to take me like 8 functions to do it all. I'm amazed at how fast it runs now.
I would post it, but it's too large
I'm stoked on how it came out, and learned a lot on my own. I think I got the semi-colons right this time, I need to read up on that.
|
|
|
|
|
The Select-Statement have to run into a variable. For all recorset in the variable you have to carry out the update statement
|
|
|
|
|
I have created a UNION query as the source for a report that is written to a Word Document using VBA. The UNION joins a Table (historical information from before the current database Application was written) and the output of a Query that extracts corresponding information from the live data. I have recently added a new text field to both 'sides'. The value of the text field for the Query (either "S" or "") is generated in an IIF() statement within it.
When I Open the UNION query via the Access interface, it works as intended - the new text field is set to the correct value for records derived from either source. When I open a ADO Recordset based on the UNION query in VBA, the records derived from the Table are correct, but the contents of the new field in the records derived from the Query is always a Null String (N.B. not Null). If I convert the Query which is the second source of the UNION into a Make Table Query, run it, and then UNION the two Tables, everything again works as it should. Does this make any sense to anyone?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
