How to get Row Number with the result of SQL select query

19 10 2009

Try the following code to get Row Number…

SELECT ROW_NUMBER() OVER (ORDER BY PRODUCTID ASC) AS ROWNO, * FROM PRODUCT_MASTER

Thank You


Comments : Leave a Comment »

Categories : SQL Server

How to Display A for one Condition and B for another condition in single result window of Select statement in SQL Server

2 09 2009

To get the result in a single result window, but depending on two different condition you have to display the result. To do so use UNION keyword of sql.

For Example :

select col1,col2, A as Col3 from Table_Name where <condition> UNION select col1,col2, B as Col3 from Table_Name where <condition>

Now you have to change the col1,col2,col3, table name and condition in the above query and execute and see the result.


Comments : 2 Comments »

Categories : SQL Server

How to Add Element Dynamically into one Array in C#

2 09 2009

We all knows that ArrayList is having one Add() to do so…
But in case of array it’s some how triky…
Here is the Example to add one element into an existing array..
CODE :

string[] str ={ "1", "2", "3" };
string[] newstr = new string[str.Length + 1];
str.CopyTo(newstr, 0);
newstr.SetValue("4", newstr.Length - 1);
str = newstr;

Try the above code and enjoy it…

Regards,

Manoranjan Sahoo

 

Comments : 2 Comments »

Categories : .Net, ASP.Net, C#.Net

SQL Server Database Shrink or Compress

27 08 2009

We are always see that our database file(.mdf) is very low, but the log file is too high nearly 10-100% high or more. One day i saw in our company database, the .mdf file is nearly 500MB but the log file is 35GB. So i tried to clean the log file and got the solution from one of sql server ebooks. So i place that code here to help those people who are having this problem.

Syntax :

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, <file size>)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, <file size>)

You can change database name, TransactionLogName and file size as per your requirement.

For Example :

USE dbproduct
GO
DBCC SHRINKFILE(dbproduct_log, 10)
BACKUP LOG dbproduct WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbproduct_log, 10)

Thank You.

Regards,
Manoranjan Sahoo


Comments : Leave a Comment »

Categories : SQL Server

How to check case sensitive data in SQL Server

24 07 2009

Converting data to binary type before comparison

SELECT * FROM dbo.TableName    WHERE CAST(Password AS varbinary(15)) = CAST(“Password” AS varbinary(15))
AND CustPassword = @CustPassword


Comments : Leave a Comment »

Categories : SQL Server

How to store a file in SQL Server 2005 database using ASP.Net & C#.Net

23 07 2009

In this post i explain about how to store a file into Microsoft SQL Server 2005 database using ASP.Net and C#.Net. First I create a database table in which we are going to store the file content and details. Then create a new Website with a sample webform file(default.aspx) which contains one upload control and a button. When we click on the button after selecting the file it will save the file details and content into database. Then we have to create a Generic Handler(.ashx) file which will fetch the data from database and display them to user.

Step 1: Now create a table with name File in your database with following fields:

Column Name        Data Type
ID                          uniqueidentifier
FileName            nvarchar(150)
FileType             nvarchar(100)
FileSize               int
FileContent       varbinary(MAX)

Step 2: Create a new WebForm in your website with name default.aspx and add the following code:

<%@ Page Language="C#" AutoEventWireup="false" 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Home Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload runat="server" ID="FileUpload1" />
<asp:Button ID="btnSave" runat="server" Text="SaveToDB" OnClick="btnSave_Click" />
<asp:HyperLink runat="server" ID="Result" Target="_blank" />
</form>
</body>
</html>

Step 3: Write the following code in default.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public partial class default : System.Web.UI.Page
{
 void btnSave_Click(object sender, EventArgs e)
 {
 if (!FileUpload1.HasFile) return;
 Guid Id = Guid.NewGuid();// generate new Id
 using (SqlCommand command = new SqlCommand())
 {
 command.Connection = new SqlConnection(ConfigurationManager.AppSettings["sqlconnstr"].ToString());
 command.CommandText = @"insert into [File](ID, FileName, FileType, FileSize, FileContent)
 values(@Id, @FileName, @FileType, @FileSize, @FileContent)";
 command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id;
 command.Parameters.Add("@FileName", SqlDbType.NVarChar, 150).Value = Path.GetFileName(FileUpload1.PostedFile.FileName);
 command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = FileUpload1.PostedFile.ContentType;
 command.Parameters.Add("@FileSize", SqlDbType.Int).Value = FileUpload1.PostedFile.ContentLength;
 // filecontent, convert from stream to byte array
 byte[] fileContent = new byte[FileUpload1.PostedFile.ContentLength];
 FileUpload1.PostedFile.InputStream.Read(fileContent, 0, FileUpload1.PostedFile.ContentLength);
 command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent;
 command.Connection.Open();
 command.ExecuteNonQuery();
 }
 // show result through generic handler
 Result.NavigateUrl = "ShowFile.ashx?Id=" + Id.ToString();
 Result.Text = "Click here to view the uploaded file";
 }
}

Step 4: Then create a Generic Handler named as ShowFile.ashx and write the below code onto that.

<%@ WebHandler Language=”C#” %>

using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public class ShowFile : IHttpHandler {

 public void ProcessRequest (HttpContext context) 
 {
 Guid Id = new Guid(context.Request.QueryString["Id"]);
 using (SqlCommand cmd = new SqlCommand())
 {
 cmd.Connection = new SqlConnection(ConfigurationManager.AppSettings["sqlconnstr"].ToString());
 cmd.CommandText = "select * from [TableName] where ID = @Id";
 cmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id;
 cmd.Connection.Open();
 SqlDataReader sdr = cmd.ExecuteReader();
 if (sdr.Read())
 {
 context.Response.Clear();
 context.Response.ContentType = (string)sdr["FileType"];
 context.Response.AddHeader("Content-Disposition", String.Format("inline;filename={0};", sdr["FileName"].ToString()));
 context.Response.AddHeader("Content-Length", sdr["FileSize"].ToString());
 context.Response.BinaryWrite((byte[])sdr["FileContent"]);
 context.Response.End();
 }
 }

 }

 public bool IsReusable {
 get {
 return false;
 }
 }

}

Step 5: Run the Website now.


Comments : 3 Comments »

Categories : .Net, ASP.Net, C#.Net

Count Online Visitor in ASP.Net & VB.Net

23 06 2009

Place the following code in Global.asax file. Here, we track the active Sessions for our web application. There are three subroutines into which we will be looking to do this – Application_OnStart, Session_OnStart, Session_OnEnd.

Step-1 :

In the Application_OnStart subroutine, we have to set the user count to 0, when the server starts the application.

Sub Application_OnStart (Sender as Object, E as EventArgs)
     ' Set user count to 0 when start the application 
     Application("ActiveUsers") = 0
 End Sub

Step-2 :

In the Session_OnStart subroutine, we have to increament the Activeuser by 1:

 Sub Session_OnStart (Sender as Object, E as EventArgs)
      Application.Lock 
      Application("ActiveUsers") = Cint(Application("ActiveUsers")) + 1 
      Application.UnLock
 End Sub

In this case you have to set Timeout – you don’t need to put anything here, but the default Timeout is 20 minutes, so you can change it depending on the needs of your particular application.

To set the session start time, we add (Session(“Start”) = Now). Basically, when the user hits the site and opens a web page (asp.net page), at the time of opening the page, the session starts. Next, we increase the active visitors count when we start the session (Application(“ActiveUsers”) = Cint(Application(“ActiveUsers”)) + 1 ). The Application lock & unlock adds more stability to the counting.

Step-3 :

we must decrement the number of Active Users on the basis of online sessions in the Session_OnEnd subroutine:

 Sub Session_OnEnd(Sender as Object, E as EventArgs)
     Application.Lock 
     Application("ActiveUsers") = Cint(Application("ActiveUsers")) - 1 
     Application.UnLock
 End Sub

Step-4 :

Then you can place the following code to access the Application(“ActiveUsers”) in .aspx file

<% 
Dim intNumber as Integer
intNumber =Application("ActiveUsers")
response.write (intNumber ) 
%> Currently Online

Regards,
Manoranjan Sahoo


Comments : Leave a Comment »

Categories : .Net, ASP.Net, VB.Net

How to Make all Row Editable of a GridView

8 06 2009

To Make All Row Editable of a GridView follow these steps :

Steps:

1. Create one GridView with all field as Template Field and add TextBox for Item Template . So, when it displays the data it will display in Textbox so that you can edit those field without click edit button.
2. Create One Button. Set Text as Update. So that when we click on this button, this will update the database as per the changed data in gridview.

Look at the following code to do so…

CODE:

<asp:GridView ID="GridView1" runat="server" Style="z-index: 106; left: 93px; position: absolute;
 top: 283px" Width="575px" EditIndex="1" AutoGenerateColumns="False">
 <Columns>
 <asp:TemplateField>
 <ItemTemplate>
 <asp:TextBox ID="txt1" runat="server" MaxLength="3" Style="position: static" CssClass ="gv" text = '<%# Eval("slNo") %>' AutoPostBack ="false" TabIndex ="0"  Width="25px"  Height ="15px" Visible ="true" ></asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField>
 <ItemTemplate>
 <asp:TextBox ID="txt2" runat="server" Style="position: static" CssClass ="gv" text = '<%# Eval("Name") %>' AutoPostBack ="false" TabIndex ="0"  Width="250px"  Height ="15px" Visible ="true" ></asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField>
 <ItemTemplate>
 <asp:TextBox ID="txt3" runat="server" Style="position: static" CssClass ="gv" text = '<%# Eval("Address") %>' AutoPostBack ="false" TabIndex ="0"  Width="150px"  Height ="15px" Visible ="true" ></asp:TextBox>
 </ItemTemplate>
 </asp:TemplateField>
</Columns>
 </asp:GridView>

Then Place the following code in the button click event,

CODE:

protected void Button1_Click(object sender, EventArgs e)
{

for (int i = 0; i
{

GridViewRow gvr = GridView1.Rows[i];

string str1 = ((TextBox)gvr.FindControl("txt1")).Text;

string str2 = ((TextBox)gvr.FindControl("txt2")).Text;

string str3 = ((TextBox)gvr.FindControl("txt3")).Text;
//code for saving the data into database
}
}

Thank You!
Regards,
Manoranjan Sahoo


Comments : Leave a Comment »

Categories : .Net, ASP.Net, C#.Net

How to Validate Decimal and Currency in ASP.Net Control using Javascript

8 06 2009

Try this code :
place this in head tag of .aspx file
CODE

function validateNumber(txtbox1)
{
     var str=txtbox1.value;
     var str1="0123456789.";
     var i=0;
     for(i=0;i<str.length;i++)
     {
          if(str1.indexOf(str.substring(i,i+1))<0 || str1.indexOf(str.substring(i,i+1))>str1.length || str.indexOf(".")!=str.lastIndexOf("."))
          {
              txtbox1.value=str.substring(0,str.length-1);
              alert("Enter Only Decimal Number");
              txtbox1.focus();
              break;
          }
      }
}
function checkDecimal(txtBox1)
{
    var str2=txtbox1.value;
    if(str2.indexOf('.')<0)
    {
       txtbox1.value=str2+".00";
    }
}

Then Place this into page_load event of that page
CODE

TextBox1.Attributes.Add("onKeyup","javascript:validateNumber(TextBox1)")
TextBox1.Attributes.Add("onBlur","javascript:checkDecimal(TextBox1)")

Thank You.


Comments : Leave a Comment »

Categories : .Net, ASP.Net, JAVA Script

Easy Way To Show Week Of Year And Corresponding Date For a Year in C#.Net

6 06 2009

Here I put the simplest way to find the week of the year and also display the date and week in the form of [01] [01-Jan-2009]

CODE

 DateTime date = DateTime.Now.AddDays(-(DateTime.Now.DayOfYear-1));
 ArrayList arr=new ArrayList ();
 int yr=date.Year;
 while (date.Year!=yr+1)
 {
 string strdate = "[" + Convert.ToString((date.DayOfYear-1) / 7+1) + "] [" + Convert.ToString(date.Day + "-" + (date.Date.ToString("dd-MMM-yyyy").Split('-'))[1] + "-" + date.Year) + "]";
 arr.Add(strdate);
 date=date.AddDays(1);
 }

The above code return one ArrayList object which contains all date from 01 jan to 31 Dec of a current year.

You can put that arraylist into any control like dropdownlist or listbox like the following code :

 DropDownList1.Items.Clear();
 for (int i = 0; i < arr.Count; i++)
 {
 DropDownList1.Items.Add(arr[i].ToString());
 }

Comments : Leave a Comment »

Categories : .Net, ASP.Net, C#.Net

« Previous Entries