Returning ID from insert query in C#

Intoduction:
         I am going to explain how to return the Id after Inserting the data into table.

There may be many occassion comes where you need to use the Id of data after inserting the values into table.If you look at my post Explained in http://aspdotnetbank-kartik.blogspot.in/2012/05/how-to-create-webservice-and-how-can-we.html there is one Table called Office in that Post.so first you Create a Table(Office)  from that Post.but Be Remeber that OfficeId is Primary Key and also Auto Increment Value.

Now Lets Look at .aspx page

<form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    <asp:Label ID="lblOfficeName" runat="server" Text="Office Name"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtOfficeName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblCity" runat="server" Text="City"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblCountry" runat="server" Text="Country"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
                    <asp:Label ID="lblId" Text="ID=" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
    </div>
    </form>

Now .aspx.cs Page write code in button Save Click Event.

  protected void btnSave_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=GTL--7\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
            con.Open();
            string s = "insert into Office values('" + txtOfficeName.Text + "','" + txtCity.Text + "','" + txtCountry.Text + "')";
            SqlCommand cmd = new SqlCommand(s, con);
            cmd.ExecuteNonQuery();
            SqlCommand idCMD = new SqlCommand("SELECT SCOPE_IDENTITY(); ", con);
            // Retrieve the identity value and store it in the CategoryID column.
            int newID = Convert.ToInt16(idCMD.ExecuteScalar());
            lblId.Text += newID;
        }

Now If you Look at the code I have used SqlCommand idCMD = new SqlCommand("SELECT SCOPE_IDENTITY(); ", con); It Just returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.


You can also use the Select Ident_Current(TableName);  and SELECT @@IDENTITY; in Place Of  SELECT SCOPE_IDENTITY();  from above example.

Means There are three way we can achieve the Id.Lets disccuss about this

Select Ident_Current(TableName);  For ex. Select Ident_Current('Office');

SELECT @@IDENTITY;

SELECT SCOPE_IDENTITY();

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’) It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Download Source Code from here


Now If you are using Linq to insert the Data then Its really Easy....

Office record = new Office();
record.OfficeName = "KP";
record.City="Ahmedabad";
record.Country="India";
db.MyTable.InsertOnSubmit(record);
db.SubmitChanges();
lblId.Text +=record.OfficeId

How do I find a value anywhere in a SQL Server Database?

Introduction

           This is really nice stuff and will get the useful at any time to anybody.I was at this situation where I want to find particular data but i dont know where this data reside means in which Table and in which column this data was there.At that Time i got this solution.I hope that it might help you.

I am posting on SP in which it searches all the columns of all tables in a given database and then Find the Particular Data.

 ALTER PROC [dbo].[SearchAllTables]

(

    @SearchText nvarchar(100)

)

AS

BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

-- Purpose: To search all columns of all tables for a given search string

-- Written by: Narayana Vyas Kondreddi

-- Site: http://vyaskn.tripod.com

-- Tested on: SQL Server 7.0 and SQL Server 2000

-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchText2 nvarchar(110)

SET  @TableName = ''

SET @SearchText2 = QUOTENAME('%' + @SearchText + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

    SET @ColumnName = ''

    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

    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN

        SET @ColumnName =

        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))

            FROM    INFORMATION_SCHEMA.COLUMNS

            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)

                AND TABLE_NAME  = PARSENAME(@TableName, 1)

                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                AND QUOTENAME(COLUMN_NAME) > @ColumnName

        )

        IF @ColumnName IS NOT NULL

        BEGIN

            INSERT INTO @Results

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

                FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchText2

            )

        END

    END 

END

SELECT ColumnName, ColumnValue FROM @Results

END