The technique that is most frequently used to transfer data to an Excel workbook is Automation. With Automation, you can call methods and properties that are specific to Excel tasks. Automation gives you the greatest flexibility for specifying the location of your data in the workbook, formatting the workbook, and making various settings at run-time.

With Automation, you can use several techniques to transfer your data:

•	Transfer data cell by cell.
•	Transfer data in an array to a range of cells.
•	Transfer data in an ADO recordset to a range of cells by using the CopyFromRecordset method.
•	Create a QueryTable object on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source.
•	Transfer data to the clipboard, and then paste the clipboard contents into an Excel worksheet.

You can also use several methods that do not necessarily require Automation to transfer data to Excel. If you are running a server-side program, this can be a good approach for taking the bulk of data processing away from your clients.

To transfer your data without Automation, you can use the following approaches:

•	Transfer your data to a tab-delimited or comma-delimited text file that Excel can later parse into cells on a worksheet.
•	Transfer your data to a worksheet by using ADO.NET.
•	Transfer XML data to Excel (version 2002 and 2003) to provide data that is formatted and arranged into rows and columns.

Read More...

All .NET code can be installed on new machines with an easy XCopy deployment. XCopy term is used to say that it is easy to copy and paste (or zip and unzip) project files from one machine to other machine and that should complete installation. XCopy deployment in.NET has no issues of DLL hell or COM registration and registry mess.

Reality: This is not much different from .ini file based configuration and using dlls from project directory, used by most of us in Win 3.1 world. Later on Microsoft introduced registry as a central place to keep configuration and introduced COM to avoid DLL hell and multiple versions of dlls, all over the machine. Now we are back to similar world with few exceptions. Configuration of .NET application is stored in XML based configuration file. Assemblies (are similar to dlls), if they are copied to current project directory then they are loaded from where they are. This will allow us to have multiple assemblies of different versions used by different projects on same machine. One good thing mainly in web world is, if you copy new assemblies or change configuration (web.config), it will be reflected in your application with out stopping the web server. Annoying thing is that there is no good tool that I am aware of, to manage all different .NET application configuration files installed on a single machine. .NET Configuration tool allows to some extent changing configuration of applications but does not support all. So we are back to opening the XML file in notepad and editing configuration settings.

Read More...

ADO.NET includes many objects you can use to work with data. This section introduces some of the primary objects you will use. Over the course of this tutorial, you'll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson. The objects below are the ones you must know. Learning about them will give you an idea of the types of things you can do with data when using ADO.NET.

The SqlConnection Object

To interact with a data base, you must have a connection to it. The connection helps identify the data base server, the data base name, user name, password, and other parameters that are required for connecting to the data base. A connection object is used by command objects so they will know which data base to execute the command on.

The SqlCommand Object

The process of interacting with a data base means that you must specify the actions you want to occur. This is done with a command object. You use a command object to send SQL statements to the data base. A command object uses a connection object to figure out which data base to communicate with. You can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work on a group of data as described below.

The SqlDataReader Object

Many data operations require that you only get a stream of data for reading. The data reader object allows you to obtain the results of a SELECT statement from a command object. For performance reasons, the data returned from a data reader is a fast forward-only stream of data. This means that you can only pull the data from the stream in a sequential manner. This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.

The DataSet Object

DataSet objects are in-memory representations of data. They contain multiple Datatable objects, which contain columns and rows, just like normal data base tables. You can even define relations between tables to create parent-child relationships. The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario make sense. The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.

The SqlDataAdapter Object

Sometimes the data you work with is primarily read-only and you rarely need to make changes to the underlying data source. Some situations also call for caching data in memory to minimize the number of data base calls for data that does not change. The data adapter makes it easy for you to accomplish these things by helping to manage data in a disconnected mode. The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the data base. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the data base. Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data. You will have a data adapter defined for each table in a DataSet and it will take care of all communication with the data base for you. All you need to do is tell the data adapter when to load from or write to the data base.

Read More...