system.Data.SqlServer for CRL integration ??

Hot Topic

Pass custom cookie from external web application to Report Manager
conditional formatting - noob question
Connectivity issues
Import objects from SSAS to Business Intelligence Development Studio
Unable to get all records
Copying columns to table with different design
Internal Query Processor Error
Cannot in install samples.
how to clean all messages of a queue ?
SSIS in MSSQL 2005

SQL Server

Updating data source schemas for cubes
SSIS - XML config file
Authentication Issues
Accelerating an update statement
Generic staging design of data warehouse
Article - delete all data property
Connection Manager to connect ot Access mdb
[Resolved] How to insert a byte() to a blob column?
How do you return query results in a streaming table-valued function?
Reports using Dynamic Columns

system.Data.SqlServer for CRL integration ??

After installing Visual Studio beta2 with SQL express, I try to use common Language Runtime to create Stored proc. But in the example I follow I got to do this But in my setup I cant add system.data.sqlServer. This namespace is not valid and return an cannot be found error. Do you think I got to install SQL server full version to use CLR Here is my code:

Imports System.Data
Imports System.Data.Sql
Imports system.Data.SqlServer

Public Class SqlData

   Public Shared Sub GetSources(ByVal Usager As String)

      Dim sp As SqlPipe = SqlContext.GetPipe()

      sp.Send("Address Type ID is " + addressTypeID.ToString() + "\n")

      Dim cmd As SqlCommand = SqlContext.GetCommand()

      cmd.CommandText = "SELECT * " & "FROM Person.AddressType " & "Where AddressTypeID=" & addressTypeID.ToString()

      Dim rdr As SqlDataReader = cmd.ExecuteReader()
      sp.Send(rdr)
   End Sub
End Class

Answer this question

system.Data.SqlServer for CRL integration ??

  • GoingDotNet

    You'll get a command as you would from client-side:
    SqlCommand cmd = new SqlCommand();

    and then assign your connection to the command, or get the command from the factory method on the SqlConnection. Assuming the SqlConnection instance is conn:
    conn.CreateCommand();

    Niels

  • ps0118mj

    maybe I go in the wrong direction.

    SQL2005 is very integrated in VS2005. It is an best way to program stored proc in VB directly in an class present in the project or the old way are always the best (create the stored proc in SQL in transac SQL and call it from our vb code).

    I follow this example:
    http://www.devx.com/dotnet/Article/21286

    FOr me is more easy to program complex SQL querryn in vb.net. With this example I supposed to be able to create som stored proc in an special class, compile it and export it in sql2005. But when I try to reproduce it I have some error generated in the fact that sql server namespace do not exist anymore.

  • Giotto

    The latest version of SQL Server does away with the SQLServer namespace and merges it with the SQLClient namespace.

    So theres no more SQLServer namespace apparently!

  • Brad Bass

    thanks niels and lee_Dale for your help. I will check this way.

    Whats about the SqlContext.GetCommand() This command is replaced by what because GetCommand is not member of microsoft.sqlserver.server.sqlcontext .

  • Phillip Shentu

    OK, a couple of things:
    1. As someone mentioned earlier in this thread, the server provider and the client side provider has been merged quite a while ago. So you need a reference to System.Data.dll. The namespace you need to use is Microsoft.SqlServer.Server.

    2. Also as someone said, you need a connection, which you create through (I apologize for the C# syntax):
    SqlConnection conn = new SqlConnection("Context Connection = true");

    3. To send data back to the client you use the SqlPipe class. However as you noticed, the GetPipe() does not exist any more. You get SqlPipe through the Pipe property on the SqlContext class:
    SqlPipe p = SqlContext.Pipe;

    This should get you started (I hope).

    Niels

  • LI Engineer

    thanks for the fast response!

    I change SQLServer namespace for SQLClient. But The line:

    Dim sp As SqlPipe = SqlContext.GetPipe()

    make an error on Dim sp As SqlPipe with is not defined

    Any suggestion

    Do someone have an good exemple for the creation of stored proc. in VB.net


  • MKU

    The SQLServer namespace is being discontinued in beta 3 so it may not be the problem.

    Are you talking about creating a stored proc within SQL Server using .net language or  from an external vb.bet app

    You can execute a SQL Create Procedure command like so:

    Imports System.Data.SqlClient
    .....
    Dim cn As sqlConnection = New sqlConnection('<connect string>')
    Dim cm As sqlCommand = New sqlCommand

    cm.Connection = cn
    cm.CommandType = CommandType.Text
    cm.CommandText = "CREATE PROCEDURE myProc ()"
    cm.ExecuteNonQuery

  • system.Data.SqlServer for CRL integration ??
Answer this question