-
Notifications
You must be signed in to change notification settings - Fork 2
Using Needletail tools
To build the source code you will need to add references to the following dlls:
- Microsoft.SqlServer.Types
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.Management.Sdk.Sfc.dll
- Microsoft.SqlServer.Smo.dll
- System.Data.SqlServerCe.dll
A tool to manage database migrations as part of your application, you can use this tool to initialize migrate and seed the database, very simple to use and understand.
Add a connection string.
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;User ID=squirrel;Password=squirrelsa"
For this sample, we are going to use the Northwind database that can be downloaded from here.
Now we need to add a couple of classes for our POCO entities:
public class Order
{
[TableKey(CanInsertKey = false)]
public int OrderID { get; set;}
public string CustomerID { get; set;}
public int EmployeeID { get; set;}
public DateTime OrderDate { get; set;}
public DateTime RequiredDate { get; set;}
public DateTime? ShippedDate { get; set;}
public int ShipVia { get; set;}
public decimal Freight { get; set;}
public string ShipName { get; set;}
public string ShipAddress { get; set;}
public string ShipCity { get; set;}
public string ShipRegion { get; set;}
public string ShipPostalCode { get; set;}
public string ShipCountry { get; set; }
}
public class OrderDetails
{
public int OrderID { get; set; }
public int ProductID { get; set; }
public decimal UnitPrice { get; set; }
public int Quantity { get; set; }
public float Discount { get; set; }
}
*The [TableKey] attribute indicates which field/property is the primary key in the database, you need to indicate if the primary key is automatically created using the “CanInsertKey” variable.
To access the Orders table we need to instantiate an object indicating the connection string and the table.
var needleTailContext = new DBTableDataSourceBase<order int ,>("connectionString", "Orders");
//Or
using (var needleTailContext = new DBTableDataSourceBase<order, int >("connectionString", "Orders"))
{
//the code to access the DB here
}
You don't need to indicate if the database is MSSQL, SQL CE or MySQL, Needletail will take care of that.
//Get all rows
var orders = needleTailContext.GetAll();
//Get a single row
var order = needleTailContext.GetSingle( where : new { OrderID = 11072 });
//Get many rows
var ordersS = needleTailContext.GetMany(where: new { CustomerID_Like = "LIN" });
//More filters and ordering
var ordersS = needleTailContext.GetMany(where: new { CustomerID_Like = "INO", Or_CustomerID_StartsWith = "FR" }, orderBy: new { OrderDate = "DESC" });
//Adding a new row
var sNewO = new Order { CustomerID = "BONAP", EmployeeID = 4, OrderDate = DateTime.Now, RequiredDate = DateTime.Now.AddDays(20), ShipVia = 1, Freight = 120, ShipName = "12, rue des Bouchers", ShipCity = "Marseille", ShipPostalCode = "13008", ShipCountry = "France" };
sNewO.OrderID = needleTailContext.Insert(sNewO);
//Updating a row, use this when you want to update many rows or when don't want to update all the row's columns
needleTailContext.UpdateWithWhere(values: new { Quantity = 10 }, where: new { OrderID = 11065 });
//Use this when you want to update a single row with all its properties, this can only be used if you defined a TableKeyneedleTailContext.Update(currentOrder);
//Delete one or many rows
needleTailContext.Delete(where: new { OrderID = sNewO.OrderID });
//Joins, this returns a DynamicEntity objects, this is easy to use but has performance issues.
var items = needleTailContext.Join("Orders.OrderID,Orders.CustomerID,Orders.OrderDate,[Order Details].ProductID,[Order Details].UnitPrice,[Order Details].Quantity","Inner Join [Order Details] on Orders.OrderID = [Order Details].OrderID AND Orders.CustomerID like '%INE%'", string.Empty,string.Empty, null);
//Joins using typed entities(user defined classes), this way is very fast but you need to define classes.
var items = needleTailContext.JoinGetTyped<orderdetailsjoin>("Orders.OrderID,Orders.CustomerID,Orders.OrderDate,[Order Details].ProductID,[Order Details].UnitPrice,[Order Details].Quantity","Inner Join [Order Details] on Orders.OrderID = [Order Details].OrderID AND Orders.CustomerID like '%INE%'", string.Empty,string.Empty, null);
The following are prefixes and suffixes that you can add to the field name in the where
- _EndsWith example:
var ordersS = needleTailContext.GetMany(where: new { CustomerID_EndsWith = "LIN" }); - _StartsWith example:
var ordersS = needleTailContext.GetMany(where: new { CustomerID_StartsWith = "ABC" }); - _MoreThan example:
var ordersS = needleTailContext.GetMany(where: new { UnitPrice_MoreThan = 10 }); - _LessThan example:
var ordersS = needleTailContext.GetMany(where: new { UnitPrice_LessThan = 5 }); - _Like example:
var ordersS = needleTailContext.GetMany(where: new { CustomerID_Like = "LIN" }); - _Not example:
var ordersS = needleTailContext.GetMany(where: new { City_Not = "Seattle" }); - And_ example:
needleTailContext.GetMany(where: new { CustomerID_Like = "INO", And_CustomerID_StartsWith = "FR" }) - Or_ example:
needleTailContext.GetMany(where: new { CustomerID_Like = "INO", Or_CustomerID_StartsWith = "FR" }) - In_ example:
needleTailContect.GetMay(where : new {CustomerID_In = new string[] {"KEY1","KEY2"} } );
AND clause.
Using DataAccess Migrations its very easy, just create a “Migrations” folder in your application’s root folder and add sql scripts to initialize and patch your application, these are the conventions:
- Use the file “InitializeDatabase.sql” for the script that will initialize your database, you can create this script using the Microsoft SQL Server Management Studio.
- Use “Patch_x.sql” for patches, the “x” should be an integer, this number also determines the order on which the script will be executed.
Important: All the scripts should be copied to the output directory, you can do this easily changing document properties, just set the “CopyToOutputDirectory” property to “Copy Always”.
Migrations use some Microsoft libraries that are Microsoft.Net framework 2.0 specific, you will need to add the following code in your web.config or app.config file:
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
If you add a Patch_xx.sql file and you have not defined an InitializeDatabase.sql file, it will be assumed that your database has already been initialized and the initialize script will never run.
Once you have all the scripts that you need, just run the following code:
Needletail.DataAccess.Migrations.Migrator.Migrate("ConnectionString");to run the migrations, a good place to put this code, is in Global.asax when your application is starting.