ASP.Net, C#, SQL Server

Get Identity Column Value just after Inserting a row in MS SQL Server Table

Many a time , We need to get the auto Incremented Identity Column value just after Inserting/Saving a row to a Table in MS SQL Server.

In my Case of this Example, When I Insert a New Project to my tblProjects Table , I want the ID column value( which is basically auto incremented Identity Column in SQL Server) of my inserted Row .

For this , TSQL provides us a way to do that like below –

SELECT SCOPE_IDENTITY()

So in C# , our code can be like below :::

Note : We can use StoredProcedue Which is better practice .Then inside the SP ,we will use the code mentioned.

public static int CreateProject(Project projectObj)
        {
            string insertProjectQuery = "INSERT INTO tblProjects VALUES('" + projectObj.ProjectName + "','" + projectObj.TblCustomerId + "','" + projectObj.BClosed + "') SELECT SCOPE_IDENTITY() As ProjectId";
            DataTable dt = QueryGateway.GetDataThroughDataTable(insertProjectQuery);
            return  Convert.ToInt32(dt.Rows[0]["ProjectId"]);
        }

Cheers

Advertisements
Standard
SQL Server

Convert image file to Byte/Binary and Save into SQL Server

For most of the Products , We need to save many types of Image files to SQL Server Table as binary Data .

For that case , We can keep our Column of the Table as varbinary(MAX) and Save the Image into this Column . Here is the SQL to do such things .

UPDATE DocumentTable
SET
DocumentContent = (SELECT * FROM OPENROWSET(BULK N'C:\logo.jpg', SINGLE_BLOB) as imagefile)
WHERE Id = 3

Standard
C#, SQL Server

Compress SQL Server DB .bak file in C#

Many a time , We need to take Backup of our Production Database . Most of the Time , This process is done Automatically .

And After Taking the Database , we can also compress that and keep for later use . This is how , we can compress Our .bak file in C# .

public void CompressDbBackup(string sFileName)
        {
            try
            {
                string sZipFileName = sFileName.Replace(".bak", ".zip");

                using (FileStream fStream = File.Open(sZipFileName, FileMode.Create))
                {
                    GZipStream obj = new GZipStream(fStream, CompressionMode.Compress);

                    byte[] bt = File.ReadAllBytes(sFileName);
                    obj.Write(bt, 0, bt.Length);

                    obj.Close();
                    obj.Dispose();
                }

                File.Delete(sFileName);
            }
            catch (Exception ex)
            {
                // Log Recorded
            }
        }

There will be another Post on How we can take Database backup Programmatically .

Keep an eye on this Blog . Thanks
Cheers 🙂

Standard
C#, SQL Server

Calling MS SQL SP (with param) from C#

By like below , we can call SP from C# . Here I am gonna show how a SP with Param can be called , which is little tricky rather than simple SP .

My SP name is ::: spU_ChangeProjectStatus Click Here to view the SP .

The SP has a param named @tblProjects_ID , and I am filling that param with projectId variable .

public static bool ChangeProjectStatus(int projectId)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(DBConnector.GetConnectionStringFromServer()))
                {
                    SqlCommand cmd = new SqlCommand("spU_ChangeProjectStatus", cn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tblProjects_ID", SqlDbType.Int).Value = projectId;
                    cn.Open();
                  
                    cmd.ExecuteNonQuery();
                    return  true;
                }
            }
            catch (Exception ex)
            {
                // Logger will be added here  Later
                return false;
            }
        }
Standard
Uncategorized

MSSQL StoredProcedure using a parameter

This SP has been developed using MS SQL . This SP takes parameter @tblProjects_ID and Updates The Column bClosed WHERE TABLE ID = That parameter .

At The End of this Post , Click the Link to know how to Call this SP from C# .

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spU_ChangeProjectStatus]
(
@tblProjects_ID INT
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @B_CLOSED BIT;
SELECT @B_CLOSED=bClosed FROM tblProjects WHERE ID=@tblProjects_ID
IF @B_CLOSED = 'FALSE'
UPDATE tblProjects SET bClosed='true' WHERE ID=@tblProjects_ID
ELSE
UPDATE tblProjects SET bClosed='false' WHERE ID=@tblProjects_ID
END

Click Here to see How to Call SP from C#

Standard
Uncategorized

Sql Transaction in C# : Part 2 – Doing in different Database

Here ,I have tried to show how you can use Sql Transaction for 2 Databases with different conection String . So you can use Sql Transaction on two DB Servers .

When you will go to first run this Sql Transaction in a Transaction Scope , you will get exception . For this, Click Enable “Distributed Transaction Coordinator” in SQL Server.

private void btnTransactionInDifferentDatabase_Click(object sender, EventArgs e)
        {
            string connectString1 = "Data Source=masoom-PC;Initial Catalog=TransactionDB;Integrated Security=true;";
            string connectString2= "Data Source=masoom-PC;Initial Catalog=TransactionDB2;Integrated Security=true;";

            string commandText1 = "INSERT INTO Transaction_T VALUES('2','30.33','Babu')";
            string commandText2 = "INSERT INTO Transaction2_T VALUES('2','20.3','Babu')";
            
            int returnValue = 0;
            System.IO.StringWriter writer = new System.IO.StringWriter();

            try
            {
               using (TransactionScope scope = new TransactionScope())
                {
                    using (SqlConnection connection1 = new SqlConnection(connectString1))
                    {
                        connection1.Open();

                        // Create the SqlCommand object and execute the first command.
                        SqlCommand command1 = new SqlCommand(commandText1, connection1);
                        returnValue = command1.ExecuteNonQuery();
                        writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
                       
                        using (SqlConnection connection2 = new SqlConnection(connectString2))
                        {
                            // The transaction is escalated to a full distributed
                            // transaction when connection2 is opened.
                            connection2.Open();

                            // Execute the second command in the second database.
                            returnValue = 0;
                            SqlCommand command2 = new SqlCommand(commandText2, connection2);
                            returnValue = command2.ExecuteNonQuery();
                            writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                        }
                    }
                    // The Complete method commits the transaction. If an exception has been thrown,
                    // Complete is not  called and the transaction is rolled back.
                    scope.Complete();
                }
            }
            catch (TransactionAbortedException ex)
            {
                writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
            }
            catch (ApplicationException ex)
            {
                writer.WriteLine("ApplicationException Message: {0}", ex.Message);
            }

            label1.Text = writer.ToString();
        }

Standard