使用存储过程从数据库加载数据 [英] Load data from the database with stored procedure

问题描述

我正在使用C＃窗口表单应用程序将数据保存到数据库中，我需要在用户点击搜索按钮时将相同的数据库加载到表单上。



我有分配doc_seq_num的问题，它扮演关键角色，用最新数据填充字段。



假设我保存原始数据doc_seq_num保存为1，然后数据被更改，doc_seq_num增量为1.



现在我的存储过程是填充字段，我试图找出传递doc_seq_num参数的方法，可以是1 ，2或3，很快取决于修改的数量。



例如：如果我有一些数字1234567890的记录在数据库中存在4次，一旦它是保存为原始文件，将doc_seq_num保存为1.后来的记录修改了3次，现在新的doc_seq_num将是4.



现在当我按下按钮点击存储过程时，它应该从da加载数据tabase表。加载的记录将是doc_seq_num = 4的记录，而不是其他3.



使用下面的代码，我可以使用doc_seq_num 1或任何值将数据加载到字段中通过，我正在寻找帮助处理任何doc_seq_num的东西。

  private   void  btnsearch_Click（ object  sender，EventArgs e）
 {
 SqlConnection con =  new  SqlConnection（数据源= serverName;初始目录= camrdbd; Trusted_Connection =  true ） ; 
 SqlCommand cmd =  new  SqlCommand（）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.CommandText =& quot; usp_CAMR_GetInfoByBatchSFN; 
 con.Open（）; 
 cmd.Connection = con; 
 cmd.Parameters.AddWithValue（  @ sfn，txtsfn.Text）; 
 cmd.Parameters.AddWithValue（  @ BATCH_NAME，txtovrbn.Text）; 
 cmd.Parameters.AddWithValue（  @ DOC_SEQ_NUM， 1 ）; 
 cmd.Parameters.AddWithValue（  @ ERR_CODE， 0 ）; 
 cmd.Parameters.AddWithValue（  @ ERR_MS， 0 ）; 
 cmd.Parameters.AddWithValue（  @ TABLE_NAME， 0 ）; 
 SqlDataReader dr = cmd.ExecuteReader（）; 
  while （dr.Read（））
 {
 txtlrn.Text = dr [  lrn]。ToString（）; 
 txtovrbn.Text = dr [  batch_name]。ToString（）; 
 txtovrts.Text = dr [  lst_mod_dt;]。ToString（）; 
 txtfpfn.Text = dr [  first_person_fname]。ToString（）; 
 txtfpln.Text = dr [  first_person_lnam]。ToString（）; 
 txtspfn.Text = dr [ ; second_person_fname]。ToString（）; 
 txtspln.Text = dr [  second_person_lname]。ToString（）; 
} 
} 
 
任何帮助和建议赞赏

以下是存储过程..

已存储程序 
  @ BATCH_NAME   VARCHAR （ 12 ），
  @ SFN   VARCHAR （ 13 ），
  @ DOC_SEQ_NUM   INT ，
  @ ERR_CODE   INT  输出，
  @ ERR_MSG   VARCHAR （ 256 ） OUTPUT ，
  @ TABLE_NAME   VARCHAR （ 50 ）输出 
  AS  
 
  SET   NOCOUNT   ON  
 
  SET   @ ERR_CODE  =  0  
  SET   @ ERR_MSG  = '  ' 
  SET   @ TABLE_NAME  = ' ' 
  SET   @ BATCH_NAME  = UPPER（LTRIM（RTRIM（ @ BATCH_NAME ）））
  SET   @ SFN  = LTRIM（RTRIM（ @ SFN ））
  PRINT  '  BATCH_NAME =' +  @ BATCH_NAME  + '  AND SFN =' +  @ SFN  
  IF  LEN（ @ BATCH_NAME ）=  12   AND  LEN（ @ SFN ）=  13  
  BEGIN  
  SELECT   DISTINCT  A.SFN，A.DOC_SEQ_NUM，B.LRN，A.BATCH_NAME，B.FIRST_PERSON_LNAME，B.FIRST_PERSON_FNAME，B.FIRST_PERSON_MNAME，
  CONVERT （ CHAR ，B.FIRST_PERSON_DOB， 101 ） AS  FIRST_PERSON_DOB，B.SECON D_PERSON_LNAME，B.SECOND_PERSON_FNAME，B.SECOND_PERSON_MNAME，
  CONVERT （ CHAR ，B.SECOND_PERSON_DOB，  101 ） AS  SECOND_PERSON_DOB， CONVERT （< span class =code-keyword> CHAR ，B.DOM， 101 ） AS  DOM， CONVERT （ CHAR ，B.DOL， 101 ） AS  DOL，
 B.AMENDMENT_IND，B.MARRIAGE_BC，B.EVENT_TYPE，
   -    B.PLACE_OF_EVENT， 
（ CASE  B.PLACE_OF_EVENT  WHEN  '  061' 那么 '  01'  WHEN  '  61' 那么 '  01'  WHEN  '  062' < span class =code-keyword> THEN  '  19'  WHEN  '  62' 那么 '  19'  WHEN  '  063' 那么 '  19'  WHEN  '  63' 那么 '  19 '  ELSE  B.PLACE_OF_EVENT  END ） AS  PLACE_OF_EVENT，
 B.CERT_IMAGE_ID， CONVERT （ CHAR ，B.REG_DT，  101 ） AS  REG_DT，
 A.ACTION_TYPE，A.PROCESS_STATUS_FLG，A.IMAGE_LOCATION，B .LST_MOD_USERID， CONVERT （ CHAR ，B.LST_MOD_DT， 101 ） AS  LST_MOD_DT 
  FROM  DBO.CAMR_DOCUMENT_CONTROL A 
  INNER   JOIN  DBO.CAMR_DOCUMENT_INDEX B 
   -    LEFT OUTER JOIN DBO.CAMR _DOCUMENT_INDEX B  
  ON  A.SFN = B.SFN 
  WHERE  A.SFN =  @ SFN  
  AND  A.DOC_SEQ_NUM = B.DOC_SEQ_NUM 
  AND  A.DOC_SEQ_NUM =  @ DOC_SEQ_NUM  
  END  
  ELSE   IF  LEN（ @BATCH_NAME ）<>  12   AND  LEN（ @ SFN ）=  13  
  BEGIN  
  SELECT  A.SFN，A.DOC_SEQ_NUM，B.LRN，A.BATCH_NAME，B.FIRST_PERSON_LNAME，B.FIRST_PERSON_FNAME，B.FIRST_PERSON_MNAME，
  CONVERT  （ CHAR ，B.FIRST_PERSON_DOB， 101 ） AS  FIRST_PERSON_DOB，B.SECOND_PERSON_LNAME，B.SECOND_PERSON_FNAME，B.SECOND_PERSON_MNAME，
  CONVERT （ CHAR ，B.SECOND_PERSON_DOB， 101 ） AS  SECOND_PERSON_DOB， CONVERT （ CHAR ，B.DOM， 101 ） AS  DOM， CONVERT （ CHAR ，B.DOL， 101 ）  AS  DOL，
 B.AMENDMENT_IND，B.MARRIAGE_BC，B.EVENT_TYPE，
   -    B.PLACE_OF_EVENT， 
（ CASE  B.PLACE_OF_EVENT  WHEN  '  061' 那么 '  01'  WHEN  '  61' 那么 '  01'  WHEN  '  062' 那么 '   19'  WHEN  '  62' 那么 '  19 '  WHEN  '  063'  THEN  '  19'  WHEN  '  63' 那么 '  19'  ELSE  B.PLACE_OF_EVENT  END ） AS  PLACE_OF_EVENT，
 B.CERT_IMAGE_ID， CONVERT （ CHAR ，B.REG_DT， 101 ）  AS  REG_DT，
 A.ACTION_TYPE，A.PROCESS_STATUS_FLG，A.IMAGE_LOCATION，B.LST_MOD_USERID， CONVERT （ CHAR ，B.LST_MOD_DT， 101 ） AS  LST_MOD_DT 
  FROM  DBO.CAMR_DOCUMENT_CONTROL A，DBO.CAMR_DOCUMENT_INDEX B 
  WHERE  A.SFN = B.SFN 
  AND  A.SFN =  @ SFN  
  AND  A.DOC_SEQ_NUM = B.DOC_SEQ_NUM 
  AND  A.DOC_SEQ_NUM =  @DOC_SEQ_NUM  
  END  
  ELSE   IF  LEN（ @ BATCH_NAME ）=  12   AND  LEN（ @ SFN ）<>  13  
  BEGIN  
  SELECT  A.SFN，A.DOC_SEQ_NUM，B.LRN，A.BATCH_NAME，B.FIRST_PERSON_LNAME，B.FIRST_PERSON_FNAME，B.FIRST_PERSON_MNAME，
  CONVERT  （ CHAR ，B.FIRST_PERSON_DOB， 101 ） AS  FIRST_PERSON_DOB，B.SECOND_PERSON_LNAME，B.SECOND_PERSON_FNAME，B.SECOND_PERSON_MNAME，
  CONVERT （ CHAR ，B.SECOND_PERSON_DOB， 101 ） AS  SECOND_PERSON_DOB， CONVERT （ CHAR ，B.DOM， 101 ） AS  DOM， CONVERT （ CHAR ，B.DOL， 101 ） AS  DOL，
 B.AMENDMENT_IND，B.MARRIAGE_BC，B.EVENT_TYPE，
   -    B.PLACE_OF_EVENT， 
（ CASE  B.PLACE_OF_EVENT  WHEN  '  061' 那么 '  01'  WHEN  < span class =code-string>'  61' 那么 '  01'  WHEN  '  062' 那么 '  19'  WHEN  '  62' 那么 '  19 '  WHEN  '  063'  THEN  '  19'  WHEN  '  63' 那么 '  19'  ELSE  B.PLACE_OF_EVENT  END ） AS  PLACE_OF_EVENT，
 B.CERT_IMAGE_ID， CONVERT （ CHAR ，B.REG_DT， 101 ）  AS  REG_DT，
 A.ACTION_TYPE，A.PROCESS_STATUS_FLG，A.IMAGE_LOCATION，B.LST_MOD_USERID， CONVERT （ CHAR ，B.LST_MOD_DT， 101 ） AS  LST_MOD_DT 
  FROM  DBO.CAMR_DOCUMENT_CONTROL A，DBO.CAMR_DOCUMENT_INDEX B 
  WHERE  A.BATCH_NAME =  @ BATCH_NAME  
  AND  A .SFN = B.SFN 
  AND  A.DOC_SEQ_NUM = B.DOC_SEQ_NUM 
   -    AND A.DOC_SEQ_NUM = @DOC_SEQ_NUM  
  END  
  ELSE  
  BEGIN  
  SET  < span class =code-sdkkeyword> @ ERR_CODE  =  50350  
  SET   @ ERR_MSG  = ' < span class =code-string>键搜索不足。无法检索婚姻信息。' 
  END  
 
  SET   NOCOUNT   OFF  
 
 返回 

解决方案

在c＃代码中，您可以执行类似

 cmd.Parameters的操作。 AddWithValue（  @ DOC_SEQ_NUM，seqnum ?? DBNull.Value）; 

（因为.net中的 null 不等于SQL中的 NULL 。或者你可以一起省略所有参数，如只要...



（...）在您的存储过程中，确保您将该参数设置为可为空的

  @ BATCH_NAME   VARCHAR （ 12 ），
 < span class =code-sdkkeyword> @ SFN   VARCHAR （ 13 ），
   @ DOC_SEQ_NUM   INT  =  NULL ， 
  @ ERR_CODE   INT   OUTPUT ，
  @ ERR_MSG   VARCHAR （ 256 ） OUTPUT ，
  @ TABLE_NAME   VARCHAR （ 50 ） OUTPUT  

现在在 WHERE 中，您可以使用 ISNULL 函数，以确保您处理参数为null（或未传入）的情况

 AND A.DOC_SEQ_NUM = ISNULL（ @DOC_SEQ_NUM，A.DOC_SEQ_NUM）

但是，如果参数为null，则此更改将导致超过o返回的行，因此您希望在返回的列列表之前加上

  SELECT    TOP   1   A.SFN，A.DOC_SEQ_NUM，B.LRN，......等

为确保您获得最新的文档序列顺序，每个查询基于序列

 ORDER BY A.DOC_SEQ_NUM DESC 

以下简单示例演示了我的意思

 创建 表表1 
（
 DOC_SEQ_NUM  int ，
 someData  varchar （ 20 ）
）
  insert   into 表1 值 
（ 1 ，' 第一个版本'），
（ 2 ，' 第二个版本'），
（ 3 ，' 第三版）

此变量将模仿输入参数

  DECLARE   @ DOC_SEQ_NUM   int  =  NULL  

这是减少的查询

  SELECT   top   1  * 
  FROM 表1 
  WHERE  DOC_SEQ_NUM = ISNULL（ @ DOC_SEQ_NUM ，DOC_SEQ_NUM ）
  ORDER   BY  DOC_SEQ_NUM  DESC  

返回

  3 第三版

但是仍然可以请求特定版本

  SET  < span class =code-sdkkeyword> @ DOC_SEQ_NUM  =  2  

返回

 2秒版本

I am working with C# window forms application which saves data into database and I need to load same database onto form while user clicks on search button.

I have problem with assigning doc_seq_num which plays the key role to populate the fields with the latest data.

Suppose I saved original data doc_seq_num saved as 1 and then data gets changed and doc_seq_num increments to 1.

Now the stored procedure I have is meant to populate the fields, I am trying to figure out the way to pass parameter for doc_seq_num which can be 1, 2 or 3 and soon depending on number of amendments made.

For ex: If I have some record with number 1234567890 that exists in database 4 times, once it was saved as original which gave it doc_seq_num as 1. Later record was amended 3 time now new doc_seq_num will be 4.

Now when I call stored procedure on button click, it should load the data from the database table. The record loaded would be the one with doc_seq_num= 4, not other 3.

With Below code I am able to load data into the fields with doc_seq_num 1 or whichever value passed, I am looking for help with something that will take care of any doc_seq_num.

private void btnsearch_Click(object sender, EventArgs e)
{
	SqlConnection con = new SqlConnection(Data Source=serverName;Initial Catalog=camrdbd;Trusted_Connection=true);
	SqlCommand cmd = new SqlCommand();
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.CommandText = "usp_CAMR_GetInfoByBatchSFN;
	con.Open();
	cmd.Connection = con;
	cmd.Parameters.AddWithValue("@sfn", txtsfn.Text);
	cmd.Parameters.AddWithValue("@BATCH_NAME", txtovrbn.Text);
	cmd.Parameters.AddWithValue("@DOC_SEQ_NUM", 1);
	cmd.Parameters.AddWithValue("@ERR_CODE", 0);
	cmd.Parameters.AddWithValue("@ERR_MS", 0);
	cmd.Parameters.AddWithValue("@TABLE_NAME", 0);
	SqlDataReader dr = cmd.ExecuteReader();
	while (dr.Read())
	{
	   txtlrn.Text = dr["lrn"].ToString();
	   txtovrbn.Text = dr["batch_name"].ToString();
	   txtovrts.Text = dr["lst_mod_dt";].ToString();
	   txtfpfn.Text = dr["first_person_fname"].ToString();
	   txtfpln.Text = dr["first_person_lnam"].ToString();
	   txtspfn.Text = dr[";second_person_fname"].ToString();
	   txtspln.Text = dr["second_person_lname"].ToString();               
	}
}

Any help and suggestions appreciated

Following is the stored procedure..

STORED PROCEDURE
@BATCH_NAME VARCHAR(12),
@SFN VARCHAR(13),
@DOC_SEQ_NUM INT,
@ERR_CODE INT OUTPUT,
@ERR_MSG VARCHAR(256) OUTPUT,
@TABLE_NAME VARCHAR(50) OUTPUT
AS

SET NOCOUNT ON

SET @ERR_CODE = 0
SET @ERR_MSG = ''
SET @TABLE_NAME = ''
SET @BATCH_NAME = UPPER(LTRIM(RTRIM(@BATCH_NAME)))
SET @SFN = LTRIM(RTRIM(@SFN))
PRINT 'BATCH_NAME = ' + @BATCH_NAME + ' AND SFN = ' + @SFN
IF LEN(@BATCH_NAME) = 12 AND LEN(@SFN) = 13
BEGIN
SELECT DISTINCT A.SFN, A.DOC_SEQ_NUM, B.LRN, A.BATCH_NAME, B.FIRST_PERSON_LNAME, B.FIRST_PERSON_FNAME, B.FIRST_PERSON_MNAME,
CONVERT(CHAR, B.FIRST_PERSON_DOB,101) AS FIRST_PERSON_DOB, B.SECOND_PERSON_LNAME, B.SECOND_PERSON_FNAME, B.SECOND_PERSON_MNAME,
CONVERT(CHAR, B.SECOND_PERSON_DOB,101) AS SECOND_PERSON_DOB, CONVERT(CHAR, B.DOM,101) AS DOM, CONVERT(CHAR, B.DOL,101) AS DOL,
B.AMENDMENT_IND, B.MARRIAGE_BC, B.EVENT_TYPE,
--B.PLACE_OF_EVENT,
(CASE B.PLACE_OF_EVENT WHEN '061' THEN '01' WHEN '61' THEN '01' WHEN '062' THEN '19' WHEN '62' THEN '19' WHEN '063' THEN '19' WHEN '63' THEN '19' ELSE B.PLACE_OF_EVENT END) AS PLACE_OF_EVENT,
B.CERT_IMAGE_ID, CONVERT(CHAR, B.REG_DT,101) AS REG_DT,
A.ACTION_TYPE, A.PROCESS_STATUS_FLG, A.IMAGE_LOCATION, B.LST_MOD_USERID, CONVERT(CHAR, B.LST_MOD_DT,101) AS LST_MOD_DT
FROM DBO.CAMR_DOCUMENT_CONTROL A
INNER JOIN DBO.CAMR_DOCUMENT_INDEX B
--LEFT OUTER JOIN DBO.CAMR_DOCUMENT_INDEX B
ON A.SFN = B.SFN
WHERE A.SFN = @SFN
AND A.DOC_SEQ_NUM = B.DOC_SEQ_NUM
AND A.DOC_SEQ_NUM = @DOC_SEQ_NUM
END
ELSE IF LEN(@BATCH_NAME) <> 12 AND LEN(@SFN) = 13
BEGIN
SELECT A.SFN, A.DOC_SEQ_NUM, B.LRN, A.BATCH_NAME, B.FIRST_PERSON_LNAME, B.FIRST_PERSON_FNAME, B.FIRST_PERSON_MNAME,
CONVERT(CHAR, B.FIRST_PERSON_DOB,101) AS FIRST_PERSON_DOB, B.SECOND_PERSON_LNAME, B.SECOND_PERSON_FNAME, B.SECOND_PERSON_MNAME,
CONVERT(CHAR, B.SECOND_PERSON_DOB,101) AS SECOND_PERSON_DOB, CONVERT(CHAR, B.DOM,101) AS DOM, CONVERT(CHAR, B.DOL,101) AS DOL,
B.AMENDMENT_IND, B.MARRIAGE_BC, B.EVENT_TYPE,
--B.PLACE_OF_EVENT,
(CASE B.PLACE_OF_EVENT WHEN '061' THEN '01' WHEN '61' THEN '01' WHEN '062' THEN '19' WHEN '62' THEN '19' WHEN '063' THEN '19' WHEN '63' THEN '19' ELSE B.PLACE_OF_EVENT END) AS PLACE_OF_EVENT,
B.CERT_IMAGE_ID, CONVERT(CHAR, B.REG_DT,101) AS REG_DT,
A.ACTION_TYPE, A.PROCESS_STATUS_FLG, A.IMAGE_LOCATION, B.LST_MOD_USERID, CONVERT(CHAR, B.LST_MOD_DT,101) AS LST_MOD_DT
FROM DBO.CAMR_DOCUMENT_CONTROL A, DBO.CAMR_DOCUMENT_INDEX B
WHERE A.SFN = B.SFN
AND A.SFN = @SFN
AND A.DOC_SEQ_NUM = B.DOC_SEQ_NUM
AND A.DOC_SEQ_NUM = @DOC_SEQ_NUM
END
ELSE IF LEN(@BATCH_NAME) = 12 AND LEN(@SFN) <> 13
BEGIN
SELECT A.SFN, A.DOC_SEQ_NUM, B.LRN, A.BATCH_NAME, B.FIRST_PERSON_LNAME, B.FIRST_PERSON_FNAME, B.FIRST_PERSON_MNAME,
CONVERT(CHAR, B.FIRST_PERSON_DOB,101) AS FIRST_PERSON_DOB, B.SECOND_PERSON_LNAME, B.SECOND_PERSON_FNAME, B.SECOND_PERSON_MNAME,
CONVERT(CHAR, B.SECOND_PERSON_DOB,101) AS SECOND_PERSON_DOB, CONVERT(CHAR, B.DOM,101) AS DOM, CONVERT(CHAR, B.DOL,101) AS DOL,
B.AMENDMENT_IND, B.MARRIAGE_BC, B.EVENT_TYPE,
--B.PLACE_OF_EVENT,
(CASE B.PLACE_OF_EVENT WHEN '061' THEN '01' WHEN '61' THEN '01' WHEN '062' THEN '19' WHEN '62' THEN '19' WHEN '063' THEN '19' WHEN '63' THEN '19' ELSE B.PLACE_OF_EVENT END) AS PLACE_OF_EVENT,
B.CERT_IMAGE_ID, CONVERT(CHAR, B.REG_DT,101) AS REG_DT,
A.ACTION_TYPE, A.PROCESS_STATUS_FLG, A.IMAGE_LOCATION, B.LST_MOD_USERID, CONVERT(CHAR, B.LST_MOD_DT,101) AS LST_MOD_DT
FROM DBO.CAMR_DOCUMENT_CONTROL A, DBO.CAMR_DOCUMENT_INDEX B
WHERE A.BATCH_NAME = @BATCH_NAME
AND A.SFN = B.SFN
AND A.DOC_SEQ_NUM = B.DOC_SEQ_NUM
--AND A.DOC_SEQ_NUM = @DOC_SEQ_NUM
END
ELSE
BEGIN
SET @ERR_CODE = 50350
SET @ERR_MSG = 'Insufficient key search. Cannot retrieve marriage information.'
END

SET NOCOUNT OFF

RETURN

解决方案

In your c# code you can do something like

cmd.Parameters.AddWithValue("@DOC_SEQ_NUM", seqnum ?? DBNull.Value);

(Because null in .net does not equate to NULL in SQL. Or you can omit the parameter all together, as long as...

(...) In your Stored Procedure make sure you have that parameter as nullable

@BATCH_NAME VARCHAR(12),
 @SFN VARCHAR(13),
 @DOC_SEQ_NUM INT = NULL,
 @ERR_CODE INT OUTPUT,
 @ERR_MSG VARCHAR(256) OUTPUT,
 @TABLE_NAME VARCHAR(50) OUTPUT

Now in your WHERE you can utilise the ISNULL function to ensure you handle the case where the parameter is null (or not passed in)

AND A.DOC_SEQ_NUM = ISNULL(@DOC_SEQ_NUM, A.DOC_SEQ_NUM)

However, if the parameter is null then this change will result in more than one row being returned, so you want to precede the list of columns returned with

SELECT TOP 1 A.SFN, A.DOC_SEQ_NUM, B.LRN, ... etc

To ensure you get the most recent document sequence order each query based on the sequence

ORDER BY A.DOC_SEQ_NUM DESC

The following trivial example demonstrates what I mean

 Create Table Table1
 (
	DOC_SEQ_NUM int,
	someData varchar(20)
)
insert into Table1 values
(1,'first version'),
(2,'second version'),
(3,'third version')

This variable will mimic the input parameter

DECLARE @DOC_SEQ_NUM int = NULL

Here is the cut-down query

SELECT top 1 *
FROM Table1
WHERE DOC_SEQ_NUM = ISNULL(@DOC_SEQ_NUM, DOC_SEQ_NUM)
ORDER BY DOC_SEQ_NUM DESC

This returns

3	third version

But a specific version can still be requested

SET @DOC_SEQ_NUM = 2

returns

2	second version

这篇关于使用存储过程从数据库加载数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！