ASP NET.MVCレッスン3.データベースの操作

レッスンの目的 ：データベースを操作する基本的な原則を学ぶ。 リレーショナルデータベースモデルの簡単な説明。 データベースを操作します（VS 2012でのテーブル、リンクの作成）。 コマンドINSERT、UPDATE、DELETE、SELECT。 LinqToSqlとLinqを使用します。 リポジトリ、IRepository、SqlRepositoryの作成。

DBとは

リレーショナルデータベースは、リレーショナルデータモデルに基づくデータベースです。 関係性は、英語からの関係（関係）です。 関係。

テーブル

これは表です：





テーブルは列と行で構成されます。 列にはプロパティがあります-名前、データ型。

テーブルには次のプロパティが必要です。

• テーブルには名前があります（一意）
• 2本の線は似ていません
• 列の名前が異なる（同一の列が2つない）
• テーブル内の行の順序は任意です（つまり、並べ替えが指定されていない場合、行の順序を考慮しないでください）

テーブル構造は、次の形式で記述できます。

• 列名
• この列のデータ型

コミュニケーションズ

テーブル間にはリレーション（関係）があります。 接続を確立するには、次のものが必要です。

• 主キーは、行の一意性を一意に決定するテーブルの列（属性）のセットです。 これは通常、IDと呼ばれる単一のフィールドです。 自動インクリメント、つまり エントリを追加しようとすると、1、2、3、4 ... n + 1が自動的に挿入されます。nは最後に追加されたIDの値です。
• 外部キーは、別のテーブルの行の一意性を一意に決定するテーブルの列（属性）のセットです。 繰り返しますが、これは通常、[テーブル名] IDと呼ばれる単一のフィールドです。 ただし、自動インクリメントではありません。
• 主キーと外部キーの関係が登録されます。

接続には3つのタイプがあります。

• 1対1。 つまり テーブルの1行は、別のテーブルの1行に対応しています。 ほとんど使用されませんが、使用されます。 たとえば、あるテーブルにはユーザーに関するデータがあり、別のテーブルには同じユーザーに関する追加データがあります。 このオプションは、必要に応じて、より少ないデータを操作するために必要です。
• 1対多。 テーブルAの1行はテーブルBの1つ以上の行に対応します。ただし、テーブルAの1行のみがテーブルBの1行に対応します。この場合、テーブルAのエントリを一意に識別する外部キーがテーブルBにあります。
• 多対多。 テーブルAの1つの行は、テーブルBの1つ以上の行に対応します。これは逆です。 この場合、主キーと、テーブルAおよびBへの2つの外部キーを使用して、追加のテーブルが作成されます。

それでは、その方法を理解しましょう。

データベースに単純なスキーマを作成する

VS 2012でデータベースを作成しましょう。







それをLessonProjectと呼び、3つのRole UserテーブルとUserRoleテーブルを追加しましょう。

ロールテーブルを作成します。











文字列値には、nvarchar（n）型を使用します。nは、文字列の最大長で、通常は50〜500です。大きなテキスト文字列には、nvarchar（MAX）が使用されます。



主キーを設定します。







IDの自動インクリメントを設定します。







同様に、Userテーブルを作成します。

フィールド	フィールドタイプ
ID	int
メール	nvarchar（150）
パスワード	nvarchar（50）
追加日	日時
ActivatedDate	日時（ヌル）
Activatedatedlink	nvarchar（50）
LastVisitDate	日時（ヌル）
アバターパス	nvarchar（150）（null）

UserRoleテーブルを作成します。

フィールド	フィールドタイプ
ID	int
ユーザーID	int
ロリド	int

リンクを追加します。







[追加]をクリックして新しいリンクを追加します。 外部キーが配置されているテーブルで関係を追加します。 [テーブルと列]タブを開き、テーブルにプライマリキーを設定し、現在のUserRoleテーブルで外部キーを選択します。







INSERTおよびUPDATE仕様プロパティで、更新時/削除時のカスケード時プロパティを設定します。







これは、Roleテーブルから列を変更/削除するときに、この行に関連付けられているUserRoleテーブルのすべての行を変更または削除するために必要です。



Userテーブルと同様の関係を確立します。



したがって、RoleテーブルとUserテーブルには、UserRoleテーブルを介した多対多の関係があります。 つまり 1人のユーザーが複数の役割を持つことができ、複数のユーザーが同じ役割を持つことができます。

SELECT、INSERT、UPDATE、DELETE。

リレーショナルデータベースは、SQLクエリ言語を使用します。



データ操作には、SELECT、INSERT、UPDATE、DELETEの4つの主要なコマンドがあります



SELECT-データとテーブルを選択します。

例：

SELECT * FROM User
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

INSERT-テーブルへの行の追加

例：

 INSERT INTO Role (Code, Name) VALUES (“admin”, “”)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

更新-テーブル内の値の変更

例：

 UPDATE User SET Password=”password1” WHERE ID=1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

DELETE-テーブルから行を削除します

例：

 DELETE FROM User WHERE ID =1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

注：リンクでSQLの詳細を確認できます。

http://www.w3schools.com/sql/

http://codingcraft.ru/sql_queries.php

LinqToSQLおよびLinq。

ClassLibraryタイプのデータベースを操作するためのLessonProject.Modelプロジェクトを作成しましょう。

LINQをSQLクラスタイプに追加し、LessonProejctDb.dbmlを呼び出します







オブジェクトを開き、すべてのテーブルを選択して、マウスでキャンバスに転送します。







実際、このような単純なアクションの助けを借りて、次のようになります。

• データベースを操作する際に使用できるクラス
• テーブルとリンクの視覚的表示

Role and Userテーブルにデータを追加します。

1	管理者	管理者
2	顧客	お客様

1	chernikov@gmail.com	123456	1/1/2012 12:00:00 AM	ヌル	123456	ヌル	ヌル
2	chernikov2@gmail.com	123456	1/1/2012 12:00:00 AM	ヌル	123456	ヌル	ヌル

そしてUserRole

ID	ユーザーID	ロリド
1	1	1
2	1	2
3	2	2

コンソールプロジェクトLesson3を作成し、LessonProject.Modelに接続します。 アセンブリSystem.ConfigurationおよびSystem.Data.Linqを追加します。 コンテキストを初期化し、ロールデータを表示します。

  class Program { static void Main(string[] args) { var context = new LessonProjectDbDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); var roles = context.Roles.ToList(); foreach (var role in roles) { Console.WriteLine("{0} {1} {2}", role.ID, role.Code, role.Name); } Console.ReadLine(); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ロールに行を追加するには、次を実行します。

 var newRole = new Role { Code = "manager", Name = "" }; context.Roles.InsertOnSubmit(newRole); context.Roles.Context.SubmitChanges();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ロールの行を削除するには、次の操作を行います。

 var role = context.Roles.Where(p => p.Name == "").FirstOrDefault(); if (role != null) { context.Roles.DeleteOnSubmit(role); context.Roles.Context.SubmitChanges(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データを変更するには、次の操作を行います。

 var role = context.Roles.Where(p => p.Name == "").FirstOrDefault(); if (role != null) { role.Name = ""; context.Roles.Context.SubmitChanges(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データ操作には、Linqクエリ言語が使用されます。 Linqの基本的な機能の一部のみを検討します。 Linqは、IQueryableインターフェイスを実装する型に適用されます<>

• .Where（）はメインのフィルタリング関数です。 IQueryable型を返します。 内部の条件はブール値（bool）を返す必要があります。
  
  

   var roles = context.Roles.Where(p => p.Name == "")
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  
  
  
• .FirstOrDefault（）-.First（）、. Single（）、. SingleOrDefault（） -最初または唯一のレコードを取得します。 レコードがない場合、FirstOrDefault（）またはSingleOrDefault（）はnullを返します（実際、このタイプのデフォルト値は[default（int）]などです）。
  
  

    var roles = context.Roles.Where(p => p.Name == "").FirstOrDefault()
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -「Manager」というロールを最初に取得します（または取得しません）。
  
  
• .Take（） -最初のN個のレコードを選択します
  
  

   var roles = context.Roles.Where(p => p.Name == "").Take(4)
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -最初の4つのエントリを選択します
  
  
• .Skip（） -N個の最初のレコードの選択をスキップします
  
  

   var roles = context.Roles.Where(p => p.Name == ""). Skip(2).Take(3)
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -最初の2つをスキップし、次の3つのエントリを選択します
  
  
• .OrderBy（） -昇順でソートします。 また、OrderByDescending（）、ThenBy（）、ThenByDescending（）もあります。 ラムダ式は、ソートが行われることに応じて、int型を返す必要があります。
  
  

   var roles = context.Roles.Where(p => p.Name == "").OrderBy(p => p.ID)
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -順番に並べ替え
  
  
• .Count（）-レコード数を取得します
  
  

   var rolesCount = context.Roles.Where(p => p.Name == "").Count()
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -レコード数
  
  
• .Any（） -この条件には1つ以上のエントリが存在します
  
  var rolesExist = context.Roles.Where（p => p.Name == "Manager"）。Any（）-そのようなレコードがあります
  
  
• 。 Select（） -任意の型のIQueryableを返します。動的な場合もあります：
  
  

   var otherRole = context.Roles.Where(p => p.Name == "").Select(p => new { ID = p.ID, Kod = p.Code})
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -ロールに基づいて形成された動的タイプを取得します。
  
  
• .SelectMany（） -選択範囲内のすべてのIQueryable型の和集合を返します。
  
  

   var otherRole = context.Roles.Where(p => p.Name == "").SelectMany(p => p.UserRoles)
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -「Manager」というロールからすべてのUserRoleを取得します
  
  
• .Distinct（） -重複を削除します
  
  

   var managers = context.Roles.Where(p => p.Name == "").SelectMany(p => p.UserRoles).Select(p => p.User).Distinct()
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  -「マネージャー」という役割を持つすべてのユーザー
  
  

注： First(), FirstOrDefault(), Single(), SingleOrDefault(), Any(), Count()



- Where()



対応するパラメーターを適用できるため、エントリが減少します。

 var roles = context.Roles.FirstOrDefault(p => p.Name == "")
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

linqのより多くの例と使用法を見つけることができます。

http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

IRepositoryリポジトリ、SqlRepositoryの作成。

実際、すでにデータベースを操作することができますが、今は特定の実装からデータモデルを分離する必要があります。 コンテキストとSystem.Data.Linqについてのコントローラーは、何も知る必要がありません。



これを行うには、データへのアクセスが許可されるIRepositoryインターフェイスを作成し、このデータを作成、変更、および削除するためのメソッドを導出します。

  public interface IRepository { IQueryable<Role> Roles { get; } bool CreateRole(Role instance); bool UpdateRole(Role instance); bool RemoveRole(int idRole); … }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実装をSqlRepositoryと呼びます。 SqlRepositoryをこのコンテキストに関連付けたくないので、NinjectモジュールをLessonProject.Modelプロジェクトに追加します。

 Install-Package Ninject
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SqlRepositoryクラスを作成します。

  public class SqlRepository : IRepository { [Inject] public LessonProjectDbDataContext Db { get; set; } public IQueryable<Role> Roles { get { throw new NotImplementedException(); } } public bool CreateRole(Role instance) { throw new NotImplementedException(); } public bool UpdateRole(Role instance) { throw new NotImplementedException(); } public bool RemoveRole(int idRole) { throw new NotImplementedException(); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

すべてのテーブルへのアクセス、作成、削除、および変更を実現する前に、このファイルは大きくて扱いにくいと思うでしょう。 このようなコードは、管理が物理的に困難です。 そのため、部分的に作成するSqlRepositoryクラスとSqlRepositoryクラスを個別のフォルダーに作成し、フォルダー内でIRepositoryインターフェイスの実装を作成し、各テーブルに分解します。 ロールファイルに名前を付けましょう：

  public partial class SqlRepository { public IQueryable<Role> Roles { get { return Db.Roles; } } public bool CreateRole(Role instance) { if (instance.ID == 0) { Db.Roles.InsertOnSubmit(instance); Db.Roles.Context.SubmitChanges(); return true; } return false; } public bool RemoveRole(int idRole) { Role instance = Db.Roles.FirstOrDefault(p => p.ID == idRole); if (instance != null) { Db.Roles.DeleteOnSubmit(instance); Db.Roles.Context.SubmitChanges(); return true; } return false; } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

小さなプロジェクトには10​​〜40のテーブルが含まれ、大きなプロジェクトには40のテーブルが含まれます。これらすべてを何らかの形で自動化したいと思います。 IRepositoryとSqlRepositoryのスニペットを作成してみましょう。 スニペットは、intelliSenceを使用して呼び出される定義済みのコードテンプレートであり、コードをすばやく作成できます。

スニペット

IRepositoryテーブルの場合、table.snippetを作成します。

 <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0" > <Header> <Title> Table </Title> <Shortcut>Table</Shortcut> </Header> <Snippet> <Declarations> <Literal> <ID>Table</ID> <ToolTip>Table name for create.</ToolTip> <Default>Table</Default> </Literal> </Declarations> <Code Language="CSharp"> <![CDATA[ #region $Table$ IQueryable<$Table$> $Table$s { get; } bool Create$Table$($Table$ instance); bool Update$Table$($Table$ instance); bool Remove$Table$(int id$Table$); #endregion ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SqlRepositoryの場合、sqlTable.snippetスニペットを作成します。

 <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0" > <Header> <Title> Sql repository </Title> <Shortcut>sqltable</Shortcut> </Header> <Snippet> <Declarations> <Literal> <ID>Table</ID> <ToolTip>Table name for create.</ToolTip> <Default>Table</Default> </Literal> </Declarations> <Code Language="CSharp"> <![CDATA[ public IQueryable<$Table$> $Table$s { get { return Db.$Table$s; } } public bool Create$Table$($Table$ instance) { if (instance.ID == 0) { Db.$Table$s.InsertOnSubmit(instance); Db.$Table$s.Context.SubmitChanges(); return true; } return false; } public bool Update$Table$($Table$ instance) { $Table$ cache = Db.$Table$s.Where(p => p.ID == instance.ID).FirstOrDefault(); if (cache != null) { //TODO : Update fields for $Table$ Db.$Table$s.Context.SubmitChanges(); return true; } return false; } public bool Remove$Table$(int id$Table$) { $Table$ instance = Db.$Table$s.Where(p => p.ID == id$Table$).FirstOrDefault(); if (instance != null) { Db.$Table$s.DeleteOnSubmit(instance); Db.$Table$s.Context.SubmitChanges(); return true; } return false; } ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

コードスニペットを追加します。 TOOLS-> Code Snippet Managerを開きます...（Ctrl-K、B）。 ウィンドウで、[インポート]をクリックし、両方のスニペットを[マイコード]スニペットにインポートします。







完了、OK。

テーブルUserおよびUserRoleに使用します。











Update



[テーブル名]のフィールドのみを登録することは残っていますが、これはすでに作業が少なくなっています。

プロキシ

ご覧のとおり、使用するクラスは部分的であるため、補足することができます。 SqlRepositoryのように、部分クラスを配置するProxyフォルダーを作成しましょう。 たとえば、Userクラスの場合、ユーザーをアクティブにするために必要な文字列を自動的に生成するメソッドを作成します。

  public partial class User { public static string GetActivateUrl() { return Guid.NewGuid().ToString("N"); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これを使用します：

 public bool CreateUser(User instance) { if (instance.ID == 0) { instance.AddedDate = DateTime.Now; instance.ActivatedLink = User.GetActivateUrl(); Db.Users.InsertOnSubmit(instance); Db.Users.Context.SubmitChanges(); return true; } return false; }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

asp.net mvcでDBを使用する

データベースアクセス行をweb.Configに追加します。

 <connectionStrings> <add name="ConnectionString" connectionString="Data Source=SATURN-PC;Initial Catalog=LessonProject;Integrated Security=True;Pooling=False" providerName="System.Data.SqlClient" /> </connectionStrings>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Ninjectのデータベースを使用して作業を初期化します。

  private static void RegisterServices(IKernel kernel) { kernel.Bind<LessonProjectDbDataContext>().ToMethod(c => new LessonProjectDbDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)); kernel.Bind<IRepository>().To<SqlRepository>().InRequestScope(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

InRequestScope（）を適用します。 つまり 各リクエストは個別のSqlRepositoryオブジェクトを使用します。 これにより、実行中の衝突が回避されますコントローラでIRepositoryを宣言します。

  public class HomeController : Controller { [Inject] public IRepository Repository { get; set; } public ActionResult Index() { var roles = Repository.Roles.ToList(); return View(roles); } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ビューを更新します（/Views/Home/Index.cshtml）：

 @model IList<LessonProject.Model.Role> @{ ViewBag.Title = "LessonProject"; Layout = "~/Views/Shared/_Layout.cshtml"; } <h2>LessonProject</h2> <p> @foreach (var role in Model) { <div class="item"> <span class="id"> @role.ID </span> <span class="name"> @role.Name </span> <span class="Code"> @role.Code </span> </div> } </p>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

良い結果が得られます。







すべてのソースはhttps://bitbucket.org/chernikov/lessonsにあります