Insert NEW RECORD to PostgreSQL Line by Line in C#

Question

Good day!

I am a newbie in C#.NET (I came from VB6).

I want to Insert new record to my database using PostgreQL.

I can use a single line of code, like:

Insert into table1 values("value1","value2","value3");

But I wanted to insert new record line by line, like:

rs.open("Select * from table1",con,AdOpenDynamic, AdLockOptimistic)
rs.Fields("Field1").value = value1    
rs.Fields("Field2").value = value2    
rs.Fields("Field3").value = value3    
rs.Update

Again, I am a newbie here. Also again, I can insert using a single INSERT statement.

But IF I have 40 Fields, the code is hard to read (for readability). If the code is line by line, it is easy to read and the code is easy to update.

Is there any way to do it? Any help will be appreciated!

Happy Coding!

The question is where is your record came from? even how may fields it is you don't have a choice except of specify the value for every designated field. You can search more on how to insert a record in your DB using Postgres — Vijunav Vastivch
– Vijunav Vastivch, Commented Mar 24, 2017 at 6:12
sir, do we have a workaround like iDataSet.Tables[0].Rows[0]["stud_id"] = txtStudID.Text;? I want it to look like in vb6, line by line. — Curbside Coder
– Curbside Coder, Commented Mar 24, 2017 at 6:16
Forgot VB6 anymore.. You are working in the other environment now. — Vijunav Vastivch
– Vijunav Vastivch, Commented Mar 24, 2017 at 6:17

Vijunav Vastivch · Accepted Answer · 2017-03-24 06:36:18Z

1

Even Postgres:

Code doesn't change it still

Insert into tablename(field1,field2,field3 and so on....) values(value1,value2,value3 and so on...)

Here's the workaround:

string connectionString = "Your connection string here";

protected static int ExecuteQuery(string query)
        {
            using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
            {
                con.Open();
                using (NpgsqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
            }
        }

To use this:

ExecuteQuery("Insert into tablename(field1,field2,field3 and so on....) values(value1,value2,value3 and so on...)")

edited Mar 24, 2017 at 6:36

answered Mar 24, 2017 at 6:15

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

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

1 Comment

Vijunav Vastivch Over a year ago

But you have to be careful of naming convention in Postgres, do not use Upper Case field like Field1 because you are going use call this as select "Field1" in your query. But if you use lower case like field1 you can directly type this as select field1 without double qoutes neede.

Curbside Coder · Accepted Answer · 2017-03-29 04:41:40Z

Thanks @reds for the Answer and suggestions. I realized my only problem is the code readability, not the code itself. As you have said, Insert Query is the best workaround. That's why I came up with the following structure:

////saving
        //CODE TO DATABASE
        NpgsqlConnection iConnect = new NpgsqlConnection("Server=localhost;Port=5432;User ID=postgres;Password=sdferekrjsdf873()#3s;Database=DB");
        iConnect.Open();
        NpgsqlCommand iQuery = new NpgsqlCommand
           ("insert into tblstudents_secure values('" + 
            myModule.studID + "','" + 
            myModule.studFname + "','" + 
            myModule.studMname + "','" + 
            myModule.studLname + "','" +
            myModule.studGrade + "','" +
            myModule.studSection + "','" + 
            myModule.studHomeAdd + "','" + 
            myModule.studProvAdd + "','" +
            myModule.studBday + "','" +
            myModule.studAge + "','" +
            myModule.studCivilStat + "','" +
            myModule.studHomeContact + "','" + 
            myModule.studProvContact + "','" + 
            myModule.studBplace + "','" +
            myModule.studGender + "','" +
            myModule.studReligion + "','" + 
            myModule.studFather + "','" + 
            myModule.studFatherOcc + "','" + 
            myModule.studMother + "','" + 
            myModule.studMotherOcc + "','" + 
            myModule.studGuardian + "','" + 
            myModule.studGuardianOcc + "','" + 
            myModule.studGuardianRel + "','" + 
            myModule.studGuardianContact + "','" + 
            myModule.studOldSchool + "','" + 
            myModule.studOldSchoolAdd  + "','" + 
            myModule.studOldGrade + "','" + 
            myModule.studOldSY + "','" + 
            myModule.studIsTransfer + "','" + 
            myModule.studHas137 + "','" + 
            myModule.studHas138 + "','" +
            myModule.studHasGoodMoral + "','" +
            myModule.studHasNSO + "','" + 
            myModule.studHasMed + "','" + 
            myModule.studRemarks + "','" +
            myModule.studDateRegistered + "','" +
            myModule.studEnrollmentStatus + "')", iConnect);

        iQuery.ExecuteNonQuery();
        iConnect.Close();

Where: `

myModule

is a class handler of static strings studID, studFname, etc...

Happy Coding!

Collectives™ on Stack Overflow

Insert NEW RECORD to PostgreSQL Line by Line in C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related