How to - Datatool

How to use Datatool

Install	How to use datatool Visit datatool page to download the tool. This will automatically install the tool and place icon on the desktop. If the installer shows error of missing components, you may have to find the download by yourself over the web. Note: Opening datatool automatically opens previously saved connection strings and selects the first connection string to use.
	Use Wizard Use wizard helps you build new connection string. Note: Upgrading to new version might not preseve previously saved connection strings.
	Connect Once a connection string is prepared or selected, clicking this button would attempt to connect to the datasource. Datatool will find appropriate .NET namespace automatically. Native providers installed will take precedence over .NET internal namespaces like OleDB and Odbc. Post connection, the namespace used to connect will be displayed below the Save button.
	Save Provide a unique name for a connected datasource and click Save. This helps you to reuse this connection later.
	Writing Language Integrated Queries [LInQ to Entities] 1. Generate ORM using Enable LInQ Button. Click Finish button directly to generate EDMX for entire database objects. Or select appropriate Schema/Tables/Views... 2. Select query type as LInQ. 3. Select the newly generated or already generated EDMX in the drop down box. 4. Type your LInQ query and execute. Ensure connection is opened to appropriate datastore before execution.
	Writing Language Integrated Queries [Generic] 1. Enter the data against you want to query in the connection string area. Ex: "New York","Washington","Hong Kong","London","New Delhi","Beijing" 2. Enter a name for this data in connection name text box. Ex: Cities. 3. Select query type as "LInQ" and enter a query. Ex: from Cty in Cities where Cty.Startswith("C") select Cty 4. If you write a dynamic query for example against processes in the system, do not give anything in connection string area or connection name area. Ex: from Prc in Process.GetProcesses where not String.IsNullOrEmpty(Prc.mainwindowtitle) select Prc
	Write SQL Once connection is established, you may type SQL. The SQL area helps you with all table names and view names as intellisense. 1. Select a query or part of query and press F5 key to execute or press Execute button. 2. If Intellisense shows up with table/view names, type part of table name or use up/down arrow to select and press Enter or Tab to continue. 3. Currently only one query at a time can be executed. Future versions will support multiple query execution. 4. Similar to SQL you may also call Stored procedures like "Call MySP(?,?)" 5. Use "getproviders" to get all .NET installed provider namespaces. 6. Use "getschema()" or "getschema("tables") Parameters 1. If you intend to use parameters in SQL, use "?" in your SQL. 2. Select "Use Parameter(s)" option. 3. Click "Add" in parameters section. This will show up parameter name selection. Datatool automatically puts column name after which "=?" is placed in the SQL. 4. The Type list will be native datatypes of the selected data source. This varies for every data source. 5. If the Value provided is near to the selected datatype, datatool tries to handle datatypes appropriately. Otherwise "String" datatype is selected by default. 6. Once a parameter is prepared, Update button has to be clicked to continue. 7. Editing a selected parameter is also similar. 8. You may choose any or all parameters. Removing a parameter will be given in future version.
	Export SQL result to Excel Once SQL result is displayed, click Export to Excel option. This will prompt use to enter a file name. Once given an XML file will load in your browser. You can save this XML file direcly as XLSX. To use this option it is not mandatory to have MS Excel to be present in your PC.