Asp.net and C#.net Tips: 2011

Tuesday, October 11, 2011

COALESCE Function In Sql Server

During Development we have use more time Colesce method If someone have home address or office address suppose if you dispaly available first record means you can use coalesce method or concatinate columns we use this coding

I have Below Table :

Insert Some Data in it :

Now the basic use of Colesce:
You can see the Id 2 hasnot OfficePhoneNo and id 5 has only MobileNo.
Colesce function can display available first record from one of the column.

It's Query Like :

select name ,coalesce(OfficePhoneNo,HomePhoneNo,MobileNo)
PhoneNo from EmpDetails

and Result will be :

Now I can show you another example of it.

We can also use coalesce function for Combining all column into single column.

CREATE TABLE Emp(FName VARCHAR(25))

INSERT INTO Emp VALUES('PalPatel')
INSERT INTO Emp VALUES('DishaAgola')
INSERT INTO Emp VALUES('Bansi')
INSERT INTO Emp VALUES('Nirav')


DECLARE @nam NVARCHAR(1024)

SELECT @nam=COALESCE(@nam+',', '')+ FName FROM Emp

SELECT Emp= @nam

Output :

PalPatel,DishaAgola,Bansi,Nirav

Thats it .....

Friday, September 16, 2011

Dynamically Load Webuser Control with Properties.

Create a new class to act as the base control:

public class MyBaseControl : System.Web.UI.UserControl
{
    public string MyProperty 
    {
        get { return ViewState["MyProp"] as string; }
        set { ViewState["MyProp"] = value; }
    }
}

Then update your user controls to inherit from your base class instead of UserControl:

public partial class SampleControl2 : MyBaseControl
{
....
Then, in the place where you load the controls, change this:

UserControl uc = (UserControl)LoadControl(controlPath);
PlaceHolder1.Controls.Add(uc);

To:

MyBaseControl uc = (MyBaseControl)LoadControl(controlPath);
uc.MyProperty = "foo";
PlaceHolder1.Controls.Add(uc);

Any suggestions will be appreciated greatly. !!.

Get Data from Excel To Sql Server Using Asp.net

Here I am going to show you the step by step process to import data from Excel to SQL Server. To do so follow the below steps :

Step:1 Create Sample Table.

 GO  
 CREATE TABLE [dbo].[Product_Master](  
   [Product_Code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  
   [Product_Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  
   [Price] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
 ) ON [PRIMARY]  
 GO

Step:2 Create New Web site with new webpage named as Default.aspx (Following is the code contained in the Default.aspx page).

1:  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
2:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
3:  <html xmlns="http://www.w3.org/1999/xhtml">  
4:  <head runat="server">  
5:    <title>Import Excel Data To SQL Server</title>  
6:  </head>  
7:  <body>  
8:    <form id="form1" runat="server">  
9:    <div>  
10:      <asp:Label ID="lblHeading" runat="server" Text="Select File To Upload : "></asp:Label>  
11:      <asp:FileUpload ID="FileUpload1" runat="server" />  
12:      <br />  
13:      <br />  
14:      <asp:Button ID="btnUpload" runat="server" Text="Upload"   
15:        onclick="btnUpload_Click" />     
16:      <asp:Button ID="btnSaveToDB" runat="server" Text="Save To DB"   
17:        onclick="btnSaveToDB_Click" />  
18:        <br /><br />  
19:      <asp:Label ID="lblerror" runat="server" ForeColor ="Red" Text=""></asp:Label>  
20:    </div>  
21:    </form>  
22:  </body>  
23:  </html>

Step:3 Here is the C# code of the Default.aspx.cs file:

1:  using System;  
2:  using System.Configuration;  
3:  using System.Data;  
4:  using System.Linq;  
5:  using System.Web;  
6:  using System.Web.Security;  
7:  using System.Web.UI;  
8:  using System.Web.UI.HtmlControls;  
9:  using System.Web.UI.WebControls;  
10:  using System.Web.UI.WebControls.WebParts;  
11:  using System.Xml.Linq;  
12:  using System.IO;  
13:  public partial class _Default : System.Web.UI.Page   
14:  {  
15:    protected void Page_Load(object sender, EventArgs e)  
16:    {  
17:    }  
18:    protected void btnUpload_Click(object sender, EventArgs e)  
19:    {  
20:      if (FileUpload1.HasFile)  
21:      {  
22:        var uploadFile = new UploadFile(FileUpload1.FileName);  
23:        try  
24:        {  
25:          FileUpload1.SaveAs(uploadFile.SavePath);  
26:        }  
27:        catch (Exception ex)  
28:        {  
29:          lblerror.Text = "error : "+ex.Message;  
30:        }  
31:       }  
32:    }  
33:    protected void btnSaveToDB_Click(object sender, EventArgs e)  
34:    {  
35:      ImportDataIntoDB idid = new ImportDataIntoDB();  
36:      string count = idid.saveDataToDB();  
37:      if (Convert.ToInt16(count) < 0)  
38:      {  
39:        lblerror.Text = "Error Occurred...";  
40:      }  
41:      UploadFile upFile = new UploadFile();  
42:      upFile.DeleteFileNoException();  
43:    }  
44:  }

Step:4 In this i call the UploadFile class which is created in a separate class file. This class is used to store uploaded file and also used to generate OleDb connection string as per file selected. Here is the code of UploadFile Class :

1:  using System;  
2:  using System.Data;  
3:  using System.Configuration;  
4:  using System.Linq;  
5:  using System.Web;  
6:  using System.Web.Security;  
7:  using System.Web.UI;  
8:  using System.Web.UI.HtmlControls;  
9:  using System.Web.UI.WebControls;  
10:  using System.Web.UI.WebControls.WebParts;  
11:  using System.Xml.Linq;  
12:  using System.IO;  
13:  ///   
14:  /// Summary description for UploadFile  
15:  ///   
16:  public class UploadFile  
17:  {  
18:    private string savePath;  
19:    public UploadFile()  
20:    {  
21:    }  
22:    public UploadFile(string originalFileName)  
23:    {  
24:      string tempFileName = Guid.NewGuid().ToString("N") + "_" + originalFileName;  
25:      string saveLocation = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["FileUploadLocation"]);  
26:      savePath = Path.Combine(saveLocation, tempFileName);  
27:      HttpContext.Current.Session["savePath"] = savePath;  
28:    }  
29:    ///   
30:    /// Temp path used to save the uploaded file  
31:    ///   
32:    public string SavePath  
33:    {  
34:      get  
35:      {  
36:        return savePath;  
37:      }  
38:    }  
39:    ///   
40:    /// Attempt to delete temp file  
41:    ///   
42:    public void DeleteFileNoException()  
43:    {  
44:      savePath = HttpContext.Current.Session["savePath"].ToString();  
45:      if (File.Exists(savePath))  
46:      {  
47:        try  
48:        {  
49:          File.Delete(savePath);  
50:        }  
51:        catch { }  
52:      }  
53:    }  
54:    ///   
55:    /// Return connection strinng based on file extension  
56:    ///   
57:    public string GetOleDbConnectionString()  
58:    {  
59:      savePath = HttpContext.Current.Session["savePath"].ToString();  
60:      var finfo = new FileInfo(savePath);  
61:      if (!finfo.Exists)  
62:      {  
63:        throw new FileNotFoundException(savePath);  
64:      }  
65:      var fileExtension = finfo.Extension.ToLower();  
66:      switch (fileExtension)  
67:      {  
68:        case ".xls":  
69:          return string.Format(ConfigurationManager.AppSettings["xlsOleDBConnection"], savePath);  
70:        case ".xlsx":  
71:          return string.Format(ConfigurationManager.AppSettings["xlsxOleDBConnection"], savePath);  
72:        default:  
73:          throw new NotSupportedException(String.Format("This file type {0} is not supported!", fileExtension));  
74:      }  
75:    }  
76:  }

Step:5 Now next step is read that file and save the data into sql server. Here is the method which reads the data from excel and save it into sql server database :

1:  public string saveDataToDB()  
2:    {  
3:      int j = 0;  
4:      string strQuery = "SELECT ProductCode,ProductName,ItemCaptionJP,ItemTextJP,IngredientsJP,CountryOfOrigin,HowToStore FROM [Sheet1$]";  
5:      DataTable dt = new DataTable();  
6:      OleDbCommand ocmd = new OleDbCommand(strQuery, oleconn);  
7:      //OleDbDataAdapter da = new OleDbDataAdapter(strQuery, oleconn);  
8:      oleconn.Open();  
9:      OleDbDataReader odr = ocmd.ExecuteReader();  
10:      int i = 0;  
11:      if (odr.HasRows)  
12:      {  
13:        while (odr.Read())  
14:        {  
15:          if (i == 0)  
16:            sb.Append("UPDATE Product_Master set [itemCaption_jp]=N'" + odr["ItemCaptionJP"] + "',[itemText_jp]=N'" + odr["ItemTextJP"].ToString() + "',[ingredients_jp]=N'" + odr["IngredientsJP"].ToString() + "',[countryoforigin_jp]=N'" + odr["CountryOfOrigin"].ToString() + "',[howtostore_jp]=N'" + odr["HowToStore"].ToString() + "' where [alternate_code]='" + odr["ProductCode"].ToString() + "' and Product_Name='" + odr["ProductName"].ToString() + "'");  
17:          else  
18:            sb.Append(";UPDATE Product_Master set [itemCaption_jp]=N'" + odr["ItemCaptionJP"] + "',[itemText_jp]=N'" + odr["ItemTextJP"].ToString() + "',[ingredients_jp]=N'" + odr["IngredientsJP"].ToString() + "',[countryoforigin_jp]=N'" + odr["CountryOfOrigin"].ToString() + "',[howtostore_jp]=N'" + odr["HowToStore"].ToString() + "' where [alternate_code]='" + odr["ProductCode"].ToString() + "' and Product_Name='" + odr["ProductName"].ToString() + "'");  
19:          i++;  
20:        }  
21:        j = UpdatedCommand(sb.ToString());  
22:      }  
23:      return j.ToString();  
24:    }  
25:    #region update,delete,insert data in Database  
26:    public int UpdatedCommand(string strupdatequery)  
27:    {  
28:      int intreturn = 0;  
29:      ClientScriptManager cs = Page.ClientScript;  
30:      Type ty = this.GetType();  
31:      try  
32:      {  
33:        SqlCommand cmdupdatecommand = new SqlCommand();  
34:        transsql = null;  
35:        if (sqlconn.State != ConnectionState.Closed)  
36:        {  
37:          sqlconn.Close();  
38:        }  
39:        sqlconn.Open();  
40:        transsql = sqlconn.BeginTransaction();  
41:        cmdupdatecommand.CommandTimeout = 0;  
42:        cmdupdatecommand.CommandText = strupdatequery;  
43:        cmdupdatecommand.Connection = sqlconn;  
44:        cmdupdatecommand.Transaction = transsql;  
45:        intreturn = cmdupdatecommand.ExecuteNonQuery();  
46:        transsql.Commit();  
47:        if (sqlconn.State != ConnectionState.Closed)  
48:        {  
49:          sqlconn.Close();  
50:        }  
51:        return intreturn;  
52:      }  
53:      catch (SqlException oleex)  
54:      {  
55:        transsql.Rollback();  
56:        strupdatequery = oleex.Message;  
57:        strerorrmsg1 = "Error In UpdatedCommand method " + oleex.Message.ToString();  
58:        strerorrmsg2 = strerorrmsg1.Replace("'", "");  
59:        string msg = "alert('" + strerorrmsg2 + "');";  
60:        cs.RegisterStartupScript(ty, strerorrmsg1, msg, true);  
61:        return -3;  
62:      }  
63:      catch (System.Exception exp)  
64:      {  
65:        transsql.Rollback();  
66:        strupdatequery = exp.Message;  
67:        strerorrmsg1 = "Error In UpdatedCommand method " + exp.Message.ToString();  
68:        strerorrmsg2 = strerorrmsg1.Replace("'", "");  
69:        string msg = "alert('" + strerorrmsg2 + "');";  
70:        cs.RegisterStartupScript(ty, strerorrmsg1, msg, true);  
71:        return -3;  
72:      }  
73:      finally  
74:      {  
75:        if (sqlconn.State != ConnectionState.Closed)  
76:        {  
77:          sqlconn.Close();  
78:        }  
79:      }  
80:    }  
81:    #endregion

Tuesday, September 13, 2011

4 Small But Important Tips For Fresher of Asp.net.

Asp.net is a awesome framework developing web sites and web applications.with visual studio asp.net become cooler and cooler. I have wrote an article Visual Studio 2010 New Tips and Shortcut previously. now I thought to write for asp.net tips and tricks because as the way you are using the framework it’ll be hard or easier to understand and do some work with minimum codes.
Ok here we go we’ll see what are the cool features offer asp.net for .net web developers.

1. Maintain the position of scrollbar on postback : since asp.net 1.1 it was a very hard to maintain the position of scrollbar when doing postback. it’s was very necessary feature when we edit some data and after submit find position that we were editing. In asp.net 2.0 you can simply add the MantainScrollPositionOnPostBack attribute to the page directive.

<%@ Page Language="C#" MaintainScrollPositionOnPostback="true" AutoEventWireup="true" CodeFile="..." Inherits="..." %>

2. Set Default Button for form when user hit the enter key : In asp.net 1.1 required to write javascript to set default key for when form submit after hit Enter key. fortunatly you can use HtmlForcontrol’s DefaultButton property to set which button should be clicked when the user hit the enter

<form id="frm" DefaultButton="btnSubmit" runat="server">

...

</form>

3. Set the default focus to control when page loads : as mention above also we can set default focus for page load like below

<form id="frm" DefaultFocus="txtUserName" runat="server">

...

</form>

4. Strongly type access to the cross-page postbacks : asp.net 2.0 introduced the concept of cross page post back where one page could post back information to another page.if you add public property in to code behind page that initiate the postback operation, you can access property in strong type manner by adding PreviouPageType directive in to target page of the post back.
<%@ PreviousPageType VirtualPath="Default.aspx" %>

by adding this directive to pageyou can access the TextBox defind in previous page in strongly type manner.

TextBox tb = PreviousPage.SearchTextBox;

Monday, September 5, 2011

Show/Hide ModalPopup Using Javascript.

The Asp.net Markup :

<asp:panel id="pnlDemo" runat="server" style="display: none;">
<h1> This panel is a test demo</h1> </asp:panel>

<cc1:modalpopupextender backgroundcssclass="modalBlockBackground" behaviorid="ModalPopupExtender" dynamicservicepath="" enabled="True" id="ModalPopupExtender" popupcontrolid="pnlDemo" runat="server" targetcontrolid="linkButton1"> </cc1:modalpopupextender>

If we want to use the javascript to control the popup, we can just simply to write the code like this:

if(!show)
{
$find('ModalPopupExtender').hide();
}
else
{
$find("ModalPopupExtender").show();
}

There is one thing need to be taken care which is the BehavirID, the code in the Javasctipt will look for the BehaviorID property for the ModalPopupExtender.

I hope this can be helpful for you.

Thursday, September 1, 2011

3 Things Must You Know About C# Code.

1. Null Coalescing Operator (??)

A new operator can be used which can be shortcut of ternary operator to check the null value:

Ternary operator (?:):

string empName = (value != null) ? value : string.Empty;

we can write it using null-coalescing operator (??):

string empName = value ?? string.Empty;

2. The As Cast

we might have seen the code something like:
if (customer is DeluxCustomer)
{
var dCust = (DeluxCustomer)customer
// ...
}

This is redundant because we are checking the type twice. Once in the is check and once in the cast.

We can use as operator in place. This will cast the type if the types are compatible, or return null if not:
var dCust = customer as DeluxCustomer;
// ... we can write if condition to check for null
}

we can avoid double-checking the type.

3. Auto-Properties

This is the new concept to define the properties in C#: we can define the auto-property without much code:
public class Name

{
public int name { get; set; }
public int name { get; set; }
}

Friday, August 19, 2011

How To Read Local Drive Information With Asp.net Application

Some time we need to interact with the local file system, reading structure of the Directories, reading and writing files, or performing many other jobs.

In this Application we need use System.IO Namespace for working with file system directories. System.IO Contain Driveinfo class.

Driveinfo class is used to get information such us the name,type, and status of each drive.

DriveInfo DriveDetails= new DriveInfo(@"C:\");

DriveDetails.Name : it is used to get the name of the specified drive.

DriveDetails.DriveType : it is used to get type of the specified drive.

DriveDetails.AvailableFreeSpace : it is used to get the available free space in the specified drive.

DriveDetails.TotalFreeSpace :it is used to get the total free space in the specified drive.

DriveDetails.TotalSize :it is used to get the total size in the specified drive.

DriveDetails.DriveFormat : it is used to get the format of the specified drive.

Here I am Getting C drive Information such us Drive Name, Drive Type, Free Space, Drive Format, Total Free Space, Total Size.

Here i am getting drive Size is in bytes. but i want result in Giga Bytes.

1024 Bytes = 1 KiloByte(KB)
1024 KiloBytes(KB) = 1 MegaByte(MB)
1024 MegaBytes(MB) = 1 GigaByte(GB)
1024 GigaBytes(GB) = 1 TeraByte(TB)

I am converting Bytes to Giga bytes.

double db = Convert.ToDouble(lblTotalSize.Text);
double db1 = db / 1024;
double db2 = db1 / 1024;
int db3 = Convert.ToInt32(db2) / 1024;

protected void Page_Load(object sender, EventArgs e)

{

DriveInfo DriveDetails= new DriveInfo(@"C:\");

lblDriveName.Text = DriveDetails.Name;

lblDriveType.Text = DriveDetails.DriveType.ToString();

lblFreeSpace.Text = DriveDetails.AvailableFreeSpace.ToString();

lblDriveFormat.Text = DriveDetails.DriveFormat.ToString();

lblTotalFreeSpace.Text = DriveDetails.TotalFreeSpace.ToString();

lblTotalSize.Text = DriveDetails.TotalSize.ToString();

double db = Convert.ToDouble(lblTotalSize.Text);

double db1 = db / 1024;

double db2 = db1 / 1024;

int db3 = Convert.ToInt32(db2) / 1024;

lblTotalSizeInGb.Text = db3.ToString() + " GB";

double TS = Convert.ToDouble(lblTotalFreeSpace.Text);

double TS1 = TS / 1024;

double TS2 = TS1 / 1024;

int TS3 = Convert.ToInt32(TS2) / 1024;

lblTotalFreeSpaceGB.Text = TS3.ToString() + " GB";

}

Now you can understand about How to read Drive information from local System using Asp.Net Web application AND to calculate Bytes to Giga bytes in Web Application

If you find a bug and have a fix for it: please let me know and I'll be happy to update the code I am hosting here.

Any suggestions will be appreciated greatly.

Saturday, February 5, 2011

varchar Vs. nvarchar

char, nchar, varchar, and nvarchar each take a length parameter which the database engine can use to optimize storage.

UTF-16 (when sticking to the BMP) has a simple two-to-one relationship between bytes of storage and characters, which the database can take advantage of.

The amount of storage required for a UTF-8 string of N characters is not as clear, and could result in wasted space, or unexpectedly truncated strings.

varchar does not store ASCII, it stores an 8-bit encoding.

nvarchar and varchar do not "function identically" since varchar does not function at all in lots of scenarios.

varchar contain max size 8000 while nvarchar size is 4000.

nvarchar may take up twice as much space, but varchar is twice as slow, since it requires string conversions for every read and write (on those occasions when it actually works).

Asp.net and C#.net Tips

Search Web

Pages