Category / Section
How to save and load report from a SQL Server Database
1 min read
We can able to save and load the report set into a SQL server database. The reports has been saved and loaded through stream by getting the report stream/string using XML serialization from the report collection. Then that stream has been saved into database using WCF Service. Please follow the steps below to achieve the same.
Steps to Create a SQL Service for connecting to database
- Create a Silverlight Project along with Web application.
- Add the OlapCient control and two buttons as “Save report to DataBase” and “LoadReport from Database” in Mainpage.xaml
- Create a WCF service as “OlapManager.svc” and inherit the IOlapDataProvider interface.
- Explicitly implement the IOlapDataProvider. The connection to the cube database is done with the help of this WCF service.
- For connecting to the SQL server database, create another WCF Service namely “ReportService.svc” and inherit the IReportService interface.
- The IReportService interface contains the following codes.
C#
[ServiceContract]
public interface IReportService
{
[OperationContract]
byte[] GetReportContent(string reportId);
[OperationContract]
bool SaveReportContent(string reportId, byte[] reportContent);
[OperationContract]
List<string> LoadReportIds();
}
- Add the following codes in ReportService.svc which helps to connect with the Database for saving and loading the report stream
C#
public class ReportService : IReportService
{
#region IReportService Members
public byte[] GetReportContent(string reportId)
{
byte[] reportContent = null;
Database1Entities entities = new Database1Entities();
foreach (var item in entities.OlapReports)
{
if (item.ReportId.Equals(reportId))
{
reportContent = item.ReportContent;
break;
}
}
return reportContent;
}
public bool SaveReportContent(string reportId, byte[] reportContent)
{
List<string> listofReportId = LoadReportIds();
foreach (var item in listofReportId)
{
if (item.Equals(reportId))
{
return false;
}
}
Database1Entities entities = new Database1Entities();
entities.AddToOlapReports(new OlapReport { ReportContent = reportContent, ReportId = reportId });
int solution = entities.SaveChanges();
if (solution > 0)
{
return true;
}
return false;
}
public List<string> LoadReportIds()
{
List<string> listOfReportId = new List<string>();
Database1Entities entities = new Database1Entities();
foreach (var item in entities.OlapReports)
{
listOfReportId.Add(item.ReportId);
}
return listOfReportId;
}
#endregion
}
- Add the ServiceReferences for the ReportService.svc in Silverlight Project.
- Create a Silverlight ChildWindow as “ReportNameLoaderChild” and add the following code in the design page.
XAML
<controls:ChildWindow x:Class="Silverlight.OlapClient.ReportNameLoaderChild" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls" Width="400" Height="120" Title="Report Name Getter"> <Grid x:Name="LayoutRoot" Margin="2"> <Grid.RowDefinitions> <RowDefinition /> <RowDefinition Height="Auto" /> </Grid.RowDefinitions> <TextBlock Text="Report Name:" Margin="10,10" VerticalAlignment="Center" /> <ComboBox x:Name="reportName" Margin="100,10,50,0" VerticalAlignment="Top" /> <Button x:Name="CancelButton" Content="Cancel" Click="CancelButton_Click" Width="75" Height="23" HorizontalAlignment="Right" Margin="0,12,0,0" Grid.Row="1" /> <Button x:Name="OKButton" Content="OK" Click="OKButton_Click" Width="75" Height="23" HorizontalAlignment="Right" Margin="0,12,79,0" Grid.Row="1" /> </Grid> </controls:ChildWindow>
Figure: “ReportNameLoaderChild” child window
- Initialize the connection with the OlapManager service and assigning the OlapDataManager to OlapClient.
- Add the following code in the Click events of Save and load buttons.
C#
public MainPage()
{
InitializeComponent();
InitializeConnection();
InitializeReportService();
}
#endregion
private void InitializeReportService()
{
_reportServiceProxy = new ReportServiceClient();
}
private void Btn_Click(object sender, RoutedEventArgs e)
{
Button btn = sender as Button;
if (btn.Name.Equals("Save"))
{
byte[] tempBuffer = null;
TextWriter strWriter = null;
try
{
OlapReportCollection reportCollection = new OlapReportCollection();
foreach (var item in this.OlapClient.OlapDataManager.ReportList)
{
reportCollection.Add(item);
}
strWriter = new StringWriter();
//Getting report stream/string through XmlSerializer
XmlSerializer xmlSerializer = new XmlSerializer(reportCollection.GetType());
System.Xml.Serialization.XmlSerializerNamespaces xs = new XmlSerializerNamespaces();
xs.Add(string.Empty, string.Empty);
xmlSerializer.Serialize(strWriter, reportCollection, xs);
Encoding encoding = new UTF8Encoding(true, true);
tempBuffer = encoding.GetBytes(strWriter.ToString());
string reportName = this.OlapClient.OlapDataManager.CurrentReport.Name;
_reportServiceProxy.SaveReportContentAsync(reportName, tempBuffer);
//Save the report in Database
_reportServiceProxy.SaveReportContentCompleted += new EventHandler<SaveReportContentCompletedEventArgs>(_reportServiceProxy_SaveReportContentCompleted);
}
catch (Exception ex)
{
MessageBox.Show("Error while save the report");
}
finally
{
if (strWriter != null)
{
strWriter.Close();
}
}
}
else if (btn.Name.Equals("Load"))
{
_childWindow = new ReportNameLoaderChild();
//Load the report into olpaclient
_childWindow.Loaded += new RoutedEventHandler(ChildWindow_Loaded);
_childWindow.Closed += new EventHandler(ChildWindow_Closed);
_childWindow.Show();
}
}
void ChildWindow_Loaded(object sender, RoutedEventArgs e)
{
_reportServiceProxy.LoadReportIdsAsync();
//load the report fromt he database
_reportServiceProxy.LoadReportIdsCompleted += new EventHandler<LoadReportIdsCompletedEventArgs>(_reportServiceProxy_LoadReportIdsCompleted);
}
void _reportServiceProxy_LoadReportIdsCompleted(object sender, LoadReportIdsCompletedEventArgs e)
{
if (e.Error == null)
{
_childWindow.reportName.ItemsSource = e.Result;
}
}
void ChildWindow_Closed(object sender, EventArgs e)
{
ReportNameLoaderChild childWindow = sender as ReportNameLoaderChild;
if (childWindow.DialogResult.HasValue && childWindow.DialogResult == true)
{
_reportServiceProxy.GetReportContentAsync(_childWindow.reportName.Items[_childWindow.reportName.SelectedIndex].ToString().Trim());
_reportServiceProxy.GetReportContentCompleted += new EventHandler<GetReportContentCompletedEventArgs>(_reportServiceProxy_GetReportContentCompleted);
}
}
void _reportServiceProxy_GetReportContentCompleted(object sender, GetReportContentCompletedEventArgs e)
{
if (e.Error == null)
{
TextReader strReader = null;
try
{
byte[] reportBytes = e.Result;
Encoding encoding = new UTF8Encoding(true, true);
string reportString = encoding.GetString(reportBytes, 0, reportBytes.Length);
reportString = reportString.Replace('\0',' ');
strReader = new StringReader(reportString);
OlapReportCollection reportCollection = new OlapReportCollection();
XmlSerializer xmlSerializer = new XmlSerializer(reportCollection.GetType());
reportCollection = xmlSerializer.Deserialize(strReader) as OlapReportCollection;
this.OlapClient.OlapDataManager.ReportList = reportCollection;
if (this.OlapClient.OlapDataManager.ReportList.Count > 0)
{
this.OlapClient.OlapDataManager.CurrentReport = this.OlapClient.OlapDataManager.ReportList[0];
this.OlapClient.OlapDataManager.IsCurrentReportModified = false;
this.OlapClient.ReportList.ItemsSource = this.OlapDataManager.ReportList;
this.OlapClient.ReportList.SelectedIndex = 0;
}
}
catch (Exception ex)
{
MessageBox.Show("Error while report loading");
}
finally
{
if (strReader != null)
{
strReader.Close();
}
}
}
}
void _reportServiceProxy_SaveReportContentCompleted(object sender, SaveReportContentCompletedEventArgs e)
{
if (e.Error == null)
{
if (e.Result)
{
MessageBox.Show("Saved! in database successfully");
}
else
{
MessageBox.Show("Save operation not succeeded");
}
}
}
- The following output has been displayed while running the application.
Figure: OlapClient with Save and Load Buttons
