Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: Microsfot T-SQL Versus FoxPro Code
Thread ID: 394885 Message ID: 395520 # Views: 41 # Ratings: 6
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, January 15, 2014 5:24:31 PM         
   


Here is the sample that I promised. Sorry I couldn't do it sooner. This sample creates a User Defined Function (UDF) that returns a table ( referred to as Table Valued Function - TVF ).

You start by creating an SQL Server project in Visual Studio (The project name may be different between VS versions. Check for a template that says SQL Server C# project or similar). Our sample project is named "UDFWithCS". Starting the template gives you a code page with a "Hello World" in it. We replace the content and here is all the code we have:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;


public partial class UserDefinedFunctions
{
    struct CustomerVariant
    {
        public string CustomerID;
        public string Company;
        public string Contact;
    }

    [SqlFunction(
        DataAccess=DataAccessKind.Read,
        FillRowMethodName = "ParseName", 
        TableDefinition = "CustomerId nvarchar(10), CompanyName nvarchar(200), FirstName nvarchar(20), LastName nvarchar(20)")]
    public static IEnumerable GetContactList()
    {
        ArrayList al = new ArrayList();
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlCommand cmd = new SqlCommand(@"select * from Customers",con);
            con.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while(rdr.Read())
                {
                   al.Add( new CustomerVariant{ 
                       CustomerID=(string)rdr["CustomerId"], 
                       Company=(string)rdr["CompanyName"], 
                       Contact=(string)rdr["ContactName"]}
                       );
                }
            }
        }
        return al;
    }

    private static void ParseName(object source, out string CustomerId, out string CompanyName, out string FirstName, out string LastName)
    {
        // parse the contact name
        // set new column values
        CustomerVariant c = (CustomerVariant)source;
        CustomerId = c.CustomerID;
        CompanyName = c.Company;
        string[] nameParts = c.Contact.Split();
        LastName = nameParts[nameParts.Length - 1];
        Array.Resize(ref nameParts, nameParts.Length - 1);
        FirstName = string.Join(" ", nameParts);
    }
};


You may or may not also wrap in a namespace. What this code does is to get Customers from its database, manipulate and return the content with new columns in which it parses the contact name into first and last name columns. Here it is a simple parsing operation, yet this simple parsing is not something that is easy to express in T-SQL so should serve as a CLR based sample I guess.

On the SQL Server side basically you need to:
-Enable CLR if not enabled.

-- I don't know where and how to do this using visual tools
-- in code it is easy enough
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


-Compatibility level should be at least set to 2005 (90).
-- if you need to set to 2005, in code
EXEC sp_dbcmptlevel 'Northwind', 90


You can also check and change that by right clicking the database name and then selecting 'Properties\Options'. Be sure it is at least 90.

-Then you need to add the assembly to your database (once added dll becomes part of that database and may be deleted but surely you would want to keep it to yourself for debugging etc.) We give the assembly 'MySample' name.

CREATE ASSEMBLY [MySample] 
    from 'C:\Users\CetinBasoz\Documents\Visual Studio 2010\Projects\UDFWithCS\UDFWithCS\bin\Debug\UDFWithCS.dll'
WITH PERMISSION_SET = SAFE;


-And create your function that calls this CLR function:

CREATE FUNCTION [TransformCustomerName]()
RETURNS table (
  CustomerId Nvarchar(10), 
  CompanyName Nvarchar(200), 
  FirstName Nvarchar(20), 
  LastName Nvarchar(20) 
) 
AS External Name [MySample].[UserDefinedFunctions].GetContactList;


'TransformCustomerName' is the name we have given to use locally within our T-SQL calls.
'MySample' is the name we have given to assembly in the previous command
'UserDefinedFunctions' is the name of our class in our C# code (if we added a namespace then it would look like NameSpaceName.UserDefinedFunctions).
'GetContactList' is the method name we want to use.

We can test our function:

SELECT * FROM TransformCustomerName()


and we get rows like:

CustomerId	CompanyName				FirstName	LastName
ALFKI		Alfreds Futterkiste			Maria		Anders
ANATR		Ana Trujillo Emparedados y helados	Ana		Trujillo
ANTON		Antonio Moreno Taquería			Antonio		Moreno
AROUT		Around the Horn				Thomas		Hardy
BERGS		Berglunds snabbköp			Christina	Berglund
...


HTH



Cetin Basoz

Give some sample data in code for your question - here is the tool you need to generate the code

My Blog


MongoDb Certified Developer
MongoDb Certified DBA


Support Wikipedia

ENTIRE THREAD

Microsoft T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/8/2014 6:29:04 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Borislav Borissov @ 1/8/2014 6:45:06 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Michel Levy @ 1/8/2014 6:56:08 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/8/2014 7:37:06 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Michel Levy @ 1/8/2014 8:49:15 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/9/2014 6:22:31 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cetin Basoz @ 1/10/2014 9:00:10 AM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Leslie Botchway @ 1/10/2014 9:14:48 AM
RE: Microsfot T-SQL Versus FoxPro Code Posted by David Mustakim @ 1/10/2014 9:22:49 AM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cetin Basoz @ 1/11/2014 5:03:40 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by David Mustakim @ 1/11/2014 5:43:39 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cetin Basoz @ 1/15/2014 5:26:33 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/10/2014 12:59:38 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cetin Basoz @ 1/15/2014 5:24:31 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/16/2014 3:26:31 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cetin Basoz @ 1/16/2014 6:45:36 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Pete Sass @ 1/8/2014 7:29:26 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/9/2014 6:24:33 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cecil Champenois @ 1/13/2014 9:20:00 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by David Mustakim @ 1/13/2014 9:32:11 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Cetin Basoz @ 1/8/2014 7:35:25 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Victor Espina @ 1/8/2014 8:58:17 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Michel Levy @ 1/10/2014 1:55:39 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Victor Espina @ 1/16/2014 12:22:07 AM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Michel Levy @ 1/16/2014 10:21:18 AM
RE: Microsfot T-SQL Versus FoxPro Code Posted by Victor Espina @ 1/16/2014 4:46:15 PM
RE: Microsfot T-SQL Versus FoxPro Code Posted by tom knauf @ 1/9/2014 6:07:46 PM