Tech Xprs

Thursday 9 May 2013

Best Way of Passing Parameter To Dynamic SQL Query In SQL Server

Monday 11 March 2013

Calling Server Side Method Using jQuery/Ajax

Place below code in .aspx page.

In the below code url indicates path of your method, here my method is in Default.aspx page.

data indicates parameters for the server side web method.

Place below code in aspx.cs page.

If we click on submit button the above method will be called and returns a Date time string appended with the text which we have entered in the text box.

Friday 8 March 2013

Getting IP Address of Client Machine in ASP .Net using C#

Wednesday 6 March 2013

Export DataTable to Excel in ASP .Net using C#

public static void ExporttoExcel(DataTable table, string fileName)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
string style = @"<style> .text { mso-number-format:\@; } </style>";
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write(style);
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
int columnscount = table.Columns.Count;

for (int j = 0; j < columnscount; j++)
{ //write in new column
HttpContext.Current.Response.Write("<Td>");
//Get column headers and make it as bold in excel columns
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(table.Columns[j].ToString());
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td class='text'>");

HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}

HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");

HttpContext.Current.Response.Flush();
HttpContext.Current.Response.SuppressContent = true;
//HttpContext.Current.Response.End();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}

Friday 15 February 2013

Splitting of comma separated string in sql server

Friday 14 September 2012

Limit Number of characters in Asp .Net multi line Text Box using Jquery.

Place below code in .aspx page.

Place below Jquery script in the same .aspx page.

Tuesday 4 September 2012

Prevent Cut, Copy and Paste in asp .net text box using Jquery.

Need to include Jquery plug in as shown in the code.(You will find this plug in Scripts folder, if you select new web site or new web project from VS 2010.
Place below code in .aspx page and run the page.
Try to paste the text Or copy the text Or cut the text in the text box, then it will raise a message you can not do copy or cut or paste in this text box.

Saturday 4 August 2012

Getting mime type from system Registry Editor by using c# .Net

Friday 3 August 2012

Sample web app that uses a WCF Service

A sample web application that consumes the my previous sample wcf service.

Open visual studio File --> New Web Site and name it as yours choice. It will create empty web site like this.

Right click on solution and select add new item.

Select web form and name it as CalculatorClient

Add this code in .aspx page.

Generate the button click events for above four buttons.

Now add the service reference to the web app as shown.

It leads you to a window which asking address for web reference.

The address is nothing but our previous wcf service. Now run wcf service application i.e. my previous post then you will get a window with methods of service as shown then right click and copy the address as shown.

Then paste that address in address bar and name it as CalculatorService then click on Go next Ok as shown.

Then service will be added to the solution as shown.

Then following lines of code automatically added to the web.config automatically the code as shown.It contains bindings and end points for the service.

Then write the following code in aspx.cs page.

Then run the application you will check all the methods of the service by using this app.

Note if the wcf service application stopped then our web app throws an exception.

If we click on stop and then use web application then it throws a error like this.

Note: Service has to be run continuously other wise we will get above error in all applications which uses the service.

Monday 30 July 2012

Sample WCF Service

Open VS10 and select new project.

Select WCF Service Application and name it as SampleCalculator

Then solution explorer will created with following default files like IService1.cs and Service1.svc

Delete those files and right click on solution explorer--> Add New Item--> Select Wcf Service template and name it as Calculator.

Then two files created in Solution Explorer i.e. ICalculator.cs(ServiceContract) and Calculator.svc is a service file which is having inheritance from ICalculator.cs

Then write the following in ICalculator.cs(ServiceContract)

Then go to Calculator.svc file and implement ICalculator interface as shown.

This will create following method definitions in Calculator.svc file.

Then modify method definitions as shown.

Then our WCF Service is ready to run, press F5 or Run button this will lead you to the following screen.

Then our service ready to check how its working, all the methods we wrote in service are visible here as shown.

Then double click on any method which you want test. I'm going for Add() method. After double click on Add() method we will get method test screen as shown.

You can check service by giving values of "a" and "b" in [Request Pane] and click on invoke you will get return value in [Response Pane].

This is WCF Service. In my next post i will try to explain how to consume this Service by using a Web App.

Tuesday 24 July 2012

Date Add functions using Sql Server

Date functions using Sql Server 2008

Difference between two dates using TimeSpan class in c# .net

Send Email from SQL Server Express Using a CLR Stored Procedure.

Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

In this CLR stored procedure we are going to pass in a few parameters such as:
recipients - list of people to receive the email
subject - subject line of the email
from - who the email is from
body - the body of the email

This is just the basic information to send an email. You could add as many parameters as you want to extend the functionality, but I kept it simple to illustrate how easy this is to do.

Step 1 - CLR code

The first thing we need to do is to write the CLR code. This could be written in either C#.NET or VB.NET. In this example we are using VB.NET.

The following code has a Class (StoredProcedure) and a Stored Procedure (spSendMail). The stored procedure takes four parameters:

recipients - list of people to receive the email

subject - subject line of the email

from - who the email is from

body - the body of the email

Before you save the code you will need to change these two lines to include the data for your SMTP server and your account you will use to send emails:

mySmtpClient = New SmtpClient("mail.yourservername.com") -- this should be your SMTP server
mySmtpClient.Credentials = New NetworkCredential("email@domain", "yourPassword") -- this should the email address and password to authenticate in to your email server

After you have made these two adjustments save the code below in a file called: C:\SendEmail.vb

Imports System.Net
Imports System.Net.Mail

Public Class StoredProcedure
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub spSendMail(ByVal recipients As String, ByVal subject As String, ByVal from As String, ByVal body As String)
Dim mySmtpClient As SmtpClient

Using myMessage As New MailMessage(from, recipients)

myMessage.Subject = subject
myMessage.Body = body

myMessage.IsBodyHtml = True

mySmtpClient = New SmtpClient("mail.yourservername.com")
mySmtpClient.Credentials = New NetworkCredential("email@domain", "yourPassword")
mySmtpClient.Send(myMessage)

End Using
End Sub
End Class

Step 2 - Compile CLR Code

In order to use this code, the code has to be compiled.

The following command is run from a command line to compile the CLR code using the vbc.exe application. This is found in the .NET 2.0 framework directory. This may be different on your server or desktop. Also, this code should be compiled on the machine where the code will run.

So from a command line run a command such as the following:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\SendEmail.vb

The code should now be compiled in a file called: C:\SendEmail.dll

Step 3 - Create Assembly and Stored Procedure

After the code has been compiled you need to create the assembly and the stored procedure within SQL Server. To do this, run these commands in the database where you want to use the function. For this example I am using the msdb database.

The assembly ties an internal object to the external DLL that was created and the stored procedure is similar to a normal SQL Server stored procedure.

For the stored procedure you will see the components that are referenced [SendEmail].[SendEmail.StoredProcedure].[spSendMail]

USE msdb
GO

CREATE ASSEMBLY SendEmail FROM 'C:\SendEmail.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE PROCEDURE [dbo].[spSendMail]
@recipients [nvarchar](4000),
@subject [nvarchar](4000),
@from [nvarchar](4000),
@body [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SendEmail].[SendEmail.StoredProcedure].[spSendMail]

If you get error messages when trying to compile the code you may need to alter the database using the following command and then try again to create the assembly and the stored procedure.

ALTER DATABASE msdb SET trustworthy ON

Step 4 - Test It

To test the stored procedure, run the following statement based on the values you want to pass.
In this example we are sending an email to "tips@mssqltips.com".

EXEC spSendMail @recipients = 'tips@mssqltips.com', @subject = 'Email from SQL Express',
@from = 'tips@mssqltips.com', @body = 'This is a test email from SQL Server'

Step 5 - Cleanup

If you want to remove these objects follow these steps. Delete the vb file and dll. And run the sql statements given below.

USE msdb

DROP PROCEDURE dbo.spSendMail

DROP ASSEMBLY SendEmail

Note: I got this article from one of the site, but i was worked on this and its working fine.

Tech Xprs is the blogger site started by Lakshmi narasimha rao Mandadapu, on 23-July-2012. The main moto of this site to provide help and support on various Microsoft technologies.

Pages