用vs2005开发存储过程遇到的数据库连接失败的问题

[jiangchuandong]

创建过程参考msdn和cnblogs.com的文章步骤
http://www.cnblogs.com/ugoer/archive/2005/04/01/129986.html
(本机未装sqlserver2005,我在sqlexpress上建立的表)

稍微修改了下代码:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;


public partial class StoredProcedures
{
[SqlProcedure]
public static void Hello()
{
SqlContext.GetPipe().Send("hello ,uGoer!");
}

[SqlProcedure]
public static void InsertData(SqlString name)
{
SqlCommand InsertCurrencyCommand = SqlContext.GetCommand(); // 修改部分,原为SqlContext.GetCommand()方法;
InsertCurrencyCommand.CommandText = "INSERT INTO table1 (Name, addDate) VALUES ('"+name.Value+"', '" + DateTime.Now.ToString() + "')";
InsertCurrencyCommand.ExecuteNonQuery();
}

[SqlFunction]
public static SqlString testFunction()
{
return "hello , cnBlogs.com";
}
};

然后部署存储过程,在测试的时候前面的那个执行成功,而后面的方法执行未成功,是什么原因呢(Connection property has not been initialized)
错误:
Auto-attach to process '[472] sqlservr.exe' on machine 'cec-jcd' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'SqlServerProject1', Symbols loaded.
'CEC-JCD;.Net SqlClient Data Provider;1808' (Managed): Loaded 'C:\WINNT\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', No symbols loaded.
Hello World
A .NET Framework error occurred during execution of user defined routine or aggregate 'InsertData':
System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.
System.InvalidOperationException:
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at StoredProcedures.InsertData(SqlString name)
.
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll
The thread 'cec-jcd\sqlexpress [53]' (0x3f0) has exited with code 0 (0x0).
The program '[472] [SQL] cec-jcd: cec-jcd\sqlexpress' has exited with code 0 (0x0).
The program '[472] sqlservr.exe: Managed' has exited with code 259 (0x103).

[vengair]

[SqlProcedure]
public static void InsertData(SqlString name)
{
using (SqlConnection myConnection = new SqlConnection("context connection=true"))
{
myConnection.Open();
SqlCommand InsertCurrencyCommand = SqlContext.GetCommand(); // 修改部分,原为SqlContext.GetCommand()方法;
InsertCurrencyCommand.CommandText = "INSERT INTO table1 (Name, addDate) VALUES ('"+name.Value+"', '" + DateTime.Now.ToString() + "')";
InsertCurrencyCommand.ExecuteNonQuery();
}
}

[jxzhang615]

关注

[jiangchuandong]

楼上的朋友SqlContext.GetCommand() 方法是没有的,你看看msdn

[vengair]

那你用这种方式写吧 微软提供的标准参考
我一直没问题 扩展也方便
[SqlProcedure]
public static void sp_Question_GetLibraries()
{
using (SqlConnection myConnection = new SqlConnection("context connection=true"))
{
myConnection.Open();

SqlCommand myCommand = new SqlCommand("SELECT * FROM dbo.[Question.Libraries] ORDER BY [LibraryName]", myConnection);

SqlContext.Pipe.ExecuteAndSend(myCommand);
}
}