C# WinForms example

[ronlemire61]

Sample programs page was locked out to me so I'm attaching a .zip file of my C# WinForms solution.
It's very basic at this point but it works.
In one step it will get a range of program data.
In another step it will load the data into a SQL Server database (database script included).
It's not fully tested.
It contains the modified .wsdl and a proxy generated by wsdl.exe.
It's only a good starting point for a C# developer.

Ron

Zip Updates:
SchedulesDirect_11_11_2008.zip
1) Load method updated to insert into the following tables: genre, lineup, map, member, program, schedule, station
2) Included some sql queries in \sql folder

[ronlemire61]

Should have added that:
1) Getting data via the proxy is working.
2) Loading the 'program' and the 'schedule' data to the database is working.
3) Loading of the other tables which I coded this morning has never been tested and is not working.
When all the bugs are fixed, I'll upload another solution.

Ron

[ronlemire61]

Here's an update to the cmdLoadData_Click method to fill the following tables: genre, lineup, map, member, program, schedule, station.
The updated code:
1) turns off database constraints so that I don't enforce foreign key constraints to parent tables.
2) checks for a few fields that were throwing exceptions for null values
The code is still just a proof of concept to:
1) see if I can get TMS data via a web service call
2) add the data to Sql Server
2) query the data to see if it's what I need
I also have to refine the database schema. I did a quick and dirty one to get some data into the database so that I could query it. There were a few database fields that are not typed correctly e.g. schedule->duration. Duration was typed as a TimeSpan in the dataset but Sql Server doesn't have a TimeSpan type so I made duration a DateTime in the dataset. Plus I forced the value to be the current datetime. Now that I look at the data I see that Duration comes in as a string ("PT01H00M") so I have to go back and make changes. I did this for about 5-10 fields. So expect some bad data. But I am getting meaningful queries with what I have done which I've included below the updated load code.

Ron

Updated load:

Code: Select all

      private void cmdLoadData_Click(object sender, EventArgs e)
      {
         tmsxtvdDataSet ds = new tmsxtvdDataSet();
         ds.EnforceConstraints = false;

         xtvdResponse results = GetResultsFromFile();

         // programRow
         tmsxtvdDataSet.programRow programRow = null;
         foreach (programsProgram programsProgram in results.xtvd.programs)
         {
            programRow = ds.program.NewprogramRow();
            programRow.id = programsProgram.id;
            programRow.title = programsProgram.title;
            programRow.subtitle = programsProgram.subtitle;
            programRow.description = programsProgram.description;
            programRow.mpaaRating = programsProgram.mpaaRating.ToString();
            programRow.starRating = programsProgram.starRating.ToString();
            //programRow.runTime = programsProgram.runTime;
            programRow.runTime = DateTime.Now;
            //programRow.year = programsProgram.year;
            programRow.year = DateTime.Now;
            programRow.showType = programsProgram.showType;
            programRow.series = programsProgram.series;
            programRow.colorCode = programsProgram.colorCode.ToString();
            programRow.syndicatedEpisodeNumber = programsProgram.syndicatedEpisodeNumber;
            //programRow.originalAirDate = programsProgram.originalAirDate;
            programRow.originalAirDate = DateTime.Now;
            //programRow.programs_Id = programsProgram.programs_Id;
            ds.program.AddprogramRow(programRow);
         }

         // scheduleRow
         tmsxtvdDataSet.scheduleRow scheduleRow = null;
         foreach (schedulesSchedule schedulesSchedule in results.xtvd.schedules)
         {
            scheduleRow = ds.schedule.NewscheduleRow();
            scheduleRow.program = schedulesSchedule.program;
            scheduleRow.station = schedulesSchedule.station;
            scheduleRow.time = schedulesSchedule.time.ToLocalTime();
            scheduleRow.duration = DateTime.Now;
            scheduleRow.repeat = schedulesSchedule.repeat;
            scheduleRow.tvRating = schedulesSchedule.tvRating.ToString();
            scheduleRow.stereo = schedulesSchedule.stereo;
            scheduleRow.subtitled = schedulesSchedule.subtitled;
            scheduleRow.hdtv = schedulesSchedule.hdtv;
            scheduleRow.closeCaptioned = schedulesSchedule.closeCaptioned;
            scheduleRow.dolby = schedulesSchedule.dolby.ToString();
            scheduleRow.ei = schedulesSchedule.ei;
            //scheduleRow.schedules_Id = schedulesSchedule.schedules_Id;
            ds.schedule.AddscheduleRow(scheduleRow);
         }

         // genreRow
         tmsxtvdDataSet.genreRow genreRow = null;
         genresProgramGenreGenre[] genreGenres = null;
         foreach (genresProgramGenre genresProgramGenre in results.xtvd.genres) {
            genreGenres = genresProgramGenre.genre;
            foreach (genresProgramGenreGenre genresProgramGenreGenre in genreGenres) {
               genreRow = ds.genre.NewgenreRow();
               genreRow._class = genresProgramGenreGenre.@class;
               genreRow.relevance = genresProgramGenreGenre.relevance;
               //TODO: link genre to programGenre
               genreRow.programGenre_Id = 0;
               ds.genre.AddgenreRow(genreRow);
            }
         }

         // lineupRow
         tmsxtvdDataSet.lineupRow lineupRow = null;
         tmsxtvdDataSet.mapRow mapRow = null;
         lineupsLineupMap[] lineupMap = null;
         foreach (lineupsLineup lineupsLineup in results.xtvd.lineups) {
            lineupMap = lineupsLineup.map;
            foreach (lineupsLineupMap lineupsLineupMap in lineupMap) {
               mapRow = ds.map.NewmapRow();
               mapRow.station = lineupsLineupMap.station;
               mapRow.channel = lineupsLineupMap.channel;
               mapRow.channelMinor = 0;
               if (lineupsLineupMap.channelMinor != null) {
                  ulong.Parse(lineupsLineupMap.channelMinor);
               }

               //TODO: Need code to filter invalid data
               mapRow.from = DateTime.Now;
               mapRow.to = DateTime.Now;
               
               //TODO: link map to lineup
               //mapRow.lineup_Id = int.Parse(lineupsLineup.id);   // map to lineup link
               mapRow.lineup_Id = 0;
               ds.map.AddmapRow(mapRow);
            }
            lineupRow = ds.lineup.NewlineupRow();
            lineupRow.id = lineupsLineup.id;
            lineupRow.name = lineupsLineup.name;
            lineupRow.userLineupName = lineupsLineup.userLineupName;
            lineupRow.location = lineupsLineup.location;
            lineupRow.type = lineupsLineup.type.ToString();
            lineupRow.device = lineupsLineup.device;
            lineupRow.postalCode = lineupsLineup.postalCode;
           
            //TODO: link linupRow with lineupsRow
            lineupRow.lineups_Id = int.MinValue;
            ds.lineup.AddlineupRow(lineupRow);

         }

         // memberRow
         tmsxtvdDataSet.memberRow memberRow = null;
         crewMember[] crewMembers = null;
         foreach (productionCrewCrew productionCrewCrew in results.xtvd.productionCrew)
         {
            crewMembers = productionCrewCrew.member;
            foreach (crewMember crewMember in crewMembers) {
               memberRow = ds.member.NewmemberRow();
               memberRow.role = crewMember.role;
               memberRow.givenname = crewMember.givenname;
               memberRow.surname = crewMember.surname;
               
               //TODO: link member with crew
               memberRow.crew_Id = int.MinValue;
               ds.member.AddmemberRow(memberRow);
            }
         }

         // stationRow
         tmsxtvdDataSet.stationRow stationRow = null;
         foreach (stationsStation stationsStation in results.xtvd.stations) {
            stationRow = ds.station.NewstationRow();
            stationRow.id = stationsStation.id;
            stationRow.callSign = stationsStation.callSign;
            stationRow.name = stationsStation.name;
            stationRow.fccChannelNumber = 0;
            if (stationsStation.fccChannelNumber != null) {
               stationRow.fccChannelNumber = ulong.Parse(stationsStation.fccChannelNumber);
            }
            stationRow.affiliate = stationsStation.affiliate;
           
            //TODO: link station with stations
            stationRow.stations_Id = int.MinValue;
            ds.station.AddstationRow(stationRow);
         }

         //SqlConnection conn = new SqlConnection(@"Data Source=neo;Initial Catalog=SchedulesDirect;Integrated Security=True");
         SqlConnection conn = new SqlConnection(@"Data Source=rlemire\sqlexpress;Initial Catalog=SchedulesDirect;Integrated Security=True");
         SqlDataAdapter da = new SqlDataAdapter();

         // Insert genre
         string insertGenre = "INSERT INTO [dbo].[genre] ([class], [relevance], [programGenre_Id]) VALUES (@class, @relevance, @programGenre_Id)";
         da.InsertCommand = new SqlCommand(insertGenre, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@class", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "class", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@relevance", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "relevance", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@programGenre_Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "programGenre_Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.genre);

         // Insert lineup
         string insertLineup = @"INSERT INTO [dbo].[lineup] ([id], [name], [userLineupName], [location], [type], [device], [postalCode], [lineups_id]) VALUES (@id, @name, @userLineupName, @location, @type, @device, @postalCode, @lineups_id)";
         da.InsertCommand = new SqlCommand(insertLineup, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "name", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@userLineupName", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "userLineupName", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@location", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "location", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "type", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@device", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "device", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@postalCode", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "postalCode", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@lineups_id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "lineups_id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.lineup);

         // Insert map
         string insertMap = @"INSERT INTO [dbo].[map] ([station], [channel], [channelMinor], [from], [to], [lineup_Id]) VALUES (@station, @channel, @channelMinor, @from, @to, @lineup_Id)";
         da.InsertCommand = new SqlCommand(insertMap, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@station", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "station", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@channel", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "channel", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@channelMinor", System.Data.SqlDbType.BigInt, 0, System.Data.ParameterDirection.Input, 0, 0, "channelMinor", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@from", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "from", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@to", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "to", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@lineup_Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "lineup_Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.map);

         // Insert member
         string insertMember = "INSERT INTO [dbo].[member] ([role], [givenName], [surname], [crew_Id]) VALUES (@role, @givenName, @surname, @crew_Id)";
         da.InsertCommand = new SqlCommand(insertMember, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@role", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "role", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@givenName", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "givenName", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@surname", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "surname", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@crew_Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "crew_Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.member);

         // Insert program
         string insertProgram = @"INSERT INTO [dbo].[program] ([id], [title], [subtitle], [description], [mpaaRating], [starRating], [runTime], [year], [showType], [series], [colorCode], [syndicatedEpisodeNumber], [originalAirDate], [programs_Id]) VALUES (@id, @title, @subtitle, @description, @mpaaRating, @starRating, @runTime, @year, @showType, @series, @colorCode, @syndicatedEpisodeNumber, @originalAirDate, @programs_Id)";
         da.InsertCommand = new SqlCommand(insertProgram, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@title", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "title", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@subtitle", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "subtitle", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@description", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "description", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@mpaaRating", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "mpaaRating", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@starRating", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "starRating", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@runTime", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "runTime", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@year", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "year", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@showType", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "showType", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@series", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "series", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@colorCode", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "colorCode", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@syndicatedEpisodeNumber", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "syndicatedEpisodeNumber", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@originalAirDate", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "originalAirDate", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@programs_Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "programs_Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.program);

         // Insert schedule
         string insertSchedule = @"INSERT INTO [dbo].[schedule] ([program], [station], [time], [duration], [repeat], [tvRating], [stereo], [subtitled], [hdtv], [closeCaptioned], [dolby], [new], [ei], [schedules_Id]) VALUES (@program, @station, @time, @duration, @repeat, @tvRating, @stereo, @subtitled, @hdtv, @closeCaptioned, @dolby, @new, @ei, @schedules_Id)";
         da.InsertCommand = new SqlCommand(insertSchedule, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@program", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "program", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@station", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "station", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@time", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "time", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@duration", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "duration", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@repeat", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "repeat", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@tvRating", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "tvRating", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stereo", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "stereo", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@subtitled", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "subtitled", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@hdtv", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "hdtv", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@closeCaptioned", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "closeCaptioned", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@dolby", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "dolby", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@new", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "new", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ei", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, 0, 0, "ei", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@schedules_Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "schedules_Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.schedule);

         // Insert station
         string insertStation = "INSERT INTO [dbo].[station] ([id], [callSign], [name], [fccChannelNumber], [affiliate], [stations_Id]) VALUES (@id, @callSign, @name, @fccChannelNumber, @affiliate, @stations_Id)";
         da.InsertCommand = new SqlCommand(insertStation, conn);
         da.InsertCommand.CommandType = System.Data.CommandType.Text;
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@callSign", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "callSign", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "name", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@fccChannelNumber", System.Data.SqlDbType.BigInt, 0, System.Data.ParameterDirection.Input, 0, 0, "fccChannelNumber", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@affiliate", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "affiliate", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stations_Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "stations_Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
         da.Update(ds.station);

         conn.Close();
         
         int programCount = ds.program.Count;
         int scheduleCount = ds.schedule.Count;

         //dgvSchedules.DataSource = ds.schedule.DefaultView;
         //dgvPrograms.DataSource = ds.program.DefaultView;

         //tcSchedulesDirect.SelectedTab = tabSchedules;     

         MessageBox.Show("Done");
      }



Some Sql Server queries:

Code: Select all

-- movie list
select distinct id, title, subtitle
from program
where id like 'MV%'
order by title, subtitle

-- movie schedule for 1 movie
select distinct p.id, p.title, p.subtitle, s.station, s.time, st.callSign, m.channel
from program p
   inner join schedule s on p.id = s.program
   inner join station st on st.id = s.station
   inner join map m on st.id = m.station
where p.id like 'MV%' and
   p.title like '%The Passion of Ayn Rand%'
order by p.title, p.subtitle, s.time, st.callSign

-- movies on a callSign in time order
select distinct p.id, p.title, p.subtitle, s.station, s.time, st.callSign, m.channel
from program p
   inner join schedule s on p.id = s.program
   inner join station st on st.id = s.station
   inner join map m on st.id = m.station
where p.id like 'MV%' and
   st.callSign like '%AMC%' and
   m.channel < 500
order by s.time, p.title, p.subtitle

-- movies on a callSign in title order
select distinct p.id, p.title, p.subtitle, s.station, s.time, st.callSign, m.channel
from program p
   inner join schedule s on p.id = s.program
   inner join station st on st.id = s.station
   inner join map m on st.id = m.station
where p.id like 'MV%' and
   st.callSign like '%AMC%' and
   m.channel < 500
order by p.title, p.subtitle, s.time

-- channels by channel number
select distinct m.channel,st.callSign, st.name
from station st
   inner join map m on st.id = m.station
order by m.channel

-- channels by callSign
select distinct st.callSign, st.name, m.channel
from station st
   inner join map m on st.id = m.station
order by st.callSign

-- channels by name
select distinct st.name, st.callSign, m.channel
from station st
   inner join map m on st.id = m.station
order by st.name

-- HD channels by channel number
select distinct m.channel,st.callSign, st.name
from station st
   inner join map m on st.id = m.station
where st.name like '%HD%'
order by m.channel

-- HD channels by channel number
select distinct st.callSign,m.channel, st.name
from station st
   inner join map m on st.id = m.station
where st.name like '%HD%'
order by st.callSign, m.channel

-- sport list
select distinct id, title, subtitle
from program
where id like 'SP%'
order by title, subtitle

-- show list
select distinct id, title, subtitle
from program
where id like 'SH%'
order by title, subtitle

-- extended info show list
select distinct p.id, p.title, p.subtitle, s.time, st.callSign, m.channel
from program p
   inner join schedule s on p.id = s.program
   inner join station st on st.id = s.station
   inner join map m on st.id = m.station
where p.id like 'MV%'
order by m.channel, p.title, p.subtitle, s.time, st.callSign

-- episode list
select distinct id, title, subtitle
from program
where id like 'EP%'
order by title, subtitle


[rmeden]

I suggest you edit the first post and include the latest version (so the latest code is already at the top for new folks). Discussion can continue of course.

I moved it to it's own (sticky) thread to get out of the business of always adding things to the other, locked thread. I think it will be more scalable.

Robert

[Amy]

Hai,

I'm new member here.
Currently working with c# and have little experience on it but eager to learn more

I have download the sample, and tested it.
I just suppose to change my username/password rite? It worked.
But is there another way to get the xml file from scheduledirect?
I means, like other EPGProvider site they just using exact link to retrieve the file.

p/s: I never use wdsl before.