Getting data from xml and inserting into existing table using stored procedure and c#

See more:

C#

C#4.0

stored-procedure

public int UpdatePlantContacts(int PlantID, List<string> PlantContacts)
{
int i = 0;
DataTable dt = new DataTable("PlantAreaContact");
dt.Columns.Add("PlantAreaID",System.Type.GetType("System.Int"));
dt.Columns.Add("PlantAreaContact", System.Type.GetType("System.String"));
dt.Columns.Add("IsAreaHead", Type.GetType("System.Boolean"));
dt.Columns.Add("AreaEmailID", Type.GetType("System.String"));
dt.Columns.Add("LoginPassword", Type.GetType("System.String"));
dt.Columns.Add("SystemPermission", Type.GetType("System.String"));
foreach (DataRow dr in dt.Rows )
{
DataRow newRow = dt.NewRow();
dt.Rows.Add("~/XmlData/PlantAreaContacts");
}
StringWriter XMLWriter = new StringWriter();
dt.WriteXml(XMLWriter);
System.Xml.Linq.XElement XMLUncertanity = System.Xml.Linq.XElement.Parse(XMLWriter.ToString());
try
{
i = plcon.UpdatePlantContacts(PlantID, dt);
}
catch
{
throw new System.NotImplementedException();
}
if (i <= 0)
{
return 0;
}
else
{
return i;
}
}




public int UpdatePlantContacts(int PlantID, List<string> PlantContacts)
{
int i = 0;
DataTable dt = new DataTable("PlantAreaContact");
dt.Columns.Add("PlantAreaID",System.Type.GetType("System.Int"));
dt.Columns.Add("PlantAreaContact", System.Type.GetType("System.String"));
dt.Columns.Add("IsAreaHead", Type.GetType("System.Boolean"));
dt.Columns.Add("AreaEmailID", Type.GetType("System.String"));
dt.Columns.Add("LoginPassword", Type.GetType("System.String"));
dt.Columns.Add("SystemPermission", Type.GetType("System.String"));
foreach (DataRow dr in dt.Rows )
{
DataRow newRow = dt.NewRow();
dt.Rows.Add("~/XmlData/PlantAreaContacts");
}
StringWriter XMLWriter = new StringWriter();
dt.WriteXml(XMLWriter);
System.Xml.Linq.XElement XMLUncertanity = System.Xml.Linq.XElement.Parse(XMLWriter.ToString());
try
{
i = plcon.UpdatePlantContacts(PlantID, dt);
}
catch
{
throw new System.NotImplementedException();
}
if (i <= 0)
{
return 0;
}
else
{
return i;
}
}

This is my BAL code, I have xmldata in my vs2010 in my folder I want to insert the data in that and after that the xml data should insert using stored proc, below is my DAL

public int UpdatePlantContacts(int PlantID, DataTable PlantContacts)
{
try
{
int result = -1;
sqlcmd = fnProcedure("sp_UpdatePlantAreaContacts");
SqlParameter par1 = sqlcmd.Parameters.Add("@PlantAreaID", SqlDbType.Int);
par1.Value = PlantID;
SqlParameter par2 = sqlcmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
par2.Value = 1015;
//SqlParameter par3 = sqlcmd.Parameters.Add("@FilePath", SqlDbType.VarChar);
//par3.Value = "E:/QONE/P218/ENG/PlantAreaContactsXML.xml";
SqlParameter par4 = sqlcmd.Parameters.Add("@Return", SqlDbType.Int);
par4.Direction = ParameterDirection.Output;
try
{
fnOpen();
sqlcmd.ExecuteNonQuery();
result = Convert.ToInt32(sqlcmd.Parameters["@Return"].Value);
fnClose();
}
catch (Exception)
{
fnClose();
result = -1; ;
}
return result;
//Conn.Close();
//Conn.Dispose();
}
catch (Exception Ex)
{
CreateErrorLog Err = new CreateErrorLog();
Err.ErrorLog(HttpContext.Current.Server.MapPath("Logs/ErrorLog"), Ex.Message);
throw new System.NotImplementedException();
}
finally
{
}
}
And my stored proc is below
ALTER PROCEDURE [dbo].[SP_PlantcontactUpdate] @PlantAreaID int, @Doc XML
AS
BEGIN
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
DROP TABLE ##temp

select *
into ##temp from dbo.PlantAreaContact where PlantAreaID= @PlantAreaID
begin try
delete from dbo.PlantAreaContact where PlantAreaID= @PlantAreaID

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
insert into dbo.PlantAreaContact
SELECT @PlantAreaID, *
FROM OPENXML (@idoc, '/DocumentElement/PlantAreaContacts',2)
WITH (PlantAreaContact varchar(50),IsAreaHead int,AreaEmailID varchar(50),
AreaMobileNumber varchar(25),LoginPassword varbinary(10),SystemPermission varchar(25))

end try

begin catch

delete from dbo.PlantAreaContact where PlantAreaID= @PlantAreaID
insert into dbo.PlantAreaContact select * from ##temp
drop table ##temp

RAISERROR ('Failed PlantAreaContact',16,1);
end catch
END