Tuesday, August 22, 2017

To Add a New Column to an Existing Table in Entity Framework

The "Update Model from Database" is hard/slow to use . It generates other stuff that you probably don't want/need. So manually adding the column that you need will work better. I suggest you do it outside the VS editor since depending on how many models/tables, it can be very slow opening the file in VS.

1. So in Windows Exlorer,right click on the *.edmx file and open with XML (Text) Editor.

 2. Search for the text <EntityType Name="YourTableNameToAddColumn">.

 3. Add the property <Property Name="YourNewColumnName" Type="varchar" MaxLength="50" />

 4. Search for the text <MappingFragment StoreEntitySet="YourTableNameToAddColumn">

 5. Add mapping to the new column <ScalarProperty Name="YourNewColumnName" ColumnName="YourNewColumnName"/>

 6. Save the *.edmx file.

Syntax:

1.Search for the text <EntityType Name="YourTableNameToAddColumn">.

2.Add the property <Property Name="LastName" Type="varchar" MaxLength="50" Nullable="false" />
<EntityType Name="tblUsers">
          <Key>
            <PropertyRef Name="UserID_pk" />
          </Key>
          <Property Name="UserID_pk" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
          <Property Name="FirstName" Type="varchar" MaxLength="50" Nullable="false" />
          <Property Name="MiddleName" Type="varchar" MaxLength="50" />
          <Property Name="LastName" Type="varchar" MaxLength="50" Nullable="false" />        
        </EntityType>

    <EntityType Name="tblUser">
          <Key>
            <PropertyRef Name="UserID_pk" />
          </Key>
          <Property Name="UserID_pk" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Name="FirstName" Type="String" Nullable="false" MaxLength="50" FixedLength="false" Unicode="false" />
          <Property Name="MiddleName" Type="String" MaxLength="50" FixedLength="false" Unicode="false" />
          <Property Name="LastName" Type="String" Nullable="false" MaxLength="50" FixedLength="false" Unicode="false" />      
        </EntityType>

3.Search for the text <MappingFragment StoreEntitySet="YourTableNameToAddColumn">
4.Add mapping to the new column <ScalarProperty Name="YourNewColumnName" ColumnName="YourNewColumnName"/>

    <EntitySetMapping Name="tblUsers">
            <EntityTypeMapping TypeName="YourModel.tblUser">
              <MappingFragment StoreEntitySet="tblUsers">              
                <ScalarProperty Name="LastName" ColumnName="LastName" />
                <ScalarProperty Name="MiddleName" ColumnName="MiddleName" />
                <ScalarProperty Name="FirstName" ColumnName="FirstName" />
                <ScalarProperty Name="UserID_pk" ColumnName="UserID_pk" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
 6. Save the *.edmx file

Wednesday, August 2, 2017

How to execute a Stored Procedure with EF Code First

Here is class structure and procedures.

public class Books_Result
    {
        public int BookId{ get; set; }    
        public string BookName{ get; set; }
        public string AuthorName{ get; set; }
        public decimal BookRate{ get; set; }    
        public string Description{ get; set; }
        public bool Active{ get; set; }
    }


CREATE PROCEDURE YourStoredProcedureName
 @SortBy VARCHAR(5000) ,
        @FilterBy VARCHAR(5000) ,
          @BookName VARCHAR(5000)
        @TotalRecords INT OUT ,  
        @TotalDisplayRecords INT OUT
AS
BEGIN

  SET @TotalRecords = 0
  SET @TotalDisplayRecords = 0

DECLARE @sqlQuery  AS NVARCHAR(MAX)

set @sqlQuery  =N'SELECT BookId,BookName,AuthorName,BookRate,Description FROM [Books]
WHERE BookName= @BookName

set @TotalDisplayOutputRecords=( SELECT COUNT(*)  [Books]
WHERE BookName= @BookName  )
'
EXECUTE sp_executesql @sqlQuery,
   N'@TotalDisplayOutputRecords INT OUT',  
   @TotalDisplayOutputRecords = @TotalDisplayRecords OUT  
 
 SET @TotalRecords = @TotalDisplayRecords

END

This method will return an DbRawSqlQuery, which you can enumerate using For / ForEach loop. For executing procedure with output parameter.
             DataTable dt = new DataTable();
          using (var Entities = new YourEntities())
            {
                var TotalRecords = new ObjectParameter("TotalRecords", typeof(int));
                var TotalDisplayRecords = new ObjectParameter("TotalDisplayRecords", typeof(int));
                System.Data.SqlClient.SqlParameter[] param = new               System.Data.SqlClient.SqlParameter[5];
                param[0] = new System.Data.SqlClient.SqlParameter();
                param[0].ParameterName = "@SortBy";
                param[0].Size = 5000;
                param[0].SqlDbType = SqlDbType.VarChar;
                param[0].Value = "BookId asc";

                param[1] = new System.Data.SqlClient.SqlParameter();
                param[1].ParameterName = "@FilterBy";
                param[1].Size = 5000;
                param[1].SqlDbType = SqlDbType.VarChar;
                param[1].Value = "";

                param[2] = new System.Data.SqlClient.SqlParameter();
                param[2].ParameterName = "@BookName";
                param[2].Size = 5000;
                param[2].SqlDbType = SqlDbType.VarChar;
                param[2].Value = "Entity Framework";
             
                param[3] = new System.Data.SqlClient.SqlParameter();
                param[3].ParameterName = "@TotalRecords";
                param[3].SqlDbType = SqlDbType.Int;
                param[3].Value = DBNull.Value;

                param[3].Direction = ParameterDirection.InputOutput;

                param[4] = new System.Data.SqlClient.SqlParameter();
                param[4].ParameterName = "@TotalDisplayRecords";
                param[4].SqlDbType = SqlDbType.Int;
                param[4].Value = DBNull.Value;

                param[4].Direction = ParameterDirection.InputOutput;

                var  BookCollectionList= Entities.ExecuteStoreQuery<Books_Result>      ("YourStoredProcedureName @SortBy, @FilterBy, @BookName,  @TotalRecords out , @TotalDisplayRecords out", param).ToList();
                dt = BookCollectionList.ToDataTable();
                TotalRecords = Convert.ToInt32(param[3].Value);
                TotalDisplayRecords = Convert.ToInt32(param[4].Value);
            }

Tuesday, August 1, 2017

Script to generate DROP/ADD queries for Foreign Keys, Primary Keys and Default constraints of a DB/Table


Script to generate DROP/ADD queries for Foreign Keys, Primary Keys and Default constraints of a DB/Table

Foreign keys
The foreign key constraint query basically bears details of the constraint_name, parent table name, child table name and the participating columns. In addition one key aspect is to script out the constraint with respect to the is_trusted and is_enabled status flags as they decide the key feature as to whether the constraint is active or not.
These info can be obtained from the following system tables:

sysforeignkeys
syscolumns
Drop Foreign Key

The drop foreign key query can be generated quite simply with the help of constraint name and the parent/child table names.

---------------------------------------------
--ALTER TABLE DROP FOREIGN CONSTRAINT Queries
---------------------------------------------
SELECT DISTINCT
 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(fkeyid))+'.'+QUOTENAME(OBJECT_NAME(fkeyid))+
' DROP CONSTRAINT '+QUOTENAME(OBJECT_NAME(constid))
AS Drop_Foreign_Key_Constraint_Query
FROM sys.sysforeignkeys sfk
/*Include below statement for generating queries for a particular table*/
--WHERE fkeyid=OBJECT_ID('table_name')


Add Foreign key

The ADD FOREIGN KEY query can be generated by coupling the sysconstraints system table with the syscolumns table to get the parent/children table names and the corresponding column names.

------------------------------------------------
--ALTER TABLE CREATE FOREIGN CONSTRAINT Queries
------------------------------------------------

--Obtaining the necessary info from the sys tables
SELECT
 constid,QUOTENAME(OBJECT_NAME(constid)) as constraint_name
,CASE WHEN fk.is_not_trusted=1 THEN 'WITH NOCHECK' ELSE 'WITH CHECK' END as trusted_status
,QUOTENAME(OBJECT_SCHEMA_NAME(fkeyid))+'.'+QUOTENAME(OBJECT_NAME(fkeyid)) AS fk_table,QUOTENAME(c1.name) AS fk_col
,QUOTENAME(OBJECT_SCHEMA_NAME(rkeyid))+'.'+QUOTENAME(OBJECT_NAME(rkeyid)) AS rk_table,QUOTENAME(c2.name) AS rk_col
,CASE WHEN fk.delete_referential_action=1 AND fk.delete_referential_action_desc='CASCADE' THEN 'ON DELETE CASCADE ' ELSE '' END AS delete_cascade
,CASE WHEN fk.update_referential_action=1 AND fk.update_referential_action_desc='CASCADE' THEN 'ON UPDATE CASCADE ' ELSE '' END AS update_cascade
,CASE WHEN fk.is_disabled=1 THEN 'NOCHECK' ELSE 'CHECK' END AS check_status
--,sysfk.*,fk.*
INTO #temp_fk
FROM sys.sysforeignkeys sysfk
INNER JOIN sys.foreign_keys fk ON sysfk.constid=fk.object_id
INNER JOIN sys.columns c1 ON sysfk.fkeyid=c1.object_id and sysfk.fkey=c1.column_id
INNER JOIN sys.columns c2 ON sysfk.rkeyid=c2.object_id and sysfk.rkey=c2.column_id
/*Include below statement for generating queries for a particular table*/
--WHERE fkeyid=OBJECT_ID('table_name')
ORDER BY constid,sysfk.keyno

--building the column list for foreign/primary key tables
;WITH cte
AS
(
SELECT DISTINCT
constraint_name,trusted_status
,fk_table
,SUBSTRING((SELECT ','+fk_col FROM #temp_fk WHERE constid=c.constid FOR XML PATH('')),2,99999) AS fk_col_list
,rk_table
,SUBSTRING((SELECT ','+rk_col FROM #temp_fk WHERE constid=c.constid FOR XML PATH('')),2,99999) AS rk_col_list
,check_status
,delete_cascade,update_cascade
FROM
#temp_fk c
)
--forming the ADD CONSTRAINT query
SELECT
'ALTER TABLE '+fk_table
+' '+trusted_status
+' ADD CONSTRAINT '+constraint_name
+' FOREIGN KEY('+fk_col_list+') REFERENCES '
+rk_table+'('+rk_col_list+')'
+' '+delete_cascade+update_cascade+';'
+' ALTER TABLE '+fk_table+' '+check_status+' CONSTRAINT '+constraint_name
AS Add_Foreign_Key_Constraint_Query
FROM cte

--dropping the temp tables
DROP TABLE #temp_fk


Primary Keys

One can obtain the basic info of the primary keys existing int the database from sys.sysconstraints and sys.key_constraints. These tables give us a fair idea on the base table name, the constraint name and the columns upon which these act upon. Though these details form the crux of a primary key constraint, info such as index type being used with the primary key, the order of columns and the current status of the constraint are also equally important.

These info can be obtained using other system and information_schema tables such as:

    information_schema.key_column_usage
    sys.indexes
    sys.index_columns and the like..
So having had the base tables, it only requires to pair them up with the right set of joins using the key columns and correct usage of column data to frame the query.

Drop Primary key

A primary key of a table can be dropped only when there parent any dependent foreign key constraints on it. This action of dropping of  foreign keys can be achieved using the script given in the previous section.

It requires just the name of the constraint and the base table name to frame the query for dropping of a primary key. Having obtained them from sys.key_constraints, the query can be designed as follows:

-------------------------------------------------
--ALTER TABLE DROP PRIMARY KEY CONSTRAINT Queries
-------------------------------------------------
SELECT DISTINCT
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id))+' DROP CONSTRAINT '+QUOTENAME(name)
AS Drop_Primary_Key_Constraint_Query
FROM sys.key_constraints skc
WHERE type='PK'
/*Include below statement for generating queries for a particular table*/
--AND parent_object_id=object_id('table_name')

Add Primary key

Creation of primary keys as an "ALTER TABLE tbl_name ADD CONSTRAINT constr_name .." syntax requires more details than seen above. With the usage of other system tables we can obtain the necessary details and script the query as follows:

---------------------------------------------------
--ALTER TABLE CREATE PRIMARY KEY CONSTRAINT Queries
---------------------------------------------------
SELECT
 QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id)) AS pk_table--PK table name
,skc.object_id AS constid
,QUOTENAME(skc.name) AS constraint_name--PK name
,QUOTENAME(iskcu.column_name) + CASE WHEN sic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END  AS pk_col
,iskcu.ordinal_position
,CASE WHEN unique_index_id=1 THEN 'UNIQUE' ELSE '' END as index_unique_type
,si.name AS index_name
,si.type_desc AS index_type
,QUOTENAME(fg.name) AS filegroup_name
,'WITH('
+' PAD_INDEX = '+CASE WHEN si.is_padded=0 THEN 'OFF' ELSE 'ON' END +','
+' IGNORE_DUP_KEY = '+CASE WHEN si.ignore_dup_key=0 THEN 'OFF' ELSE 'ON' END +','
+' ALLOW_ROW_LOCKS = '+CASE WHEN si.allow_row_locks=0 THEN 'OFF' ELSE 'ON' END +','
+' ALLOW_PAGE_LOCKS = '+CASE WHEN si.allow_page_locks=0 THEN 'OFF' ELSE 'ON' END
+')' AS index_property
--,*
INTO #temp_pk
FROM sys.key_constraints skc
INNER JOIN information_schema.key_column_usage iskcu ON skc.name=iskcu.constraint_name
INNER JOIN sys.indexes si ON si.object_id=skc.parent_object_id and si.is_primary_key=1
INNER JOIN sys.index_columns sic ON si.object_id=sic.object_id and si.index_id=sic.index_id
INNER JOIN sys.columns c ON sic.object_id=c.object_id AND sic.column_id=c.column_id
INNER JOIN sys.filegroups fg ON si.data_space_id=fg.data_space_id
WHERE
skc.type='PK'
AND iskcu.column_name=c.name
/*Include below statement for generating queries for a particular table*/
--AND skc.parent_object_id= object_id('table_name')
ORDER BY skc.parent_object_id,skc.name,ordinal_position

;WITH cte
AS
(
SELECT
pk_table
,constraint_name
,index_type
,SUBSTRING((SELECT ','+pk_col FROM #temp_pk WHERE constid=t.constid FOR XML PATH('')),2,99999) AS pk_col_list
,index_unique_type
,filegroup_name
,index_property
FROM #temp_pk t
)
--forming the ADD CONSTRAINT query
SELECT DISTINCT
'ALTER TABLE '+pk_table
+' ADD CONSTRAINT '+constraint_name
+' PRIMARY KEY '+CAST(index_type COLLATE database_default AS VARCHAR(100))
+' ('+pk_col_list+')'
+index_property
+' ON '+filegroup_name+''
AS Create_Primary_Key_Constraint_Query
FROM cte

--dropping the temp tables
DROP TABLE #temp_pk


Default Constraints
Default constraints on a column allows for automatic population of data in the absence of user supplied values. These can be created during the table creation itself or by means of an ALTER TABLE statement as well.

Drop Default Constraint

The default constraint existing on a column of a table can be dropped with the knowledge of the table name and the corresponding default constraint name. The following script generates these DROP CONSTRAINT statements using info from sys.default_constraints table.

/*****************************************DEFAULT CONSTRAINT****************************************************/

---------------------------------------------
--ALTER TABLE DROP DEFAULT CONSTRAINT Queries
---------------------------------------------
SELECT
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(object_name(parent_object_id))
+' DROP CONSTRAINT '+QUOTENAME(sdc.name)+''
AS Drop_Default_Constraint_Query
FROM sys.default_constraints sdc
/*Include below statement for generating queries for a particular table*/
--WHERE parent_object_id=object_id('table_name')


Add Default Constraint

The ADD CONSTRAINT query can be generated by using the default definition and other columns of the sys.default_constraints system table as follows:
---------------------------------------------
--ALTER TABLE CREATE DEFAULT CONSTRAINT Queries
---------------------------------------------
select
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id))
+' ADD CONSTRAINT '+QUOTENAME(sdc.name)+' DEFAULT '+definition+' FOR '+QUOTENAME(c.name)+''
AS Add_Default_Constraint_Query
FROM sys.default_constraints sdc
inner join sys.columns c ON sdc.parent_object_id=c.object_id and sdc.parent_column_id=c.column_id
/*Include below statement for generating queries for a particular table*/
--WHERE parent_object_id=object_id('table_name')

Mixed Content: The page at xxx was loaded over HTTPS, but requested an insecure

 Mixed Content: The page at ' https ://www.test.com/signup.aspx' was loaded over HTTPS, but requested an insecure script ' http ...