Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Microsfot T-SQL Versus FoxPro Code
Thread ID: 394885 Message ID: 395631 # Views: 29 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, January 16, 2014 3:26: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

Thanks Cetin. I printed this so I can try it out. I appreciate the effort you went to for this sample. Thank you so much!
Cecil

Cecil Champenois, Jr.

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