Find Stored Procedure Related to Table in Database

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

How to use modal popup inside the gridview

Here is some code sample on how to use the modal-up inside gridview, here is sample screenshot of my project.

Gridview with Join button that will trigger the modal popup.

Modal popup confirmation after clicking the Join button

The trick to do this is just to add a hidden button to be used as the TargetControlID as you code see in aspx code below:

<asp:Button id="btnShowPopup" runat="server" style="display:none" />

<asp:ModalPopupExtender ID="JoinModal" runat="server" PopupControlID="pnlJoin"

TargetControlID="btnShowPopup" BackgroundCssClass="modalBackground"                                            CancelControlID="bnCancel">

asp:ModalPopupExtender>

Modal popup code:

Gridview code

And from the code behind all you need is to call the ".Show()" method of the modal popup extender.

protected void grdResult_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Join")
            {
                    this.updJoin.Update();// use this if you need to update something inside the modal panel
                    JoinModal.Show();
            }
           
        }

GridView With ScrollBars

GridView still have not ScrollBars property. To create GridView with horizontal or vertical scrollbar you need to place GridView inside

tag or inside Panel control.

GridView inside div  HTML tag

To produce GridView scrollbars with div tag, use this code:

<div style="width:100%; height:300; overflow:auto;">
<asp:GridView ID="GridView1" runat="server">
asp:GridView>
div>

GridView inside Panel Control

To create GridView scrollbars with a little help of Panel control, use this code:

<asp:Panel ID="Panel1" runat="server" ScrollBars="Both" Height="300"Width="100%">
 <asp:GridView ID="GridView1" runat="server">
 asp:GridView>
asp:Panel>

Error message when you connect to an instance of SQL Server: "(Cannot open user default database error 4064)"

SQL Server 2005

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:
• If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

sqlcmd –E -S InstanceName –d master

o    If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

sqlcmd -S InstanceName -d master -U SQLLogin -P Password

3.     Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.

4.     At the sqlcmd prompt, type the following, and then press ENTER:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.

5.     At the sqlcmd prompt, type GO, and then press ENTER.

SQL Server Paging sample utility

The spUtilPAGEprocedure accepts 6 parameters:

@datasrc - the table (or stored procedure, etc.) name
@orderBy - the ORDER BY clause
@fieldlis - the fields to return (including calculated expressions)
@filter - the WHERE clause
@pageNum - the page to return (must be greater than or equal to one)
@pageSize - the number of records per page

CREATE PROCEDURE [dbo].[spUtilPAGE]
  @datasrc nvarchar(200)
 ,@orderBy nvarchar(200)
 ,@fieldlist nvarchar(200) = '*'
 ,@filter nvarchar(200) = ''
 ,@pageNum int = 1
 ,@pageSize int = NULL
AS
  SET NOCOUNT ON
  DECLARE
     @STMT nvarchar(max)         -- SQL to execute
    ,@recct int                  -- total # of records (for GridView paging interface)

  IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
  IF @pageSize IS NULL BEGIN
    SET @STMT =  'SELECT   ' + @fieldlist + 
                 'FROM     ' + @datasrc +
                 'WHERE    ' + @filter + 
                 'ORDER BY ' + @orderBy
    EXEC (@STMT)                 -- return requested records 
  END ELSE BEGIN
    SET @STMT =  'SELECT   @recct = COUNT(*)
                  FROM     ' + @datasrc + '
                  WHERE    ' + @filter
    EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
    SELECT @recct AS recct       -- return the total # of records

    DECLARE
      @lbound int,
      @ubound int

    SET @pageNum = ABS(@pageNum)
    SET @pageSize = ABS(@pageSize)
    IF @pageNum < 1 SET @pageNum = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@pageNum - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
      SET @ubound = @recct + 1
      SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if                                               -- no records would be on the
                                              -- specified page
    END
    SET @STMT =  'SELECT  ' + @fieldlist + '
                  FROM    (
                            SELECT  ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
                            FROM    ' + @datasrc + '
                            WHERE   ' + @filter + '
                          ) AS tbl
                  WHERE
                          row > ' + CONVERT(varchar(9), @lbound) + ' AND
                          row < ' + CONVERT(varchar(9), @ubound)
    EXEC (@STMT)                 -- return requested records 
  END

client side validation using javascrip in asp.net

pass argument in imagebutton inside gridview

aspx code

protected void btn_Click(object sender, ImageClickEventArgs e)
        {
            ImageButton btn = (ImageButton)sender;

            int id = Convert.ToInt32(btn.CommandArgument.ToString());

            switch (btn.CommandName.ToString())
            { 
            
                case "Edit":
                    Session["customer_id"] = id;
                    //Response.Redirect("~/Customer/CustomerDetails.aspx");
                    Response.Redirect("~/Customer/CustomerDetails.aspx");
                    break;
                case "Delete":
                    
                    Session["customer_id"] = id;

                    var cust = new CRM.Lib.Customer(); 
                    cust.Delete(Convert.ToInt32(Session["customer_id"].ToString()));
                    RefreshList();
                    break;
            }
        }

Saving record using stored procedure

CREATE PROCEDURE [dbo].[spHelpdeskUpdate]
(
    @id int,
    @customername varchar(250),
    @contactno varchar(25),
    @email varchar(50),
    @details varchar(8000),
    @assignedstaff varchar(50),
    @requesttypeid int,
    @statustype int,
    @requestdate datetime,
    @solution varchar(8000),
    @solvedate datetime = null,
    @discontinued bigint,
    @createdby varchar(25),
    @createddate datetime,
    @lastmodifiedby varchar(25),
    @lastmodifieddate datetime = null
)
AS
BEGIN
    SET NOCOUNT ON;

    IF @id= 0
        BEGIN
            INSERT INTO Helpdesk
            (CustomerName,
            ContactNo,
            Email,
            Details,
            AssignedStaff,
            RequestTypeID,
            StatusType,
            RequestDate,
            Solution,
            SolveDate,
            Discontinued,
            CreatedBy,
            CreatedDate,
            LastModifiedBy,
            LastModifiedDate)
            VALUES
            (@customername,
            @contactno,
            @email,
            @details,
            @assignedstaff,
            @requesttypeid,
            @statustype,
            @requestdate,
            @solution,
            @solvedate,
            @discontinued,
            @createdby,
            @createddate,
            @lastmodifiedby,
            @lastmodifieddate)

        SELECT SCOPE_IDENTITY() AS theID

        END
    ELSE
        BEGIN
            UPDATE Helpdesk
            SET
                CustomerName=@customername,
                ContactNo=@contactno,
                Email=@email,
                Details=@details,
                AssignedStaff=@assignedstaff,
                RequestTypeID=@requesttypeid,
                StatusType=@statustype,
                RequestDate=@requestdate,
                Solution=@solution,
                SolveDate=@solvedate,
                Discontinued=@discontinued,
                CreatedBy=@createdby,
                CreatedDate=@createddate,
                LastModifiedBy=@lastmodifiedby,
                LastModifiedDate=@lastmodifieddate
            WHERE ID = @id
            SELECT @id AS theID

        END
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

DAL code c#

using System;
using System.Data;
using System.Data.SqlClient;

namespace CRM.Lib.DAL
{
    internal class HelpdeskActionDAL : BaseDAL
    {

        public DataTable GetAll()
        {
            base.com.CommandText = "spHelpdeskAction";
            base.com.Parameters.AddWithValue("@id", 0);
            return base.GetDataTable();

        }

        public DataRow GetById(int id)
        {
            base.com.CommandText = "spHelpdeskAction";
            base.com.Parameters.AddWithValue("@id", id);
            return base.GetFirstRow();
        }

        public int Save(int id, int helpdeskid, string actionspecifics, string actionby, string resultspecifics, DateTime actiondate, string endorsedto, DateTime endorseddate, DateTime daterecorded, string createdby, DateTime createddate, string modifiedby, DateTime modifieddate, out string message)
        {
            message = "";
            base.com.CommandText = "spHelpdeskActionUpdate";
            base.com.Parameters.AddWithValue("@id", id);
            base.com.Parameters.AddWithValue("@helpdeskid", helpdeskid);
            base.com.Parameters.AddWithValue("@actionspecifics", actionspecifics);
            base.com.Parameters.AddWithValue("@actionby", actionby);
            base.com.Parameters.AddWithValue("@resultspecifics", resultspecifics);
            base.com.Parameters.AddWithValue("@actiondate", actiondate);
            base.com.Parameters.AddWithValue("@endorsedto", endorsedto);
            base.com.Parameters.AddWithValue("@endorseddate", endorseddate);
            base.com.Parameters.AddWithValue("@daterecorded", daterecorded);
            base.com.Parameters.AddWithValue("@createdby", createdby);
            base.com.Parameters.AddWithValue("@createddate", createddate);
            base.com.Parameters.AddWithValue("@modifiedby", modifiedby);
            base.com.Parameters.AddWithValue("@modifieddate", modifieddate);


            int ra = 0;
            try
            {
                ra = Convert.ToInt32(base.com.ExecuteScalar());
            }
            catch (SqlException sqlex)
            {
                switch (sqlex.Number)
                {
                    case 2601:
                        message = "HelpdeskAction Name already exists!";
                        break;
                    default:
                        message = "Update failed!";
                        break;
                }
            }
            return ra;
        }

        public bool Delete(int id)
        {
            base.com.CommandText = "spHelpdeskActionDelete";
            base.com.Parameters.AddWithValue("@id", id);

            int ra;
            try
            {
                ra = Convert.ToInt32(base.com.ExecuteScalar());
            }
            catch
            {
                throw new Exception("Delete helpdeskaction failed!");
            }
            return (ra > 0);
        }

    }
}

BLL code c#

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using CRM.Lib.DAL;

namespace CRM.Lib
{
    public class Helpdesk
    {

        #region CONSTRUCTOR
        public Helpdesk()
        {
            this.init();
        }

        public void init()
        {
            this.ID = 0;
            
            this.LastName = "";
            this.FirstName = "";
            this.MiddleName = "";
            this.VIPNo = "";
            this.ContactNo = "";
            this.Email = "";
            this.Details = "";
            this.Branch = "";
            this.AssignedStaff = "";
            this.StatusType = 0;
            this.RequestDate = DateTime.Now;
            this.RequestTime = DateTime.Now;
            this.Solution = "";
            this.Discontinued = 0;
            this.CreatedBy = "";
            this.CreatedDate = DateTime.Now;
            this.LastModifiedBy = "";
            this.LastModifiedDate = DateTime.Now;

        }
        #endregion
        #region Properties
        public int ID { get; set; }
        public string CustomerName {
            get { return this.FirstName + " " + this.MiddleName + " " + this.LastName; } 
        }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string VIPNo { get; set; }
        public string ContactNo { get; set; }
        public string Email { get; set; }
        public string Details { get; set; }
        public string Branch { get; set; }
        public string AssignedStaff { get; set; }
        public int StatusType { get; set; }
        public string Status {
            get { return CRM.Lib.StatusType.GetById(this.StatusType).Status.ToString(); } 
        }
        public DateTime RequestDate { get; set; }
        public DateTime RequestTime { get; set; }
        public string Solution { get; set; }
        public int Discontinued { get; set; }
        public string CreatedBy { get; set; }
        public DateTime CreatedDate { get; set; }
        public string LastModifiedBy { get; set; }
        public DateTime LastModifiedDate { get; set; }

        #endregion

        #region Public Methods
        public static Helpdesk GetById(int id)
        {
            var dal = new HelpdeskDAL();
            var instance = new Helpdesk();
            instance.Bind(dal.GetById(id));
            return instance;
        }
        public static List GetAll()
        {
            var dal = new HelpdeskDAL();
            var collection = new List();
            foreach (DataRow row in dal.GetAll().Rows)
            {
                var instance = new Helpdesk();
                instance.Bind(row);
                collection.Add(instance);
            }
            return collection;
        }
        public static List GetByStaffId(string id)
        {
            var dal = new HelpdeskDAL();
            var collection = new List();
            foreach (DataRow row in dal.GetByStaffId(id).Rows)
            {
                var instance = new Helpdesk();
                instance.Bind(row);
                collection.Add(instance);
            }
            return collection;
        }
        public void Bind(DataRow row)
        {
            if (row != null)
            {
                this.ID = Convert.ToInt32(row["ID"]);
                
                this.LastName = Convert.ToString(row["LastName"]);
                this.FirstName = Convert.ToString(row["FirstName"]);
                this.MiddleName = Convert.ToString(row["MiddleName"]);
                this.VIPNo = Convert.ToString(row["VIPNo"]);
                this.ContactNo = Convert.ToString(row["ContactNo"]);
                this.Email = Convert.ToString(row["Email"]);
                this.Details = Convert.ToString(row["Details"]);
                this.Branch = Convert.ToString(row["Branch"]);
                this.AssignedStaff = Convert.ToString(row["AssignedStaff"]);
                this.StatusType = Convert.ToInt32(row["StatusType"]);
                this.RequestDate = Convert.ToDateTime(row["RequestDate"]);
                this.RequestTime = Convert.ToDateTime(row["RequestTime"]);
                this.Solution = Convert.ToString(row["Solution"]);
                this.Discontinued = Convert.ToInt32(row["Discontinued"]);
                this.CreatedBy = Convert.ToString(row["CreatedBy"]);
                this.CreatedDate = Convert.ToDateTime(row["CreatedDate"]);
                this.LastModifiedBy = Convert.ToString(row["LastModifiedBy"]);

                if (!DBNull.Value.Equals(row["LastModifiedDate"]))
                    this.LastModifiedDate = Convert.ToDateTime(row["LastModifiedDate"]);

            }
        }
        public bool Save()
        {
            var dal = new HelpdeskDAL();

            string message = "";
            int ret = dal.Save(this.ID, this.CustomerName,this.LastName,this.FirstName,this.MiddleName, this.VIPNo, this.ContactNo, this.Email, this.Details, this.Branch, this.AssignedStaff, this.StatusType, this.RequestDate, this.RequestTime, this.Solution, this.Discontinued, this.CreatedBy, this.CreatedDate, this.LastModifiedBy, this.LastModifiedDate, out message);

            this.ID = ret;
            return (ret > 0);
        }
        public bool Delete()
        {
            var dal = new HelpdeskDAL();
            bool ret = dal.Delete(this.ID);
            return ret;
        }
        #endregion
    }
}

my css templates

   .Main
{
 background-color:#6591CD;
 background-image:url(images/DataEntry/splitter_bg.gif);
 background-position:left top;
 background-repeat:repeat-x;
 margin-left:0;
 margin-top:0;
}

.pageHeader
{
 background-image:url(images/TitlebarBackground.png);
 font-family: tahoma, verdana;
 font-size: 11px;
 height: 50px;
 font-weight:bold;
 color: #15428B;
}
.pageFooter
{
 background-image:url(images/Footer1.png);
 height: 50px;
}
.pagePanel
{
 background-image:url(images/ToolBar/GroupBackground.png);
 height: 82px;
 top: 40px; 
 left: 5px; 
 width: 500px;
}
.quickAccess
{
 background-image:url(images/QuickAccess/Background.gif);
 width:100px;
}
.tabStrip
{
 background-image:url(images/ToolBar/Background.gif);
 
}
.accordionHeader
{
    border: 1px solid #3399FF;
    color: white;
    background-color: #2E4d7B;
    background-image:url(images/item_bg.gif);
 font-family: tahoma, verdana;
 font-size: 11px;
 font-weight: bold;
    padding: 9px;
    margin-top: 1px;
    cursor: pointer;
    text-decoration: none;
}
.accordionHeader a
{
 text-decoration: none;
}
.accordionContent
{
    background-color: #FFFFFF;
    border: 1px solid #3399FF;
    font-family: tahoma, verdana;
 font-size: 11px;
    border-top: none;
    padding: 5px;
    padding-top: 1px;
    padding-left:1px;
    padding-bottom:1px;
    cursor: pointer;
}

.accordionHeaderSelected
{
 border: 1px solid #3399FF;
    color: white;
    background-color: #2E4d7B;
    background-image:url(images/item_expanded_bg.gif);
 font-family: tahoma, verdana;
 font-size: 11px;
 font-weight: bold;
 text-decoration: none;
    padding: 9px;
    margin-top: 1px;
    cursor: pointer;
}
.accordionHeaderSelected a
{
 text-decoration: none;
}
.tableMenu a
{
 width:100%;
 background-color: #FFFFFF;
 text-align: left;
 display:block;
 text-decoration: none;
 font-family:Tahoma,verdana;
 font-size :11px;
 color:Black;
 padding:7px;
}
.tableMenu a:hover
{
 text-decoration: none;
 background-color: #E1EAF7;
 color: black;
}
.tableMenu a:selected
{
 text-decoration: none;
 background-color: #E1EAF7;
 color: black;
 font-weight:bold;
}

.popupMenu a
{
 width:100%;
 background-color: #FFFFFF;
 text-align: left;
 display:block;
 text-decoration: none;
 font-family:Tahoma,verdana;
 font-size :11px;
 color:Black;
 padding:5px;
}
.popupMenu a:hover
{
 text-decoration: none;
 background-color: #E1EAF7;
 color: black;
}
.popupMenu a:selected
{
 text-decoration: none;
 background-color: #E1EAF7;
 color: black;
 font-weight:bold;
}

.DataEntry
{
 font-family:Tahoma,verdana;
 font-size :11px;
 border:thin solid #FFFFFF;
}
.dataEntryHeader
{
 background-color: #BFDBFF;
 padding:5px;
 font-weight:bold;
 color: #15428B;
}
.toolBar
{
 background-image:url('images/DataEntry/bg.gif');
 background-color: #BFDBFF;
 width: 100%;
}

.moduleEntry
{
 background-color: #BFDBFF;
 font-family:Arial,Tahoma,Verdana;
 font-size:11px;
 color: #15428B;
}

.moduletabEntry
{
 font-family:Arial,Tahoma,Verdana;
 font-size:11px;
 color: #15428B;
}
.moduleAccess
{
 font-family:Arial,Tahoma,Verdana;
 font-size:11px;
}
.tabgroupBackGround
{
 background-color: #BFDBFF;
 font-family:Arial,Tahoma,Verdana;
 font-size:11px;
 color: #15428B;
 border:thin solid #587fb2;
}
.groupEntry
{
 background-color: #BFDBFF;
 border:solid thin #587fb2;
}

.gray .ajax__tab_header 
{
    font-family:Tahoma,Arial, Sans-Serif;
    background:url(images/tab/tab-line.png) repeat-x bottom;
    font-size:11px;
    font-weight:bold;
    display:block;
}
.gray .ajax__tab_header .ajax__tab_outer 
{
    background:url(images/tab/tab.png) no-repeat left top;
    border-color:#222;
    color:#222;
    padding-left:10px;
    margin-right:3px;
}
.gray .ajax__tab_header .ajax__tab_inner 
{
    background:url(images/tab/tab.png) no-repeat right top;
    border-color:#666;
    color:#666;
    padding:3px 10px 2px 0px;
}
.gray .ajax__tab_hover .ajax__tab_inner 
{
    color:#000;
}
.gray .ajax__tab_active .ajax__tab_outer 
{
    background:url(images/tab/tab.png) no-repeat 0pt -40px;
    border-bottom-color:#ffffff;
}
.gray .ajax__tab_active .ajax__tab_inner 
{
    background:url(images/tab/tab.png) no-repeat right -40px;
    color:#000;
    border-color:#333
}
.gray .ajax__tab_body 
{
    font-family:tahoma,verdana,helvetica;
    font-size:10pt;
    background-color:#DDEAFC;
    border:solid 1px #d7d7d7;
    border-top-width:0;
}

.Grid 
{ 
  padding: 3px; 
  padding-top: 2px; 
  padding-bottom: 1px; 
  border-bottom: 1px solid #E3EFFF; 
  font-family: Arial,Tahoma;
  font-size: 11px;
  color: #000000;
  background-color: #ffffff;
  cursor: pointer;
  border: 1px solid #3399FF; 
}

.Grid tr.normal
{
 color: black;
 background-color: #ffffff;
}

.Grid tr.alternate
{
 color: black;
 background-color: #ffffff;
}

.Grid tr.normal:hover, .Grid tr.alternate:hover
{
 background-color: #e5f1fb;
 color: black;
 font-weight: bold;
}
.GridHeadingRow 
{ 
  
  background-image: url(images/Grid/header1stCell_bg.gif);   
  font-family: Arial,Tahoma;
  font-size: 11px;
  font-weight:normal;
  padding:5px;
}

.LoginHeader
{
 background-image: url(images/Grid/header1stCell_bg.gif);  
 padding:5px;
 font-weight:bold;
 color: #15428B;
 
}

.SelectLink 
{
 display:none;
 width:0px;
}
.Paging
{
 background-color:#FFF;
 font-family: Arial,Tahoma;
 font-size: 11px;
}

.validatorCalloutHighlight
{
    background-color: lemonchiffon;
}


/*Textbox Watermark*/

.unwatermarked {
 height:18px;
 width:148px;
}

.watermarked {
 height:20px;
 width:150px;
 padding:2px 0 0 2px;
 border:1px solid #BEBEBE;
 background-color:#F0F8FF;
 font-family: Arial,Tahoma;
 font-size: 11px;
 color:Gray;
}

.pnlPopupContainer
{
    
    background-repeat: repeat;
    padding: 10px;
    width: 600px;
}

.pnlPopupContainer > div
{
    background-color:White;
    padding: 10px;
    border: 1px solid #FF0000;
    background-repeat: repeat;
}

.msg_button_class
{
   background-image: url(MsgBox/msg_button_1.jpg);
   font-family:Tahoma;
   font-size:11px;
   font-weight:bold;
   color:#FFFFFF;
   border:1px solid #DDDDDD;
   height:22px;
}
.progressBackgroundFilter {
  position:absolute;
  top:0px;
  bottom:0px;
  left:0px;
  right:0px;
  overflow:hidden;
  padding:0;
  margin:0;
  background-color:#000; 
  filter:alpha(opacity=50);
  opacity:0.5;
  z-index:1000;
}
.processMessage { 
  position:absolute; 
  top:30%; 
  left:43%;
  padding:10px;
  height:5%;
  width:14%;
  z-index:1001;
  background-color:#fff;
  font-family: tahoma, verdana;
  font-size: 11px;
  font-weight: bold;
}

.multiCombo
{
 color:Black;
 border:thin solid #587fb2;
 
 font-family:Tahoma,verdana;
 font-size :11px;

}

C# BaseDAL sample code

using System;
using System.Configuration;

using System.Data;

using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace CRM.Lib.DAL {
    internal abstract class BaseDAL {

        protected SqlConnection cnn;
        protected SqlCommand com;
        protected SqlTransaction tran;

        #region CTOR



        protected BaseDAL() {
            //add reference to System.Configuration.dll

            string connStr = "Data Source=" + ConfigurationManager.AppSettings["srv"] + ";";
            connStr += "user=sa;password=" + ConfigurationManager.AppSettings["pwd"] + ";";
            connStr += " database=" + ConfigurationManager.AppSettings["db"] + ";";

            //connection pooling
            /**/
            connStr += "connection reset=false;";
            connStr += "connection lifetime=0;";
            connStr += "enlist=true;";
            connStr += "min pool size=1;";
            connStr += "max pool size=50";
            /**/
            cnn = new SqlConnection(connStr);


            this.com = new SqlCommand();
            this.com.CommandType = CommandType.StoredProcedure;
            this.com.CommandTimeout = 120;
            //SqlDependency.Start(connD);
            try {
                if (this.cnn.State == ConnectionState.Closed) this.cnn.Open();
                this.com.Connection = cnn;
            }
            catch (Exception ex) {
#if DEBUG
                throw new Exception(ex.Message);
#else
                throw new Exception(ex.Message);
                //throw new Exception("error");
#endif
            }

        }

        ~BaseDAL() {
            try {
                //if (cnn != null)
                //    cnn.Dispose();
            }
            finally { }
        }

        #endregion

        #region Methods

        protected DataRow GetFirstRow() {
            DataTable dt = this.GetDataTable();

            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;

        }

        protected DataTable GetDataTable() {
            DataTable ret = new DataTable();
            try {
                ret.Load(this.com.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex) {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            cnn.Close();

            return ret;
        }

        protected DataTable GetDataTable2(string tablename) {
            DataTable ret = new DataTable();
            try {
                if (this.cnn.State == ConnectionState.Closed) this.cnn.Open();
                ret.Load(this.com.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex) {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            cnn.Close();

            return ret;
        }
        protected SqlDataReader GetDataReader() {
            return this.com.ExecuteReader(CommandBehavior.CloseConnection);

        }

        protected DataSet GetDataSet2(string parentname, string childname) {
            DataSet ds = new DataSet();

            DataTable parent = new DataTable(parentname);
            DataTable child = new DataTable(childname);

            try {
                SqlDataReader dr = this.com.ExecuteReader();

                parent.Load(dr);
                ds.Tables.Add(parent);

                dr.NextResult();

                child.Load(dr);

                ds.Tables.Add(child);


                dr.Close();
            }
            catch (Exception ex) {
                System.Diagnostics.Debug.Print(ex.Message);
            }


            cnn.Close();

            return ds;
        }

        void dep_OnChange(object sender, SqlNotificationEventArgs e) {
            System.Diagnostics.Debug.WriteLine("Received OnChange Event");

            //if (e.Info ==NpgsqlConnection.  SqlNotificationInfo.Invalid) {
            //    System.Diagnostics.Debug.WriteLine("Invalid Statement");
            //}
            return;
        }

        protected DataSet GetDataSet(string tablename) {
            SqlDataAdapter da = new SqlDataAdapter(this.com);

            DataSet ret = new DataSet();
            da.Fill(ret, tablename);

            cnn.Close();
            da.Dispose();

            return ret;
        }

        internal void Begin() {
            tran = this.cnn.BeginTransaction();
            this.com.Transaction = tran;
        }
        internal void End(bool Commit) {
            if (Commit)
                tran.Commit();
            else
                tran.Rollback();
        }

        #endregion
    }
}

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

Example below:

USE AdventureWorks2008R2;
GO
SELECT IDENT_CURRENT ('Person.Address') AS Current_Identity;
GO

Database Mail in SQL Server 2005

Database Mail has four components.

1.     Configuration Component
Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.
2.     Messaging Component
Messaging component is basically all of the objects related to sending email stored in the MSDB database.
3.     Database Mail Executable
Database Mail uses the DatabaseMail90.exe executable to send email.
4.     Logging and Auditing component
Database Mail stores the log information on MSDB database and it can be queried usingsysmail_event_log.



Step 1
Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.
In the SQL Server Management Studio, execute the following statement.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go

Alternatively, you could use the SQL Server Surface area configuration. Refer Fig 1.0.

Step 2
The Configuration Component Database account can be enabled by using the sysmail_add_accountprocedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and
makclaire@optimumonline.net as the e-mail account.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyMailAccount',
    @description = 'Mail account for Database Mail',
    @email_address = 'makclaire@optonline.net',
    @display_name = 'MyAccount',
 @username='makclaire@optonline.net',
 @password='abc123',
    @mailserver_name = 'mail.optonline.net'

Step 3
The second sub component of the configuration requires us to create a Mail profile.
In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'MyMailProfile',
       @description = 'Profile used for database mail'

Step 4
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyMailProfile',
    @account_name = 'MyMailAccount',
    @sequence_number = 1

Step 5
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyMailProfile',
    @principal_name = 'public',
    @is_default = 1 ;

Step 6
Now let us send a test email from SQL Server.
Please execute the statement below.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'HTML' ;