What is Store Procedure ? -- Store Procedure Advantages


What is Store Procedure?
Store Procedure is nothing but just a set of T-SQL statements combined to
Perform a single task of several tasks. It’s basically like a
Macro so when you invoke the Stored procedure, you actually
Run a set of statements.
In other Word We Can Say that Store Procedure is subprogram that performs a Specific task.
Advantages of Store Procedure
1.  Store Procedure are Faster because they eliminate the need of reparse
And reoptimize.
2.  Reduce Traffic.
3.  Store Procedure is Reusable Object.
4.  Performance and Easy of Maintain ace are to Good Point for Using Store Procedure
5.  They allow faster Execution because it’s in Pre-Compile Statement.
6.  We Can Execute SP in the SP.

PLEASE WRITE YOUR VIEW ON SP IN BELOW COMMENT BOX.


                 First page SP         Second Page SP         Third Page SP           Fourth Page SP

Posted in | 5 comments

Execute Store Procedure from Asp.net

Execute Store Procedure from Asp.net

In this Example I Show How to Set Parameter from Asp.Net application and execute Store Procedure from asp.net Application.
First Declare Private Variable for Store Procedure

private const string SP_USER_NEWDESG = "IDBadge_DesignationInsert";


Method to Set Parameter and Execute Store Procedure

public string Save_Designation(Desg desg_code)
    {
     
        // Execute SQL Command
        SqlCommand sqlCmd = new SqlCommand();
        AddParamToSQLCmd(sqlCmd, "@desg_code", SqlDbType.Int, 
256, ParameterDirection.Input, desg_code.desg_code);
        AddParamToSQLCmd(sqlCmd, "@desg_name", SqlDbType.NText,
256, ParameterDirection.Input, desg_code.desg_name);
        SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_USER_NEWDESG);
        return ExecuteScalarCmd(sqlCmd);
        
    }
  
private void AddParamToSQLCmd(SqlCommand sqlCmd, string paramId, SqlDbType sqlType, 
int paramSize, ParameterDirection paramDirection, object paramvalue)
    {
        // Validate Parameter Properties
        if (sqlCmd == null)
            throw (new ArgumentNullException("sqlCmd"));
        if (paramId == string.Empty)
            throw (new ArgumentOutOfRangeException("paramId"));

        // Add Parameter
        SqlParameter newSqlParam = new SqlParameter();
        newSqlParam.ParameterName = paramId;
        newSqlParam.SqlDbType = sqlType;
        newSqlParam.Direction = paramDirection;

        if (paramSize > 0)
            newSqlParam.Size = paramSize;

        if (paramvalue != null)
            newSqlParam.Value = paramvalue;

        sqlCmd.Parameters.Add(newSqlParam);
    }
  private string ExecuteScalarCmd(SqlCommand sqlCmd)
    {
        string result = "";
        using (SqlConnection cn = new SqlConnection(this.ConnectionString))
        {
            try
            {
                cn.Open();
                sqlCmd.Connection = cn;
                result = Convert.ToString(sqlCmd.ExecuteScalar());
                return result;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        
    } 
 
 
PLEASE WRITE YOUR VIEW ON SP IN BELOW COMMENT BOX.
 
 
  
 
 First page SP   Second Page SP   Third Page SP      Fourth Page SP 




Prev Post                                                      All Post                                                           Next Post

Posted in | 0 comments

Store Procedure Example (Select Statement with Passed Query as Parameter)


Store Procedure Example (Select Statement with Passed Query as Parameter)
In this Example I Show how to Write Store Procedure for Search Engine. in @query parameter you need to pass full Filter Section. Below Shown Simple example.
CREATE PROCEDURE IDBadge_RptEmployeePointSummaryReport
@query varchar(200)
AS
declare @filter nvarchar(1000)
select @filter = '
select count(ep.uid) as tot,p.point_type_name
from
employee_point as ep,
point_type_master as p
where p.point_type_code = ep.point_type_code
and  0=0 ' + @query
+' group by point_type_name'
exec (@filter)
GO
                                              
This Store Procedure return Datatable So in Programming Side you Need to handle that Datatable.


PLEASE WRITE YOUR VIEW ON SP IN BELOW COMMENT BOX.



                 First page SP         Second Page SP         Third Page SP           Fourth Page SP

Posted in | 0 comments

Store Procedure Example (How to Write Simple Store Procedure)

Store Procedure Example (How to Write Simple Store Procedure)

Here I Show How to Write Simple Store Procedure. Support there is table name with print_queue
Which has a column name badge_id and print_given_date.
Now you want to insert badge_id and print_given_date in table. Following Example Show how to Write Simple Insert Store Procedure.


CREATE PROCEDURE IDBadge_Insert_PrintQueue
@badge_id varchar(8),
@print_given_date datetime

AS
                  insert into print_queue
                          ( 
                          badge_id,
                          print_given_date
                           )
                         values
                          (
                         @badge_id,
                        @print_given_date
                         )

GO

In this Example you need to pass badge_id and Given_date value 
your Programming. 
 
 
PLEASE WRITE YOUR VIEW ON SP IN BELOW COMMENT BOX.
  
 
 
  First page SP      Second Page SP      Third Page SP      Fourth Page SP


Posted in | 0 comments

GridView Row Color

GridView Row Color :-

There is often required to changed display color of gridview.For Example some time it required’s that in Product detail we need to display Expired product row by red Color.It Can be possible in Gridview by RowDataCommand Event.
Here Example Shown in Which if Product is Active then LightGreen Color Shown Else Normally Color Display Which We Select in Gridview
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.BackColor = System.Drawing.Color.White;
// determine the value of the UnitsInStock field
LinkButton link_active = (LinkButton)e.Row.FindControl("link_active");
if (link_active.Text == "")
{
e.Row.BackColor = System.Drawing.Color.LightGreen;
}
}











Prev Post                                                      All Post                                                           Next Post

Posted in | 0 comments

Why do I get the error message "Server Application Unavailable The web application you are attempting to access on this web server is currently ....

Why do I get the error message "Server Application Unavailable The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request."?

By default, ASP.NET runs its worker process (Aspnet_wp.exe) with a weak account (the local machine account, which is named ASPNET) to provide a more secure environment. On a domain controller or on a backup domain controller, all user accounts are domain accounts and are not local machine accounts. Therefore, Aspnet_wp.exe fails to start because it cannot find a local account named "localmachinename\ASPNET". To provide a valid user account on the domain controller, you must specify an explicit account in the section of the Machine.config file, or you must use the SYSTEM account.

Prev Post                                                      All Post                                                           Next Post

Posted in | 0 comments

Not Use Try Catch More

There are Lot's of Try Catch Used by Developer When developing a Site.
developer's inefficiently to Use more Try Catch on the Web site.
Instead Of Try Catch use Using Blog.
By Using Instead of try Catch Objects are directly Dispose.So you Not Need to
Separately Dispose all objects.

What are you Saying ??



Prev Post                                                      All Post                                                           Next Post


Posted in | 1 comments

Submit form when user press enter key on dropdownlist box

Submit form when user press enter key on dropdownlist box


This function will help to submit form when user press enter key at dropdownlist box.


//if enter key pressed at DropDownList

function onEnterpress(e)
{
//define any varible
var KeyPress
//if which property of event object is supported
if(e && e.which)
{
e = e
//character code is contained in NN4's which property
KeyPress = e.which
}
else
{
e = event
KeyPress = e.keyCode
}
//13 is the key code of enter key
if(KeyPress == 13)
{
//frmLogin is the name of form
document.frmLogin.submit()
return false
}
else
{
return true
}
}

After this just add the event in your code file at pageLoad like is:

Your code file may be in VB.NET or C# so you may add even like this.


ddlDropDownList.Attributes.Add("onKeyPress", "javascript:onEnterpress(event);")
Event is the name of event pressed by user at keyBoard Key.
ddlDropDownList is the name of dropdown on which we want to add event.

Prev Post                                                      All Post                                                           Next Post



Posted in | 1 comments

Connection String SQL Server 2005 With Various Data Provider




The following is a list of database connection strings to use with various Sql Server data providers. Cut and paste the connection string to use within your application. For connection strings that require a servername, username and password.



·    ODBC: 
  • Driver={SQL Server}; Server=your Server name; Database=DBname; Uid=’your uid’; Pwd='your password';  
  • OLE DB, OleDbConnection (.NET):
  • Provider=sqloledb; Data Source= your Server name; Initial Catalog= DBname; User ID=’your uid’; Password='your password';
  • SqlConnection (.NET):
  • Data Source=your server name; Initial Catalog=DB name; User ID=’your uid’; Password='your password';

Prev Post                                                      All Post                                                           Next Post

Posted in | 0 comments