Database:
//Create new type (i.e. Table data type) as input parameter to be used in ADO.Net SQL Command
CREATE TYPE DtFile as Table(
Id int
)
//Create Procedure and use the above created type in it.
Create Proc dbo.UspInsertFileData(
@FileId dbo.DtFile READONLY
)
AS
Begin
INSERT INTO MainFileData (FileId, AddedOn)
SELECT Id, GETDATE() From dbo.DtFile
End
C# Asp.Net:
Data Access Layer:
public static int InsertFileData(DataTable dtFileIds)
{
var sqlConnectionString = ConfigurationManager.AppSettings["fileConnection"];
var sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter
{
ParameterName = @"FileId",
SqlDbType = SqlDbType.Structured,
Value = fileTreeIds,
TypeName = @"DtFile"
};
var result = SqlHelper.ExecuteScalar(sqlConnectionString, CommandType.StoredProcedure, @"UspInsertFileData" , sqlParameters); return Convert.ToInt32(result);
}
Presentation Layer:
protected void BtnClickAddFileData(object sender, EventArgs e)
{
var fileIds = new[] {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
var dtFileData = new DataTable();
if (fileIds.Length > 0)
{
{
/* Make Sure to keep the name of the data column same as given in the SQL User-Defined type */
dtFileData.Columns.Add(new DataColumn(@"Id", typeof(int)));
foreach (var id in fileIds)
{
var dr = dtFileData.NewRow();
dr["FileTreeId"] = item;
dtFileData.Rows.Add(dr);
dtFileData.AcceptChanges();
}
}
foreach (var id in fileIds)
{
var dr = dtFileData.NewRow();
dr["FileTreeId"] = item;
dtFileData.Rows.Add(dr);
dtFileData.AcceptChanges();
}
}
var result = Dal.InsertFileData(dtFileData);
}
No comments:
Post a Comment