Swap Value In A Table Column

Introduction:  In This article we will see how to interchange the values of  a column in a table. Sometimes in our development cycle we may have to face a situation where we might have to interchange the values of a table column. SQL has provided us a very useful query for this purpose.

Example:  Suppose you have a Product table as below. And you have to swap the active field values i.e. True must be False and False must be True.

Important Sql Queries

Introduction: In this article i am going to give some of very useful SQL Queries that are required to us in our day to day programming life.

1. Get current Database Name:  Select DB_NAME()

2. Get Details Of All Databases Present In SQL Server: Select * from sys.databases

3. Rename A Database: Exec sp_renamedb ‘oldDatabaseName’ , ‘newDatabaseName’

Dataset Vs DataReader

Introduction: Following are some of the differences between DataSet and DataReader objects in .NET.

Dataset	DataReader
It is used in disconnected architecture	It is connection oriented.
Works with multiple tables	Works with single tables
Forward and backward scanning of data.	Only forward scanning is possible
We can make several edits on the dataset.	It is read only record set.
Occupy more memory	Occupy less memory.
Relationship can be maintained	Relationship can’t be maintained.
Relatively slower data access	Faster than data set.
Works with multiple records at a time.	Works with only 1 record at a time.
Transactions can be handled	No transaction can be handled as its read only.
Can be bind to multiple controls	Can be bind to a single control only
More overhead in memory as it is heavyweight	Lightweight object hence very less overhead
Supported by VS.Net tools	Must be manually coded.

Optimization Of Stored Procedure In SQL Server

This article describes that how we can optimize the Stored Procedures so that we can get better performance while our application is talking to database. I am enlisting some of the points from my personal experience.

 

1)    Always Use Fully Qualified Name For All Database Objects:

While working with stored procedure we need to pass name of database objects (table, view, function, other stored procedure(s) etc.) several times. I recommend every developer to use fully qualified object names instead of using only object name. There is a very strong reason behind this recommendation.

• 
  

  SQL server always has to find supplied database object name from sys object that took some millisecond of processing time and consumption of CPU resources. And if, we pass fully qualified name then that processing time and CPU resource consumption to search the object from sys objects get reduced to significant amount.

  
  


• 
  

  Another reason behind the scene is it helps SQL Server to directly finding the Complied Cache Plan if available instead of searching the objects in other possible schema. This process of searching and deciding a schema for a database object leads to COMPILE lock on stored procedure which decreases the performance of a stored procedure.

  
  

How to improve performance of Database Operations in application

 Hello Guys.

                   Today I am going to explain some tips that I have realized to improve the performance of web application or portal in context of database operation. We all know that without any database no dynamic website/application or portal can function.  So it’s very important for us to effectively work with database. Following are some of the points which we need to consider while designing and developing our application so that its performance is always better. And we wouldn’t have to worry after the application development that how to optimize this application as its performance is very poor with respect to others.

Dynamic SQL Statement in SQL Server

Dynamic SQL:

• 
  

  A dynamic sql statement is a set of sql statements which are constructed at execution time.

  
  


• 
  

  We may have to face certain condition in our application development where we might have to retrieve records from different table based on different conditions then in that scenario we do use dynamic SQL.

  
  


• 
  

  These dynamic Sql statement doesn’t parsed at compile time so it may introduce security vulnerabilities in our databse so we should try to avoid using dynamic sql as much as possible.

  
  


• 
  

  There are two ways to execute a dynamic sql statement in sql server:-

  
  

sp_executesql
EXECUTE()

• 
  

  Although these two methods produces same results but there might be certain scenario where it may produce different results.

  
  

Following is little description about the above two methods:

1.       sp_executesql :-

• 
  

  It is a system stored procedure.

  
  


• 
  

  It allows parameters to be passed IN or OUT of the dynamic sql statement.

  
  


• 
  

  It is less susceptible to SQL Injection.

  
  


• 
  

  Higher chance for sql string to remain in cache which results better performance when the same sql statement is executed.

  
  


• 
  

  Clean code hence easier to read and maintain.

  
  


• 
  

  Support parameter substitution hence more preferable than EXECUTE command.

  
  


• 
  

  Syntax:-

  
  

                       sp_executesql [@sqlstmt ],[ @ParameterDefinitionList],[ @ParameterValueList ]

2.       EXECUTE():-

• 
  

  When we use this command the parameters should be converted to characters.

  
  


• 
  

  Syntax:-

  
  

EXECUTE (@sqlStmt)

Example:-

Create procedure sp_GetSalesHistory

(

                @WhereClouse nvarchar(2000)=NULL,

                @TotalRowsReturned  INT OPUTPUT

)

AS

BEGIN

                DECLARE @SelectStmt         nvarchar(),

                DECLARE @FullStmt             nvarchar(),

                DECLARE @ParameterList   nvarchar()

                SET @ ParameterList   = ’@TotalRowsReturned  INT  OUTPUT ’

SET @ SelectStmt       =  ‘SELECT @ TotalRowsReturned   = COUNT(*) FROM SalesHistory’

SET @ FullStmt           = @ SelectStmt     + ISNULL(@WhereClouse,’ ’)

PRINT @ FullStmt

EXECUTE sp_executesql @ FullStmt           ,@ ParameterList   ,@ TotalRowsReturned  =@ TotalRowsReturned  OUTPUT

END

Bulk Insert or Update- SQL 2008

SQL SERVER - ColumnStore Index - Batch Mode vs Row Mode

Database Concept

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Top 10 features of SQL Server 2008

Introduction

                      I this article i am going to describe top 10 features of SQL Server 2008 database.

Description

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg.

Instead of:

DECLARE @myVar int
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

Temporary Table Concept In SQL Server

Introduction

This article covers the concept of temporary tables and tables variables in SQL server database.

Temporary Tables

Lets take a look at a simple CREATE TABLE statement that is going to create a temporary table:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Points to consider while designing database

SQL Server DO's and DON'Ts

DO know your tools.

Please, don't underestimate this tip. This is the best of all of those you'll see in this article. You'd be surprised of how many SQL Server programmers don't even know all T-SQL commands and all of those effective tools SQL Server has.

"What? I need to spend a month learning all those SQL commands I'll never use???" you might say. No, you don't need to. But spend a weekend at MSDN and browse through all T-SQL commands: the mission here is to learn a lot of what can and what can't be done. And, in the future, when designing a query, you'll remember "Hey, there's this command that does exactly what I need", and then you'll refer again to MSDN to see its exact syntax.

In this article I'll assume that you already know the T-SQL syntax or can find about it on MSDN.

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

 Following are some of the techniques that i remember now to optimize the SQL :--

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

Store and Retrieve Images in Sql Server ASP.NET

Intruduction

In this article i am going to write that how to store and retrieve images in our Sql Server Database.Let us start off by first creating a sample database and adding a table to it. We will call the database ‘Employee’ and the table will be called ‘EmpDetails’. This table will contain an image column along with some other columns. Run the following script in your SQL 2005 Query window (or server explorer) to construct the database and the table.

Database Script

CREATE DATABASE [Employee]

GO

USE [Employee]

GO

CREATE TABLE EmpDetails

(

empid int IDENTITY NOT NULL,

empname varchar(20),

empimg image

)

Step 1: Create a new asp.net website. In the code-behind, add the following namespace

C#

using System.Data.SqlClient;

VB.NET

Imports System.Data.SqlClient

Step 2: Drag and drop two label and one textbox control. Also drag drop a FileUpload control and a button control to upload the selected image on button click. As mentioned earlier, there are no validations performed. The source would look similar to the following:

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Save Retrieve Images</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

   

        <asp:Label ID="lblEmpName" runat="server" Text="Employee Name"></asp:Label>

&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:TextBox ID="txtEName" runat="server"></asp:TextBox>

        <br />

        <asp:Label ID="lblImage" runat="server" Text="Employee Image"></asp:Label>

&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:FileUpload ID="imgUpload" runat="server" />

        <br />

        <br />

        <asp:Button ID="btnSubmit" runat="server" onclick="btnSubmit_Click"

            Text="Submit" />

   

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp        <asp:Label ID="lblResult" runat="server" ForeColor="#0066FF"></asp:Label>

    <br />

    <hr />

     <asp:Image ID="Image1" style="width:200px" Runat="server" />  

    </div>

    </form>

</body>

</html>

Step 3: In the button click event, add the following code:

 C#

protected void btnSubmit_Click(object sender, EventArgs e)

    {

        SqlConnection connection = null;

        try

        {

            FileUpload img = (FileUpload)imgUpload;

            Byte[] imgByte = null;

            if (img.HasFile && img.PostedFile != null)

            {

                //To create a PostedFile

                HttpPostedFile File = imgUpload.PostedFile;

                //Create byte Array with file len

                imgByte = new Byte[File.ContentLength];

                //force the control to load data in array

                File.InputStream.Read(imgByte, 0, File.ContentLength);

            }

            // Insert the employee name and image into db

string conn = ConfigurationManager.ConnectionStrings ["EmployeeConnString"].ConnectionString;

            connection = new SqlConnection(conn);

            connection.Open();

string sql = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY";

            SqlCommand cmd = new SqlCommand(sql, connection);

            cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim());

            cmd.Parameters.AddWithValue("@eimg", imgByte);

            int id = Convert.ToInt32(cmd.ExecuteScalar());

            lblResult.Text = String.Format("Employee ID is {0}", id);

        }

        catch

        {

            lblResult.Text = "There was an error";

        }

        finally

        {

            connection.Close();

        }

    }

VB.NET

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim connection As SqlConnection = Nothing

        Try

            Dim img As FileUpload = CType(imgUpload, FileUpload)

            Dim imgByte As Byte() = Nothing

            If img.HasFile AndAlso Not img.PostedFile Is Nothing Then

                'To create a PostedFile

                Dim File As HttpPostedFile = imgUpload.PostedFile

                'Create byte Array with file len

                imgByte = New Byte(File.ContentLength - 1) {}

                'force the control to load data in array

                File.InputStream.Read(imgByte, 0, File.ContentLength)

            End If

            ' Insert the employee name and image into db

            Dim conn As String = ConfigurationManager.ConnectionStrings("EmployeeConnString").ConnectionString

            connection = New SqlConnection(conn)

            connection.Open()

            Dim sql As String = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY"

            Dim cmd As SqlCommand = New SqlCommand(sql, connection)

            cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim())

            cmd.Parameters.AddWithValue("@eimg", imgByte)

            Dim id As Integer = Convert.ToInt32(cmd.ExecuteScalar())

            lblResult.Text = String.Format("Employee ID is {0}", id)

        Catch

            lblResult.Text = "There was an error"

        Finally

            connection.Close()

        End Try

    End Sub

                         In the code above, i have created a byte array equal to the length of the file. The byte array will store the image. Then load the image data into the array. The record containing the Employee Name and Image is then inserted into the database using the ADO.NET code. The ID inserted is returned back using the @@Identity. We will shortly use this ID and pass it as a query string parameter to the ShowImage handler. The image will then be fetched against the EmployeeID (empid).

Step 4: In order to display the image on the page, we will create an Http handler. To do so, right click project > Add New Item > Generic Handler > ShowImage.ashx. The code shown below, uses the Request.QueryString[“id”] to retrieve the EmployeeID from it. The ID is then passed to the ‘ShowEmpImage()’ method where the image is fetched from the database and returned in a MemoryStream object. We then read the stream into a byte array. Using the OutputStream.Write(), we write the sequence of bytes to the current stream and you get to see your image.

C#

<%@ WebHandler Language="C#" Class="ShowImage" %>

using System;

using System.Configuration;

using System.Web;

using System.IO;

using System.Data;

using System.Data.SqlClient;

public class ShowImage : IHttpHandler

{

    public void ProcessRequest(HttpContext context)

    {

       Int32 empno;

       if (context.Request.QueryString["id"] != null)

            empno = Convert.ToInt32(context.Request.QueryString["id"]);

       else

            throw new ArgumentException("No parameter specified");

       context.Response.ContentType = "image/jpeg";

       Stream strm = ShowEmpImage(empno);

       byte[] buffer = new byte[4096];

       int byteSeq = strm.Read(buffer, 0, 4096);

       while (byteSeq > 0)

       {

           context.Response.OutputStream.Write(buffer, 0, byteSeq);

           byteSeq = strm.Read(buffer, 0, 4096);

       }       

       //context.Response.BinaryWrite(buffer);

    }

    public Stream ShowEmpImage(int empno)

    {

 string conn = ConfigurationManager.ConnectionStrings     ["EmployeeConnString"].ConnectionString;

        SqlConnection connection = new SqlConnection(conn);

        string sql = "SELECT empimg FROM EmpDetails WHERE empid = @ID";

        SqlCommand cmd = new SqlCommand(sql,connection);

        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("@ID", empno);

        connection.Open();

        object img = cmd.ExecuteScalar();

        try

        {

            return new MemoryStream((byte[])img);

        }

        catch

        {

            return null;

        }

        finally

        {

            connection.Close();

        }

    }

    public bool IsReusable

    {

        get

        {

            return false;

        }

    }

}

VB.NET

<%@ WebHandler Language="vb" Class="ShowImage" %>

Imports System

Imports System.Configuration

Imports System.Web

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Public Class ShowImage

    Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

        Dim empno As Int32

        If Not context.Request.QueryString("id") Is Nothing Then

            empno = Convert.ToInt32(context.Request.QueryString("id"))

        Else

            Throw New ArgumentException("No parameter specified")

        End If

        context.Response.ContentType = "image/jpeg"

        Dim strm As Stream = ShowEmpImage(empno)

        Dim buffer As Byte() = New Byte(4095) {}

        Dim byteSeq As Integer = strm.Read(buffer, 0, 4096)

        Do While byteSeq > 0

            context.Response.OutputStream.Write(buffer, 0, byteSeq)

            byteSeq = strm.Read(buffer, 0, 4096)

        Loop

        'context.Response.BinaryWrite(buffer);

    End Sub

    Public Function ShowEmpImage(ByVal empno As Integer) As Stream

        Dim conn As String = ConfigurationManager.ConnectionStrings("EmployeeConnString").ConnectionString

        Dim connection As SqlConnection = New SqlConnection(conn)

        Dim sql As String = "SELECT empimg FROM EmpDetails WHERE empid = @ID"

        Dim cmd As SqlCommand = New SqlCommand(sql, connection)

        cmd.CommandType = CommandType.Text

        cmd.Parameters.AddWithValue("@ID", empno)

        connection.Open()

        Dim img As Object = cmd.ExecuteScalar()

        Try

            Return New MemoryStream(CType(img, Byte()))

        Catch

            Return Nothing

        Finally

            connection.Close()

        End Try

    End Function

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable

        Get

            Return False

        End Get

    End Property

End Class

Step 5: One final step. Add the following code in the button click (just above the catch block) to call the handler and display the newly inserted image from the database. In the code below, we pass the EmployeeID as a query string parameter to the Http Handler.

C#

// Display the image from the database

Image1.ImageUrl = "~/ShowImage.ashx?id=" + id;

VB.NET

' Display the image from the database

 Image1.ImageUrl = "~/ShowImage.ashx?id=" & id

That’s it. Run the code and check out the functionality. Just change the connection string in the web.config to point to your database. The code works fine for .jpg, .gif and .bmp images. I would encourage you to extend the sample and include validations in it. Some validations could be to check the size of the image uploaded, make sure that only images are uploaded, check the length of the Employee name, prevent the user from entering numeric and special characters, so on and so forth.

                   I hope this article was useful and I thank you for viewing it.If you like this post , i request you to rate this article.