Insert data into databse code first

Question

0.00/5 (No votes)

See more:

Iam New In Entity-framework...i created Windows Apllication in visual studio 2019
iam using Entity-framework 6.i created DatabaseContext like that

C#

using LedgerGroup.TheLedger.Core.Entities;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace LedgerGroup.TheLedger.Core.Data
{
    public class DatabaseContext: DbContext
    {
        public DatabaseContext() : base("DatabaseConnectionString")
        {
            Database.SetInitializer<databasecontext>(new CreateDatabaseIfNotExists<databasecontext>());
        }
       
        public DbSet<customer> Customers { get; set; }
        
    }
}

and then created Customer Table like that

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LedgerGroup.TheLedger.Core.Entities
{
    public class Customer:BaseEntity
    {
        public string Name { get; set; }
        public string Telephone { get; set; }
        public string Address { get; set; }
        public string CarType { get; set; }
        public string CarModel { get; set; }
        public string Notes { get; set; }

    }
}

and then i create BaseEntity Class which Every Table in database will get somecolumns(Id ,CreatedBy,LastUpdatedBy,CreatedOn,LastUpdatedOn,bool Deleted)
from this class
this the code inside BaseEntity class

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LedgerGroup.TheLedger.Core.Entities
{
    public class BaseEntity
    {
        public int Id { get; set; }
        public int CreatedBy { get; set; }
        public int LastUpdatedBy { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime LastUpdatedOn { get; set; }
        public bool Deleted { get; set; }
    }
}

and i add-migration CreateTheLedgerDB
which create the databse in my databse Engine and Update-Databse

then i wanted to insert Some records inside customers Table

i made Aform called it AddCustomerForm and in the event BtnAddCustomer_Click(object sender, EventArgs e) i used some code to insert data
but it gives me this error
(SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.)>
can Any one help me

in the AddCustomerForm i want to Insert new customer

What I have tried:

C#

private void BtnAddCustomer_Click(object sender, EventArgs e)
        {
            
                using (var context = new DatabaseContext())
                {
                    var customer = new Customer()
                    {
                        Name = txtCustomerName.Text,
                        Telephone = txtCustomerPhone.Text,
                        Address = txtCustomerAddress.Text,
                        CarType = txtCustomerCar.Text,
                        CarModel = txtCustomerCarModel.Text
                    };
                    context.Customers.Add(customer);
                    context.SaveChanges();
                    //TODO:Add MessageManager Class
                    MessageBox.Show("Saved Successfully");
                }
            
        }

Posted 4-Oct-19 23:30pm

Abuamer

Updated 8-Oct-19 8:19am

Nelek

v2

Add a Solution

Comments

Patrice T 5-Oct-19 8:37am

What is the question/problem ?

2 solutions

Solution 1

Use a separate step for (re)creating the database. Keep the "regular" stuff separate. Here's one of my EF databases:

using System.Data.Entity;

namespace HT.Reporting {

   /// <summary>
   /// 
   /// </summary>
   public class ReportingDbContext : DbContext {

      public DbSet<WorkSession> Sessions { get; set; }
      public DbSet<Treatment> Treatments { get; set; }
      public DbSet<EventRecord> LogEntries { get; set; }

      /// <summary>
      /// CONSTRUCTOR.
      /// </summary>
      public ReportingDbContext( string connectionString ) : base( connectionString ) {

      }

      //==============================
      // PUBLIC.
      //==============================

      /// <summary>
      /// 
      /// </summary>
      public static ReportingDbContext GetDbContext() {

         string connectionString = DatabaseAdapter.GetConnectionString();

         ReportingDbContext context = new ReportingDbContext( connectionString );
         return context;
      }

      //====================
      // CREATE.
      //====================

      /// <summary>
      /// Create / recreate.
      /// </summary>
      public static bool CreateDatabase() {

         bool deleted = false;
         bool created = false;

         using ( ReportingDbContext context = ReportingDbContext.GetDbContext() ) {

            Database db = context.Database;

            if ( db.Exists() ) {
               // Deletes db.
               deleted = db.Delete();
            }

            // Creates db.
            db.Initialize( force: true );

            // Confirms db.
            if ( db.Exists() ) {
               created = true;
            }

         }  // end using.

         return created;
      }

      /// <summary>
      /// 
      /// </summary>
      public static bool CreateDatabaseIfNotExists() {

         bool created = false;

         using ( ReportingDbContext context = ReportingDbContext.GetDbContext() ) {

            Database db = context.Database;

            if ( !db.Exists() ) {
               // Creates db.
               db.Initialize( force: true );

               // Confirms.
               if ( db.Exists() ) {
                  created = true;
               }
            }

         }  // end using.

         return created;
      }

      //======================
      // DATABASE.
      //======================

      /// <summary>
      /// 
      /// </summary>
      public static bool DatabaseExists() {

         bool exists = false;

         using ( ReportingDbContext context = ReportingDbContext.GetDbContext() ) {

            Database db = context.Database;
            exists = db.Exists();

         }  // end using.

         return exists;
      }

      /// <summary>
      /// 
      /// </summary>
      public static bool DeleteDatabase() {

         bool deleted = false;

         using ( ReportingDbContext context = ReportingDbContext.GetDbContext() ) {

            Database db = context.Database;

            if ( db.Exists() ) {
               deleted = db.Delete();
            }

         }  // end using.

         return deleted;
      }

   }  // end class.
}

Posted 5-Oct-19 3:56am

Gerry Schmitz

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-10-08T08:20:00

The issue is that your DateTime properties by default map to SQL's datetime type. But that can only handle dates after 1753-01-01, whereas DateTime can handle dates as far back as 0001-01-01.

Assuming you're using SQL 2008 or higher, you can change the mapping to store the columns using the datetime2 type, which precisely matches the range of .NET's DateTime type:

C#

public class BaseEntity
{
    public int Id { get; set; }
    public int CreatedBy { get; set; }
    public int LastUpdatedBy { get; set; }
    [Column(TypeName = "datetime2")]
    public DateTime CreatedOn { get; set; }
    [Column(TypeName = "datetime2")]
    public DateTime LastUpdatedOn { get; set; }
    public bool Deleted { get; set; }
}

Once you've done that and recreated your database, you'll be able to save records. However, you'll notice that both the CreatedOn and LastUpdateOn columns are set to 0001-01-01 00:00:00. That's because you never initialize them:

C#

using (var context = new DatabaseContext())
{
    var customer = new Customer
    {
        Name = txtCustomerName.Text,
        Telephone = txtCustomerPhone.Text,
        Address = txtCustomerAddress.Text,
        CarType = txtCustomerCar.Text,
        CarModel = txtCustomerCarModel.Text,
        
        // Set the created and updated dates:
        CreatedOn = DateTime.UtcNow,
        LastUpdatedOn = DateTime.UtcNow,
        // TODO: Set the CreatedBy and LastUpdatedBy properties as well...
    };
    
    context.Customers.Add(customer);
    context.SaveChanges();
    MessageBox.Show("Saved Successfully");
}