MVC

  • Home
  • HTML
    • Overview
    • Formatting
  • Java Script
    • Overview
    • Validation
  • CSS
    • Overview
  • Sql
    • Overview
    • SQL Select
    • SQL Command
    • Advance Query
    • SQL Create
    • Function
    • Function vs StoreProcedure
    • Stored Procedures
  • MVC
    • OverView
    • Evolution of MVC
    • Install VisualStudio-2015
    • Create First Application
  • LINQ
    • Introduction
  • Privacy Policy

Friday, 11 August 2017

IDENTITY_INSERT is set to OFF/ ON using SQL SERVER

  Unknown       11:10       Function       No comments    

In this Article introduce about IDENTITY_INSERT ON/OFF in SQL Server Discuss in detail with example see bleow discription in this Article

What is IDENTITY_INSERT


IDENTITY_INSERT When you create table in database then one field is ID then this field is integer type and autoincrement if yuow will set Identity(i,1), when you create table then insert any row then Identity column will autometic incremet by 1 this is the time IDENTITY_INSERT is OFF means you can't insert manualy. Means in insert query You can't insert id in table in example Follow some steps-
1st Steps - Create Data Base and Create Table in Sql Server like


CREATE TABLE Tbl_Identity
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100)
)
Note- This is syntax of Create table and execute run F5 in sql server

2nd Steps - After Create table You can insert data in table like in example

INSERT INTO Tbl_Identity (Id, Name) VALUES (1, 'Testing')
Note - Write Insert query and execute this query then you find a error

Error show like this-
Msg 544, Level 16, State 1, Line 6 Cannot insert explicit value for identity column in table 'Tbl_Identity' when IDENTITY_INSERT is set to OFF.

Create table and set Id int identity then id column is autometicaly incremented by 1 that the resion if you insert any row then id field is automatic incremented, in this previous example you insert any record in table then you find error when you execute and run this query because the IDENTITY_INSERT is OFF On Tbl_Identity That the resion you can't enter any new id manully...

So first of all You Set IDENTITY_INSERT ON
Syntex - Set IDENTITY_INSERT TableName ON
Example - Set IDENTITY_INSERT Tbl_Identity ON

Note - Write this syntex and execute

Set IDENTITY_INSERT ON Execute after that You can Insert query and pass id field manully using insert query and you find record is inserted successfully,Untill unless you can not insert id manully.
Note - If Table have ID field is autoincremented and You want to insert Id manually then First of all You should Set IDENTITY_INSERT [TableName] ON ,Run this query after you can insert and if you set Off then you can't insert id manually, Syntex - Set IDENTITY_INSERT [TableName] OFF , Run this query so identity Off

Set IDENTITY_INSERT ON/OFF using LINQ.


Insert identity on table you can change Id values in table through linq query Following some steps like -
  • 1st Step - Establish Connection (connect database in your project).
DummyShoppingEntities db = new DummyShoppingEntities();
Add entity in your project through add edmx file and automatic created connection string in web config file like this.
Ex. -Show in Web.Config file-
  <connectionStrings>
  <add name="DummyShoppingEntities" connectionString="metadata=res://*/Models.Dummy_DB.csdl|res://*/Models.Dummy_DB.ssdl|res://*/Models.Dummy_DB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-GFUF27H;initial catalog=Db_DummyShopping;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
Note-

name="DummyShoppingEntities" add name for access database in mvc

  • 2nd Step - Write query in linq.
Create Object of entity and write queryin sql in example-
public ActionResult Index()
{
 db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT TableName ON;INSERT INTO    TableName (ColumnName, ColumnName) VALUES (ColumnValue, 'ColumnValue');Set  IDENTITY_INSERT Tbl_Identity TableName ");
db.SaveChanges();
 return View();
}
Example
public ActionResult Index()
{
db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT Tbl_Identity ON;INSERT INTO Tbl_Identity (Id, Name) VALUES (2, 'Testing');Set IDENTITY_INSERT Tbl_Identity OFF ");
db.SaveChanges();
return View();
}

Note - In this example IDENTITY_INSERT on on table and write query in insert record in table and IDENTITY_INSERT off, Tbl_Identity is a table name
You can change another method also like
Example
public ActionResult Index()
{
Tbl_Identity item = new Tbl_Identity();
item.Id = 12;
item.Name = "";
db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT Tbl_Identity ON;INSERT INTO Tbl_Identity (Id, Name) VALUES ("+ item.Id + ", '"+ item.Name+ "');Set IDENTITY_INSERT Tbl_Identity OFF ");
OR Add parameter also
db.Database.ExecuteSqlCommand("Set IDENTtestITY_INSERT Tbl_Identity ON;INSERT INTO Tbl_Identity (Id, Name) VALUES ({0},{1});Set IDENTITY_INSERT Tbl_Identity OFF,item.Id,item.Name ");
db.SaveChanges();
return View();
}

Note - Tbl_Identity is table name and you can define values in table field also

you can also use sql connection and add parameter like ado.net show in example like-

Example -
public ActionResult Index()
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ConnectionString);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
ModelClass item = new ModelClass();
item.Id = 12;
item.Name = "test";
string query = "INSERT INTO Tbl_Identity (Id, Name) VALUES (@id,'" + item.Id + "', @name,'" + item.Name + "')";
sqlcom.Parameters.AddWithValue("id", item.Id);
sqlcom.Parameters.AddWithValue("id", item.Name);
db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT Tbl_Identity ON;" + query + ";Set IDENTITY_INSERT Tbl_Identity OFF ");
db.SaveChanges();
con.Close();
return View();
}

Note - In this example firstly you establish a connection using sqlconnection and command after that open connection and declare model class and define properties.in this example class is "ModelClass" and set values then add parameter.Execute command identity insert on and insert record and identity off.

  • 3rd Steps - Work on Identity_Insert on and insert record theen off and save changes through linq. If u try this the you can easly insert id in table and update also.

Reset Identity Column in Sql Server

What is Reset Identity ?

Resest Identity - In Sql server whenever you create a table you define primary key identity means autometic increment by 1 when ever you insert record in table then so identity is autoincrement,Incase You want to change and reset identity field that is Reset Identity

Why Need to Reset Identity Column in sqlServer ?

In This artical we discuss about Reset identity column in table.Sometime you want to Change identity field in table like
Ex.
If you create a table and insert some record and you want to delete some record and again insert record then you find new inserted record those id number is different because Deleted record id number is skip. So you want to change identity field in table and maintain sequence then use Reset Identity.This more clear by Example see below example -
Example-

SELECT * FROM vibha1<Table Name> --Here Vibha1 is TableName

We get Values-











Delete data  from table
Ex. 

DELETE FROM vibha1 WHERE id=6
One Value Delete then Insert Value 

INSERT INTO vibha1(name) VALUES('vibha1')

Get Values-










Then We Use Code

Syntax: DBCC CHECKIDENT( <TableName>, RESEED, <ValuesThen Start>) 

And Delete id 7 record

DELETE FROM vibha1 WHERE id=7
Then  Execute This

 DBCC CHECKIDENT( vibha1 , RESEED, 5)
Then Insert

INSERT INTO vibha1(name) VALUES('vibha1')

Now Get Result

    • Share This:  
    •  Facebook
    •  Twitter
    •  Google+
    Email This BlogThis! Share to X Share to Facebook
    Newer Post Older Post Home

    0 comments :

    Post a Comment

    learningmvctutorial

    Popular Posts

    • Difference Between function v/s store procedure
      Definition  of Store Procedure In a database management system (DBMS), a  stored procedure  is a set of Structured Query Language (SQL...
    • Overview of HTML
      What is HTML? First developed by Tim Berners-Lee in 1990 . HTML is used to create electronic documents (called pages) that are displ...
    • Overview
        SQL (Structured Query Language)   SQL  is a language that interacts with the database on the server-side. This dynamite duo has changed ...
    • What is LINQ
      LINQ  (Language Integrated Query) is a Microsoft programming model and methodology that essentially adds formal query capabilities into Mic...
    • What is Function in Sql Server
      A function is a block of code that has a name and it has a property that it is reusable. We use function in store Procedure and queries in...
    • About Javascript
      What is JavaScript? JavaScript was designed to add interactivity to HTML pages JavaScript is a scripting language A scripting langua...
    • Create Duplicate Table of existing table in SQL Server
      In This Tutorial we create table and want to same structure and same data then We create new table with data type after that  insert in ta...
    • Operator in JavaScript
      What is an operator? JavaScript has both binary and unary operators, and one special ternary operator, the conditional operator. A binar...
    • SQL Select
      The SQL SELECT Statement The SELECT statement is used to select data from a database. SQL SELECT Syntax SELECT column_name , column_n...
    • Create First Application in MVC
      We want to create Firstly application in MVC first of how we create first application Show Steps   as below-     STEP 1 .  Start Computer ...

    Blog Archive

    • ▼  2017 ( 35 )
      • ►  November ( 1 )
      • ►  October ( 2 )
      • ►  September ( 4 )
      • ▼  August ( 1 )
        • IDENTITY_INSERT is set to OFF/ ON using SQL SERVER
      • ►  July ( 1 )
      • ►  May ( 3 )
      • ►  April ( 7 )
      • ►  March ( 13 )
      • ►  February ( 3 )
    Powered by Blogger.

    Categories

    • AdvanceQuery
    • CreateApplication
    • CreateDb
    • CssOverview
    • Evolution
    • Formatting
    • Function
    • FunctionVSSp
    • HtmlOverview
    • InstallVisualStudio
    • Introduction
    • javascriptOverview
    • JsValidation
    • Mvc-vs-Asp.Net
    • OverView
    • Select
    • SqlCommand
    • SQLOverview
    • StoredProcedures

    Text Widget

    Sample Text

    Pages

    • Home

    Copyright © MVC | Powered by Blogger
    Design by Vibha Acharya