Total Pageviews

Thursday, June 25, 2009

How to retrieve the identity value when inserting a record into a Microsoft SQL Server table

A common requirement when inserting a record into a table that contains an identity column is to return the identity value of the newly inserted record. A common mistake is to use @@IDENTITY or IDENT_CURRENT, both of which could return an unexpected value under some circumstances. This document describes a technique to use SCOPE_IDENTITY which does not suffer the drawbacks of the other two methods.

Why @@IDENTITY and IDENT_CURRENT should not be used

Using @@IDENTITY after an insert will return the last-inserted identity value. However, this might be an unexpected value if, for example, the inserted record fires triggers to perform additional inserts. In this case, calling @@IDENTITY immediately after the statement will return the last identity value generated by the triggers.

Using IDENT_CURRENT after an insert will return the last-inserted identity value for a specified table. However, this might be an unexpected value if another insert on the table is performed between the time of the initial insert and the calling of IDENT_CURRENT.

How and why to use SCOPE_IDENTITY

Using SCOPE_IDENTITY avoids the drawbacks of using @@IDENTITY and IDENT_CURRENT.Note however that SCOPE_IDENTITY is only available with Microsoft SQL Server 2000 or later.The technique for using SCOPE_IDENTITY is to call it immediately after the INSERT statement within a stored procedure. The following example shows a stored procedure called InsertCases that uses this technique to return the identity value for the CaseID identity column of the inserted record. This example has two columns, in addition to the CaseID identity column, called CaseName and CaseDescription. Notice that the @CaseName and @CaseDescription are input parameters but the @CaseID parameter is an output parameter used to return the identity value on exit from the stored procedure.

CREATE PROCEDURE [dbo].[InsertCases] @CaseID int output, @CaseName nvarchar(255), @CaseDescription ntext AS SET NOCOUNT ON INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription]) VALUES (@CaseName,@CaseDescription) SET @CaseID=SCOPE_IDENTITY() SET NOCOUNT OFF

The following example shows how to call the InsertCases stored procedure using the Microsoft .NET Framework. Notice that the @CaseID parameter is set as an output parameter by setting its Direction property to arameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.

C#

Command.Parameters.Add(new System.Data.SqlClient.SqlParameter(”@CaseID”,System.Data.SqlDbType.Int));

Command.ExecuteNonQuery();

CaseID = System.Convert.ToInt32( Command.Parameters["@CaseID"].Value);

Conclusion

By using SCOPE_IDENTITY within a stored procedure, the identity value of the most recently inserted record can always be correctly obtained.

Custom Paging in Datalist

Introduction

In this article i will explain a method for providing custom paging for datalist or repeater.

As you know the datalist is a very powerful control with one drawback that it does not have built-in paging capability, a feature the DataGrid offers. to provide paging to datalist or repeater we can either use “PagedDataSource” class, found in the System.Web.UI.WebControls namespace for auto paging like the datagrid or implement custom paging functionality.

So here is the scenario, i have an image gallery with 8 images per page. i need to provide paging so the user can navigate and view all images. The first step is to create the datalist and paging links.

Datalist <asp:DataList runat=”server” id=”dlGallery” RepeatColumns=”4″ RepeatDirection=”Horizontal”> <ItemTemplate> <table border=”0″ cellpadding=”0″ cellspacing=”0″> <tr> <td> <img src=”<%#DataBinder.Eval(Container.DataItem,”Image_URL”)%>” width=”90″ Height=”90″> </td> </tr> </table> </ItemTemplate> </asp:DataList> //Next/Prev Links. <table border=”0″ width=”410″> <tr> <td align=”left”><asp:LinkButton ID=”lbtnPrev” Runat=”server”>Prev</asp:LinkButton></td> <td align=”right”><asp:LinkButton ID=”lbtnNext” Runat=”server”>Next</asp:LinkButton></td> </tr> </table> //The Code Behind Create a public function that will return only the neccessary rows (After paging). For that we need five static variables. Collapse private int imgID; private string imgTitle; private string imgURL; private static int pageSize = 8; //(This one will hold the no of records return //i mean “no. of records per page”). private static int pageIndex = 0; //(This one is for checking the current page). public DataSet GetAllImagesCustom(int pageIndex, out int outPageIndex) { try { int count = 0; DataSet ds = new DataSet(); ds = //retrieve the data from the database. //for paging int page = 0; //checking the whether the pageIndex value is not Last. //And if it is then assigning the default //values for pageIndex and page variables. if(((pageIndex-1) <= (ds.Tables[0].Rows.Count/pageSize)) && (pageIndex-1) >= 0) { //If the pageIndex is >=first and = //eg. if pageIndex = 2 then value of ‘page’ = 8. //So in the loop it will add rows to the table //from the 8 th row. page = pageSize * (pageIndex-1); } else { //Assigning default values. page = 0; pageIndex = 1; } //creating a data table for adding the required rows or u //can clone the existing table. DataTable dtImg = new DataTable(“Images”); DataColumn newCol = new DataColumn(“Image_ID”,Type.GetType(“System.Int32″)); dtImg.Columns.Add(newCol);//For storing image id. newCol = new DataColumn(“Image_Title”,Type.GetType(“System.String”)); dtImg.Columns.Add(newCol);//For storing image Title. newCol = new DataColumn(“Image_URL”,Type.GetType(“System.String”)); dtImg.Columns.Add(newCol);//For storing image URL. //adding the required rows to the datatable dtImg. foreach(DataRow nRow in ds.Tables[0].Rows) { //if the page=8 and pageIndex =2 then //rows between 8 to 16(if exists) will be added to the new table. if(count >= page && count < (pageSize * pageIndex)) { //Adding rows to the datatable ‘dtImg’. dtImg.Rows.Add(nRow.ItemArray); } count++; } outPageIndex = pageIndex; return ds; } } catch(Exception ex) { throw ex; } } public void BindList() { ….. DataSet ds = new DataSet(); ds = GetAllImagesCustom(Convert.ToInt32(txtPageIndex.Text), out outPageIndex); dlGallery.DataSource = ds; dlGallery.DataBind(); //Assigning the new pageIndex value returned from the //function to the Hidden textbox. txtPageIndex.Text = Convert.ToString(outPageIndex); } Now we have a datalist with 8 images per page. But still we hav’nt done the navigation part. Thats simple as u can see from the above function there isn’t much logic needed. we only need to plus or minus the pageindex value and call the BindList function. private void lbtnPrev_Click(object sender, System.EventArgs e) { //Actual pageIndex -1 txtPageIndex.Text = Convert.ToString(Convert.ToInt32(txtPageIndex.Text) – 1); BindList(); } private void lbtnNext_Click(object sender, System.EventArgs e) { //Actual pageIndex +1 txtPageIndex.Text = Convert.ToString(Convert.ToInt32(txtPageIndex.Text) + 1); BindList(); }

AsyncPostBackTrigger vs PostBackTrigger

In the <triggers> template in an update panel, there are the options of an AsyncPostBackTrigger or a PostBackTrigger. By default, controls outside of an update panel will trigger a normal synchronous post back. The AsyncPostBackTrigger “wires” up these controls to trigger an asynchronous post back. Conversely, controls declared inside an update panel will trigger an asynchronous call by default. The PostBackTrigger short circuits this, and forces the control to do a synchronous post back. Utilizing a simple “time” example: <form id=”form1″ runat=”server”> <asp:ScriptManager ID=”ScriptManager1″ runat=”server” /> <div> Page Generated @ <asp:Label runat=”server” ID=”uiPageTime” /> <p /> <asp:UpdatePanel runat=”server” ID=”update” UpdateMode=”Conditional”> <ContentTemplate> <asp:Label runat=”server” ID=”uiTime” /> <asp:Button runat=”server” ID=”uiInternalButton” Text=”Click” /> </ContentTemplate> <Triggers> <asp:AsyncPostBackTrigger ControlID=”uiAsynch” EventName=”click” /> <asp:PostBackTrigger ControlID=”uiInternalButton” /> </Triggers> </asp:UpdatePanel> <asp:Button runat=”server” ID=”uiPostback” Text=”Click” /> <asp:Button runat=”server” ID=”uiAsynch” Text=”Asynch” /> </div> </form> And the code behind file. public partial class _Default : System.Web.UI.Page { protected void Page_Init(object sender, EventArgs e) { uiAsynch.Click += uiAsynch_Click; uiPostback.Click += uiPostback_Click; uiInternalButton.Click += uiInternalButton_Click; } protected void Page_Load(object sender, EventArgs e) { uiPageTime.Text = DateTime.Now.ToLongTimeString(); if (!IsPostBack) { uiTime.Text = DateTime.Now.ToLongTimeString(); } } private void uiInternalButton_Click(object sender, EventArgs e) { uiTime.Text = “Internal @ ” + DateTime.Now.ToLongTimeString(); } private void uiPostback_Click(object sender, EventArgs e) { uiTime.Text = “Postback click @ ” + DateTime.Now.ToLongTimeString(); update.Update(); } private void uiAsynch_Click(object sender, EventArgs e) { uiTime.Text = “Asych click @ ” + DateTime.Now.ToLongTimeString(); update.Update(); } }

Selecting All CheckBoxes in GridView

<script type=”text/javascript”> var TotalChkBx; var Counter; window.onload = function() { //Get total no. of CheckBoxes in side the GridView. TotalChkBx = parseInt(’<%= this.gvCheckboxes.Rows.Count %>’); //Get total no. of checked CheckBoxes in side the GridView. Counter = 0; } function HeaderClick(CheckBox) { //Get target base & child control. var TargetBaseControl = document.getElementById(’<%= this.gvCheckboxes.ClientID %>’); var TargetChildControl = “chkBxSelect”; //Get all the control of the type INPUT in the base control. var Inputs = TargetBaseControl.getElementsByTagName(”input”); //Checked/Unchecked all the checkBoxes in side the GridView. for(var n = 0; n < Inputs.length; ++n) if(Inputs[n].type == ‘checkbox’ && Inputs[n].id.indexOf(TargetChildControl,0) >= 0) Inputs[n].checked = CheckBox.checked; //Reset Counter Counter = CheckBox.checked ? TotalChkBx : 0; } function ChildClick(CheckBox, HCheckBox) { //get target base & child control. var HeaderCheckBox = document.getElementById(HCheckBox); //Modifiy Counter; if(CheckBox.checked && Counter < TotalChkBx) Counter++; else if(Counter > 0) Counter–; //Change state of the header CheckBox. if(Counter < TotalChkBx) HeaderCheckBox.checked = false; else if(Counter == TotalChkBx) HeaderCheckBox.checked = true; } </script> <asp:GridView ID=”gvCheckboxes” runat=”server” AutoGenerateColumns=”False” OnRowCreated=”gvCheckboxes_RowCreated”> <Columns> <asp:BoundField HeaderText=”S.N.” DataField=”sno”> <HeaderStyle HorizontalAlign=”Center” VerticalAlign=”Middle” Width=”50px” /> <ItemStyle HorizontalAlign=”Center” VerticalAlign=”Middle” Width=”50px” /> </asp:BoundField> <asp:TemplateField HeaderText=”Select”> <ItemTemplate> <asp:CheckBox ID=”chkBxSelect” runat=”server” /> </ItemTemplate> <HeaderStyle HorizontalAlign=”Center” VerticalAlign=”Middle” Width=”50px” /> <ItemStyle HorizontalAlign=”Center” VerticalAlign=”Middle” Width=”50px” /> <HeaderTemplate> <asp:CheckBox ID=”chkBxHeader” onclick=”javascript:HeaderClick(this);” runat=”server” /> </HeaderTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:CheckBox ID=”chkBx” runat=”server” /> </ItemTemplate> <ItemStyle HorizontalAlign=”Center” VerticalAlign=”Middle” Width=”50px” /> </asp:TemplateField> </Columns> <RowStyle BackColor=”Moccasin” /> <AlternatingRowStyle BackColor=”NavajoWhite” /> <HeaderStyle BackColor=”DarkOrange” Font-Bold=”True” ForeColor=”White” /> </asp:GridView> protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindGridView(); } } protected void BindGridView() { gvCheckboxes.DataSource = GetDataSource(); gvCheckboxes.DataBind(); } protected DataTable GetDataSource() { DataTable dTable = new DataTable(); DataRow dRow = null; Random rnd = new Random(); dTable.Columns.Add(”sno”); for (int n = 0; n < 10; ++n) { dRow = dTable.NewRow(); dRow["sno"] = n + “.”; dTable.Rows.Add(dRow); dTable.AcceptChanges(); } return dTable; } protected void gvCheckboxes_RowCreated(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)) { CheckBox chkBxSelect = (CheckBox)e.Row.Cells[1].FindControl(”chkBxSelect”); CheckBox chkBxHeader = (CheckBox)this.gvCheckboxes.HeaderRow.FindControl(”chkBxHeader”); chkBxSelect.Attributes["onclick"] = string.Format(”javascript:ChildClick(this,’{0}’);”, chkBxHeader.ClientID); } }

Displaying Alert Message Boxes from your .aspx page

Step 1 : Create a General class and place the following Code in it

public static void CreateMessageAlert(System.Web.UI.Page senderPage, string alertMsg, string alertKey) { ScriptManager.RegisterStartupScript(senderPage, senderPage.GetType(), alertKey, “alert(’” + alertMsg + “‘);”,true); }

Step 2 : Call the created method from where ever you wish to send the alert from

string alertmessage = “Thank You for visiting MySite.Com”;

YourClassName.CreateMessageAlert(this,alertmessage,”alertKey”);

Where YourClassName is nothing but the name of the class file where your CreateMessageAlert method resides and alertmessage is where you assign the string you wish to display.

Friday, June 19, 2009

Send Email with Inline Images & Attachments

When sending an email from an ASP.NET 2.0 page you will, typically: 1. Create a MailMessage object 2. Assign its properties 3. Create an instance of the SmtpClient class 4. Specify details about the SMTP server to use (if they’re not already specified within Web.config) 5. Send the MailMessage via the SmtpClient object’s Send method ASP.NET makes it easy to utilise e-mail in an application with the System.Web.Mail namespace. Let’s take a closer look at putting this namespace to work in your applications. MailAttachment class The basic approach is the creation of a MailMessage object followed by sending it on its way via a SmtpMail object. The MailMessage class contains numerous methods and properties for working with an e-mail message. Properties such as From, Subject, and Body provide everything you need to create an e-mail message, but a SmtpMail object is still necessary for sending it on its way. SmtpMail class The SmtpMail class includes the SmtpServer property that gets or sets the name of the SMTP relay mail server to use to send messages, and the Send method actually sends the message. The Send method is overloaded. It allows a message to send using two approaches: 1. A MailMessage object is passed to the SmtpServer object. Four string objects may be passed to the SmtpServer object with the first being the From field followed by the Recipient, Subject, and the message’s Body. 2. You’ll use the MailAttachment and SmtpMail classes together to create the necessary messages in your application, but make sure the Web server is properly configured to send a message via SMTP. Since IIS (Internet Information Services) is the most popular platform for ASP.NET applications, go ahead and use both the IIS and SMTP services to send messages from your application.

Using SMTP with IIS

You can set up both IIS and SMTP services via the Windows control panel. The SMTP service’s role is to accept and deliver the messages using the server’s configuration. It may deliver the messages directly, or utilise a smart host to deliver the message instead. When a smart host is enlisted, all messages are forwarded to it for delivery.

A little more information is appropriate for debugging. The SMTP service uses a directory structure to contain messages prior to delivery with the default directory being C:\Inetpub\mailroot. It contains numerous subdirectories including Queue, Drop, and Badmail. If you’re unable to configure your instance of the SMTP Service for delivery, you can find the message in an EML file in the Queue subdirectory. The Badmail directory contains messages that that couldn’t be delivered. Now, let’s take a look at sending mail messages from your code.

Sending e-mail messages

To compose an e-mail message in your code, you need to start by creating an instance of the MailMessage class, as shown in the following C# snippet:

MailMessage msg = new MailMessage();

Be sure to include the System.Web.Mail namespace in your code:

using System.Web.Mail;

Once the object is instantiated, the various properties of the MailMessage class are used per your application. The following lines set the recipient, sender, and subject of the message:

msg.To = “test@test.com”; msg.From = “me@test.com”; msg.Subject = “Test Message”;

The next step is setting our mail server via the SmtpServer object’s SmtpServer property:

SmtpMail.SmtpServer = “smtp server name or address”;

The final step is sending the message by passing our MailMessage object to the SmtpMail object’s Send method:

SmtpMail.Send(msg);

Here I am Providing you the Complete Example of how to send Email with Inline Image / Multiple Attachments …etc…

—————————————- Button Event Code —————————————-

protected void Button1_Click(object sender, EventArgs e) { string from = “”, to = “”, cc = “”, subject = “”, body = “”, attachedfiles = “”;

from = “yogesh@gmail.com”; to = “yrb.yogi@gmail.com;bhadauriya.yogesh@gmail.com”; // multiple entry are separated bt ‘;’ cc = “yrb.yogigmail.com”; subject = “Testing Email Module”;

//Read Mail Template StreamReader sr; StringBuilder sb = new StringBuilder(); string BodyOfMail = “”; sr = File.OpenText(Server.MapPath(”~/MailTemplate.txt”)); sb.Append(sr.ReadToEnd()); body = sb.ToString();

//attachments attachedfiles = “E:\\Images\\1.jpg;E:\\Images\\2.jpg;”; emailClass.SendEmail(from, to, cc, subject, body, attachedfiles); }

—————————————- MailTemplate.txt —————————————-

<title>Untitled Document</title> </head> <body> <form id=”form1″ name=”form1″ method=”post” action=”"> <table width=”100%” border=”0″ cellspacing=”10″ cellpadding=”10″> <tr> <td bgcolor=”#FFFFFF”> <br /> <table width=”550″ border=”0″ cellpadding=”2″ cellspacing=”0″ bordercolor=”#FFFFFF” id=”Comment Table2″> <tr> <td > Hello Friend Name,<br /> <br /> Your friend has been send you this offer. <br /> </td> </tr> <tr> <td align=”right”> <table width=”530″ border=”0″ cellpadding=”0″ cellspacing=”0″> <tr> <td > <img src=”{images/logo1.jpg” /> </td> <td > <img src=”{images/logo2.jpg” /> </td> </tr> </table> </td> </tr> </table> </td> </tr> </table> </form> </body> </html>

—————————————- emailClass Class —————————————-

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

///

/// Summary description for emailClass /// public class emailClass { public emailClass() { // // TODO: Add constructor logic here // } public static void SendEmail(string from, string to, string cc, string subject, string body, string attachedFiles) { try { MailMessage mailMsg = new MailMessage(); SmtpClient mailObj = new SmtpClient(”server”, 25);//”192.168.0.17″ – SmtpClient

//to authenticate we set the username and password properites on the SmtpClient mailObj.Credentials = new NetworkCredential(”yogesh@gmail.com”, “password”);

mailMsg.From = new MailAddress(from);

string[] temp = to.Split(’;'); for (int i = 0; i <> 1) mailMsg.To.Add(temp[i].ToString()); }

temp = cc.Split(’;'); for (int i = 0; i <> 1) mailMsg.CC.Add(temp[i].ToString()); } temp = attachedFiles.Split(’;'); for (int i = 0; i <> 1) mailMsg.Attachments.Add(new Attachment(temp[i].ToString())); }

mailMsg.Subject = subject; mailMsg.Body = body; mailMsg.IsBodyHtml = true; int count = 0; string oldChar = ExtractImages(body, ref count); Random RGen = new Random(); while (oldChar != “”) { string imgPath = oldChar; int startIndex = imgPath.ToLower().IndexOf(”images/”); if (startIndex > 0) { imgPath = imgPath.Substring(startIndex); imgPath = imgPath.Replace(”/”, “\\”); System.Net.Mail.Attachment A = new Attachment(HttpContext.Current.Request.PhysicalApplicationPath + “\\” + imgPath); A.ContentId = RGen.Next(100000, 9999999).ToString(); body = body.Replace(oldChar, “cid:” + A.ContentId); mailMsg.Attachments.Add(A); oldChar = ExtractImages(body, ref count); } else { oldChar = ExtractImages(body, ref count); } } mailMsg.Body = body; mailObj.Send(mailMsg); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write(ex.Message); } }

private static string ExtractImages(string body, ref int count) { int startIndex = body.ToLower().IndexOf(”= 0) { endIndex = body.IndexOf(”\”", startIndex + 10); } else { return “”; } startIndex = startIndex + 10; string imgurl = body.Substring(startIndex, (endIndex – (startIndex))); count = startIndex; return imgurl; } }

Thursday, June 4, 2009

Split Values in My Sql with using store procedure

Hello, here i m going to describe the split value function /Store procedure in mysql....

suppose i want to pass mulitple id as '12,113,14,15,10' than how can i separate.... the solution is below....use below store procedure to separate each id's

DELIMITER $$  
DROP PROCEDURE IF EXISTS `ecommerce`.`SplitValue`$$  
CREATE  PROCEDURE `SplitValue`
(    
    input VARCHAR(8000),    
    delim VARCHAR(10),    
    Ids VARCHAR(36) 
) 
BEGIN 
DECLARE foundPos TINYINT UNSIGNED; 
DECLARE tmpTxt TEXT; 
DECLARE delimLen TINYINT UNSIGNED; 
DECLARE element TEXT; 
DROP TEMPORARY TABLE IF EXISTS tmpValues;  
CREATE TEMPORARY TABLE tmpValues 
(    
    valuess DECIMAL(18,0) NOT NULL DEFAULT 0,    
    Id VARCHAR(36) 
) 
ENGINE = MEMORY; 

SET delimLen = LENGTH(delim); 
SET tmpTxt = input; 
SET foundPos = INSTR(tmpTxt,delim); 

WHILE foundPos <> 0 
DO    
    SET element = SUBSTRING(tmpTxt, 1, foundPos-1);    
    SET tmpTxt = REPLACE(tmpTxt, CONCAT(element,delim), '');    
    SET element=CAST(element AS DECIMAL(18,0));    
    INSERT INTO tmpValues (valuess,Id) VALUES ( element,Ids);    
    SET foundPos = INSTR(tmpTxt,delim);   
END WHILE; 
IF tmpTxt <> '' 
THEN    
    SET tmpTxt=CAST(tmpTxt AS DECIMAL(18,0));    
    INSERT INTO tmpValues (valuess,Id)    
    VALUES (tmpTxt,Ids); 
END IF;  
SELECT Id,valuess FROM tmpValues;  
END$$  DELIMITER ;   
   

suppose i m calling this store procedure as

CALL SplitValue('11,12,13,14,15') 

-- then the oputput of this 
-- id VALUE 
-- 1  11 
-- 2  12 
-- 3  13 
-- 4  14 
-- 5  15

Blog Archive

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...