Insert values into SQL Server table using SqlDataReader

Question

This is how I wrote a select statement to check if there's a value in the database.

bool userIsPresent=false;
string sqlQuery = string.Format("SELECT * FROM Person WHERE Name = '{0}'", name);

SqlCommand s = new SqlCommand(sqlQuery, con);
con.Open();

SqlDataReader sqlread = s.ExecuteReader();
userIsPresent = sqlread.HasRows;
con.Close();

But now I need to save some values into the database. How can I do this ? I don't think I should use SqlDataReader, so how can I save and confirm if the data is saved to the database?

public static bool saveToDb(string name1,string nam2, DateTime dat)
{
    bool ok=false;
    string sqlQuery = string.Format("INSERT into NumbersTable values ('{0}', '{1}','{2}')",name1,nam2,dat );

    SqlCommand s = new SqlCommand(sqlQuery, con);

    con.Open();
    SqlDataReader sr = s.ExecuteReader(); // MIGHT BE WRONG
    ok = sr.HasRows;

    con.Close();
    return ok;
}

Note that string.Format does not prevent you from sql-injection attacks. Use SQL-Parameters instead! — Tim Schmelter
– Tim Schmelter, Commented Feb 18, 2013 at 8:08

Habib · Accepted Answer · 2013-02-18 08:57:07Z

6

You need ExecuteNonQuery for inserting records in the database.

s.ExecuteNonQuery();

(Use Parameterized query to prevent SQL Injection)

edited Feb 18, 2013 at 8:57

answered Feb 18, 2013 at 8:02

Habib

224k30 gold badges420 silver badges446 bronze badges

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

6 Comments

Sharon Watinsan Over a year ago

You mean i should leave SqlDataReader sr =as it is and change ExecuteReader to ExectureNonQuery ?

Artur Udod Over a year ago

@sharonHwk, no! Habib, you are wrong. ExecuteNonQuery returns and integer number of rows affected by the query. Therefore you can't use SqlDataReader (since you're obviously not reading anything!).

Habib Over a year ago

@ArturUdod, yes you are right, I misread the comment by the OP that he/she should leave SqlDataReader and missed the part "as it is" :)

Habib Over a year ago

@sharonHwk, No, you can't use SqlDataReader, you are not reading anything from the database, you are inserting record in the table, therefore not need of SqlDataReader

Chazt3n Over a year ago

thanks for all the comments about who is wrong. Any ideas that are right would be greatly appreciated.

|

Ryoku · Accepted Answer · 2013-02-18 10:22:37Z

Okay so what you are looking to do is insert to a database and not read from it so you therefore have to simply execute an sql query on the database and not read any data selected from it. For this you need to use the ExecuteNonQuery statement rather than the sqlDataReader. The result would look something like this

public static bool saveToDb(string name1, string nam2, DateTime dat)
{
  bool ok = false;
  string sqlQuery = "INSERT INTO NumbersTable VALUES (@name1, @nam2, @dat)";
  //This is the sql query we will use and by placing the @ symbol in front of words
  //Will establish them as variables and placeholders for information in an sql command
  //Next we create the sql command
  SqlCommand cmd = new SqlCommand(sqlQuery, con);
  //Here we will insert the correct values into the placeholders via the commands
  //parameters
  cmd.Parameters.AddWithValue("name1", name1);
  //This tells it to replace "@name1" with the value of name1
  cmd.Parameters.AddWithValue("nam2", nam2);
  cmd.Parameters.AddWithValue("dat", dat);
  //Finally we open the connection
  con.Open();
  //Lastly we tell the sql command to execute on the database, in this case inserting
  //the values
  int i = cmd.ExecuteNonQuery();
  //Here we have the results of the execution stored in an integer, this is because
  //ExecuteNonQuery returns the number of rows it has effected, in the case of this
  //Insert statement it would effect one row by creating it, therefore i is 1
  //This is useful for determining if your sql statement was successfully executed
  //As if it returns 0, nothing has happened and something has gone wrong
  con.Close();
}

I hope this has helped, if you need anything else feel free to ask.

Vinay Sinha · Accepted Answer · 2013-02-18 11:12:42Z

0

just need to correct some thing in your existing code, Definitely this will be helpful for you:

 public static bool executeMyQuery(string sqlQuery)
      {
        try
        {        con.Open();
                 SqlCommand s = new SqlCommand(sqlQuery, con);
                 s.ExecuteNonQuery();
                 con.Close();
                 return true;
         } 
       catch()
        {
          return false;
        }

    And use the above function[executeMyQuery()] anywhere you want to insert like and check whether record is inserted or not like below:

     bool isInserted = false;
     // give dummy value or get it what u want to inser on name1,nam2,dat
     string rawQuery = string.Format("INSERT into NumbersTable values ('{0}', '{1}','{2}')",name1,nam2,dat );

    isInserted = myExecuteQuery(rawQuery);

  if(isInserted)
    { 
      // lblStatus i am taking only to make you understand
      // lblStatus.text = "Record inserted succesfully";
    }
  else
    {
       // lblStatus.text = "Record insertion failed";
    }

edited Feb 18, 2013 at 11:12

answered Feb 18, 2013 at 8:30

Vinay Sinha

3233 silver badges13 bronze badges

4 Comments

Sharon Watinsan Over a year ago

Will this method prevent, SQL injections ?

Vinay Sinha Over a year ago

i am just giving the example to show how he/she can use executenonquery for checking whether the record is inserted or not, Yes this code not prevent sql injection, i will edit accordingly later, want to give the answer where he/she got stuck :)

Sharon Watinsan Over a year ago

Can someone explain why this answer was down voted ? Is there a better way of doing it ?

Vinay Sinha Over a year ago

The below answer is also write @coder, but why it is downvoted ?

Anders R. Bystrup · Accepted Answer · 2013-02-18 11:33:51Z

0

Add this in your code:

con.open();
S.executeNonQuery();

edited Feb 18, 2013 at 11:33

Anders R. Bystrup

16.1k11 gold badges65 silver badges59 bronze badges

answered Feb 18, 2013 at 8:14

coder

2,0183 gold badges22 silver badges32 bronze badges

4 Comments

coder Over a year ago

Whats the problem with this code..why is it downvoted? can anyone tell me the reason ?

coder Over a year ago

@llya lvanov thats not my question it was asked by someoneelse and i just added executenonquery in my answer,but my answer was downvoted.

Ilya Ivanov Over a year ago

Ok, fair point, I've formatted your code and put a +1. I very strongly suggest to add to your answer comments regarding not returning status bool, but catch low-level sql exception and return more high-level exceptuin. Also, use sql parameters instead of string.Format

coder Over a year ago

@llya lvanov thanks and sorry i didnt see the question properly and just answered it..I too have doubts regarding the bool which the questioner used and not returning it..I am just editing my answer too.

Princee Singhal · Accepted Answer · 2013-02-19 04:17:15Z

0

Try it... This code will give the proper message ..
name1, name2 , dat are the columns name in Data table in Database

public static void saveToDb()
{
string sqlQuery="INSERT into NumbersTable (name1,name2,dat) values ('Princee', 'Singhal','18/02/2012');
SqlCommand s = new SqlCommand(sqlQuery, con);
con.Open();
int i=s.ExecuteNonQuery();
if(i>=1)
{
MessageBox.Show("Record Inserted");
}
else
{
MessageBox.Show("May Be Some Problem Occure !");
}
con.Close();
}

edited Feb 19, 2013 at 4:17

answered Feb 18, 2013 at 11:26

Princee Singhal

291 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

Insert values into SQL Server table using SqlDataReader

5 Answers 5

6 Comments

Comments

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related