SimpleNet.Standard.Data.SqlServer 2.0.1

There is a newer version of this package available.
See the version list below for details.
dotnet add package SimpleNet.Standard.Data.SqlServer --version 2.0.1                
NuGet\Install-Package SimpleNet.Standard.Data.SqlServer -Version 2.0.1                
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="SimpleNet.Standard.Data.SqlServer" Version="2.0.1" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add SimpleNet.Standard.Data.SqlServer --version 2.0.1                
#r "nuget: SimpleNet.Standard.Data.SqlServer, 2.0.1"                
#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 SimpleNet.Standard.Data.SqlServer as a Cake Addin
#addin nuget:?package=SimpleNet.Standard.Data.SqlServer&version=2.0.1

// Install SimpleNet.Standard.Data.SqlServer as a Cake Tool
#tool nuget:?package=SimpleNet.Standard.Data.SqlServer&version=2.0.1                

SimpleNet.Standard

SimpleNet.Standard.Data is a .NET Standard implementation of a DAL (Data Access Layer) with the hopes of simlifying execution of SQL Statements.

SimpleNet.Standard.Data.SqlServer

An implementation of ISimpleDatabaseProvider for SQL Server databases.

How To

  1. Include SimpleNet.Standard.Data and SimpleNet.Standard.Data.SqlServer in your project

  2. Create an instance of ISimpleDatabaseProvider ()

var dbProvider = new SqlServerProvider("Your SQL server Connection String");

  1. Create an instance of ISimpleDataAccessLayer

var dal = new SimpleDataAccessLayer(dbProvider);

  1. Execute SQL statements
     dal.ExecuteNonQuery (  
            "UPDATE Table SET Col1=@param1 WHERE Id = @Id ",
            CommandType.Text,
            new []{ 
                dbProvider.GetParameter("@param1", someValue) ,
                 dbProvider.GetParameter("@id", someId) 
            });


    dal.ExecuteScalar (  
        "SELECT SomeValue FROM TABLE WHERE Id = @Id ",
        CommandType.Text,
        new []{ 
             dbProvider.GetParameter("@id", someId) 
        });
  1. Selecting records from the database?

We prefer working with objects instead of datatables, to enable this functionality we require and implementation of IRowMapper<T> to be provided when we Read data. We are using the IRowMapper implementation as originally implemented by Enterprise Library's Data Access Block

  ``` 
   const string SQL = @"SELECT Id, Code, Name FROM STATE s where s.Id = @Id";
	var records = dal.Read<State>(StateMapper, SQL, CommandType.Text, new[]
			{
				dbProvider.GetDbParameter("@Id", id)
			});
  ```

IRowMapper<T> Examples

Note: Please review documentation from Enterprise Libary Data Access Block to Find out more about IRowMapper

 // Map all properties on the object.
private static readonly IRowMapper<State> StateMapper = MapBuilder<State>.BuildAllProperties(); 

 // Map all properties by name ... override how we map name
private static readonly IRowMapper<State> StateRowMapper = MapBuilder<State>
            .MapAllProperties()
            .Map(x => x.Name).WithFunc(x => x["Name"].ToString())
            .Build(); 

 // Manually map some/all properties
private static readonly IRowMapper<State> StateRowMapper2 = MapBuilder<State>
        .MapNoProperties()
        .MapByName(x => x.Id)
        .Map(x => x.Code).ToColumn("Code")
        .Map(x => x.Name).WithFunc(x => x["Name"].ToString())
        .Build(); 

Working with IRepository<T> and AbstractSimpleRepository

  1. Create a BaseRepository that extends AbstractSimpleRepository
    public class BaseSqlRepository : AbstractSimpleRepository
    {
        public override sealed ISimpleDataAccessLayer Database { get; set; }

         public BaseSqlRepository()
         {
            var dbProvider = new SqlServerProvider("Your SQL server Connection String");
            
            Database = new SimpleDataAccessLayer(dbProvider); 
         }
    }
  1. Create your Repository class by extending BaseSqlRepository
    public class StateRepository : BaseSqlRepository, IStateRepository
    {
         // Please review documentation from Enterprise Libary Db Accessors to Find out more about IRowMapper
         
         private static readonly IRowMapper<State> StateMapper = MapBuilder<State>.BuildAllProperties();

         // sample 2
         private static readonly IRowMapper<State> StateRowMapper = MapBuilder<State>.MapAllProperties().Build();

          // sample 3 for mapping
         private static readonly IRowMapper<State> StateRowMapper2 = MapBuilder<State>
                   .MapNoProperties()
                   .MapByName(x => x.Id)
                   .Map(x => x.Code).ToColumn("Code")
                   .Map(x => x.Name).WithFunc(x => x["Name"].ToString())
                   .Build();



		public State GetById(int id)
		{
				const string SQL = @"SELECT Id, Code, Name FROM STATE s where s.Id = @Id";

				return Read<State>(StateMapper, SQL, CommandType.Text, new[]
				{
					GetDbParameter("@Id", id)
				}).FirstOrDefault();
		}


		public State GetAll(int id)
         	{
			const string SQL = @"SELECT Id, Code, Name FROM STATE s  ORDER BY s.Name ";

			return Read<State>(StateMapper, SQL, CommandType.Text, null).FirstOrDefault();
         	}


		public DataTable ReadById(int id)
		{
			const string SQL = @"SELECT Id, Code, Name FROM STATE s where s.Id = @Id";

			return Read(SQL, CommandType.Text, new[]
			{
				GetDbParameter("@Id", id)
			});
		}


		public State Create(State state)
		{
			const string SQL = @"INSERT INTO STATE (Code, Name)
								 VALUES (@Code, @Name);
								 SELECT SCOPE_IDENTITY()
									 ";

			var id = ExecuteScalar(SQL, CommandType.Text, new[]
			{
				GetDbParameter("@Code", state.Code),
				GetDbParameter("@Name", state.Name)
			});

			state.Id = Convert.ToInt32(id);

			return state;
		}


		public State Update(State state)
		{
			const string SQL = @" UPDATE STATE
			SET Code = @Code,
			Name = @Name
			WHERE Id = @Id
			";

			var id = ExecuteNonQuery(SQL, CommandType.Text, new[]
			{
				GetDbParameter("@Code", state.Code),
				GetDbParameter("@Name", state.Name),
				GetDbParameter("@Id", state.Id)
			});

			return state;
		}

    }

Working with database transactions

    using (var connection = dal.GetConnection())
    {
        using (var transaction = connection.BeginTransaction())
        {
            try{
        
                dal.ExecuteNonQuery(connection, ..... , transaction);
                ...     
                dal.ExecuteScalar(connection, ..... , transaction);
                ...
                transaction.Commit();

            }catch(){
                transaction.Rollback();
            }
        }
    }
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 netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen 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
2.0.2 122 1/27/2024
2.0.1 100 1/22/2024
2.0.0 303 2/18/2023
1.0.0 1,115 11/2/2017