generate xml file with sql query

amina89

Hi, I work on the code generation xml file I do not know how to modify it to populate the xml using a query sqlserver:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml.Serialization;
using System.Collections.Generic;
using System.Collections.Generic;
using System.IO;
using System.Xml;
using System.Text;

public partial class Etudiant11 : System.Web.UI.Page
{

protected void Page\_Load(object sender, EventArgs e)
{

}
public class Etudiant
{
    public int NumInscription { get; set; }
    public string Nom { get; set; }
    public string Prenom { get; set; }
    public string CodeSexe { get; set; }
}

protected void Button1\_Click(object sender, EventArgs e)
{
    XmlSerializer serializer = new XmlSerializer(typeof(List<Etudiant>));
    List<Etudiant> etudiants = new List<Etudiant>();
   
    FileInfo fi = new FileInfo(@"C:\\Temp\\Etudiants.xml");

    // Creation d'un étudiant. Peu être créé à partir d'une requête SQL 
    Etudiant etudiant = new Etudiant() { NumInscription = 123, Nom = "xxx", Prenom = "", CodeSexe = "M" };
    // Ajout de l'étudiant à la liste 
    etudiants.Add(etudiant);
    etudiants.Add(etudiant);
    etudiants.Add(etudiant);

    // Création du fichier XML correspondant à la liste d'etudiants 
    using (MemoryStream mem = new MemoryStream())
    {
        serializer.Serialize(mem, etudiants);

        using (FileStream fs = new FileStream(fi.FullName, FileMode.Create))
        {
            using (StreamWriter sw = new StreamWriter(fs, Encoding.UTF8))
            {
                sw.Write(Encoding.UTF8.GetString(mem.ToArray()));
            }
        }
    }

    // Destruction des objets 
    etudiant = null;
    etudiants = null;

    // Lecture du fichier XML généré. 
    // Recréation de la liste d'étudiant 
    using (FileStream stream = fi.OpenRead())
    {
        XmlReader reader = XmlReader.Create(stream);
        string str = reader.ReadInnerXml();

        if (serializer.CanDeserialize(reader))
        {
            etudiants = (List<Etudiant>)serialize

Rhys Jacob

Do you mean you want to save an Etudiant to the DB or you want to populate an Etudiant from the DB?

dan sh

You are trying to use an update statement on a geenric list of ETUDIANT objects. AFAIK, you cannot do this. Since your aim is to modify the XML file, you can make use of XmlDocument class.

amina89

Hi, I try to create an xml file (from the database ) containing the student of a module, and then allow the teachers to download the file and convert it to Excel and insert markes for students then send the data to bda. I do not know how do this, and if my way is right or not, I need your advice and help. Thanks

dan sh

Why are you creating XML? Just create an Excel file from the database and let user download it. Once user is done with the changes, have a place where he can upload it and then save it back to the database. You also get rid of this download and upload. Have a page with some GridView to display the student's records and have a set of controls where one can edit the data. Place a save button and make changes directly to the database instead of having files in between.

amina89

Hi, I already tried the method of the gridview but I have hundreds of students in a module managing the grid is heavy. To excel I try this code

cn.Open();
SqlDataAdapter da = new SqlDataAdapter(@"DECLARE @MATIERE nvarchar(max)
SELECT @MATIERE =
STUFF(
(
select distinct ',[' + LIBELLE_MATIERE + ']'
from MATIERE where CODE_MODULE =11
for xml path('')
),
1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
*
from (

SELECT ETUDIANT.NUM_INSCRIPTION, ETUDIANT.NOM_PRENOM_ETUDIANT_ARABE,EXAMEN.CODE,
EXAMEN.NOTE,
MATIERE.LIBELLE_MATIERE
FROM ETUDIANT INNER JOIN
EXAMEN ON ETUDIANT.NUM_INSCRIPTION = EXAMEN.NUM_INSCRIPTION INNER JOIN
CAPITALISE_MODULE ON
ETUDIANT.NUM_INSCRIPTION = CAPITALISE_MODULE.NUM_INSCRIPTION INNER JOIN
MATIERE ON EXAMEN.CODE_MATIERE = MATIERE.CODE_MATIERE INNER JOIN
MODULE ON CAPITALISE_MODULE.CODE_MODULE = MODULE.CODE_MODULE
AND MATIERE.CODE_MODULE = MODULE.CODE_MODULE
WHERE (CAPITALISE_MODULE.CODE_MODULE = 11) AND
(CAPITALISE_MODULE.CODE_TYPE_RELATION_MODULE = 1)
) Data
PIVOT (
SUM(NOTE)
FOR LIBELLE_MATIERE
IN (
' + @MATIERE + '
)
) PivotTable
'

exec sp_executesql @SQL

", cn);
DataTable dt = new DataTable();
da.Fill(dt);
cn.Close();

   Response.Clear();
   Response.ContentType = "application/vnd.ms-excel";
   string sep = "";
   foreach (DataColumn dc in dt.Columns)
   {
          Response.Write(sep + dc.ColumnName);
          sep = "\\t";
   }
   Response.Write("\\n");

   int i;
   foreach (DataRow dr in dt.Rows)
   {
          sep = "";
          for (i = 0; i < dt.Columns.Count; i++)
          {
                 Response.Write(sep + dr\[i\].ToString());
                 sep = "\\t";
          }
          Response.Write("\\n");
   }

}

excel file generated by this code gives me this: Sudeni_id_____marks_matter1____marks_matte2 but how to allow just the marks between 0 and 20? is a method to calculate the average module directl:ysum(marks_matter1*coefficient)/sum(coefficent) I chose XML because it's easy to send to the database(I read that in an article )but i have a problem with generate it with sql,excel i dont have any edea if the data are not the same table.