Archive

Posts Tagged ‘SQL’

[Powershell] Executing an sql query and returning a disconnected data using Powershell

April 17, 2012 Leave a comment

Below is a script that will execute an sql query and returns a disconnected data (DataTable) using Powershell.

<#
.SYNOPSIS
	Queries a data source and returns a disconnected data (DataTable)

.DESCRIPTION
	Queries a data source and returns a disconnected data (DataTable)

.NOTES
	File Name: Get-DbDataTable.ps1
	Author: Randy Aldrich Paulo

.EXAMPLE
	Get-DbDataTable "Data Source=.;Integrated Security=SSPI;Initial Catalog=DatabaseName" "SELECT TOP 10 * From Table1"
#>

function Get-DbDataTable
(
	[string]$connectionString,
	[string]$query,
	[System.Data.CommandType]$commandType=[System.Data.CommandType]::Text,
	[string]$providerName = "System.Data.SqlClient"	
)
{
	#Initialize
	$databaseFactory = [System.Data.Common.DbProviderFactories]::GetFactory($providerName)
	$dbConnection = $databaseFactory.CreateConnection()
	$dbConnection.ConnectionString = $connectionString

	#Create Command
	$dbCommand = $databaseFactory.CreateCommand()
	$dbCommand.Connection = $dbConnection
	$dbCommand.CommandText =$query
	$dbCommand.CommandType = $commandType

	#Create Data Adapter
	$dbAdapter = $databaseFactory.CreateDataAdapter();
	$dbAdapter.SelectCommand = $dbCommand

	$dataTable = New-Object System.Data.DataTable

	try
	{
		#Fill with Results
		$dbAdapter.Fill($dataTable)
	}
	catch { 
		throw 
		}
	finally { 
			if($dbConnection.ConnectionState -ne [System.Data.ConnectionState]::Closed) 
			{ 
				$dbConnection.Close() 
			}
		}
	return $dataTable
}
Advertisements

SSRS – Calling Reporting Services using WCF and using the HTML output for email notification

October 27, 2011 6 comments

Recently we had a project that requires us to use Reporting Server for email notification. In short,  the HTML output of the Report is assigned to the body of the email notification.

1) First step is to create a proxy library utility containing reference to Reporting Server Web Service. To do this Click Add Service Reference and set url to:

 http://{ReportServerName}/ReportServer/ReportExecution2005.asmx, In the collection type set it to System.Collections.Generic.List

Note: Version of SSRS is 2005

Update!: Problem with Authorization:

“The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’.”

To fix problem with Authorization issue in LoadReport method:

1. Added new method called GetDefaultBinding.

2.Also a new parameter in Export method named  useDefaultBinding with value set to false was added. If this value is set to true, it would use the binding from GetDefaultBinding and will use DefaultNetworkCredentials.

Note: Network credential is not being used when useDefaultBinding is set to true.

Updated code is as follows:

  /// <summary>
    /// Utility class that renders and exports a SQL Reporting Services report into the specified output format.
    /// </summary>
    public static class ReportExporter
    {
        private static string GetExportFormat(ExportFormat f)
        {
            switch (f)
            {
                case ExportFormat.XML: return "XML";
                case ExportFormat.CSV: return "CSV";
                case ExportFormat.Image: return "IMAGE";
                case ExportFormat.PDF: return "PDF";
                case ExportFormat.MHTML: return "MHTML";
                case ExportFormat.HTML4: return "HTML4.0";
                case ExportFormat.HTML32: return "HTML3.2";
                case ExportFormat.Excel: return "EXCEL";
                case ExportFormat.Word: return "WORD";

                default:
                    return "PDF";
            }
        }

        /// <summary>
        /// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified.
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="format"></param>
        /// <param name="output">rendering output result in bytes</param>
        /// <param name="extension">output format file extension</param>
        /// <param name="mimeType">output MIME type</param>
        /// <param name="encoding">output encoding</param>
        /// <param name="warnings">warnings (if any)</param>
        /// <param name="streamIds">stream identifiers for external resources (images, etc) that are associated with a given report</param>
        /// <param name="dataSourceCredentials"></param>
        public static void Export(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters,
            ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds, DataSourceCredentials[] dataSourceCredentials=null, bool UseDefaultBinding=false)
        {

            Binding binding;
            if (UseDefaultBinding)
            {
                binding = GetDefaultBinding();
            }
            else
            {
                binding = GetBinding();
            }

            using (var webServiceProxy = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(url)))
            {
                webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                if (UseDefaultBinding)
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
                }
                else
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials;
                }

                // Init Report to execute
                ServerInfoHeader serverInfoHeader;
                ExecutionInfo executionInfo;

                try
                {

                    ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo);

                    // Attach Report Parameters
                    webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);

                    //Set Credentials
                    if(dataSourceCredentials!=null)
                        webServiceProxy.SetExecutionCredentials(executionHeader, null, dataSourceCredentials, out executionInfo);

                    // Render
                    webServiceProxy.Render(executionHeader, null, GetExportFormat(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds);
                }
                catch (FaultException faultExc)
                {
                    throw new Exception(string.Format("Encountered error while generating the report: '{0}', Report Url: '{1}', Web Service Url: '{2}' Message: '{3}'",  reportName, report, url, faultExc.Message ), faultExc);
                }
            }
        }

        /// <summary>
        /// Export a Reporting Services to HTML
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="dataSourceCredentials"></param>
        /// <returns></returns>
        public static string ExportToHTML(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, DataSourceCredentials[] dataSourceCredentials = null, bool UseDefaultBinding = false)
        {
            Warning[] warn;
            string[] p;

            byte[] output;
            string extension, mimeType, encoding;

            //Get HTML
            ReportExporter.Export(url,
                                  clientCredentials,
                                  reportName,
                                  report,
                                  parameters.ToArray(),
                                  ExportFormat.HTML4,
                                  out output,
                                    out extension,
                                    out mimeType,
                                    out encoding, out warn, out p, dataSourceCredentials, UseDefaultBinding
                                    );

            System.Text.Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
            string html = enc.GetString(output);
            return html;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
              {
                  MaxArrayLength = 2147483647,
                  MaxBytesPerRead = 2147483647,
                  MaxDepth = 2147483647,
                  MaxNameTableCharCount = 2147483647,
                  MaxStringContentLength = 2147483647
              };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
            return binding;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetDefaultBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
            {
                MaxArrayLength = 2147483647,
                MaxBytesPerRead = 2147483647,
                MaxDepth = 2147483647,
                MaxNameTableCharCount = 2147483647,
                MaxStringContentLength = 2147483647
            };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
            return binding;
        }
        #endregion
    }

Updated Unit Test to use default bindings:

[TestMethod]
        public void GetHTMLReportTest()
        {

            //Set network credentials
            string userName = "NotUsed";
            string password = "NotUsed";
            string domain = "NotUsed";
            System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(userName, password, domain);
            string outputPath = @"C:\TestFolder";

            //Url
            string path = "/MyReport/Invoice";

            //Parameters
            List<ParameterValue> parameters = new List<ParameterValue>();
            parameters.Add(new ParameterValue { Name = "AcctId", Value = "0001" });

            //Get HTML
            string html = ReportExporter.ExportToHTML("http://HypV-Rpaulo/ReportServer/ReportExecution2005.asmx",
                                  credentials,
                                  "My invoice report",
                                  path ,
                                  parameters.ToArray(),UseDefaultBinding:true
                                  );

            Assert.AreEqual(true, html.Length > 0, "No HTML generated");
            Console.WriteLine(html);
            System.IO.File.WriteAllText(outputPath + "result.html", html);

        }

The library can be used in BizTalk (deployed in GAC) , SSIS 2005 or 2008(deployed in GAC, .NET version is <=3.5 and is copied to %Windir%\Microsoft.NET\Framework\v2.0.50727 folder to be able to referenced in a SSIS Package..

BizTalk WCF-SQL Adapter MSDTC Issue

September 27, 2011 Leave a comment

This was imported from my old blog dated  July 3, 2009.

I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we’ve tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues.

To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I’m configuring the mySAP adapter, I found out that license has expired, since we’ve downloaded the trial version of BizTalk adapter pack 1.0. So I have to look for another setup, it so happen that for some reason I can’t find the 1.0 version that’s why I’ve installed the Adapter pack 2.0. I was a little bit surprise to see that there’s a new SQL Adapter included. So I’ve installed it and give it a try and it works fine.

Two important sql binding settings that needs to be set are:

1) useAmbientTransaction = false, to solve MSDTC issue.

2) allowIdentityInsert = true, if you’re trying to add rows to a table that has identity column.

 

SQL Login not working in SQL 2008R2/2005

August 19, 2011 1 comment

From time to time I see this problem reoccurring, suppose you correctly setup an SQL Login for a certain account and when you tried to login it throws: Login failed for ‘RPAULO_WebUser’ (Microsoft SQL Server, Error: 18456) you might want to check whether the sql server supports mixed mode by going to SQL Server Properties of the server (connect via RDP).

Note that updating the settings would require a service restart to take effect.

Storing files to mssql database and downloading using Asp.NET

June 21, 2011 2 comments

1) First create a new column and set it as varbinary(MAX) for the datatype.

2) Use this code to store the file to database:

            //Read File to Bytes
                    FileStream st = new FileStream(<Location of the file>, FileMode.Open);
                    byte[] fileData= new byte[st.Length];
                    st.Read(fileData, 0, (int)st.Length);
                    st.Close();

                    SqlParameter[] param = new SqlParameter[] { new SqlParameter(“@ID”,<Reference ID>),
                                                    new SqlParameter(“@Data”,fileData)  };

            int i = SqlAccessor.ExecuteNonQuery(<ConnectionString>, SqlAccessor.SqlCommandBuilder(new SqlCommand (<Stored Procedure Name>), param), CommandType.StoredProcedure, out retVal);

 I’ve used Application Blocks to simplify data operation.

To Retrieve the data and to be downloaded from an ASP.NET Page:

3)  I created a function that returns a byte array with ID as parameter

 public static byte[] GetFileFromDB(string id)
    {
        object[] retVal = null;
        byte[] file = null;
        SqlParameter[] param = new SqlParameter[]
        {
            new SqlParameter (“@id”, id)
        };
        string _connString = ConfigurationManager.ConnectionStrings[<ConnectionStringName>].ConnectionString.ToString();
        SqlDataReader reader = SqlAccessor.ExecuteReader(_connString, SqlAccessor.SqlCommandBuilder(new SqlCommand(“SourceFileLogSelFileDataProc”), param), CommandType.StoredProcedure, out retVal);

        if (reader.Read())
        {
            file = (byte[])reader[“<varbinary column that we define in Step 1>“];
        }

        return file;
    }

4) And on the ASP.NET Page itself, on the Page_Load event:

           byte[] fileData = Utils.GetFileFromDB(<ID>);
            Response.ClearContent();
            Response.AddHeader(“Content-Disposition”, “attachment; filename=” + <filename>);
            BinaryWriter bw = new BinaryWriter(Response.OutputStream);
            bw.Write(fileData);
            bw.Close();
            Response.ContentType = ReturnExtension(<file extension>);
            Response.End();

 5) I have another method to determine the ContentType:

  private string ReturnExtension(string fileExtension)
    {
        switch (fileExtension)
        {
            case “.htm”:
            case “.html”:
            case “.log”:
                return “text/HTML”;
            case “.txt”:
                return “text/plain”;
            case “.doc”:
                return “application/ms-word”;
            case “.tiff”:
            case “.tif”:
                return “image/tiff”;
            case “.asf”:
                return “video/x-ms-asf”;
            case “.avi”:
                return “video/avi”;
            case “.zip”:
                return “application/zip”;
            case “.xls”:
            case “.csv”:
                return “application/vnd.ms-excel”;
            case “.gif”:
                return “image/gif”;
            case “.jpg”:
            case “jpeg”:
                return “image/jpeg”;
            case “.bmp”:
                return “image/bmp”;
            case “.wav”:
                return “audio/wav”;
            case “.mp3”:
                return “audio/mpeg3”;
            case “.mpg”:
            case “mpeg”:
                return “video/mpeg”;
            case “.rtf”:
                return “application/rtf”;
            case “.asp”:
                return “text/asp”;
            case “.pdf”:
                return “application/pdf”;
            case “.fdf”:
                return “application/vnd.fdf”;
            case “.ppt”:
                return “application/mspowerpoint”;
            case “.dwg”:
                return “image/vnd.dwg”;
            case “.msg”:
                return “application/msoutlook”;
            case “.xml”:
            case “.sdxl”:
                return “application/xml”;
            case “.xdp”:
                return “application/vnd.adobe.xdp+xml”;
            default:
                return “application/octet-stream”;
        }
    }

Hope this helps.

Categories: .NET Development Tags: ,
%d bloggers like this: