Visits management

Question

I have a system for Visitor management , it's include 2part one for visit and other for visitor a form for visit info conatains many field enterd by textbox and dropdown list , the visitor info appear in gridview becuase its possible one visit include many visitors so i have many info for them Like (Nationality, National ID, OrgName , Name).

i need stored procedure to save all this data in DB in Visit table and to link the tow part in the same table by one button.

Tuhin Paul · Answer

The stored procedure that you can use to save data to the Visit table and link it with the visitors: CREATE PROCEDURE SaveVisit ( @VisitDate DATE, @VisitStartTime TIME, @VisitEndTime TIME, @VisitPurpose NVARCHAR(100), @VisitLocation NVARCHAR(100), @VisitorData AS VisitorData READONLY ) AS BEGIN DECLARE @VisitId INT; -- Insert the visit data into the Visit table INSERT INTO Visit (VisitDate, VisitStartTime, VisitEndTime, VisitPurpose, VisitLocation) VALUES (@VisitDate, @VisitStartTime, @VisitEndTime, @VisitPurpose, @VisitLocation); -- Get the ID of the newly inserted visit SET @VisitId = SCOPE_IDENTITY(); -- Insert the visitor data into the Visitor table and link it with the visit INSERT INTO Visitor (Nationality, NationalID, OrgName, Name, VisitId) SELECT Nationality, NationalID, OrgName, Name, @VisitId FROM @VisitorData; END This stored procedure takes in the visit data as parameters (@VisitDate, @VisitStartTime, @VisitEndTime, @VisitPurpose, and @VisitLocation), as well as a table-valued parameter @VisitorData that contains the visitor data. The VisitorData table type needs to be created first, like this: CREATE TYPE VisitorData AS TABLE ( Nationality NVARCHAR(50), NationalID NVARCHAR(50), OrgName NVARCHAR(100), Name NVARCHAR(100) ) To call the stored procedure, you can use something like this in your application code: // Assuming you have a DataTable called "visitors" that contains the visitor data using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SaveVisit", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@VisitDate", visitDate); cmd.Parameters.AddWithValue("@VisitStartTime", visitStartTime); cmd.Parameters.AddWithValue("@VisitEndTime", visitEndTime); cmd.Parameters.AddWithValue("@VisitPurpose", visitPurpose); cmd.Parameters.AddWithValue("@VisitLocation", visitLocation); SqlParameter visitorDataParam = cmd.Parameters.AddWithValue("@VisitorData", visitors); visitorDataParam.SqlDbType = SqlDbType.Structured; visitorDataParam.TypeName = "VisitorData"; cmd.ExecuteNonQuery(); } } This assumes that you have a connection string stored in a variable called connectionString, and that you have variables for the visit data (visitDate, visitStartTime, visitEndTime, visitPurpose, and visitLocation) that you're passing to the stored procedure.

Name Mame · Answer

@Tuhin Paul Thanks alot for your help , its work .