Archive for the ‘SQL Server 2005’ Category
What a sigh of relief
Posted by fr3dr1k | Filed under C#, SQL Server 2005
Ok so I have written my first CLR project, but there are a few things I didnt do right and which I know requires a bit more knowledge and effort. Firstly, the context-connection seems to be only applicable to the SqlPipe class. I tried using an SqlConnection that used a regular connection string, but I got an error stating that SqlPipe.Context can only be used with context-connection = true. Allrighty then. Coming to think of it I did not do much wrong in terms of data access, because I used stored procedures that already existed. Anyway…
My main problem was that I needed a connection string and I didnt know how to get it. To get a connection string into a CLR project you have these choices:
- Use an App.config file. This is not really recommended in a production environment because you will have to restart SQL Services for SQL Server to read the values in the config file. If you plan on using a config file, you will have to place the config file in the root folder of the SQL server instance. You get this by looking at the properties of the database (look for Root folder), and naming the file something like sqlsrv.exe.config.
- Hardcode the connection strings. This is hardly a good thing to do
- Use context connection = true. Again though you need to be aware that the usage for this connection type differs from a regular connection string. Read the MSDN article here
- Use some external file, plain text, xml, etc. This means however knowing a part of the directory structure the machine/server resides on.
- Could you use a web service? Haven’t really thought this one through yet. But could you hypothetically speaking use a web service to initialise a connection string? I’m not sure
- Use a scalar user defined function and pass the database details as parameters, and run the function as a Job. This is the method I chose.
CLR Stored Procedures
Posted by fr3dr1k | Filed under C#, SQL Server 2005
In one of my recent projects I learnt how to use CLR stored procedures. So what exactly are CLR stored procedures? Well they allow you to run code (C#) inside SQL Server 2005. So instead of creating stored procedures or UDF (User Defined Functions) with SQL code you use C# code to achieve this. There are various scenarios where you would want to do this, and here are a few reasons why:
- Complex code – C# (or any CLR language) has much better support for doing complex string manipulations or hectic calculations than SQL Server does. SQL server does not have support for generics and collections, for example, and to be honest Lists and Dictionaries are very convenient structures
- Functionality that extends beyond the scope of SQL Server 2005. I had to, for instance, check a POP3 email account and update DB values based on the results from checking the POP 3 account. The POP 3 functionality does not exist, and cannot be replicated with plain SQL Code.
There are however a few instances where I think you should not use CLR stored procedures:
- When the assemblies you are adding are not tested and supported. What I mean by this is that for example System.Drawing is considered ‘unsafe’, and you have to wonder why MS do not make it easy to integrate this. Personally I think you should stick to things that are supported, and tested.
- If you’re CLR sproc does data access (selects, updates, inserts) or simple queries. SQL Server 2005 is a very powerful piece of software
I also found that by assigning my CLR sprocs to Jobs I could leverage off the power that SQL Server 2005 provides, and let my sprocs run at intervals without having to write a windows service.
Using SQL Server Authentication
Posted by fr3dr1k | Filed under ASP.NET, SQL Server 2005, Web Development
So during the week I had a situation where I had to take an existing web application, that used SQL Server Express, and had to make it work with SQL Server 2005 Standard Edition. Both versions of SQL Server form part of the SQL Server SKU line of products, but their implementation in certain scenarios differ. The providers that are used to connect through your applications are the same though.
I used Visual Studio 2005 to develop the initial application, and in many ways Visual Studio is awesome but in other ways its not, and specifically with regards to SQL Server databases its not that great. Visual Studio allows you to simply drag and drop databases into your web application project, which is great for productivity, but it may cause you some headaches later on. If you have SQL Server Express running on a local machine you will note that a database created in Visual Studio will not show up in SQL Server Management Studio Express. Visual Studio creates the database for you, but it does not attach it to the SQL Server instance that you are running in SQL Server Management Studio Express. SQL Server Express uses a special mode of operation called user instancing, which means it uses accounts such as NETWORK SERVICE and ASPNET to give it rights to connect to the SQL Server Express Instance. SQL Server Express Instancing also adds a 45-75MB memory overhead.
ASP.NET Authentication
Posted by fr3dr1k | Filed under ASP.NET, SQL Server 2005, Web Development, Web Technologies
During the week I have been working with SQL Server 2005 Standard Edition and 2005 Express Edition for a website. I have specifically been looking at using SQL Server 2005 for Authentication as opposed to using the standard authentication that comes with ASP.NET when you configure an ASP.NET application in Visual Studio 2005. When you enable authentication for your website an SQL Server Express database (MDF file) is automatically created and the connection string in your web.config file is also changed to point to that SQL Server Express database.
Why is the standard authentication model not appropriate for all uses?
A couple of reasons I can think of:
- If you wanted your development environment and your application environment to work together seamlessly then you might want to consider using SQL Server Authentication, or at least get both environments to use connection strings that are similiar or the same.
- If you wanted to change or add to the fields provided by the regular membership model provided in ASP.NET and in particular have those fields stored in a proper database table.
- If you wanted to use a Database Management System other than SQL Server. Perhaps you want to use MySQL or MS Access. You can also use Active Directory and XML as an authentication model.
