Moving From ASP Business Logic Trapped Inside Stored Procedures
Oct 28, 2010
How would you structure a large project with most of the business logic already inside stored procedures?We are moving from classic ASP to ASP.NET (VB) and pretty much all the business logic is inside stored procedures.Getting the logic out of there is pretty much impossible since my boss doesn't want to (too expensive, takes too long, no "real" added value).I was thinking about making a presentation layer made of aspx pages, a business logic / data access layer that would basically get the data and interact with the existing stored procedures and a business entities layer that would be made of classes (for entities and collections) containing the information to interact between those two layers.The reason I wanted to make those layers was to be able to reuse most of the code without duplicating it.
i am currently trying, to create some sort of a business logic within my class, that contains information about a machine, now this machine once sold, can be re-sold by its owner. the company needs to keep track of the machine and its owner for warranty claim purposes.the class takes in two values, a from date and a to date.now when a new owner is added to the owner collection of the machine, the TO date of the previous owner should automatically be updated to one day less than the From date of the new owner that is being added.
I have tried searching all over the web for this logic. Got many but half of them did not match what i was looking for and half were malfunctioning.I want to calculate business working hours between 2 datetime, where in I should be able to set the working hours as well as weekends and holidays should not be calculated.
I have several successive web pages going through a checkout process and they each have tons of business logic that time restraints limit me from pulling out and wrapping up elsewhere in the code the way it needs to be. What would be a good method for calling each one of these pages' Submit buttons in order from one page to the next?
The way I was going to do this was to (laugh at me go ahead) make PUBLIC all of the submit buttons' code and just call them. But what I have found is that all of them have, of course, certain calls to controls on their pages where it might make a label visible or change a text property then that will crash the app due to a null reference. I believe it is basically saying if the webpage is not visible then it is not going to let the code start setting certain GUI properties of the controls on the page. It seems like I recall this in VB6, too.
Anyway, what would you do if you were in this situation? I have started down the path of creating a flag that says whether or not I am in expresscheckoutmode and, if so, it just doesn't do the "illegal" things mixed into all of the code like making labels visible and all that. But that also means that I have an IF BLOCK everywhere where the code simply changes a label. It junked up the code bad and I still haven't finished that implementation of it all. I wanted to come here to ask the collective mind what programming principles I can best apply here.
I also went though the whole "ASP.Net in 24 Hours" book by Scott Mictchell. I followed everything that went on in that book and built the final project successfully. I also used the tutorials here to better understand the whole Membership Provider/User tables setup that was going on in the book. Next, I thought I would learn more about accessing data for ASP.Net since that is what most application do.I went through the first Data Tutorial here. I seemed to understand what was going on just fine.The second Data Tutorial seemed to just show a lot of VB code to build the Business Logic Layer. There was not much explanation about how to do it yourself.I think I can keep going in the tutorials here. I downloaded the code from the second tutorial and went through it.I am guessing that the code in the second tutorial was given just to proceed with the overall project that is being built throughout the tutorial sequence.
I have a shopping cart ListView control backed by a custom object that has methods for selecting and deleting. In the ListView, one of the columns is an ImageButton that is essentially a delete icon, with the CommandName parameter set to CommandName="Delete".
My issue is related to updating the shopping cart ListView on delete; as the delete method is stored in the custom business entity object, I am having trouble in terms of updating the calculations made inside the cart (we have deleted an item, so I need to recalc shipping, taxes, etc).
my form displays locations and owners for a same product, the forms have a data grid view that actually takes in the details of the location and owner of that particular product. a product can have multiple owners and locations once its manufactured over time, the forms actually display this all i need is some kind of business logic that can actually manage the historical events of the location and the owner that when a user actually goes in and adds a new entry, the end date of the previous owner gets reduced to one less than the start date of the new owner. this in formation is stored in business layers called the owner collection and the location collection, i am not able to actually inherit or call the location or the owner collection class in the classes.
My question is, am I able to share a business logic layer seemlessly between the two presentation layers or is there anything I need to watch out for. I'd really like to not have any duplication of business logic, so any advice would be more than welcome.
Some question about Ado.net and SQL Server stored procedures. We have a stored procedure in SQL Server as described below
Procedure dob.PendingCalls @UsID As Char(10), @Sts As Integer OUTPUT , @Msg As VarChar(100) OUTPUT AS Begin
[code]....
we are using vb.net in visual studio 2003. Now if ExecuteNonQuery is used then we do not get rows. If ExecuteReader is used then we get rows [ with forward-only access which is of no use to us] but we cannot get output parameters values returned by stored procedure?
calling stored procedures using ADO.NET. We have an application developed, which uses SQL Sever as backend and Visual foxpro as front end. In this application every thing is done by database only. That is all insert, update, delete operations are done by SQL Server only. So we have to provide some input and output paramaters to stored procedures. Some time these procedure carry out insert, update, delete operations as well as return some data in the form of tables.
I want to call a Stored Procedure using SqlDataSource. I've been reading the library documentation of the classes but i'm getting slightly confused. To call a stored procedure, I understand that I have to change the selectcommandtype and then set the selectcommand property to the name of the procedure.
'Change command type to stored procedure SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure 'set name of stored procedure
For some reason my stored procedures are all executing twice! I have a static function that runs an SP given its name and the parameters and fills a datatable.
Public Shared Function RunSP(ByVal spName As String, ByRef spParams As Dictionary(Of String, String), ByRef pDataTable As DataTable) As Integer Dim cmd As New SqlCommand Dim lAdapter As SqlDataAdapter
[code]....
Is there something wrong with the code? I've checked with the debugger and the appropriate SPs are definitely only being called once, i.e. this function only runs once for a particular insertion function, but two things are inserted into the table.
I am trying to use stored procedures with LINQ to SQL to perform insert, update, and delete operations. My update and delete procedures implement optimistic concurrency by checking the original values retrieved from the database with the current values in the database. A row count is returned to indicate if the row was updated. When I execute SubmitChanges on the data context to update the database, everything works fine except that the generated code doesn't check for concurrency. (I configured the insert, update, and delete operations to use the data context methods created from the stored procedures using the O/R Designer, and I assigned the original object properties to the method arguments.) I can customize the generated code so it works properly, but I know that I shouldn't do that because I'll lose the changes if the code is regenerated. I also know that I can add a partial class to extend the data context, but I don't know how to implement the methods correctly. All I really need to do is to have the methods return the row count, but, of course, that's not an acceptable method overload. I know that I can also call the methods directly rather than calling SubmitChanges, but it seems like this is intended to work with SubmitChanges.
I'm trying to figure out a way to save all the stored procedures on a SQL Server 2005instance to text files. One of our developers has made a huge amount of StoredProcedures,and theseneed to be managed somehow between our test / integration and production environments. I know how to retrieve / run / create stored procedures, it's just the "save-to-file" i need.
I noticed in the Entity Framework designer that you can map stored procedures for Insert, Update, and Delete operations. Is there any way to do this for Select operations as well, or is there a new direction for database access code where we don't really write stored procedures for our basic select operations any more?The company I work for is pretty adamant about always using stored procedures for every database operation, even though the Entity Framework makes the calls safe by calling sp_executesql.It seems like both LINQ to SQL and Entity Framework have moved away from using stored procedures for selecting the data. Is this an accurate statement?Just to clarify my question:I have a table in my database called Product. I use the wizard in the Entity Framework to generate my models...so I now have an Entity called Product. When I do the following query:
db.Products.SingleOrDefault(p => p.Id == 1);
It generates code similar to:
EXEC sp_executesql N'SELECT * FROM Product'[code].....
If it's not possible to do that using SingleOrDefault I'm fine with that. I'd much rather have the following:
db.Products.GetProduct(1);
Is this something that is normally done or do most people just have it dynamically generate the SQL?
I'm creating a database installer where I have some code to create database, tables, stored procedures, etc.I don't have problems when creating the database, tables and inserting required data. My problem is when my code gets to the stored procedures section.
Here is what I do:I have different text files (database.txt, tables.txt, insertdata.txt, storeprocedures.txt) where my sql syntax is located.I execute every process/text file using the following:
ExecuteSql("master", GetSql("database.txt")) ' Creating 1 database. ExecuteSql("mynewdatabase", GetSql("tables.txt")) ' Creating 15 tables. ExecuteSql("mynewdatabase", GetSql("insertdata.txt")) ' Inserting data to many tables. ExecuteSql("mynewdatabase", GetSql("storeprocedures.txt")) ' Creating 6 stored procedures.
Here is part of the error:In exception handler: Incorrect syntax near the keyword 'PROCEDURE'. "This is the second stored procedure because the variables I get below belong to the second one"
Must declare the scalar variable "@intVariable1". Must declare the scalar variable "@intVariable1". Must declare the scalar variable "@intVariable1". Must declare the scalar variable "@intVariable1".
When trying to create 6 or even 2 stored procedures using the same file 'storeprocedures.txt' is not possible. If I leave just 1 it works but I really want to keep them together.
Creating 15 tables using the same file 'tables.txt' works, inserting data in different tables using the same file 'insertdata.txt' works.This is the general syntax I use:
Create Procedure sp_stored1 @intVariablex int As
[code].....
Is is possible to create more than 1 stored procedure the way I'm trying to?Do I need to end every stored procedure with specific sql syntax, other than the 'end'?
I'm new user of SQL 2005 Stored Procedures and i start to create my new projects trying use stored procedures instead of writeing sql statments in my VB project.
first of all my idea is to have stored procedures for each table in my database handle Adding records or edit or delete.if i have a table name (Persons) as example i will Creat 3 stored Procedures called (sp_Persons_add) and (sp_pesons_edit) and (sp_persons_del)each stored procedure will have its input Parameters and also output parameters . this procedures will take care of everything .. check dupicates .. and check if any value is not valid because of any forign key related to that table .. and then the procedure pass me value into output parameter to tell me if it do the jop to the end . or it not .. and also can pass me message text through output parameter whitch i will display directly in my VB form .
I am working on a project that requires me to hop into to separate DB's. So I have figured that I need to have multiple functions inside of my VB page. The only problem I am having,is I am not to sure how to get this all accomplished. So far I have figured out the overall structure, just need to implement that structure.
Here is my idea: The main Function would call two other functions. We can Call them Sub Function 1 and Sub Function 2. So, the main Function takes the saved sessions information for the E-mail address and dumps in into Sub Function 1. It needs to open up a new connection to the db/stored procedure and RUN the following procedure and then return the result. Here is the stored procedure and what I think is correct.
CREATE PROCEDURE WEB_User (@EMAIL_ADDRESS varchar(80) = [EMAIL_ADDRESS]) AS SELECT MEMBER_NUMBER FROM WEB_LOGIN WHERE EMAIL_ADDRESS = @EMAIL_ADDRESS
So my question is, what is the function suppose to look like? how do I send the session information to the procedure? and finally, how do I return the stored procedure results and push back into the main function so it can be carried into sub function 2?
I am working on a Tag system for a news page designed in ASP.NET. For the system I require a TagExists method to check for tags within the database. The stored procedure I have written is below.
ALTER PROCEDURE [dbo].[Tags_TagExists]( @Tag varchar(50)) AS BEGIN
[code]....
I have tried switching the procedure to return 0 if the tag exists and 1 if it does not and it still returns 0 despite the exact same testing conditions. I have also returned the actual select query and it has complained of the Tag "news" (my test item) not being an int on execution showing the select itself is definitely properly formed.
I want to create a stored procedure in SQL server 2005 in order to check if there a specific value in a table. As I don't know how to do this can someone give me a link or an example on how to create and store the stored procedure? Secondly I wrote this for my the above stored procedure: CREATE PROCEDURE dbo.dokimi -- Add the parameters for the stored procedure here
[Code]....
One of the things that I am not sure is that I want to check the value that the user passes throuhg my application that I am creating. In the above code this exists in the select query @txtUserName.text. Is this right? Can I use it like this or should I declare something more after the create procedure statement?
On a ASP.net form, I need to add a textbox to update a date field in MS SQL server via stored procedure. I want to verify user input cant figure out the correct syntax to do so (please see below)
If d.Text <> "" Then cmd.Parameters.Add("@date", SqlDbType.DateTime) cmd.Parameters("@date").Value = Date.Parse(d.Text)
All of those disadvantages of stored procedures (database portability etc.) I am now facing. We are going to migrate our VB.Net/ASP/SQL Server application to something like Mono/Postgresql and fully internationalize.
One of the many issues we face is that we have 800-900 stored procedures. What we are thinking of doing is moving the logic in these SP's in to the application code. I have searched around for how people have done this or how it can be done however I have found very little. Most of the info on stored procedures is about what should be in them etc. So I have come to the conclusion that the best way to approach this is to leave the SQL retrieve/update type queries as stored procedures and move the ones with business logic in to the application.
