How To Create Users To Connect To An Azure SQL Server Database



 TODO:

 Have you ever wanted to connect to a SQL Azure Database from .Net and get a Access Denied error?  The issue is you need to create a user to use in your connection strings.

 

SOLUTION:

--run on master DB
CREATE LOGIN myuser WITH password='mypassword';

--run on Application DB
CREATE USER myuser FROM LOGIN myuser;
exec sp_addrolemember db_datareader, myuser 
go
exec sp_addrolemember db_datawriter, myuser

 

NOTES:

You cannot use the 'use' keyword, so  you need to open a new query window for each database.  Once complete, use this user / pass in your code and you can connect to the DB.

How To Delete The Last X Records From A Table By Group



TODO:

Have you ever wanted to keep the last X records for a Customer, Employee, Order, etc?

 

SOLUTION:

DELETE c FROM (
  SELECT *, rn=row_number() OVER (PARTITION BY CustomerId ORDER BY Id desc)
  FROM CustomerHistory
) c
WHERE rn > 3

 

NOTES:

The SQL above will keep the last 3 records in the table CustomerHistory.  The Grouping is by CustomerId, and Id is an Identity column, which ensures we keep the last 3 records (order by desc).  If you want to test it, just execute the statement as a select (Change DELETE c to SELECT CustomerId, Id, rn

How To Change The Encryption Password On A Database Master Key



TODO:

Have you ever wanted to change the password associated with your Database Master Key?

 

SOLUTION:

use my database
GO
--step 1, open the key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my password'
GO

--step 2, regenerate it with new password
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD 'new password'
GO

 

NOTES:

There are no notes on this topic.

How To Restore A SQL Server Database Master Key On A New Server



TODO:

Have you ever wanted to backup your database master key and move it to a new server?

 

SOLUTION:

--Step 1 -- Backup file
OPEN MASTER KEY DECRYPTION BY PASSWORD 'my password'
     BACKUP MASTER KEY TO FILE = 'c:\myfile' ENCRYPTION BY PASSWORD ='mypassword'

--Step 2 -- Restore file
USE myDatabase
GO
RESTORE MASTER KEY FROM FILE 'my file'

     DECRYPTION BY PASSWORD = 'mypassword'
     ENCRYPTION BY PASSWORD = 'mypassword'
     FORCE;
GO
 

NOTES:

You may not need the 'FORCE' option, read up on what it does and decide accordingly.

How To Install CSF On A GoDaddy VPS Running CentOS 6



TODO:

You are running a VPS server at GoDaddy and need to set up a FireWall.

 

SOLUTION:

Follow the directions at HERE to get it to work.

 

NOTES:

I tried a few articles, and none worked.  This article worked like a snap.

How To Read The Base64 Encoded Xml Of A WCF Message



TODO:

Have you ever wanted to get the Base64 encoded Xml of your WCF message?

 

SOLUTION:

public object AfterReceiveRequest(ref Message request, IClientChannel channel, InstanceContext instanceContext)
{
      try
      {
           //create a copy for use
           MessageBuffer buffer = request.CreateBufferedCopy(Int32.MaxValue);
           Message newMessage = buffer.CreateMessage();
          
           //assign original so it can be used again
           request = buffer.CreateMessage();
           string messageBody = string.Empty;

           //read the message
           using (XmlDictionaryReader bodyReader = newMessage.GetReaderAtBodyContents() 
           {
                bodyReader.ReadStartElement("Binary");
                byte[] bodyBytes = bodyReader.ReadContentAsBase64();
                messageBody = Encoding.UTF8.GetString(bodyBytes);
           }

           //....do your other stuff here, the messageBody will now contain your text.
      }
      catch (Exception x)
      {
            return null;
      }
}

 

NOTES:

There are no notes on this topic.

How To List All Columns In All User Tables In A Sql Server Database



TODO:

Have you ever wanted to list all columns in all User tables that exist in a SQL Server Database?

 

SOLUTION:

SELECT so.name "table", sc.name "column", sm.text "Default" 
FROM dbo.sysobjects so INNER JOIN dbo.syscolumns sc ON so.id = sc.id 
LEFT JOIN dbo.syscomments sm ON sc.cdefault = sm.id  
WHERE so.xtype = 'U'  
ORDER BY so.[name], sc.colid

 

NOTES:

There are no notes on this topic

How To Display A Confirmation Dialog And Cancel The Postback When Using A Telerik RadButton



TODO:

You are using a Telerik RadButton, and you want to display a Confirm Dialog, and capture the user pressing cancel, thus canceling the Postback.

 

SOLUTION:

    <!-- Define button, and put method to call in the OnClientClicking event -->

    <telerik:RadButtonID="btnDelete"runat="server"Text="Delete Customer"OnClick="btnDelete_Click"

        OnClientClicking="ConfirmDelete">

    </telerik:RadButton>

   

    <!-- Confirm action here.  Cancel press in this dialog will return FALSE, which in turn we will catch and set cancel = true in the args -->

    <telerik:RadCodeBlockID="RadCodeBlock1"runat="server">

        <scripttype="text/javascript">

            function ConfirmDelete(sender, args) {

                if (!confirm('Delete Customer, do you wish to proceed?')) {

                    args.set_cancel(true);

                }

            }

        </script>

 

    </telerik:RadCodeBlock>

 

NOTES:

You may not need the RadCodeBlock, I did in this case due to Application

How To Force WCF Service To Use XmlSerializer Instead Of The DataContractSerializer



TODO:

Have you ever wanted to return Attributes, or have further control over serialization when using WCF?  By default, it uses the DataContractSerializer, but you can tell it to use the XmlSerializer.  

 

SOLUTION:

1.  Ensure you have your interface marked with [ServiceContract]

2.  Add XmlSerializerFormat to your method

 [ServiceContract]

 publicinterfaceIMyService


 [WebGet(UriTemplate = "employee")]

 [OperationContract]

 [XmlSerializerFormat]

 Response GetEmployees();

 

Now your WCF call will use the XmlSerializer rather than the DataContractSerializer.


NOTES:

There are no notes on this topic.

WebInvoke Verbs To Use With Restful WCF Service



TODO:

Have you ever wanted to know which verbs to use when defining your OperationContract in a WCF service?

 

SOLUTION:

// Get an employee by ID - GET - employee/id (  [WebGet(UriTemplate = "/employee/{id}")] )

// Create a new employee - POST - employee  [WebInvoke(Method = "POST", UriTemplate = "/employee/{xml}")] )

// Update an employee - PUT - employee/id  [WebInvoke(Method = "PUT", UriTemplate = "/employee/{xml}")]

// Delete an employee - DELETE - employee/id  [WebInvoke(Method = "DELETE", UriTemplate = "/employee/{id}")]

 

NOTES:

There are no notes on this topic