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