Ternary Operator in SQL

there technically isn't one, but an equivalent isn't too hard to do

This ternary in C#:
(str.Length > 0 ? "yes" : "no");

Is equivalent to this in SQL:
(CASE WHEN LEN(@str) > 0 THEN "yes" ELSE "no" END);

Running C# libraries with the SQL CLR

You can run your dlls from inside SQL - but it is a bit limited

Sometimes you need your SQL server to run a little bit of one of your programs, this is not a common case, but I have found it does happen. It is quite possible and not too complicated, but you are limited in what you can use. For example, even in SQL 2008, the maximum framework you can run is 3.5 (really 2.0), so anything with 4.0 is off limits due to the CLR. Also, many dlls are also off limits such as System.Data.Linq and System.Data.Entity so kiss most of your ORM solutions goodbye if you plan on doing this.

For this example, I am going to use a simple table in my SQL which will follow after this. For the example, I am going to write a C# method that writes in a new Widget to the Widgets table in the 'SqlClrTest' database at the current time - nothing fancy or useful, but it is easy to understand.
CREATE TABLE Widget(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL,
	Created DATETIME NOT NULL
);

Now you need to set up your environment to handle the CLR:
--allow you to use it
ALTER DATABASE SqlClrTest SET TRUSTWORTHY ON;

--and turn it on
sp_configure 'clr enabled', 1
GO
reconfigure
GO

Now in a new library, I will make a simple class with older ADO that inserts a new entry. Yes, I know this isn't written well, but it is very simple to understand...
using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace SqlClr
{
    public class Widget
    {
        public Widget() { }

        [SqlProcedure]
        public static void Insert(SqlString name)
        {
            string connectionString = 
                "Data Source=.\\sqlexpress;
                 Initial Catalog=SqlClrTest;
                 Integrated Security=True";
            string date = SqlDateTime
                .Parse(DateTime.Now.ToString())
            SqlConnection conn = new 
                SqlConnection(connectionString);

            SqlCommand sqlComm = new 
                SqlCommand("INSERT INTO Widget 
                (Name, Created) VALUES 
                ('" + name.ToString() + "', '" + 
                date + "')", conn);

            conn.Open();
            sqlComm.ExecuteNonQuery();
            conn.Close();
        }
    }
}

Notice that the inputs are defined in System.Data.SqlType, also, the method is painted with a [SqlProcedure] attribute. Now compile the dll and take down the path to it. Go back into SQL and enter the following to compile the assembly into SQL:
CREATE ASSEMBLY Sql_Clr
AUTHORIZATION dbo
FROM 'C:\path-to-assembly\SqlClr.dll'
WITH PERMISSION_SET = UNSAFE;

Now you will notice it shows up under your table > programmability > assemblies directory if it worked correctly. I gave it a name with an underscore so you can see where that comes into play later. Next you will need to make a stored procedure that calls the method:
CREATE PROCEDURE WidgetInsert 
	@name nvarchar(100)
AS EXTERNAL 
	NAME Sql_Clr.[SqlClr.Widget].Insert;
GO

You can see that you call it in this format: SqlDllName[C#DllName].Method. Now all that is left is to call the method in SQL:
EXEC WidgetInsert 'NEW WIDGET'

And you have successfully (in the most round-about way possible) used a SQL stored procedure to call a .Net dll to run a method to update the database.

Drop all tables in a database with simple SQL

sometimes you just want to drop the tables and leave the DB

This is a very simple and useful bit of SQL shown to me by a colleague. Often times, there are a bunch of foreign keys preventing you form just dorpping tables - it can be a pain to figure out what order you need to delete them in in order to do it correctly. Just run this until all the tables are gone:
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "

You may see errors, that is normal (you are simply being notified of the foreign keys and such. All the tables will be gone when you no longer see and error messages.

Linq-to-SQL vs Linq-to-Entities : Revisited

After 2 months of nothing but L2E, I feel I can give a decent comparison

Most likely you have read more than just a few of these comparisons and are tired of the debate. I too disliked L2E from the get-go but decided that my opinion was not totally grounded and that I should really understand L2E before I crap on it, so, I decided to do an entire project at work with it and get down to the nuts and bolts. Now, 2 months, a lot of frustration and some happy customers later, I can give a fully educated opinion on L2E: it is not as bad as I originally thought, in fact, it is pretty sweet in some areas, but still not as easy to use as L2S.

If you have read any of my multiple posts on Entities this will mostly be a re-hash of what I have already said. If not, this is a good summary of my findings and hopefully will help you decide.

dynamic queries

Winner: TIE
Thanks to the geniuses that wrote System.Linq.Dynamic.dll the syntax is *mostly* interchangable between the two languages (which I didn't realize when writing this). If I was forced to pick a winner, it would be L2S as there are more choices for syntax than for L2E, but that can also be viewed as a fault.

linq-to-sql -and- linq-to-entities
var results = db.branch
  .Where("branch_id == @0 || display == @1", 
    new object[] {5, "Hello"});

With L2S, you can do this as well:
var results = db.branch
  .Where("branch_id == 5 || display == \"Hello\"");

working with databound controls

Winner: Linq-to-SQL
This one is a no-brainer. After being lulled into the fact that L2S just works with so many things, I had to work backwards on problems I found with L2E. One annoyance I noticed was that if I was making an update in a GridView, it would error out unless I explicitly had the primary key in the GridView - and if I wanted it hidden, it was necessary to use css; it is documented here.

Another thing I ran across is the fact that you need to use all the required 'Include' statements in order to get a Repeater/GridView to show child values of relationships with Entities where is L2S, you could simply call the relation without any extra work.

multiple databases

Winner: Linq-to-SQL
The choice is quite simple here, as Linq-to-Entities has no native support for multiple databases.

database relation management

Winner: Linq-to-SQL
First of all, simple things such as getting foreign key values with linq-to-entities is much more difficult than in L2S; examples (written as short as I could):

linq-to-sql
int s_id = dataContext.records
  .First(r => r.record_id == 1).state_id;

linq-to-entities
int s_id = Convert.ToInt32(
  Entities.records.First(r => r.record_id == 1)
    .stateReference.EntityKey
    .EntityKeyValues.First().Value
  );

As for putting those relation values into the DB, the clear winner is L2S as well, just take a look at what it takes to set this object 'r' to have a reference to the 'city' which has a key of the integer value of 5:

linq-to-sql
r.city_id = 5;

linq-to-entities
r.cityReference.EntityKey = new EntityKey(
  "YourEntitiesNamespace.cities", "city_id", 5);

a few more...

A while back I went over some other reasons L2E annoyed me such as DataSource annoyances, relations (again) and the pain of updating your model.

verdict

Linq-to-Entities is fully capable and can do *almost* everything Linq-to-SQL can do, it just takes a bit more code some times; the almost is for multiple database utilization. L2E is a great ORM, but many of us were spoiled with L2S, so it is now up to Microsoft to live up to the expectations that they put out for us with this framework that they are deprecating. I hope and assume it will become a superior ORM, but that is in Microsoft's hands now; for the time being, it is not.

Shout it kick it on DotNetKicks.com

SQL State and Country lists

sql files to make and fill country and state lists with abbreviations

Doesn't get any more straight-forward than that, here is the table structures:
CREATE TABLE dbo.country (
country_id INT NOT NULL PRIMARY KEY IDENTITY,
country_name VARCHAR(50) NOT NULL,
country_abbrev VARCHAR(3) NOT NULL
);

CREATE TABLE dbo.state (
state_id INT NOT NULL PRIMARY KEY IDENTITY,
state_name VARCHAR(50) NOT NULL,
state_abbrev VARCHAR(2) NOT NULL
);



Getting started with Linq-To-Entities tutorial

The transition from Linq-to-SQL to .Net's Entities framework is incredibly simple

In my humble opinion, Linq is easily the greatest thing .Net has come out with in the past few years, and along with it, Linq-to-SQL (L2S) was a godsend. Being quick to hop on the L2S bandwagon, I quickly became a huge fan of the framework, it is amazing easy and useful. That being as it is, I was quite disappointed when I heard that Linq-to-SQL was no longer going to be advanced at all. Now, it is surely not dead, it is still very usable and effective at what it does, but I like to stay with frameworks that will be actively advanced and fbug-fixed. So I decided to make the jump to Linq-to-Entities (L2E), and it was suprisingly simple.

This guide should be a good starting point for anyone whether or not they are familiar with L2S, and a quick 'jumping guide' to L2S developers. Here's how to get started:

Make your ADO.NET Entity Data Model (.edmx file)

This is comparable to the .dbml file that you made with L2S.
  • Right click on your App_Code folder and select New Item
  • Select ADO.NET Entity Data Model and name it (I left the default Model.edmx for the example)
  • Choose Generate from database and click Next
  • Choose your database from the dropdown and choose the name to save the ConnectionString as and click Next
  • Choose all the things you want included, I just chose tables, but you may want to include views and SPs if you have them
  • *Be sure to remember the Model Namespace you choose for your .edmx (I will use DatabaseModel for the example) - click Finish

Now it will take a moment to run through and produce your .edmx; when it is done you will see a nice little representation of your tables reminiscent of the .dbml display in L2S:

Access your .edmx

Now we simply access the .edmx much like we did with L2S. You must remember to add Using DatabaseModel (or whatever your namespace was) to the code where you are using it. Now we will make a DatabaseModel object and use it to add a 'product' to the database. First, you need to make your Entity Access Object:
DatabaseEntities db = new DatabaseEntities();

Then you can use it to enter an object:
products p = new products();
p.product_name = "Word Processing Software";
p.price = 99;
p.stock = 100;
db.AddToproducts(p);
db.SaveChanges();

Once again, if you are familiar with L2S, this is almost the same exact thing! If you are new, this is very straight-forward and should be easy to understand:
  • Make an object of a type inside your database
  • Fill the object up with data
  • Put it into the database
  • Save the changes

Now L2E one-ups L2S and makes data entry even easier, this will accomplish the same as above:
db.AddToproducts(products.Createproducts(0, "Accounting Software", 300, 125));
db.SaveChanges();

Notice that the 'product_id' is entered as '0', that is because it is auto-incrementing, so it doesn't matter what I put there, it will be ignored. Now I don't know about you, but that little bit there will save me hundreds of lines of code! I am starting to like L2E already!

Display your data

Now that we have put a few objects in to the database, we can go and check it out. Later we will dig in with code, but first we will use a LinqDatasource/GridView:
  • Drag a LinqDataSource (LDS) on to the page and click Configure Data Source from the little square on the upper right of the LDS
  • Choose your Object Model and click Next
  • Choose your table and click Finished
  • Drag a GridView (GV) on to your page
  • In the GV option box, choose your datasource from the dropdown

now just view your page:

Modify Data

Now that we have seen how to put in data, we will modify some; once again, L2E makes it trivially simple:
products edit = (from p in db.products where p.product_id == 2 select p).First();
edit.product_name = "Account Software v2";
db.SaveChanges();

The Linq statement is EXACTLY like it would be in L2S, and just like in L2S, you can shorten up this with some Lambda integration; this will accomplish the same thing:
db.products.First(p => p.product_id == 2).product_name = "Accounting Software v2.1";
db.SaveChanges();

Deleting Items
Now that we have seen Insert and Update, the next logical step is Delete, and it is just as easy. Let us delete the 'Word Processing Software' frorm the table:
products del = (from p in db.products where p.product_id == 1 select p).First();
db.DeleteObject(del);
db.SaveChanges();

And the same exact thing shorthand with Lambdas:
db.DeleteObject(db.products.First(p => p.product_id == 1));
db.SaveChanges();

Once again, I have to say I like the approach that L2E takes of that of L2S, as it is not necessary to specify which table to delete from, as an object can only be deleted frorm the table that it is in.

Using your database relations

As in L2S, L2E takes great advantage of well designed databases and relations. If you noticed up above, the two tables in my database have a 1 to many relation frorm product to reviews. Each element in the 'reviews' table is required to relate to an element in the 'products' table (column 'product_id'). Let's fill the DB with a few more products and some reviews; a lot is going to go on here:
products p1 = db.products.First(p => p.product_id == 2);
products p2 = products.Createproducts(0, "Strategy Game", 49, 99);
products p3 = products.Createproducts(0, "Sports Game", 39, 99);
db.AddToproducts(p2);
db.AddToproducts(p3);

reviews r1_1 = reviews.Createreviews(0, "Much Improved", "this is a much better version", "Bill Brasky");
r1_1.productsReference.Value = p1;
reviews r2_1 = reviews.Createreviews(0, "Terrible", "worthless", "Dirk Digler");
r2_1.productsReference.Value = p2;
reviews r3_1 = reviews.Createreviews(0, "Great Game", "very tough AI", "Wonderboy");
r3_1.productsReference.Value = p3;
reviews r3_2 = reviews.Createreviews(0, "Very Fun", "the Bears rule", "Mike Ditka");
r3_2.productsReference.Value = p3;

db.AddToreviews(r1_1);
db.AddToreviews(r2_1);
db.AddToreviews(r3_1);
db.AddToreviews(r3_2);

db.SaveChanges();

Now to explain what just happened:
  • The first line gets an object of type products where product_id == 2 (this is 'Accounting Software v2.1')
  • The next two lines create new products
  • The next two submit those into the database

Now we have a total of 3 objects in the 'products' table and none in the 'review' table, that is where the next 8 lines come in. If you break up those 8 lines into pairs of two, you can see they are all the same thing really:
  • Make a new object of type reviews
  • Assign its foreign key reference to a products object

Since the database requires a relation between these two tables, you must be sure to set the reference. The last lines just submit everything and commit them to the database.

Now that that is in there, you can use the real power of relations in L2E. Once again, it is almost the same as L2S:
foreach (products p in (from _p in db.products select _p))
{
  Response.Write("<h3>" + p.product_name + " - $" + p.price + "</h3>");
  Response.Write("<b>Reviews:</b><div style='border:1px solid navy;padding:5px;'>");
  p.reviews.Load(); // this loads all the reviews that relate to the product p
  foreach (reviews r in p.reviews)
    Response.Write("<b>" + r.title + " - " + r.reviewer + "</b><br />" + r.review + "<br />");
  Response.Write("</div><br />");
}

And this is what you get:
Yes, I know I used the ugly 'Response.Write', and the output is hideous... but this is just a demo people! As you can see, it is incredibly easy to iterate through all of the reviews of a products, this big difference being that you need to call the .Load() method or they will not be populated. Now this is both a blessing and a curse; this is very efficient as it only loads the related objects if need be, but... it is soemthing that is easy to forget and may leave you scratching your head. I think I like it.

Now relations also work the other way as well. Say you have a reviews object, and you want to know the price of the products it is related to. No need to actually look up the products object, you just call the relation:
reviews r = db.reviews.First(_r => _r.review_id == 3);
r.productsReference.Load();
Response.Write("Price: $" + r.productsReference.Value.price.ToString());

Once again, notice that you have to Load() the reference, or you will get nothing.

Now that should get you started. Like I said, if you are familiar with L2S, this transition should be no problem, and if you are new to this whole Linq arena, this should be simple to pick up. This new ADO is getting more and more impressive the more MS works on it. I can't even imagine goging back to the old methods...

Change column name in a MSSQL table

For some reason, MS does not have a built in command for this?


Not sure why this is as competeing technologies like Oracle and MySQL do, but there is a stored procedure that will get you where you are going:
EXEC sp_rename 'TABLENAME.OLD_COLUMNNAME', 'NEW_COLUMNAME', 'COLUMN';

Just replace TABLENAME.OLD_COLUMNNAME and NEW_COLUMNNAME with your new values.

A simple way to add a 'View All' sort option to your DataSource in Asp.Net

Just use a little SQL trick to simplify and add usability to your DataSource

Often times I have integer fields in my database that serve as categories. These categories are very helpful when sorting for the users, butit is sometimes tough to write a simple, one-line query to output ALL of them; I figured an extremely simple way to do this. This may be a little hard to explain, but it is very useful.

For this example, I am going to use a LINQDataSource

<asp:LinqDataSource ID="ldsCases" runat="server" ContextTypeName="dbDataContext" TableName="Cases" Where="Category == @Category">

   <WhereParameters>

     <asp:SessionParameter Name="Category" SessionField="Cat" Type="Int32" />

   </WhereParameters>

</asp:LinqDataSource>


This is using a Session variable to work with the DataSource which will be set the the integer value that corresponds to the category I want to find. Now this will work just fine if we are trying to just look at a single category, but what if we want to look at all of them? It is really quite simple, now, just add one more OR (||) statement to our SQL and set the Session variable to 0 any time we want to call everything:

<asp:LinqDataSource ID="ldsCases" runat="server" ContextTypeName="dbDataContext" TableName="Cases" Where="(Category == @Category) || ((Category * @Category) == 0)">

   <WhereParameters>

     <asp:SessionParameter Name="Category" SessionField="Cat" Type="Int32" />

   </WhereParameters>

</asp:LinqDataSource>

An error occured: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

One simple solution to this error I often face

More often than not, I think everything should be working when this happens. I check to make sure that connections are allowed, I make sure my sql user has proper permissions and is mapped to the correct database and all that good stuff.

But one thing this fails to tell you is when you do not have sql server login enabled (windows only).

Simply go in to SQL Server Management Studio and right click on your instance and choose properties then click on security in the right side and there you will see your Server Authentication select the proper one and apply.

Now remember, many things can cause this error, this won't fix them all. I just thought I would share as this is not something you may think of when reading that error message.

Incorrect syntax near 'something' in SQL 2005 while doing a 'CREATE USER' or 'CREATE LOGIN'

Annoying little problem and the simple fix

I was entering the following into my SQL Server Management Express window:
CREATE LOGIN 160thUser
  WITH PASSWORD = 'some_password';
USE name_of_db_to_use;
CREATE USER 160thUser FOR LOGIN 160thUser;

And I kept getting the error:
Incorrect syntax near '160'

Now I often use the same LOGIN and USER, so that was not the problem. What I found it out to be is that SQL 2005 does not allow LOGIN and USER obejcts to start with a number... sooo I just flipped them around to:
CREATE LOGIN User160th
  WITH PASSWORD = 'some_password';
USE name_of_db_to_use;
CREATE USER User160th FOR LOGIN User160th;

And everything worked.