Dot Net

Syrinx .NET Development Blog
Need help on your project? info@syrinx.com, or toll free (888) 579-7469, press 1

News



Need help with your .NET Development project?

Syrinx works with clients throughout New England to architect, design, develop, and deploy .NET Applications. Working on fully outsourced projects, as part of your team, helping to train your team, or rescuing projects in trouble, we are comfortable doing it all. Projects from a couple weeks to several months in duration, reference clients available. Contact us today - info@syrinx.com, or toll free (888) 579-7469 and press 1 to speak to someone now!

Part 4 - Tables Creation in SQL Server using SMO

In this installation of my blog series I’ll cover defining, creating and persisting SQL scripts for tables based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO).

The script for creating tables and then saving the SQL script for the table creation is relatively straightforward.  We go through each of the properties in our property list and if it is a primitive type (i.e. not a class or class list) then we add that property as a field in our table. Once complete we create the table and then save the script that was created for the table into a .sql file.  The TableManager class used to complete this is as follows, the comments have been added to show the interesting parts of the code:

    public class TableManager    {        private const string SQL_FILE_EXTENSION = ".sql";        private const string EXTENDED_DESCRIPTION_IDENTIFIER = "MS_Description";         public static void CreateTable(string Namespace, string ClassName, string Prefix, bool UsePrefix,                                             CodeGeneratorPropertyList PropertyList,                                            string FolderName, bool PersistToDatabase, Database SelectedDatabase)        {            string newPrefix = string.Empty;            if (UsePrefix)            {                if (Prefix.Length == 0)                {                    newPrefix = Namespace;                }                else                {                    newPrefix = Prefix;                }            }            string tableName = GetTableName(newPrefix, ClassName);            string fileName = string.Format("{0}\\{1}{2}", FolderName, tableName, SQL_FILE_EXTENSION);            string tableScriptContent = string.Empty;             Server server = SelectedDatabase.Parent;            if (PersistToDatabase) // Executiuon mode determines persistence to database            {                server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;            }            else            {                server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;            }             // Creating New Table            Table tbl = new Table(SelectedDatabase, tableName);             // Go through all of the properties in the property list            foreach (CodeGeneratorProperty property in PropertyList)            {                // Ensure that the property isn’t a class or class list                if (CommonHelper.IsValidDatabaseDataType(property))                {                    Column col = new Column(tbl, property.PropertyName, CommonHelper.GetDataType(property));                    // Make the primary key an Id column and give it  a PK index                    if (property.IsPrimaryKey)                    {                        // Add Constraint                        if (property.DataType.Equals("Guid"))                        {                            col.AddDefaultConstraint(GetDefaultConstraintName(property.PropertyName)).Text = "newid()";                        }                        else if (property.DataType.Equals("int"))                        {                            col.Identity = true;                            col.IdentityIncrement = 1;                            col.IdentitySeed = 1;                        }                        // Create a PK Index on Table                        Index idx = new Index(tbl, GetPrimaryKeyName(property.PropertyName));                        idx.IndexKeyType = IndexKeyType.DriPrimaryKey;                        idx.IndexedColumns.Add(new IndexedColumn(idx, property.PropertyName));                        tbl.Indexes.Add(idx);                         col.Nullable = false;                    }                    else                    {                        col.Nullable = !property.IsRequired;                    }                    // Add the description                    ExtendedProperty desc =                        new ExtendedProperty(col, EXTENDED_DESCRIPTION_IDENTIFIER, property.Description);                    col.ExtendedProperties.Add(desc);                    tbl.Columns.Add(col);                }            }             // Attempt to Drop the Table and add that drop code to the beginning of the script content string             tableScriptContent = AttemptTableDrop(SelectedDatabase, tableName);            // Create Table - if SqlExecutionModes is CaptureSql then the table             // is not actually created on the database            tbl.Create();             // Get Script            StringCollection commands = server.ConnectionContext.CapturedSql.Text;            foreach (string s in commands)            {                tableScriptContent += string.Format("{0}\n", s);            }            CodeGenerationHelper.WriteFile(fileName, tableScriptContent);        }         private static string AttemptTableDrop(Database SelectedDatabase, string TableName)        {            string procedureContent =                 string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND
 
                                type in (N'U')){1}", TableName, CodeGenerationHelper.GetEndOfLine());
            procedureContent += string.Format("DROP TABLE [dbo].[{0}]", TableName) +
                                 CodeGenerationHelper.GetEndOfLine();
            procedureContent += CodeGenerationHelper.GetEndOfLine();            SelectedDatabase.ExecuteNonQuery(procedureContent);            return procedureContent;        }         private static string GetPrimaryKeyName(string PropertyName)        {            return string.Format("PK_{0}", PropertyName);        }         private static string GetDefaultConstraintName(string PropertyName)        {            return string.Format("DF_{0}", PropertyName);        }         private static string GetTableName(string Prefix, string ClassName)        {            string newPrefix = string.Empty;            if (Prefix.Length > 0)            {                newPrefix = string.Format("{0}_", Prefix);            }            return string.Format("{0}{1}", newPrefix, ClassName);        }

    }

That wraps it up for this installation, next we’ll look at defining, creating and persisting SQL scripts for stored procedures based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO).  This takes an entirely different approach than table generation.  Where tables are created and then the script is generated from the execution, stored procedures require us to create the sql script and then execute it against the server.

 

Comments

codegenerator said:

Pingback from  codegenerator

# May 8, 2008 2:17 AM

Corvette Cooler Beer, Corvette Promotional Models Collectibles said:

Pingback from  Corvette Cooler Beer, Corvette Promotional Models Collectibles

# May 21, 2010 5:43 PM

Aztek G6 Used Pontiac Sunfire, Aztek Tent Package Camping said:

Pingback from  Aztek G6 Used Pontiac Sunfire, Aztek Tent Package Camping

# May 21, 2010 9:16 PM

Lacrosse Business For Sale Turnkey, War Lacrosse Balls said:

Pingback from  Lacrosse Business For Sale Turnkey, War Lacrosse Balls

# May 22, 2010 1:10 PM

280zx Chevelle, 280zx Headlight Sealed Beam Head Lamp said:

Pingback from  280zx Chevelle, 280zx Headlight Sealed Beam Head Lamp

# May 22, 2010 4:10 PM

1980 240d Specs Mercedes Benz 500e, 240d Parts Sports Catalog - 489.eumreborn.com said:

Pingback from  1980 240d Specs Mercedes Benz 500e, 240d Parts Sports Catalog - 489.eumreborn.com

# May 23, 2010 9:31 AM

Montego Radiator Current Replacement, Montego Performance List - 339.jeepsunlimted.com said:

Pingback from  Montego Radiator Current Replacement, Montego Performance List - 339.jeepsunlimted.com

# May 24, 2010 8:34 PM

Mercedes E55 Amg Sale E320 Bluetec Benz Slk, 1998 E320 Sedan Mercedes Benz E Class - 225.jeepsunlimted.com said:

Pingback from  Mercedes E55 Amg Sale E320 Bluetec Benz Slk, 1998 E320 Sedan Mercedes Benz E Class - 225.jeepsunlimted.com

# May 25, 2010 12:56 AM

I850 Cooling System Lcs, Pn58b850 Srs Trusurround - 234.ja3ra.com said:

Pingback from  I850 Cooling System Lcs, Pn58b850 Srs Trusurround - 234.ja3ra.com

# May 25, 2010 4:11 AM

E 250 Data Econoline Car, 1997 Ford E250 Econoline - 296.rkwrh.com said:

Pingback from  E 250 Data Econoline Car, 1997 Ford E250 Econoline - 296.rkwrh.com

# May 25, 2010 5:17 AM

Lw300 Sale Saturn L Series Interior, Christie Lw300 Lens Shift Bright - 432.mfbattle.com said:

Pingback from  Lw300 Sale Saturn L Series Interior, Christie Lw300 Lens Shift Bright - 432.mfbattle.com

# May 25, 2010 7:04 AM

Rd200 Fluid Click Here, D200 Pickup Discount Grand Caravan - 394.1fh.org said:

Pingback from  Rd200 Fluid Click Here, D200 Pickup Discount Grand Caravan - 394.1fh.org

# May 25, 2010 2:11 PM

Glc Auction Centrifuge, Glc Available Discount Prices - 417.codebluehacks.org said:

Pingback from  Glc Auction Centrifuge, Glc Available Discount Prices - 417.codebluehacks.org

# May 25, 2010 4:14 PM

2002 Ford Contour Truck Parts, Parts Ford Contour Accessories Oxygen Sensor - 391.defutbolazo.com said:

Pingback from  2002 Ford Contour Truck Parts, Parts Ford Contour Accessories Oxygen Sensor - 391.defutbolazo.com

# May 25, 2010 6:02 PM

Parts 1995 Club Wagon Ford E150 Crown Victoria, 1993 Ford E150 Msrp - 267.ja3ra.com said:

Pingback from  Parts 1995 Club Wagon Ford E150 Crown Victoria, 1993 Ford E150 Msrp - 267.ja3ra.com

# May 25, 2010 7:30 PM

1995 - 1983 @ 400sel Secret, 400sel Auto Fit - 32.unlockiphone30.net said:

Pingback from  1995 - 1983 @ 400sel Secret, 400sel Auto Fit - 32.unlockiphone30.net

# May 27, 2010 9:38 AM