Archive for mssql

mlandi | October 14, 2011
No comments

SQL to Delete All Users from Membership Provider

The .NET Membership Provider provides us with a stored procedure ‘aspnet_Users_DeleteUser’ to delete a user from a particular application. There are problems with this. What if you have a user who is allocated to many applications? What if you need to delete more than one user? I came up with the following script to completely remove all users (except the admin) from all applications.

DECLARE user_cursor CURSOR FOR
SELECT ApplicationName AS field1, Username AS field2, UserId AS field3
FROM aspnet_Users CROSS JOIN dbo.aspnet_Applications
WHERE username != 'admin'

OPEN user_cursor
DECLARE @field1 NVARCHAR(256)
DECLARE @field2 NVARCHAR(256)
DECLARE @field3 UNIQUEIDENTIFIER

FETCH NEXT FROM user_cursor
	INTO @field1, @field2, @field3
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    PRINT @field1 + ':' + @field2
	    DELETE FROM dbo.aspnet_UsersInRoles WHERE UserId = @field3
		EXEC dbo.aspnet_Users_DeleteUser @ApplicationName = @field1,
		    @UserName = @field2,
		    @TablesToDeleteFrom = 50,
		    @NumTablesDeletedFrom = NULL
		DELETE FROM dbo.aspnet_Users WHERE UserId = @field3
		FETCH NEXT FROM user_cursor
		INTO @field1, @field2, @field3
	END
DEALLOCATE user_cursor;

The script works by first cross-joining applications and users, giving us a full list of every user to every application. We then use a cursor to loop the given set, and run our deletes against the set. This saved me countless hours of manual labor.

Category: .net framework, mssql | Tags:
mlandi | March 18, 2011
No comments

SQL Hash Functions

It took me awhile to find how to create MD5 and SHA-1 hashes in SQL Server:

SELECT HASHBYTES(‘MD5’, ‘HASH THIS TEXT’)
SELECT HASHBYTES(‘SHA1’, ‘HASH THIS TEXT’)
Category: mssql | Tags: , ,
mlandi | March 15, 2011
No comments

Compiling LINQ Queries

The performance of LINQ queries can suffer greatly when an application performs the same query multiple times with different parameters. A good example of this situation occurs when your query is inside of a loop as shown below:

foreach (Company company in CompanyList)
{
     var customers = (from c in context.Customers
               where c.CompanyID == company.ID
               select c); 

     Console.WriteLine(customers.Count);
}

Each iteration of the above loop requires that the query be translated into SQL. This process becomes increasingly costly based on the number of iterations and the complexity of the query.

To increase the performance, .NET’s LINQ implementation allows us to preform compiled queries. Compiled queries require the use of a Generic Function whose implementation contains the LINQ query to execute.

private static Func<DBDataContext, Guid, Customer>
     GetCustomers = CompiledQuery.Compile(
     (DBDataContext context, Guid companyID) =>
               from c in context.Customers
               where c.CompanyID== companyID
               select c);

This function will compile the LINQ query only once. Subsequent calls to the function will reuse the existing compiled query. This greatly increases the performance of subsequent queries and avoids excessive recompilation. You can call the procedure using a standard function call:

foreach (Company company in CompanyList)
{
     var customers = GetCustomers(context, company.ID); 

     Console.WriteLine(customers.Count);
}

For more information: http://msdn.microsoft.com/en-us/library/bb399335.aspx

Category: .net framework, c#, linq, mssql |
mlandi | March 8, 2011
No comments

SSIS: ConnectionManager To SqlConnection

I spent a decent amount of time trying to figure out how to get the DTS ConnectionManager to give me a SQL Connection. I used the following code to grab a SQL Connection from the connection manager:

SqlConnection sConnection = (SqlConnection)Dts.Connections[“MYDB”].AcquireConnection(null);

We pass NULL into the AquireConnection method to specify that we will not be using an existing transaction. Once the SQLConnection is instantiated, it is usually in the OPEN state and does not require you to call the Open() method it.

Easy enough. Just remember to ALWAYS use this method with an ADO.NET connection. Using anything else will result in an error in an InvalidCastException:

Error: System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.SqlClient.SqlConnection’.

Category: .net framework, c#, mssql, ssis |