SqlServerExplorerLib 1.3.0
dotnet add package SqlServerExplorerLib --version 1.3.0
NuGet\Install-Package SqlServerExplorerLib -Version 1.3.0
<PackageReference Include="SqlServerExplorerLib" Version="1.3.0" />
paket add SqlServerExplorerLib --version 1.3.0
#r "nuget: SqlServerExplorerLib, 1.3.0"
// Install SqlServerExplorerLib as a Cake Addin #addin nuget:?package=SqlServerExplorerLib&version=1.3.0 // Install SqlServerExplorerLib as a Cake Tool #tool nuget:?package=SqlServerExplorerLib&version=1.3.0
SqlServerExplorerLib
Explore and manipulate your SQL Server database the low-level way (a FAST alternative).
Do you want just to copy a database table to a file or copy tables between databases as fast as possible? If yes, you have come to the right place.
How to install
Via tha Package Manager:
Install-Package SqlServerExplorerLib
Via the .NET CLI
dotnet add package SqlServerExplorerLib
How to use
In order to use the library you have to add the following using statements as shown below.
For all examples below the sql
instance declared here is used as the SqlServerExplorer
object and the cs
variable as the connection string.
using SqlServerExplorerLib;
using System.Data;
...
Initialize sql
and cs
directly:
var sql = new SqlServerExplorer();
//Note that you can get the connection string from the SQL Server Object Explorer (assuming you are using Visual Studio) by expanding the database node and then retrieving the ConnectionString from the Properties window
string cs = "my_connection_string";
We could also instantiate via dependency injection (DI).
Let's assume that we have an appsettings.json
file in the project that is copied to the output directory.
{
"ConnectionStrings": {
"Default": "Data Source=DESKTOPCOMP\\SERVER2019;Initial Catalog=co2;Integrated Security=True;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False"
}
}
In all the following examples we assume that we used the 2nd approach. For the examples below we also instantiate a ILogger
instance.
We also assume a tableName
string that contains the target of all data operations in the database.
//example of using DI we could use the following:
var host = Host
.CreateDefaultBuilder()
.ConfigureServices(services => services.AddSingleton<SqlServerExplorer>())
.Build();
var logger = host.Services.GetRequiredService<ILoggerFactory>().CreateLogger("SQLServerExplorer");
var configuration = host.Services.GetRequiredService<IConfiguration>();
string cs = configuration.GetConnectionString("Default")!;
var sql = host.Services.GetRequiredService<SqlServerExplorer>();
//we also assume that we have a table named TEST_TABLE
string tableName = "TEST_TABLE";
In all cases, it is best to nest the code in a try
/catch
block for debugging purposes:
try
{
//all the code we write from now on
//...
}
catch (Exception e)
{
logger.LogCritical(e.Message);
return;
}
In almost all methods we are using async
methods internally, so we are using the await
keyword accordingly.
Test database connection
We can use the TestConnection
method to check for database connectivity before doing any other operation.
bool testConnection = await sql.TestConnection(cs);
if (testConnection)
logger.LogInformation("Connection is successfully tested.");
else
{
logger.LogError("Could not connect to the database.");
return;
}
Check for table existence
The TableExists
method returns true
if the table exists. For example:
bool tableExists = await sql.TableExists(cs, tableName);
if (tableExists) logger.LogInformation("Table {t} already exists.", tableName);
//if not then create the table with int, string and float fields
if (!tableExists)
{
await sql.Execute(cs, $"create table [{tableName}](id int, name nvarchar(50), value float)");
logger.LogInformation("Successfully created table {t}.", tableName);
}
Check for data existence
The IsTableEmpty
method returns true
if the table is empty. Raw operations are done using the functions Query
,QueryScalar<T>
, Execute
as shown in the following examples. In the example below, we create a new table if the table does not exist. The TruncateTable
function clears the table content.
bool isTableEmpty = await sql.IsTableEmpty(cs, tableName);
if (!isTableEmpty) logger.LogInformation("Table {t} is not empty.", tableName);
if (isTableEmpty)
{
logger.LogInformation("Table {t} is empty. New values will be added.", tableName);
//and insert some values
await sql.Execute(cs, $@"
insert into [{tableName}](id,name,value) values
(1, 'boo',2.3), (2, 'mpe',1.2), (3,'keftes',4.5)");
logger.LogInformation("Successfully added sample row to the table.");
}
...
//to clear all data from the table
await sql.TruncateTable(cs, tableName);
Get all tables
The GetTables
method returns the Schema
and Name
for each table in the database:
//list all tables
logger.LogInformation("Listing tables...");
List<SqlServerTable> tables = await sql.GetTables(cs);
foreach(var table in tables)
logger.LogInformation("Schema: {s}, Name: {n}, Full name: {t}", table.Schema, table.Name, table.ToString());
/*
info: SQLServerExplorer[0]
Schema: dbo, Name: TEST_TABLE, Full name: [dbo].[TEST_TABLE]
*/
Get all fields
The GetFields
method return all the fields in a specific table. Use the tables
instance from the previous code fragment:
//get the fields for the table we created
var myTable = tables.First(f=>f.Name == tableName);
logger.LogInformation("Listing fields...");
List<SqlServerField> fields = await sql.GetFields(cs,myTable);
foreach(var field in fields)
logger.LogInformation("Position: {o}, Name: {s}, Type: {t1}, SqlType: {t2}, Max length: {l}, Nullable: {n}",
field.OrdinalPosition,
field.Name,
field.DataType.Name,
field.SqlServerDataType,
field.MaximumCharacterLength, //for (n)varchar only
field.IsNullable
);
/*
info: SQLServerExplorer[0]
Position: 1, Name: id, Type: Int32, SqlType: int, Max length: (null), Nullable: True
info: SQLServerExplorer[0]
Position: 2, Name: name, Type: String, SqlType: nvarchar(50), Max length: 50, Nullable: True
info: SQLServerExplorer[0]
Position: 3, Name: value, Type: Double, SqlType: float, Max length: (null), Nullable: True
*/
The GetRecordsCount
returns the number of records for a specified table:
//check the number of records
int recordsCount = await sql.GetRecordsCount(cs,tableName);
logger.LogInformation("Number of records: {c}.",recordsCount);
//we can also get the records count using the myTable object
recordsCount = await sql.GetRecordsCount(cs, myTable);
DataTable and file operations
The Query
and GetTopRecords
get a DataTable
which we can use in any other operations subsequently:
string selectSql = $"select * from [{tableName}]";
//get custom scalar value
int customCount = await sql.QueryScalar<int>(cs, $"{selectSql} where id>1");
//get the datatable of a query result
DataTable data = await sql.Query(cs, selectSql);
//get top rows (practical for quick preview purposes)
DataTable previewData = await sql.GetTopRecords(cs, myTable, count: 200);
There are 2 ways to save a DataTable
to a file: indirectly via querying the database or directly via passing a DataTable
instance. To safe to a file we use the QueryToFile
and SaveToFile
methods:
//get the data AND save the result to a file
data = await sql.QueryToFile(cs, selectSql, targetFile: "./mydata.csv", fieldSeparator: ";");
//save a DataTable to a file
sql.SaveToFile(data, targetFile: "./mydata.csv", fieldSeparator: ";");
Bulk copy operations
In order to write data to a table in the database, we need to use bulk copy operations. These are used internally via the CopyTo
function. The CopyTo
has 2 overloads. We can copy data from datatable to a target database table, or we can copy data from one database to another database. The BulkCopyTimeoutInSeconds
and BatchSize
properties are used to control globally the bulk copy behavior.
//to control batch operations
sql.BulkCopyTimeoutInSeconds = 0; //0 (default) means no timeout
sql.BatchSize = 0; //0 (default) means send all records at one (for large datasets this value needs tuning for maximum performance
//copy data from a datatable to a table (fast way). The target table must pre-exist.
//an optional event handler may notify when the data has been loaded prior to bulk-copying
sql.LoadedData += (o, e) => { logger.LogInformation("Data has been loaded"); };
await sql.CopyTo(cs, data, destinationTableName: "OTHER_TABLE");
//copy data to another database. A table with the same name must pre-exist.
await sql.CopyTo(cs, targetConnectionString: "other_database_string", table: myTable, destinationTableName: "TARGET_TABLE");
STAY TUNED
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
-
net8.0
- Microsoft.Data.SqlClient (>= 5.2.0)
- Microsoft.SqlServer.Types (>= 160.1000.6)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.