Return multiple result sets using IMultipleResults in linq c#

As we know that LINQ introduced a new feature which lets us to get multiple result sets from the stored procedure. But there is one important which all should know about IMultipleResults. The order of the GetResult's should be same as the order of the select statements in the sproc. This must be followed in order to avoid unexpected errors. I spent many hours on this to get rid of this problem.

For better understanding please go through the following

The following tsql code shows how the spoc which returns multiple result sets look like

CREATE PROCEDURE GetMultipleResults
@SomeID int
AS
BEGIN
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID
END

The below code shows how to get the IMultipleResult object by calling the above sproc using LINQ

[Function(Name = "dbo.SPROCName")]
[ResultType(typeof(ResultSet1))]
[ResultType(typeof(ResultSet2))]
[ResultType(typeof(ResultSet3))]
[ResultType(typeof(ResultSet4))]
public IMultipleResults SomeMethod([Parameter(DbType = "INT")] int? SomeID
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SomeID);
return ((IMultipleResults)(result.ReturnValue));
}


The below code shows how to read the result set from the object of IMultipleResult type

public void SomeOtherMethod(int SomeID)
{
DataContext1 context = new DataContext1 (dbConnString);
IMultipleResults results = context.SomeMethod(SomeID);

ResultSet1 resultSet1= results.GetResult().FirstOrDefault();
IEnumerable resultSet2 = results.GetResult();
IEnumerable resultSet3 = results.GetResult();
ResultSet4 resultSet4= results.GetResult().FirstOrDefault();
}

If you observe the above code you can notice that the method is reading individual result sets from the IMultipleResult object in the same sequence as the sproc returns the results. This is very important that the order of the IMultipleResults.GetResult() statements for the result sets should be same as the order of the select statements in sproc. If the order is different then you will get runtime errors or invalid objects and many more issues.

Impose maxlength on textarea controls to avoid "String or binary data would be truncated" error

As we all know that you can restrict a text box to allow limited number of characters using maxlength attribute as shown below

<input id="tbName" maxlength="20" type="textbox" width="100"/>

(or)

<asp:TextBox ID="tbName" MaxLength="20" Width="100px" runat="server" />

It is very simple and easy to impose the maximum length constraint for text boxes.

But this is not the case for textarea /multiline text box controls. There is no straight way to do this. We don't have the maxlength attribute for html textarea control. This post explains how to add that support.

Please check the below javascript function imposeMaxLength(field,maxChars) which takes 2 parameters:
- field which is the textarea control itself
- maxChars to tell the function how many charaters to be allowed.

<script type="text/javascript">
function imposeMaxLength(field,maxChars)
{
if(field.value.length > maxChars)
{
field.value = field.value.substring( 0, maxChars );
alert( 'Textarea value can only be ' +maxChars + ' characters in length.' );
return false;
}
}
</script>

The function needs to be called in onkeypress event of the textarea control. Please see the below code to know how we should call the method imposeMaxLength in onkeypress event of the textareancontrol

<textarea id="tbDescription" onkeypress="imposeMaxLength(this, 512)" />

(or)

<asp:TextBox ID="tbDescription" TexMode="MultiLine" runat="server" onkeypress="imposeMaxLength(this, 512)" />

The above code renders a text area control and restricts the control not to allow more than 512 characters.

This is very useful when you have database fields with limited string length. We have faced many pronlems as the user was entering bulky data and the field doesn't support and hence throws the exception. After implementing this we were comfortable and there were no exceptions like String or binary data would be truncated.

How to sort list of objects in C#

The following code helps in sorting list of objects based on some property’s value.

public class Person
{
public int age;
public string name;

public Person(int age, string name)
{
this.age = age;
this.name = name;
}
}

List people = new List();

people.Add(new Person(50, “Kiran”));
people.Add(new Person(30, “Raghava”));
people.Add(new Person(26, “Andrew”));
people.Add(new Person(24, “Praveen”));
people.Add(new Person(5, “Sudhir”));
people.Add(new Person(6, “Prudhvi”));

Console.WriteLine(”Unsorted list”);
people.ForEach(delegate(Person p) { Console.WriteLine(String.Format(”{0} {1}”, p.age, p.name)); });

Console.WriteLine(”Sorted list, by name”);
people.Sort(delegate(Person p1, Person p2) { return p1.name.CompareTo(p2.name); });
people.ForEach(delegate(Person p) { Console.WriteLine(String.Format(”{0} {1}”, p.age, p.name)); });

people.Sort(delegate(Person p1, Person p2) { return p1.age.CompareTo(p2.age); });
Console.WriteLine(”Sorted list, by age”);
people.ForEach(delegate(Person p) { Console.WriteLine(String.Format(”{0} {1}”, p.age, p.name)); });

Learn How HyperText Transfer Protocol works

This post describes the complete HTTP protocol how it works.

HTTP stands for Hypertext Transfer Protocol. It’s the network protocol used to deliver virtually all files and other data (collectively called resources) on the World Wide Web, whether they’re HTML files, image files, query results, or anything else. Usually, HTTP takes place through TCP/IP sockets.

A browser is an HTTP client because it sends requests to an HTTP server (Web server), which then sends responses back to the client. The standard (and default) port for HTTP servers to listen on is 80, though they can use any port.

HTTP is used to transmit resources, not just files. A resource is some chunk of information that can be identified by a URL (it’s the R in URL). The most common kind of resource is a file, but a resource may also be a dynamically-generated query result, the output of a .aspx file, a document that is available in several languages, or something else.

Structure of HTTP Transactions:

Like most network protocols, HTTP uses the client-server model: An HTTP client opens a connection and sends a request message to an HTTP server; the server then returns a response message, usually containing the resource that was requested. Once the connection is opened It remains open in order to process further requests.

The format of the request and response messages is similar, and English-oriented. Both kinds of messages consist of:

• an initial line,
• zero or more header lines,
• a blank line (i.e. a CRLF – new line by itself), and
• an optional message body (e.g. a file, or query data, or query output).

Put another way, the format of an HTTP message is:

Header1: value1

Header2: value2

Header3: value3

it can be many lines long, or even binary data $&*%@!^$@>

Initial lines and headers should end in CRLF, though you should gracefully handle lines ending in just LF. (More exactly, CR and LF here mean ASCII values 13 and 10, even though some platforms may use different characters.)

Initial Request Line:

The initial line is different for the request than for the response. A request line has three parts, separated by spaces: a method name, the local path of the requested resource, and the version of HTTP being used. A typical request line is:

GET /path/to/file/index.html HTTP/1.0

Notes:

• GET is the most common HTTP method; it says “give me this resource”. Other methods include POST and HEAD. Method names are always uppercase.
• The path is the part of the URL after the host name, also called the request URI (a URI is like a URL, but more general).
• The HTTP version always takes the form “HTTP/x.x“, uppercase.

Initial Response Line (Status Line):

The initial response line, called the status line, also has three parts separated by spaces: the HTTP version, a response status code that gives the result of the request, and an English reason phrase describing the status code. Typical status lines are:

HTTP/1.0 200 OK

or

HTTP/1.0 404 Not Found

Notes:

• The HTTP version is in the same format as in the request line, “HTTP/x.x“.
• The status code is meant to be computer-readable; the reason phrase is meant to be human-readable, and may vary.
• The status code is a three-digit integer, and the first digit identifies the general category of response:
  • 1xx indicates an informational message only
  • 2xx indicates success of some kind
  • 3xx redirects the client to another URL
  • 4xx indicates an error on the client’s part
  • 5xx indicates an error on the server’s part

The most common status codes are:

200 OK

The request succeeded, and the resulting resource (e.g. file or script output) is returned in the message body.

404 Not Found. The requested resource doesn’t exist.

301 Moved Permanently
302 Moved Temporarily
303 See Other (HTTP 1.1 only)

The resource has moved to another URL (given by the Location: response header), and should be automatically retrieved by the client. This is often used by a CGI script to redirect the browser to an existing file.

500 Server Error

An unexpected server error. The most common cause is a server-side script that has bad syntax, fails, or otherwise can’t run correctly.

Header Lines:

Header lines provide information about the request or response, or about the object sent in the message body.

The header lines are in the usual text header format, which is: one line per header, of the form “Header-Name: value“, ending with CRLF. . Details about RFC 822 header lines:

• As noted above, they should end in CRLF, but you should handle LF correctly.
• The header name is not case-sensitive (though the value may be).
• Any number of spaces or tabs may be between the “:” and the value.
• Header lines beginning with space or tab are actually part of the previous header line, folded into multiple lines for easy reading.

Thus, the following two headers are equivalent:

Header1: some-long-value-1a, some-long-value-1b

HEADER1: some-long-value-1a,

some-long-value-1b

HTTP 1.0 defines 16 headers, though none are required. HTTP 1.1 defines 46 headers, and one (Host:) is required in requests. For Net-politeness, consider including these headers in your requests:

• The From: header gives the email address of whoever’s making the request, or running the program doing so. (This must be user-configurable, for privacy concerns.)
• The User-Agent: header identifies the program that’s making the request, in the form “Program-name/x.xx“, where x.xx is the (mostly) alphanumeric version of the program. For example, Netscape 3.0 sends the header “User-agent: Mozilla/3.0Gold“.

These headers help webmasters troubleshoot problems. They also reveal information about the user. When you decide which headers to include, you must balance the webmasters’ logging needs against your users’ needs for privacy.

If you’re writing servers, consider including these headers in your responses:

• The Server: header is analogous to the User-Agent: header: it identifies the server software in the form “Program-name/x.xx“. For example, one beta version of Apache’s server returns “Server: Apache/1.2b3-dev“.
• The Last-Modified: header gives the modification date of the resource that’s being returned. It’s used in caching and other bandwidth-saving activities. Use Greenwich Mean Time, in the format
• Last-Modified: Fri, 31 Dec 1999 23:59:59 GMT

The Message Body:

An HTTP message may have a body of data sent after the header lines. In a response, this is where the requested resource is returned to the client (the most common use of the message body), or perhaps explanatory text if there’s an error. In a request, this is where user-entered data or uploaded files are sent to the server.

If an HTTP message includes a body, there are usually header lines in the message that describe the body. In particular,

• The Content-Type: header gives the MIME-type of the data in the body, such as text/html or image/gif.
• The Content-Length: header gives the number of bytes in the body.

Methods:

GET :

The GET method is used when the client wants to retrieve a document from the server.

POST:

A POST request is used to send data to the server to be processed in some way, like by an aspx file. A POST request is different from a GET request in the following ways:

• There’s a block of data sent with the request, in the message body. There are usually extra headers to describe this message body, like Content-Type: and Content-Length:.
• The request URI is not a resource to retrieve; it’s usually a program to handle the data you’re sending.
• The HTTP response is normally program output, not a static file.

The most common use of POST, by far, is to submit HTML form data to CGI scripts. In this case, the Content-Type: header is usually application/x-www-form-urlencoded, and the Content-Length: header gives the length of the URL-encoded form data. The CGI script receives the message body through STDIN, and decodes it. Here’s a typical form submission, using POST:

POST /path/script.cgi HTTP/1.0

From: frog@jmarshall.com

User-Agent: HTTPTool/1.0

Content-Type: application/x-www-form-urlencoded

Content-Length: 32

You can use a POST request to send whatever data you want, not just form submissions. Just make sure the sender and the receiving program agree on the format.

The GET method can also be used to submit forms. The form data is URL-encoded and appended to the request URI.

Schedule daily backup for sql server 2005 database using sql agent

Many of us come across a need to schedule periodic database backups for sql server. But many of us don't realize that this feature is readily available in sql server management studio and we purchase some 3rd party tools to create such scheduled tasks. I recommend to use Sql Server Management Studio to do such jobs rather than spending on unnecessary tools. But remember, this feature is not available in express releases of sql server. If you are using sql server express edition please ignore this post. And this post also assumes that you have SSIS (Sql Server Integration Services) installed and running on your machine. SSIS is the prerequisite to follow the below instructions to schedule the database periodic backups.
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.

1. Open sql server 2005 Management Studio and connect a server
2. Expand the tree (left navigation) ”Management->Maintenance Plans”
3. Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.

4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following

6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.

7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.
8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.

9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.

10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish step.
12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.
13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).

The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

Every time I install sql server, in the initial days I used to face problems in logging on by sql server authentication mode.

These were the steps I used to follow to create new login in order to logon using sql server authentication mode.

1. Log on to the server using windows authentication mode and create some login in the section of Sqlserver->Security->Logins->(Right click)NewLogin.

2. Restart the sql server and disconnect it.

3. This time connect to the server using the sql server authentication mode by entering new login credentials.

But it never used to work. The error I receive was “The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)”.

After doing a long research on this I found the solution to this. To make sql server authentication work we need to configure the sql server to use Sql server and windows authentication mode. The following steps will solve the problem.

1. Right click on the root node of the sql server and select properties in the context menu.

2. That will open a popup window which will have server level settings.

3. Click on security tab it will show the view as in the following figure. There you have to make sure you have “Sql server and Windows authentication mode” is selected.

4. Press OK and restart the sql server again.

5. This time the sql server authentication mode using the new login credential will work.

Note: You have to make sure your login has server role as “SysAdmin” in order to logon.

How to invoke a process/application from parent application using C#

You can start a new process/application using the following C# code line.

System.Diagnostics.Process.Start(filePATH);

filePATH is the relative path of the application executable.

If you give a website url in filePATH then it will be opened in a default web browser.

Ex: System.Diagnostics.Process.Start(”http://rampgroup.com”); will cause to open the website home page in a default web browser.

How to access Remote sql server’s database table

Recently I got a requirement to migrate data from other sql server’s database to my local sql server’s database. Then I tried querying like this.

select * from ServerName.DataBaseName.dbo.TableName

When I ran the query “select * from FilterDB011.FilterWebDB.dbo.SiteUser” I got an error saying

“Could not find server ‘FilterDB011′ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.”

Then I ran the query ” execute sp_addlinkedserver FilterDB011″ as the error message suggested.

Then It worked fine.

Now I am able to access the tables and databases from that remote database server.

The syntax to add a remote sql server to sys.servers is:

execute sp_addlinkedserver sqlserverName

The syntax to access a table in remote sql server is:

SeverName.DatabaseName.dbo.TableName

Exception handling in Sql server 2000

When I was working on a project, I had written a sproc which runs in sql server 2005. I had used Try Catch blocks to handle the exceptions and roll back the transaction for any exception. My script was working fine with sql-2005. But when I had to deploy on production, I noticed that we had sql server 2000 on which our legacy database was sitting. When I ran my script it was failing to create the sproc due to try catch blocks. After doing a while research on it I found that sql server 2000 deals with exceptions in different way.

When any exception is raised then @@ERROR will be set to a non zero value. If you want to check whether exception has araised you should check

@@ERROR <>0 -> Exception occurred

@@ERROR =0 -> No Exception

To handle exceptions in sql server 2000 we need to check the condition @@ERROR <>0 for every insert/update/delete statement and based on that we need to roll back the transaction if used.

Example:

BEGIN TRAN

DELETE EMP WHERE EmployeeType = ‘ProjectManager’

IF (@@ERROR <>0)

GOTO HandleError;

UPDATE EMP SET Salary = Salary * 1.30, EmployeeType = ‘ProjectManager’ WHERE EmployeeType = ‘TeamLead’

IF (@@ERROR <>0)

GOTO HandleError;

COMMIT TRANS

RETURN;

HandleError:

ROLLBACK TRAN

RETURN;

Setting Execution Order of Triggers in Sql Server

I had multiple triggers defined for update on a table. I got a requirement to force the execution for the triggers to be done in a defined order. I had searched some websites and found that it is possible using the built in sproc sp_settriggerorder

sp_settriggerorder [@triggername = ] ‘triggername‘
, [@order = ] ‘value‘
, [@stmttype = ] ‘statement_type‘

Argument

[@triggername = ] ‘triggername‘

Is the name of the trigger whose order will be set or changed. triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure will return an error.

[@order = ] ‘value‘

Is the setting for the new trigger order. value is varchar(10) and it can be any of the following values.

Important The First and Last triggers must be two different triggers.

Value	Description
First	Trigger will be fired first.
Last	Trigger will be fired last.
None	Trigger will be fired in undefined order.

[@stmttype = ] ‘statement_type‘

Specifies which SQL statement fires the trigger. statement_type is varchar(10) and can be INSERT, UPDATE, or DELETE. A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. SQL Server will return an error if TR1, which has been defined only as an INSERT trigger, is set as a First (or Last) trigger for an UPDATE statement. For more information, see the Remarks section.

solving error #2104 “Could not download the Silverlight application.”

This error generally occurs when the silver light file types are not registered as MIME types in the web server. To fix this error please follow the following guide lines.
1. Go to
inetmgr — > select ur silverlight applciation –>Right click Properties –> HTTP Headers — MIME Map –> File Types — New Types –>

2. Add one by one the below mentioned:

Associated Extension Content Type(MIME)

.xaml application/xaml+xml
.xap application/x-silverlight-app
.manifest application/manifest
.application application/x-ms-application
.xbap application/x-ms-xbap
.deploy application/octet-stream
.xps application/vnd.ms-xpsdocument
3. Click OK and close the dialog.

Asp.Net Server.UrlEncode doesn't work if performed once

Hi,

I have found one interesting thing about UrlEncode when I was working on a project.

In many page requests we were passing sensitive query string parameters. As these were sensitive we were encrypting them using some encrypting algorithm. That algorithm was producing non numeric characters (Ex:'/', '&', '=','?',...) which are recognized by the web server. This was leading to conflicts by the web server and the application was breaking.

To Avoid this we started performing Server.UrlEncode on the encrypted string and using it in any url or querystring. On landing page we were doing Server.Decode and then decrypting the resulted string. Please see the blow code for better understanding.

Server.UrlEncode(Utility.Encrypt("sensitive data"));
Utility.Decrypt(Server.UrlDecode("url encoded and encrypted sensitive data"));

But this didn't help us. Even though we did url encoding we were not seeing the url encoded url in the browser. And due to this the application was breaking. After this we did a long research and tried all combinations but nothing helped.

Finally we got the solution. The solution is we need to perform the url encoding twice and url decoding once. That's how it was working perfectly. We changed the code accordingly in the entire project and we didn't see any issue. The final fix for this problem is shown below.

Server.UrlEncode(Server.UrlEncode(Utility.Encrypt("sensitive data"))); //Encode twice
Utility.Decrypt(Server.UrlDecode("url encoded and encrypted sensitive data")); //Decode once


Example:

....
.....
int CustomerID;
string CustomerIDEncrypyted = Utility.Encrypt(CustomerID.ToString());
string CustomerIDEncoded = Server.UrlEncode(Server.UrlEncode(CustomerIDEncrypyted));
string url = string.Format("Customer.aspx?CID={0}", CustomerIDEncoded );
anchorNext.Href = url;
.....
.....

//PageLoad event code in Customer.aspx.cs page

protected void Page_Load(object sender, EventArgs e)
{
string CustomerIDEncoded = Request.QueryString["CID"];
string CustomerIDDecoded = Server.Decode(CustomerIDEncoded);
string CustomerIDDecrypted = Utility.Decrypt(CustomerIDDecoded);
.....
.....
}

Search for keywords in stored procedures

When I was working on a project I got a requirement to know how certain columns/tables are populated, which sprocs are populating them. Then I googled for a while about it and got the link about information_schema.routines. information_schema.routines is a system view which returns all the sprocs defined in the database.

You can have a look at the below example to know the usage of it. The below query shows the list of sprocs whose definition contains the word "customers". In general terms I am using this query to know what all sprocs are operating on the table "customers"

use northwind
go
select routine_name,routine_definition from information_schema.routines where routine_definition like '%customers%'

This is very useful when there is a huge database with many number of tables and many sprocs.

It was useful to me as our project was very complex and huge.