Introduction
Recently, when I was working on a project, I had a Page in ASP.NET with master and details Data. Master data was present as textbox fields and Details Data was in a pure HTML Table.
What I wished then to pass my data (both Master and Details) to Web Method without much effort.
Here, I will be showing to save Details Data by converting it to a jagged array (array of an array) and pass it to Web Method and then Passing Details Data to database with the help of Structured Parameter without using StringBuilder
or XML to send data to SQL Server in bulk.
Personally, I don't like stringbuilder
or XML approach.
There may be several approaches. It's just one of them that I learnt.
What You Must Know?
Knowledge of C#, ASP.NET, Web Method (there may be Web Services, WCF, Web API, etc.), Jquery Ajax, basic SQL knowledge (Types, Stored Procedures) and Visual Studio 2012.
How Things Work?
- We will first convert the HTML table to jagged array.
- Pass this jagged array to
WebMethod
by Jquery Ajax request. - Fill a
DataTable
with jagged array data. - Pass this filled
Datatable
to stored procedure with Structure Parameter. - In USP Table-Valued Type will fetch the data passed with Structure Parameter.
- Finally, insert data into database table from Table-Valued Type.
Let's go step by step.
Assume that you have an HTML table in your ASP.NET page. The HTML Table may have dropdowns, hidden fields, textarea, checkboxes, etc. Here, I am going through a very basic example of an HTML table.
<table id="tbldata" style="width: 100%;" border="1">
<tr>
<th>Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Address</th>
<th>comments</th>
<th>Course</th>
<th>Is Eligible ?</th>
<th>Hidden fields</th>
</tr>
<tr>
<td>Amit</td>
<td>Singh</td>
<td>23</td>
<td>
<input type="text" value="Amit's Address" /></td>
<td>
<textarea rows="4" cols="50">your comments </textarea>
</td>
<td>
<select>
<option value="0">MBA</option>
<option value="1">B.E.</option>
</select>
</td>
<td>
<input type="checkbox" />
</td>
<td>
<input type="hidden" value="1" />
</td>
</tr>
<tr>
<td>Sakshi</td>
<td>Singh</td>
<td>20</td>
<td>
<input type="text" value="Sakshi's Address" /></td>
<td>
<textarea rows="4" cols="50">your comments</textarea>
</td>
<td>
<select>
<option value="0">MBA</option>
<option value="1">B.E.</option>
</select>
</td>
<td>
<input type="checkbox" />
</td>
<td>
<input type="hidden" value="2" />
</td>
</tr>
<tr>
<td>Ram</td>
<td>Singh</td>
<td>25</td>
<td>
<input type="text" value="Ram's Address" /></td>
<td>
<textarea rows="4" cols="50">your comments </textarea>
</td>
<td>
<select>
<option value="0">MBA</option>
<option value="1">B.E.</option>
</select>
</td>
<td>
<input type="checkbox" />
</td>
<td>
<input type="hidden" value="3" />
</td>
</tr>
<tr>
<td>Rahul</td>
<td>Sharma</td>
<td>23</td>
<td>
<input type="text" value="Rahul's Address" /></td>
<td>
<textarea rows="4" cols="50">your comments </textarea>
</td>
<td>
<select>
<option value="0">MBA</option>
<option value="1">B.E.</option>
</select>
</td>
<td>
<input type="checkbox" />
</td>
<td>
<input type="hidden" value="4" />
</td>
</tr>
</table>
So, we have got a table having some fields, inputbox, checkbox, hidden fields, dropdown and textarea too.
I have written a code that converts a table into a jagged array. Just reference Jquery in your page before using this code.
var HTMLtbl =
{
getData: function (table) {
var data = [];
table.find('tr').not(':first').each(function (rowIndex, r) {
var cols = [];
$(this).find('td').each(function (colIndex, c) {
if ($(this).children(':text,:hidden,textarea,select').length > 0)
cols.push($(this).children('input,textarea,select').val().trim());
else if ($(this).children(':checkbox').length > 0)
cols.push($(this).children(':checkbox').is(':checked') ? 1 : 0);
else
cols.push($(this).text().trim());
});
data.push(cols);
});
return data;
}
}
This function converts Table
to a jagged array.
We call this function on a button's click event like this:
$(document).on('click', '#btnSave', function () {
var data = HTMLtbl.getData($('#tbldata'));
var parameters = {};
parameters.array = data;
var request = $.ajax({
async: true,
cache: false,
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
url: "index.aspx/SaveData",
data: JSON.stringify(parameters)
});
request.done(function (msg) {
alert("Row saved " + msg.d);
});
request.fail(function (jqXHR, textStatus) {
alert("Request failed: " + textStatus);
});
});
It executes an Ajax request. We will handle this request in WebMethod
.
Though we are passing a Jagged array to WebMethod
, parameter of the WebMethod
must also be a jagged array.
[WebMethod]
public static string SaveData(string[][] array)
This is how we will be handling Ajax request and saving data to database.
We create a DataTable
with the exact columns as we will create in Table-Value Type in the database.
Fill this DataTable
with the data in jagged array with the help of Foreach
.
Open the connection for our database.
Choose commandType
to be StoredProcedure
.
Pass the name of the SP, i.e., USPSaveDetails
in my case.
Now, the most important one we pass the DataTable
to a structured parameter of sqlCommand
which is taken care by a Table-Valued Type in USP.
cmd.Parameters.Add("@TableType", SqlDbType.Structured).SqlValue = dt;
Here is the full code:
[WebMethod]
public static string SaveData(string[][] array)
{
string result = string.Empty;
try
{
DataTable dt=new DataTable();
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("Age");
dt.Columns.Add("Address");
dt.Columns.Add("comments");
dt.Columns.Add("Course");
dt.Columns.Add("Eligible");
dt.Columns.Add("HFID");
foreach (var arr in array)
{
DataRow dr = dt.NewRow();
dr["FirstName"] = arr[0];
dr["LastName"] = arr[1];
dr["Age"] = arr[2];
dr["Address"] = arr[3];
dr["comments"] = arr[4];
dr["Course"] = arr[5];
dr["Eligible"] = arr[6];
dr["HFId"] = arr[7];
dt.Rows.Add(dr);
}
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["DefaultConnection"].ToString();
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText="USPSaveDetails";
cmd.Connection = cnn;
cmd.Parameters.Add("@TableType", SqlDbType.Structured).SqlValue = dt;
result= cmd.ExecuteNonQuery().ToString();
}
catch (Exception ex)
{
result = ex.Message;
}
return result;
}
And in database:
We create a table into which we will insert data passed through Table-Valued type.
CREATE TABLE [dbo].[Table] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NULL,
[Age] INT NULL,
[Address] NVARCHAR (50) NULL,
[comments] NVARCHAR (50) NULL,
[Course] INT NULL,
[Eligible] BIT NULL,
[HFId] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
And then after creating the table, we will create a Table-Valued Type.
CREATE TYPE TableType AS TABLE
(
[FirstName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NULL,
[Age] INT NULL,
[Address] NVARCHAR (50) NULL,
[comments] NVARCHAR (50) NULL,
[Course] INT NULL,
[Eligible] BIT NULL,
[HFId] INT NULL
);
and then the Stored procedure with a parameter in it with type TableType
(that we created above).
CREATE PROCEDURE [dbo].[USPSaveDetails]
(
@TableType TableType READONLY
)
AS
begin
INSERT INTO [Table](FirstName,LastName,Age,Address,comments,Course,Eligible,HFId)
select FirstName,LastName,Age,Address,comments,Course,Eligible,HFId FROM @TableType
end
- In this USP, we are selecting rows from
TableType
and insert them into Table table.
That's it. The task is done. We just inserted the rows in a single batch!!!!!
You can find a demo in the attached zip file.