How to find row size of the table in MS SQL

 

Some times you may required to know each row size in your table. Today I am going to present you a simple query which is helpful to find the row size of your table in MS SQL.

First we will see the design of the table,

CREATE TABLE [dbo].[Customer](

    [id] [int] NOT NULL,

    [name] [nchar](10) NULL,

    [Address] [nvarchar](50) NULL,

 ) ON [PRIMARY]

GO

Now we will insert some values to the table

INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'John','Spain')

INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'James','Wales')

INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'Michael','Scotland')

INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'Mark','United Kingdom')

GO

Now we will see the query to get the row size,

select

row_number() over (order by id desc) as [ROW],

ISNULL(datalength(id),0)+

ISNULL(datalength(name),0)+

ISNULL(datalength(Address),0) as RowSize

from Customer

Output of the above query will be

ROW    RowSize

1    52

2    40

3    34

4    34

Cheers!

How to find application path in Console/VB.NET windows application

In ASP.NET web application we normally use Server.MapPath to get the application root folder path. But what we will write in VB.NET windows and console application???

Here is the two lines of code which will help you to get the application path in VB.NET windows and console application.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

namespace ConsoleApplication2

{

    class TestConsole 

    {

        static void Main(string[] args)

        {

            string strLocation = System.AppDomain.CurrentDomain.BaseDirectory;

            Console.WriteLine(strLocation);

        }

    }

}

Below is the screen print of the above code with output,

Open MS Word document in web browser

 

This is one of the common request you will get when you do any file operation in your project. This code can be used to open EXCEL, Power Point, PDF etc.

protected void btnWord_Click(object sender, EventArgs e)

{

    string strWordFile = @"c:/Test.docx";

    FileInfo objDoc = new FileInfo(strWordFile);

    Response.Clear();

    Response.ContentType = "Application/msword";

    Response.AddHeader("content-disposition", "attachment;filename=" + objDoc.Name);

    Response.AddHeader("Content-Length", objDoc.Length.ToString());

    Response.ContentType = "application/octet-stream";

    Response.TransmitFile(objDoc.FullName); 

    Response.End(); 

}

How to add different column values as Tooltip for DropDownList in ASP.NET

 

Sometimes we may have to show different field from the database as tool tip for the DropDownList in ASP.NET.

Below is a sample code to display the tooltip for DropDownList. Here I have used different field from the database to show as tooltip.

protected void btnLoad_Click(object sender, EventArgs e)

        {

            string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnection"].ToString();

            SqlConnection conn = new SqlConnection(strCon);

            conn.Open();

            SqlDataAdapter da = null;

            DataSet dsCountry = new DataSet();

            try

            {

                string strCountrySQL = "select id, name, Address from dbo.Customer";

                da = new SqlDataAdapter(strCountrySQL, conn);

                da.Fill(dsCountry);

                DropDownList1.DataSource = dsCountry;

                DropDownList1.DataTextField = "name";

                DropDownList1.DataValueField = "id";

                DropDownList1.DataBind();

                int ItemCount = DropDownList1.Items.Count;

                for (int i = 0; i < ItemCount; i++)

                {

                    DropDownList1.Items[i].Attributes.Add("Title", dsCountry.Tables[0].Rows[i]["Address"].ToString());

                }

                DropDownList1.Items.Insert(0, new ListItem("--Select--", "--Select--"));

                DropDownList1.SelectedItem.Selected = false;

                DropDownList1.Items.FindByText("--Select--").Selected = true;

            }

            catch (Exception ex)

            {

                lblMsg.Text = "Error!! <br>+" + ex.Message.ToString();

            }

            finally

            {

                dsCountry.Dispose();

                da.Dispose();

                conn.Close();

                conn.Dispose();

            }

Below is the screen print of the tooltip,

How to use IN Clause with parameter in MS SQL

 

You might be wondering how to pass the value as parameter in IN clause. It is a kind of tricky one. If you directly execute your select query by passing the parameter your query will through error.

Here we will see how it can be done,

First we will create the table

CREATE TABLE [dbo].[EMP](

    [ID] [int] NULL,

    [Name] [nchar](10) NULL

) ON [PRIMARY]

GO

Now we will insert some values to the table

INSERT INTO [Test].[dbo].[T] ([ID],[Name]) VALUES(1,'Mike')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(2,'John')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(3,'Russel')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(4,'Thomas')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(5,'David')

GO

Finally we will query the table using IN. I want all the Employee with the ID in 3,4,5 and our query for that should be

select * from t

Declare @id varchar(20)

set @id= '3,4,5'

exec('SELECT * FROM EMP WHERE ID IN('+@id+')')

Above query was useful if you wanted to use INT or any other numeric data type in your IN clause. What you will do if you wanted to use it for char data type?

Yes we will see that as well here.

Query for Char data type should be,

DECLARE @id VARCHAR(MAX)

set @id= 'Mike,John,David'

SELECT @id = '''' + REPLACE(@id,',',''',''') + ''''

EXEC('SELECT * FROM EMP WHERE Name IN('+@id+')')

GO

Result after executing above query for Int data type,

Result after executing above query for CHAR data type,

How to call Stored Procedure from Trigger in MS SQL

 

Another interesting Interview question “Can we call Stored procedure form Trigger in MS SQL”? What will be your answer YES or NO? Answer should always be “YES”.

You can call Stored Procedure from trigger. In this article we will go through a simple After Insert trigger as an example.

create trigger trg_Ins_CustTrigger on Customer

After Insert 

AS

Declare @Name as varchar(100)

Declare @Address as varchar(100)

select @Name=Name, @Address=Address from inserted

exec CustDet @Name, @Address

I believe above code is self explanatory as the table and field names I have used is a clear English text.

If you have any question please post it in comment section.

How to make Marquee in ASP.NET web page and dynamically assign the value to it.

 

When you make a web application it is always good to make a colourful user interface.  One of the most attractive feature in HTML is Marquee. Today we will see how to use marquee in ASP.NET web application.

Here we will use label to assign the text which moves inside the marquee tag.

We will see the code now,

<div> 

<marquee direction="right" scrollamount="2" loop="true" width="100%" bgcolor="#ffffff" >

    <asp:Label id="lblMarquee" runat="server" ForeColor="ForestGreen" Font-Bold="True" ></asp:Label>

</marquee>

</div>

Here what we are doing is we kept the label inside marquee tag so label will move based on the marquee settings.

Now if you assign any value from your code behind to this label it will move or rotate based on the marquee settings.

Microsoft open sourcing ASP.NET MVC 4, ASP.NET Web API, ASP.NET Web Pages v2 (Razor) - under the Apache 2.0 license.

 

Microsoft announce that developers outside the Microsoft can now submit code snippet and patches which they may review and include in the products.

You can find more details on Scott Guthrie's blog

“We will also for the first time allow developers outside of Microsoft to submit patches and code contributions that the Microsoft development team will review for potential inclusion in the products,” Guthrie says. “We announced a similar open development approach with the Windows Azure SDK last December, and have found it to be a great way to build an even tighter feedback loop with developers – and ultimately deliver even better products as a result.”

Also you can find the source on CodePlex.

Query to find number of users connected in an MS SQL database

 

Sometimes you may get an error from your application saying "Connection pool size exceeded".

Here I am going to explain you few queries which can be used to find out the number of open connections in your MS SQL database and also the query to find the maximum connections allowed in SQL server.

First we will check, what is the maximum connections SQL server will allow you to connect.

SELECT @@MAX_CONNECTIONS AS 'Maximum Connections Allowed in the database'

Now you know that how many connections you can have in your database. So we will check how many are already used.

Below query can be used to find the number of connections in each database in your server if you execute this query against master database.

SELECT DB_NAME(dbid) as [Database Name], COUNT(dbid) as [Number of Connections] from master.dbo.sysprocesses with (nolock)

WHERE dbid > 0 GROUP BY dbid

Now we will see who all are the users connected to your database.

Below query will show you the login name and the host name of the user connected to your database. In below query you may change the database name when you execute in your system.

SELECT LOGINAME=RTRIM(LOGINAME),HOSTNAME FROM MASTER.DBO.SYSPROCESSES

WHERE DB_NAME(DBID) = 'DataBaseName' AND DBID != 0

Now at the end we will see a very simple query which can be used to show all the sessions that are currently established in the database.

sp_who2

How to Export Gridview data to Excel with special characters in ASP.NET

Many times you may get requirement to export data to excel with special characters. When you do normal export with special characters your excel file will have weird characters in place of special character.

So in such cases what you have to do is, Unicode encoding. I have given sample code below.

private void ExportToExcel()

      {

          string Excelfilename = "Test_Excel_Export" + DateTime.Now;

          Response.Clear();

          Response.Buffer = true;

          Response.ContentType = "application/vnd.ms-excel";

          Response.AppendHeader("Content-Disposition:", "attachment; filename=" + Excelfilename + ".xls");

          Response.Charset = "";

          Response.ContentEncoding = Encoding.Unicode;

          Response.BinaryWrite(Encoding.Unicode.GetPreamble());

          this.EnableViewState = false;

          System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

          System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

          GridView1.RenderControl(oHtmlTextWriter);

          Response.Write(oStringWriter.ToString());

          Response.End();

      }

In the above code two import lines of code which does the encoding is below,

Response.ContentEncoding = Encoding.Unicode;

Response.BinaryWrite(Encoding.Unicode.GetPreamble());

To use above code you have to add below namespace otherwise you will get error.

using System.Text;

I hope now you are very clear about “How to Export Gridview data to Excel with special characters in ASP.NET”.

You are always welcome to post your queries below.

How to list all the constraints in SQL Server Database

 

There are many methods to lists the constraints in SQL database. We will go through few methods one by one.

Method 1:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Above query will return all the constraints in all the database.

Method 2:

SELECT * FROM [DataBaseName].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Above query will return all the constraints in the specific database.

Method 3:

SELECT OBJECT_NAME(OBJECT_ID) AS [Constraint_Name],

OBJECT_NAME(parent_object_id) AS [TableName],

type_desc AS [TypeOfConstraint]

FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT'

This method will give bit more details about the constraints. This can be used to return all constraints in the database with Constraint Name, Table Name and Type of Constraint.

Method 4:

sp_help TableName

This query will return all the constraints in the specific table.