What we want to achieve?
We want to insert bulk question and its options in question table and Option table respectively
using excel sheet as per given excel format.
We'll be reading question and all options for that question question by question using excel data reader and implementing bulk insert.
Add library for ExcelDataReader :
We'll passing all option as a single string using separator +|+ and *|* like as given below :
Option+|+true+|+1*|*Option2+|+false+|+2 that is Option+|+Iscorrect+|+OrderBy*|* to save in option table.
In View :
@{
ViewBag.Title = "BulkUpload";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="maincontainer">
<div id="divExcel" class="divExcelUP">
@using (Html.BeginForm("BulkUpload", "Question", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="editor-label">
Select File :
</div>
<div class="editor-field">
<input type="file" id="flExcel" name="postedFile" />
<div id="errflExcel" class="divErr"></div>
</div>
<div class="editor-label">
</div>
<div class="editor-field">
<input class="Button" id="btnUpload" type="submit" value="Upload" />
</div>
}
</div>
<div class="clearfix"></div>
</div>
@section scripts{
<script>
$(document).ready(function () {
$('div[id^="err"]').html("");
var fileName = document.getElementById('flExcel').value;
$("#btnUpload").click(function () {
if ($("#flExcel").val() != "") {
}
else {
$("#errflExcel").html("Please select file for bulk upload.");
}
});
});
</script>
}
We want to insert bulk question and its options in question table and Option table respectively
using excel sheet as per given excel format.
We'll be reading question and all options for that question question by question using excel data reader and implementing bulk insert.
Add library for ExcelDataReader :
- Install the ExcelDataReader base package to use the "low level" reader interface.
- Install the ExcelDataReader.DataSet extension package to use the AsDataSet() method to populate a System.Data.DataSet.
We'll passing all option as a single string using separator +|+ and *|* like as given below :
Option+|+true+|+1*|*Option2+|+false+|+2 that is Option+|+Iscorrect+|+OrderBy*|* to save in option table.
In View :
@{
ViewBag.Title = "BulkUpload";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="maincontainer">
<div id="divExcel" class="divExcelUP">
@using (Html.BeginForm("BulkUpload", "Question", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="editor-label">
Select File :
</div>
<div class="editor-field">
<input type="file" id="flExcel" name="postedFile" />
<div id="errflExcel" class="divErr"></div>
</div>
<div class="editor-label">
</div>
<div class="editor-field">
<input class="Button" id="btnUpload" type="submit" value="Upload" />
</div>
}
</div>
<div class="clearfix"></div>
</div>
@section scripts{
<script>
$(document).ready(function () {
$('div[id^="err"]').html("");
var fileName = document.getElementById('flExcel').value;
$("#btnUpload").click(function () {
if ($("#flExcel").val() != "") {
}
else {
$("#errflExcel").html("Please select file for bulk upload.");
}
});
});
</script>
}
In Controller :
public ActionResult BulkUpload()
{
return View();
}
[HttpPost]
public ActionResult BulkUpload(HttpPostedFileBase postedFile)
{
string ext = Path.GetExtension(postedFile.FileName);
if (ext == ".xls" || ext == ".xlsx")
{
string filePath = "";
filePath = "/BulkUpload/" + "QA" + ext;
string path = Server.MapPath(filePath);
postedFile.SaveAs(path);
// Reading excel file using excel data reader.
using (var stream = System.IO.File.Open(path, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet();
DataTable dt = result.Tables[0];
int quesColumnCount = dt.Columns.Count;
int quesRowCount = dt.Rows.Count;
int quesID = Convert.ToInt32(dt.Rows[2][0]);
Question question = new Question();
QuestionDA questionDA = new QuestionDA();
string Option = string.Empty;
for (int row = 1; row <= quesRowCount - 1; row++)
{
if (quesID == Convert.ToInt32(dt.Rows[row][0]))
{
if (Convert.ToString(dt.Rows[row][1]) == "Q")
{
question.Title = Convert.ToString(dt.Rows[row][2]);
question.Description = Convert.ToString(dt.Rows[row][3]);
question.QueImageURL = Convert.ToString(dt.Rows[row][4]);
question.BaseValue = Convert.ToInt32(dt.Rows[row][5]);
question.ModuleId = Convert.ToInt32(dt.Rows[row][6]);
question.QuestionType = Convert.ToString(dt.Rows[row][7]);
}
else if (Convert.ToString(dt.Rows[row][1]) == "A")
{
Option += Convert.ToString(dt.Rows[row][2]) + "+|+" + Convert.ToString(dt.Rows[row][3]) + "+|+";
Option += Convert.ToInt32(dt.Rows[row][4]) + "*|*";
if (row == quesRowCount - 1)
{
//Call function to save last set of record before exiting loop.
Option = Option.Substring(0, Option.Length-3);
questionDA.CreateBulkQuestionOptions(question,Option);
}
}
}
else
{
//If Qid differs then call function to save data.
Option = Option.Substring(0, Option.Length - 3);
questionDA.CreateBulkQuestionOptions(question, Option);
quesID = Convert.ToInt32(dt.Rows[row][0]);
Option = "";
row--;
}
}
}
}
}
return View();
}
In Data Access :
public void CreateBulkQuestionOptions(Question question, string Option)
{
try
{
int i = ExecNonQueryProc("Usp_QAModule_CreateBulkQuestionOptions",
new SqlParameter("@Title", question.Title),
new SqlParameter("@Description", question.Description),
new SqlParameter("@QueImageURL", question.QueImageURL),
new SqlParameter("@BaseValue", question.BaseValue),
new SqlParameter("@ModuleId", question.ModuleId),
new SqlParameter("@QuestionType", question.QuestionType),
new SqlParameter("@CreatedOn", DateTime.Now.ToShortDateString()),
new SqlParameter("@Options", Option));
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
In Database we are using two procedures and one function in order achieve our goal with out using cursor :
CREATE PROC [dbo].[Usp_QAModule_CreateBulkQuestionOptions]
@Title TEXT,
@Description TEXT,
@QueImageURL VARCHAR(MAX),
@BaseValue FLOAT,
@ModuleId INT,
@QuestionType VARCHAR(30),
@CreatedOn DATETIME,
@Options VARCHAR(MAX)
AS
BEGIN
DECLARE @QId INT
DECLARE @Option VARCHAR(MAX)
INSERT jquestions
(
Title,
[Description],
QueImageURL,
BaseValue,
ModuleId,
QuestionType,
CreatedOn,
IsActive
)
VALUES
(
@Title,
@Description,
@QueImageURL,
@BaseValue,
@ModuleId,
@QuestionType,
@CreatedOn,
1
);
SET @QId= @@IDENTITY;
DECLARE @execQuery NVARCHAR(MAX);
SELECT @execQuery = COALESCE(@execQuery +';' ,'') + 'EXEC [dbo].[usp_QaModule_CreateOptions] '+CAST(@QId AS VARCHAR)+', '''+ CAST(@CreatedOn AS VARCHAR) +''', '''+@QuestionType+''', ''' + Element+''', ''+|+'''
FROM dbo.func_split(@Options, '*|*');
--PRINT(@execQuery)
EXECUTE sp_executesql @execQuery;
END
======================================================
CREATE PROCEDURE [dbo].[usp_QaModule_CreateOptions]
(
@qId INT,
@createdDate DATETIME,
@questionType VARCHAR(MAX),
@element VARCHAR(MAX),
@delimiter VARCHAR(MAX)
)
AS
BEGIN
DECLARE @result BIT = 0;
;WITH opt AS(
SELECT * FROM dbo.func_split(@element, '+|+')
)
INSERT INTO jquestionoptions
(
Id_Question,
[Option],
IsCorrect,
CreatedOn,
OptionType,
OrderBy
)
VALUES
(
@qId,
(select Element from opt where elementId = 1),
CAST((select Element from opt where elementId = 2) AS BIT),
@createdDate,
@questionType,
(select Element from opt where elementId = 3)
)
RETURN @result
END
=============================================================
CREATE FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(MAX),
@Delimiter varchar(MAX)
)
RETURNS @tblArray TABLE
(
ElementID int IDENTITY(1,1), -- Array index
Element varchar(MAX) -- Array element contents
)
AS
BEGIN
-- Local Variable Declarations
-- ---------------------------
DECLARE @Index smallint,
@Start smallint,
@DelSize smallint
SET @DelSize = LEN(@Delimiter)
-- Loop through source string and add elements to destination table array
-- ----------------------------------------------------------------------
WHILE LEN(@DelimitedString) > 0
BEGIN
SET @Index = CHARINDEX(@Delimiter, @DelimitedString)
IF @Index = 0
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(@DelimitedString)))
BREAK
END
ELSE
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))
SET @Start = @Index + @DelSize
SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)
END
END
RETURN
END
No comments:
Post a Comment