Asp.net blogs

Saturday 26 July 2014

Find Column Name,Text from Tables In A Sql Server Database

Below Stored Procedure is used "To Find Column Name,Text from Tables In A Sql Server Database" : Just copy Paste the below query in query window of Sql Server Database, execute it and then pass the text , execute it the search result will shown :


Create PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Monday 12 May 2014

Apple Push Notification On IOS,IPhone Using C#

 Working Example Push Notification On IOS Uing C#

using JdSoft.Apple.Apns.Notifications;
using Newtonsoft.Json.Linq;


public void Ios_Push_Notification(String SenderName, Int32 SenderID, Int32 ReceiverID, String  TextMessage)
    {
        bool sandbox = true;

        //Put your device token in here

        string testDeviceToken = "3b717cfe826825daa6ee3d8756de8a101ba5f46d5de35d3730748dbac976d2uy";

        //Put your PKCS12 .p12 or .pfx filename here.
        // Assumes it is in the same directory as your app
        string p12File = Server.MapPath("~/Key.p12");

        //This is the password that you protected your p12File
        // If you did not use a password, set it as null or an empty string
        string p12FilePassword = "123";

        //Number of notifications to send
        int count = 1;

        //Number of milliseconds to wait in between sending notifications in the loop
        // This is just to demonstrate that the APNS connection stays alive between messages
        int sleepBetweenNotifications = 3000;

        string p12Filename = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, p12File);

        NotificationService service = new NotificationService(sandbox, p12Filename, p12FilePassword, 1);

        service.SendRetries = 5; //5 retries before generating notificationfailed event
        service.ReconnectDelay = 5000; //5 seconds

        service.Error += new NotificationService.OnError(service_Error);
        service.NotificationTooLong += new NotificationService.OnNotificationTooLong(service_NotificationTooLong);

        service.BadDeviceToken += new NotificationService.OnBadDeviceToken(service_BadDeviceToken);
        service.NotificationFailed += new NotificationService.OnNotificationFailed(service_NotificationFailed);
        service.NotificationSuccess += new NotificationService.OnNotificationSuccess(service_NotificationSuccess);
        service.Connecting += new NotificationService.OnConnecting(service_Connecting);
        service.Connected += new NotificationService.OnConnected(service_Connected);
        service.Disconnected += new NotificationService.OnDisconnected(service_Disconnected);


        Dictionary<string, object> rows = new Dictionary<string, object>();
        JavaScriptSerializer objSerializable = new JavaScriptSerializer();
        List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
        Dictionary<string, object> row = new Dictionary<string, object>();


        rows.Add("Result", "Success");
        rows.Add("SenderName", SenderName);
        rows.Add("SenderID", SenderID);
        rows.Add("ReceiverID", ReceiverID);
        rows.Add("TextMessage", TextMessage);

        String postDatas = objSerializable.Serialize(rows);

        for (int i = 0; i < count; i++)
        {
            //Create a new notification to send
            Notification alertNotification = new Notification(testDeviceToken);

            alertNotification.Payload.Alert.Body = postDatas;
            alertNotification.Payload.Sound = "default";
            //Queue the notification to be sent
            if (service.QueueNotification(alertNotification))
                Console.WriteLine("Notification Queued!");
            else
                Console.WriteLine("Notification Failed to be Queued!");

            //Sleep in between each message
            if (i < count)
            {
                Console.WriteLine("Sleeping " + sleepBetweenNotifications + " milliseconds before next Notification...");
                System.Threading.Thread.Sleep(sleepBetweenNotifications);
            }
        }

        Console.WriteLine("Cleaning Up...");

        //First, close the service.
        //This ensures any queued notifications get sent befor the connections are closed
        service.Close();

        //Clean up
        service.Dispose();

        Console.WriteLine("Done!");
        Console.WriteLine("Press enter to exit...");
        Console.ReadLine();
    }

    static void service_BadDeviceToken(object sender, BadDeviceTokenException ex)
    {
        Console.WriteLine("Bad Device Token: {0}", ex.Message);
    }

    static void service_Disconnected(object sender)
    {
        Console.WriteLine("Disconnected...");
    }

    static void service_Connected(object sender)
    {
        Console.WriteLine("Connected...");
    }

    static void service_Connecting(object sender)
    {
        Console.WriteLine("Connecting...");
    }

    static void service_NotificationTooLong(object sender, NotificationLengthException ex)
    {
        Console.WriteLine(string.Format("Notification Too Long: {0}", ex.Notification.ToString()));
    }

    static void service_NotificationSuccess(object sender, Notification notification)
    {
        Console.WriteLine(string.Format("Notification Success: {0}", notification.ToString()));
    }

    static void service_NotificationFailed(object sender, Notification notification)
    {
        Console.WriteLine(string.Format("Notification Failed: {0}", notification.ToString()));
    }

    static void service_Error(object sender, Exception ex)
    {
        Console.WriteLine(string.Format("Error: {0}", ex.Message));
    }

Push Notification On Android Using C#

Working Example to push notification on Android using C# 

using JdSoft.Apple.Apns.Notifications;
using Newtonsoft.Json.Linq;

 string regId =   "APA91bEFbLOkP56DfLtUeXD1xiap54BDxQa1iP321W_-                                                                                                          QnRSYEenap8uz8lk8hkieEHRMH4rr6cAN-XE14IO2rx4IEZiN0xQ4Yf39aTw63zHM6swbp8zfwB_MEF6fTPTKEwxhtcpUfdc1Xhik6u6gQZIz6pULifQ";

        var applicationID = "AIzaSyDK7e2fDNymJCl8toInDPmPTlUsSKE1wz0";
        var SENDER_ID = "572165304164";
        var value = "TextMessage";
        WebRequest tRequest;
        tRequest = WebRequest.Create("https://android.googleapis.com/gcm/send");
        tRequest.Method = "post";
        tRequest.ContentType = " application/x-www-form-urlencoded;charset=UTF-8";
        tRequest.Headers.Add(string.Format("Authorization: key={0}", applicationID));

        tRequest.Headers.Add(string.Format("Sender: id={0}", SENDER_ID));

        Dictionary<string, object> rows = new Dictionary<string, object>();
        JavaScriptSerializer objSerializable = new JavaScriptSerializer();
        List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
        Dictionary<string, object> row = new Dictionary<string, object>();


        rows.Add("Result", "Success");
        rows.Add("SenderName", SenderName);
        rows.Add("SenderID", SenderID);
        rows.Add("ReceiverID", ReceiverID);
        rows.Add("TextMessage", TextMessage);



        //Context.Response.Write(objSerializable.Serialize(rows));

        String postDatas = objSerializable.Serialize(rows);



        //Data_Post Format
        //string postDatas = "{'data': {'SenderName':'" + SenderName + "','SenderID': '" + SenderID + "','ReceiverID':'" + ReceiverID + "','TextMessage':'" + TextMessage + "'}}";
        //string postDatas = "{" + "data:" + "{" + "SenderName:" + SenderName + "','SenderID': '" + SenderID + "','ReceiverID':'" + ReceiverID + "','TextMessage':'" + TextMessage + "'}}";


        string postData = "collapse_key=score_update&time_to_live=108&delay_while_idle=1&data.message="
          + postDatas + "&data.time=" + System.DateTime.Now.ToString() + "&registration_id=" + regId + "";


        Console.WriteLine(postData);
        Byte[] byteArray = Encoding.UTF8.GetBytes(postData);
        tRequest.ContentLength = byteArray.Length;

        Stream dataStream = tRequest.GetRequestStream();
        dataStream.Write(byteArray, 0, byteArray.Length);
        dataStream.Close();

        WebResponse tResponse = tRequest.GetResponse();

        dataStream = tResponse.GetResponseStream();

        StreamReader tReader = new StreamReader(dataStream);

        String sResponseFromServer = tReader.ReadToEnd();

        //  Label3.Text = sResponseFromServer; //printing response from GCM server.
        tReader.Close();
        dataStream.Close();
        tResponse.Close();

Friday 7 March 2014

Query to generate comma-separated list from one individual column data from database table in sql server using COALESCE()

Table Name > tbmap
                    Fields >  Place
                                    Data > Amritsar
                                           Bhutan
                                           Kerala
                                          

Query to generate comma-separated list from one individual column data
from database table in sql server using COALESCE()


Query >

 DECLARE @List VARCHAR(8000)

SELECT @List = COALESCE(@List + ',', '') + CAST(Place AS VARCHAR)
FROM   tbmap
where Place='PLACENAME'
SELECT @List as Place

Output :
Amritsar,Bhutan,Kerala

Query to generate comma-separated list from one individual column data from database table in sql server

Table Name > EmployeeDetails
           Fields >  EmployeeName
             Data > Rohin
                          Rajat
                          Rahul
                          Ishan

Query to generate comma-separated list from one individual column data from database table in sql server

Query >
SELECT STUFF((SELECT ',' + [EmployeeName]
              FROM @EmployeeDetails
              ORDER BY [EmployeeName]
              FOR XML PATH('')), 1, 1, '') AS [EmployeeName]

Output
Ishan,Rahul,Rajat,Rohin