public ActionResult Index()
{
var query = db.Users.Select(c => new SelectListItem
{
Value = c.UserId,
Text = c.UserName,
Selected = c.UserId.Equals(3)
});
var model = new MyFormViewModel
{
List = query.ToList()
};
return View(model);
}
public class MyFormViewModel
{
public int UserId { get; set; }
public IList<SelectListItem> List { get; set; }
}
@model MyFormViewModel @Html.DropDownListFor(m => m.UserId, Model.List, "--Select One--")
public ActionResult GetComments(JqGridRequest request, int? nodeid, int? parentid, int? n_level)
if (nodeid == null)
{
productsQuery = productsQuery.Where(x => x.ParentId == null);
} else
{
// آی دی یک گره میتواند کلید خارجی یک جدول دیگر باشد
productsQuery = productsQuery.Where(x => x.ParentId == nodeid.Value);
} using System.Collections.Generic;
namespace EF_Sample08.DomainClasses
{
public class Doctor
{
public int Id { set; get; }
public string Name { set; get; }
public virtual ICollection<Patient> Patients { set; get; }
}
}
namespace EF_Sample08.DomainClasses
{
public class Patient
{
public int Id { set; get; }
public string Name { set; get; }
public virtual Doctor Doctor { set; get; }
}
}
using System.Data.Entity;
using EF_Sample08.DomainClasses;
namespace EF_Sample08.DataLayer.Context
{
public class Sample08Context : DbContext
{
public DbSet<Doctor> Doctors { set; get; }
public DbSet<Patient> Patients { set; get; }
}
}
using System.Data.Entity.Migrations;
using EF_Sample08.DomainClasses;
using System.Collections.Generic;
namespace EF_Sample08.DataLayer.Context
{
public class Configuration : DbMigrationsConfiguration<Sample08Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
}
protected override void Seed(Sample08Context context)
{
addData(context);
addSP(context);
addFn(context);
base.Seed(context);
}
private static void addData(Sample08Context context)
{
var patient1 = new Patient { Name = "p1" };
var patient2 = new Patient { Name = "p2" };
var doctor1 = new Doctor { Name = "doc1", Patients = new List<Patient> { patient1, patient2 } };
context.Doctors.Add(doctor1);
}
private static void addFn(Sample08Context context)
{
context.Database.ExecuteSqlCommand(
@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorPatientsCount]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FindDoctorPatientsCount]");
context.Database.ExecuteSqlCommand(
@"CREATE FUNCTION FindDoctorPatientsCount(@Doctor_Id INT)
RETURNS INT
BEGIN
RETURN
(
SELECT COUNT(*)
FROM Patients
WHERE Doctor_Id = @Doctor_Id
);
END");
}
private static void addSP(Sample08Context context)
{
context.Database.ExecuteSqlCommand(
@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorsStartWith]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FindDoctorsStartWith]
");
context.Database.ExecuteSqlCommand(
@"CREATE PROCEDURE FindDoctorsStartWith(@name NVARCHAR(400))
AS
SELECT *
FROM Doctors
WHERE [Name] LIKE @name + '%'");
}
}
}
using System;
using System.Data;
using System.Data.Entity;
using System.Data.Objects.SqlClient;
using System.Data.SqlClient;
using System.Linq;
using EF_Sample08.DataLayer.Context;
using EF_Sample08.DomainClasses;
namespace EF_Sample08
{
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample08Context, Configuration>());
using (var db = new Sample08Context())
{
runSp(db);
runFn(db);
usingSqlFunctions(db);
}
}
private static void usingSqlFunctions(Sample08Context db)
{
var doctorsWithNumericNameList = db.Doctors.Where(x => SqlFunctions.IsNumeric(x.Name) == 1).ToList();
if (doctorsWithNumericNameList.Any())
{
//do something
}
}
private static void runFn(Sample08Context db)
{
var doctorIdParameter = new SqlParameter
{
ParameterName = "@doctor_id",
Value = 1,
SqlDbType = SqlDbType.Int
};
var patientsCount = db.Database.SqlQuery<int>("select dbo.FindDoctorPatientsCount(@doctor_id)", doctorIdParameter).FirstOrDefault();
Console.WriteLine(patientsCount);
}
private static void runSp(Sample08Context db)
{
var nameParameter = new SqlParameter
{
ParameterName = "@name",
Value = "doc",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.NVarChar
};
var doctors = db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith @name", nameParameter).ToList();
if (doctors.Any())
{
foreach (var item in doctors)
{
Console.WriteLine(item.Name);
}
}
}
}
}
db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith "+ txtName.Text, nameParameter).ToList()
