CREATE FUNCTION dbo.fnc_Split
(
@Data varchar(2000),
@Sep varchar(5)
)
RETURNS @Temp table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@Sep,@RData)>0)
Begin
Insert Into @Temp(data)
Select
Data = ltrim(rtrim(Substring(@Data,1,Charindex(@Sep,@Data)-1)))
Set @Data = Substring(@Data,Charindex(@Sep,@Data)+1,len(@Data))
Set @Cnt = @Cnt + 1
End
Insert Into @Temp (data)
Select Data = ltrim(rtrim(@Data))
Return
END
Friday, November 6, 2009
Thursday, October 29, 2009
Windows Run Commands
Windows Run Commands
Windows Run Commands
Windows has a lot of useful commands accessible from Run menu. To invoke the Run box, click on Start button and choose
Run, or hold down the Windows key and hit R. Run commands allows simply run applications by typing their name instead of click on an icon. Remember
these commands. They can save a lot of your time.
P.S. Some commands may be missed in your system.
|
Thursday, October 22, 2009
Thursday, October 15, 2009
Thursday, September 24, 2009
ranking in sql
RANK() VS DENSE_RANK() With an Example using SQL Server 2005/2008
1:12 AM Posted by Suprotim Agarwal
Labels: Transact SQL T-SQL The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.
The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.
Let us understand this difference with an example and then observe the results while using these two functions:
We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.
Using the RANK() function
SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.
Using the DENSE_RANK() function
SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.
Well I hope after seeing these example, you will understand
dense_rank() over(partition by field1 order by field2) as name1,
row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1 order by fldnm2)) order by fld2) as name2
1:12 AM Posted by Suprotim Agarwal
Labels: Transact SQL T-SQL The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.
The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.
Let us understand this difference with an example and then observe the results while using these two functions:
We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.
Using the RANK() function
SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.
Using the DENSE_RANK() function
SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.
Well I hope after seeing these example, you will understand
dense_rank() over(partition by field1 order by field2) as name1,
row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1 order by fldnm2)) order by fld2) as name2
Monday, September 21, 2009
assembly and reassembly
2 votes for this article.
Popularity: 1.20 Rating: 4.00 out of 5
1
2
3
4
5
Introduction
Do you have a distributed .NET application from the pre-WCF days that you want to migrate to WCF, but you either don't have the source because it's a third party component, or you can't change the source because your boss can't or won't make the jump to .NET 3.0? You just need to decorate your interfaces with some attributes which would be incredibly simple if you could modify the source code, but in those cases where you can't, it's not much harder to modify the MSIL.
Background
I have all these applications built for .NET remoting that are being actively maintained in a .NET 1.1 environment. When I built them, I split out the interface code from the implementation code because the interfaces are distributed in an assembly to clients and the implementation is in a server side assembly. This is exactly the way you would organize your ServiceContract in WCF, but I need one more thing to host the code in a WCF ServiceHost - the attributes. It would be really nice to add the [ServiceContract] and [OperationContract] attributes to my interface, but that means I have to recompile my interface library under .NET 3.0 and also branch my source control projects to maintain the separate versions, just to add some silly attributes. The more I think about it, that' s probably easier than updating the MSIL, but in some cases with legacy code, that isn't an option.
To port that old remoting application assembly over to WCF, the process is pretty simple:
Use ILDASM to disassemble your assembly into MSIL
Use your favorite text editor to add IL for referencing the assembly where the attributes are defined (System.ServiceModel), and then add IL for the attributes themselves to the interface and methods (ServiceContract and OperationContract)
Use ILASM to assemble your MSIL back into an assembly with your new attributes
Tedious? A little, but not as bad as trying to talk your boss into dropping .NET 1.1 support.
Disassembly
The easy part. Just make sure you aren't violating any license agreements here if you're working with third party code. They usually have a clause about how you better not disassemble it. If they do, turn back now and ask them to do this for you. Use the ildasm version (and later on, ilasm) that the existing assembly is built with to ensure you'll keep compatibility in your legacy environment. My assembly currently runs under .NET 1.1, so I'm using ildasm version 1.1.4322.573.
ildasm.dll /out=.il
Now you've probably got two files - the MSIL files (.il) and the resource file (.res). Open the MSIL file in your favorite text editor. Since you're digging into MSIL, you should make your background black and your text bright green Courier font so it's exactly how movie producers envisioned programmers hacking executables back in 1979.
You'll probably also find it helpful to disassemble an assembly from a working WCF project so you can see how the attributes are applied by the compiler.
Modification to IL
Tedious, but still easy. You need to add the referenced assembly to the top of the MSIL, where you'll find any other assembly references, like mscorlib and System:
.assembly extern System.ServiceModel
{
.publickeytoken = (B7 7A 5C 56 19 34 E0 89 ) // .z\V.4..
.ver 3:0:0:0
}
Now you need to find the interface. It will start with .class interface public abstract. Just below the curly brace but before the first .method, you need to add the [ServiceContract] attribute MSIL:
.class interface public abstract auto ansi IMyServiceInterface
{
.custom instance void [System.ServiceModel]
System.ServiceModel.ServiceContractAttribute::.ctor() = ( 01 00 00 00 )
Now you need to add the [OperationContract] MSIL to your methods. Since they are interface methods, they'll be abstract - just a pair of curly braces. You need to add the attribute inside the curly braces.
.method public hidebysig newslot abstract virtual
instance class MyReturnType
MyMethod(valuetype [mscorlib]System.Guid inputId,
string inputData) cil managed
{
.custom instance void [System.ServiceModel]
System.ServiceModel.OperationContractAttribute::.ctor() = ( 01 00 00 00 )
} // end of method IMyServiceInterface::MyMethod
Re-assembly
Once you've updated all the methods, it's time to recompile the assembly. If you haven't already, make a backup of your existing assembly just in case you break something. When you assemble the new file, you will need to remember to include the resource file if ildasm produced one.
Again, make sure you use the ilasm version that corresponds to the .NET version of your legacy assembly. Mine is .NET 1.1, so I'm using ilasm version 1.1.4322.2032. This will ensure that your assembly will still run under .NET 1.1 if necessary. The great thing about attributes is that if the executing code doesn't know about them, it just won't use them; you aren't going to break runtime compatibility.
ilasm /DLL.il /resource=.res
At this point you have the same .NET 1.1 compatible assembly, but your interface and methods are decorated with the attributes WCF will need when running under .NET 3.0. Take the updated interface assembly and the unchanged implementation assembly and rehost that legacy code in the robust and secure infrastructure provided by WCF!
License
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
Popularity: 1.20 Rating: 4.00 out of 5
1
2
3
4
5
Introduction
Do you have a distributed .NET application from the pre-WCF days that you want to migrate to WCF, but you either don't have the source because it's a third party component, or you can't change the source because your boss can't or won't make the jump to .NET 3.0? You just need to decorate your interfaces with some attributes which would be incredibly simple if you could modify the source code, but in those cases where you can't, it's not much harder to modify the MSIL.
Background
I have all these applications built for .NET remoting that are being actively maintained in a .NET 1.1 environment. When I built them, I split out the interface code from the implementation code because the interfaces are distributed in an assembly to clients and the implementation is in a server side assembly. This is exactly the way you would organize your ServiceContract in WCF, but I need one more thing to host the code in a WCF ServiceHost - the attributes. It would be really nice to add the [ServiceContract] and [OperationContract] attributes to my interface, but that means I have to recompile my interface library under .NET 3.0 and also branch my source control projects to maintain the separate versions, just to add some silly attributes. The more I think about it, that' s probably easier than updating the MSIL, but in some cases with legacy code, that isn't an option.
To port that old remoting application assembly over to WCF, the process is pretty simple:
Use ILDASM to disassemble your assembly into MSIL
Use your favorite text editor to add IL for referencing the assembly where the attributes are defined (System.ServiceModel), and then add IL for the attributes themselves to the interface and methods (ServiceContract and OperationContract)
Use ILASM to assemble your MSIL back into an assembly with your new attributes
Tedious? A little, but not as bad as trying to talk your boss into dropping .NET 1.1 support.
Disassembly
The easy part. Just make sure you aren't violating any license agreements here if you're working with third party code. They usually have a clause about how you better not disassemble it. If they do, turn back now and ask them to do this for you. Use the ildasm version (and later on, ilasm) that the existing assembly is built with to ensure you'll keep compatibility in your legacy environment. My assembly currently runs under .NET 1.1, so I'm using ildasm version 1.1.4322.573.
ildasm
Now you've probably got two files - the MSIL files (
You'll probably also find it helpful to disassemble an assembly from a working WCF project so you can see how the attributes are applied by the compiler.
Modification to IL
Tedious, but still easy. You need to add the referenced assembly to the top of the MSIL, where you'll find any other assembly references, like mscorlib and System:
.assembly extern System.ServiceModel
{
.publickeytoken = (B7 7A 5C 56 19 34 E0 89 ) // .z\V.4..
.ver 3:0:0:0
}
Now you need to find the interface. It will start with .class interface public abstract. Just below the curly brace but before the first .method, you need to add the [ServiceContract] attribute MSIL:
.class interface public abstract auto ansi IMyServiceInterface
{
.custom instance void [System.ServiceModel]
System.ServiceModel.ServiceContractAttribute::.ctor() = ( 01 00 00 00 )
Now you need to add the [OperationContract] MSIL to your methods. Since they are interface methods, they'll be abstract - just a pair of curly braces. You need to add the attribute inside the curly braces.
.method public hidebysig newslot abstract virtual
instance class MyReturnType
MyMethod(valuetype [mscorlib]System.Guid inputId,
string inputData) cil managed
{
.custom instance void [System.ServiceModel]
System.ServiceModel.OperationContractAttribute::.ctor() = ( 01 00 00 00 )
} // end of method IMyServiceInterface::MyMethod
Re-assembly
Once you've updated all the methods, it's time to recompile the assembly. If you haven't already, make a backup of your existing assembly just in case you break something. When you assemble the new file, you will need to remember to include the resource file if ildasm produced one.
Again, make sure you use the ilasm version that corresponds to the .NET version of your legacy assembly. Mine is .NET 1.1, so I'm using ilasm version 1.1.4322.2032. This will ensure that your assembly will still run under .NET 1.1 if necessary. The great thing about attributes is that if the executing code doesn't know about them, it just won't use them; you aren't going to break runtime compatibility.
ilasm /DLL
At this point you have the same .NET 1.1 compatible assembly, but your interface and methods are decorated with the attributes WCF will need when running under .NET 3.0. Take the updated interface assembly and the unchanged implementation assembly and rehost that legacy code in the robust and secure infrastructure provided by WCF!
License
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
Friday, July 31, 2009
export data image to excel from gridview
http://www.aspsnippets.com/post/2009/04/23/Export-GridView-with-Images-to-Word-Excel-and-PDF-Formats-in-ASPNet.aspx
Thursday, July 30, 2009
merge cell of same data in grid view
public static void MergeRows(GridView gridView)
{
for (int rowIndex = 0; rowIndex <= gridView.Rows.Count - 1; rowIndex++)
{
GridViewRow row = gridView.Rows[rowIndex];
for (int i = 2; i
int j = 1;
for (int z = i+1; z < row.Cells.Count ; z++)
{
if (row.Cells[i].Text == row.Cells[z].Text)
{
j++;
}
else
{
break;
}
}
row.Cells[i].ColumnSpan = j;
if (j > 1)
{
for (int k = 1; k <= j - 1; k++)
{
i++;
row.Cells[i].Visible = false;
}
}
}
}
}
{
for (int rowIndex = 0; rowIndex <= gridView.Rows.Count - 1; rowIndex++)
{
GridViewRow row = gridView.Rows[rowIndex];
for (int i = 2; i
int j = 1;
for (int z = i+1; z < row.Cells.Count ; z++)
{
if (row.Cells[i].Text == row.Cells[z].Text)
{
j++;
}
else
{
break;
}
}
row.Cells[i].ColumnSpan = j;
if (j > 1)
{
for (int k = 1; k <= j - 1; k++)
{
i++;
row.Cells[i].Visible = false;
}
}
}
}
}
export data to excel from gridview
public void ExportGridToExcel(GridView grdGridView, string fileName)
{
Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
{
Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
Merge Header - Gridview/DataGrid
protected void GridView_Merge_Header_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
//Build custom header.
GridView oGridView = (GridView)sender;
GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
TableCell oTableCell = new TableCell();
//Add Department
oTableCell.Text = "Department";
oTableCell.ColumnSpan = 2;
oGridViewRow.Cells.Add(oTableCell);
//Add Employee
oTableCell = new TableCell();
oTableCell.Text = "Employee";
oTableCell.ColumnSpan = 3;
oGridViewRow.Cells.Add(oTableCell);
oGridView.Controls[0].Controls.AddAt(0, oGridViewRow);
}
}
{
if (e.Row.RowType == DataControlRowType.Header)
{
//Build custom header.
GridView oGridView = (GridView)sender;
GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
TableCell oTableCell = new TableCell();
//Add Department
oTableCell.Text = "Department";
oTableCell.ColumnSpan = 2;
oGridViewRow.Cells.Add(oTableCell);
//Add Employee
oTableCell = new TableCell();
oTableCell.Text = "Employee";
oTableCell.ColumnSpan = 3;
oGridViewRow.Cells.Add(oTableCell);
oGridView.Controls[0].Controls.AddAt(0, oGridViewRow);
}
}
Merging columns in GridView/DataGrid header
Background
As necessity to show header columns in a few rows occurs fairly often it would be good to have such functionality in the GridView/DataGrid control as an in-built feature. But meanwhile everyone solves this problem in his own way.
The described below variant of the merging implementation is based on irwansyah's idea to use the SetRenderMethodDelegate method for custom rendering of grid columns header. I guess this approach can be simplified in order to get more compact and handy code for reuse.
The code overview
As it may be required to merge a few groups of columns - for example, 1,2 and 4,5,6 - we need a class to store common information about all united columns.
[Serializable]
private class MergedColumnsInfo
{
// indexes of merged columns
public List MergedColumns = new List();
// key-value pairs: key = the first column index, value = number of the merged columns
public Hashtable StartColumns = new Hashtable();
// key-value pairs: key = the first column index, value = common title of the merged columns
public Hashtable Titles = new Hashtable();
//parameters: the merged columns indexes, common title of the merged columns
public void AddMergedColumns(int[] columnsIndexes, string title)
{
MergedColumns.AddRange(columnsIndexes);
StartColumns.Add(columnsIndexes[0], columnsIndexes.Length);
Titles.Add(columnsIndexes[0], title);
}
}
Attribute Serializable is added in order to have a possibility to store information about merged columns in ViewState - it is required if paging or sorting is used.
That is the only additional action. Now the code usage.
.ascx file:
//for GridView
//for DataGrid
Columns can be defined in design time or can be auto generated - it does not matter and doesn't influence the further code. Merging also does not harm sorting and paging if they are used in the GridView/DataGrid.
.cs file:
//property for storing of information about merged columns
private MergedColumnsInfo info
{
get
{
if (ViewState["info"] == null)
ViewState["info"] = new MergedColumnsInfo();
return (MergedColumnsInfo)ViewState["info"];
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//merge the second, third and fourth columns with common title "Subjects"
info.AddMergedColumns(new int[] { 1, 2, 3 }, "Subjects");
grid.DataSource = ... //some data source
grid.DataBind();
}
}
Particular code for GridView: protected void GridView_RowCreated(object sender, GridViewRowEventArgs e)
{
//call the method for custom rendering the columns headers
if (e.Row.RowType == DataControlRowType.Header)
e.Row.SetRenderMethodDelegate(RenderHeader);
}
and for DataGrid: protected void DataGrid_ItemCreated(object sender, DataGridItemEventArgs e)
{
//call the method for custom rendering the columns headers
if (e.Item.ItemType == ListItemType.Header)
e.Item.SetRenderMethodDelegate(RenderHeader);
}
Next code is common for both GridView and DataGrid:
//method for rendering the columns headers
private void RenderHeader(HtmlTextWriter output, Control container)
{
for (int i = 0; i < container.Controls.Count; i++)
{
TableCell cell = (TableCell)container.Controls[i];
//stretch non merged columns for two rows
if (!info.MergedColumns.Contains(i))
{
cell.Attributes["rowspan"] = "2";
cell.RenderControl(output);
}
else //render merged columns common title
if (info.StartColumns.Contains(i))
{
output.Write(string.Format("{1} ",
info.StartColumns[i], info.Titles[i]));
}
}
//close the first row
output.RenderEndTag();
//set attributes for the second row
grid.HeaderStyle.AddAttributesToRender(output);
//start the second row
output.RenderBeginTag("tr");
//render the second row (only the merged columns)
for (int i = 0; i < info.MergedColumns.Count; i++)
{
TableCell cell = (TableCell)container.Controls[info.MergedColumns[i]];
cell.RenderControl(output);
}
}
As necessity to show header columns in a few rows occurs fairly often it would be good to have such functionality in the GridView/DataGrid control as an in-built feature. But meanwhile everyone solves this problem in his own way.
The described below variant of the merging implementation is based on irwansyah's idea to use the SetRenderMethodDelegate method for custom rendering of grid columns header. I guess this approach can be simplified in order to get more compact and handy code for reuse.
The code overview
As it may be required to merge a few groups of columns - for example, 1,2 and 4,5,6 - we need a class to store common information about all united columns.
[Serializable]
private class MergedColumnsInfo
{
// indexes of merged columns
public List
// key-value pairs: key = the first column index, value = number of the merged columns
public Hashtable StartColumns = new Hashtable();
// key-value pairs: key = the first column index, value = common title of the merged columns
public Hashtable Titles = new Hashtable();
//parameters: the merged columns indexes, common title of the merged columns
public void AddMergedColumns(int[] columnsIndexes, string title)
{
MergedColumns.AddRange(columnsIndexes);
StartColumns.Add(columnsIndexes[0], columnsIndexes.Length);
Titles.Add(columnsIndexes[0], title);
}
}
Attribute Serializable is added in order to have a possibility to store information about merged columns in ViewState - it is required if paging or sorting is used.
That is the only additional action. Now the code usage.
.ascx file:
//for GridView
//for DataGrid
Columns can be defined in design time or can be auto generated - it does not matter and doesn't influence the further code. Merging also does not harm sorting and paging if they are used in the GridView/DataGrid.
.cs file:
//property for storing of information about merged columns
private MergedColumnsInfo info
{
get
{
if (ViewState["info"] == null)
ViewState["info"] = new MergedColumnsInfo();
return (MergedColumnsInfo)ViewState["info"];
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//merge the second, third and fourth columns with common title "Subjects"
info.AddMergedColumns(new int[] { 1, 2, 3 }, "Subjects");
grid.DataSource = ... //some data source
grid.DataBind();
}
}
Particular code for GridView: protected void GridView_RowCreated(object sender, GridViewRowEventArgs e)
{
//call the method for custom rendering the columns headers
if (e.Row.RowType == DataControlRowType.Header)
e.Row.SetRenderMethodDelegate(RenderHeader);
}
and for DataGrid: protected void DataGrid_ItemCreated(object sender, DataGridItemEventArgs e)
{
//call the method for custom rendering the columns headers
if (e.Item.ItemType == ListItemType.Header)
e.Item.SetRenderMethodDelegate(RenderHeader);
}
Next code is common for both GridView and DataGrid:
//method for rendering the columns headers
private void RenderHeader(HtmlTextWriter output, Control container)
{
for (int i = 0; i < container.Controls.Count; i++)
{
TableCell cell = (TableCell)container.Controls[i];
//stretch non merged columns for two rows
if (!info.MergedColumns.Contains(i))
{
cell.Attributes["rowspan"] = "2";
cell.RenderControl(output);
}
else //render merged columns common title
if (info.StartColumns.Contains(i))
{
output.Write(string.Format("
info.StartColumns[i], info.Titles[i]));
}
}
//close the first row
output.RenderEndTag();
//set attributes for the second row
grid.HeaderStyle.AddAttributesToRender(output);
//start the second row
output.RenderBeginTag("tr");
//render the second row (only the merged columns)
for (int i = 0; i < info.MergedColumns.Count; i++)
{
TableCell cell = (TableCell)container.Controls[info.MergedColumns[i]];
cell.RenderControl(output);
}
}
Wednesday, July 22, 2009
convert dd/mm/yyyy to any format
Dim result As String = DateTime.ParseExact("24/5/2009 3:40:00 AM", "d/M/yyyy h:mm:ss tt", CultureInfo.InvariantCulture) .ToString("M/d/yyyy h:mm:ss tt")
Wednesday, July 15, 2009
ASP.net Tips
1.Smart navigation
Smart navigation is a little-known Internet Explorer feature that enables the individual controls on your Web forms to maintain focus between postback, as well as allows you to suppress that flicker that occurs as you load the new page.
To turn on this little-known feature, simply set the smartNavigation property of your ASPX page to True. You can also apply the property to all project pages, by adding the tag to the following location within your Web.config file:
Note that smart navigation works on only Internet Explorer 5 and above; however, ASP.NET will automatically detect this and serve up the ?smart? code only if the target browser supports it.
Also, I?d personally advise that you test it against any third-party menu controls or scripts you may have running: it is prone to falling over on particularly advanced pages.
2.Stopping Your User from Right-Clicking
Want to prevent your user from performing any of the other commands available by right-clicking on a Web page in Internet Explorer? It?s not foolproof, but this neat little HTML edit usually does the trick.
Just alter the opening tag of your HTML to the following:
When the menu is requested, the oncontextmenu event runs, and we instantly cancel it using JavaScript. This is especially potent as a method for stopping the user from viewing your source, when used in conjunction with a menu-less browser window. Great stuff!
3.Creating Images Dynamically
Ask any ASP developer who has ever tried to dynamically create his own images and he?ll tell you it?s a nightmare. In fact, it?s more than a nightmare. It?s practically hell. The only true solution? Reverting to an expensive, dodgy, third-party control to do the work for you.
With ASP.NET, however, you can develop your own dynamic images with ease. Simply create an image object and use the new GDI+ features to add objects to that image, such as text, rectangles, and ellipses. After that, you can simply stream straight back down to the client.
But covering the graphics features in depth would require at least another two books, and, unfortunately, we don?t have that much room. So, I?m going to share a sample that demonstrates creating a small ?Drawing? button, alongside a little blue-and-yellow bullet point.It?s the sort of personalized graphic you?ll find on sites such as Amazon.com.
Here?s the code:
Bitmap objBitmap = new Bitmap(120, 30);
Graphics objGraphics = Graphics.FromImage(objBitmap);
objGraphics.FillRectangle(new SolidBrush(Color.LightBlue), 0, 0, 120, 30);
objGraphics.FillEllipse(new SolidBrush(Color.Blue), 3, 9, 10, 10);
objGraphics.FillEllipse(new SolidBrush(Color.Yellow), 4, 10, 8, 8);
objGraphics.DrawString("Drawing", new Font("Tahoma", 8), new SolidBrush(Color.Green), 16, 8);
Response.Clear();
Response.ContentType = "image/jpeg";
objBitmap.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
objGraphics.Dispose();
objBitmap.Dispose();
You can put it inside any event you want.
4.Clear All The Textbox Values (Reset Function)
In Classic ASP, to clear all the textboxes in a form, to start over, we just had to use a simple html 'Reset' button in the form. Sometimes that works in ASP.Net;sometimes it doesn't.
Here are a couple of ways to do this, iterating through the ASP.Net TextBox controls in a form --
Just create a Reset type subroutine - in that routine, use the following code:
in C# - it would be:
Control myForm = Page.FindControl("Form1");
foreach (Control ctl in myForm.Controls)
if(ctl.GetType().ToString().Equals("System.Web.UI.WebControls.TextBox"))
((TextBox)ctl).Text = "";
This will clear EVERYTHING from the textboxes - even if you had them pre-populated with data. A VERY simple way to just reset it to the condition at Page_Load time, just do this in the Reset SubRoutine:
Server.Transfer("YourPageName.aspx")
5.Pressing Enter key
Sometimes, you will notice, that, in an ASP.Net form, depending on the circumstances, pressing the 'Enter' key to submit the form does not work.
To force this to happen for a particular button on your page, just put this in the Page_Load routine:
Page.RegisterHiddenField("__EVENTTARGET", "button1")
Then, change 'button1' to the ID of your particular button. Understand, of course, if your cursor is inside of a MultiLine textbox, the default action of the enter key is to create a new line in the textbox, so, if this basically works anywhere outside of that scenario.
6.ASP.Net Server Controls Not Showing on pages
It's possible that ASP.Net is not registered correctly on your system.Try running aspnet_regiis from the command prompt.
Here's the default location:
C:\WINNT\Microsoft.NET\Framework\<>\aspnet_regiis.exe -i
Windows Server 2003, you must use aspnet_regiis -i -enable. This is because of the "Web Service Extensions" feature in IIS 6
7.Where To Store Database Connection
Let's say you have a database connection (or several) that you will be using over and over. Yes, you can manually copy/type it in on every ASP.Net page - BUT - an easier way is to store it in the Web.Config file (formerly config.web) and then refer to it in the code.
In Web.Config, you would add a key to the AppSettings Section:
for OleDb - use Absolute Path - Not Server.MapPath:
Then, in your ASP.Net application - just refer to it like this:
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings("MyDBConnection"));
Smart navigation is a little-known Internet Explorer feature that enables the individual controls on your Web forms to maintain focus between postback, as well as allows you to suppress that flicker that occurs as you load the new page.
To turn on this little-known feature, simply set the smartNavigation property of your ASPX page to True. You can also apply the property to all project pages, by adding the
Note that smart navigation works on only Internet Explorer 5 and above; however, ASP.NET will automatically detect this and serve up the ?smart? code only if the target browser supports it.
Also, I?d personally advise that you test it against any third-party menu controls or scripts you may have running: it is prone to falling over on particularly advanced pages.
2.Stopping Your User from Right-Clicking
Want to prevent your user from performing any of the other commands available by right-clicking on a Web page in Internet Explorer? It?s not foolproof, but this neat little HTML edit usually does the trick.
Just alter the opening tag of your HTML to the following:
When the menu is requested, the oncontextmenu event runs, and we instantly cancel it using JavaScript. This is especially potent as a method for stopping the user from viewing your source, when used in conjunction with a menu-less browser window. Great stuff!
3.Creating Images Dynamically
Ask any ASP developer who has ever tried to dynamically create his own images and he?ll tell you it?s a nightmare. In fact, it?s more than a nightmare. It?s practically hell. The only true solution? Reverting to an expensive, dodgy, third-party control to do the work for you.
With ASP.NET, however, you can develop your own dynamic images with ease. Simply create an image object and use the new GDI+ features to add objects to that image, such as text, rectangles, and ellipses. After that, you can simply stream straight back down to the client.
But covering the graphics features in depth would require at least another two books, and, unfortunately, we don?t have that much room. So, I?m going to share a sample that demonstrates creating a small ?Drawing? button, alongside a little blue-and-yellow bullet point.It?s the sort of personalized graphic you?ll find on sites such as Amazon.com.
Here?s the code:
Bitmap objBitmap = new Bitmap(120, 30);
Graphics objGraphics = Graphics.FromImage(objBitmap);
objGraphics.FillRectangle(new SolidBrush(Color.LightBlue), 0, 0, 120, 30);
objGraphics.FillEllipse(new SolidBrush(Color.Blue), 3, 9, 10, 10);
objGraphics.FillEllipse(new SolidBrush(Color.Yellow), 4, 10, 8, 8);
objGraphics.DrawString("Drawing", new Font("Tahoma", 8), new SolidBrush(Color.Green), 16, 8);
Response.Clear();
Response.ContentType = "image/jpeg";
objBitmap.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
objGraphics.Dispose();
objBitmap.Dispose();
You can put it inside any event you want.
4.Clear All The Textbox Values (Reset Function)
In Classic ASP, to clear all the textboxes in a form, to start over, we just had to use a simple html 'Reset' button in the form. Sometimes that works in ASP.Net;sometimes it doesn't.
Here are a couple of ways to do this, iterating through the ASP.Net TextBox controls in a form --
Just create a Reset type subroutine - in that routine, use the following code:
in C# - it would be:
Control myForm = Page.FindControl("Form1");
foreach (Control ctl in myForm.Controls)
if(ctl.GetType().ToString().Equals("System.Web.UI.WebControls.TextBox"))
((TextBox)ctl).Text = "";
This will clear EVERYTHING from the textboxes - even if you had them pre-populated with data. A VERY simple way to just reset it to the condition at Page_Load time, just do this in the Reset SubRoutine:
Server.Transfer("YourPageName.aspx")
5.Pressing Enter key
Sometimes, you will notice, that, in an ASP.Net form, depending on the circumstances, pressing the 'Enter' key to submit the form does not work.
To force this to happen for a particular button on your page, just put this in the Page_Load routine:
Page.RegisterHiddenField("__EVENTTARGET", "button1")
Then, change 'button1' to the ID of your particular button. Understand, of course, if your cursor is inside of a MultiLine textbox, the default action of the enter key is to create a new line in the textbox, so, if this basically works anywhere outside of that scenario.
6.ASP.Net Server Controls Not Showing on pages
It's possible that ASP.Net is not registered correctly on your system.Try running aspnet_regiis from the command prompt.
Here's the default location:
C:\WINNT\Microsoft.NET\Framework\<
Windows Server 2003, you must use aspnet_regiis -i -enable. This is because of the "Web Service Extensions" feature in IIS 6
7.Where To Store Database Connection
Let's say you have a database connection (or several) that you will be using over and over. Yes, you can manually copy/type it in on every ASP.Net page - BUT - an easier way is to store it in the Web.Config file (formerly config.web) and then refer to it in the code.
In Web.Config, you would add a key to the AppSettings Section:
for OleDb - use Absolute Path - Not Server.MapPath:
Then, in your ASP.Net application - just refer to it like this:
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings("MyDBConnection"));
Wednesday, July 1, 2009
Generate LDF file from MDF file in sqlserver
Last would be undocumented command DBCC REBUILD_LOG but I am not sure it will work in 2005...
1. Rename existing .mdf file to .mdf_old
2. Create a new database with same .mdf and .ldf file as old one.
3. Stop the sql server
4. Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
5. Rename .mdf_old to .mdf
6. Start sql server
7. You should see db in suspect mode.
and then follow the following instructions...
Note: you can't update system tables in 2005 so you can use alter database command to change to db emergency mode...
8. Change the database context to Master and allow updates to system
tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
9. Set the database in Emergency (bypass recovery) mode:
-- note the value of the status column for later use
select * from sysdatabases where name = ''
begin tran
update sysdatabases set status = 32768 where name = ''
-- Verify one row is updated before committing
commit tran
If you run DBCC REBUILD_LOG without setting the database in Emergency
mode, the command does not work. You do not receive an error, but the
log is not rebuilt either.
10. Stop and restart SQL server.
If you run DBCC REBUILD_LOG without recycling the server, the following
message displays:
Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in
bypass recovery mode to rebuild the log. DBCC execution completed. If
DBCC printed error messages, contact your system administrator.
11. The syntax for DBCC REBUILD_LOG is as follows:
DBCC REBUILD_LOG('','')
where is the name of the database and is the
physical path to the new log file, not a logical file name. If you do
not specify the full path, the new log is created in the Windows NT
system root directory (by default, this is the Winnt\System32
directory).
12. Rebuild the log with this code:
DBCC TRACEON (3604)
DBCC REBUILD_LOG('','')
Go
If the command is successful, the following message appears:
Warning: The log for database '' has been rebuilt.
Transactional consistency has been lost. DBCC CHECKDB should be run to
validate physical consistency. Database options will have to be reset,
and extra log files may need to be deleted.
After the log is successfully rebuilt, the database is placed in DBO Use
Only mode. That is, the status of the database is 2048 irrespective of
what the status was previously. You must reset the status using
sp_dboption or through the SEM.
13. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '', 'single user', 'true'
DBCC CHECKDB('')
Go
begin tran
update sysdatabases set status = where name =
''
-- verify one row is updated before committing
commit tran
Go
14. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
WARNING: After verifying the consistency of the database by running DBCC
CHECKDB, and fixing any errors, please make sure to check the database
for logical consistency as well. Because a new log has been built, the
transactions in the
old log are lost, hence you must also verify the logical consistency of
the data as well.
1. Rename existing .mdf file to .mdf_old
2. Create a new database with same .mdf and .ldf file as old one.
3. Stop the sql server
4. Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
5. Rename .mdf_old to .mdf
6. Start sql server
7. You should see db in suspect mode.
and then follow the following instructions...
Note: you can't update system tables in 2005 so you can use alter database command to change to db emergency mode...
8. Change the database context to Master and allow updates to system
tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
9. Set the database in Emergency (bypass recovery) mode:
-- note the value of the status column for later use
select * from sysdatabases where name = '
begin tran
update sysdatabases set status = 32768 where name = '
-- Verify one row is updated before committing
commit tran
If you run DBCC REBUILD_LOG without setting the database in Emergency
mode, the command does not work. You do not receive an error, but the
log is not rebuilt either.
10. Stop and restart SQL server.
If you run DBCC REBUILD_LOG without recycling the server, the following
message displays:
Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in
bypass recovery mode to rebuild the log. DBCC execution completed. If
DBCC printed error messages, contact your system administrator.
11. The syntax for DBCC REBUILD_LOG is as follows:
DBCC REBUILD_LOG('
where
physical path to the new log file, not a logical file name. If you do
not specify the full path, the new log is created in the Windows NT
system root directory (by default, this is the Winnt\System32
directory).
12. Rebuild the log with this code:
DBCC TRACEON (3604)
DBCC REBUILD_LOG('
Go
If the command is successful, the following message appears:
Warning: The log for database '
Transactional consistency has been lost. DBCC CHECKDB should be run to
validate physical consistency. Database options will have to be reset,
and extra log files may need to be deleted.
After the log is successfully rebuilt, the database is placed in DBO Use
Only mode. That is, the status of the database is 2048 irrespective of
what the status was previously. You must reset the status using
sp_dboption or through the SEM.
13. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '
DBCC CHECKDB('
Go
begin tran
update sysdatabases set status =
'
-- verify one row is updated before committing
commit tran
Go
14. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
WARNING: After verifying the consistency of the database by running DBCC
CHECKDB, and fixing any errors, please make sure to check the database
for logical consistency as well. Because a new log has been built, the
transactions in the
old log are lost, hence you must also verify the logical consistency of
the data as well.
Tuesday, June 23, 2009
javascript in asp.net and i frame redirect url,open popup
ClientScript.RegisterStartupScript(this.GetType(), "scriptid", "window.location.href='../sessexp.aspx'", true);
Page.RegisterStartupScript("openpopup", "");
Page.RegisterStartupScript("openpopup", "");
Thursday, June 11, 2009
Tuesday, June 9, 2009
Tuesday, June 2, 2009
Monday, May 25, 2009
date Format (for MM/DD/YYYY format)date Format (for DD/MM/YYYY format)
date Format (for MM/DD/YYYY format)
([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d
date Format (for DD/MM/YYYY format)
(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d
([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d
date Format (for DD/MM/YYYY format)
(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d
Thursday, May 7, 2009
dynamic Query sql
alter Procedure Search
@strSearch VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM ASSET_MASTER '
Select @SQL = @SQL + @strSearch
Exec ( @SQL)
GO
exec search 'where ASSET_NAME like ''monit%'''
@strSearch VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM ASSET_MASTER '
Select @SQL = @SQL + @strSearch
Exec ( @SQL)
GO
exec search 'where ASSET_NAME like ''monit%'''
Dynamic SQL
In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We'll cover Create Table, sp_executesql, permissions, scope and more.
Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
@strIDs VarChar(100)
AS
SELECT *
FROM
Products
WHERE
ProductID in (@strIDs)
GOOooops! No Go.
This will work
Create Procedure Search
@strIDs VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'
Exec ( @SQL)
GON.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @AliasThis will
Exec('Select UserName FROM Table as ' @Alias) DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
ID int NOT NULL Primary Key,
FieldName VarChar(10)
)Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)
SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
Exec (@SQL)Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'
Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)
Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'
Execute pubs.dbo.sp_executesql @sqlPermissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')
Select * from Authors
exec('set rowcount 0')Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
au_id VarChar(100),
au_fname VarChar(100),
au_lname VarChar(100)
)
declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)
Select * from #tempauth
drop table #tempauth
Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
@strIDs VarChar(100)
AS
SELECT *
FROM
Products
WHERE
ProductID in (@strIDs)
GOOooops! No Go.
This will work
Create Procedure Search
@strIDs VarChar(100)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'
Exec ( @SQL)
GON.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @AliasThis will
Exec('Select UserName FROM Table as ' @Alias) DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
ID int NOT NULL Primary Key,
FieldName VarChar(10)
)Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)
SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
Exec (@SQL)Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'
Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)
Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'
Execute pubs.dbo.sp_executesql @sqlPermissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')
Select * from Authors
exec('set rowcount 0')Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
au_id VarChar(100),
au_fname VarChar(100),
au_lname VarChar(100)
)
declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)
Select * from #tempauth
drop table #tempauth
Friday, May 1, 2009
Tuesday, April 21, 2009
date time format
DateTime.Today - returns the current date.
DateTime.Now - returns the current time.
You can use the following methods to convert a date object to a string.
ToShortDateString()
ToLongDateString()
ToShortTimeString()
ToLongTimeString()
Custom formatting for datetime
The ToString() method of the DateTime class can be used to format the
date & time the way you want.
ToString() method of the DateTime class takes a 'formatter string' as
parameter. Here you can specify the format you want. There are various
formatting patterns you can use to format the date and time.
string formattedDate;
// Format : 07 / 03 / 2004
formattedDate = DateTime.Now.ToString("dd / MM / yyyy");
// Format : 7 / 3 / 2004 (without the preceding zeroes)
formattedDate = DateTime.Now.ToString("d / M / yyyy");
// Format : 07 / Mar / 2004
formattedDate = DateTime.Now.ToString("dd / MMM / yy");
// How to get the name of the day ?
// Format : Wednesday
formattedDate = DateTime.Today.DayOfWeek.ToString();
Formatting Patterns
The following list shows the various patterns that can be used to
convert the datetime object to custom formatted string. The patterns are
case-sensitive; for example, "MM" is different from "mm".
If the custom pattern contains white-space characters or characters
enclosed in single quotation marks, the output string will also contain
those characters. Characters not part of a format pattern or not format
characters are reproduced 'as it is'.
Pre-defined Format Patterns and Description (From the MSDN Documentation)
d The day of the month. Single-digit days will not have a leading
zero.
dd The day of the month. Single-digit days will have a leading zero.
ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd The full name of the day of the week, as defined in DayNames.
M The numeric month. Single-digit months will not have a leading zero.
MM The numeric month. Single-digit months will have a leading zero.
MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM The full name of the month, as defined in MonthNames.
y The year without the century. If the year without the century is less than 10,
the year is displayed with no leading zero.
yy The year without the century. If the year without the century is less than 10,
the year is displayed with a leading zero.
yyyy The year in four digits, including the century.
gg The period or era. This pattern is ignored if the date to be formatted does
not have an associated period or era string.
h The hour in a 12-hour clock. Single-digit hours will not have a leading zero.
hh The hour in a 12-hour clock. Single-digit hours will have a leading zero.
H The hour in a 24-hour clock. Single-digit hours will not have a leading zero.
HH The hour in a 24-hour clock. Single-digit hours will have a leading zero.
m The minute. Single-digit minutes will not have a leading zero.
mm The minute. Single-digit minutes will have a leading zero.
s The second. Single-digit seconds will not have a leading zero.
ss The second. Single-digit seconds will have a leading zero.
f The fraction of a second in single-digit precision. The remaining digits are truncated.
ff The fraction of a second in double-digit precision. The remaining digits are truncated.
fff The fraction of a second in three-digit precision. The remaining digits are truncated.
ffff The fraction of a second in four-digit precision. The remaining digits are truncated.
fffff The fraction of a second in five-digit precision. The remaining digits are truncated.
ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.
fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.
t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any.
tt The AM/PM designator defined in AMDesignator or PMDesignator, if any.
z The time zone offset ("+" or "-" followed by the hour only). Single-digit hours will
not have a leading zero. For example, Pacific Standard Time is "-8".
zz The time zone offset ("+" or "-" followed by the hour only). Single-digit hours
will have a leading zero. For example, Pacific Standard Time is
"-08".
zzz The full time zone offset ("+" or "-" followed by the hour and minutes). Single-digit
hours and minutes will have leading zeros. For example, Pacific Standard Time is "-08:00".
: The default time separator defined in TimeSeparator.
/ The default date separator defined in DateSeparator.
% c - Where c is a format pattern if used alone. The "%" character can be omitted if the
format pattern is combined with literal characters or other format patterns.
\ c - Where c is any character. Displays the character literally. To display the backslash
character, use "\\".
DateTime.Now - returns the current time.
You can use the following methods to convert a date object to a string.
ToShortDateString()
ToLongDateString()
ToShortTimeString()
ToLongTimeString()
Custom formatting for datetime
The ToString() method of the DateTime class can be used to format the
date & time the way you want.
ToString() method of the DateTime class takes a 'formatter string' as
parameter. Here you can specify the format you want. There are various
formatting patterns you can use to format the date and time.
string formattedDate;
// Format : 07 / 03 / 2004
formattedDate = DateTime.Now.ToString("dd / MM / yyyy");
// Format : 7 / 3 / 2004 (without the preceding zeroes)
formattedDate = DateTime.Now.ToString("d / M / yyyy");
// Format : 07 / Mar / 2004
formattedDate = DateTime.Now.ToString("dd / MMM / yy");
// How to get the name of the day ?
// Format : Wednesday
formattedDate = DateTime.Today.DayOfWeek.ToString();
Formatting Patterns
The following list shows the various patterns that can be used to
convert the datetime object to custom formatted string. The patterns are
case-sensitive; for example, "MM" is different from "mm".
If the custom pattern contains white-space characters or characters
enclosed in single quotation marks, the output string will also contain
those characters. Characters not part of a format pattern or not format
characters are reproduced 'as it is'.
Pre-defined Format Patterns and Description (From the MSDN Documentation)
d The day of the month. Single-digit days will not have a leading
zero.
dd The day of the month. Single-digit days will have a leading zero.
ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd The full name of the day of the week, as defined in DayNames.
M The numeric month. Single-digit months will not have a leading zero.
MM The numeric month. Single-digit months will have a leading zero.
MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM The full name of the month, as defined in MonthNames.
y The year without the century. If the year without the century is less than 10,
the year is displayed with no leading zero.
yy The year without the century. If the year without the century is less than 10,
the year is displayed with a leading zero.
yyyy The year in four digits, including the century.
gg The period or era. This pattern is ignored if the date to be formatted does
not have an associated period or era string.
h The hour in a 12-hour clock. Single-digit hours will not have a leading zero.
hh The hour in a 12-hour clock. Single-digit hours will have a leading zero.
H The hour in a 24-hour clock. Single-digit hours will not have a leading zero.
HH The hour in a 24-hour clock. Single-digit hours will have a leading zero.
m The minute. Single-digit minutes will not have a leading zero.
mm The minute. Single-digit minutes will have a leading zero.
s The second. Single-digit seconds will not have a leading zero.
ss The second. Single-digit seconds will have a leading zero.
f The fraction of a second in single-digit precision. The remaining digits are truncated.
ff The fraction of a second in double-digit precision. The remaining digits are truncated.
fff The fraction of a second in three-digit precision. The remaining digits are truncated.
ffff The fraction of a second in four-digit precision. The remaining digits are truncated.
fffff The fraction of a second in five-digit precision. The remaining digits are truncated.
ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.
fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.
t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any.
tt The AM/PM designator defined in AMDesignator or PMDesignator, if any.
z The time zone offset ("+" or "-" followed by the hour only). Single-digit hours will
not have a leading zero. For example, Pacific Standard Time is "-8".
zz The time zone offset ("+" or "-" followed by the hour only). Single-digit hours
will have a leading zero. For example, Pacific Standard Time is
"-08".
zzz The full time zone offset ("+" or "-" followed by the hour and minutes). Single-digit
hours and minutes will have leading zeros. For example, Pacific Standard Time is "-08:00".
: The default time separator defined in TimeSeparator.
/ The default date separator defined in DateSeparator.
% c - Where c is a format pattern if used alone. The "%" character can be omitted if the
format pattern is combined with literal characters or other format patterns.
\ c - Where c is any character. Displays the character literally. To display the backslash
character, use "\\".
Tuesday, April 14, 2009
icon on internet explorer & tag
<link rel="shortcut icon" href="http://www.rsbtechnologies.com/images/favicon.ico" type="image/x-icon" />
Thursday, April 9, 2009
retrive image from database
try
{
con = new SqlConnection(constr);
cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
con.Open();
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (!dr.IsDBNull(1))
{
byte[] photo = (byte[])dr[1];
MemoryStream ms = new MemoryStream(photo);
pictureBox1.Image = Image.FromStream(ms);
}
}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();
MessageBox.Show(ex.Message);
}
{
con = new SqlConnection(constr);
cmd = new SqlCommand("select photopath,Photo from employees where employeeid=14", con);
con.Open();
dr = cmd.ExecuteReader();
while(dr.Read())
{
if (!dr.IsDBNull(1))
{
byte[] photo = (byte[])dr[1];
MemoryStream ms = new MemoryStream(photo);
pictureBox1.Image = Image.FromStream(ms);
}
}
}
catch (Exception ex)
{
dr.Close();
cmd.Dispose();
con.Close();
MessageBox.Show(ex.Message);
}
Thursday, March 19, 2009
Handling exceptions in ASP.NET Ajax
If you ever used ASP.NET Ajax and UpdatePanels you must have seen the annoying alert window that pops up when error occurres during Ajax request. Since WebFu event where Milica and I presented ASP.NET Ajax, many developers asked us how to "kill" this popup window.
I'm going to show you two simple ways to kill the popup window forever. Note: You can find the complete source code in the attachment.
Handling exceptions in ASP.NET Ajax using JavaScript
Let's say we have two buttons inside the UpdatePanel. One that will perform a successful Ajax postback and render the current time, and one that will cause an error. We also have a div (divError) that will display the error message if one occurres. Don't remember that we need a ScriptManager on every Ajax-enabled web page. The code below shows this case.
Oooooops,
Now let's see what is happening on the server. Request that performs a successful Ajax postback will render the time in a label. The other one will throw generic Exception.
protected void Button1_Click(object sender, EventArgs e)
{
throw new Exception("Exception generated on server occurred!");
}
protected void Button2_Click(object sender, EventArgs e)
{
lblDateTime.Text = "The last Ajax PostBack occurred at " +
DateTime.Now.ToString();
}
Now comes the key point. We are going to create three JavaScript functions that will do all the work. First, we'll hook on PageRequestManager class and add a handler that will handle EndRequest event (the code in blue box). Next, we'll proccess the arguments (EndRequestEventArgs class). We'll use get_error() property accessor method to determine if any error occurred. Then we'll get the Exception source message and render it in our . The last and the most important thing that we have to do is to set the ErrorHandled flag to true. By doing this we are preventing the alert widnow to popup.
Handling exceptions using ScriptManager events
We can use some of ScriptManager functionality to handle exceptions in ASP.NET Ajax postbacks. Let's change slightly the example above. We'll define OnAsyncPostBackError handler in the definition of ScriptManager.
On the server, we'll get the Exception message from AsyncPostBackErrorEventArgs class and pass it to the ScriptManager.
protected void ScriptManager1_AsyncPostBackError(object sender, AsyncPostBackErrorEventArgs e)
{
ScriptManager1.AsyncPostBackErrorMessage = e.Exception.Message;
}
All other code will be the same as in the example above.
Conclusion
You saw how to use ScriptManager and JavaScript to handle exceptions that occurres durign Ajax requests. This example is very basic and can be extended. You can use a server user control, you can filter the exceptions uisng get_error().httpStatusCode or something else that is included in your exception handling strategy.
You can find the source code in the attachment.
I'm going to show you two simple ways to kill the popup window forever. Note: You can find the complete source code in the attachment.
Handling exceptions in ASP.NET Ajax using JavaScript
Let's say we have two buttons inside the UpdatePanel. One that will perform a successful Ajax postback and render the current time, and one that will cause an error. We also have a div (divError) that will display the error message if one occurres. Don't remember that we need a ScriptManager on every Ajax-enabled web page. The code below shows this case.
Oooooops,
Now let's see what is happening on the server. Request that performs a successful Ajax postback will render the time in a label. The other one will throw generic Exception.
protected void Button1_Click(object sender, EventArgs e)
{
throw new Exception("Exception generated on server occurred!");
}
protected void Button2_Click(object sender, EventArgs e)
{
lblDateTime.Text = "The last Ajax PostBack occurred at " +
DateTime.Now.ToString();
}
Now comes the key point. We are going to create three JavaScript functions that will do all the work. First, we'll hook on PageRequestManager class and add a handler that will handle EndRequest event (the code in blue box). Next, we'll proccess the arguments (EndRequestEventArgs class). We'll use get_error() property accessor method to determine if any error occurred. Then we'll get the Exception source message and render it in our . The last and the most important thing that we have to do is to set the ErrorHandled flag to true. By doing this we are preventing the alert widnow to popup.
Handling exceptions using ScriptManager events
We can use some of ScriptManager functionality to handle exceptions in ASP.NET Ajax postbacks. Let's change slightly the example above. We'll define OnAsyncPostBackError handler in the definition of ScriptManager.
On the server, we'll get the Exception message from AsyncPostBackErrorEventArgs class and pass it to the ScriptManager.
protected void ScriptManager1_AsyncPostBackError(object sender, AsyncPostBackErrorEventArgs e)
{
ScriptManager1.AsyncPostBackErrorMessage = e.Exception.Message;
}
All other code will be the same as in the example above.
Conclusion
You saw how to use ScriptManager and JavaScript to handle exceptions that occurres durign Ajax requests. This example is very basic and can be extended. You can use a server user control, you can filter the exceptions uisng get_error().httpStatusCode or something else that is included in your exception handling strategy.
You can find the source code in the attachment.
Wednesday, March 18, 2009
rotate image through javascript
var i=5;
function imgrotate()
{
i=i+1;
if(i%5==0)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/delete.jpg";
}
if(i%5==1)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/arr.jpg";
}
if(i%5==2)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/edit.jpg";
}
if(i%5==3)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/print.gif";
}
if(i%5==4)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/warning.gif";
}
window.setTimeout("imgrotate()","1000");
}
window.setTimeout("imgrotate()","1");
function imgrotate()
{
i=i+1;
if(i%5==0)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/delete.jpg";
}
if(i%5==1)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/arr.jpg";
}
if(i%5==2)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/edit.jpg";
}
if(i%5==3)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/print.gif";
}
if(i%5==4)
{
document.getElementById("img1").src="http://localhost:1096/WebSite1/img/warning.gif";
}
window.setTimeout("imgrotate()","1000");
}
window.setTimeout("imgrotate()","1");
Tuesday, February 10, 2009
object tag in html for PDF ,MOVIE,SOUND
http://joliclic.free.fr/html/object-tag/en/
http://joliclic.free.fr/html/object-tag/en/object-video.html#avi
http://joliclic.free.fr/html/object-tag/en/object-application.html#pdf
http://joliclic.free.fr/html/object-tag/en/object-video.html#avi
http://joliclic.free.fr/html/object-tag/en/object-application.html#pdf
Wednesday, February 4, 2009
Tuesday, February 3, 2009
javascript code
http://www.mattkruse.com/javascript/calendarpopup/
http://www.zapatec.com/website/main/products/prod1/
http://www.mattkruse.com/javascript/calendarpopup/combined_compact_source.html
http://www.zapatec.com/website/main/products/prod1/
http://www.mattkruse.com/javascript/calendarpopup/combined_compact_source.html
search on rank Query
SELECT TOP 30 Key_ID, Keyword, SUM(Rank) AS Expr1
FROM (SELECT Key_ID, Keyword, 7 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Nursing %') OR
(' ' + Keyword + ' ' LIKE '%-Nursing %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 5 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Home %') OR
(' ' + Keyword + ' ' LIKE '%-Home %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 3 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Government %') OR
(' ' + Keyword + ' ' LIKE '%-Government %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 1 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Approved %') OR
(' ' + Keyword + ' ' LIKE '%-Approved %')) AND Status = 1) KeywordMaster
GROUP BY Key_ID, Keyword
ORDER BY SUM(Rank) DESC
FROM (SELECT Key_ID, Keyword, 7 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Nursing %') OR
(' ' + Keyword + ' ' LIKE '%-Nursing %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 5 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Home %') OR
(' ' + Keyword + ' ' LIKE '%-Home %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 3 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Government %') OR
(' ' + Keyword + ' ' LIKE '%-Government %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 1 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Approved %') OR
(' ' + Keyword + ' ' LIKE '%-Approved %')) AND Status = 1) KeywordMaster
GROUP BY Key_ID, Keyword
ORDER BY SUM(Rank) DESC
Tuesday, January 27, 2009
imp point Query String
Often you need to pass variable content between your html pages or aspx webforms in context of Asp.Net. For example in first page you collect information about your client, her name and last name and use this information in your second page.
For passing variables content between pages ASP.NET gives us several choices. One choice is using QueryString property of Request Object. When surfing internet you should have seen weird internet address such as one below.
http://www.localhost.com/Webform2.aspx?name=Atilla&lastName=Ozgur
This html addresses use QueryString property to pass values between pages. In this address you send 3 information.
Webform2.aspx this is the page your browser will go.
name=Atilla you send a name variable which is set to Atilla
lastName=Ozgur you send a lastName variable which is set to Ozgur
As you have guessed ? starts your QueryString, and & is used between variables. Building such a query string in Asp.Net is very easy. Our first form will have 2 textboxes and one submit button.
Put this code to your submit button event handler.
Collapse Copy Codeprivate void btnSubmit_Click(object sender, System.EventArgs e)
{
Response.Redirect("Webform2.aspx?Name=" +
this.txtName.Text + "&LastName=" +
this.txtLastName.Text);
} Our first code part builds a query string for your application and send contents of your textboxes to second page. Now how to retrieve this values from second page. Put this code to second page page_load.
Collapse Copy Codeprivate void Page_Load(object sender, System.EventArgs e)
{
this.txtBox1.Text = Request.QueryString["Name"];
this.txtBox2.Text = Request.QueryString["LastName"];
} Request.QueryString is overloaded with a second way. You can also retrieve this values using their position in the querystring. There is a little trick here. If your QueryString is not properly built Asp.Net will give error.
Collapse Copy Codeprivate void Page_Load(object sender,
System.EventArgs e)
{
this.txtBox1.Text = Request.QueryString[0];
this.txtBox2.Text = Request.QueryString[1];
}
Some other ways to reach contents of QueryString.
Collapse Copy Codeforeach( string s in Request.QueryString)
{
Response.Write(Request.QueryString[s]);
}
Or
Collapse Copy Codefor (int i =0;i < Request.QueryString.Count;i++)
{
Response.Write(Request.QueryString[i]);
} Advantages of this approach
It is very easy.
Disadvantages of this approach
QueryString have a max length, If you have to send a lot information this approach does not work.
QueryString is visible in your address part of your browser so you should not use it with sensitive information.
QueryString can not be used to send & and space characters.
If you write this code and try them you will see that you have a problems with space and & characters, e.g. if you need to send a variable which contains & such as "Mark & Spencer". There must be a solution for this problem. If you look to Google’s query string you will see that it contains a lot of %20. This is the solution of our third disadvantage. Replace space with %20 and & with %26 for example.
Collapse Copy Codeprivate void btnSubmit_Click(object sender, System.EventArgs e)
{
string p1 = this.txtName.Text.Replace("&","%26");
p1 = this.txtName.Text.Replace(" ","%20");
string p2 = this.txtLastName.Text.Replace("&","%26");
p2 = this.txtName.Text.Replace(" ","%20");
"WebForm2.aspx?" +
"Name=" + p1 +
"&LastName=" + p2;
Response.Redirect(p2);
} Since this is a such a common problem Asp.Net should have some way to solve. There it is Server.UrlEncode. Server.UrlEncode method changes your query strings to so that they will not create problems.
Collapse Copy Code private void btnSubmit_Click(object sender, System.EventArgs e)
{
Response.Redirect("WebForm2.Aspx?" +
"Name=" + Server.UrlEncode(this.txtName.Text) +
"&LastName=" + Server.UrlEncode(this.txtLastName.Text));
}
Same solution is in Microsoft .Net Quick Start tutorials.
ASP.NET --- Working with Web Controls ---
--- Performing Page Navigation (Scenario 1) ---
--- Performing Page Navigation (Scenario 2) ---
Look at them also if you want to see more example for this technique. Also I advise you to look at Alex Beynenson's article about building QueryString(s).
License
For passing variables content between pages ASP.NET gives us several choices. One choice is using QueryString property of Request Object. When surfing internet you should have seen weird internet address such as one below.
http://www.localhost.com/Webform2.aspx?name=Atilla&lastName=Ozgur
This html addresses use QueryString property to pass values between pages. In this address you send 3 information.
Webform2.aspx this is the page your browser will go.
name=Atilla you send a name variable which is set to Atilla
lastName=Ozgur you send a lastName variable which is set to Ozgur
As you have guessed ? starts your QueryString, and & is used between variables. Building such a query string in Asp.Net is very easy. Our first form will have 2 textboxes and one submit button.
Put this code to your submit button event handler.
Collapse Copy Codeprivate void btnSubmit_Click(object sender, System.EventArgs e)
{
Response.Redirect("Webform2.aspx?Name=" +
this.txtName.Text + "&LastName=" +
this.txtLastName.Text);
} Our first code part builds a query string for your application and send contents of your textboxes to second page. Now how to retrieve this values from second page. Put this code to second page page_load.
Collapse Copy Codeprivate void Page_Load(object sender, System.EventArgs e)
{
this.txtBox1.Text = Request.QueryString["Name"];
this.txtBox2.Text = Request.QueryString["LastName"];
} Request.QueryString is overloaded with a second way. You can also retrieve this values using their position in the querystring. There is a little trick here. If your QueryString is not properly built Asp.Net will give error.
Collapse Copy Codeprivate void Page_Load(object sender,
System.EventArgs e)
{
this.txtBox1.Text = Request.QueryString[0];
this.txtBox2.Text = Request.QueryString[1];
}
Some other ways to reach contents of QueryString.
Collapse Copy Codeforeach( string s in Request.QueryString)
{
Response.Write(Request.QueryString[s]);
}
Or
Collapse Copy Codefor (int i =0;i < Request.QueryString.Count;i++)
{
Response.Write(Request.QueryString[i]);
} Advantages of this approach
It is very easy.
Disadvantages of this approach
QueryString have a max length, If you have to send a lot information this approach does not work.
QueryString is visible in your address part of your browser so you should not use it with sensitive information.
QueryString can not be used to send & and space characters.
If you write this code and try them you will see that you have a problems with space and & characters, e.g. if you need to send a variable which contains & such as "Mark & Spencer". There must be a solution for this problem. If you look to Google’s query string you will see that it contains a lot of %20. This is the solution of our third disadvantage. Replace space with %20 and & with %26 for example.
Collapse Copy Codeprivate void btnSubmit_Click(object sender, System.EventArgs e)
{
string p1 = this.txtName.Text.Replace("&","%26");
p1 = this.txtName.Text.Replace(" ","%20");
string p2 = this.txtLastName.Text.Replace("&","%26");
p2 = this.txtName.Text.Replace(" ","%20");
"WebForm2.aspx?" +
"Name=" + p1 +
"&LastName=" + p2;
Response.Redirect(p2);
} Since this is a such a common problem Asp.Net should have some way to solve. There it is Server.UrlEncode. Server.UrlEncode method changes your query strings to so that they will not create problems.
Collapse Copy Code private void btnSubmit_Click(object sender, System.EventArgs e)
{
Response.Redirect("WebForm2.Aspx?" +
"Name=" + Server.UrlEncode(this.txtName.Text) +
"&LastName=" + Server.UrlEncode(this.txtLastName.Text));
}
Same solution is in Microsoft .Net Quick Start tutorials.
ASP.NET --- Working with Web Controls ---
--- Performing Page Navigation (Scenario 1) ---
--- Performing Page Navigation (Scenario 2) ---
Look at them also if you want to see more example for this technique. Also I advise you to look at Alex Beynenson's article about building QueryString(s).
License
Friday, January 23, 2009
public partial class Form1 : Form
{
string path = "";
public Form1()
{
InitializeComponent();
}
private void button2_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
pictureBox1.ImageLocation= openFileDialog1.FileName;
path = openFileDialog1.FileName;
}
private void button1_Click(object sender, EventArgs e)
{
loaddata();
}
private void loaddata()
{
FileStream st = new FileStream(path, FileMode.Open);
byte[] buffer = new byte[st.Length];
st.Read(buffer, 0, (int)st.Length);
st.Close();
SqlConnection cn = new SqlConnection("User ID=sa;Initial Catalog=test;Data Source=.");
SqlCommand cmd = new SqlCommand("insert into emp values('"+textBox1.Text+"',@photo)",cn);
cmd.Parameters.Add("@photo", buffer);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
private void button3_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("User ID=sa;Initial Catalog=test;Data Source=.");
SqlCommand cmd = new SqlCommand("select * from emp where empno=" + textBox2.Text,cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
textBox1.Text = dr[1].ToString();
byte[] image = (byte[])dr[2];
// System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";
System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream(image));
//System.Drawing.Image _newimage = _image.GetThumbnailImage(_width, _height, null, new System.IntPtr());
//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
pictureBox1.Image = _image;
cn.Close();
}
}
CREATE TABLE [dbo].[emp] (
[empno] [int] IDENTITY (1, 1) NOT NULL ,
[ename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eimage] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
{
string path = "";
public Form1()
{
InitializeComponent();
}
private void button2_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
pictureBox1.ImageLocation= openFileDialog1.FileName;
path = openFileDialog1.FileName;
}
private void button1_Click(object sender, EventArgs e)
{
loaddata();
}
private void loaddata()
{
FileStream st = new FileStream(path, FileMode.Open);
byte[] buffer = new byte[st.Length];
st.Read(buffer, 0, (int)st.Length);
st.Close();
SqlConnection cn = new SqlConnection("User ID=sa;Initial Catalog=test;Data Source=.");
SqlCommand cmd = new SqlCommand("insert into emp values('"+textBox1.Text+"',@photo)",cn);
cmd.Parameters.Add("@photo", buffer);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
private void button3_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("User ID=sa;Initial Catalog=test;Data Source=.");
SqlCommand cmd = new SqlCommand("select * from emp where empno=" + textBox2.Text,cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
textBox1.Text = dr[1].ToString();
byte[] image = (byte[])dr[2];
// System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";
System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream(image));
//System.Drawing.Image _newimage = _image.GetThumbnailImage(_width, _height, null, new System.IntPtr());
//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
pictureBox1.Image = _image;
cn.Close();
}
}
CREATE TABLE [dbo].[emp] (
[empno] [int] IDENTITY (1, 1) NOT NULL ,
[ename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eimage] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Wednesday, January 21, 2009
read write text file in asp.net
************************read text file*************************
Dim fp As StreamReader
Try
fp = File.OpenText(Server.MapPath(".\Upload\") & "test.txt")
txtMyFile.Text = fp.ReadToEnd()
lblStatus.Text = "File Succesfully Read!"
fp.Close()
*********************create text file*****************************
Dim fp As StreamWriter
Try
fp = File.CreateText(Server.MapPath(".\Upload\") & "test.txt")
fp.WriteLine(txtMyFile.Text)
lblStatus.Text = "File Succesfully created!"
fp.Close()
Catch err As Exception
lblStatus.Text = "File Creation failed. Reason is as follows
************************write doc file*******************************
private void WriteToWordFile()
{
// Any folder
string path = @"C:\ServerFolder\MyWordFile.doc";
string text = TextBox2.Text;
// Put it in try-catch finally :)
FileStream fs = File.Create(path);
fs.Close();
StreamWriter sw = new StreamWriter(path);
sw.Write(text);
sw.Close();
}
****************************write text into file*****************************
Writing to a Text File
Listing 1: Writing Text Data to a File: TextFileWriter.cs
using System;
using System.IO;
namespace csharp_station.howto
{
class TextFileWriter
{
static void Main(string[] args)
{
// create a writer and open the file
TextWriter tw = new StreamWriter("date.txt");
// write a line of text to the file
tw.WriteLine(DateTime.Now);
// close the stream
tw.Close();
}
}
}
****************************read text from file*****************************
using System;
using System.IO;
namespace csharp_station.howto
{
class TextFileReader
{
static void Main(string[] args)
{
// create reader & open file
Textreader tr = new StreamReader("date.txt");
// read a line of text
Console.WriteLine(tr.ReadLine());
// close the stream
tr.Close();
}
}
}
Dim fp As StreamReader
Try
fp = File.OpenText(Server.MapPath(".\Upload\") & "test.txt")
txtMyFile.Text = fp.ReadToEnd()
lblStatus.Text = "File Succesfully Read!"
fp.Close()
*********************create text file*****************************
Dim fp As StreamWriter
Try
fp = File.CreateText(Server.MapPath(".\Upload\") & "test.txt")
fp.WriteLine(txtMyFile.Text)
lblStatus.Text = "File Succesfully created!"
fp.Close()
Catch err As Exception
lblStatus.Text = "File Creation failed. Reason is as follows
************************write doc file*******************************
private void WriteToWordFile()
{
// Any folder
string path = @"C:\ServerFolder\MyWordFile.doc";
string text = TextBox2.Text;
// Put it in try-catch finally :)
FileStream fs = File.Create(path);
fs.Close();
StreamWriter sw = new StreamWriter(path);
sw.Write(text);
sw.Close();
}
****************************write text into file*****************************
Writing to a Text File
Listing 1: Writing Text Data to a File: TextFileWriter.cs
using System;
using System.IO;
namespace csharp_station.howto
{
class TextFileWriter
{
static void Main(string[] args)
{
// create a writer and open the file
TextWriter tw = new StreamWriter("date.txt");
// write a line of text to the file
tw.WriteLine(DateTime.Now);
// close the stream
tw.Close();
}
}
}
****************************read text from file*****************************
using System;
using System.IO;
namespace csharp_station.howto
{
class TextFileReader
{
static void Main(string[] args)
{
// create reader & open file
Textreader tr = new StreamReader("date.txt");
// read a line of text
Console.WriteLine(tr.ReadLine());
// close the stream
tr.Close();
}
}
}
Monday, January 19, 2009
SQL Server: Best way to Update row if exists, Insert if not
http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx
SQL Server: Best way to Update row if exists, Insert if not
http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx
Subscribe to:
Posts (Atom)