SqlCRUDRepository 1.0.21

.NET Core 3.0 This package targets .NET Core 3.0. The package is compatible with this framework or higher. .NET Standard 2.0 This package targets .NET Standard 2.0. The package is compatible with this framework or higher. .NET Framework 4.0 This package targets .NET Framework 4.0. The package is compatible with this framework or higher.
There is a newer version of this package available.
See the version list below for details. 
dotnet add package SqlCRUDRepository --version 1.0.21                

                    
                

            
NuGet\Install-Package SqlCRUDRepository -Version 1.0.21
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package. 
<PackageReference Include="SqlCRUDRepository" Version="1.0.21" />
For projects that support PackageReference, copy this XML node into the project file to reference the package. 
paket add SqlCRUDRepository --version 1.0.21                

                    
                

            
#r "nuget: SqlCRUDRepository, 1.0.21"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package. 
// Install SqlCRUDRepository as a Cake Addin
#addin nuget:?package=SqlCRUDRepository&version=1.0.21

// Install SqlCRUDRepository as a Cake Tool
#tool nuget:?package=SqlCRUDRepository&version=1.0.21

SqlCRUDRepository

SqlCRUDRepository is a .net library To perform read, insert, edit and delete operations on SQL Server database tables.

This project was developed for Microsoft SQL Server. You can extend this project for other databases.

Samples

1. How to create a new record in a table?

Suppose you have a table called tblCustomer in a database.

Create Table tblCustomer (ID int Identity (1,1), FirstName nvarchar (max), LastName nvarchar (max))

we create a model from this table.(In later sections we will change the class name so that it is not the same as the table name)

public class tblCustomer
{
    public Int32 ID {get; set; }
    public string FirstName {get; set; }
    public string LastName {get; set; }
}

then save it!

var Sampledb = Repository.RepositoryFactory.CreateRepository (ConnectionString);
tblCustomer customer = new tblCustomer ();
customer.FirstName = "FirstNameCustomer";
customer.LastName = "LastNameCustomer";
Sampledb.Save (customer);
Console.WriteLine (customer.ID);
  • You can use IDbConnection instead of ConnectionString for added security.
  • After saving in the table, an ID will be given to the customer.

2. How to find a customer with an ID and change its fields?

        var customerdb = Sampledb.GetByID <tblCustomer> (customer.ID.ToString (), false);
        customerdb.FirstName = "ChangedFirstNameCustomer";
        Sampledb.Save (customerdb);

  • Note: properties with null value ignored in SQl update statment .

    for example.

          tblCustomer customer = new tblCustomer ();
      customer.ID=3;
      customer.FirstName = "ChangedFirstNameCustomer";
      Sampledb.Save (customer);

    Update tblCustomer without change LastName field in database. (LastName is nullable and has value null in sample code)

3. How to remove customer from database?

Sampledb.Delete (customerdb); // you can also use DeleteList(batch delete) In latest version.

4. How do I use transactions?

        Sampledb.BeginTransaction ();
        tblCustomer customer = new tblCustomer ();
        customer.FirstName = "FirstNameCustomer";
        customer.LastName = "LastNameCustomer";
        Sampledb.Save (customer);
        var customerdb = Sampledb.GetByID <tblCustomer> (customer.ID.ToString (), false);
        customerdb.FirstName = "ChangedFirstNameCustomer";
        Sampledb.Save (customerdb);
        Sampledb.Delete (customerdb);
        Sampledb.CommitTransaction ();

5. How do I change the model name?

[Repository.Domain.TableInfo ("tblCustomer", "ID", true)]
public class Customer
{
    public Int32 ID {get; set; }
    public string FirstName {get; set; }
    public string LastName {get; set; }
}
  • In this section you can create a read-only or write-only table.

6. Can the name of the class property be different from the name of the fields in my table?

Yes. Using [Repository.Domain.FieldInfo ("FirstName", false)]

[Repository.Domain.TableInfo ("tblCustomer", "ID", true)]
public class Customer
{
    public Int32 ID {get; set; }
    [Repository.Domain.FieldInfo ("FirstName", false)]
    public string FirstName {get; set; }
    public string LastName {get; set; }
}

7. What can be done for properties that exist in the class but do not exist in the database?

    [Repository.Domain.FieldInfo ("CustomProperty", true)]
    public int CustomProperty {get; set; }

8. How do I call a StoredProcedure?

var result = Sampledb.Execute_StoredProcedure <Customer> (spName, null);

  • Up to 10 ResultSet can be obtained with one call.

9. How can I find specific values ​​in a table? For example, all customers whose name is equal to a certain value?

        string Filter = "FirstName = @ FirstName";
        Dictionary <string, string> parameters = new Dictionary <string, string> ();
        parameters.Add ("FirstName", "FirstNameCustomer");
        var result = Sampledb.Find <Customer> (Filter, "ID", false, parameters, "");
        Console.WriteLine (result.Count);

  • In this section you can both sort and name the fields you just want so that only those fields are provided for you.

  • In the Find and GetByID methods, a parameter called withLock is considered, which can also be used to lock table records when reading. In cases where there is a transaction, this can be useful to make the synchronization controllable.

10. Can we give the ID generation mechanism ourselves, for example not the Identity table?

Yes. To do this, you must first change the model(Both database and code).

Create Table tblCustomer2(ID nvarchar(500), FirstName nvarchar(max), CustomerLastName nvarchar(max),Description nvarchar(max))

[Repository.Domain.TableInfo("tblCustomer2","ID",false)]
public class Customer
{
    public string ID { get; set; }
    [Repository.Domain.FieldInfo("FirstName",false)]
    public string FirstName { get; set; }
    [Repository.Domain.FieldInfo("CustomerLastName", false)]
    public string LastName { get; set; }

    [Repository.Domain.FieldInfo("CustomProperty", true)]
    public int CustomProperty { get; set; }
}

There are two mechanisms for creating an ID

  1. In the program code (this model is available in version 1.0.14 and above)

    You must have a function to create an ID.

    For example

     private string keygenerator(string ClassName)
 {
     return Guid.NewGuid().ToString();
 }

    And at the end

    Sampledb.__ KeyGenerator = keygenerator;

  2. In the database (default)

    There is a stored procedure in the database called __KeyGenerator that must be overwritten.

11. Is it possible to update in batches?

Yes.

For example This command updates the first name column of all records whose ID is not 2.

   Customer o = new Customer();
   o.FirstName = "a";
   Sampledb.Save(o,"ID <> 2");

12. How can I find the first record matching the filter?

        string Filter = "FirstName = @FirstName";
        Dictionary <string, string> parameters = new Dictionary <string, string> ();
        parameters.Add ("FirstName", "FirstNameCustomer");
        var result = Sampledb.FindFirst <Customer> (Filter, "ID", false, parameters, "");

License

MIT

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  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. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 is compatible.  netcoreapp3.1 is compatible. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net40 is compatible.  net403 was computed.  net45 is compatible.  net451 was computed.  net452 was computed.  net46 was computed.  net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.27 269 10/29/2023
1.0.26 390 1/21/2023
1.0.25 331 1/17/2023
1.0.23 423 10/31/2022
1.0.22 456 10/8/2022
1.0.21 454 8/27/2022
1.0.19 446 8/15/2022
1.0.18 522 2/19/2022
1.0.17 495 1/30/2022
1.0.16 509 1/30/2022
1.0.15 499 1/26/2022
1.0.14 328 1/8/2022
1.0.13 354 12/15/2021
1.0.12 402 10/31/2021
1.0.11 397 9/5/2021
1.0.10 413 8/29/2021
1.0.9 378 8/28/2021
1.0.6 390 6/16/2021
1.0.5 350 6/13/2021
1.0.4 374 6/8/2021

1.0.21
Fix Bug FindFirst.
1.0.20
Improved performance of FindFirst function by adding Top 1.
1.0.19
Batch Update.
Added parameter to Save function to IRepository with Filter(Update all recorde in database match with specific filter).
1.0.18
GUID Column support For primary key.
1.0.17
Added Connection TimeOut For StoredProcedure.
1.0.16
Added Connection TimeOut.
1.0.15
Added DeleteList function to IRepository with Filter(Delete all recorde in database match with specific filter).
1.0.14
Added ID Generation mechanism in Code.
1.0.13
Added Transaction Count property to IRepository interface.
1.0.12
When you use special characters in the name of properties in objects such as Chinese characters!
Add brackets for the name of the properties of each object to prevent errors when query(SELECT ...  FROM TABLE) the database.
1.0.11
Add new constructor with IDbConnection parameter.