C# INSERT with INSTEAD OF INSERT TRIGGER

Question

I have a table Teacher in my database:

TABLE Teacher
      (
        ID             CHAR (7) NOT NULL ,
        name           VARCHAR (50) NOT NULL ,
        surname        VARCHAR (50) NOT NULL ,
        email          VARCHAR (50) NOT NULL ,
        phone          CHAR (13) NOT NULL
      )

In the database I have an INSTEAD OF INSERT trigger which creates ID and email from surname and numbers. Everything works fine when I insert in SQL Server. I can do

INSERT INTO Teacher(name, surname, phone) 
VALUES('John', 'Doe', '+111111111111')

I'm implementing ORM on this database.

public static String SQL_INSERT = 
    "INSERT INTO \"Teacher\" VALUES (@ID, @name, @surname, @email, @phone)";

    public static int Insert(Teacher teacher, Database pDb = null)
    {
        Database db;
        if (pDb == null)
        {
            db = new Database();
            db.Connect();
        }
        else
        {
            db = (Database)pDb;
        }

        SqlCommand command = db.CreateCommand(SQL_INSERT);
        PrepareCommand(command, teacher);
        int ret = db.ExecuteNonQuery(command);

        if (pDb == null)
        {
            db.Close();
        }

        return ret;
    }

    private static void PrepareCommand(SqlCommand command, Teacher teacher)
    {
        command.Parameters.AddWithValue("@ID", teacher.ID);
        command.Parameters.AddWithValue("@name", teacher.Name);
        command.Parameters.AddWithValue("@surname", teacher.Surname);
        command.Parameters.AddWithValue("@email", teacher.Email);
        command.Parameters.AddWithValue("@phone", teacher.Phone);
    }

The problem is when I try to insert from the ORM. I have to do it like this because it doesn't let me insert without all mandatory attributes.

Teacher newTeacher  = new Teacher ();

newTeacher.ID = "";
newTeacher.Name= "John";
newTeacher.Surname= "Doe";
newTeacher.Email = "";
newTeacher.Phone= "+111111111111";

TeacherTable.Insert(newTeacher, db);

Is there any way I could insert without having to assign empty strings into ID and Email? it inserts fine, but the code looks bad to me. Thanks for help.

You SQL_INSERT string is forcing you to supply all the parameters, may you should build up the parameters for the query dynamically. — momar
– momar, Commented Apr 20, 2016 at 13:09
Any reason you want to create your own home grown ORM? Many of these problems have been tackled already by the ORMs that are out there like Entity Framework or NHibernate. Why not choose something ready off the shelf so you do not waste time duplicating something that already exists? — Igor
– Igor, Commented Apr 20, 2016 at 13:12
"In the database I have an INSTEAD OF INSERT trigger which creates ID and email from surname and numbers." He is storing neither nulls nor blanks for those values. — fqhv
– fqhv, Commented Apr 20, 2016 at 13:21

Gottfried Lesigang · Accepted Answer · 2016-04-20 13:07:19Z

2

If you define your table with default constraints, the values are set to the default on insertion without the need of a trigger...

CREATE TABLE Teacher
(
    ID             CHAR (7) NOT NULL, --don't know what is the content here... In most cases an ID column with INT IDENTITY is perefered
    name           VARCHAR (50) NOT NULL CONSTRAINT DF_Teacher_name    DEFAULT(''),
    surname        VARCHAR (50) NOT NULL CONSTRAINT DF_Teacher_surname DEFAULT('') ,
    email          VARCHAR (50) NOT NULL CONSTRAINT DF_Teacher_email   DEFAULT('') ,
    phone          CHAR (13) NOT NULL    CONSTRAINT DF_Teacher_phone   DEFAULT('')
);

Btw: I would not force a phone number to fit into 13 characters...

answered Apr 20, 2016 at 13:07

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David C Over a year ago

Agree with the phone number comment, we can't forget international numbers!

Gottfried Lesigang Over a year ago

@DavidC And I agree with your answer, the upvote there is mine :-)

David C · Accepted Answer · 2016-04-20 13:23:18Z

The ORM is doing what it's supposed to. If your table fields are nullable, and your variable is a reference type that's not been set, then it should insert a NULL. Strings are reference types by the way.

One of the cleanest ways to handle this is to add a Constructor to the Teacher Class, set defaults for all your values, that way, you don't have to set them each time you new up a new object.

E.g.

public class Teacher
{
    public Teacher()
      {
           ID = "";
           Name= "";
           Surname= "";
           Email = "";
           Phone= "";
      }
    ....
}

Then you can do the following :

Teacher newTeacher  = new Teacher ();
newTeacher.Name = "Johnny";
TeacherTable.Insert(newTeacher, db);

Only set the variable you need, all others will be the default set in the constructor.

fqhv · Accepted Answer · 2016-04-20 13:13:58Z

0

You could have the PrepareCommand default them to blank instead.

private static void PrepareCommand(SqlCommand command, Teacher teacher)
{
     command.Parameters.AddWithValue("@ID", teacher.ID ?? "");
     command.Parameters.AddWithValue("@name", teacher.Name);
     command.Parameters.AddWithValue("@surname", teacher.Surname);
     command.Parameters.AddWithValue("@email", teacher.Email ?? "");
     command.Parameters.AddWithValue("@phone", teacher.Phone);
 }

Alternativly you could not pass them at all if they are null, then supply the default within the trigger.

private static void PrepareCommand(SqlCommand command, Teacher teacher)
{
    if(string.IsNullOrEmpty(teacher.ID))
        command.Parameters.AddWithValue("@ID", teacher.ID);
    command.Parameters.AddWithValue("@name", teacher.Name);
    command.Parameters.AddWithValue("@surname", teacher.Surname);
    if(string.IsNullOrEmpty(teacher.Email))        
        command.Parameters.AddWithValue("@email", teacher.Email);
    command.Parameters.AddWithValue("@phone", teacher.Phone);
}

Both of these would allow you to create the teacher object like...

Teacher newTeacher  = new Teacher ();

newTeacher.Name= "John";
newTeacher.Surname= "Doe";
newTeacher.Phone= "+111111111111";

TeacherTable.Insert(newTeacher, db);

answered Apr 20, 2016 at 13:13

fqhv

1,1971 gold badge13 silver badges25 bronze badges

1 Comment

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

Collectives™ on Stack Overflow

C# INSERT with INSTEAD OF INSERT TRIGGER

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related