TSQL创建存储过程使用c#
问题描述:
我想在我的.net项目中创建一个过程(删除一个过程并重新创建它与新的数据)。当我在数据库“创建新查询”中运行相同的查询时,它工作正常,但是当我尝试在c#中运行它时,它给我一个错误。TSQL创建存储过程使用c#
private void makeprocedure()
{
string sqlProcedureCreate = @"
IF(OBJECT_ID('usp_HourData') IS NOT NULL)
DROP PROCEDURE IF EXISTS usp_HourData;
GO
CREATE PROCEDURE usp_HourData
AS
BEGIN
SELECT Employee.[First Name] + ' ' + Employee.[Last Name] AS 'Name',
sum(Time.[Total Hours]) AS 'Total Hours'
, FORMAT(Time.[Time in], 'd', 'en-gb') AS 'Worked On'
FROM Employee
inner join Time on
Employee.ID ='" + getID() + "' and Time.EmployeeIdFK = '" + getID()
+ "' WHERE Time.[Time in] between '" + CalendarStart.SelectedDate + "' and '" + CalendarEnd.SelectedDate
+ @"'GROUP BY FORMAT(Time.[Time in], 'd', 'en-gb') ,Employee.[First Name] + ' ' +Employee.[Last Name];
END
";
using (SqlCommand command = new SqlCommand(sqlProcedureCreate, con))
{
command.CommandType = CommandType.Text; //I tried command.CommandType = CommandType.StoredProcedure;
con.Open();
command.ExecuteNonQuery(); //Compiler says error is on this line
con.Close();
}
}
的错误是接近 “GO” 和 “CREATE/ALTER PROCEDURE” 必须先走了。
System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
HResult=-2146232060
LineNumber=4
Message=Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Number=102
Procedure=""
Server=(LocalDB)\MSSQLLocalDB
Source=.Net SqlClient Data Provider
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at WebApplication2.Manager.makeprocedure() in D:\Users\Albin\MyClockIn\WebApplication2\WebApplication2\Manager.aspx.cs:line 209
at WebApplication2.Manager.ButtonSearch_Click(Object sender, EventArgs e) in D:\Users\Albin\MyClockIn\WebApplication2\WebApplication2\Manager.aspx.cs:line 161
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:
我该如何解决这个错误?
答
这可能不是您的问题的答案,而只是关于您的编程风格的建议。这些在您的代码中是不好的东西:
- 您正在连接查询字符串。你应该使用参数。
- 您正在创建一个查询包含数据而不是参数的过程。存储过程应该包含带参数的查询,并且应该将数据传递给它。
存储过程不应该在每次访问时丢弃和重新创建。我可以清楚地看到,只要使用select语句就可以达到相同的结果。如果你正在创建存储过程,那么必须有一些数据才能通过,否则存储过程就没有意义了。
足够的理论,让我们看看在行动的事情:
这里是你的MakeProcedure(请重命名此)功能:
//Rename this function to some useful name
private void makeprocedure()
{
string query = @" SELECT Employee.[First Name] + ' ' + Employee.[Last Name] AS 'Name',
sum(Time.[Total Hours]) AS 'Total Hours'
, FORMAT(Time.[Time in], 'd', 'en-gb') AS 'Worked On'
FROM Employee
inner join Time on
Employee.ID = @EmpId and Time.EmployeeIdFK = @EmpId WHERE Time.[Time in]
between @StartDate and @EndDate GROUP BY FORMAT(Time.[Time in], 'd', 'en-gb'),
Employee.[First Name] + ' ' +Employee.[Last Name];";
using (SqlCommand command = new SqlCommand(query, con))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@EmpId", getID());
command.Parameters.AddWithValue("@StartDate", CalendarStart.SelectedDate);
command.Parameters.AddWithValue("@EndDate", CalendarEnd.SelectedDate);
con.Open();
var reader = command.ExecuteReader();
//do something with data in the reader.
con.Close();
}
}
在猜测,我会说你不能做多Visual Studio中的SQL命令。我不知道(我总是在SSMS中做),但这就是错误似乎表明的。 – Tim
你为什么需要从C#中完成它的原因是什么?通常这不是如何创建DB对象。 – objectNotFound
它应该是'command.ExecuteReader();' – Rahul