本文介绍了使用存储过程从数据库加载数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我正在使用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 ( 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' 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 ' 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 @ ERR_CODE = 50350 SET @ ERR_MSG = ' 键搜索不足。无法检索婚姻信息。' END SET NOCOUNT OFF 返回 解决方案 在c#代码中,您可以执行类似 cmd.Parameters的操作。 AddWithValue( @ DOC_SEQ_NUM,seqnum ?? DBNull.Value); (因为.net中的 null 不等于SQL中的 NULL 。或者你可以一起省略所有参数,如只要... (...)在您的存储过程中,确保您将该参数设置为可为空的 @ 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 现在在 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 @ 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 = &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();}}Any help and suggestions appreciatedFollowing 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) OUTPUTASSET NOCOUNT ONSET @ERR_CODE = 0SET @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 = ' + @SFNIF LEN(@BATCH_NAME) = 12 AND LEN(@SFN) = 13BEGINSELECT 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_DTFROM DBO.CAMR_DOCUMENT_CONTROL AINNER JOIN DBO.CAMR_DOCUMENT_INDEX B--LEFT OUTER JOIN DBO.CAMR_DOCUMENT_INDEX BON A.SFN = B.SFNWHERE A.SFN = @SFNAND A.DOC_SEQ_NUM = B.DOC_SEQ_NUMAND A.DOC_SEQ_NUM = @DOC_SEQ_NUMENDELSE IF LEN(@BATCH_NAME) <> 12 AND LEN(@SFN) = 13BEGINSELECT 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_DTFROM DBO.CAMR_DOCUMENT_CONTROL A, DBO.CAMR_DOCUMENT_INDEX BWHERE A.SFN = B.SFNAND A.SFN = @SFNAND A.DOC_SEQ_NUM = B.DOC_SEQ_NUMAND A.DOC_SEQ_NUM = @DOC_SEQ_NUMENDELSE IF LEN(@BATCH_NAME) = 12 AND LEN(@SFN) <> 13BEGINSELECT 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_DTFROM DBO.CAMR_DOCUMENT_CONTROL A, DBO.CAMR_DOCUMENT_INDEX BWHERE A.BATCH_NAME = @BATCH_NAMEAND A.SFN = B.SFNAND A.DOC_SEQ_NUM = B.DOC_SEQ_NUM--AND A.DOC_SEQ_NUM = @DOC_SEQ_NUMENDELSEBEGINSET @ERR_CODE = 50350SET @ERR_MSG = 'Insufficient key search. Cannot retrieve marriage information.'ENDSET NOCOUNT OFFRETURN 解决方案 In your c# code you can do something likecmd.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) OUTPUTNow 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, ... etcTo ensure you get the most recent document sequence order each query based on the sequence ORDER BY A.DOC_SEQ_NUM DESCThe 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 parameterDECLARE @DOC_SEQ_NUM int = NULLHere is the cut-down querySELECT top 1 *FROM Table1WHERE DOC_SEQ_NUM = ISNULL(@DOC_SEQ_NUM, DOC_SEQ_NUM)ORDER BY DOC_SEQ_NUM DESCThis returns 3third versionBut a specific version can still be requestedSET @DOC_SEQ_NUM = 2returns2second version 这篇关于使用存储过程从数据库加载数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
07-23 03:04
查看更多