C＃中的Ms sqlserver存储过程问题 [英] Ms sqlserver store procedure problem in C#

问题描述

我在Ms SQL server中编写了一个存储过程。在那个过程中，我使用了一些try catch块。如果我从SQL服务器管理工​​作室运行该存储过程，它运行正常。如果首先尝试阻止结果错误，它将转到其他块。但我从c＃调用的那个程序。在该存储过程结果错误的c＃第一个块中，它不会进入该存储过程的下一个块。简单就是显示错误。如何解决



我的尝试：



我用Google搜索解决方案但无法找到合适的解决方案

  USE  [inventoryDB] 
  GO  
  / *   * ****对象：StoredProcedure [dbo]。[clearinv]脚本日期：05-10-2018 18:42:11 ****** /  
  GO  
  GO  
  ALTER   PROCEDURE  [dbo]。[SPNULL] 
（
  @ errorstat   AS   INT  =  0  
）
  AS  
  WHILE   @ errorstat  =  0  
  BEGIN  
  BEGIN  TRY 
  UPDATE  suppDB 
  SET  ldgrid =  0  
  WHERE  ldgrid  IS   NULL  
 
  ALTER   TABLE  suppDB 
  ALTER   COLUMN  [ldgrid] INTEGER  NOT   NULL  
 
  ALTER   TABLE  suppDB 
  ADD   CONSTRAINT  sldgrid 
  DEFAULT   0   FOR  ldgridif  @@ ERROR <>  0  或  @@ ERROR  =  0  
 
  BEGIN  
  PRINT  '  0' 
  END  
  END  TRY 
  BEGIN  CATCH 
 < span class =code-keyword> PRINT  '  1' 
 < span class =code-keyword> END  CATCH 
  END  
  GO  
 
  DECLARE   @ errorstat   AS   INT  =  0  
  WHILE   @ errorstat  =  0  
  BEGIN  
  BEGIN  TRY 
 更新 suppDB1718 
  SET  ldgrid =  0  
  WHERE  ldgrid  IS   NULL  
 
  ALTER  表 suppDB1718 
  ALTER   COLUMN  [ldgrid] INTEGER  NOT   NULL  
 
  ALTER  表 suppDB1718 
  ADD   CONSTRAINT  sldgrid1718 
  DEFAULT   0  
  FOR  ldgridif  @@ ERROR <>  0  或  @@ ERROR  =  0  
 
  BEGIN  
  PRINT  '  0' 
  END  
  END  TRY 
  BEGIN  CATCH 
 < span class =code-keyword> PRINT  '  2' 
 < span class =code-keyword> END  CATCH 
  END  
  GO  
 
  IF   @@ ERROR <>  0   OR   @@ ERROR  =  0   DECLARE   @ errorstat   AS   INT  =  0  
  BEGIN 尝试
 更新 suppDB1819 
  SET  ldgrid =  0  
  WHERE  ldgrid  IS   NULL  
 
  ALTER   TABLE  suppDB1819 
  ALTER   COLUMN  [ldgrid] INTEGER  NOT   NULL  
 
  ALTER  表 suppDB1819 
  ADD   CONSTRAINT  sldgrid1819  DEFAULT   0   FOR  ldgrid 
  END 尝试
  BEGIN  CATCH 
  PRINT  '  3' 
  END  CATCH 
  GO  
 
  IF   @@ ERROR <>  0   OR   @@ ERROR  =  0   DECLARE   @ errorstat   AS   INT  =  0  
  IF   @ errorstat  =  0  
  BEGIN  TRY 
  UPDATE  suppDB  SET  jvno =  0   WHERE  jvno  IS   NULL  
 
  ALTER   TABLE  suppDB 
  ALTER   COLUMN  [jvno] INTEGER  NOT   NULL  
 
  ALTER   TABLE  suppDB 
  ADD   CONSTRAINT  sjvno  DEFAULT   0   FOR  jvno 
  END 尝试
  BEGIN  CATCH 
  PRINT  '  4' 
  END  CATCH 
  GO  
 
  IF  < span class =code-systemcall> @@ ERROR <>  0   OR   @@ ERROR  =  0   DECLARE   @ errorstat   AS   INT  =  0  
  IF   @ errorstat  =  0  
  BEGIN  TRY 
 更新 suppDB1718  SET  jvno =  0   WHERE  jvno  IS   NULL  
 
  ALTER  表 suppDB1718 
  ALTER   COLUMN  [jvno] INTEGER  NOT   NULL  
 
  ALTER  表 suppDB1718 
  ADD   CONSTRAINT  sjvno1718  DEFAULT   0   FOR  jvno 
  END  TRY 
  BEGIN  CATCH 
  PRINT  '  5' 
 结束 CATCH 
  GO  
 
  IF   @@ ERROR <>  0  或  @@ ERROR  =  0   DECLARE   @ errorstat   AS   INT  =  0  
  IF   @ errorstat  =  0  
  BEGIN  TRY 
  UPDATE  suppDB1819  SET  jvno =  0   WHERE  jvno  IS   NULL  
 
  ALTER  表 suppDB1819 
  ALTER   COLUMN  [jvno] INTEGER  NOT   NULL  
 
  ALTER  表 suppDB1819 
  ADD   CONSTRAINT  sjvno1819  DEFAULT   0   FOR  jvno 
  END  TRY 
  BEGIN  CATCH 
  PRINT  '  6' 
 结束 CATCH 
  GO  

解决方案

< blockquote> 0）允许异常发生以实现某种任意形式的错误处理只是一种不好的做法。例外通常应保留用于意外问题。



1）如果不需要，重复执行流程是没有意义的。



2）如果数据库中已存在数据，更改列的类型将导致数据被删除，因此我将该代码删除了。



试试这段代码：

   -  -   如果尚未设置默认值 
  IF （ SELECT  COLUMN_DEFAULT  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = '  suppDB'  AND  COLUMN_NAME =   ldgrid'） IS   NULL  
  ALTER  表 suppDB  ADD   C. ONSTRAINT  DF_suppDB_ldgrid  DEFAULT   0   FOR  ldgrid; 
 
  IF （ SELECT  COLUMN_DEFAULT  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = '  suppDB1718'  AND  COLUMN_NAME = '  ldgrid' ） IS   NULL  
  ALTER  表 suppDB1718  ADD   CONSTRAINT  DF_suppDB1718_ldgrid  DEFAULT   0   FOR  ldgrid; 
 
  IF （ SELECT  COLUMN_DEFAULT  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = '  suppDB1819'  AND  COLUMN_NAME = '  ldgrid' ） IS   NULL  
  ALTER  表 suppDB1819  ADD   CONSTRAINT  DF_suppDB1819_ldgrid  DEFAULT   0   FOR  ldgrid; 
 
  IF （ SELECT  COLUMN_DEFAULT  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = '  suppDB'  AND  COLUMN_NAME = '  jvno' ） IS   NULL  
  ALTER  表 suppDB  ADD   CONSTRAINT  DF_suppDB_jvno  DEFAULT   0   FOR  jvno; 
 
  IF （ SELECT  COLUMN_DEFAULT  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = '  suppDB1718'  AND  COLUMN_NAME = '  jvno' ） IS   NULL  
  ALTER  表 suppDB1718  ADD   CONSTRAINT  DF_suppDB1718_jvno  DEFAULT   0   FOR  jvno; 
 
  IF （ SELECT  COLUMN_DEFAULT  FROM  INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = '  suppDB1819'  AND  COLUMN_NAME = '  jvno' ） IS   NULL  
  ALTER  表 suppDB1819  ADD   CONSTRAINT  DF_suppDB1819_jvno  DEFAULT   0   FOR  jvno; 
 
 更新 suppDB  SET  ldgrid =  0   WHERE  ldgrid  IS   NULL ; 
 更新 suppDB1718  SET  ldgrid =  0   WHERE  ldgrid  IS   NULL ; 
 更新 suppDB1819  SET  ldgrid =  0   WHERE  ldgrid  IS   NULL ; 
 更新 suppDB  SET  jvno =  0   WHERE  jvno  IS   NULL ; 
 更新 suppDB1718  SET  jvno =  0   WHERE  jvno  IS   NULL ; 
 更新 suppDB1819  SET  jvno =  0   WHERE  jvno  IS   NULL ; 

I Wrote one store procedure in Ms SQL server. In that procedure I used some try catch blocks. If I run that store procedure from SQL server Management studio ,it is running fine.If first try block result error, it is going to other block. But That procedure I am calling from c#. In c# first block of that store procedure result error then it is not going next block of that store procedure. Simply It is showing error. How to solve that

What I have tried:

I googled for solution but unable to find right one

USE [inventoryDB]
GO
/****** Object:  StoredProcedure [dbo].[clearinv]    Script Date: 05-10-2018 18:42:11 ******/
GO
GO
ALTER PROCEDURE [dbo].[SPNULL]
(
    @errorstat AS INT = 0
)
AS
	WHILE @errorstat = 0
	BEGIN
		BEGIN TRY
			UPDATE suppDB 
			SET ldgrid = 0 
			WHERE ldgrid IS NULL

			ALTER TABLE suppDB 
			ALTER COLUMN [ldgrid] INTEGER NOT NULL

			ALTER TABLE suppDB 
			ADD CONSTRAINT sldgrid  
			DEFAULT 0 FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0

			BEGIN 
				PRINT '0'
			END
		END TRY
		BEGIN CATCH
			PRINT '1'
		END CATCH
	END
	GO

	DECLARE @errorstat AS INT = 0
	WHILE @errorstat = 0
	BEGIN
		BEGIN TRY
			UPDATE suppDB1718 
			SET ldgrid = 0 
			WHERE ldgrid IS NULL
		
			ALTER TABLE suppDB1718 
			ALTER COLUMN [ldgrid] INTEGER NOT NULL
		
			ALTER TABLE suppDB1718 
			ADD CONSTRAINT sldgrid1718  
			DEFAULT 0 
			FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0
		
			BEGIN 
				PRINT '0'
			END
		END TRY
		BEGIN CATCH
			PRINT '2'
		END CATCH
	END
	GO

	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	BEGIN try
		UPDATE suppDB1819 
		SET ldgrid = 0 
		WHERE ldgrid IS NULL

		ALTER TABLE suppDB1819 
		ALTER COLUMN [ldgrid] INTEGER NOT NULL
	
		ALTER TABLE suppDB1819 
		ADD CONSTRAINT sldgrid1819 DEFAULT 0 FOR ldgrid
	END try
	BEGIN CATCH
		PRINT '3'
	END CATCH
	GO
	
	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	IF @errorstat = 0
	BEGIN TRY
		UPDATE suppDB SET jvno = 0 WHERE jvno IS NULL

		ALTER TABLE suppDB 
		ALTER COLUMN [jvno] INTEGER NOT NULL

		ALTER TABLE suppDB 
		ADD CONSTRAINT sjvno DEFAULT 0 FOR jvno
		END try
	BEGIN CATCH
		PRINT '4'
	END CATCH
	GO

	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	IF @errorstat = 0
	BEGIN TRY
		UPDATE suppDB1718 SET jvno = 0 WHERE jvno IS NULL

		ALTER TABLE suppDB1718 
		ALTER COLUMN [jvno] INTEGER NOT NULL

		ALTER TABLE suppDB1718 
		ADD CONSTRAINT sjvno1718 DEFAULT 0 FOR jvno
	END TRY
	BEGIN CATCH
		PRINT '5'
	END CATCH
	GO

	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	IF @errorstat = 0
	BEGIN TRY
		UPDATE suppDB1819 SET jvno = 0 WHERE jvno IS NULL

		ALTER TABLE suppDB1819 
		ALTER COLUMN [jvno] INTEGER NOT NULL

		ALTER TABLE suppDB1819 
		ADD CONSTRAINT sjvno1819 DEFAULT 0 FOR jvno
	END TRY
	BEGIN CATCH
		PRINT '6'
	END CATCH
	GO

解决方案

0) Allowing an exception to happen to implement some arbitrary form of error handling is just plain bad practice. Exceptions should generally be reserved for unexpected problems.

1) There's no point in repeatedly performing a process if it doesn't need to be done.

2) If there's already data in the database, altering a column's type will cause the data to be removed, so I took that code out.

Try this code:

--if the default value has not been set
IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB' AND COLUMN_NAME = 'ldgrid') IS NULL
   ALTER TABLE suppDB ADD CONSTRAINT DF_suppDB_ldgrid DEFAULT 0 FOR ldgrid;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1718' AND COLUMN_NAME = 'ldgrid') IS NULL
    ALTER TABLE suppDB1718 ADD CONSTRAINT DF_suppDB1718_ldgrid DEFAULT 0 FOR ldgrid;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1819' AND COLUMN_NAME = 'ldgrid') IS NULL
    ALTER TABLE suppDB1819 ADD CONSTRAINT DF_suppDB1819_ldgrid DEFAULT 0 FOR ldgrid;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB' AND COLUMN_NAME = 'jvno') IS NULL
    ALTER TABLE suppDB ADD CONSTRAINT DF_suppDB_jvno DEFAULT 0 FOR jvno;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1718' AND COLUMN_NAME = 'jvno') IS NULL
    ALTER TABLE suppDB1718 ADD CONSTRAINT DF_suppDB1718_jvno DEFAULT 0 FOR jvno;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1819' AND COLUMN_NAME = 'jvno') IS NULL
    ALTER TABLE suppDB1819 ADD CONSTRAINT DF_suppDB1819_jvno DEFAULT 0 FOR jvno;

UPDATE suppDB     SET ldgrid = 0 WHERE ldgrid IS NULL;
UPDATE suppDB1718 SET ldgrid = 0 WHERE ldgrid IS NULL;
UPDATE suppDB1819 SET ldgrid = 0 WHERE ldgrid IS NULL;
UPDATE suppDB     SET jvno   = 0 WHERE jvno   IS NULL;
UPDATE suppDB1718 SET jvno   = 0 WHERE jvno   IS NULL;
UPDATE suppDB1819 SET jvno   = 0 WHERE jvno   IS NULL;

这篇关于C＃中的Ms sqlserver存储过程问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！