• C#中执行存储过程并在SQL server中调试


    1.编写存储过程

     1 if exists (select * from sysobjects where id = object_id(N'[sp_calcPci_of_baseRcd_GTmpTbl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
     2      drop PROCEDURE [dbo].[sp_calcPci_of_baseRcd_GTmpTbl]
     3 
     4 CREATE PROCEDURE [dbo].[sp_calcPci_of_baseRcd_GTmpTbl]
     5     --定义参数
     6     @bdindexId INT ,    
     7     @groupId INT,    
     8     @disVal INT ,    
     9     @retVal INT OUTPUT,  -- 返回值  
    10     @routeType INT  
    11                             
    12 --WITH ENCRYPTION -- 加密
    13 AS
    14     BEGIN                
    15         DECLARE @pciCalcRcdId0 INT
    16         --执行另外一个存储过程,输出参数需带上关键字‘OUTPUT’
    17         EXEC dbo.procGetCurNo 'PCA_T_D_PCICalcRcd', @pciCalcRcdId0 OUTPUT
    18         IF( @pciCalcRcdId0 <= 0 )
    19             BEGIN--sql中没有{},以BEGIN、END代替
    20                 SET @retVal = -5
    21                 RETURN -5;
    22             END
    23 
    24         SET @curSegIndex = 1;    --变量赋值     
    25         --为查询结果定义游标     
    26         DECLARE segListCursor CURSOR      
    27         FOR
    28             SELECT section_code ,pav_type ,segment_code ,begin_mile ,end_mile ,segment_length, lane_width
    29             FROM    dbo.ZY_T_D_Segment
    30             WHERE   route_code = @routeCode AND up_down = @direction AND lane = @lane
    31                     AND begin_mile < @endMile AND end_mile > @beginMile AND valid_flag=1
    32             ORDER BY begin_mile;
    33         --打开游标
    34         OPEN segListCursor;
    35         --按行取值
    36         FETCH NEXT FROM segListCursor INTO @sectionCode, @pavType,
    37             @segmentCode, @segBeginMile, @segEndMile, @segLength, @laneWidth;
    38         WHILE @@FETCH_STATUS = 0
    39             BEGIN    -- 
    40                 ---处理---              
    41                 FETCH NEXT FROM segListCursor INTO @sectionCode,
    42                     @pavType, @segmentCode, @segBeginMile, @segEndMile,@segLength, @laneWidth;
    43             END;
    44         CLOSE segListCursor;--关闭游标
    45         DEALLOCATE segListCursor;--释放游标 deallocate
    46 
    47         -- 将临时表中的数据插入到实际表a_test中        
    48         SET @insertSql = 'INSERT INTO ' + @pavDataLocTable + ' SELECT * FROM ' + @tempPavDataLocTable
    49         EXEC sys.sp_executesql @insertSql
    50         SET @insertSql = 'INSERT INTO ' + @pciCalcTable + ' SELECT * FROM ' + @tempPciCalcTable        
    51         EXEC sys.sp_executesql @insertSql
    52         
    53         DROP table [dbo].[##pavDataLoc_temp_table]
    54         DROP table [dbo].[##pciCalc_temp_table]        
    55     END;
    56 GO

    2.C#中调用存储过程

     1 SqlParameter dmgPara1 = new SqlParameter("@bdindexId", SqlDbType.Int, 4);
     2             dmgPara1.Value = pciCalcRcd.BdindexId;
     3             SqlParameter dmgPara2 = new SqlParameter("@groupId", SqlDbType.Int, 4);
     4             dmgPara2.Value = pciCalcRcd.GroupId;
     5             SqlParameter dmgPara3 = new SqlParameter("@disVal", SqlDbType.Int, 4);
     6             dmgPara3.Value = pciCalcRcd.DisVal;
     7             SqlParameter dmgPara4 = new SqlParameter("@retVal", SqlDbType.Int, 4);
     8             dmgPara4.Value = 0;
     9             dmgPara4.Direction = ParameterDirection.Output;//输出参数
    10             SqlParameter dmgPara5 = new SqlParameter("@routeType", SqlDbType.Int, 4);
    11             dmgPara5.Value = routeTypeVal;//道路等级,高速与一级公路为0,二三四级为1
    12             SqlParameter[] sqlDmgParam = new SqlParameter[5];
    13             sqlDmgParam[0] = dmgPara1;
    14             sqlDmgParam[1] = dmgPara2;
    15             sqlDmgParam[2] = dmgPara3;
    16             sqlDmgParam[3] = dmgPara4;
    17             sqlDmgParam[4] = dmgPara5;
    18             // 根据病害,入库相应的路面破损数据
    19             try
    20             {
    21                 nRet = MsSqlTool.executeProcedureNoDataSet(conn, "sp_calcPci_of_baseRcd_GTmpTbl", sqlDmgParam);
    22             }
    23             catch (Exception ex)
    24             {
    25             }
    26             if (int.Parse(sqlDmgParam[3].Value.ToString()) <= 0)
    27             {
    28                 return int.Parse(sqlDmgParam[3].Value.ToString());//获取输出参数返回值
    29             }
     1 public static int executeProcedureNoDataSet(SqlConnection dbConn, string strProcName, SqlParameter[] procParams)
     2         {
     3             SqlConnection conn = dbConn;
     4             DataSet ds = new DataSet();
     5             try
     6             {
     7                 if (conn.State != ConnectionState.Open)
     8                     conn.Open();
     9 
    10                 // 确认打开连接  Open();  
    11                 SqlCommand cmd = new SqlCommand(strProcName, dbConn);  
    12                 cmd.CommandType = CommandType.StoredProcedure;
    13                 cmd.CommandTimeout = 3600;
    14                 // 依次把参数传入存储过程 
    15                 if (procParams != null)  
    16                 {  
    17                     foreach (SqlParameter parameter in procParams)  
    18                         cmd.Parameters.Add(parameter);  
    19                 }   
    20                
    21                 cmd.ExecuteNonQuery();
    22 
    23                 return 1;
    24             }
    25             catch (System.Exception ex)
    26             {
    27                 throw ex;
    28             }
    29             finally
    30             {
    31                 conn.Close();
    32             }
    33             return 1;
    34         }

    3.SQL 调试存储过程

  • 相关阅读:
    第四次作业
    第二次上机作业
    5.21上机作业
    第十一周课后作业
    第十一周上机练习
    第10周java作业
    第9次上级联系
    第8周作业
    第八周上机练习
    第七周作业
  • 原文地址:https://www.cnblogs.com/lyd2016/p/6610260.html
Copyright © 2020-2023  润新知