How to switch Entity Framework database connected to

If you have a connection to a database through entity framework and you need to switch it to another database (with the exact same structure) you just need to set the Connection.ConnectionString (as seen below).
I had an application where we created a copy of the Master database when setting up a new client.  So using Entity Framework I switched from the master database to the client (depending on what the admin was doing).

using (MasterEntities aEntities = new MasterEntities())
{//Switch db connected to

 

aEntities.Database.Connection.ConnectionString = aEntities.Database.Connection.ConnectionString.Replace("OldDatabaseName", "NewDatabaseName");

      //Some Query

}

[tweetmeme only_single=”false”]
Advertisements

Tulsa Techfest – SQL Support Scripts presentation

Thanks to Tulsa Tech Fest for letting me present.
Attached is my presentation about SQL Support Scripts.

In the notes are the links to the scripts.
Tulsa TechFest 2013 Presentation

I hope you find something valuable to use.

List all Primary Keys in a database

This script will list all primary keys in a database.

SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER 
FROM sys.key_constraints AS SKC 
INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id 
INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id 
INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')
[tweetmeme only_single=”false”]

List of all views and scripts to create

Here is a script to list all views in a database and provide the script to create them.  This helps when moving views to new environments.

SELECT a.name AS VIEW_NAME, LEFT(b.definition, 4000) AS SQL1, SUBSTRING(b.definition, 4001, 4000) AS SQL2, LEN(b.definition) AS SQL_Length 
FROM sys.sql_modules AS b 
INNER JOIN sys.views AS a ON b.object_id = a.object_id 
[tweetmeme only_single=”false”]

Get list of all default values for every table

This script will list all default values for every table.

SELECT ao.name AS TABLE_NAME, dc.name AS CONSTRAINT_NAME, dc.definition AS DEFAULT_VALUE, ac.name AS COLUMN_NAME 
FROM sys.default_constraints AS dc 
INNER JOIN sys.all_columns AS ac ON dc.parent_object_id = ac.object_id 
AND dc.parent_column_id = ac.column_id 
INNER JOIN sys.all_objects AS ao ON ac.object_id = ao.object_id

[tweetmeme only_single=”false”]

Simple command to disable all Foreign Key checks

After blogging about how to disable all Foreign Key constraints and the undocumented sp for looping through all tables in a database, I thought about combining the two.

So I’ve now got a much easier way to disable FK constraints in a database.
The script below will loop through each table and disable the FK check.

--Disable FK Checks
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I’ve got an application that has a SQL CE offline mode where the Foreign Key keyword is not supported when running scripts against it.  (At least that’s what I’ve been told by the original developer).

[tweetmeme only_single=”false”]

How to disable all Foreign Key constraints

Here is a script that will loop through all tables in your database and disable the foreign key constraints.

DECLARE @TableName nvarchar(256)
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped' ) = 0)

    IF (@TableName <> '')
    BEGIN
        EXEC ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
        PRINT ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
    END

END
GO
[tweetmeme only_single=”false”]

Add your own error message to a ValidationSummary

There are time where you need to display a message to the users.  Here’s how to do it with a Validation Summary on your ASP.NET page.
I use this when I want to display an error message that doesn’t come from an exception or another validation.
This is helpful when you want all messages to have the same look and feel.
You MUST have a validation summary on your page otherwise nothing happens.

Below is the code.

CustomValidator val = new CustomValidator();
val.IsValid = false;
val.ErrorMessage = "My error message for the ValidationSummary";
this.Page.Validators.Add(val);

You create create a custom validator, set it to invalid, define the error message, and add it to your page.

[tweetmeme only_single=”false”]

What little I know about SQL Bulk Insert

I’ve got a stored procedure to upload a csv file to a database table.  From what I’ve read that’s the fastest way to load csv data to SQL.

Cool, but I’ve had errors when I went to deploy to production.

Here are just a few things I’ve learned.
1. The permissions used are different when using windows login vs sql login (to call the stored procedure).
2. If you use a windows login to call the stored procedure it will use the permissions of the user to read the file (no matter what you what the SQL process is set to run).
3. If you use a SQL login then account the SQL process is setup to run as is used.
4.  Bulk Insert doesn’t like to pull data from any server,  but the server that SQL is running on.  If you do try pulling data from another server then make sure you use a UNC path.

After all that I still had permissions trouble.
So my application copied the csv file to a directory on the SQL server.  Then calling the procedure to do the Bulk Insert worked without issue.

Below is my stored procedure.  You just need to pass it the path to the file you want to import.
It will load it to a table dbtemp.
Make sure that table has the same fields as your source file.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ImportFile] 
    @filename varchar(1000) 

AS
BEGIN

    SET NOCOUNT ON;

declare @sql varchar(4000)
select @sql = '
BULK
INSERT dbtemp
FROM ''' + @filename + '''
WITH
(
FIELDTERMINATOR = ''","'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)'
exec (@sql)
update dbtemp set funding = REPLACE(funding,'"',''), initialdate = REPLACE(initialdate,'"','')

END
[tweetmeme only_single=”false”]

How to create a new site based on a custom site template

Here is a method to create a sub site based on a custom site template.  You need to pass in your new site name and the name of the site template.  It will check the available site templates for the one you specified (so you must deploy the site template first).  If it’s not there an error gets displayed.  I put a label on my web part to display the error _lblErrMsg.  You will need that control (or you can remove it and handle errors whatever way you want.

private bool CreateSubSite(string newSiteName, string ProjectsiteTemplateName)
      {
          try
          {
              SPWeb webSite = SPContext.Current.Web;

              webSite.AllowUnsafeUpdates = true;

              //Get all the site templates
              SPWebTemplateCollection Templates = webSite.GetAvailableWebTemplates(Convert.ToUInt32(LOCALE_ID_ENGLISH));

              //Get the specific project template
              SPWebTemplate siteTemplate = null;

              //Verify the custom site template exists
              if (templateExists(Templates, ProjectsiteTemplateName))
              {
                  siteTemplate = Templates[ProjectsiteTemplateName];
              }
              else
              {
                  _lblErrMsg.Text = "Could not find the custom site template.";
              }

              if (siteTemplate != null)
              {

                  SPWeb newWeb; 
                  newWeb = SPContext.GetContext(HttpContext.Current).Web.Webs.Add(
                      newSiteName.Trim(),
                      newSiteName,
                              "Project Site",
                              LOCALE_ID_ENGLISH, siteTemplate,
                              true, false);

                  //Inherit navigation from parent site
                  newWeb.Navigation.UseShared = true;

                  newWeb.Update();

                  siteCreated = true;

                  webSite.AllowUnsafeUpdates = false;

              }

              webSite.Close();

          }
          catch (Exception ex)
          {
              this._lblErrMsg.Text = ex.Message.ToString();
          }

          return siteCreated;
      }
[tweetmeme only_single=”false”]