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

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

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 🙂

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;
            }
        }