ASP.NET and SQL: March 2012

Saturday, March 31, 2012

Useful SQL Functions


NEWID ( )



DECLARE @myid uniqueidentifier
Example

SET @myid =NEWID()

PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid) 



OBJECT_NAME(@@PROCID)



PROCID: Returns the object identifier (ID) of the current
Transact-SQL module. A Transact-SQL module can be a stored procedure,
user-defined function, or trigger. @@PROCID cannot be specified in CLR modules
or the in-process data access provider.

Return Type : int






@@TRANCOUNT

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.




ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for
ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT.

COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by

RAISERROR



 Itis used to return

messages back to applications using the same format as a system error or 

warning message generated by the SQL Server Database Engine.

RAISERROR can return either:




A user-defined error message that has been created using the
sp_addmessage system  stored procedure.
These are messages with a message number greater than 50000 that  can be viewed in the sys.messages catalog
view.

A message string specified in the RAISERROR statement.




RAISERROR can also:

Assign a specific error number, severity, and state.

Request that the error be logged in the Database Engine
error log and the Microsoft Windows application log.

Substitute argument values into the message text, much like
the C language printf_s function.

Both RAISERROR and PRINT can be used to return informational
or warning messages to an application. The message text returned by RAISERROR
can be built using string substitution functionality similar to the printf_s
function of the C standard library, whereas PRINT can only return a character
string or character expression. A RAISERROR severity of 11 to 19 executed in
the TRY block of a TRY…CATCH construct causes control to transfer to the
associated CATCH block. Specify a severity of 10 or lower to return messages
using RAISERROR without invoking a CATCH block. PRINT does not transfer 

control to a CATCH block.

When RAISERROR is used with the msg_id of a user-defined
message in sys.messages, msg_id is returned as the SQL Server error number, or
native error code. When RAISERROR is used with a msg_str instead of a msg_id,
the SQL Server error number and native error number returned is 50000.

When you use RAISERROR to return a user-defined error
message, use a different state number in each RAISERROR that references that
error. This can help in diagnosing the errors when they are raised.

Use RAISERROR to:

Help in troubleshooting Transact-SQL code.

Check the values of data.

Return messages that contain variable text.

Cause execution to jump from a TRY block to the associated
CATCH block.

Return error information from the CATCH block to the calling
batch or application.

Example

RAISERROR ('your error message',16,2);  --Here 16 is the Level and 2 is the 

state 




DB_ID() 

Returns the database identification (ID) number

Example 1 

SELECT DB_ID() AS my_database_id 

Example 2 

SELECT DB_ID(N'AcharyaDatabase') AS my_database_id 

DB_NAME()




Returns the Current database name

SELECT DB_NAME() AS my_db_Name; 

@@SERVERNAME

Returns the name of the local server 

SELECT @@SERVERNAME AS 'Server Name' 

@@SERVICENAME

Returns the Servicename

SELECT @@SERVICENAME AS 'Service Name'

USER

SELECT USER AS 'User Name'

@@SPID

SELECT @@SPID AS 'ID'

SYSTEM_USER

SELECT SYSTEM_USER AS 'Login Name'



 @@TEXTSIZE

SELECT @@TEXTSIZE AS 'Text Size'

@@TIMETICKS

Returns the number of microseconds per tick.

SELECT @@TIMETICKS AS 'Time Ticks'




@@TOTAL_READ,@@TOTAL_WRITE

Returns the number of disk reads, not cache reads, by SQL
Server since SQL Server was last started

SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes'




@@VERSION

SELECT @@VERSION AS 'SQL Server Version'

AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM

Select Sum(salary) from wages

Select AVG(salary) from wages

Select COUNT(salary) from wages

Select MAX(salary) from wages

Select MIN(salary) from wages

Select STDEV(salary) from wages

Select STDEVP(salary) from wages

Select VAR(salary) from wages

Select VARP(salary) from wages

Friday, March 30, 2012

Dynamic menu ASP.NET Working on All Browser(Safari, Chrome, FireFox)

Hello Friends,

Dynamic menu is needed where Admin wishes to distribute work and at the same time he wants each people should do the work that has been assign to him.

For example Admin is a shop owner and he has employee like

1: sales man,

2: storekeeper,

3: dataentry operator

sales man should not interfere in storekeeper's or Dataentry operator's work,

Similarly storekeeper should not interfere in sales man's or Dataentry operator's work,

BUT Dataentry operator CAN interfere with both storekeeper's and sales man's work

One single menu will not work here and authorization can be changed time to time thus we need a dynamic menu.

Step 1: creat login table as given example below

Create a Mastermenu table with all menus as shown below

Create Table Dynamic As Shown below.

CREATE TABLE [dbo].[Dynamic](

[MenuID] [int] NULL,

[Text] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ParentID] [int] NULL,

[Fkadminid] [int] NULL

) ON [PRIMARY]

CREATE a login page and if admin is logging in then go to Access Specify.aspx To provide different Menu Access to different users

Else if other user is logging in then

go to UserHome.aspx with Admin Decided menu

------------------------------------------------------------------------------------------------------------

Dynamic Menu

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>DataBase Driven Menu</title>

.IE8Fix

{

z-index: 1000;

}

</style>

</head>

<body>

<asp:LinkButton ID="LinkButton1" runat="server" style="float:right;"

onclick="LinkButton1_Click">LOG OUT</asp:LinkButton></div>

<div>

<asp:Menu ID="Menu1" DataSourceID="xmlDataSource" runat="server"

BackColor="#FFFBD6" DynamicHorizontalOffset="2" Font-Names="Verdana"

ForeColor="#990000" StaticSubMenuIndent="10px" StaticDisplayLevels="1" Orientation="Horizontal" >

<asp:MenuItemBinding DataMember="MenuItem"

NavigateUrlField="NavigateUrl" TextField="Text" ToolTipField="ToolTip"/>

</DataBindings>

</asp:Menu>

<asp:XmlDataSource ID="xmlDataSource" TransformFile="~/TransformXSLT.xsl" EnableCaching="false"

XPath="MenuItems/MenuItem" runat="server"/>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:AcharyaConnectionString %>"

SelectCommand="SELECT [MenuID], [Text] FROM [DynaMenu]"></asp:SqlDataSource>

<br />

</div>

</form>

</body>

</html>

Dynamicmenu.aspx.cs

public partial class _Default : System.Web.UI.Page

{

DataSet ds;

SqlConnection conn;

string connStr;

string sql;

SqlDataAdapter da;

protected override void OnPreInit(EventArgs e)

{

if (Request.UserAgent != null &&

(Request.UserAgent.IndexOf("AppleWebKit") > 0))

{

this.ClientTarget = "uplevel";

}

base.OnPreInit(e);

}

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

int sss = int.Parse( Session["userid"].ToString());

string sid = Session["userid"].ToString();

//Response.Write(sid);

ds = new DataSet();

connStr = "Data Source=ITPL-16;Initial Catalog=Acharya;Integrated Security=True";

using (conn = new SqlConnection(connStr))

{

sql = "Select MenuID, Text, Description, ParentID from Dynamic where fkadminid='"+sid+"'";

da = new SqlDataAdapter(sql, conn);

da.Fill(ds);

da.Dispose();

conn.Close();

conn.Dispose();

}

ds.DataSetName = "Menus";

ds.Tables[0].TableName = "Menu";

DataRelation relation = new DataRelation("ParentChild",

ds.Tables["Menu"].Columns["MenuID"],

ds.Tables["Menu"].Columns["ParentID"],

true);

relation.Nested = true;

ds.Relations.Add(relation);

xmlDataSource.Data = ds.GetXml();

ds.Clear();

if (Request.Params["Sel"] != null)

{

string ss = Request.Params["Sel"];

Response.Redirect(Request.Params["Sel"]);

Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " + Request.Params["Sel"]));

}

Menu1.DataBind();

}

//Response.Cache.SetCacheability(HttpCacheability.NoCache);

}

protected void LinkButton1_Click(object sender, EventArgs e)

{

Session.Clear();

Session.RemoveAll();

Session.Abandon();

Session.Remove("userid");

da = new SqlDataAdapter();

da.Dispose();

xmlDataSource.Data = null;

Response.Redirect("Login.aspx");

}

Create a TransformXSLT.xsl file as shown below

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="yes" encoding="utf-8"/>

<!-- Find the root node called Menus

and call MenuListing for its children -->

<xsl:template match="/Menus">

<xsl:call-template name="MenuListing" />

</MenuItems>

</xsl:template>

<xsl:template name="MenuListing">

<xsl:apply-templates select="Menu" />

</xsl:template>

<xsl:template match="Menu">

<xsl:attribute name="Text">

<xsl:value-of select="Text"/>

</xsl:attribute>

<xsl:attribute name="ToolTip">

<xsl:value-of select="Text"/>

</xsl:attribute>

<xsl:attribute name="NavigateUrl">

<xsl:text>?Sel=</xsl:text>

<xsl:value-of select="Description"/>

</xsl:attribute>

<xsl:if test="count(Menu) > 0">

<xsl:call-template name="MenuListing" />

</xsl:if>

</MenuItem>

</xsl:template>

</xsl:stylesheet>

--------------------------------------------N Joy------------------------------------------------------

Tuesday, March 27, 2012

Procedure 1:
-------------Send_mail.aspx.cs---------------------------------------------------
protected void Button1_Click(object sender, EventArgs e)
{
try
{
using (MailMessage message = new MailMessage())
{

message.To.Add(new MailAddress(YourEmail.Text.ToString()));
message.To.Add(new MailAddress(YourEmail.Text.ToString()));
message.From = new MailAddress("romeoacharya@gmail.com");
message.Subject = "Message via My Site from " + YourName.Text.ToString();
message.Body = Comments.Text.ToString();

SmtpClient client = new SmtpClient();
client.Host = "Smtp.gmail.com";
client.Credentials = new System.Net.NetworkCredential("USERNAME@gmail.com", " PASSWORD ");
client.EnableSsl = true;
client.Send(message);
Comments.Text = "sent";
}
}
catch (Exception dd)
{
Comments.Text = dd.ToString();
}
}
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Procedure 2:
-----------------email.cs class file---------------------------------------------------------

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Net;
using System.Net.Mail;

/// <summary>
/// Summary description for Email
/// This is the class used to send the Email alert to a given/ selected registered user
/// Use two methods & smmtp server is smtp.gmail.com
/// Can use in any application for any purpose
/// Sends with our Authentication
/// Completely secured. Use SSL V3 & Https & a Encoded message
/// </summary>
public class Email
{
public Email()
{
//
// TODO: Add constructor logic here
//
}

/// <summary>
/// This is the primery method try from the program
/// Latest & easiest method of sending an Email using ASP.NET
/// </summary>
/// <param name="to"></param>
/// <param name="from"></param>
/// <param name="subject"></param>
/// <param name="body"></param>
public static void sendMail(string to, string from, string subject, string body)
{
///Smtp config
SmtpClient client = new SmtpClient("smtp.gmail.com", 465);
// Edit password and username
client.Credentials = new NetworkCredential("USERNAME@gmail.com", "PASSWORD");
client.EnableSsl = true;

///mail details
MailMessage msg = new MailMessage();
try
{
msg.From = new MailAddress(from);
msg.To.Add(to);
// msg.SubjectEncoding = System.Text.Encoding.UTF8;
msg.Subject = subject;
//msg.CC.Add();
msg.IsBodyHtml = true;
msg.BodyEncoding = System.Text.Encoding.UTF8;
msg.Body = body;
msg.Priority = MailPriority.Normal;

// Enable one of the following method.

client.Send(msg);

// or use the following alternative after enabling send mail asynchronous option in the global.asax

//object userState = msg;
//client.SendAsync(msg, userState);

}
catch (Exception exp)
{
///This runs the backup plan
SendMailAlt(to, from, subject, body);
}

}

private static void SendMailAlt(string to, string from, string subject, string body)
{
System.Web.Mail.MailMessage Mail = new System.Web.Mail.MailMessage();
Mail.Fields["http://schemas.microsoft.com/cdo/configuration/smtpserver"] = ("smtp.gmail.com");
Mail.Fields["http://schemas.microsoft.com/cdo/configuration/sendusing"] = 2;

Mail.Fields["http://schemas.microsoft.com/cdo/configuration/smtpserverport"] = "465";

Mail.Fields["http://schemas.microsoft.com/cdo/configuration/smtpusessl"] = "true";

Mail.Fields["http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"] = 1;
// Edit username & password
Mail.Fields["http://schemas.microsoft.com/cdo/configuration/sendusername"] = "USERNAME@gmail.com";
Mail.Fields["http://schemas.microsoft.com/cdo/configuration/sendpassword"] = "PASSWORD";

Mail.To = to;
Mail.From = from;
Mail.Subject = subject;
Mail.Body = body;
Mail.BodyFormat = System.Web.Mail.MailFormat.Html;

System.Web.Mail.SmtpMail.SmtpServer = "smtp.gmail.com";
System.Web.Mail.SmtpMail.Send(Mail);
}
}
_____________________________________________________________________________----

Send Mail from SQL Query

Send Mail from SQL Query
We can send mail by different ways, but they are mostly slow in execution. But if we make a table in database with mail attributes like sender, receiver, subject, body etc and run a procedure to send one by one from the table then it wont harm our project execution time. n joy

--------Enable DatabaseMail Option -------------

use databasename
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
--------Database Mail option Enabled --------------

--------set gmail account for sending an email -----

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'admin',
@description = 'Mail account for Database Mail',
@email_address = 'romeoacharya@gmail.com',
@display_name = 'ProjectName',
@username='Email',   --give your valid gmail id
@password='PASSWORD',   --give your valid gmail password
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@enable_ssl = 1

------account configured created------

--------------Create Profile --------------
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ' ProfileName', --remeber the ProfileName we will use it every where
@description = 'Profile used for database mail'
--------------Profile Created  --------------

------add database mail account to mail profile -----------
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ' ProfileName ',
@account_name = 'admin',
@sequence_number = 1
------add database mail account to mail profile -----------

------ Now grant the Database Mail profile access to the msdb public ...
------ ...database role and to make the profile the default Database Mail profile

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

----------------------------------------------------------------------------------------

--To send an email use

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'romeoacharya@gmail.com',@body= "This is <b>HTML</b> body",
@subject ='Email from database ASPNET website', @body_format = 'HTML' ,
@profile_name ='  ProfileName '

select * from msdb.dbo.sysmail_event_log
select * from msdb.dbo.sysmail_mailItems

--------------------------Procedure Example----------------------------------------------

Create proc [dbo].[sendmail_after_user_registration]
(
@profilename varchar(100),
@accountname varchar(100),
@recipentname varchar(200),
@sendername varchar(200),
@bodycontent nvarchar(max),
@sub varchar(max)
)
as
begin
-- exec msdb.dbo.sp_configure 'show advanced options', 1 --The left side green back code
-- reconfigure --Should run only once to enable
-- exec msdb.dbo.sp_configure 'Database Mail XPs', 1 --Database mail options
-- reconfigure
IF exists
(SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p
ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = @profilename AND a.name = @accountname)
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients=@recipentname,
@subject = @sub,
@body= @bodycontent,
@body_format = 'HTML'
end
else
begin
-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @accountname,
@description = 'Mail account for administrative e-mail.',
@email_address = @sendername,
@replyto_address = @sendername,
@display_name = 'ProjectName',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username = @sendername,
@password = 'PASSWORD', --give your valid gmail password
@enable_ssl = 1
-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @profilename,
@description = 'Profile used for administrative mail.'

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profilename,
@account_name = @accountname,
@sequence_number =1

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @profilename,
@principal_name = 'public',
@is_default = 1
--send mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients=@recipentname,
@subject = @sub,
@body= @bodycontent,
@body_format = 'HTML'
end
end

------------------------------------ N-joy -------------------------------------

Saturday, March 24, 2012

Sql date time

To show current date in Sql
-------------------------------
SELECT GETDATE()
-------------------------------
To Show only the date part
---------------------------------------------------------------------------------------------------------
Select CAST(DATEDIFF(Day, 0, GETDATE()) AS DATETIME)
---------------------------------------------------------------------------------------------------------
OR
--------------------------------------------------------------------------------------------------

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

--------------------------------------------------------------------------------------------------

Works on All Sql Versions

To Show Difference between dates
---------------------------------------------------------------------------------------------
DATEDIFF ( datepart , startdate , enddate )
---------------------------------------------------------------------------------------------

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned

For different Format : http://msdn.microsoft.com/en-us/library/ms186724.aspx

SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.

CAST function Syntax: CAST(expression as data_type)

Let's convert current date time to varchar

select cast(getdate() as varchar)

CONVERT function is used to change or convert the DateTime formats.By using convert function you can get only Date part or only Time part from the datetime.

CONVERT Function Syntax: CONVERT(data_type,expression,date Format style)

Let's take Sql Server DateTtime styles example:

Format	Query
USA mm/dd/yy	select convert(varchar, getdate(), 1)
ANSI yy.mm.dd	select convert(varchar, getdate(), 2)
British/French dd/mm/yy	select convert(varchar, getdate(), 3)
German dd.mm.yy	select convert(varchar, getdate(), 4)
Italian dd-mm-yy	select convert(varchar, getdate(), 5)
dd mon yy	select convert(varchar, getdate(), 6)
Mon dd, yy	select convert(varchar, getdate(), 7)
USA mm-dd-yy	select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd	select convert(varchar, getdate(), 11)
ISO yymmdd	select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM)	select convert(varchar, getdate(), 100)
mm/dd/yyyy	select convert(varchar, getdate(), 101)
yyyy.mm.dd	select convert(varchar, getdate(), 102)
dd/mm/yyyy	select convert(varchar, getdate(), 103)
dd.mm.yyyy	select convert(varchar, getdate(), 104)
dd-mm-yyyy	select convert(varchar, getdate(), 105)
dd mon yyyy	select convert(varchar, getdate(), 106)
Mon dd, yyyy	select convert(varchar, getdate(), 107)
hh:mm:ss	select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)	select convert(varchar, getdate(), 109)
mm-dd-yyyy	select convert(varchar, getdate(), 110)
yyyy/mm/dd	select convert(varchar, getdate(), 111)
yyyymmdd	select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)	select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h)	select convert(varchar, getdate(), 114)

Saturday, March 31, 2012

Useful SQL Functions

Friday, March 30, 2012

Dynamic menu ASP.NET Working on All Browser(Safari, Chrome, FireFox)

Tuesday, March 27, 2012

Send mail from Dot net Code

Send Mail from SQL Query

Saturday, March 24, 2012

Sql date time