ASP.NET offers a simple way to provide an alternative to the default error page. You may create a custom error page and tell Internet Information Services to display it instead of the default. The web.config file with the customErrors element contains the setting:
This tells the system to display the custom error page (errors/ErrorPage.aspx) instead of the default error page. Since errors may occur at any place within the application, the error page should be available to all users (security access of public). I usually create a public errors directory and place the appropriate resources within it.
The defaultRedirect attribute contains the path to the error page; in addition, the mode attribute tells the system if and how error handling is enabled. Here are the possible values of the attribute:
On: All errors utilize the custom error page. Off: Custom error pages aren't used. RemoteOnly: The custom error pages are used for clients other than the local machine. This is good for debugging a production application. It allows you to sit at the server and view errors while clients receive the custom error pages. A custom error page may be a simple HTML file or an ASPX file--it depends upon your requirements. For example, you may use the following HTML as a custom error page:
Handling specific errors
The customErrors section of the web.config file allows you to handle individual errors differently. Errors are distinguished by their HTTP error codes.
Let's look at a few of the more common errors:
400--Bad request--The request could not be understood by the server. 401--Unauthorized--The request requires authentication. 404--Not found--The server has not found anything matching the request. 500--Internal server error--The server encountered an unexpected condition that prevented it from fulfilling the request. A complete HTTP status code listing is available online. You may expand the customErrors section of the web.config file to handle specific error codes. You can achieve the expansion by adding error elements under the customErrors element. The following sample shows how you may accomplish this:
But how to get what sort of error has come due to which custom error page has been displayed.I mean how would the developer understands what error has come.Its OK,custom error page will not give that error page which is displayed in ASP.NET. We just can display some messages to the user through custom error page ,but I think it can hide errors from user only and yes,there is a way,developer can get understand these errors.Plz enhance this custom error topic with all these explaination like How the developer understands what errors has come when custom error message is displayed.
Calling Stored Procedure Hope everyone have used SQLCommand / OLEDB Command objects in .NET. Here we can call stored procedures in two different forms, one without using parameter objects which is not recommended for conventional development environments, the other one is the familiar model of using Parameters.
In the first method you can call the procedure using Exec command followed by the procedure name and the list of parameters, which doesn’t need any parameters.
Example: Dim SQLCon As New SqlClient.SqlConnection SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;" SQLCon.Open()
The second most conventional method of calling stored procedures is to use the parameter objects and get the return values using them. In this method we need to set the “SQLCommandType” to “StoredProcedure” remember you need to set this explicitly as the the default type for SQLCommand is SQLQuery”.
Here is an example to call a simple stored procedure.
Example - I (A Stored Procedure Returns Single Value)
In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly
'Set up Connection object and Connection String for a SQL Client Dim SQLCon As New SqlClient.SqlConnection SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;" SQLCon.Open()
SQLCmd.CommandText = "SelectRecords" ' Stored Procedure to Call SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type SQLCmd.Connection = SQLCon 'Active Connection
There is no real voodoo magic to creating a connection to a SQL Server assuming it is properly setup, which I am not going to go into in this article, in fact .NET has made working with SQL quite easy. First step is add the SQL Client namespace:
Collapse using System.Data.SqlClient; Then we create a SqlConnection and specifying the connection string.
Collapse SqlConnection myConnection = new SqlConnection("user id=username;" + "password=password;server=serverurl;" + "Trusted_Connection=yes;" + "database=database; " + "connection timeout=30"); Note: line break in connection string is for formatting purposes only SqlConnection.ConnectionString
The connection string is simply a compilation of options and values to specify how and what to connect to. Upon investigating the Visual Studio .NET help files I discovered that several fields had multiple names that worked the same, like Password and Pwd work interchangeably. I have not included all of the options for SqlConnection.ConnectionString at this time. As I get a chance to test and use these other options I will include them in the article.
User ID
The User ID is used when you are using SQL Authentication. In my experience this is ignored when using a Trusted_Connection, or Windows Authentication. If the username is associated with a password Password or Pwd will be used.
"user id=userid;"
Password or Pwd
The password field is to be used with the User ID, it just wouldn't make sense to log in without a username, just a password. Both Password and Pwd are completely interchangeable.
Data Source or Server or Address or Addr or Network Address
Upon looking in the MSDN documentation I found that there are several ways to specify the network address. The documentation mentions no differences between them and they appear to be interchangeable. The address is an valid network address, for brevity I am only using the localhost address in the examples.
Integrated Security and Trusted_Connection are used to specify wheter the connnection is secure, such as Windows Authentication or SSPI. The recognized values are true, false, and sspi. According to the MSDN documentation sspi is equivalent to true. Note: I do not know how SSPI works, or affects the connection.
Connect Timeout or Connection Timeout
These specify the time, in seconds, to wait for the server to respond before generating an error. The default value is 15 (seconds).
This is the last part of getting connected and is simply executed by the following (remember to make sure your connection has a connection string first):
Any guesses on what SqlCommand is used for? If you guessed for SQL commands then you are right on. An SqlCommand needs at least two things to operate. A command string, and a connection. First we'll look at the connection requirement. There are two ways to specify the connection, both are illustrated below:
Collapse SqlCommand myCommand = new SqlCommand("Command String", myConnection);
// - or -
myCommand.Connection = myConnection; The connection string can also be specified both ways using the SqlCommand.CommandText property. Now lets look at our first SqlCommand. To keep it simple it will be a simple INSERT command.
Collapse SqlCommand myCommand= new SqlCommand("INSERT INTO table (Column1, Column2) " + "Values ('string', 1)", myConnection);
// - or -
myCommand.CommandText = "INSERT INTO table (Column1, Column2) " + "Values ('string', 1)"; Now we will take a look at the values . table is simply the table within the database. Column1 and Column2 are merely the names of the columns. Within the values section I demonstrated how to insert a string type and an int type value. The string value is placed in single quotes and as you can see an integer is just passed as is. The final step is to execute the command with:
Inserting data is good, but getting the data out is just as important. Thats when the SqlDataReader comes to the rescue. Not only do you need a data reader but you need a SqlCommand. The following code demonstrates how to set up and execute a simple reader:
Collapse try { SqlDataReader myReader = null; SqlCommand myCommand = new SqlCommand("select * from table", myConnection); myReader = myCommand.ExecuteReader(); while(myReader.Read()) { Console.WriteLine(myReader["Column1"].ToString()); Console.WriteLine(myReader["Column2"].ToString()); } } catch (Exception e) { Console.WriteLine(e.ToString()); } As you can see the SqlDataReader does not access the database, it merely holds the data and provides an easy interface to use the data. The SqlCommand is fairly simple, table is the table your are going to read from. Column1 and Column2 are just the columns as in the table. Since there is a very high probability your will be reading more than one line a while loop is required to retrieve all of the records. And like always you want to try it and catch it so you don't break it.
There is a small problem with using SqlCommand as I have demonstrated, it leaves a large security hole. For example, with the way previously demonstrated your command string would be constructed something like this if you were to get input from a user:
Collapse SqlCommand myCommand = new SqlCommand( "SELECT * FROM table WHERE Column = " + input.Text, myConnection);
Its all fine and dandy if the user puts in correct syntax, however, what happens if the user puts value1, DROP table. Best case scenario it will cause an exception (I haven't checked to see what this example will do but it demonstrates a point), worst case you can kiss your table goodbye. You could parse all user input and strip out anything that could cause problems OR you could use an SqlParameter. Now the SqlParameter class is pretty big, but I will just show you a basic parameter usage. Basically you need three things to create a parameter. A name, data type, and size. (note for some data types you will want to leave off the size, such as Text).
SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.Int, 4); myParam2.Value = 42;
SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.Text); myParam.Value = "Note that I am not specifying size. " + "If I did that it would trunicate the text."; It is naming convention, it might be required I'm not sure, to name all parameters starting with the @ symbol. Now how do you use a parameter? Will its pretty easy as the following code shows.
Collapse SqlCommand myCommand = new SqlCommand( "SELECT * FROM table WHERE Column = @Param2", myConnection); myCommand.Parameters.Add(myParam2); Now this keeps a rogue user from high-jacking your command string. This isn't all there is to parameters if you want to learn more advanced topics a good place to start is here[^].
Closing a connection is just as easy as opening it. Just callSqlConnection.Close() but remember to put it in try/catch because like SqlConnection.Open() it does not return errors but throws an exception instead.
Re: how to work with database using ado.net and wcf 06-22-2010 6:07 AM |
public class Service1 : IService1 {
public List DoWork() {
SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\Administrator\Documents\visual studio 2010\Projects\LoadExcelObjects\LoadExcelObjects.Web\App_Data\Database1.mdf';Integrated Security=True;User Instance=True"); SqlDataAdapter da = new SqlDataAdapter("Select * from student",cn); DataSet ds = new DataSet(); da.Fill(ds);
List st = new List(); foreach (DataRow item in ds.Tables[0].Rows)
this is programmers blog
ReplyDeleteI have used custom error page in my project but i am unable to use it properly any
ReplyDeleteone give me idea
sorry i can't get your questions can you ellobrate it ?what kind of page it is?
ReplyDeleteHow to handle custom errors in .net
ReplyDeleteCreate your own
ReplyDeleteASP.NET offers a simple way to provide an alternative to the default error page. You may create a custom error page and tell Internet Information Services to display it instead of the default. The web.config file with the customErrors element contains the setting:
This tells the system to display the custom error page (errors/ErrorPage.aspx) instead of the default error page. Since errors may occur at any place within the application, the error page should be available to all users (security access of public). I usually create a public errors directory and place the appropriate resources within it.
The defaultRedirect attribute contains the path to the error page; in addition, the mode attribute tells the system if and how error handling is enabled. Here are the possible values of the attribute:
On: All errors utilize the custom error page.
Off: Custom error pages aren't used.
RemoteOnly: The custom error pages are used for clients other than the local machine. This is good for debugging a production application. It allows you to sit at the server and view errors while clients receive the custom error pages.
A custom error page may be a simple HTML file or an ASPX file--it depends upon your requirements. For example, you may use the following HTML as a custom error page:
Handling specific errors
The customErrors section of the web.config file allows you to handle individual errors differently. Errors are distinguished by their HTTP error codes.
Let's look at a few of the more common errors:
400--Bad request--The request could not be understood by the server.
401--Unauthorized--The request requires authentication.
404--Not found--The server has not found anything matching the request.
500--Internal server error--The server encountered an unexpected condition that prevented it from fulfilling the request.
A complete HTTP status code listing is available online. You may expand the customErrors section of the web.config file to handle specific error codes. You can achieve the expansion by adding error elements under the customErrors element. The following sample shows how you may accomplish this:
But how to get what sort of error has come due to which custom error page has been displayed.I mean how would the developer understands what error has come.Its OK,custom error page will not give that error page which is displayed in ASP.NET.
ReplyDeleteWe just can display some messages to the user through custom error page ,but I think it can hide errors from user only and yes,there is a way,developer can get understand these errors.Plz enhance this custom error topic with all these explaination like How the developer understands what errors has come when custom error message is displayed.
custom error page is used to display a custom page to the user only .developer can handle this problem by testing the page when an error occurred.
ReplyDeletei need code to upload photo in .net
ReplyDeletehow to send sms from ur website
ReplyDeletei need code to send sms from site
ReplyDeletehow to pass query string in html
ReplyDeleteCalling Stored Procedure
ReplyDeleteHope everyone have used SQLCommand / OLEDB Command objects in .NET. Here we can call stored procedures in two different forms, one without using parameter objects which is not recommended for conventional development environments, the other one is the familiar model of using Parameters.
In the first method you can call the procedure using Exec command followed by the procedure name and the list of parameters, which doesn’t need any parameters.
Example:
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()
Calling Stored Procedures with Exec command
SQLCmd.CommandText = "Exec SelectRecords 'Test', 'Test', 'Test'"
SQLCmd.Connection = SQLCon 'Active Connection
The second most conventional method of calling stored procedures is to use the parameter objects and get the return values using them. In this method we need to set the “SQLCommandType” to “StoredProcedure” remember you need to set this explicitly as the the default type for SQLCommand is SQLQuery”.
Here is an example to call a simple stored procedure.
Example - I (A Stored Procedure Returns Single Value)
In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly
'Set up Connection object and Connection String for a SQL Client
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()
SQLCmd.CommandText = "SelectRecords" ' Stored Procedure to Call
SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
SQLCmd.Connection = SQLCon 'Active Connection
http://www.dotnetspider.com/projects/Index.aspx?PageNumber=3
ReplyDeleteSqlConnection myConnection = new SqlConnection("user id=username;" +
ReplyDelete"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");
Making the Love Connection
ReplyDeleteThere is no real voodoo magic to creating a connection to a SQL Server assuming it is properly setup, which I am not going to go into in this article, in fact .NET has made working with SQL quite easy. First step is add the SQL Client namespace:
Collapse
using System.Data.SqlClient;
Then we create a SqlConnection and specifying the connection string.
Collapse
SqlConnection myConnection = new SqlConnection("user id=username;" +
"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");
Note: line break in connection string is for formatting purposes only
SqlConnection.ConnectionString
The connection string is simply a compilation of options and values to specify how and what to connect to. Upon investigating the Visual Studio .NET help files I discovered that several fields had multiple names that worked the same, like Password and Pwd work interchangeably. I have not included all of the options for SqlConnection.ConnectionString at this time. As I get a chance to test and use these other options I will include them in the article.
User ID
The User ID is used when you are using SQL Authentication. In my experience this is ignored when using a Trusted_Connection, or Windows Authentication. If the username is associated with a password Password or Pwd will be used.
"user id=userid;"
Password or Pwd
The password field is to be used with the User ID, it just wouldn't make sense to log in without a username, just a password. Both Password and Pwd are completely interchangeable.
"Password=validpassword;"-or-
"Pwd=validpassword;"
Data Source or Server or Address or Addr or Network Address
Upon looking in the MSDN documentation I found that there are several ways to specify the network address. The documentation mentions no differences between them and they appear to be interchangeable. The address is an valid network address, for brevity I am only using the localhost address in the examples.
"Data Source=localhost;"
-or-
"Server=localhost;"
-or-
"Address=localhost;"-or-"Addr=localhost;"
-or-"Network Address=localhost;"
Integrated Sercurity or Trusted_Connection
Integrated Security and Trusted_Connection are used to specify wheter the connnection is secure, such as Windows Authentication or SSPI. The recognized values are true, false, and sspi. According to the MSDN documentation sspi is equivalent to true. Note: I do not know how SSPI works, or affects the connection.
Connect Timeout or Connection Timeout
These specify the time, in seconds, to wait for the server to respond before generating an error. The default value is 15 (seconds).
"Connect Timeout=10;"-or-
"Connection Timeout=10;"
SqlConnection.Open()
ReplyDeleteThis is the last part of getting connected and is simply executed by the following (remember to make sure your connection has a connection string first):
Collapse
try
{
myConnection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
SqlCommand
ReplyDeleteAny guesses on what SqlCommand is used for? If you guessed for SQL commands then you are right on. An SqlCommand needs at least two things to operate. A command string, and a connection. First we'll look at the connection requirement. There are two ways to specify the connection, both are illustrated below:
Collapse
SqlCommand myCommand = new SqlCommand("Command String", myConnection);
// - or -
myCommand.Connection = myConnection;
The connection string can also be specified both ways using the SqlCommand.CommandText property. Now lets look at our first SqlCommand. To keep it simple it will be a simple INSERT command.
Collapse
SqlCommand myCommand= new SqlCommand("INSERT INTO table (Column1, Column2) " +
"Values ('string', 1)", myConnection);
// - or -
myCommand.CommandText = "INSERT INTO table (Column1, Column2) " +
"Values ('string', 1)";
Now we will take a look at the values . table is simply the table within the database. Column1 and Column2 are merely the names of the columns. Within the values section I demonstrated how to insert a string type and an int type value. The string value is placed in single quotes and as you can see an integer is just passed as is. The final step is to execute the command with:
Collapse
myCommand.ExecuteNonQuery();
SqlDataReader
ReplyDeleteInserting data is good, but getting the data out is just as important. Thats when the SqlDataReader comes to the rescue. Not only do you need a data reader but you need a SqlCommand. The following code demonstrates how to set up and execute a simple reader:
Collapse
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("select * from table",
myConnection);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
Console.WriteLine(myReader["Column1"].ToString());
Console.WriteLine(myReader["Column2"].ToString());
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
As you can see the SqlDataReader does not access the database, it merely holds the data and provides an easy interface to use the data. The SqlCommand is fairly simple, table is the table your are going to read from. Column1 and Column2 are just the columns as in the table. Since there is a very high probability your will be reading more than one line a while loop is required to retrieve all of the records. And like always you want to try it and catch it so you don't break it.
SqlParameter
ReplyDeleteThere is a small problem with using SqlCommand as I have demonstrated, it leaves a large security hole. For example, with the way previously demonstrated your command string would be constructed something like this if you were to get input from a user:
Collapse
SqlCommand myCommand = new SqlCommand(
"SELECT * FROM table WHERE Column = " + input.Text, myConnection);
Its all fine and dandy if the user puts in correct syntax, however, what happens if the user puts value1, DROP table. Best case scenario it will cause an exception (I haven't checked to see what this example will do but it demonstrates a point), worst case you can kiss your table goodbye. You could parse all user input and strip out anything that could cause problems OR you could use an SqlParameter. Now the SqlParameter class is pretty big, but I will just show you a basic parameter usage. Basically you need three things to create a parameter. A name, data type, and size. (note for some data types you will want to leave off the size, such as Text).
Collapse
SqlParameter myParam = new SqlParameter("@Param1", SqlDbType.VarChar, 11);
myParam.Value = "Garden Hose";
SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.Int, 4);
myParam2.Value = 42;
SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.Text);
myParam.Value = "Note that I am not specifying size. " +
"If I did that it would trunicate the text.";
It is naming convention, it might be required I'm not sure, to name all parameters starting with the @ symbol. Now how do you use a parameter? Will its pretty easy as the following code shows.
Collapse
SqlCommand myCommand = new SqlCommand(
"SELECT * FROM table WHERE Column = @Param2", myConnection);
myCommand.Parameters.Add(myParam2);
Now this keeps a rogue user from high-jacking your command string. This isn't all there is to parameters if you want to learn more advanced topics a good place to start is here[^].
Don't forget to close up when your done!
ReplyDeleteClosing a connection is just as easy as opening it. Just callSqlConnection.Close() but remember to put it in try/catch because like SqlConnection.Open() it does not return errors but throws an exception instead.
Collapse
try
{
myConnection.Close();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
Re: how to work with database using ado.net and wcf
ReplyDelete06-22-2010 6:07 AM |
public class Service1 : IService1
{
public List DoWork()
{
SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\Administrator\Documents\visual studio 2010\Projects\LoadExcelObjects\LoadExcelObjects.Web\App_Data\Database1.mdf';Integrated Security=True;User Instance=True");
SqlDataAdapter da = new SqlDataAdapter("Select * from student",cn);
DataSet ds = new DataSet();
da.Fill(ds);
List st = new List();
foreach (DataRow item in ds.Tables[0].Rows)
{
st.Add(new student(item[0].ToString(),item[1].ToString()));
}
// = ds.Tables[0].Rows.AsQueryable().Cast<student>().ToList();
return st;
}
}
[ServiceContract]
public interface IService1
{
[OperationContract]
List DoWork();
}
[DataContract]
public class student
{
public student(string name,string classes)
{
myclass = classes;
Name = name;
}
public string Name {get;set;}
public string myclass {get;set;}
}
We provide the solutions for every problem related to .net
ReplyDeleteplease send the queries regarding .net problems
ReplyDelete