SqlBuilder.cs source code in C# .NET

                        Code:
                         / 4.0 / 4.0 / untmp / DEVDIV_TFS / Dev10 / Releases / RTMRel / ndp / fx / src / DLinq / Dlinq / SqlClient / SqlBuilder.cs / 1305376 / SqlBuilder.cs
                        
                        
                            using System; 
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
using System.IO; 
using System.Linq;
using System.Linq.Expressions; 
using System.Reflection; 
using System.Text;
 
namespace System.Data.Linq.SqlClient {
    using System.Data.Linq.Mapping;
    using System.Data.Linq.Provider;
    using System.Diagnostics.CodeAnalysis; 

    ///  
    /// Class for building up SQL DDL commands. 
    /// 
    internal static class SqlBuilder { 

        internal static string GetCreateDatabaseCommand(string catalog, string dataFilename, string logFilename) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE DATABASE {0}", SqlIdentifier.QuoteIdentifier(catalog)); 
            if (dataFilename != null) {
                sb.AppendFormat(" ON PRIMARY (NAME='{0}', FILENAME='{1}')", Path.GetFileName(dataFilename), dataFilename); 
                sb.AppendFormat(" LOG ON (NAME='{0}', FILENAME='{1}')", Path.GetFileName(logFilename), logFilename); 
            }
            return sb.ToString(); 
        }

        internal static string GetDropDatabaseCommand(string catalog) {
            StringBuilder sb = new StringBuilder(); 
            sb.AppendFormat("DROP DATABASE {0}", SqlIdentifier.QuoteIdentifier(catalog));
            return sb.ToString(); 
        } 

        internal static string GetCreateSchemaForTableCommand(MetaTable table) { 
            StringBuilder sb = new StringBuilder();
            List parts = new List(SqlIdentifier.GetCompoundIdentifierParts(table.TableName));

            // table names look like this in Yukon (according to MSDN): 
            //     [ database_name . [ schema_name ] . | schema_name . ] table_name
            // ... which means that either way, the schema name is the second to last part. 
 
            if ((parts.Count) < 2) {
                return null; 
            }

            string schema = parts[parts.Count - 2];
            if (String.Compare(schema, "DBO", StringComparison.OrdinalIgnoreCase) != 0 && 
                String.Compare(schema, "[DBO]", StringComparison.OrdinalIgnoreCase) != 0) {
                sb.AppendFormat("CREATE SCHEMA {0}", SqlIdentifier.QuoteIdentifier(schema)); 
            } 
            return sb.ToString();
        } 

        internal static string GetCreateTableCommand(MetaTable table) {
            StringBuilder sb = new StringBuilder();
            StringBuilder decl = new StringBuilder(); 
            BuildFieldDeclarations(table, decl);
            sb.AppendFormat("CREATE TABLE {0}", SqlIdentifier.QuoteCompoundIdentifier(table.TableName)); 
            sb.Append("("); 
            sb.Append(decl.ToString());
            decl = new StringBuilder(); 
            BuildPrimaryKey(table, decl);
            if (decl.Length > 0) {
                string name = String.Format(Globalization.CultureInfo.InvariantCulture, "PK_{0}", table.TableName);
                sb.Append(", "); 
                sb.AppendLine();
                sb.AppendFormat("  CONSTRAINT {0} PRIMARY KEY ({1})", SqlIdentifier.QuoteIdentifier(name), decl.ToString()); 
            } 
            sb.AppendLine();
            sb.Append("  )"); 
            return sb.ToString();
        }

        internal static void BuildFieldDeclarations(MetaTable table, StringBuilder sb) { 
            int n = 0;
            Dictionary memberNameToMappedName = new Dictionary(); 
            foreach (MetaType type in table.RowType.InheritanceTypes) { 
                n += BuildFieldDeclarations(type, memberNameToMappedName, sb);
            } 
            if (n == 0) {
                throw Error.CreateDatabaseFailedBecauseOfClassWithNoMembers(table.RowType.Type);
            }
        } 

        private static int BuildFieldDeclarations(MetaType type, Dictionary memberNameToMappedName, StringBuilder sb) { 
            int n = 0; 
            foreach (MetaDataMember mm in type.DataMembers) {
                // Only generate declarations for the current type. 
                if (mm.IsDeclaredBy(type)) {
                    if (!mm.IsAssociation) {
                        if (mm.IsPersistent) {
                            object dn = InheritanceRules.DistinguishedMemberName(mm.Member); 
                            string mappedName;
                            if (memberNameToMappedName.TryGetValue(dn, out mappedName)) { 
                                if (mappedName == mm.MappedName) { 
                                    continue;
                                } 
                            }
                            else {
                                memberNameToMappedName.Add(dn, mm.MappedName);
                            } 
                            if (sb.Length > 0) {
                                sb.Append(", "); 
                            } 
                            sb.AppendLine();
                            sb.Append(string.Format(Globalization.CultureInfo.InvariantCulture, "  {0} ", SqlIdentifier.QuoteCompoundIdentifier(mm.MappedName))); 
                            if (!string.IsNullOrEmpty(mm.Expression)) {
                                // Generate "AS " for computed columns
                                sb.Append("AS " + mm.Expression);
                            } 
                            else {
                                sb.Append(GetDbType(mm)); 
                            } 
                            n++;
                        } 
                    }
                }
            }
            return n; 
        }
 
        private static void BuildPrimaryKey(MetaTable table, StringBuilder sb) { 
            foreach (MetaDataMember mm in table.RowType.IdentityMembers) {
                if (sb.Length > 0) { 
                    sb.Append(", ");
                }
                sb.Append(SqlIdentifier.QuoteCompoundIdentifier(mm.MappedName));
            } 
        }
 
        private static string BuildKey(IEnumerable members) { 
            StringBuilder sb = new StringBuilder();
            foreach (MetaDataMember mm in members) { 
                if (sb.Length > 0) {
                    sb.Append(", ");
                }
                sb.Append(SqlIdentifier.QuoteCompoundIdentifier(mm.MappedName)); 
            }
            return sb.ToString(); 
        } 

        internal static IEnumerable GetCreateForeignKeyCommands(MetaTable table) { 
            foreach (MetaType type in table.RowType.InheritanceTypes) {
                foreach (string command in GetCreateForeignKeyCommands(type)) {
                    yield return command;
                } 
            }
        } 
 
        private static IEnumerable GetCreateForeignKeyCommands(MetaType type) {
            string tableName = type.Table.TableName; 
            foreach (MetaDataMember mm in type.DataMembers) {
                if (mm.IsDeclaredBy(type) && mm.IsAssociation) {
                    MetaAssociation assoc = mm.Association;
                    if (assoc.IsForeignKey) { 
                        StringBuilder sb = new StringBuilder();
                        string thisKey = BuildKey(assoc.ThisKey); 
                        string otherKey = BuildKey(assoc.OtherKey); 
                        string otherTable = assoc.OtherType.Table.TableName;
                        string name; 
                        name = mm.MappedName;
                        if (name == mm.Name) {
                            name = String.Format(Globalization.CultureInfo.InvariantCulture, "FK_{0}_{1}", tableName, mm.Name);
                        } 
                        string cmd = "ALTER TABLE {0}" + Environment.NewLine + "  ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}({4})";
                        //In DLinq we put the constraint on the child object (which triggers the behavior when deleted), 
                        //but in SQL it is part of the parent constraint (the parent row gets changed / deleted to satisfy the constraint) 
                        MetaDataMember otherMember = mm.Association.OtherMember;
                        if (otherMember != null) { 
                            string delConstr = otherMember.Association.DeleteRule;
                            if (delConstr != null) {
                                cmd += Environment.NewLine + "  ON DELETE " + delConstr;
                            } 
                        }
                        sb.AppendFormat(cmd, 
                            SqlIdentifier.QuoteCompoundIdentifier(tableName), 
                            SqlIdentifier.QuoteIdentifier(name),
                            SqlIdentifier.QuoteCompoundIdentifier(thisKey), 
                            SqlIdentifier.QuoteCompoundIdentifier(otherTable),
                            SqlIdentifier.QuoteCompoundIdentifier(otherKey));
                        yield return sb.ToString();
                    } 
                }
            } 
        } 

        [SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity", Justification="These issues are related to our use of if-then and case statements for node types, which adds to the complexity count however when reviewed they are easy to navigate and understand.")] 
        private static string GetDbType(MetaDataMember mm) {
            string dbType = mm.DbType;

            if (dbType != null) return dbType; 
            StringBuilder sb = new StringBuilder();
 
            Type type = mm.Type; 
            bool isNullable = mm.CanBeNull;
            if (type.IsValueType && IsNullable(type)) { 
                type = type.GetGenericArguments()[0];
            }

            if (mm.IsVersion) { 
                sb.Append("Timestamp");
            } 
            else { 
                if (mm.IsPrimaryKey && mm.IsDbGenerated) {
                    switch (Type.GetTypeCode(type)) { 
                        case TypeCode.Byte:
                            sb.Append("TinyInt");
                            break;
                        case TypeCode.SByte: 
                        case TypeCode.Int16:
                            sb.Append("SmallInt"); 
                            break; 
                        case TypeCode.Int32:
                        case TypeCode.UInt16: 
                            sb.Append("Int");
                            break;
                        case TypeCode.Int64:
                        case TypeCode.UInt32: 
                            sb.Append("BigInt");
                            break; 
                        case TypeCode.UInt64: 
                        case TypeCode.Decimal:
                            sb.Append("Decimal(20)"); 
                            break;
                        case TypeCode.Object:
                            if (type == typeof(Guid)) {
                                sb.Append("UniqueIdentifier"); 
                            } else {
                                throw Error.CouldNotDetermineDbGeneratedSqlType(type); 
                            } 
                            break;
                    } 
                }
                else {
                    switch (Type.GetTypeCode(type)) {
                        case TypeCode.Boolean: 
                           sb.Append("Bit");
                            break; 
                        case TypeCode.Byte: 
                            sb.Append("TinyInt");
                            break; 
                        case TypeCode.SByte:
                        case TypeCode.Int16:
                            sb.Append("SmallInt");
                            break; 
                        case TypeCode.Int32:
                        case TypeCode.UInt16: 
                            sb.Append("Int"); 
                            break;
                        case TypeCode.Int64: 
                        case TypeCode.UInt32:
                            sb.Append("BigInt");
                            break;
                        case TypeCode.UInt64: 
                            sb.Append("Decimal(20)");
                            break; 
                        case TypeCode.Decimal: 
                            sb.Append("Decimal(29, 4)");
                            break; 
                        case TypeCode.Double:
                            sb.Append("Float");
                            break;
                        case TypeCode.Single: 
                            sb.Append("Real");
                            break; 
                        case TypeCode.Char: 
                            sb.Append("NChar(1)");
                            break; 
                        case TypeCode.String:
                            sb.Append("NVarChar(4000)");
                            break;
                        case TypeCode.DateTime: 
                            sb.Append("DateTime");
                            break; 
                            case TypeCode.Object: 
                                if (type == typeof(Guid)) {
                                    sb.Append("UniqueIdentifier"); 
                                } else if (type == typeof(byte[])) {
                                    sb.Append("VarBinary(8000)");
                                } else if (type == typeof(char[])) {
                                    sb.Append("NVarChar(4000)"); 
                                } else if (type == typeof(DateTimeOffset)) {
                                    sb.Append("DateTimeOffset"); 
                                } else if (type == typeof(TimeSpan)) { 
                                    sb.Append("Time");
                                } else { 
                                    throw Error.CouldNotDetermineSqlType(type);
                                }
                                break;
                     } 
                }
            } 
 
            if (!isNullable) {
                sb.Append(" NOT NULL"); 
            }

            if (mm.IsPrimaryKey && mm.IsDbGenerated) {
                if (type == typeof(Guid)) { 
                    sb.Append(" DEFAULT NEWID()");
                } 
                else { 
                    sb.Append(" IDENTITY");
                } 
            }

            return sb.ToString();
        } 

        internal static bool IsNullable(Type type) { 
            return type.IsGenericType && typeof(Nullable<>).IsAssignableFrom(type.GetGenericTypeDefinition()); 
        }
    } 
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
// Copyright (c) Microsoft Corporation. All rights reserved.
Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK
SqlBuilder.cs source code in C# .NET

Source code for the .NET framework in C#

Code:

Link Menu
