How do I call a stored procedure from my application using ADO.Net?
Using stored procedures with ADO.Net in C# is extremely simple, especially when we have developed the application with SQL commands. All we need is:
• Create a command object (SqlCommand, etc) and specify the stored procedure name
• Set the CommandType property of the command object to the
CommandType.StoredProcedure enumeration value. This tells the runtime that the command used here is a stored procedure.
What are the important points when developing a data access app with ADO.Net?
• Always try to use the base interfaces for connection, command, data reader and other objects.
• Always try to use the SqlClient, SqlServerCe and OracleClient to connect with the Sql Server, Sql Server CE and Oracle Database servers as they are specialized and optimized for the specific database servers.
• Still remember to reference the data provider specific objects (SqlConnection, OracleCommand) to reference with the base interface (IDbConnection, IDbCommand) .
• Do not write the connection string in your code as it may change. Either write it in a text file or an xml file and read it on the application startup. For security purposes, you may also write the encrypted connection string in the text/xml file .
• Try to use the stored procedures wherever possible especially when you are to write a series of queries whose individual results are not required to be used in the code in between this series of queries.
• Do not use the complex queries in the source code. If the query is getting complex, try to make the views inside the database server and use the views instead.
• Practice using the transactions when it makes sense, especially with error handling codes .
• Put special consideration in the error handling code. The database operation may fail due to various reasons such as invalid connection string, invalid table/field name in the query, database server failure, connection failure, too many connections on the server or the server busy, invalid query, etc You need to consider all these while writing the code for error handling.
• Using Visual Studio.Net’s debugger is a very good and useful practice to find the possible errors. Remember, Ado.Net exception messages are not much useful (or quite vague) in general for debugging; hence the use of watch and quick watch debugger windows is extremely useful and helpful in when debugging the code.
• When using dataset and disconnected architecture, we don’t update the data source (by calling DataAdapter’s Update() method and DataSet’s AcceptChanges() method) for each update.
Instead we make the changes local and update all these changes later as a batch. This provides optimized use of network bandwidth. BUT, this off course is not a better option when multiple users are updating the same database.
When changes are not to be done locally and need to be reflected at database server at the same time, it is preferred to use the connected oriented environment for all the changes (UPDATE, INSERT and DELETE) by calling the ExecuteNonQuery() method of your command (SqlCommand or OleDbCommand) object.
• Disconnected data access is suited most to read only services. In common practice clients are often interested in reading and displaying data. In this type of situation, the disconnected data access excels as it fetches the whole data in a single go and store it in the local buffer (dataset).
This local storage of data eliminates the need of staying connecting to the database and fetching single record at a time. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
What is data grid and what is the use of it?
Data Grid is the standard control for viewing data in .Net environment. A data grid control is represented in .Net by System.Windows.Forms.DataGrid class. The data grid control can display the data from a number of data sources e.g. data table, dataset, data view and array.
How can I make my first application with DataGrid using the data from ADO.Net?
Let’s create a simple application first that loads a table data from database server to the data grid control. First of all, add a data grid control and a button to your form from Visual Studio toolbox. We have set the Name property of data grid to ‘dgDetails’ and its CaptionText property to ‘ProgrammersHeaven Database’. The name of button is ‘btnLoadData’.
How can I make my data grid to view data from multiple related tables?
Let’s see how we can use Data Grid control to show multiple related tables. When two tables are related, one is called the parent table while the other is called the child table.
The child table contains the primary key of parent table as a foreign key. For example in our ProgrammersHeaven database, table Author is the parent table of the Article table as the Article table contains ‘AuthorId’ as foreign key which is a primary key in the Author table.
RELATED POST
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART ONE
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART TWO
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART THREE
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART FOUR
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART FIVE
ADO.NET INTERVIEW QUESTIONS AND ANSWERS PART ONE
ADO.NET INTERVIEW QUESTIONS AND ANSWERS PART TWO
You can also learn the concept of frame work concept in detail with questions and answers in the following place.
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART ONE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART TWO
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART THREE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART FOUR
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART FIVE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART SIX
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART SEVEN
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART EIGHT
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART NINE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART TEN
Using stored procedures with ADO.Net in C# is extremely simple, especially when we have developed the application with SQL commands. All we need is:
• Create a command object (SqlCommand, etc) and specify the stored procedure name
• Set the CommandType property of the command object to the
CommandType.StoredProcedure enumeration value. This tells the runtime that the command used here is a stored procedure.
What are the important points when developing a data access app with ADO.Net?
• Always try to use the base interfaces for connection, command, data reader and other objects.
• Always try to use the SqlClient, SqlServerCe and OracleClient to connect with the Sql Server, Sql Server CE and Oracle Database servers as they are specialized and optimized for the specific database servers.
• Still remember to reference the data provider specific objects (SqlConnection, OracleCommand) to reference with the base interface (IDbConnection, IDbCommand) .
• Do not write the connection string in your code as it may change. Either write it in a text file or an xml file and read it on the application startup. For security purposes, you may also write the encrypted connection string in the text/xml file .
• Try to use the stored procedures wherever possible especially when you are to write a series of queries whose individual results are not required to be used in the code in between this series of queries.
• Do not use the complex queries in the source code. If the query is getting complex, try to make the views inside the database server and use the views instead.
• Practice using the transactions when it makes sense, especially with error handling codes .
• Put special consideration in the error handling code. The database operation may fail due to various reasons such as invalid connection string, invalid table/field name in the query, database server failure, connection failure, too many connections on the server or the server busy, invalid query, etc You need to consider all these while writing the code for error handling.
• Using Visual Studio.Net’s debugger is a very good and useful practice to find the possible errors. Remember, Ado.Net exception messages are not much useful (or quite vague) in general for debugging; hence the use of watch and quick watch debugger windows is extremely useful and helpful in when debugging the code.
• When using dataset and disconnected architecture, we don’t update the data source (by calling DataAdapter’s Update() method and DataSet’s AcceptChanges() method) for each update.
Instead we make the changes local and update all these changes later as a batch. This provides optimized use of network bandwidth. BUT, this off course is not a better option when multiple users are updating the same database.
When changes are not to be done locally and need to be reflected at database server at the same time, it is preferred to use the connected oriented environment for all the changes (UPDATE, INSERT and DELETE) by calling the ExecuteNonQuery() method of your command (SqlCommand or OleDbCommand) object.
• Disconnected data access is suited most to read only services. In common practice clients are often interested in reading and displaying data. In this type of situation, the disconnected data access excels as it fetches the whole data in a single go and store it in the local buffer (dataset).
This local storage of data eliminates the need of staying connecting to the database and fetching single record at a time. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
What is data grid and what is the use of it?
Data Grid is the standard control for viewing data in .Net environment. A data grid control is represented in .Net by System.Windows.Forms.DataGrid class. The data grid control can display the data from a number of data sources e.g. data table, dataset, data view and array.
How can I make my first application with DataGrid using the data from ADO.Net?
Let’s create a simple application first that loads a table data from database server to the data grid control. First of all, add a data grid control and a button to your form from Visual Studio toolbox. We have set the Name property of data grid to ‘dgDetails’ and its CaptionText property to ‘ProgrammersHeaven Database’. The name of button is ‘btnLoadData’.
How can I make my data grid to view data from multiple related tables?
Let’s see how we can use Data Grid control to show multiple related tables. When two tables are related, one is called the parent table while the other is called the child table.
The child table contains the primary key of parent table as a foreign key. For example in our ProgrammersHeaven database, table Author is the parent table of the Article table as the Article table contains ‘AuthorId’ as foreign key which is a primary key in the Author table.
RELATED POST
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART ONE
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART TWO
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART THREE
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART FOUR
ASP.NET INTERVIEW QUESTIONS AND ANSWERS PART FIVE
ADO.NET INTERVIEW QUESTIONS AND ANSWERS PART ONE
ADO.NET INTERVIEW QUESTIONS AND ANSWERS PART TWO
You can also learn the concept of frame work concept in detail with questions and answers in the following place.
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART ONE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART TWO
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART THREE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART FOUR
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART FIVE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART SIX
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART SEVEN
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART EIGHT
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART NINE
MICROSOFT DOT NET FRAME WORK QUESTIONS AND ANSWERS PART TEN
No comments:
Post a Comment