Archive for the ‘Database’ Category.

How to truncate all tables except one in MS SQL

It is well-known that a SQL guru can truncate all tables. This is not something anyone is going to do in production. For while coding or testing, this might be a common practice.

To truncate all tables, use the following sql:

EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

However, what if you wanted to exclude one table. For example, if using Entity Framework, one might want to keep the __MigrationHistory table untouched.

EXEC sp_MSForEachTable 'if ("?" NOT IN ''[dbo].[__MigrationHistory]'')
	TRUNCATE TABLE ?'

I finally figured it out by learning how to query the values:

EXEC sp_MSforeachtable 'if ("?" NOT IN ("[dbo].[__MigrationHistory]"))
         SELECT "?"'

It took me a good hour to figure this out. The key was to quote the ? variable.

Back up and restore a single table with foreign keys using SQL Server

Today I needed to backup a single database table, then test a change to the data, then if the change failed, restore the original data.

Below is how I did this.

What I used

I used SQL Server Management Studio to do all of the following steps.
I performed the steps on a database running on a SQL 2012 database server.

Part 1 – Backup the table

SELECT *
INTO MyTable_Bak
FROM MyTable;

Note: This will work usually, however, it won’t work if you have a calculated column. If you have a calculated column, create the table first, then specify the columns you are inserting. I didn’t have a calculated column, so I didn’t take time to figure this out.

Part 2 – Restoring the table

Step 1 – Finding the Foreign Key Constraints

SELECT Name, Object_Name(parent_object_id) as [Table]
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('MyTable')

The results were like this:

Name                    Table
FKDDED6AECAD1D93C0      MyOtherTable1
FK166B6670AD1D93C0      MyOtherTable2

Step 2 – Get the Drop and Create for each Foreign Key

In SQL Management Studio Express, I went to each table in the above list, and did the following:

  1. Locate the foreign key under Database | MyDb | Tables | dbo.MyTable | Keys.
  2. Right-click on the Foreign Key and choose Script Key as | Drop and Create to | Clipboard.
  3. Paste this into the query window.
  4. Delete the USING MyDb statement and separate the DROP statement from the two ALTER TABLE statements.
  5. Repeat for the next foreign key constraint, grouping the DROP statements and the ALTER TABLE statements together.

Step 3 – Run the DROP statements

Run the two DROP statements created above.

ALTER TABLE [dbo].[MyOtherTable1] DROP CONSTRAINT [FKDDED6AECAD1D93C0]
GO
ALTER TABLE [dbo].[MyOtherTable2] DROP CONSTRAINT [FK166B6670AD1D93C0]
GO

Step 4 – Restore the table

I used this query to restore the table from the backup.

SELECT * FROM MyTable
SET IDENTITY_INSERT dbo.MyTable ON; 
TRUNCATE TABLE MyTable ;
INSERT INTO MyTable (Id, Col1, Col2, Col3) -- Specify all columns here
SELECT (Id, Col1, Col2, Col3)              -- Specify all columns again here
FROM MyTable_Bak

Step 5 – Restore the foriegn key constraints

Run the ALTER TABLE scripts you grouped together from Step 2.

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK ADD  CONSTRAINT [FKDDED6AECAD1D93C0] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FKDDED6AECAD1D93C0]
GO

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK ADD  CONSTRAINT [FK166B6670AD1D93C0] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FK166B6670AD1D93C0]
GO

Conclusion

Your table is restored.

Observations

  1. This is a simple process for a table with only a few foriegn key constraints but could be difficult if you have dozens of foreign key constraints.
  2. Also, this process might not work perfectly with calculated columns without changes to the above process.
  3. It should be easier to do this.

If you know of a better way that doesn’t require $oftware, let me know.

SQL Query to Entity Framework Cheat Sheet

The following are examples of SQL queries and how to execute them with Entity Framework

SELECT * with Entity Framework

SELECT * FROM Contacts
var mydbContext.Contacts.ToList();

SELECT Id with Entity Framework

This is really for how to select any single column.

SELECT Id FROM Contacts
List<int> idList = dbContext.Contacts.Select( c => c.Id ).ToList(); // Assuming Id is of type int

SELECT only one row with Entity Framework

This is really for how to select any single row from a table.

SELECT * FROM Contacts where Id = 10
var id = 10;
var contact = dbContext.Contacts.SingleOrDefault( c => c.Id = id );

SELECT only one result with Entity Framework

This is really for how to select any single value from a single row in a table.

SELECT UserId FROM Contacts where Id = 10
var id = 10;
int userId = dbContext.Contacts.Where( c => c.Id = id ).Select( c => c.UserId ).SingleOrDefault();

SELECT first result with Entity Framework

This is really for how to select the first value from a list of returned rows from a table.

SELECT TOP 1 * FROM Contacts
var id = 10;
int userId = dbContext.Contacts.FirstOrDefault();

INSERT INTO with Entity Framework

INSERT INTO dbo.LD_User (Name, Type, Active, CreateDate, CreatedBy)
VALUES ('user1', 1, 1, GetDate(), 101)
var user = new User { Name = "user1", Type = UserType.Contact, true, DateTime.Now, User.System };
dbContext.Users.Add(user);
dbContext.SaveChanges();

Note: UserType.Contact and User.System are enums in the above example.

INSERT INTO … SELECT with Entity Framework

Imagine you have these three tables. Product, ProductGroup, ProductGroupMembership. You want to make Products a member of a group by inserting into the ProductGroupMembership table.

INSERT INTO ProductGroupMembership (ProductId, GroupId, CreateDate, CreatedBy)
SELECT Id, @GroupId, GetDate(), @CreatedBy FROM Product
WHERE Id IN (1, 2, 3, 4, 5) -- there could be hundreds or thousands of numbers in the IN statement
EPIC FAIL!!! Can't be done without raw sql and opening up to sql injection attacks.

However, you can create a stored procedure that takes and user-defined table called ArrayOfInt. Then add EntityFrameworkExtras.EF6, which is available as a NuGet package, to call the storedprocedure and pass it an array.

WHERE with many AND/ORs with Entity Framework

Imagine you have these three tables. Product, ProductGroup, ProductGroupMembership. You want to make Products a member of a group by inserting into the ProductGroupMembership table.

You have a list of software Products provided to you. However, the input only includes Product.Name and Product.Version. You now need to check if the products exist and get the id.

SELECT Id FROM Product
WHERE (Name = 'Product 1' AND Version = '10.0')
WHERE (Name = 'Product 2' AND Version = '10.0')
WHERE (Name = 'Product 3' AND Version = '10.1')
WHERE (Name = 'Product 4' AND Version = '10.0')
WHERE (Name = 'Product 5' AND Version = '1.0')
EPIC FAIL!!! Can't be done without raw sql

However, you can add LinqKit’s PredicateBuilder to do this. PredicateBuilder works on top of Entity Framework and is available as a NuGet package. See how I used it here: Entity Framework and many WHERE clauses

SQL Addendum Table

I have an application I am building that needs to be malleable. It is a data-driven application. It will have users, contacts, organizations, and many other objects represented as a database table. One goal of this project is to allow for extension. Some customers are going to want to add a field to an object that our tables don’t include. We want to handle this end to end. It seems the perfect use of a property value table.

It would be pretty easy to create an Addendum table for each object.

dbo.Organization
dbo.OrganizationAddendum
dbo.User
dbo.UserAddendum

While that is OK, it requires additional work every time a table is created. What if a Partner writes a plugin and adds an object in the database? Well, unless the Partner creates an addendum table, this won’t really work.

Is there a way to solve this so any object in the database can have Addendum data?

I came up with this table.

CREATE TABLE [dbo].[LD_Addendum](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Table] [nvarchar](100) NOT NULL,
	[TableId] [int] NOT NULL,
	[Property] [nvarchar](255) NOT NULL,
	[Value] [nvarchar](255) NOT NULL,
	[CreateDate] [datetime2](7) NOT NULL,
	[LastUpdated] [datetime2](7) NULL,
	[CreatedBy] [int] NOT NULL,
	[LastUpdatedBy] [int] NULL,
 CONSTRAINT [PK_Addendum_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])

Then in the Web interface, I could have single template control that works for any object. Whatever object I am editing, be it user, contact, organization or other, the control would exist. If a partner adds a plugin with a new object, the control would exist. Seems easy enough, right?

The problem comes in with some of the features that we would like to be automatically handled on the database side:

  1. Table should have Id column
  2. Table should have four main fields
    • Table <– Database table to add addendum data for
    • TableId <– The row of in the table that addendum is for
    • Property <– The property of the addendum
    • Value <– the value of the addendum data
  3. Table should have the four auditing fields in IAuditTable
    • CreateDate
    • CreatedBy
    • LastUpdated
    • LastUpdatedBy
  4. Only one Property of the same name should exist per table and id. Easily done with a Unique constraint.
  5. Table should have a constraint that enforces that table must exist.
    I found a way to do this: I created User-defined Function (UDF) and check constraint that uses the UDF.

    CREATE FUNCTION [dbo].[TableExists](@TableName NVARCHAR(255))
    RETURNS bit
    AS
    BEGIN
    RETURN ((SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName))
    END
    
  6. Table should have a constraint that enforces that TableId must exist in the Table
    1. Not supported – CLR UDFin C#? Or handle this in code?
  7. The row should delete when Table is deleted. Similar to ON DELETE CASCADE.
    • Not supported – CLR UDFin C#? Or handle this in code?
  8. The row should delete when a row from a table that matches Table and Id is deleted. Similar to ON DELETE CASCADE.
    • Not supported – CLR UDFin C#? Or handle this in code?

Perhaps we ignore the missing features from database side and handle them with code?

Or perhaps another database system other than Microsoft SQL Server (such as Postgresql) could do this?

Scaling

Assuming I got this to work, I see one main problem: Table size. However, I am not sure this is an issue. Tables can quite large, millions of rows. If this table got too big, then we could investigate why, analyze the table, and perhaps move a property value from the Addendum table to an actual column in a real table. This should replace the ability to create a plugin with an additional table, but it should make it so few plugins are needed as there is more extensibility out of the box.

Also, we found that default values often alleviate addendum tables. For example, imagine adding an addendum item to an organization, ContactIntervalInDays. Say a company is supposed to contact their customers every 90 days. However, some customers might require more or less contact. However, the default is 90. Instead of adding 90 to all customers, you set a default. If ContactIntervalInDays is not in the Addendum table, then use 90, otherwise use the value.

Anyway, it seems like an Addendum table is something that most projects and solutions, such as CRMs, Shopping Carts, ERPs, etc. should implement. It won’t solve the most complex issues with extending a product, but it would perhaps solve many of them. The more complex extension can continue to be added via a well-designed plugin architecture.

Unfortunately, this simplistic solution is not supported. The recommendation is to have 1 addendum table for every regular table. Ugh! That doesn’t scale and is not maintainable long term.

Still, I went ahead and requested this feature from the SQL team.

Entity Framework and many WHERE clauses

So today, I needed to get Entity Framework to return me a list of Products from the database based on a list of Product.Name and Product.Version values (not Ids). If it were Product.Id, it would have been simple as I could have used an IN statement, but it wasn’t.

The query might get many (maybe hundreds at a time) products based on the list. Here is the query I imagined.

So when doing a query like this, since there could be hundreds, I have a couple of options.

  1. Query the database once for each product.
    SELECT * FROM dbo.Product
    WHERE (Name = 'Product 1' AND Version = '10.0')
    

    Repeat this same query once for each Product.

  2. Query the database one time with an or clause for each Product.Name and Product.Version.
    SELECT * FROM dbo.Product
    WHERE (Name = 'Product 1' AND Version = '10.0')
       OR (Name = 'Product 2' AND Version = '10.0')
       OR (Name = 'Product 3' AND Version = '10.0')
       OR (Name = 'Product 4' AND Version = '1.0')
       -- There could be hundreds
    
  3. Query the database once and get all products and use code to find the ones I wanted.
    SELECT * FROM dbo.Product
    

Option 1
I didn’t like this option because I could end up doing hundreds of single queries. That doesn’t sound like a good idea. What would the performance impact would be when doing hundreds of single queries? The overhead of traversing over the network to the database would prevent this option from scaling.

Option 2
This is the option I imagined in my head. My gut said to use this option.

Option 3
This would work. We only have about two thousand products today and querying them all would, right now, not be bad at all. However, we just bought a company and will be adding more products. We plan to buy more companies. Also, we have two companies that we have already bought and have yet to add those products in. When would the number of Product rows in the database make the SELECT * and invalid option? Doing this would work now, but it leave a time bomb for some future developer encounter and have to fix.

Winner: Option 2

Problem
Entity Framework doesn’t really have an easy way to create the Option 2 query.

So, how do I create this query with many where statements?

Here are the two options I’ve found:

Inline SQL Query with Entity Framework

        private static List<Product> GetProductsByNameAndVersion(ActivationDbContext dbContext, IEnumerable<ProductRequest> products)
        {
            if (!products.Any())
            {
                return new List<Product>();
            }
            var query = "Select * FROM Product WHERE ";
            var or = "";
            var template = "(Name = '{0}' AND Version = '{1}')";
            foreach (var prod in products)
            {
                query += or;
                query += string.Format(template, prod.Name, prod.Version);
                or = " OR ";
            }
            var dbProducts = dbContext.Products.SqlQuery(query).ToList();
            return dbProducts.ToList();
        }

This option means I have to create magic strings and make sure that I handle the strings correctly. It has bugs already. Such as what if a product only has a name and not a version (version could be null or empty, who knows) or vice-versa? How would this affect my query string?

PredicateBuilder

Predicate Builder from the LinqKit library which is available as a NuGet package.

        private static List<Product> GetProductsByNameAndVersionPredicate(ActivationDbContext dbContext, IEnumerable<ProductRequest> products)
        {
            if (!products.Any())
            {
                return new List<Product>();
            }

            var predicate = PredicateBuilder.False<Product>();

            foreach (var prod in products)
            {
                var inner = PredicateBuilder.True<Product>();
                inner = inner.And(p => p.Name== prod.Name);
                inner = inner.And(p => p.Version == prod.Version);
                predicate = predicate.Or(inner);
            }
            var dbProducts = dbContext.Products.AsExpandable().Where(predicate).ToList();
            return dbProducts;            
        }

PredicateBuilder isn’t very intuitive. For starters, what is the different between these methods:

  • PredicateBuilder.True() – from what I understand this would be more appropriate and understandable as PredicateBuilder.And()
  • PredicateBuilder.False() – from what I understand this would be more appropriate and understandable as PredicateBuilder.Or()

Also, you have to remember to call AsExpandable() on the first call to a table in order to use it.

Conclusion

I am going to go with PredicateBuilder for now. It feels cleaner than rolling my own string query. But both solutions ultimately worked. That means that Entity Framework ultimately provided me a solution without an extra library. However, LinqKit saved me from magic strings. My only question is this: Why isn’t a predicate builder built into Entity Framework?

Why we need RFCs for database implementations!

We eschew reinventing the wheel in every aspect of development, yet when it comes to creating a customer database, or an order database, everyone creates their own. They roll out their own database model, usually to their regret, and have to roll their own create, read, update, delete (CRUD) services. We’ve been using databases for decades and somehow, we have failed to eliminate the constant repetition of the same work. Everyday someone is creating a Customer or Order database model from scratch. Why?

We have technologies like Entity Framework, NHibernate, Linq to Sql, etc… to help try to make sure we don’t reinvent the wheel for Object-relational mapping (ORM, a technique to talk to a relational database with object-oriented code), but we are constantly doing it just above the ORM layer because we are reinventing the database itself. Look at the design below. The blocks in red are the ones we are reinventing over and over again.

Reinventing the database wheel

Red = Layers in which we reinvent the wheel and duplicate a lot of code

Let’s look at how this causes a chain reaction of reinventing the wheel. No only do we reinvent the wheel for the services, we do so for the UI. You can break it down further from UI to MVC or MVVM, or your favorite UI partern, and you will see that we are duplicating a lot of code that we just don’t need to duplicate.

This also has an effect on Business Intelligence (BI). Because everyone is created a completely different database design, all the queries to get data from the database are different. These queries are used to build reports or to mine the data into a a data warehouse. The data often has to go through an Extract, Translate, Load (ETL) procedure to even make it reportable. Why?

Reinventing the database wheel chain reaction

Why are we all excepting the need to reinvent the wheel in so many layers? Is the open source world missing the vision of separation of concerns? We have entire projects that are tightly coupled end to end, some are far better and are loosely coupled end to end. But we don’t have a well-tuned engine of replaceable parts.

Why is it that we don’t have a place for a common database schema and ORM design. The closest thing I have found is this:
http://www.databaseanswers.org/data_models

This list of common schemas is really great, but it is just one box of the image above and that box isn’t even complete. We need not only common data models, but we need them in a source control repository so people can branch them, change them and contribute back. We need versions for MS Sql, Oracle, MySql, Postgresql, etc… We need tests to make sure the data models scale. We need a common services layer, and not just in one language. Perhaps Java, C#, and PHP to start out with, so companies don’t have to change the language they code in to use this.

Imagine it. Software startups could really get a jumpstart if they could download a schema in the database they want to use (perhaps Postgresql), a common service layer in their language (perhaps C#), and then build their proprietary idea on top of that. The startup can claim to implement the standard data model (SDM) and so enterprise companies can know their software will drop in.

UI designers could start building purchasable UIs for these database models and services.

BI companies could start selling prepackaged content reports along with their BI software.

This blows up even bigger when you start stacking these up. We could start using either inheritance or data model bundling. A shopping cart database model be combination of a person database model an order data model, and a Product data model. A software product data model might inherit from Product data model and add a few software specific tables, etc… When a full solution is released, each bundled model is independent of the rest. Each could be on their own databases instances on different servers if desired.

Now you can see how integration becomes so much easier. You start seeing how two completely separate applications could share the same data source. No more ETL from one system to the next for integration purposes. You wouldn’t need to do that. Imagine you have already implement your Customer Relationship Management (CRM) using Standard Data Models (SDMs), and then you want to implement an E-Commerce shopping cart. They choose an E-Commerce shopping cart that also uses the SDMs. Instead of having to migrate customers into the E-Commerce solution, they simply redirect the E-Commerce solution’s customer data model to point to their existing system. The rest of the E-Commerce data models can point to a separate database.

I feel like everyone is missing the big picture here. What do you think?

Add sql constraint on null, empty, or whitespace (C# string.IsNullOrWhiteSpace() equivelant)

Here is a User table. We wanted to make the UserName column not be null, empty, or whitespace.

So the constraint I made is this

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [UserNameNotEmpty] CHECK  (([UserName]<>'' AND rtrim(ltrim(replace(replace(replace([UserName],char((9)),''),char((13)),''),char((10)),'')))<>''))
[UserName]<>''
Empty is checked first and not allowed.
replace([UserName],char((9)),'')
Replaces any instance a Tab character with an empty string.
replace([UserName],char((10)),'')
Replaces any instance a Carriage Return character with an empty string.
replace([UserName],char((13)),'')
Replaces any instance a Line Feed character with an empty string.
ltrim([UserName])
Left trim. It trims spaces from the left side of the string.
rtrim([UserName])
Right trim. It trims spaces from the right side of the string.

Note: You should know that char(9) is tab, char(10) is line feed, char(13) is carriage return.

Here is a complete User table. (This is a legacy system I inherited and I am fixing inadequacies.)

CREATE TABLE [dbo].[User](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](255) NOT NULL,
	[Password] [varchar](255) NOT NULL,
	[Salt] [varchar](255) NOT NULL,
	[FirstName] [varchar](255) NULL,
	[LastName] [varchar](255) NULL,
	[Email] [varchar](255) NULL,
	[Active] [bit] NOT NULL
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Active]  DEFAULT ((1)) FOR [Active]
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [UserNameNotEmpty] CHECK  (([UserName]<>'' AND rtrim(ltrim(replace(replace(replace([UserName],char((9)),''),char((13)),''),char((10)),'')))<>''))
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [UserNameNotEmpty]

Notice, I don’t check null on the constraint, as NOT NULL is part of the UserName column design.

Removing the namespace from the ConnectionString name in the web.config

So we are working on moving a legacy web site to MVC4.  Yes, it is my first time doing this.  I encountered an annoying issue that I thought worth blogging about.

Ok, so I have a DLL that is pretty much nothing more than a TableAdapter. (Yes, I wish this legacy project was using Entity Framework, but alas, it is not). I have a config file and the connectionstring name has a the mode horrible namespace.

  <connectionStrings>
    <add name="Company.Division.Feature.DataAccessLayer.MyConnectionString" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyUser;Password=MyPasswd" providerName="System.Data.SqlClient" />
    <add name="MyConnectionString" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyUser;Password=MyPasswd" providerName="System.Data.SqlClient" />
  </connectionStrings>

Seriously: Company.Division.Feature.DataAccessLayer.MyConnectionString.

Yes, the same connection string is listed twice, once with the namespace and once without.

Why? Well, because all throughout the code, the original authors make call this:

ConfigurationManager.ConnectionStrings["WavelinkLMConnectionString"].ConnectionString

I would like to get rid of one of these configuration settings. Obviously to me, it makes sense to get rid of the long one.

So why doesn’t my TableAdapter library project use the shorter table adapter. Well, I figured that out. Because it is in the Settings file and the Settings file looks for the value using the namespace.

Look at this Settings.Designer.cs file

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.18051
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace Company.Division.Feature.DataAccessLayer.Properties {
    
    
    [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]
    [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "11.0.0.0")]
    internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase {
        
        private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings())));
        
        public static Settings Default {
            get {
                return defaultInstance;
            }
        }
        
        [global::System.Configuration.ApplicationScopedSettingAttribute()]
        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.Configuration.SpecialSettingAttribute(global::System.Configuration.SpecialSetting.ConnectionString)]
        [global::System.Configuration.DefaultSettingValueAttribute("Data Source=MyServer;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyUser;Password=MyPasswd")]
        public string MyConnectionString{
            get {
                return ((string)(this["MyConnectionString"]));
            }
        }
    }
}

OK, so every single TableAdapter requires this value, so I can’t remove it. I could change it but then everytime the settings regenerated this file, I would have to recreate teh change. Not a good idea.

Hooray for partial classes! To fix this, I removed this setting and put it in a separate partial class. In fact, I learned that in the Settings UI, you can click View Code and it will create this file for you. But for me it created in the project root and not under the Properties solution folder, so I had to move it under Properties myself.

using System.Configuration;
using System.Diagnostics;

namespace Company.Division.Feature.DataAccessLayer.Properties
{
    internal sealed partial class Settings
    {
        [ApplicationScopedSetting]
        [DebuggerNonUserCode]
        [SpecialSetting(SpecialSetting.ConnectionString)]
        [global::System.Configuration.DefaultSettingValueAttribute("Data Source=MyServer;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyUser;Password=MyPasswd")]
        public string MyConnectionString
        {
            get
            {
                if (!string.IsNullOrWhiteSpace(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
                    this["MyConnectionString"] = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
                return (string)this["MyConnectionString"];
            }
        }
    }
}

As you can see, I just check if the value is set in the config file without the namespace. If it is, I set the setting to that value.

And that is it! I now can make my config have a single connection string and I don’t have to include the namespace!

  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyUser;Password=MyPasswd" providerName="System.Data.SqlClient" />
  </connectionStrings>

How to query a SQL database in C# using TableAdapters

Visual Studio has some great features to help you access the database and create objects for your database. You could manually create a connection string and manually create objects that represent the data in your database described here: How to query a SQL database in C#?. This article can show you how Visual Studio can do this for you.

So how is it done? By adding a Data Source.

Imagine you have a simple database for authentication with these tables:

User
- Id INT AUTOINCREMENT
- UserName VARCHAR(100)
- Password VARCHAR(MAX)
- Salt VARCHAR(MAX)

Person
- Id INT AUTOINCREMENT
- FirstName VARCHAR(255)
- LastName VARCHAR(255)
- Birthdate DATETIME
- UserId int FK to User.Id

Now imagine that you want to query these tables and use the data in your application.

Step 1 – Create a Visual Studio Project

  1. In visual studio create a new C# Console Application project.
  2. Once you have the project created, click on Project | Add New Data Source.
  3. Select Database and click Next.
  4. Select DataSet and click Next.
  5. Click New Connection and follow the wizard to connect to your database.
  6. Make sure that Yes, save the connection as is checked and give your saved connection a name and click Next.
  7. Click the checkbox next to Tables and click Finish.

This adds the following files to your project (the names might be slightly different on yours):

  • AuthDataSet.xsd
  • AuthDataSet.Designer.cs
  • AuthDataSet.xsc
  • AuthDataSet.xss

This code will add table adapters to your project. This basically does a lot of work for you and can save you a lot of potential development time.

Step 2 – Query a SQL Database using the Table Adapter

Now you can get the data from either of your tables with one line of code:

using System;
using System.Data;
using TableAdapterExample.AuthDataSetTableAdapters;

namespace TableAdapterExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Query the database (select * from Person) into a DataTable
            AuthDataSet.PersonDataTable table = new PersonTableAdapter().GetData();

            // Print out the table as proof.
            PrintDataTable(table);
        }

        /// How to print a DataTable
        private static void PrintDataTable(AuthDataSet.PersonDataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn col in table.Columns)
                {
                    Console.Write(row[col].ToString().Trim() + " ");
                }
                Console.WriteLine();
            }
        }
    }
}

Hope that helps you.

How to execute a sql statement with a parameter in SQL Management Studio?

Today I got asked how to execute a parameterized SQL query in SQL Management Studio.

You can do it two ways:

Method 1 – Use SP_EXECUTESQL stored procedure to execute parameterized SQL

DECLARE @query NVARCHAR(MAX) = N'SELECT * FROM customer WHERE CustomerId = @id'
DECLARE @params NVARCHAR(MAX) = N'@id int';

EXEC SP_EXECUTESQL @query, @params, @id = 3;

If you need to do multiple parameters, it is pretty much the same syntax.

DECLARE @query NVARCHAR(MAX) = N'SELECT * FROM customer WHERE CustomerId IN (@id1, @id2)'
DECLARE @params NVARCHAR(MAX) = N'@id1 int, @id2 int';

EXEC SP_EXECUTESQL @query, @params, @id1 = 3, @id2 = 4;

Method 2 – DECLARE Variables to execute parameterized SQL

DECLARE @id INT = 3;
SELECT * FROM customer WHERE CustomerId = @id;

If you need to do multiple parameters, just declare another variable.

DECLARE @id1 INT = 3;
DECLARE @id2 INT = 4;
SELECT * FROM customer WHERE CustomerId IN (@id1, @id2);

SQL INSERT statement syntax is poorly designed

Am I on the only in the technical community that is completely annoyed by SQL’s INSERT statement syntax? Especially with long INSERT queries.

Why in the world do we have to have the column and its value separated.

Imagine this table, which is an overly simplified example only version of a Person table.

FirstName MiddleName LastName Birthday UserName Password PhoneNumber Address SignupDate LastUpdated LastPasswordChange

Please not that this table only has 10 columns. I am working with tables with three of four times as many columns. Also these columns are pretty simple. Many columns have name like PK_D or other cryptic titles.

So wouldn’t it be nice to have a syntax that more easily connects the column and the value?

Lets look at the insert syntax:

INSERT INTO Person (FirstName, MiddleName, LastName, Birthday, UserName, Password, PhoneNumber, Address, SignupDate, LastUpdated, LastPasswordChange)
VALUES ('John', 'J.', 'Doe', '3/27/1977', 'jdoe', 'p@ssw0rd!, '555-555-5555, '1234 ABCD Street SomeCity, Utah 81234', '12/1/2012', '3/20/2013', '3/13/2013')

Ok, so you can start to see how even with these easy and well-known columns, it starts to get confusing as to which order you need to enter data. Also, you have to scroll right to left a lot. Also, in whichever order you type your column names, you have to enter you data.

Even with every value on its own line, this is still not very clear.

INSERT INTO Person (
FirstName, 
MiddleName, 
LastName, 
Birthday, 
UserName, 
Password, 
PhoneNumber, 
Address, 
SignupDate, 
LastUpdated, 
LastPasswordChange)
VALUES (
'John', 
'J.', 
'Doe', 
'3/27/1977', 
'jdoe', 
'p@ssw0rd!, 
'555-555-5555, 
'1234 ABCD Street SomeCity, Utah 81234', 
'12/1/2012', 
'3/20/2013', 
'3/13/2013')

Now it is a lot of lines, and you have just swapped extensive left and right scrolling to up and down scrolling. Remember, this is a small table. Some tables have many times more columns.

Of course you can get around some of this using default values and allowing NULLs so you don’t have to insert values for those columns. But if you have 30+ required columns, which the database I am working with has, then that doesn’t help. No, I am not in control of the database or I would likely change it.

Why is the syntax not something that is order insignificant?

INSERT INTO Person
FirstName='John', MiddleName='J.', LastName='Doe', Birthday='3/27/1977'
UserName='jdoe', Password='p@ssw0rd!, PhoneNumber='555-555-5555,
Address='1234 ABCD Street SomeCity, Utah 81234', SignupDate='12/1/2012',
LastUpdate='3/20/2013', LastPasswordChange='3/13/2013'

Or all on its own line:

INSERT INTO Person
FirstName='John', 
MiddleName='J.', 
LastName='Doe', 
Birthday='3/27/1977'
UserName='jdoe', 
Password='p@ssw0rd!, 
PhoneNumber='555-555-5555,
Address='1234 ABCD Street SomeCity, Utah 81234', 
SignupDate='12/1/2012',
LastUpdate='3/20/2013', 
LastPasswordChange='3/13/2013'

Is it just me who thinks that the standard property=value syntax is a quite a bit more readable and a lot more writable?

I am sure there are all kinds of reasons why this syntax isn’t used. I am not saying property=value syntax is a fix to the horrible INSERT syntax. I am just saying that the INSERT syntax is horrible and I would recommend that future version of SQL provide and more readable a more writable alternative.

How to export table data into a SQL insert script?

Have you ever had a table that you wanted to drop and recreate but you need all the data inside it. Perhaps it took you a lot of time to generate this data.

Well, you can save the data out as a SQL insert script if you want. Here is how:

  1. Open Microsoft SQL Server Management Studio and login.
  2. Right-click on the database and choose Tasks | Generate Scripts.
    Note: The first time you do this you will see and Introduction page. Click Next. You can click the check box next to Do not show this page again and you won’t see this screen.
  3. On the Choose Objects screen, click Select specific database objects.
  4. Expand Tables and click the check box next to the table you want to save and click Next.
  5. On the Set Scripting Options screen, click Advanced.
  6. Scroll down and under Types of data to script select Data only and click OK.
  7. Under File name select a file name to save to and click Next.
  8. Click Next, Next, Finish.

You now have a script to insert your data.

How to install MySQL on FreeBSD

Note: Tested on FreeBSD 9

Step 1 – Install FreeBSD

  1. First install FreeBSD. Instructions for installing FreeBSD is contained in this article.
    How I install FreeBSD 9?
    (Legacy) How I install FreeBSD?
  2. Second update FreeBSD and install the ports tree. Instructions for this are in this article.
    What are the first commands I run after installing FreeBSD?

Step 2 – Installing MySQL

Install MySQL from Ports

  1. Change to the directory of the mysql55-server port.
    # cd /usr/ports/databases/mysql55-server
  2. Now install mysql55-server with ‘make install’.
    # make install

    MySQL 5.5 Server (and MySQL 5.5 client) will download, compile, and install automagically for you.

    Note: You may be wondering about the WITH_CHARSET option that used to exist. This is not necessary during compile and install and we will set the character set in a later step. Don’t start the MySQL service until we make these changes.

Installing MySQL from Packages

  1. Install easily as a binary package with this simple command.
    pkg_add -r mysql55-server

Step 3 – Configure MySQL

Configuration of MySQL is done in the my.cnf file.

Example 1 – Configuring mysql to use UTF8

For this example, we will change our server to use UTF8.

  1. Change to the /usr/local/etc/ directory. This is the default location for the my.cnf file.
    cd /usr/local/etc/
  2. Add the following to the my.cnf file.
    # # # > /usr/local/etc/my.cnf echo '[mysqld]' >> /usr/local/etc/my.cnf echo character-set-server=utf8 >> /usr/local/etc/my.cnf echo collation-server=utf8_general_ci

Note: FreeBSD has multiple example my.cnf files here: /usr/local/share/

  • my-huge.cnf
  • my-innodb-heavy-4G.cnf
  • my-large.cnf
  • my-medium.cnf
  • my-small.cnf

Step 4 – Configure MySQL to start on boot

  1. Add the following lines to the /etc/rc.conf file.
    #
    #
    echo # MySQL 5.5 Server >> /etc/rc.conf
    echo 'mysql_enable="YES"' >> /etc/rc.conf
  2. Now start your server.
    # /usr/local/etc/rc.d/mysql-server start

Step 5 – Secure your MySQL installation

MySQL documentation covers this and I’ll not repeat it here. Instead, go here:
2.2 Securing the Initial MySQL Accounts

Integration with Apache and PHP

If you want to integrate Apache and PHP see these articles.

Why Technical Support Engineers are not all the same!

Technical Support Engineers are not all the same. There is an inclination in the industry to look down on Technical Support Engineers.

Recently the following article was published:
10 IT positions ranked by prestige

This article didn’t exactly identify the Technical Support Engineer role, but it was unfortunately encompassed in the bottom two positions with the lowest prestige, Technical and Help Desk Analyst.

Should a Technical Support Engineer have the lowest prestige of all technical jobs in the industry? If you think so, you might want to reconsider after read this.

There are multiple levels of technical support and you should know what level of technical support a person is in because that should significantly change your view of this persons technical skills and ability.

What they support and to what level they support it makes a major difference in how to view a Technical Support Engineers background.

Obviously there is a difference between someone who does tech support for a company like Cisco, Microsoft, LANDesk than someone who does technical support for a BowFlex. But this is an obvious difference. A chart that is more of gradient is needed.

Here is some information to help guide you in determining what experience a Technical Support Engineer really has in the technology industry.

1 – Complex product that requires knowledge of an entire area of technology, including both software and hardware environments

These engineers are often not just support engineers. Along with being an expert on their product, they must understand many other concepts such as Networking, Servers and server-side software such as Web Servers, Database Servers, DNS servers, DHCP servers and more. It is not enough to just know how to set up their software, they have to know how to set up the environment around it. They also have to know how to troubleshoot to deep levels both their software and the environment around it.

Usually these engineers practice during portions of their job being Systems Analysts, Consultants, Sales Engineers, IT administrators, Change Controls administrators, developers, and more. They deal will the full gamut of technology and all the areas around it.

Example companies

Desktop Management companies such as LANDesk, SCCM, Kace.
Network Manager Software such as HP OpenView

2 – Specialized product that requires knowledge of one major portion of an IT or Software Environment

These engineers are often not just support engineers. Along with being an expert on their product, they must understand many other concepts in the technology world. It is not enough to just know how to set up their software, they have to know how to set up some portion, though not all, of the environment around it. They also have to know how to troubleshoot to deep levels both their software and their portion of the environment around it.

Like above, these engineers have some limited consulting experience and are gaining understanding of change control and IT administrative processes along with being technology experts.

Example companies

Support for Network Equipment such as Cisco, Juniper Networks, etc…

3 – Specialized product that requires knowledge of a single area of an IT or Software Environment

This person is an expert on their software or hardware product as well as an expert in one or more areas surrounding it.

Individuals who excel here are usually are ready to explode into a new technology after a little as one year in this position.

Example Companies

Business intelligence software, such as QlikView.
Dell, HP and other computer resellers.
Simple Appliances, such as a NAS.
Any company’s internal Computer Help Desk (but be aware of glorified password resetters)

4- Specialized product that requires technical knowledge but only for that exact product

This person is an expert on their software or hardware product but there is not indication they know anything else about technology from this position, which doesn’t mean they don’t, just that this position doesn’t indicate it.

Example Companies

Home consumer products such as Wireless Routers from D-Link, Linksys, etc.

5 – Specialized product that requires knowledge in an area outside of IT but still somewhat technical

This person is an expert on their software or hardware product as well as an expert in one or more areas surrounding it.
Usually this product has interfaces into other technology that not used commonly but Technical Support Engineers usually don’t take the common calls for things that just work, they learn the tough issues, which usually involves integrating with something else. However, they don’t always know that area of technology, just the minimal knowledge to make their product work with it.

Example companies

Software Applications outside of IT: Microsoft Word, Excel.

6 – Specialized product that requires knowledge in an area outside of IT but not exactly technical

This person is an expert on their software or hardware product as well as an expert in one or more areas surrounding it. This expertise is beneficial outside the position but only in limited areas.

Example companies

Software Applications outside of IT:
Adobe Photoshop
Dentrix
gaming software

7 – Specialized product that requires knowledge that is technical but not really related to software or IT at all

This person is an expert product but it is just a simple product that being an expert on it really has no value anywhere else.

Example companies

Television
Cable box companies
radios and sound systems
Cell-phones

8 – Generic product that requires little technical knowledge

This person usually supports something that is sold on a made-for-tv ad, such as an exercise appliance. They usually have a script they follow and this position can be filled by almost anyone who can read and speak.

Example companies

BowFlex
Clock Radios

Backing up PostgreSQL databases nightly on FreeBSD

I didn’t write this post, a blogger named Keith did, but I am definitely going to link to his work on his blog.

Backing up PostgreSQL databases nightly on FreeBSD

He did a good job editing this script to backup postgresql on FreeBSD. While he does say he got some of it from another site he found on using Google…

…Using Google I found a generic script, after some custom tweaks for FreeBSD I had the script I wanted.

…so some of the work should be attributed elsewhere. I want to thank him for the work he did. This was something that was on my to do list and now it is pretty much done for me.

Here is the script he posted.

#!/bin/sh
# Location of binaries
bin="/usr/local/bin"
# Location of the backup logfile.
logfile="/data/backup/postgres/postgres.log"
# Location to place backups.
backup_dir="/data/backup/postgres"
username="pgsql"
database="template1"
touch $logfile
timeslot=`date +%H-%M`
databases=`$bin/psql -h localhost -d $database -U $username -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        $bin/vacuumdb -z -h localhost -U $username $i >/dev/null 2>&1
        j="60"
        while [ $j -ge 0 ]
        do
                temp0=`expr $j - 1`
                temp1=$j
                j=`expr $j - 1`
                if [ $temp0 -lt 0 ]
                then
                        suffix0=""
                        suffix1=".0"
                else
                        suffix0=".$temp0"
                        suffix1=".$temp1"
                fi
                if [ -f $backup_dir/postgresql-$i-database.gz$suffix0 ]
                then
                        echo "Renaming postgresql-$i-database.gz$suffix0 to postgresql-$i-database.gz$suffix1"
                        mv $backup_dir/postgresql-$i-database.gz$suffix0 $backup_dir/postgresql-$i-database.gz$suffix1
                fi
        done
        if [ $i != 'template0' ]
        then
                $bin/pg_dump -U $username $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-database.gz"
        fi
done

Here is what I like about his script:

  1. Variables are used at the top of the script that are easily modifiable.
  2. It backs up all databases without having to list the databases, so you don’t have to update the list with every new database.
  3. It does compress using gzip the databases, so uses as little space as possible.

Here are some ideas for future enhancements to the script

  1. The gzip compression level as a variable up top. The default gzip compression level is 6 and 9 could make the file significantly smaller with large databases, while 1 would be faster for machines with weaker processors.
  2. Database to exclude from the backup.  I like the idea to get them all by default, but sometimes you have a test database you just don’t care about and you don’t want a nightly backup of it.
  3. I don’t see the password in the script, and I am not sure how it is authenticating without it, maybe a password feature needs to be added.