ADONET-Sql-Server-Helper-Tools 1.0.0

ADONet Sql Server Tools

ADONet Sql Server Tools is a powerful library that provides a set of helper functions to work with SQL Server databases using ADO.NET. It streamlines common database operations such as executing queries, running stored procedures, managing transactions, and performing bulk insertions—all through a simple and intuitive API.

Whether you're building applications or automating tasks that interact with SQL Server, this library will make your life easier.

Features

• Query Execution: Easily execute SQL queries and retrieve results.
• Stored Procedures: Call stored procedures with parameters and fetch results.
• Bulk Insert: Perform efficient bulk insert operations using SqlBulkCopy.
• Transactions: Simplify complex transaction-based operations.
• Table/View/Stored Procedure Management: Easily create, alter, or drop tables, views, and stored procedures.
• JSON Export: Serialize query results into JSON format for easy integration with other services or APIs.

Installation

You can install ADONet Sql Server Tools through NuGet, making it easy to integrate into your project:

##Using Visual Studio

Right-click on your project in Solution Explorer. Select Manage NuGet Packages. Search for ADONet-Sql-Server-Tools. Click Install. using Microsoft.Data.SqlClient; using AdonetSqlserverHelper;

var connectionString = "your-connection-string-here"; using var connection = new SqlConnection(connectionString);

var sqlHelper = new SqlServerHelper(connection);

Execute SQL Queries Execute a standard SQL query and get the results as a DataTable:

var query = "SELECT * FROM Users"; var result = sqlHelper.ExecuteQuery(query);

foreach (DataRow row in result.Rows) { Console.WriteLine($"{row["UserId"]}: {row["UserName"]}"); }

Execute a Stored Procedure Call a stored procedure with parameters:

var parameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.Int) { Value = 1 } };

var storedProcedureResults = sqlHelper.ExecuteStoredProcedure("GetUserById", parameters);

Execute Non-Query (e.g., Insert, Update, Delete) To execute non-query SQL statements:

var insertQuery = "INSERT INTO Users (UserName, Email) VALUES (@UserName, @Email)";

var affectedRows = sqlHelper.ExecuteNonQuery(insertQuery, "Error inserting data."); Console.WriteLine($"{affectedRows} rows affected."); Perform Bulk Insert Insert a large dataset into a SQL Server table efficiently:

DataTable dataTable = new DataTable(); dataTable.Columns.Add("UserName", typeof(string)); dataTable.Columns.Add("Email", typeof(string));

dataTable.Rows.Add("Alice", "alice@example.com"); dataTable.Rows.Add("Bob", "bob@example.com");

sqlHelper.BulkInsert("Users", dataTable); Execute Transactions Execute multiple SQL commands within a single transaction:

var command1 = new SqlCommand("UPDATE Users SET IsActive = 1 WHERE UserId = 1"); var command2 = new SqlCommand("UPDATE Users SET IsActive = 0 WHERE UserId = 2");

sqlHelper.ExecuteTransaction(new[] { command1, command2 });

##Advanced Features Command Parameters: Support for parameterized queries to prevent SQL injection attacks. JSON Export: Convert your query results to JSON for easy integration with APIs or web services.

var jsonResult = sqlHelper.ExecuteQueryAsJson("SELECT * FROM Users"); Console.WriteLine(jsonResult); Table/View/Stored Procedure Management: Automate database schema management:

Create a table string createTableQuery = "CREATE TABLE Users (UserId INT PRIMARY KEY, UserName VARCHAR(50), Email VARCHAR(100))"; sqlHelper.CreateTable(createTableQuery);

Drop a table sqlHelper.DropTable("Users"); Example Project For more details, refer to the example project included in this repository. It shows how to integrate and use all the functionality offered by the library.

Contributing We welcome contributions from the community! If you would like to contribute to ADONet Sql Server Tools, please follow the steps below:

Fork the repository. Create a feature branch. Make your changes and commit them. Push your changes and create a pull request. License This project is licensed under the MIT License - see the LICENSE file for details.

Using .NET CLI

Run the following command in your project directory:

dotnet add package ADONet-Sql-Server-Tools