locked
C# 2010 and datacontext object RRS feed

  • Question

  • I have a C#.net 2010 new application that I am writing where I use linq to sql so I can work with the datacontext object. On the first menu tab page, I update the 'main'table with default values and values the user enters. The user clicks the next button the user is allowed to change some values based upon when customers submitted work. Basically some of the dates that are loaded on the first page come off of a schedule due date table. If the customer submits various parts of the paperwork late, the user changes the dates in the database.

      So the question is if a date was loaded into a sql server 2008 r2 datqabase, and the user finds that they need to change the value to NULL is that allowed? Basically on some of the paperwork that is supplied by the customer if they do not supply a date, the data needs to be set to NULL. Thus in the application how  do I pass the null value to the database via the datacontext object? Do I pass the value as '' or 'NULL', or some other value?

    Thus can you tell me how to pass this Null value to the appropriate column in the database?

    • Moved by Leo Liu - MSFT Wednesday, November 23, 2011 6:37 AM Moved for better support. (From:Visual C# Language)
    Wednesday, November 23, 2011 1:30 AM

Answers

  • Hi wendy;

    To your question, "if a date was loaded into a sql server 2008 r2 datqabase, and the user finds that they need to change the value to NULL is that allowed?", Yes you can change a date field in the database to null as long as the Date field in the database is set to nullable.

    To your question, "Thus in the application how do I pass the null value to the database via the datacontext object? Do I pass the value as '' or 'NULL', or some other value?", As long as the database field is nullable you pass the value, null, all lowercase characters.
     
    To your question, "Thus can you tell me how to pass this Null value to the appropriate column in the database?", lets assume that we are using the Microsoft sample Northwind database. The following code snippet shows how to set the ShippedDate to a null value.

    DataContext db = new DataContext();
    
    var query = (from o in db.Orders
                 where o.ShippedDate == null && o.OrderID == 11008
                 select o).FirstOrDefault( );
    
    query.ShippedDate = null;   // Make the ShippedDate columns null
    
    db.SubmitChanges();         // Save the changes to the database.
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, November 23, 2011 5:09 PM
  • > I am not using the entity framework and MVC. Thus how would this code be differrent to pass a null the database using linq to sql?


     
    take a look at the following example. at the method Update.
     
       

    using System;
    using System.Data.Entity;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Linq;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            [STAThread]
            static void Main(string[] args)
            {
                Create();
                Update();
            }
            static void Create()
            {
                var d = new Data(@".\SQLEXPRESS");
                if (d.DatabaseExists() == false)
                    d.CreateDatabase();
    
                var et = d.GetTable<Entity>();
                et.InsertOnSubmit(new Entity() { Date = DateTime.Now });
                d.SubmitChanges();
            }
            static void Update()
            {
                var d = new Data(@".\SQLEXPRESS");
                var et = d.GetTable<Entity>();
                var r = et.OrderByDescending(e => e.Id).First();
                r.Date = null;     // 
                d.SubmitChanges();
            }
        }
        [Database(Name = "Test.Data")]
        public class Data : DataContext
        {
            public Data(string path) : base(path) { }
            public Table<Entity> Entities; // for db generation
        }
        [Table(Name = "Entity")]
        public class Entity
        {
            [Column(IsPrimaryKey = true, IsDbGenerated = true)]
            public int Id { get; set; }
            [Column(CanBeNull = true)]
            public DateTime? Date { get; set; }
        }
    }
    

     

    below is almost the same code for those who uses EF Code First
     
    using System;
    using System.Data.Entity;
    using System.Data.Linq.Mapping;
    using System.Linq;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            [STAThread]
            static void Main(string[] args)
            {
                Create();
                Update();
            }
            static void Create()
            {
                var d = new Data();
                d.Database.CreateIfNotExists();
                d.Entities.Add(new Entity() { Date = DateTime.Now });
                d.SaveChanges();
            }
            static void Update()
            {
                var d = new Data();
                var r = d.Entities.OrderByDescending(e => e.Id).First();
                r.Date = null;     // 
                d.SaveChanges();
            }
        }
        public class Entity
        {
            public int Id { get; set; }
            public DateTime? Date { get; set; }
        }
        public class Data : DbContext
        {
            public DbSet<Entity> Entities { get; set; }
            protected override void OnModelCreating(DbModelBuilder mb)
            {
                base.OnModelCreating(mb);
                mb.Entity<Entity>().HasKey(t => t.Id).ToTable("Entities");
            }
        }
    }
    
      
    • Edited by Malobukv Thursday, November 24, 2011 3:01 AM
    • Marked as answer by wendy elizabeth Friday, November 25, 2011 3:12 AM
    Thursday, November 24, 2011 3:00 AM

All replies

  • > if they do not supply a date, the data needs to be set to NULL. Thus in the application how do I pass the null value to the database via the datacontext object? Do I pass the value as '' or 'NULL', or some other value?
     

    you can use Nullable type; take a look at the public DateTime? Date { get; set; } below.
     
    using System;
    using System.Data.Entity;
    
    namespace MvcApplication2.Models
    {
        public class Entity
        {
            public int Id { get; set; }
            public DateTime? Date { get; set; }
        }
        public class Data : System.Data.Entity.DbContext
        {
            public DbSet<Entity> Entities { get; set; }
            protected override void OnModelCreating(DbModelBuilder mb)
            {
                base.OnModelCreating(mb);
                mb.Entity<Entity>().HasKey(t => t.Id).ToTable("Entities");
            }
        }
    }
    
    using System.Data;
    using System.Data.Entity;
    using System.Web.Mvc;
    using MvcApplication2.Models;
    
    namespace MvcApplication2.Controllers
    {
        public class HomeController : Controller
        {
            public ActionResult Index()
            {
                var d = new Data();
                d.Entities.Add(new Entity() { Date = null });
                d.SaveChanges();
                ...
    


    • Edited by Malobukv Wednesday, November 23, 2011 3:17 AM
    Wednesday, November 23, 2011 2:48 AM
  • Hi Wendy,

    I am moving your thread into the LINQ to SQL Forum for dedicated support.
    Have a nice day,

    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, November 23, 2011 6:38 AM
  • I am not using the entity framework and MVC. Thus how would this code be differrent to pass a null the database using linq to sql?
    Wednesday, November 23, 2011 3:25 PM
  • Hi wendy;

    To your question, "if a date was loaded into a sql server 2008 r2 datqabase, and the user finds that they need to change the value to NULL is that allowed?", Yes you can change a date field in the database to null as long as the Date field in the database is set to nullable.

    To your question, "Thus in the application how do I pass the null value to the database via the datacontext object? Do I pass the value as '' or 'NULL', or some other value?", As long as the database field is nullable you pass the value, null, all lowercase characters.
     
    To your question, "Thus can you tell me how to pass this Null value to the appropriate column in the database?", lets assume that we are using the Microsoft sample Northwind database. The following code snippet shows how to set the ShippedDate to a null value.

    DataContext db = new DataContext();
    
    var query = (from o in db.Orders
                 where o.ShippedDate == null && o.OrderID == 11008
                 select o).FirstOrDefault( );
    
    query.ShippedDate = null;   // Make the ShippedDate columns null
    
    db.SubmitChanges();         // Save the changes to the database.
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, November 23, 2011 5:09 PM
  • > I am not using the entity framework and MVC. Thus how would this code be differrent to pass a null the database using linq to sql?


     
    take a look at the following example. at the method Update.
     
       

    using System;
    using System.Data.Entity;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Linq;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            [STAThread]
            static void Main(string[] args)
            {
                Create();
                Update();
            }
            static void Create()
            {
                var d = new Data(@".\SQLEXPRESS");
                if (d.DatabaseExists() == false)
                    d.CreateDatabase();
    
                var et = d.GetTable<Entity>();
                et.InsertOnSubmit(new Entity() { Date = DateTime.Now });
                d.SubmitChanges();
            }
            static void Update()
            {
                var d = new Data(@".\SQLEXPRESS");
                var et = d.GetTable<Entity>();
                var r = et.OrderByDescending(e => e.Id).First();
                r.Date = null;     // 
                d.SubmitChanges();
            }
        }
        [Database(Name = "Test.Data")]
        public class Data : DataContext
        {
            public Data(string path) : base(path) { }
            public Table<Entity> Entities; // for db generation
        }
        [Table(Name = "Entity")]
        public class Entity
        {
            [Column(IsPrimaryKey = true, IsDbGenerated = true)]
            public int Id { get; set; }
            [Column(CanBeNull = true)]
            public DateTime? Date { get; set; }
        }
    }
    

     

    below is almost the same code for those who uses EF Code First
     
    using System;
    using System.Data.Entity;
    using System.Data.Linq.Mapping;
    using System.Linq;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            [STAThread]
            static void Main(string[] args)
            {
                Create();
                Update();
            }
            static void Create()
            {
                var d = new Data();
                d.Database.CreateIfNotExists();
                d.Entities.Add(new Entity() { Date = DateTime.Now });
                d.SaveChanges();
            }
            static void Update()
            {
                var d = new Data();
                var r = d.Entities.OrderByDescending(e => e.Id).First();
                r.Date = null;     // 
                d.SaveChanges();
            }
        }
        public class Entity
        {
            public int Id { get; set; }
            public DateTime? Date { get; set; }
        }
        public class Data : DbContext
        {
            public DbSet<Entity> Entities { get; set; }
            protected override void OnModelCreating(DbModelBuilder mb)
            {
                base.OnModelCreating(mb);
                mb.Entity<Entity>().HasKey(t => t.Id).ToTable("Entities");
            }
        }
    }
    
      
    • Edited by Malobukv Thursday, November 24, 2011 3:01 AM
    • Marked as answer by wendy elizabeth Friday, November 25, 2011 3:12 AM
    Thursday, November 24, 2011 3:00 AM