Rhyous

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.

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.

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

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.

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?

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.

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?

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?

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]<>''

replace([UserName],char((9)),'')

replace([UserName],char((10)),'')

replace([UserName],char((13)),'')

ltrim([UserName])

rtrim([UserName])

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.

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>

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.

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);

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.

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.

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.

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.

• Installing an Apache + SSL on FreeBSD using the ports tree
• How to install PHP5 and PHP5 Extensions on FreeBSD?

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.

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.