Pass (Data Table) to SQL Sever From ASP.NEt using Enterprise Library

Question

User-1780421697 posted

Using enterprise library to pass xml type parmeter is quite good experience but sometime it is hard to catch errors,may be while casting types in sql server.

It is good idea to create data type (table) in sql server and pass data table to stored procedure.

 

Regards

Khuram Shehzad 

Answer 1

User-1780421697 posted

I have two classes in my .net library

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataAccessLayerVer2
{
    public class dbTableType
    {
        DataTable dt;
        string  pname;

        public DataTable dataTable
        {
            get { return dt; }
            set { dt = value; }
        }

        public string ParameterName
        {
            get { return pname; }
            set{pname = value;}
        }

    }
}

is ist class responsible to take datatable and parameter name like "@paramName"

 

My Add Method

       public structResult Add(String strLoginEnvironment, String strSPName, dbTableType odbTableType)
        {
            oclsDbTransacted = new clsDbTransacted();
            return oclsDbTransacted.Add(strLoginEnvironment, strSPName, odbTableType);
        }

             dbTableType odbTableType = new dbTableType();
            odbTableType.dataTable = dt;
            odbTableType.ParameterName = "@myTable";
            //@myTable
           ostructResult = oclsWrapperClass.Add("Live", "Usp_AddRandomQuestion",  odbTableType);
            if (ostructResult.intCode == 1)
            {
                saveStatus = true;
            }
            else
            {
                saveStatus = false;
                        
            }

 DATABASE Store Procedure:

-- =============================================      
-- Author:  <Author,,Khuram>      
-- Create date: <Create Date,,>      
-- Description: <Description,,>      
-- =============================================      
CREATE PROCEDURE [Usp_AddRandomQuestion]      
   (    
     @myTable UserRandomQuestion ReadOnly      
    )    
AS      
BEGIN      
      
 SET NOCOUNT ON;      
      
   Declare @testid as int     
   Declare @username as varchar(50)    
   Declare @countrow int =0    
   Declare @UserTestID int =0    
       
   Select @testid = testid from @myTable    
   Select @username = username from @myTable    
   Select @UserTestID = UserTestId from @myTable    
   --Select isInitialized from AssignTesttoUser Where TestId=@testid and UserName=@username    
   Select @countrow = COUNT(*) from UserTestQuestion Where TestId=@testid and UserName=@username and UserTestID = @UserTestID  
        
   if(@countrow = 0)    
   begin      
      Insert Into UserTestQuestion (TestId,QuestionId,UserName,UserTestID,StatusId)      
      Select TestId ,QuestionId ,UserName,UserTestID,1 From  @myTable      
   end    
END

/****** Object:  UserDefinedTableType [dbo].[UserRandomQuestion]    Script Date: 04/09/2013 10:03:00 ******/
CREATE TYPE [dbo].[UserRandomQuestion] AS TABLE(
	[TestId] [int] NULL,
	[QuestionId] [int] NULL,
	[UserName] [varchar](50) NULL,
	[UserTestID] [int] NULL
)
GO

 

 

Answer 2

User-1280676833 posted