Menu

Insert Multiple Row in Database using Silverlight with Entity Framework

1. Create your table look like

 CREATE TABLE [dbo].[Students]
 (
 [StudentId] [int] IDENTITY(1,1) NOT NULL,
 [FName] [nvarchar](max) NOT NULL,
 [LName] [nvarchar](max) NOT NULL,
 [Address] [nvarchar](max) NOT NULL,
 [CountryId] [nvarchar](max) NOT NULL,
 [StateId] [nvarchar](max) NOT NULL,
 [CityId] [nvarchar](max) NOT NULL
 )

2. Then after create store procedure code like

 CREATE PROCEDURE InsertStudentInfoUsingXML
 (
 @PARAM VARCHAR(MAX)
 )
 AS
 BEGIN
 DECLARE @hDoc INT

EXEC sp_xml_preparedocument @hDoc OUTPUT, @PARAM

INSERT INTO Students
 SELECT FName ,
 LName ,
 Address ,
 CountryId ,
 StateId ,
 CityId
 FROM OPENXML(@hDoc,'/table/row')
 WITH (
 FName NVARCHAR(MAX),
 LName NVARCHAR(MAX),
 Address NVARCHAR(MAX),
 CountryId NVARCHAR(MAX),
 StateId NVARCHAR(MAX),
 CityId NVARCHAR(MAX)
 )
 EXEC sp_xml_removedocument @hDoc
 END

sp_xml_preparedocument returns a handle to the memory representation of XML document created with the second argument. This handle is later used in OPENXML function.
OPENXML provides a rowset view over an XML document.
sp_xml_removedocument removes the internal representation of the XML document specified by the document handle and invalidates the document handle.
3. Update your ‘Ado.Net Entity Data Model’
4. Now import store procedure in your ‘Ado.Net Entity Data Model’
5. Then after code in your “Domain Service” code like


[Invoke]
 public Int32 InsertStudentUsingXMl(string strStudent)
 {
 return this.ObjectContext.InsertStudentInfoUsingXML(strStudent);
 }

6. Build your solution
7. Now code in your “XAML.CS” file or where you needed this code like.

 //This method to use converts Ilist to string.
 public string ToXMListTest(IList list)
 {
 var sb = new StringBuilder();
 foreach(Student item in list)
 sb.AppendLine("");
 return String.Concat("

<table>", sb.ToString(), "</table>

");
 }

 // Insert Student information
 public void insertStudentInfoUsingXML()
 {
 List list = new List
 {
 new Student
 {
 FName="Sandeep1",
 LName="Test 1",
 Address="India",
 CountryId="1",
 StateId="1",
 CityId="1"
 },
 new Student
 {
 FName="Sandeep !",
 LName="Test",
 Address="India",
 CountryId="1",
 StateId="1",
 CityId="1"
 }
 };
 var result = context.InsertStudentUsingXMl(ToXMListTest(list),
 Insert_Completed, null);
 }

// Callback method.
 private void Insert_Completed(InvokeOperation iOperation)
 {

if (!iOperation.HasError)
 {
 if (iOperation.Value > 0)
 MessageBox.Show("Complete");

}
 else
 MessageBox.Show(iOperation.Error.ToString());
 }

No comments

Leave a Reply


6 × = 6

Categories