Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 15th, 2008, 11:43 AM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How to return nvarchar(max) to SQL server from CLR?
----------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString Select_Description(int Obj_ID)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            try
            {
                string sql = @"Select description from tbl_detail";

                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                return (SqlString)cmd.ExecuteScalar();

            }
            catch (Exception e)
            {
                throw e;
            }

            finally
            {
                conn.Close();
            }
        }
    }

};
----------------------

Code above work fine with any [description]< 4000 characters. Get an error if description field have values> 4000 characters.

Try to solve ==> Looking for the net they said change SqlString ==> SqlChars

Here my modify code
------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString Select_Description(int Obj_ID)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            try
            {
                string sql = @"Select description from tbl_detail";

                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                return (SqlChars)cmd.ExecuteScalar(); //<== is this correct syntax?

            }
            catch (Exception e)
            {
                throw e;
            }

            finally
            {
                conn.Close();
            }
        }
    }

};
------------------------

the error come when compile

Error 1 Cannot implicitly convert type 'System.Data.SqlTypes.SqlChars' to 'System.Data.SqlTypes.SqlString'. An explicit conversion exists (are you missing a cast?)

Please teach me how to solve this problem. Thank you.
I'm newbie on CLR and .NET

-------------------------------
Dear Gonzalo,

I try to make my data simple to explain I will post short data construct and values soon, hope you have time give me your hand to solve my problem about my SQL query.

 
Old January 20th, 2008, 09:36 PM
Authorized User
 
Join Date: Jan 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Gonzalo,


I have solve all my problem now.
---------------------------------
[Microsoft.SqlServer.Server.SqlFunction(FillRowMeth odName="Obj_Row",
                                                IsDeterministic=true,
                                                IsPrecise=true,
                                                TableDefinition = "ObjID int,ObjCategoryID int,ObjValue nvarchar(400)",
                                                DataAccess= DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.Read )] <== After add this line CLR funtion work fine.

My function A, call function B, and Store procedure C. I change some of them to CLR may be that need SystemDataAccessKind.Read ------------------------------

Other question about sqlchars, I solve that problem too. Thank you for reading my post.





Similar Threads
Thread Thread Starter Forum Replies Last Post
v urgent..Error: not all code paths return a value prv299 .NET Web Services 1 May 20th, 2008 06:17 AM
not all code paths return a value c# awaisfaisal C# 5 May 10th, 2007 08:02 AM
Hard coded paths pauliehaha Classic ASP Professional 8 February 6th, 2007 06:48 PM
paths table FT BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 December 12th, 2006 11:50 AM
Code to Remove Carriage Return From Spreadsheet Jersey Eric Excel VBA 2 May 12th, 2005 08:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.