frends-community - Frends.Community.SQL 1.1.1

Package Description

PM> Install-Package Frends.Community.SQL -Version 1.1.1 -Source https://www.myget.org/F/frends-community/api/v3/index.json

Copy to clipboard

> nuget.exe install Frends.Community.SQL -Version 1.1.1 -Source https://www.myget.org/F/frends-community/api/v3/index.json

Copy to clipboard

> dotnet add package Frends.Community.SQL --version 1.1.1 --source https://www.myget.org/F/frends-community/api/v3/index.json

Copy to clipboard

<PackageReference Include="Frends.Community.SQL" Version="1.1.1" />

Copy to clipboard

source https://www.myget.org/F/frends-community/api/v3/index.json

nuget Frends.Community.SQL  ~> 1.1.1

Copy to clipboard

> choco install Frends.Community.SQL --version 1.1.1 --source https://www.myget.org/F/frends-community/api/v2

Copy to clipboard

Import-Module PowerShellGet
Register-PSRepository -Name "frends-community" -SourceLocation "https://www.myget.org/F/frends-community/api/v2"
Install-Module -Name "Frends.Community.SQL" -RequiredVersion "1.1.1" -Repository "frends-community"

Copy to clipboard

Browse the sources in this package using Visual Studio or WinDbg by configuring the following legacy symbol server URL: https://www.myget.org/F/frends-community/symbols/

Frends.Community.SQL

FRENDS Community Task for SQL query results to CSV-file

MyGet

Installing
Tasks
- SaveQueryToCSV
- BulkInsertDataTable
License
Building
Contributing
Change Log

Installing

You can install the task via FRENDS UI Task View or you can find the NuGet package from the following NuGet feed https://www.myget.org/F/frends-community/api/v3/index.json and in Gallery view in MyGet https://www.myget.org/feed/frends-community/package/nuget/Frends.Community.SQL

Tasks

SaveQueryToCSV

Parameters

Property	Type	Description	Example
Query	string	SQL query to execute	`SELECT id FROM foo`
ConnectionString	string	Database connection string	`Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword`
TimeoutSeconds	int	Timeout in seconds	30
OutputFilePath	string	CSV file path for output	`C:\output\path.csv`
QueryParameters	SQLParameter[]	Query parameters	`[ { "@Param1", "Value1" }, { "@Param2", "Value2" }]`

Options

Settings for included attachments

Property	Type	Description	Example
ColumnsToInclude	string[]	Columns to include in the output CSV. If no columns defined here then all columns will be written to output CSV.	`[id, name, value]`
FieldDelimiter	enum { Comma, Semicolon, Pipe }	Field delimeter to use in output CSV	Comma
LineBreak	enum { CR, LF, CRLF }	Line break style to use in output CSV.	CRLF
FileEncoding	enum	Encoding for the read content. By selecting 'Other' you can use any encoding.
EncodingInString	string	The name of encoding to use. Required if the FileEncoding choice is 'Other'. A partial list of supported encoding names: https://msdn.microsoft.com/en-us/library/system.text.encoding.getencodings(v=vs.110).aspx	`iso-8859-1`
IncludeHeadersInOutput	bool	Wherther to include headers in output CSV.	`true`
SanitizeColumnHeaders	bool	Whether to sanitize headers in output: (1) Strip any chars that are not 0-9, a-z or _ (2) Make sure that column does not start with a number or underscore (3) Force lower case	`true`
AddQuotesToDates	bool	Whether to add quotes around DATE and DATETIME fields	`true`
AddQuotesToStrings	bool	Whether to add quotes around string typed fields.	`true`
DateFormat	string	Date format to use for formatting DATE columns, use .NET formatting tokens. Note that formatting is done using invariant culture.	`yyyy-MM-dd`
DateTimeFormat	string	Date format to use for formatting DATETIME columns, use .NET formatting tokens. Note that formatting is done using invariant culture.	`yyyy-MM-dd HH:mm:ss`

Notes

Newlines in text fields are replaced with spaces.

Binary datatypes are converted using BitConverter.ToString() method. The method returns the byte array as string representation of the byte array. The output can be changed back to byte array using Split method: output.Split('-').Select(b => Convert.ToByte(b, 16)).ToArray()

Returns

Result object with properties:

Property	Type	Description	Example
EntriesWritten	int	Amount of entries written.	1
Path	string	Path to the file.	C:\test.csv
FileName	string	Name of the file	test.csv

BulkInsertDataTable

Parameters

Property	Type	Description	Example
InputData	DataTable	Data to insert into table.	See DataTable examples.
TableName	string	Destination table name.	MyTable
Connection String	string	Connection String to be used to connect to the database.	Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Options

Property	Type	Description
Command Timeout Seconds	int	Timeout in seconds to be used for the query. Default is 60 seconds,
Fire Triggers	bool	When specified, cause the server to fire the insert triggers for the rows being inserted into the database.
Keep Identity	bool	Preserve source identity values. When not specified, identity values are assigned by the destination.
Sql Transaction Isolation Level	SqlTransationIsolationLevel	Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Possible values are: Default, None, Serializable, ReadUncommitted, ReadCommitted, RepeatableRead, Snapshot. Additional documentation https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx

Notes

This is a wrapper around SqlBulkCopy, similar to Frends.Sql.BulkInsert.

Unlike Frends.Sql.BulkInsert, this task takes the data as DataTable, which allows specifying column types explicitly. In contrast, Frends.Sql.BulkInsert infers the types from the first inserted row, which can cause errors with non-string nullable columns.

Returns

Integer - Number of copied rows.

License

This project is licensed under the MIT License - see the LICENSE file for details

Building

Clone a copy of the repo

git clone https://github.com/CommunityHiQ/Frends.Community.SQL.git

Build the project

dotnet build

Build docker container

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Salakala123!" -p 1433:1433 --name sql1 --hostname sql1 -d mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04

Run Tests

dotnet test

Create a nuget package

dotnet pack --configuration Release

Contributing

When contributing to this repository, please first discuss the change you wish to make via issue, email, or any other method with the owners of this repository before making a change.

Fork the repo on GitHub
Clone the project to your own machine
Commit changes to your own branch
Push your work back up to your fork
Submit a Pull request so that we can review your changes

NOTE: Be sure to merge the latest from "upstream" before making a pull request!

Change Log

Version	Changes
1.1.1	Migration from Frends.Community.SQL.QueryToFile. Converted to support .Net Framework 4.7.1 and .Net Standard 2.0. Renamed task.
1.1.2	Updated README and changed CI to create release in GitHub.
1.1.3	Updated dependencies System.ComponentModel.Annotations to 5.0.0 and CsvHelper to 27.1.1, also replaced MSTest.TestAdapter and MSTest.TestFramework with NUnit.Framework.
1.2.0	Added BulkInsertDataTable and related test.
1.3.0	Added parameter AddQuotesToStrings to SaveQueryToCSVOptions which if disabled will not add quotes to string typed fields.
1.4.0	Added result object SaveQueryToCSVResult with EntriesWritten, Path and FileName properties. Added option to pass custom field delimiter. Added support for binary datatypes.
2.0.0	Added targeting to .NET6 and .NET8. Updated the following packages: CsvHelper to v33.0.1 and System.Configuration.ConfigurationManager to 9.0.0. Replaced System.Data.SqlClient with Microsoft.Data.SqlClient, as the former is deprecated. Made small adjustments as required after package updates.

.NETFramework 4.7.1
- CsvHelper (>= 15.0.5)
- Microsoft.CSharp (>= 4.7.0)
- MSTest.TestAdapter (>= 2.1.2)
- System.ComponentModel.Annotations (>= 4.7.0)
- System.Configuration.ConfigurationManager (>= 4.7.0)
- System.Data.SqlClient (>= 4.8.2)
.NETStandard 2.0
- CsvHelper (>= 15.0.5)
- Microsoft.CSharp (>= 4.7.0)
- MSTest.TestAdapter (>= 2.1.2)
- System.ComponentModel.Annotations (>= 4.7.0)
- System.Configuration.ConfigurationManager (>= 4.7.0)
- System.Data.SqlClient (>= 4.8.2)

.NETFramework 4.7.1: 4.7.1.0
.NETStandard 2.0: 2.0.0.0

Assembly	Assembly hash	Match
/lib/net471/frends.community.sql.dll	38ab3d0506aa4d0eb4cfad535ac5cefdFFFFFFFF
/lib/netstandard2.0/frends.community.sql.dll	b1d6ac27b39d4c90bad7edcef849ed26FFFFFFFF

Owners

FRENDS Community Feed User

Authors

HiQ Finland

Project URL

https://github.com/CommunityHiQ/Frends.Community.SQL

License

Unknown

Info

106 total downloads

2 downloads for version 1.1.1

Download (17.7 KB)

Download legacy symbols (34.27 KB)

Found on the current feed only

Package history

Version	Size	Last updated	Downloads
2.0.0	45.27 KB	Wed, 29 Jan 2025 12:03:16 GMT	16
1.4.0	23.35 KB	Tue, 06 Feb 2024 13:59:20 GMT	23
1.3.0	22.18 KB	Thu, 02 Feb 2023 08:33:55 GMT	25
1.2.0	38.18 KB	Mon, 23 Jan 2023 10:32:00 GMT	8
1.1.3	17.7 KB	Mon, 19 Dec 2022 11:46:54 GMT	6
1.1.2	17.71 KB	Tue, 28 Dec 2021 10:02:51 GMT	26
1.1.1	17.7 KB	Tue, 28 Dec 2021 09:51:24 GMT	2

frends-community - Frends.Community.SQL 1.1.1

Frends.Community.SQL

Installing

Tasks

SaveQueryToCSV

Parameters

Options

Notes

Returns

BulkInsertDataTable

Parameters

Options

Notes

Returns

License

Building

Contributing

Change Log

Owners

Authors

Project URL

License

Tags

Info

Package history