Code Monkey home page Code Monkey logo

frends.sql's Introduction

Frends.Sql

FRENDS SQL Tasks.

Installing

You can install the task via FRENDS UI Task view, by searching for packages. You can also download the latest NuGet package from https://www.myget.org/feed/frends/package/nuget/Frends.Sql and import it manually via the Task view.

Building

Clone a copy of the repo

git clone https://github.com/FrendsPlatform/Frends.Sql.git

Restore dependencies

dotnet restore

Rebuild the project

dotnet build

Run Tests To run the tests you will need an SQL server. You can set the database connection string in test project appsettings.json file

dotnet test Frends.Sql.Tests

Create a nuget package

dotnet pack Frends.Sql

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.

  1. Fork the repo on GitHub
  2. Clone the project to your own machine
  3. Commit changes to your own branch
  4. Push your work back up to your fork
  5. 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!

Documentation

Sql.ExecuteQuery

Input

Property Type Description Example
Query string The query that will be executed to the database. select Name,Age from MyTable where AGE = @Age
Parameters Array{Name: string, Value: string} A array of parameters to be appended to the query. Name = Age, Value = 42
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 int Timeout in seconds to be used for the query. 60 seconds by default.
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

Result

JToken. JObject[]

Example result

[ 
 {
  "Name": "Foo",
  "Age": 42
 },
 {
  "Name": "Adam",
  "Age": 42
 }
]

The second name 'Adam' can be now be accessed by #result[1].Name in the process parameter editor.

Sql.ExecuteProcedure

Input

Property Type Description Example
Execute string The stored procedure that will be executed. SpGetResultsByAge
Parameters Array{Name: string, Value: string} A array of parameters to be appended to the query. Name = Age, Value = 42
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 int Timeout in seconds to be used for the query. 60 seconds by default.
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

Result

JToken. JObject[]

Example result

[ 
 {
  "Name": "Foo",
  "Age": 42
 },
 {
  "Name": "Adam",
  "Age": 42
 }
]

The second name 'Adam' can be now be accessed by #result[1].Name in the process parameter editor.

Sql.BulkInsert

Input

Property Type Description Example
Input Data string The data that will be inserted into the database. The data is a json string formated as Json Array of objects. The data has to have the same number of columns in the same order as the destination table. [{"Column1": "One", "Column2": 10},{"Column1": "Two", "Column2": 20}]
Table Name 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
Convert Empty PropertyValues To Null bool If the input properties have empty values i.e. "", the values will be converted to null if this parameter is set to true.

Result

Integer - Number of copied rows

Sql.BatchOperation

Input

Property Type Description Example
Query string The query that will be executed to the database. insert into MyTable(ID,NAME) VALUES (@Id, @FirstName)
Input Json string A Json Array of objects that has their properties mapped to the parameters in the Query [{"Id":10, "FirstName": "Foo"},{"Id":15, "FirstName": "Bar"}]
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. 60 seconds by default.
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

Result

Integer - Number of affected rows

Example usage

BatchOperationExample.png

License

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

frends.sql's People

Contributors

ekih avatar jefim avatar jvuoti avatar nickeeex avatar ossigalkin avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar

Watchers

avatar avatar avatar avatar avatar avatar avatar avatar

Forkers

palssve

frends.sql's Issues

Task should return hierarchical JToken when querying joined/subqueried data

Joined data should be returned from SQL Server in similar data structure FOR JSON AUTO produces in which joined rows are in arrays within the object they're linked to.

Current Query + Dataset - JToken conversion creates flattened result set.

Perhaps adding an option which queries with 'FOR JSON AUTO' to return json string data that is deserialized to JToken by task itself would enable this?

Odd or misleading handling of empty values in BulkInsert

In BulkInsert if the parameter "Convert Empty PropertyValues To Null" is true

Frends.Sql/Frends.Sql/Sql.cs

Line 239 in 83d118c

if (options.ConvertEmptyPropertyValuesToNull)
the Task will set whole row to null instead of the individual cell

Frends.Sql/Frends.Sql/Extensions.cs

Line 49 in 83d118c

row[index] = null;
but that's what the actual method name implies though. But it does that when any of the cells are empty.

Why it sets whole row to null (why not element)?

ExecuteProcedure should support output parameters

ExecuteProcedure should support output parameters. It should be done by changing the parameter direction if it's output parameter and read them to return object after executing the command but before connection is terminated.

Fix documentation for BulkInsert

Documentation says "All object property names need to match with the destination table column names."

Looking from code it just uses https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-5.0

It won't use column mappings, which afaik should be used if column names would have any effect.

"If the data source and the destination table have the same number of columns, and the ordinal position of each source column within the data source matches the ordinal position of the corresponding destination column, the ColumnMappings collection is unnecessary. However, if the column counts differ, or the ordinal positions are not consistent, you must use ColumnMappings to make sure that data is copied into the correct columns." https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.columnmappings?redirectedfrom=MSDN&view=dotnet-plat-ext-5.0#System_Data_SqlClient_SqlBulkCopy_ColumnMappings

-> check if instead of names, the amount and order needs to match and fix documentation if needed

Memory issue

Works perfectly but there is somewhat of an issue with memory usage.
This code was consuming nearly 96% of my server memory.
I've tracked it down to the use of the JArray.
Why it's occurring is beyond my skill set and time but if you switch and return a DataTable the problem goes away.
I don't know why the GetSqlCommandResult loads a datatable then converts that to JToken/JArray. This is all SQL Server and we all know what a DataTable is and how to operate on that data.
My suggestion and what I did was to return the DataTable from the GetSqlCommandResult.
Works now with little impact to the memory usage.

Possible bug when handling Decimal types in BulkInsert

From customer:

When you try to write two or more rows to column with Decimal type and at least one row is NULL the Task will throw an error.

One row will work with NULL.

It seems that the Task will convert Decimal to String when handling multi line json.

BulkInsert: Small decimal number in json given scientific notation format

Hi,

I have an issue using the Frends.Sql.BulkInsert task where a small decimal number in the JToken is formatted with scientific notation on insertion. It crashes with the exception "The given value of type String from the data source cannot be converted to type float of the specified target column." Specifically, there is a decimal value 0.000000741 which is turned into 7.41e-7. I am almost certain this is the reason because when changing this field's value, the BulkInsert runs successfully.

Do you know if there is a way to work around this other than changing the datatype of the table in the destination database as suggested here?

Some more information:

I am moving data from an oracle database to an mssql server database.

Here is the row as output by the oracle db:

image

Here is the row as displayed in the Input.Input data in the BulkInsert log:

image

Here is most of stack trace of the exception:

image

You should be able to define input parameter type

The task wants all parameters to be of type string. This means that there is no way to execute a query such as "SELECT TOP(@rowCountParameter) * FROM MyTable" currently, forcing cumbersome checks in the Frends process.

It would be nice if the user could define the type of the SQL parameter in the task, or if the task could automatically recognize it.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.