Blog Archives

MSSQL User Defined Functions vs Stored Procedures

I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”

While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.

Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.

Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.

References
SP_who filtering UDF vs SP, http://stackoverflow.com/questions/2567141/use-sql-to-filter-the-results-of-a-stored-procedure
Data Manipulation Language, http://en.wikipedia.org/wiki/Data_manipulation_language
(Some) Differences (about.com), http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
(Some) Differences (stackoverflow), http://stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions

Advertisements

mssql aes function encrypt decrypt in microsoft sql

Simple symmetric encryption in MSSQL. Copied from MS references below:

USE AdventureWorks2012;
--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard 
    ADD CardNumber_Encrypted varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
    , CreditCardID)));
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.

SELECT CardNumber, CardNumber_Encrypted 
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 , 
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

References:

http://msdn.microsoft.com/en-us/library/bb669072.aspx

http://msdn.microsoft.com/en-us/library/ms179331.aspx

Non-static method requires a target

In my specific situation where I received this error, I was able to resolve it by simply changing “null” to the object containing the method which needed to be call.

This error indicates a use of reflections that can easily be confusing, but is very simple to resolve.

The specific resolution in the (StackOverflow) example in the referenced link below did not apply to my situation, but helped me quickly understand what caused the error and come to quick resolution by simply reading through the responses.

Example code from reference below:

public static T Test<T>(MyClass myClass) where T : MyClass2, new()
{
    var result = new T();
    //...
}

For specific code example where I encountered this message and how I was able to resolve, see the snippet below or my full post Call Parent Page from User Control.

Quick comparison where I encountered the error:

if (mi!=null) mi.Invoke(null, new Object[] {sender,e }); //throws indicated error

if (mi!=null) mi.Invoke(this.Page, new Object[] {sender,e }); //runs through clean

References
“Call Parent Page from User Control”, https://ronniediaz.com/2011/07/15/call-parent-page-from-user-control/
StackOverflow, http://stackoverflow.com/questions/3577407/non-static-method-requires-a-target-in-propertyinfo-setvalue

call parent page from user control to invoke page methods

To illustrate how we will accomplish this using Reflections, we will be using repeater in a user control within a page.

Your repeater loads data easily within the user control code behind, but you want to utilize the “ItemCommand” event of the repeater to update some values on the page page.

Here’s how this can work:

Parent Page:

//the name doesn't have to match the corresponding method on user control but makes code more readable
    public void rptRepeaterName_ItemCommand(object sender, RepeaterCommandEventArgs e)
    {

        try
        {
            if (e.CommandName == "commandname")
            {
               //do some work on data then call update on parent page to reload data and show within a modal

                ParentPage_UpdatePanel.Update();
                ParentPage_ModalPopup.Show();

            }
        }
        catch (Exception ex)
        { 
//do something
            throw new ApplicationException(ex.ToString());
        }

    }

User Control:

    protected void rptRepeaterName_ItemCommand(object sender, RepeaterCommandEventArgs e)
    {
        MethodInfo mi = this.Page.GetType().GetMethod("rptRepeaterName_ItemCommand", BindingFlags.Public | BindingFlags.Instance);

//note we specify parent page as the object and pass in a new object representing our repeater and carrying its parameters
        if (mi!=null) mi.Invoke(this.Page, new Object[] {sender,e });

    }

Voila! Behold the power of Reflections! 8)

In particular, one of the references below (thanks Bruce Barker!) helped me come to this answer, however, the exact code he presents will result in a “Non-static method requires a target” error.

To avoid this error, make sure you always pass in the object when invoking a method that is non-static (within a class that is instantiated).

To learn more about reflections and how it works search my blog for other examples, and visit MSDN for a good overview.

References
Velocity Reviews, http://www.velocityreviews.com/forums/t71075-invoke-methods-on-the-parent-page-from-the-user-control.html
Reflection Overview (MSDN), http://msdn.microsoft.com/en-us/library/f7ykdhsy%28v=vs.71%29.aspx

Lambda Functions in .Net

Lambda expressions in .Net are denoted by the ‘=>’ symbol.

These powerful operators can be used to create sleek, optimized loops that perform much faster.

The usage below condenses the equivalent of at least 20 lines of two loops.

(Sorry kiddos, no time for VB conversion…C# only this time. ;))

Edit (20101229): Lambda rocks! Simply amazing! Greatest ever!

Ex. 1:

            PaymentMethodCollection visiblePaymentMethods = new PaymentMethodCollection();

            DataSet myPaymentMethods = GlobalDataLoads.PaymentMethods();
            List<DataRow> pmids = myPaymentMethods.Tables[0].Select("Visible = 1").ToList();
            List<int> ids = pmids.Select(i => i["PaymentMethodId"]).Cast<int>().ToList(); 
//.Cast<int>().ToList();
            List<PaymentMethod> pm = availablePaymentMethods.FindAll(i => ids.Contains(i.PaymentMethodId));

            availablePaymentMethods.Clear();
            availablePaymentMethods.AddRange(pm);

Ex. 2:

USER user = tol_dc.USERs.Single(u => u.USER_ID == (int)userid);

Ex. 3:

//DataContext tol_dc;
            List<int> courseids = registrationList.Select(i=>i.MODULE_ID).ToList();
            List<MODULE> modulenames = tol_dc.MODULEs.Where(i=>courseids.Contains(i.MODULE_ID)).Cast<MODULE>().ToList();

Ex. 4: (Get the value of data item inside the child repeater of a parent repeater if the UID is known)

//RepeaterCommandEventArgs e
        if ((stringvalueineed==null) || (stringvalueineed=="")) {
        int uid= (int)((System.Data.DataTable)rptCourses.DataSource).Rows[e.Item.ItemIndex]["UID"];
        stringvalueineed= ((System.Data.DataRow[])(((Repeater)rptOne.Items[e.Item.ItemIndex].FindControl("rptTwo")).DataSource)).Single(i => (int)i["UID"] == uid)["COLUMNNAME"].ToString();
        }

Ex. 5a: (filter a list of available products by removing all excluded products from the list, such as those out of stock)

        private bool Load_AvailableProducts()
        {
            string region = Session["region"];

            lbAvailableProducts.Items.Clear();
            LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();

            List<LinqData.GET_PRODUCT_LISTResult> allproducts = (from p in dc.GET_PRODUCT_LIST(region)
                                       select p).ToList();
            List<int> excproducts = (from ListItem m in lbExcludedProducts.Items
                                       select Convert.ToInt32(m.Value)).ToList();

            List <LinqData.GET_PRODUCT_LISTResult> availproducts = allproducts.Where(p => !excproducts .Contains(p.ProductID)).ToList();

            lbAvailableProducts.DataSource = availproducts;

            lbAvailableProducts.DataTextField = "ProductName";
            lbAvailableProducts.DataValueField = "ProductID";
            lbAvailableMarkets.DataBind();
            return true;
        }

Ex. 5b: (same as above.in this version code is smaller and more load is placed on SQL server rather application (IIS). analyze with SQL profiler and you’ll see the difference)

        private bool Load_AvailableProducts()
        {
            string region = Session["Region"];

            lbAvailableProducts.Items.Clear();
            LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();

            List<LinqData.GET_PRODUCT_LISTResult> availproducts = (from i in dc.GET_PRODUCT_LIST(region)
                                       select i).Where(m => !(from ListItem l in lbExcludedProducts.Items
                                       select Convert.ToInt32(l.Value)).ToList().Contains(m.ProductID)).ToList();

            lbAvailableProducts.DataSource = availproducts;

            lbAvailableProducts.DataTextField = "ProductName";
            lbAvailableProducts.DataValueField = "ProductID";
            lbAvailableProducts.DataBind();
            return true;
        }

The lambda one-liner indicated in the snippet below essentially nests at least two “for/foreach” loops. See my related article here for full source.

Ex. 6:

protected void LoadFactories()
    {
        ClearLists();

        if (ddlSegment.SelectedIndex > 0)
        {
            using (AndroneticsDataContext db = new AndroneticsDataContext(
        ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString))
            {
                List<UP_GET_FactoriesResult> allFactories = db.UP_GET_Factories(Convert.ToInt32(ddlRegion.SelectedValue), null).ToList();

                ////this would be approximately how you would approach traditionally
                //foreach (UP_GET_FactoriesResult r in allFactories)
                //{
                //    if (GetUnregisteredRobots(r.FACTORY_ID).Count != 0)
                //    {
                //        //etc, would need another loop here
                //    }
                //}

                //now for the lambda way
                var Factories = allFactories.Where(
                    r => GetUnregisteredRobots(r.FACTORY_ID).Where(
                        ra => !lstDecommissionRobots.Items.Contains(new ListItem(ra.ROBOT_NAME,ra.ROBOT_ID.ToString()))).Count() != 0);

                ddlFactories.DataSource = Factories;
                ddlFactories.DataTextField = "FACOTRY_TITLE";
                ddlFactories.DataValueField = "FACTORY_ID";
                ddlFactories.DataBind();

                ListItem li = new ListItem("", "");
                ddlFactories.Items.Insert(0, li);
            }
        }
    }

    private List<UP_LOAD_COURSE_RobotsResult> GetUnregisteredRobots(int courseid)
    {
        using (AndroneticsDataContext db = new AndroneticsDataContext(
        ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString))
        {
            List<UP_LOAD_COURSE_RobotsResult> allRobots = db.UP_LOAD_COURSE_Robots(courseid).ToList();

            int userID = int.Parse(Request.QueryString["ID"]);
            List<string> excregisteredRobots = (from m in db.UP_GET_REGISTERED_Robots(userID) select m.MODULE_NAME).ToList();

            return allRobots.Where(m => !excregisteredRobots.Contains(m.MODULE_NAME)).ToList();
        }
    }

Ex. 7 (foreach)

//make sure list first
List<mydatatype> data = ReturnSomeData().ToList();

//technically long way but helps understand what is happening
Func<mydatatype, string> f = (a) => a.somepropertyname = "somevalue";
                    data.ForEach(i => f(i));

//or simply

data.ForEach(c => c.somepropertyname = "somevalue";

Ex. 8 Lambda Cast to Custom Object (list of objects from one type to another)

//DAL.StoreProcedures is simply a static class with functions that instantiate and dispose of datacontexts and return object collections from data calls

List<geocode> geocodes = DAL.StoredProcedures.LoadGeoCodes_Zip(market, datestart).Select(g=> new geocode {lat=g.Latitude.ToString(),lng=g.Longitude.ToString()}).Cast<geocode>().ToList();

References:
MSDN, “Lambda Expressions (C# Programming Guide)”, http://msdn.microsoft.com/en-us/library/bb397687.aspx
MSDN C# Developer Center, “101 Linq Samples”, http://msdn.microsoft.com/en-us/vcsharp/aa336746
Fraction of The Blogosphere, https://ronniediaz.com/2010/12/21/lambda_functions_in_-net/
StackOverflow, http://stackoverflow.com/questions/1909268/convert-a-list-of-objects-from-one-type-to-another-using-lambda-expression