用vs2005开发存储过程遇到的数据库连接失败的问题
创建过程参考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).
[解决办法]
关注
[解决办法]
那你用这种方式写吧 微软提供的标准参考
我一直没问题 扩展也方便
[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);
}
}
