MS SQL – C# – Entity Framework Project (import Data from XML, export to PDF)

Posted on by

In this project you’ll see how to use Entity Framework with MS SQL, parse data from XML file and export PDF with data from a database.

The project is about “Music store” where you have XML file with data about music albums. You should parse the date and fill it into database. Then you can make queries and get the results in PDF all done by C#.

The source files

If you try to start the project you’ll need Visual Studio and MS SQL Server.
Don’t forget to change the connection string in all App.config files (If you are using SQL Server Express, change the ‘.’ in “source=.”, with ‘.\SQLEXPRESS’).

I’ve created a simple database (use the music_store_db.sql script to generate the tables) and using Entity Framework (db first), C#, XPath and ItextSharp I’m parsing data from .xml file and I’m filling the data into db tables, then I’m exporting some data into .pdf.

The XMLParser.cs parse the date from XML and calls the Method “AddAlbumToDB” (MusicStoreDAL.cs) which fills the database tables with the parsed data.
The PDFExporter.cs makes a query to the database, makes PDF file and fills the data into it.
The Executor.cs calls XMLParser and PDFExporter (for ease).

The Pdf


SQL Diagram

diagram

XMLParser.cs

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
using MusicStore.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
 
namespace XMLParser
{
    public class XMLParser
    {
        static void Main()
        {          
        }
 
        public static void ParseDataFromXML(string path)
        {
            //Invariant Culture for the dates and decimas
            System.Threading.Thread.CurrentThread.CurrentCulture =
               System.Globalization.CultureInfo.InvariantCulture;
 
 
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(path);
            string xPath = "/albums/album";
            int indexer = 0;
 
            XmlNodeList albumCollection = xmlDoc.SelectNodes(xPath);
            foreach (XmlNode album in albumCollection)
            {
                indexer++;
                string albumName = GetChildText(album, "name");
                string artistName = GetChildText(album, "artist");
                string year = GetChildText(album, "year");
                string price = GetChildText(album, "price");
                string producerName = GetChildText(album, "producer");
                string genresString = GetChildText(album, "genre");
                string[] genres = { };
 
                if (genresString != null)
                {
                    genres = genresString.Split(',');
                    for (int i = 0; i < genres.Length; i++)
                    {
                        genres[i] = genres[i].Trim();
                    }
                }
 
                List<string> songs = new List<string>();
                List<string> duration = new List<string>();
 
                XmlNodeList songsCollection = xmlDoc.SelectNodes("albums/album[" + indexer + "]/songs/song");
 
                foreach (XmlNode song in songsCollection)
                {
                    songs.Add(song.InnerText.ToString());
                    duration.Add(song.SelectSingleNode("@duration").FirstChild.Value.ToString());
                }
 
                MusicStoreDAL.AddAlbumToDB(albumName, artistName, year, price, producerName, genres, songs, duration);
            }
        }
 
        private static string GetChildText(XmlNode album, string element)
        {
            XmlNode childNode = album.SelectSingleNode(element);
            if (childNode == null)
            {
                return null;
            }
            return childNode.InnerText.Trim();
        }
    }
}
 

MusicStoreDAL.cs

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace MusicStore.Data
{
    public class MusicStoreDAL
    {
        public static void AddAlbumToDB(string albumName, string artistName, string year, string price, string producerName, string[] genres, List<string> songs, List<string> duration)
        {
            CultureInfo provider = CultureInfo.InvariantCulture;
 
            using (var context = new MusicStoreEntities())
            {
                Artist newArtist = AddOrLoadArtist(context, artistName);
 
                Producer newProducer = AddOrLoadProducer(context, producerName);
 
                Album newAlbum = new Album();
                newAlbum.AlbumName = albumName;
                newAlbum.ArtistID = newArtist.ArtistID;
                newAlbum.ProducerID = newProducer.ProducerID;
                newAlbum.Date = DateTime.ParseExact(year.ToString(), "yyyy.mm.dd", provider);
                newAlbum.Price = decimal.Parse(price, provider);
 
                foreach (var genre in genres)
                {
                    Genre newGenre = AddOrLoadGenre(context, genre);
                    newAlbum.Genres.Add(newGenre);
                }
 
                int counter = 0;
                foreach (var song in songs)
                {
                    Song newSong = AddOrLoadSong(context, song, duration[counter]);
                    newAlbum.Songs.Add(newSong);
                    counter++;
                }
 
                context.Albums.Add(newAlbum);
                context.SaveChanges();
            }
        }
 
        private static Artist AddOrLoadArtist(MusicStoreEntities context, string artistName)
        {
            Artist existingArtist =
            (from a in context.Artists
             where a.ArtistName == artistName
             select a).FirstOrDefault();
            if (existingArtist != null)
            {
                return existingArtist;
            }
 
            Artist newArtist = new Artist();
            newArtist.ArtistName = artistName;
            context.Artists.Add(newArtist);
            context.SaveChanges();
 
            return newArtist;
        }
 
        private static Producer AddOrLoadProducer(MusicStoreEntities context, string producerName)
        {
            Producer existingProducer =
            (from p in context.Producers
             where p.ProducerName == producerName
             select p).FirstOrDefault();
 
            if (existingProducer != null)
            {
                return existingProducer;
            }
 
            Producer newProducer = new Producer();
            newProducer.ProducerName = producerName;
            context.Producers.Add(newProducer);
            context.SaveChanges();
 
            return newProducer;
        }
 
        private static Genre AddOrLoadGenre(MusicStoreEntities context, string genreName)
        {
            Genre existingGenre =
            (from g in context.Genres
             where g.GenreName == genreName
             select g).FirstOrDefault();
 
            if (existingGenre != null)
            {
                return existingGenre;
            }
 
            Genre newGenre = new Genre();
            newGenre.GenreName = genreName;
            context.Genres.Add(newGenre);
            context.SaveChanges();
 
            return newGenre;
        }
 
        private static Song AddOrLoadSong(MusicStoreEntities context, string song, string duration)
        {
            Song newSong = new Song();
            newSong.SongName = song;
            newSong.Duration = TimeSpan.Parse("00:"+duration);
            context.SaveChanges();
 
            return newSong;
        }
    }
}
 

PDFExporter.cs

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
using iTextSharp.text;
using iTextSharp.text.pdf;
using MusicStore.Data;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace PDFExporter
{
    public class PDFExporter
    {
        static void Main()
        {
          
        }
        public static void ExportDataToPDF(string startDate, string endDate)
        {
            using (var context = new MusicStoreEntities())
            {
                Document doc = new Document();
                FileStream file = File.Create("../../../AlbumsQuery.pdf");
                PdfWriter.GetInstance(doc, file);
                doc.Open();
 
                //set table
                PdfPTable table = new PdfPTable(4);
                table.TotalWidth = 440f;
                table.LockedWidth = true;
                float[] widths = new float[] { 30f, 160f, 120f, 70f };
                table.SetWidths(widths);
 
                PdfPCell cell = new PdfPCell();
 
                //set fonts
                BaseFont bfTimes = BaseFont.CreateFont("../../../segoeui.ttf", BaseFont.CP1252, false);
                Font normal = new Font(bfTimes, 10);
                Font bold = new Font(bfTimes, 11, Font.BOLD);
 
                //title cell
                cell = new PdfPCell(new Phrase("Albums Between: " + startDate +" - " + endDate , bold));
                cell.Colspan = 4;
                cell.HorizontalAlignment = 1;
                cell.BackgroundColor = new BaseColor(135, 196, 28);
                cell.PaddingTop = 10f;
                cell.PaddingBottom = 10f;
                table.AddCell(cell);
 
                DateTime sD = DateTime.ParseExact(startDate, "yyyy", CultureInfo.InvariantCulture);
                DateTime eD = DateTime.ParseExact(endDate, "yyyy", CultureInfo.InvariantCulture);
                //the query from the DB                  
                var query = (from al in context.Albums
                             join art in context.Artists on al.ArtistID equals art.ArtistID
                             join p in context.Producers on al.ProducerID equals p.ProducerID
                             join s in context.Songs on al.AlbumID equals s.AlbumID
                             where al.Date >= sD && al.Date <= eD
                             select new
                             {
                                 al.AlbumName,
                                 al.Date,
                                 al.Price,
                                 art.ArtistName,
                                 p.ProducerName,
                                 s.SongName,
                                 s.Duration,
                                 Genres = al.Genres.Select(g => g.GenreName)
                             })
                             .OrderBy(x => x.ArtistName).ThenBy(x => x.Date);
 
                //helpers
                bool isFirst = true;
                string albName = string.Empty;
                TimeSpan totalDur = new TimeSpan(0, 0, 0);
                TimeSpan lastTotalDur = new TimeSpan(0, 0, 0);
                decimal? lastPrice = 0.00m;
                int index = 1;
                List<string> firstGenres = new List<string>();
 
                foreach (var item in query)
                {
                    //only for firs header
                    if (isFirst)
                    {    
                        //helpers
                        albName = item.AlbumName;
                        isFirst = false;
 
                        //empty row
                        cell = new PdfPCell(new Phrase(" "));
                        cell.Colspan = 4;
                        cell.BackgroundColor = new BaseColor(220, 237, 191);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //artist info
                        cell = new PdfPCell(new Phrase("Artist: " + item.ArtistName, normal));
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //album info
                        cell = new PdfPCell(new Phrase("Album: " + item.AlbumName, normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //date
                        cell = new PdfPCell(new Phrase("Date: " + item.Date.ToString("dd-mm-yyyy"), normal));
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //producer
                        cell = new PdfPCell(new Phrase("Producer: " + item.ProducerName, normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //"N"
                        cell = new PdfPCell(new Phrase("N", normal));
                        cell.HorizontalAlignment = 1;
                        cell.Colspan = 1;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //"Tracks"
                        cell = new PdfPCell(new Phrase("Tracks", normal));
                        cell.HorizontalAlignment = 0;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //"Duration"
                        cell = new PdfPCell(new Phrase("Duration", normal));
                        cell.HorizontalAlignment = 1;
                        cell.Colspan = 1;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
                    }
 
                    //all data from single album
                    if (albName == item.AlbumName)
                    {
                        //tracks data
                        //index
                        cell = new PdfPCell(new Phrase(index.ToString(), normal));
                        cell.HorizontalAlignment = 1;
                        cell.Colspan = 1;
                        table.AddCell(cell);
 
                        //song name
                        cell = new PdfPCell(new Phrase(item.SongName, normal));
                        cell.HorizontalAlignment = 0;
                        cell.Colspan = 2;
                        table.AddCell(cell);
 
                        //song duration
                        cell = new PdfPCell(new Phrase(item.Duration.ToString(@"mm\:ss"), normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        table.AddCell(cell);
                        index++;
                        totalDur += TimeSpan.Parse(item.Duration.ToString());
 
                        //helpers
                        lastPrice = item.Price;
                        lastTotalDur = totalDur;
 
                        //genres
                        firstGenres = string.Join(", ", item.Genres.ToList()).Split(',').ToList();
                        for (int i = 0; i < firstGenres.Count; i++)
                        {
                            firstGenres[i] = firstGenres[i].Trim();
                        }
                    }
                    //end of an album (footer) and start of a new one
                    else
                    {
                        index = 1;
 
                        //---------footer except the last one
                        //price info
                        cell = new PdfPCell(new Phrase("Price: " + lastPrice.Value.ToString("00.00"), normal));
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //total duration info
                        cell = new PdfPCell(new Phrase("Total Duration: " + totalDur.ToString(@"hh\:mm\:ss"), normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //genres
                        cell = new PdfPCell(new Phrase("Genre: " + string.Join(", ", firstGenres), normal));
                        cell.Colspan = 4;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        albName = item.AlbumName;
                        totalDur = new TimeSpan(0, 0, 0);
 
                        //-------------start new album info----------------------
                        //air info
                        cell = new PdfPCell(new Phrase(" "));
                        cell.Colspan = 4;
                        cell.BackgroundColor = new BaseColor(250, 250, 250);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //artist info
                        cell = new PdfPCell(new Phrase("Artist: " + item.ArtistName, normal));
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //album info
                        cell = new PdfPCell(new Phrase("Album: " + item.AlbumName, normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //date
                        cell = new PdfPCell(new Phrase("Date: " + item.Date.ToString("dd-mm-yyyy"), normal));
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.HorizontalAlignment = 0;
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //producer
                        cell = new PdfPCell(new Phrase("Producer: " + item.ProducerName, normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //"N"
                        cell = new PdfPCell(new Phrase("N", normal));
                        cell.HorizontalAlignment = 1;
                        cell.Colspan = 1;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //"Tracks"
                        cell = new PdfPCell(new Phrase("Tracks", normal));
                        cell.HorizontalAlignment = 0;
                        cell.Colspan = 2;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //"Duration"
                        cell = new PdfPCell(new Phrase("Duration", normal));
                        cell.HorizontalAlignment = 1;
                        cell.Colspan = 1;
                        cell.BackgroundColor = new BaseColor(135, 196, 28);
                        cell.PaddingBottom = 5f;
                        cell.PaddingLeft = 5f;
                        table.AddCell(cell);
 
                        //----------------first row of data on evry album except the first one
                        //tracks data
                        //index
                        cell = new PdfPCell(new Phrase(index.ToString(), normal));
                        cell.HorizontalAlignment = 1;
                        cell.Colspan = 1;
                        table.AddCell(cell);
 
                        //song name
                        cell = new PdfPCell(new Phrase(item.SongName, normal));
                        cell.HorizontalAlignment = 0;
                        cell.Colspan = 2;
                        table.AddCell(cell);
 
                        //song duration
                        cell = new PdfPCell(new Phrase(item.Duration.ToString(@"mm\:ss"), normal));
                        cell.HorizontalAlignment = 2;
                        cell.Colspan = 2;
                        table.AddCell(cell);
 
                        index++;
                        totalDur += TimeSpan.Parse(item.Duration.ToString());
 
                        //the genres except for first album
                        firstGenres = string.Join(", ", item.Genres.ToList()).Split(',').ToList();
                        for (int i = 0; i < firstGenres.Count; i++)
                        {
                            firstGenres[i] = firstGenres[i].Trim();
                        }
                    }
                }
                //the final price, duration and genre (last footer)
                //price info
                cell = new PdfPCell(new Phrase("Price: " + lastPrice.Value.ToString("00.00"), normal));
                cell.Colspan = 2;
                cell.BackgroundColor = new BaseColor(135, 196, 28);
                cell.HorizontalAlignment = 0;
                cell.PaddingBottom = 5f;
                cell.PaddingLeft = 5f;
                table.AddCell(cell);
 
                //total duration info
                cell = new PdfPCell(new Phrase("Total Duration: " + lastTotalDur.ToString(@"hh\:mm\:ss"), normal));
                cell.HorizontalAlignment = 2;
                cell.Colspan = 2;
                cell.BackgroundColor = new BaseColor(135, 196, 28);
                cell.PaddingBottom = 5f;
                cell.PaddingLeft = 5f;
                table.AddCell(cell);
 
                //ganres info
                cell = new PdfPCell(new Phrase("Genre: " + string.Join(", ", firstGenres), normal));
                cell.Colspan = 4;
                cell.BackgroundColor = new BaseColor(135, 196, 28);
                cell.HorizontalAlignment = 0;
                cell.PaddingBottom = 5f;
                cell.PaddingLeft = 5f;
                table.AddCell(cell);
 
                doc.Add(table);
                doc.Close();
            }
        }
    }
}
 

Sublime Text 2 Installation On Windows 7 Plus Package Control And Node.js

Posted on by

In this video you can see how to install Sublime text 2 on Windows 7. In addition I’ll show you how to add extensions (packages) directly from the interface with the help of “Package control”, how to add option to the windows context menu “Open folder as Sublime project”, and how to install and integrate node.js which can build javaScript code and show log massages in the Sublime’s console.

Download Sublime text 2

Download and install package control for sublime

Sublime text 2 context menu windows

Download Node.js

Snippet for node.js (build system) –

1
2
3
4
5
6
{
 
    "cmd":  ["node",  "$file"]
 
}
 

(save it as javascript.sublime-build)

Tagged ,