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.
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.
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)
Sampledb.Delete (customerdb); // you can also use DeleteList(batch delete) In latest version.
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 ();
[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.
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; }
}
[Repository.Domain.FieldInfo ("CustomProperty", true)]
public int CustomProperty {get; set; }
var result = Sampledb.Execute_StoredProcedure (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.
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
-
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;
-
In the database (default)
There is a stored procedure in the database called __KeyGenerator that must be overwritten.
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");
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, "");
Yes.
For example
List<Customer> lst = new List<Customer>();
for (int i = 0; i < 10000; i++)
{
Customer o2 = new Customer();
o2.FirstName = body.FirstName;
o2.LastName = body.LastName;
lst.Add(o2);
}
Sampledb.SaveList(lst, "ID");
return "1";
Note: If the record exists, it will update it, if it does not exist, it will insert it.
Note: You can specify multiple fields to check, for example: "ID, FirstName".
Yes.
For example
Dictionary<string, string> parameters = new Dictionary<string, string>();
parameters.Add("FirstName", body.FirstName);
//you can not use writeonly object in execute procedure
var result= Sampledb.Execute_StoredProcedure<Customer,Customer,Customer2,ReadonlyCustomer>("SP_GetCustomerDetail", parameters,100);
return (List<Customer>)result.First();
Note: You can not use writeonly object in execute procedure.