Load data from the database with stored procedure

See more:

C#

SQL

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