I am creating a customized activity related to SQL, and I require the SQLClient DLL to be included in the project. However, it seems that UiPath does not permit adding the System.Data.SqlClient package either from the Manage NuGet Packages option or the project reference window.
Previously, I had not used this customized activity package and instead used the Class.Net framework, which worked well. However, due to issues with migrating to .NET 6.0, I thought that using the available package might be helpful. But now, I am unable to add the essential component of my activity, which is the SQLClient.
Here is the code if need for refrence
using System;
using System.Activities;
using System.Threading;
using System.Threading.Tasks;
using System.Data;
using IndieDev91.BulkInsertToSql.Activities.Properties;
using UiPath.Shared.Activities;
using UiPath.Shared.Activities.Localization;
using System.Linq;
namespace IndieDev91.BulkInsertToSql.Activities
{
[LocalizedDisplayName(nameof(Resources.BulkInsertToSQL_DisplayName))]
[LocalizedDescription(nameof(Resources.BulkInsertToSQL_Description))]
public class BulkInsertToSQL : ContinuableAsyncCodeActivity
{
#region Properties
/// <summary>
/// If set, continue executing the remaining activities even if the current activity has failed.
/// </summary>
[LocalizedCategory(nameof(Resources.Common_Category))]
[LocalizedDisplayName(nameof(Resources.ContinueOnError_DisplayName))]
[LocalizedDescription(nameof(Resources.ContinueOnError_Description))]
public override InArgument<bool> ContinueOnError { get; set; }
[LocalizedDisplayName(nameof(Resources.BulkInsertToSQL_ConnectionString_DisplayName))]
[LocalizedDescription(nameof(Resources.BulkInsertToSQL_ConnectionString_Description))]
[LocalizedCategory(nameof(Resources.Input_Category))]
public InArgument<string> ConnectionString { get; set; }
[LocalizedDisplayName(nameof(Resources.BulkInsertToSQL_TableName_DisplayName))]
[LocalizedDescription(nameof(Resources.BulkInsertToSQL_TableName_Description))]
[LocalizedCategory(nameof(Resources.Input_Category))]
public InArgument<string> TableName { get; set; }
[LocalizedDisplayName(nameof(Resources.BulkInsertToSQL_Datatable_DisplayName))]
[LocalizedDescription(nameof(Resources.BulkInsertToSQL_Datatable_Description))]
[LocalizedCategory(nameof(Resources.Input_Category))]
public InArgument<DataTable> Datatable { get; set; }
#endregion
#region Constructors
public BulkInsertToSQL()
{
}
#endregion
#region Protected Methods
protected override void CacheMetadata(CodeActivityMetadata metadata)
{
if (ConnectionString == null) metadata.AddValidationError(string.Format(Resources.ValidationValue_Error, nameof(ConnectionString)));
if (TableName == null) metadata.AddValidationError(string.Format(Resources.ValidationValue_Error, nameof(TableName)));
if (Datatable == null) metadata.AddValidationError(string.Format(Resources.ValidationValue_Error, nameof(Datatable)));
base.CacheMetadata(metadata);
}
protected override async Task<Action<AsyncCodeActivityContext>> ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
{
// Inputs
var connectionString = ConnectionString.Get(context);
var tableName = TableName.Get(context);
var datatable = Datatable.Get(context);
// My Code Starts Here
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Check if table exists
using (SqlCommand checkTableCommand = new SqlCommand())
{
checkTableCommand.Connection = connection;
checkTableCommand.CommandText = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'";
int count = (int)checkTableCommand.ExecuteScalar();
if (count == 0)
{
// Table doesn't exist, so create it
using (SqlCommand createTableCommand = new SqlCommand())
{
createTableCommand.Connection = connection;
createTableCommand.CommandText = GenerateCreateTableQuery(dataTable, tableName);
createTableCommand.ExecuteNonQuery();
}
}
else
{
// Table exists, so check the number of columns
using (SqlCommand checkColumnsCommand = new SqlCommand())
{
checkColumnsCommand.Connection = connection;
checkColumnsCommand.CommandText = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'";
int columnCount = (int)checkColumnsCommand.ExecuteScalar();
if (columnCount < dataTable.Columns.Count)
{
throw new Exception("The number of columns in the DataTable is greater than the number of columns in the database table.");
}
}
}
// Use SqlBulkCopy to insert the data in bulk to the SQL Server table
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = 10000; // adjust batch size as per your requirement
bulkCopy.BulkCopyTimeout = 60; // set bulk copy timeout as per your requirement
foreach (DataColumn column in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, $"[{column.ColumnName}]");
}
bulkCopy.WriteToServer(dataTable);
}
}
connection.Close();
}
}
catch (Exception ex)
{
// Handle the exception here
throw new Exception($"Exception: {ex.Message}");
}
// Outputs
return (ctx) => {
};
}
#endregion
public static string GenerateCreateTableQuery(DataTable dataTable, string tableName)
{
var columnDefs = dataTable.Columns
.Cast<DataColumn>()
.Select(column => $"[{column.ColumnName}] {GetSqlDataType(column.DataType)}");
string createTableQuery = $"CREATE TABLE [{tableName}] ({string.Join(", ", columnDefs)})";
return createTableQuery;
}
public static string GetSqlDataType(Type dataType)
{
if (dataType == typeof(int))
{
return "INT";
}
else if (dataType == typeof(string))
{
return "NVARCHAR(MAX)";
}
else if (dataType == typeof(double))
{
return "FLOAT";
}
else if (dataType == typeof(DateTime))
{
return "DATETIME";
}
else if (dataType == typeof(object))
{
return "NVARCHAR(MAX)";
}
// add more data types as per your requirement
else
{
throw new NotSupportedException($"Data type '{dataType.Name}' not supported.");
}
}
}
}
I have provided the code for the .csproj file of the previous version of this project, which was created using the Class (.NET Framework). However, I am currently unable to use it due to issues with the .NET Framework version. I have attempted to migrate the project using the official documentation, but have not had success thus far.
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
<PropertyGroup>
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
<Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
<ProjectGuid>{14248EFD-2B04-4F22-8FBE-ACFC62A61DC5}</ProjectGuid>
<OutputType>Library</OutputType>
<AppDesignerFolder>Properties</AppDesignerFolder>
<RootNamespace>SqlBulkInsert</RootNamespace>
<AssemblyName>SqlBulkInsert</AssemblyName>
<TargetFrameworks>net461;net6.0-windows</TargetFrameworks>
<FileAlignment>512</FileAlignment>
<Deterministic>true</Deterministic>
</PropertyGroup>
<ItemGroup Condition=" '$(TargetFramework)' == 'net461' ">
<Reference Include="PresentationCore" />
<Reference Include="PresentationFramework" />
<Reference Include="System" />
<Reference Include="System.Activities" />
<Reference Include="System.Activities.Core.Presentation" />
<Reference Include="System.Activities.Presentation" />
<Reference Include="System.ComponentModel.Composition" />
<Reference Include="System.Core" />
<Reference Include="System.Xaml" />
<Reference Include="System.Xml.Linq" />
<Reference Include="System.Data.DataSetExtensions" />
<Reference Include="Microsoft.CSharp" />
<Reference Include="System.Data" />
<Reference Include="System.Net.Http" />
<Reference Include="System.Xml" />
<Reference Include="WindowsBase" />
</ItemGroup>
<ItemGroup Condition=" '$(TargetFramework)' == 'net6.0-windows' ">
<PackageReference Include="UiPath.Workflow.Runtime" Version="6.0.0-20220401-03" PrivateAssets="All" />
<PackageReference Include="UiPath.Workflow" Version="6.0.0-20220401-03" PrivateAssets="All" />
<PackageReference Include="System.Activities.Core.Presentation" Version="6.0.0-20220318.2" PrivateAssets="All" />
<PackageReference Include="System.Activities.Metadata" Version="6.0.0-20220318.2" PrivateAssets="All" />
</ItemGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
<DebugSymbols>true</DebugSymbols>
<DebugType>full</DebugType>
<Optimize>false</Optimize>
<OutputPath>bin\Debug\</OutputPath>
<DefineConstants>DEBUG;TRACE</DefineConstants>
<ErrorReport>prompt</ErrorReport>
<WarningLevel>4</WarningLevel>
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
<DebugType>pdbonly</DebugType>
<Optimize>true</Optimize>
<OutputPath>bin\Release\</OutputPath>
<DefineConstants>TRACE</DefineConstants>
<ErrorReport>prompt</ErrorReport>
<WarningLevel>4</WarningLevel>
</PropertyGroup>
<ItemGroup>
<Reference Include="System" />
<Reference Include="System.Activities" />
<Reference Include="System.ComponentModel.Composition" />
<Reference Include="System.Core" />
<Reference Include="System.Xml.Linq" />
<Reference Include="System.Data.DataSetExtensions" />
<Reference Include="Microsoft.CSharp" />
<Reference Include="System.Data" />
<Reference Include="System.Net.Http" />
<Reference Include="System.Xml" />
</ItemGroup>
<ItemGroup>
<Compile Include="Class1.cs" />
<Compile Include="Properties\AssemblyInfo.cs" />
</ItemGroup>
<Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
</Project>
ashokkarale
Yoichi
postwick
Anil_G
kishan.savaliya
Laura_Hubbard
Tselisehang_Molebatsi
Byrnecut
Lahiru.Fernando
wahyumrizqi