Detailed explanation of LINQ to SQL technology for C# database operations

1. Overview of LINQ to SQL Technology

LINQ to SQL is a component of .NET Framework version 3.5 that provides a runtime infrastructure for managing relational data as an object.

In LINQ to SQL, the data model of a relational database is mapped into an object model that refers to an object represented by a programming language used by developers. In simple terms, it is to map the relational data in the database to the instance objects in the code. When the application runs, LINQ to SQL converts language integration queries in the object model into SQL statements and then sends them to the database for execution. When the database returns results, LINQ to SQL converts them back to objects you can process in your own programming language.

【tips】Developers using Visual Studio can use the Object Relational Designer, which provides a user interface for implementing many features of LINQ to SQL.

2. Use LINQ to SQL to perform simple operations.

By using LINQ to SQL, accessing a SQL database is like accessing a collection in memory.

The following code creates a Northwnd object, representing the northwnd database, and query the data with the city "London" from the Customers table, return a collection, assign it to companyNameQuery, and then output elements in the collection through a loop.

// Northwnd inherits from .
Northwnd nw = new Northwnd(@"");
// or, if you are not using SQL Server Express
// Northwnd nw = new Northwnd("Database=Northwind;Server=server_name;Integrated Security=SSPI");

var companyNameQuery =
    from cust in 
    where  == "London"
    select ;

foreach (var customer in companyNameQuery)
{
    (customer);
}

Through the above examples, you have already realized the power of LINQ to SQL technology. Next, I will lead you to gradually absorb LINQ to SQL technology.

3. How to create a database dynamically

In LINQ to SQL technology, the object model and the relational database are mapping relationships. To enable mapping relationships, you can generally use feature-based mappings, or use external mapping files that describe the structure of the relational database. In both cases, if you have enough information about a relational database, you can create a database instance by using the() method.

() method, only create a copy of the database within the scope of the encoded information in the object model. Mapping files and features in the object model will not encode everything in the existing database structure. Mapping information does not represent the content of user-defined functions, stored procedures, triggers, or check constraints. This behavior is sufficient for various databases.

If a known data provider is available (such as SQL Server 2008), you can use the() method in any case.

→Build an application that is automatically installed on the customer system.
→ Build a client application that requires a local database to save its offline state.

You can also use the() method to use SQL Server by creating a .mdf file or directory name, which depends on your connection string. LINQ to SQL uses a connection string to define the database to be created and the server to which data connection is to be created.

【tips】If possible, please use Windows Integration to connect to the database so that you do not need a password in the connection string.

Case 1:

This example shows how to create a database.

//Before using the method shown in this example, the following two steps must be performed://1. Add a quote;//2. Use using statement to add namespace, otherwise an error will be reported.//   using TableAttribute = ;
//   using ColumnAttribute = ;

//Table feature creates a mapping relationship between a table named DVDTable in the database and this class.[Table(Name = "MyOrders")]
public class Order
{
	//Column feature creates a mapping relationship between the column with the same name in the DVDTable table and the attributes of the member of the class.	[Column(IsPrimaryKey = true)]
	public int id;
	[Column]
	public string name;
	[Column]
	public DateTime date;

	public override string ToString()
	{
		return "OrderObject:{" + id + "，" + name + "," + date + "}";
	}
}

//Custom class, inherit the DataContext class.public class MyOrders : DataContext
{
    public MyOrders(String connection) : base(connection)
    {
    }

    public Table&lt;Order&gt; orders;
}

//Create an object db of the MyOrders class, which is inherited from the DataContext class.MyOrders myOrders = new MyOrders(@"D:\");

public void CreateDatabase()
{
    if (())
    {
        //First determine whether the database file exists in the path, and if it exists, delete it.        ("Deleting old database...");
        ();
    }
    //myOrders object creates database    ();
    //Release DataContext instance, all resources used    //();
}

【Notice】

MyOrders myOrders = new MyOrders(@"D:\");

It is best to set the myOrders object in the statement to a global variable. If it is set to a local variable, the "This database already exists, please select another database name" exception may appear when repeatedly calling the method.

When building an application that is automatically installed on the customer system, first check if the database already exists and delete it before creating a new database. The DataContext class provides DatabaseExists() and DeleteDatabase() methods to help you complete this process.

4. How to insert data into the database

You can insert rows into the database by adding objects to the associated LINQ to SQL and submitting changes to the database. LINK to SQL translates the changes you committed into the appropriate SQL insert statement, and then commits them to the database for execution.

【tips】You can also reload the default insert database, Update database, Delete database and other operations in LINQ to SQL.

The following steps assume that there is a DataContext connection to the Northwind database.

1. Create a new object containing the column data to be submitted.
2. Add the new object to the LINQ to SQL Table collection associated with the target table in the database.
3. Submit changes to the database.

The following code shows creating a new Order object (Order is a user-defined class) and then populating it with the appropriate property values. Then add the new Order object to the Order collection. Finally, commit the changes to the database as a new row in the Orders table.

// Create a new Order  class is user defined class.
Order ord = new Order
{
    id = 12000,
    name = "Harry Potty",
    OrderDate = 
    // …
};

// Add the new object to the Orders collection.
&lt;Order&gt; table = &lt;Order&gt;();
(ord);

// Submit the change to the database.
try
{
    ();
}
catch (Exception e)
{
    ();
    // Make some adjustments.
    // ...
    // Try again.
    ();
}
//Release resources//();

[Note] When inserting data into tables in the database or subsequent update or delete operations, no matter how many changes you make to the object, you are just changing the copy in memory. You have not made any changes to the actual data in the database. The changes you make are not transferred to the server until you explicitly call the SubmitChanges() method in the DataContext.

5. How to query data from the database

Queries in LINQ to Queries in SQL use the same syntax as those in LINQ. The only difference is that the objects referenced in the LINQ to SQL query map to elements in the database. Friends who are interested can check out the LINQ query knowledge.

The following code shows a simple query request, querying an order with an id equal to 12000, and outputting the query result.

MyOrders myOrders = new MyOrders(@"c:\");
&lt;Order&gt; table = &lt;Order&gt;();

// Query for customers in London.
IQueryable&lt;Customer&gt; custQuery =
    from order in 
    where  == 12000
    select order;

//Output the query resultforeach( var o in result)
{
	(());
}

There are more complex operations for querying data from the database. I will continue to organize more detailed database query operations in the future. You can pay attention to it first.

6. How to update the data in the table

By modifying the member values of the object associated with the Table collection of LINQ to SQL and then submitting the changes to the database, you can update a row of data in the database. LINQ to SQL Convert your changes to the corresponding SQL update statement.

The steps to update a row of data to the database are as follows:

1. Query the rows to be updated in the database.

2. Make the required changes to the member values in the LINQ to SQL query results in step 1.

3. Submit the changes to the database.

The following example querys order #11000 in the database and then changes the values of name and date in the result order object. Finally, changes to these member values are submitted to the database as changes in the name and date columns.

// Query the database for the row to be updated.
var query =
    from ord in 
    where == 11000
    select ord;

// Execute the query, and change the column values
// you want to change.
foreach (Order ord in query)
{
     = "Hong Lou Meng";
     = ;
    // Insert any additional changes to column values.
}

// Submit the changes to the database.
try
{
    ();
}
catch (Exception e)
{
    ((e));
    // Provide for exceptions.
}
//Release the resources occupied by the DataContext object.//();

7. How to delete the data in the table

Deletion of a row in the database can be completed by deleting the corresponding LINQ to SQL object from the collection associated with the table. LINQ to SQL converts the delete operation into the appropriate SQL delete statement.

LINQ to SQL does not support or recognize cascading deletion operations. If you want to delete a row with constraints in the table, you must complete one of the following conditions:

1. Set the ON DELETE CASCADE rule in the foreign key constraints of the database.

2. Use your own code to delete the child object that prevents the parent object from being deleted first.

If at least one of the above conditions cannot be guaranteed to be true, an exception will be thrown. See the second code example for this topic.

The operation of deleting a row in the database is as follows:

1. Query the rows that need to be deleted in the database.
2. Call the DeleteOnSubmit method.
3. Submit the deletion operation to the database.

The 1st code example below queries the database for order#11000 order details, marks these order details as deleted, and submits these changes to the database.

Table<Order> orders = <Order>();
// Query the database for the rows to be deleted.

var deleteResult =
    from ord in orders
    where  == 11000
    select ord;

foreach (var ord in deleteResult )
{
    (ord );
}

try
{
    ();
}
catch (Exception e)
{
    ();
    // Provide for exceptions.
}

In the second code example, the goal is to delete an order (#10250). The code first checks the OrderDetails table to see if the order to be deleted has suborders there. If the order has a sub-order, first mark the sub-order as to be deleted and then mark the order as to be deleted. DataContext arranges the actual deletions in the correct order so that the deletion commands sent to the database comply with database constraints.

//Northwnd myOrders = new Northwnd(@"c:\");

MyOrders myOrders = new MyOrders(@"D:\");

 = ;

// Specify order to be removed from database
int reqOrder = 10250;

// Fetch OrderDetails for requested order.
var ordDetailQuery =
    from odq in 
    where  == reqOrder
    select odq;

foreach (var selectedDetail in ordDetailQuery)
{
    (());
    (selectedDetail);
}

// Display progress.
("detail section finished.");

// Determine from Detail collection whether parent exists.
if (())
{
    ("The parent is present in the Orders collection.");
    // Fetch Order.
    try
    {
        var ordFetch =
            (from ofetch in 
             where  == reqOrder
             select ofetch).First();
        (ordFetch);
        ("{0} OrderID is marked for deletion.", );
    }
    catch (Exception e)
    {
        ();
    }
}
else
{
    ("There was no parent in the Orders collection.");
}

// Display progress.
("Order section finished.");

try
{
    ();
}
catch (Exception e)
{
    ();
}

// Display progress.
("Submit finished.");

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.