Calling authenticated WSE web service in Visual Studio 2008.

Hi All,

This time i was given a task to call a web service which was written in older technology WSE and as you all know that its obsolete and replaced by WCF services but still some old written services are to be used.

When i added the service reference in my application it auto generates the configuration file as shown below, but by using this configuration file i was not able to provide credentials to web service call.

<basicHttpBinding>
        <binding name="FsetServiceSoap" closeTimeout="00:01:00" openTimeout="00:01:00"
          receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
          bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
          useDefaultWebProxy="true">

          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
            maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          <security mode="Transport">
            <transport clientCredentialType="None" proxyCredentialType="None"
              realm="">
              <extendedProtectionPolicy policyEnforcement="Never" />
            </transport>
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>


        </binding>
        <binding name="FsetServiceSoap1" closeTimeout="00:01:00" openTimeout="00:01:00"
          receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
          bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
          useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
            maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None" realm="">
              <extendedProtectionPolicy policyEnforcement="Never" />
            </transport>
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>

so i replaced this basichttp binding with custom binding.

<customBinding>

        <binding name="FsetServiceSoap" >

          <textMessageEncoding messageVersion="Soap12WSAddressingAugust2004" />

          <security authenticationMode="UserNameOverTransport"></security>

          <httpsTransport/>

        </binding>

      </customBinding>

and when calling the web service passed the credentials and it worked like charm.

FsetServiceHeader header = new FsetServiceHeader();
FsetServiceSoapClient client = new FsetServiceSoapClient();
client.ClientCredentials.UserName.UserName = _userName;
client.ClientCredentials.UserName.Password = _password;
byte[] rawData = File.ReadAllBytes(_filePath);
TransmissionAcknowledgementType result = client.SendTransmission(header, SendType, rawData);

Hope it helps.

Happy coding.

Regards,
U

Calling OOyala player API using C#.

Hello Friends,

Some or any of you might have used OOyala tool for you projects as its one of the best video management tools i have ever seen.

Some days back i have given a task to get ooyala integrated to our project and i decided to do it via their API but all the samples given on site are eighter in PHP or in Ruby but i want things to be done in c# so by following their documentation i have finally got the response from ooyala site.

Here i am showing you two functions and two properties.

1. _params: i have used sorted dictionary because ooyala demands that all parameters should be in acceding order.

2. Query type: specify which API should be called.

3. send_request(): It does the required encoding of parameters by using the secret and partner code and generated the URL by which XML response can be fetched.

4. getXMLResponse(): Get all XML from URL.

#region Properties

        private string PARTNET_CODE = "PARTNET_CODE";
        private string PARTNET_SECRET = "PARTNET_SECRET";


        private string _queryType = string.Empty;
        public string QueryType
        {
            get
            {
                return _queryType;
            }

            set
            {
                _queryType = value;
            }

        }

        private SortedDictionary<string, string> _params;
        public SortedDictionary<string, string> Params
        {

            get
            {
                return _params;
            }
            set
            {
                _params = value;
            }

        }

        #endregion



        #region Public function



        public string getXMLResponse()
        {


            string sUrl = send_request();
            StringBuilder oBuilder = new StringBuilder();
            StringWriter oStringWriter = new StringWriter(oBuilder);
            XmlTextReader oXmlReader = new XmlTextReader(sUrl);
            XmlTextWriter oXmlWriter = new XmlTextWriter(oStringWriter);
            while (oXmlReader.Read())
            {
                oXmlWriter.WriteNode(oXmlReader, true);
            }
            oXmlReader.Close();
            oXmlWriter.Close(); 

            return oBuilder.ToString();


        }


        /// <summary>
        /// Returns the URL for request parameters 
        /// </summary>
        private string send_request()
        {

            string url = "http://api.ooyala.com/partner/" + QueryType + "?pcode=" + PARTNET_CODE;
            foreach (KeyValuePair<string, string> pair in _params)
            {
                PARTNET_SECRET += pair.Key + "=" + pair.Value;
                url += "&" + HttpUtility.UrlEncodeUnicode(pair.Key) + "=" + HttpUtility.UrlEncodeUnicode(pair.Value);
            }
            SHA256Managed sh = new SHA256Managed();
            byte[] request = System.Text.Encoding.ASCII.GetBytes(PARTNET_SECRET);
            sh.Initialize();
            byte[] b4bbuff = sh.ComputeHash(request, 0, request.Length);
            string hashString = System.Text.Encoding.ASCII.GetString(b4bbuff);

            string encryptedRequest = HttpUtility.UrlEncode((Convert.ToBase64String(b4bbuff)).Substring(0, 43));
            url += "&signature=" + encryptedRequest;

            return url;
        }
#endregion

Once you get the XMl response do what ever you want, Deserilise it and use it in c# code or parse it with jQuery choice is yours.

Hope it helps.

Happy coding.

Regards,
U

Paging Sorting Searching in MySQL Procedure

Hi All,

Lets do some mySQL programming in the last post i have shown how to do custom pagination from jQuery Data table and today i will share the procedure that i called at the back end to do the pagination stuff this one is in mySQL

USE `Database_Name`;

DROP procedure IF EXISTS `up_GlobalSearch`;



DELIMITER $$

USE `Database_Name`$$

CREATE PROCEDURE `scientestdb`.`up_GlobalSearch` (

IN pFreeText varchar(200), 

IN pStartRecord int, 

IN pSize int, 

IN pSortColumn varchar(20),

IN pSortDirection varchar(20)

)

BEGIN





SET @q := concat('select * from Table_Name where MATCH (Column_Name1,Column_Name2) AGAINST (\'', pFreeText ,'\'  IN BOOLEAN MODE)  ORDER By ', pSortColumn, ' ' , pSortDirection, ' limit ?,?');

PREPARE stmt1 FROM @q;

SET @recStrt = pStartRecord;

SET @recCount = pSize;

EXECUTE stmt1 USING @recStrt, @recCount;





END

$$



DELIMITER ;

Hope it helps.

Happy Coding.

Regards,
U

Extending jQuery Datatable with server side processing and pagination.

Hi All,

Once again i came up with a blog for jQuery Datatable as i really liked this control and love to extend its functionality for .NET, in my previous blog i have bound jQuery Data table with dataset at once which might not be very useful then data is so large as fetching complete data in one hit is not a good job.

So Today we will do some bServerSide processing and make this data table more useful in fetching records from database with pagination and sorting enabled i am still not able to coupe with free text search in returned results but ill share that soon as well.

first step would be to create a handler / web service which we would be passing in the argument sAjaxSource of datatable.

Remember that the response should be a JSON formated text as datatable only reads JSON in adata parameter.

Lets start with the Handler.

<%@ WebHandler Language="C#" Class="Search" %>

using System;
using System.Web;
using System.Collections.Generic;
using System.Web.Script.Serialization;
using System.Linq;
using System.Data;
using DAL.Modules;
using DAL;

public class Search : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        // Those parameters are sent by the plugin
        var iDisplayLength = int.Parse(context.Request["iDisplayLength"]);
        var iDisplayStart = int.Parse(context.Request["iDisplayStart"]);
        var iSortCol = int.Parse(context.Request["iSortCol_0"]);
        var iSortDir = context.Request["sSortDir_0"];


        IEnumerable<SearchResults> myResults = SearchResults.GetResults(iDisplayStart, iDisplayLength);

        // Define an order function based on the iSortCol parameter
        Func<SearchResults, object> order = rslt =>
        {
            if (iSortCol == 0)
                return rslt.Project_Name;
            else if (iSortCol == 1)
                return rslt.Project_Goal;
            else if (iSortCol == 2)
                return rslt.Proposed_Budget;
            else if (iSortCol == 3)
                return rslt.Status;
            else
                return rslt.Creation_Date;
        };

        // Define the order direction based on the iSortDir parameter
        if ("desc" == iSortDir)
        {
            myResults = myResults.OrderByDescending(order);
        }
        else
        {
            myResults = myResults.OrderBy(order);
        }


        // Remove Skip and Take when doing Custom Pagination from Database. 
        var result = new
        {
            iTotalRecords = myResults.Count(),
            iTotalDisplayRecords = myResults.Count(),
            aaData = myResults
                .Select(p => new[] { p.Project_Name, p.Project_Goal, p.Proposed_Budget, p.Status, p.Creation_Date, p.View_Details })
                .Skip(iDisplayStart)
                .Take(iDisplayLength)

        };

        var serializer = new JavaScriptSerializer();
        var json = serializer.Serialize(result);
        context.Response.ContentType = "application/json";
        context.Response.Write(json);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

So parsing JSON would require a class structure.

public class SearchResults
{
    public string Project_Name { get; set; }
    public string Project_Goal { get; set; }
    public string Status { get; set; }
    public string Proposed_Budget { get; set; }
    public string Creation_Date { get; set; }
    public string View_Details { get; set; }

    public static IEnumerable<SearchResults> GetResults(int RecordId, int count)
    {
        DataSet ds = null;// Get you Dataset from Database by passing the two parameters for pagination. 

        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                yield return new SearchResults
                {
                    Project_Name = Helper.ParseString(dr["Project_Name"]),
                    Project_Goal = Helper.ParseString(dr["Project_Goal"]),
                    Status = Helper.ParseString(dr["Status"]),
                    Proposed_Budget = Helper.ParseString(dr["Proposed_Budget"]),
                    Creation_Date = Helper.ParseString(DateTime.Now),
                    View_Details = "<a href='#'>View</a>"
                };
            }
    }
}

Now its time for the final step calling it in ASPX page.

the javascript for table would be

<script type="text/javascript">
        $(function () {
            $('#.datatable').dataTable({
                'bServerSide': true,
                "iDisplayLength": 2,
                "sPaginationType": "full_numbers",
                'sAjaxSource': '/Handlers/Search.ashx',
                "bFilter": false
            });
        });
    </script>

now finally we come to the last step which all of you must be familiar of is the HTML table which will turn into the jQuery Datatable.

<table cellpadding="0" cellspacing="0" border="0" class="datatable">
                    <thead>
                        <tr>
                            <th>
                                Project Name
                            </th>
                            <th>
                                Project Goal
                            </th>
                            <th>
                                Proposed Budget
                            </th>
                            <th>
                                Status
                            </th>
                            <th>
                                Creation Date
                            </th>
                            <th>
                            </th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td colspan="5" class="dataTables_empty">
                                <img alt="Please Wait..." src="/images/loader.png" />
                            </td>
                        </tr>
                    </tbody>
                </table>

That's All if you find any query please leave a comment.

Happy Coding.

Get All Days/Months/Years/Quarters with in Two dates.

Hi All,

Today i am feeling soo great as i finally got what i needed the function that i am going to show now will return all the Dates with in two dates but wait its not this simple, i can have periods defined like difference would be in weeks / Months / Years / Quarters and so on....

What it will return in end is Minimum Date of reporting period Maximum date of reporting period and name of that Period.

CREATE FUNCTION [dbo].[GetReportingPeriods] (
  @StartDate DATETIME,
  @EndDate   DATETIME,
  @type varchar(20)
)
RETURNS @A TABLE (
  stdate datetime,eddate datetime, PartName varchar(30)
)
AS
BEGIN
/******************************************************************************
 * Author:      USMAN SHABBIR 
 * Create Date: 2010-11-20
 * Description: Create a table of dates between @StartDate and @EndDate
 *****************************************************************************/
  if @type = 'daily'
 begin 
  ;with cte as 
  ( select CONVERT(DATETIME,@startDate) AS DATE 
  UNION ALL 
  select Dateadd(dd,1,DATE) as date 
  from cte   
  where date <= @endDate - 1 
  )  
 
  insert into @a
  SELECT DATE, DATE + 1 - 1, 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  --Group by 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))
  order by (DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end

 else if @type = 'Weekly'
 begin 
  ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
 
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'Monthly'
  begin 
  
   ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), CAST(DATENAME(month, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by CAST(DATENAME(month, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'quarterly'
  begin 
  
 ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'yearly'
  begin 
  
 ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'year ' + CAST(DatePart(yy, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'year ' + CAST(DatePart(yy, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end

  RETURN;
END

Hope it will help some one the same way as it did to me :)

Happy coding.

Kill all connections of a Database SQL server 2008

Hi All,

I was just trying to restore a DB and it was giving me error of Database in use so can not be restored. I googled a bit and found a nice solution so decided to share with you guys.

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DATABASE_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

It worked like charm hope you enjoy it as well.

Happy Coding.

Simplest way to use JQuery DataTable in ASP.net (C#)

Hi All,

Today i will be using JQuery dataTable in ASP.net c# code without any 'bProcessing' , 'bServerSide' and 'sAjaxSource'.

First of All what is Jquery Datatable, just Click and find out :).

After getting the basic structure of JQuery datatable you may find that all the server side examples on site is in PHP and the stuff done for ASP.net seems so difficult nor such newbies like me. so lets talk about a simpler solution.

First we create a simple HTML table. and the place where data will get populated just add an ASP Literal control. :)

<table class="datatable">
                        <thead>
                            <tr>
                                <th class="bSortable">
                                    <input type="checkbox" class="checkbox select-all" />
                                </th>
                                <th>
                                    First Name
                                </th>
                                <th>
                                    Middle Name
                                </th>
                                <th>
                                    Last Name
                                </th>
                               </tr>
                        </thead>
                        <tbody>
                            <asp:Literal runat="server" ID="ltData"></asp:Literal>
                        </tbody>
                    </table>

Now on the server side write a simple function to populate the literal with required data.

private void loadGrid()
    {
        StringBuilder sb = new StringBuilder();
        DataTable dt = MyData.GetAllUserData();
        if (dt != null)
            if (dt.Rows.Count > 0)
                foreach (DataRow dr in dt.Rows)
                {

                    sb.Append("<tr>");
                    sb.Append("<td>");
                    sb.Append("<input type='checkbox' class='checkbox' />");
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchFirstName"]));
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchMiddleName"]));
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchLastName"]));
                    sb.Append("</td>");
                   
                    sb.Append("</tr>");

                }

        ltData.Text = sb.ToString();

    }

For initializing the data table you can refer to the site or use the following code and write it in document.ready() function of jQuery.

function LoadTable() {
 $('.datatable').dataTable({
  'bLengthChange': true,
  'bPaginate': true,
  'sPaginationType': 'full_numbers',
  'iDisplayLength': 5,
  'bInfo': false,
  'oLanguage': 
  {
   'sSearch': 'Search all columns:',
   'oPaginate': 
   {
    'sNext': '>',
    'sLast': '>>',
    'sFirst': '<<',
    'sPrevious': '<'
   }
  }
 });
}

At this point we are done with the basic grid view with just text data but what if we need to perform some functions like delete a particular row, Just don't worry about it create a webservice and write the deletion function in that service then use the following code to call it using jQuery.

function DeleteMe(string IdToBeDeleted){
var webMethod = 'DeleteRecord.asmx/DeleteUser'
var parameters = "{'ID':'" + IdToBeDeleted + "'}"

$.ajax({
        type: "POST",
        url: webMethod,
        data: parameters,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function(msg) {    
                alert('Success Deletion');
        },
        error: function(e){
                 alert('Failure Deletion');                         
        }
});

and for Datatable just create an other column and call this DeleteMe function with RowId as parameter to it and a nice deletion will occur.

Hope it helps.

Happy coding....