Thursday, August 29, 2013 #

SQL SERVER - Creating A Stored Procedure for a Select statement with Optional Parameters

Meanwhile while I'm on bench (yep, mapabasketball man o sa trabaho suki ata ako dito hahaha.) I managed to entertain myself and do some self studying. I fancied having my own Class library for CRUD operations (Im still not jumping into the EF bandwagon as Im not sure if the project that hopefully I will be in will be using one. If ever that's the case then I will still have a treasure waiting for me if ever i'll be moved to another project). My research (Online Research) led to me learning interfaces to impose a blue print for the class



Interface ICrud(Of T)

Function Insert(obj As T) As Boolean

Function [Select](obj As T) As DataTable

Function Update(obj As T) As Boolean

Function Delete(obj As T) As Boolean

End Interface 


(got the idea from here)


then one thing led to the other and I decided to learn more about SQL Stored Procedures (SP). I was thinking of using them for fetching data or my select statement,delete, insert and update.

After finishing the SP for Insert, update and Delete I had my problems with my SP for Select (I used the Company Table for studying, c/o Humprey Cogay form our previous MVC Tutorial)


A. Flexibility of the Parameters (Optional Parameters)

B. Flexibility of Query (Less TSQL (If statements, Case) the better)


For Problem A what I did was :


USE [Testing]

GO

Create Procedure [dbo].[sp_S_CompanyRecord]

(

@ID int = null,

@CompanyName Varchar(100) = null,

@ContactNo Varchar(100)=null,

@ContactPerson Varchar(100)=null,

@Description Varchar(100) = null

)


For Problem B at first I thought of catching the null values with if and my code will look like this :

if @id = null

if @ContactNo= null

begin

if @ContactPerson = null

begin

if @Description= null

begin

select * from company;

end

end

end

and the if statement will nest again if the params are not null.


Then after several revisions/tinkering of my code I was able to produce a friendlier T-SQL

select * from testing.dbo.company

where id = isnull(@id,id)

and CompanyName = isnull(@CompanyName,CompanyName)

and contactno = isnull(@contactNo,ContactNo)

and ContactPerson = isnull(@ContactPerson,ContactPerson)

and Description= isnull(@Description,Description);

Explanation: field = isnull(@Param,field) tells you that if the the Parameter has a null value it will assign the field vale be used instead of parameter value.



The whole SP here:



Create Procedure [dbo].[sp_S_CompanyRecord]

(

@ID int = null,

@CompanyName Varchar(100) = null,

@ContactNo Varchar(100)=null,

@ContactPerson Varchar(100)=null,

@Description Varchar(100) = null

)

as

select * from testing.dbo.company

where id = isnull(@id,id)

and CompanyName = isnull(@CompanyName,CompanyName)

and contactno = isnull(@contactNo,ContactNo)

and ContactPerson = isnull(@ContactPerson,ContactPerson)

and Description= isnull(@Description,Description);



I will also try this trick for my SP on Update.#

Posted On Thursday, August 29, 2013 2:32 PM | Comments (0)

Saturday, September 8, 2012 #

Entity Framework - Single EMDX Mapping Multiple Database


Because of my recent craze on Entity Framework thanks to Sir Humprey, I have continuously searched the Internet for tutorials on how to apply it to our current system.


So I've come to learn that with EF, I can eliminate the numerous coding of methods/functions for CRUD operations, my overly used assigning of connection strings, Data Adapters or Data Readers as Entity Framework will map my desired database and will do its magic to create entities for each table I want (using EF Powertool) and does all the methods/functions for my Crud Operations.


 But as I begin applying it to a new project I was assigned to, I realized our current server is designed to contain each similar entities in different databases. For example Our lookup tables are stored in LookupDb, Accounting-related tables are in AccountingDb, Sales-related tables in SalesDb. My dilemma is I have to use an existing table from LookupDB and use it as a look-up for my new table. 


Then I have found Miss Rachel's Blog (here)Thank You Miss Rachel!  which enables me to let EF think that my TableLookup1 is in the AccountingDB using the following steps.


 Im on VS 2010, I am using C# , Using Entity Framework 5, SQL Server 2008 as our DB Server



Step 1:

Creating A SQL Synonym. If you want a more detailed discussion on synonyms, this was what i have read -> (link here). To simply put it, A synonym enabled me to simplify my query for the Look-up table when I'm using the AccountingDB from


SELECT [columns] FROM LookupDB.dbo.TableLookup1


to

SELECT [columns] FROM TableLookup1


Syntax: CREATE SYNONYM  TableLookup1(1) FOR LookupDB.dbo.TableLookup1 (2)


1. What you want to call the table on your other DB

2. DataBaseName.schema.TableName


Step 2:


We will now follow Miss Rachel's steps. you can either visit the link on the original topic I posted earlier or just follow the step I made.


1. I created a Visual Basic Solution that will contain the 4 projects needed to complete the merging

2. First project will contain the edmx file pointing to the AccountingDB

3. Second project will contain the edmx file pointing to the LookupDB

4. Third Project will will be our repository of the merged edmx file. Create an edmx file pointing To AccountingDB as this the database that we created the Synonym on.

Reminder: Aside from using the same name for the Entities, please make sure that you have the same Model Namespace for all your Entities 

5. Fourth project that will contain the beautiful EDMX merger that Miss Rachel created that will free you from Hard coding of the merge/recoding the Edmx File of the third project everytime a change is done on either one of the first two projects' Edmx File.

6. Run the solution, but make sure that on the solutions properties Single startup project is selected and the project containing the EDMX merger is selected.

7. After running the solution, double click on the EDMX file of the 3rd project and set Lazy Loading Enabled = False. This will let you use the tables/entities that you see in that EDMX File.

8. Feel free to do your CRUD Operations.

I don't know if EF 5 already has a feature to support synonyms as I am still a newbie on that aspect but I have seen a linked where there are supposed suggestions on Entity Framework upgrades and one is the "Support for multiple databases"


 So that's it! Thanks for reading!





Posted On Saturday, September 8, 2012 1:25 AM | Comments (0)