Example #1
0
        public void CreateView(string instanceName)
        {
            var connectionString = "server=(localdb)\\" + instanceName + ";" +
                                   "Trusted_Connection=yes;" +
                                   "database=" + Database + @"; " +
                                   "Integrated Security=true; ";

            using (var db = new ADODatabaseContext(connectionString))
            {
                // first, drop the view if it already exists
                var sp = @"if exists (select * from sys.views where name = N'" + Name + @"' and type = N'P') 
                          begin
                            drop view " + Name + @"
                          end";
                db.ExecuteNonQuery(sp);

                // need to read the text file and create the view in the test database
                var tsqlCommandList = Regex.Split(Code, "GO");

                foreach (var tsqlCommand in tsqlCommandList)
                {
                    db.ExecuteNonQuery(tsqlCommand);
                }
            }
        }
Example #2
0
        /// <summary>
        /// Execute any query stored in a resource file against your SQLLocalDB instance.
        /// </summary>
        /// <param name="filePath">Full namespace path to the embedded resource file</param>
        /// <param name="database"></param>
        public static void ExecuteSQLCodeFromFile(string filePath, string database)
        {
            using (var db = new ADODatabaseContext("TEST", database))
            {
                var assembly = Assembly.GetCallingAssembly();
                using (var stream = assembly.GetManifestResourceStream(filePath))
                {
                    if (stream == null)
                    {
                        throw new Exception("Cannot find SQL query file, make sure it is set to Embedded Resource!");
                    }

                    using (var reader = new StreamReader(stream))
                    {
                        var code            = reader.ReadToEnd();
                        var tsqlCommandList = Regex.Split(code, "GO");

                        foreach (var tsqlCommand in tsqlCommandList)
                        {
                            if (tsqlCommand.Trim() != "")
                            {
                                db.ExecuteNonQuery(tsqlCommand.Replace("\n", "").Replace("\r", ""));
                            }
                        }
                    }
                }
            }
        }
Example #3
0
 /// <summary>
 /// Execute a raw query string against a test database
 /// </summary>
 /// <param name="query">One SQL statement to be executed</param>
 /// <param name="database">database to execute against</param>
 public static void ExecuteSQLQuery(string query, string database)
 {
     using (var db = new ADODatabaseContext("TEST", database))
     {
         db.ExecuteNonQuery(query);
     }
 }
Example #4
0
        /// <summary>
        /// Create one constraint between two tables.  Use this method to create a few constraints for the tables you use in one test.
        /// </summary>
        /// <param name="pConstraintList">This is the list of available constraints in the code created by the Unit Test Database Creator utility</param>
        /// <param name="table1">The first table that the constraint applies to</param>
        /// <param name="table2">The second table the constraint applies to</param>
        public static void CreateConstraint(List <ConstraintDefinition> pConstraintList, string table1, string table2)
        {
            var constraintList = pConstraintList.Where(x => x.PkTable.ToLower() == table1 && x.FkTable.ToLower() == table2).ToList();

            foreach (var constraint in constraintList)
            {
                var query = "ALTER TABLE " + constraint.FkTable + " ADD CONSTRAINT fk_" + constraint.FkTable + "_" + constraint.PkTable + " FOREIGN KEY (" + constraint.FkField + ") REFERENCES " + constraint.PkTable + "(" + constraint.PkField + ")";

                using (var db = new ADODatabaseContext("TEST"))
                {
                    db.ExecuteNonQuery(query);
                }
            }

            constraintList = pConstraintList.Where(x => x.PkTable.ToLower() == table2 && x.FkTable.ToLower() == table1).ToList();
            foreach (var constraint in constraintList)
            {
                var query = "ALTER TABLE " + constraint.FkTable + " ADD CONSTRAINT fk_" + constraint.FkTable + "_" + constraint.PkTable + " FOREIGN KEY (" + constraint.FkField + ") REFERENCES " + constraint.PkTable + "(" + constraint.PkField + ")";

                using (var db = new ADODatabaseContext("TEST", constraint.DatabaseName))
                {
                    db.ExecuteNonQuery(query);
                }
            }
        }
Example #5
0
        /// <summary>
        /// Clear all the constraints in the database.
        /// </summary>
        /// <param name="pConstraintList">This is the list of available constraints in the code created by the Unit Test Database Creator utility</param>
        public static void ClearConstraints(List <ConstraintDefinition> pConstraintList)
        {
            var schemaName = "dbo";

            // delete all foreign constraints in all databases
            using (var db = new ADODatabaseContext("TEST"))
            {
                //_databaseList
                foreach (var database in _databaseList)
                {
                    var constraints = pConstraintList.Where(x => x.DatabaseName == database).ToList();

                    foreach (var constraint in constraints)
                    {
                        var constraintName = "fk_" + constraint.FkTable + "_" + constraint.PkTable;

                        if (!string.IsNullOrEmpty(constraint.SchemaName))
                        {
                            schemaName = constraint.SchemaName;
                        }

                        var query = "SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='" + constraintName + "' AND CONSTRAINT_SCHEMA='" + schemaName + "'";
                        using (var reader = db.ReadQuery(query))
                        {
                            while (reader.Read())
                            {
                                query = "ALTER TABLE " + constraint.DatabaseName + ".." + constraint.FkTable + " DROP CONSTRAINT " + constraintName;
                                db.ExecuteNonQuery(query);
                                break;
                            }
                        }
                    }
                }
            }
        }
Example #6
0
        /// <summary>
        /// This method will create a list of tables in the database provided.  You must call this method
        /// for each database you want to create tables inside of.
        /// </summary>
        /// <param name="tableList">List of table definitions provided by the code generated with the Unit Test Database Generator utility.</param>
        /// <param name="databaseName">The database name these tables will be created inside of.</param>
        public static void CreateAllTables(List <TableDefinition> tableList, string databaseName)
        {
            // create all non "dbo" schemas in database
            var schemaList = (from t in tableList where t.SchemaName != "dbo" select t.SchemaName).Distinct();

            foreach (var schemaName in schemaList)
            {
                if (!string.IsNullOrEmpty(schemaName))
                {
                    using (var db = new ADODatabaseContext("TEST", databaseName))
                    {
                        db.ExecuteNonQuery("CREATE SCHEMA [" + schemaName + "] AUTHORIZATION [dbo]");
                    }
                }
            }

            // generate all tables listed in the table name list
            foreach (var tableDefinition in tableList)
            {
                var query = tableDefinition.CreateScript;

                using (var db = new ADODatabaseContext("TEST", databaseName))
                {
                    db.ExecuteNonQuery(query);
                }
            }
        }
Example #7
0
        /// <summary>
        /// Use this method to create a stored procedure defined in the code generated by the Unit Test Generator utility.
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="databaseName"></param>
        /// <param name="spName"></param>
        public static void CreateStoredProcedure(Stream stream, string databaseName, string spName)
        {
            using (var db = new ADODatabaseContext("TEST", databaseName))
            {
                // first, drop the stored procedure if it already exists
                var sp = @"if exists (select * from sys.objects where name = N'" + spName + @"' and type = N'P') 
						  begin
							drop procedure "                             + spName + @"
						  end"                        ;
                db.ExecuteNonQuery(sp);

                // need to read the text file and create the stored procedure in the test database
                using (var reader = new StreamReader(stream))
                {
                    var storedProcText = reader.ReadToEnd();

                    var tsqLcommandList = Regex.Split(storedProcText, "GO");

                    foreach (var tsqlCommand in tsqLcommandList)
                    {
                        db.ExecuteNonQuery(tsqlCommand);
                    }
                }
            }
        }
Example #8
0
        /// <summary>
        /// clear all constraints in database
        /// </summary>
        private static void ClearAllConstraints(string database)
        {
            using (var db = new ADODatabaseContext("TEST", database))
            {
                using (var reader = db.ReadQuery(@"
						SELECT 
							OBJECT_NAME(OBJECT_ID) AS ConstraintName,
							SCHEMA_NAME(schema_id) AS SchemaName,
							OBJECT_NAME(parent_object_id) AS TableName,
							type_desc AS ConstraintType
						FROM 
							sys.objects
						WHERE 
							type_desc LIKE '%CONSTRAINT' AND 
							OBJECT_NAME(OBJECT_ID) LIKE'fk_%'"                            ))
                {
                    while (reader.Read())
                    {
                        var foreignKeyTableName = reader["TableName"].ToString();
                        var constraintName      = reader["ConstraintName"].ToString();
                        var schemaName          = reader["SchemaName"].ToString();

                        using (var dbExec = new ADODatabaseContext("TEST", database))
                        {
                            var query = "ALTER TABLE [" + database + "].[" + schemaName + "].[" + foreignKeyTableName + "] DROP CONSTRAINT " + constraintName;
                            dbExec.ExecuteNonQuery(query);
                        }
                    }
                }
            }
        }
Example #9
0
        private void BuildQueryInsertData()
        {
            var query = "";

            foreach (var field in Fields)
            {
                if (field.Value != null)
                {
                    query += "[" + field.Name + "],";
                }
            }

            if (query[query.Length - 1] == ',')
            {
                query = query.Substring(0, query.Length - 1);
            }

            if (query == "")
            {
                return;
            }

            query = $"INSERT INTO [{_databaseName}].[{_schemaName}].[{_tableName}] ({query}) VALUES (";

            foreach (var field in Fields)
            {
                if (field.Value != null)
                {
                    query += field.Value + ",";
                }
            }

            if (query[query.Length - 1] == ',')
            {
                query = query.Substring(0, query.Length - 1);
            }

            query += ")";

            using (var db = new ADODatabaseContext(_connectionString))
            {
                if (_dataContainsPrimaryKey)
                {
                    db.ExecuteNonQuery("SET IDENTITY_INSERT [" + _databaseName + "].[" + _schemaName + "].[" + _tableName + "] ON");
                }
                db.ExecuteNonQuery(query);
                if (_dataContainsPrimaryKey)
                {
                    db.ExecuteNonQuery("SET IDENTITY_INSERT [" + _databaseName + "].[" + _schemaName + "].[" + _tableName + "] OFF");
                }
            }
        }
Example #10
0
        /// <summary>
        /// Read a generic List of your datatype from a table
        /// </summary>
        /// <typeparam name="T">Define a POCO of your table results</typeparam>
        /// <param name="tableName">Table to query</param>
        /// <param name="database">Database containing table</param>
        /// <param name="schema">Optional schema</param>
        /// <returns></returns>
        public static List <T> ReadDataFromTable <T>(string tableName, string database, string schema = "dbo") where T : new()
        {
            // make sure the schema name doesn't already contain a "."
            schema = schema.Replace(".", "");

            using (var db = new ADODatabaseContext("TEST", database))
            {
                var query  = $"SELECT * FROM [{schema}].[{tableName}]";
                var data   = db.ReadDataSet(query);
                var result = data.Tables[0].ToList <T>();
                return(result);
            }
        }
Example #11
0
        private static void CreateDatabase(string databaseName)
        {
            var databaseDirectory = Directory.GetCurrentDirectory();

            using (var db = new ADODatabaseContext("TEST"))
            {
                db.ExecuteNonQuery(@"CREATE DATABASE [" + databaseName + @"]
				  CONTAINMENT = NONE
				  ON  PRIMARY 
				  ( NAME = N'"                 + databaseName + @"', FILENAME = N'" + databaseDirectory + @"\" + databaseName +
                                   @".mdf' , SIZE = 8096KB , FILEGROWTH = 1024KB )
				  LOG ON 
				  ( NAME = N'"                 + databaseName + @"_log', FILENAME = N'" + databaseDirectory + @"\" + databaseName +
                                   @"_log.ldf' , SIZE = 8096KB , FILEGROWTH = 10%)
				  "                );
            }
        }
Example #12
0
        private string ReadTableIdentityFieldName()
        {
            var query = @"
					SELECT * FROM ["                     + _databaseName + @"].sys.identity_columns AS a 
					INNER JOIN ["                     + _databaseName + @"].sys.objects AS b ON a.object_id=b.object_id 
					WHERE 
						LOWER(b.name)='"                         + _tableName + @"' AND 
						type='U'"                        ;

            using (var db = new ADODatabaseContext(_connectionString))
            {
                var reader = db.ReadQuery(query);
                while (reader.Read())
                {
                    return(reader["name"].ToString());
                }
            }
            return("");
        }
Example #13
0
 private void BuildFieldsForTable()
 {
     Fields.Clear();
     using (var db = new ADODatabaseContext(_connectionString))
     {
         var columnQuery = "SELECT * FROM [" + _databaseName + "].INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + _tableName + "'";
         using (var reader = db.ReadQuery(columnQuery))
         {
             while (reader.Read())
             {
                 Fields.Add(new FieldDefinition
                 {
                     Name = reader["COLUMN_NAME"].ToString(),
                     Type = reader["DATA_TYPE"].ToString()
                 });
             }
         }
     }
 }
Example #14
0
        public void CreateStoredProcedure()
        {
            using (var db = new ADODatabaseContext("TEST", Database))
            {
                // first, drop the stored procedure if it already exists
                var sp = @"if exists (select * from sys.objects where name = N'" + Name.Replace("_tilde_", "~") + @"' and type = N'P') 
					  begin
						drop procedure "                         + Name.Replace("_tilde_", "~") + @"
					  end"                    ;
                db.ExecuteNonQuery(sp);

                // need to read the text file and create the stored procedure in the test database
                var tsqlCommandList = Regex.Split(Code, "GO");

                foreach (var tsqlCommand in tsqlCommandList)
                {
                    db.ExecuteNonQuery(tsqlCommand);
                }
            }
        }
Example #15
0
        /// <summary>
        /// Returns the total records in a table specified.  This can be used to verify how many records are created in your unit test.
        /// </summary>
        /// <param name="tableName">Table to check</param>
        /// <param name="database">Database that table resides in</param>
        /// <param name="schema">Optional schema name.  If not specified, then it will be set to "dbo"</param>
        /// <returns></returns>
        public static int TotalRecords(string tableName, string database, string schema = "dbo")
        {
            var results = 0;

            // make sure the schema name doesn't already contain a "."
            schema = schema.Replace(".", "");

            using (var db = new ADODatabaseContext("TEST", database))
            {
                var query = $"SELECT COUNT(*) AS total FROM [{schema}].[{tableName}]";
                using (var reader = db.ReadQuery(query))
                {
                    while (reader.Read())
                    {
                        results = int.Parse(reader["total"].ToString());
                        break;
                    }
                }
            }

            return(results);
        }
Example #16
0
        /// <summary>
        /// Truncate all tables in the databases setup.
        /// This method will remove all constraints before truncating tables.  You must
        /// re-apply your constraints for each test, if you need them.
        /// </summary>
        public static void TruncateData()
        {
            var tableList = new List <string>();

            using (var db = new ADODatabaseContext("TEST"))
            {
                //_databaseList
                foreach (var database in _databaseList)
                {
                    ClearAllConstraints(database);

                    // generate a table list
                    using (var reader = db.ReadQuery(@"
						SELECT * 
						FROM ["                         + database + @"].INFORMATION_SCHEMA.tables 
						WHERE TABLE_TYPE = 'BASE TABLE'
						ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME"                        ))
                    {
                        while (reader.Read())
                        {
                            var tableName  = reader["table_name"].ToString();
                            var schemaName = reader["TABLE_SCHEMA"].ToString();

                            tableList.Add($"[{database}].[{schemaName}].[{tableName}]");
                        }
                    }
                }
            }

            using (var db = new ADODatabaseContext("TEST"))
            {
                foreach (var item in tableList)
                {
                    db.ExecuteNonQuery(@"TRUNCATE TABLE " + item);
                }
            }
        }