Insert Records into SQL Server Database using C# Data Objects

Question

I'm trying to properly bind a Data Adapter to my SQL database to insert records submitted by a person in a c# program. I feel like I am 80% of the way, but now i've hit a hitch.

So as in the code below, I can create and bind a Data Table just fine. I've tested the delete functions and they work just fine. I am now attempting to have a 'save' button insert a new row to my database. The problem I have now is that a user is supposed to put in their 'notes' and then hit save. I auto populate the rest of the columns, but I do not know how to grab the notes that the user entered.

Here is my code so far:

string userVerify = User.CurrentUser.UserName.ToString();
int participantID = this.mParticipant.ParticipantID;
DateTime date = DateTime.Now;
string properRow = dtNotes[1, dtNotes.NewRowIndex - 1].Value.ToString();

sqlDataAdapter.InsertCommand = new SqlCommand("INSERT INTO xxMyDatabasexx (ParticipantID,Verifier,Notes,Date) VALUES  (@participantID,@notes, @userVerify,@date);");
sqlDataAdapter.InsertCommand.Parameters.AddWithValue("@participantID", participantID);
sqlDataAdapter.InsertCommand.Parameters.AddWithValue("@userVerify", userVerify);
sqlDataAdapter.InsertCommand.Parameters.AddWithValue("@date", date);
sqlDataAdapter.InsertCommand.Parameters.AddWithValue("@notes", properRow);

sqlDataAdapter.Fill(dataTable);

sqlDataAdapter.Update(dataTable);

I am aware that the properRow variable's logic is wrong. Of course if there are no rows then the program will crash, but also if no new note has been entered it will just reproduce the last note entered which of course is wrong as well. When i look into my dataTable at the time of sqlDataAdapter.Fill, I can see the note in the correct column but I don't know how to simply save it.

Any help is appreciated. Thanks.

EDIT: What I was unaware of is that the InsertCommand (naturally) also needs the ExecuteNonQuery command with it. I was under the assumption that since both Delete and Update did not, that Insert wouldn't either. This seemed to fix the issue. Thanks all for the help.

FYI: Data objects like DataAdapter bind to controls, they connect to databases. — RBarryYoung
– RBarryYoung, Commented Mar 14, 2016 at 20:59
I are not bery young, but what I would mention is that when you say, "I feel like I am 80% of the way" you should be forewarned that the last 20% takes 80% of the time. — B. Clay Shannon-B. Crow Raven
– B. Clay Shannon-B. Crow Raven, Commented Mar 14, 2016 at 21:02
Offhand, the .Fill looks out of place. Comment out that line and try it. — RBarryYoung
– RBarryYoung, Commented Mar 14, 2016 at 21:03
The Update command looks at the RowState of the rows in the DataTable passed and updates (if a proper INSERT/UPDATE/DELETE command exists) only the rows that have the RowState set to DataRowState.Changed. Setting an Insert command but without any row changed doesn't update anything — Steve
– Steve, Commented Mar 14, 2016 at 21:07
Thanks RBarry Young. I'll have to read up more on the terminology and distinction to prevent this mistake in the future. The Fill also works fine so far. My problem is when people enter data into the datatable on the C# side, I d'ont know how to get that rows information into the database. So the problem lies with what I called 'properRow' I think. — G3TH
– G3TH, Commented Mar 14, 2016 at 21:07

Alexander Bell · Accepted Answer · 2016-03-15 01:10:31Z

You can insert the record into SQL Database without need for DataAdapter just by using Command object as shown in the following code snippet (just pass your Insert SQL statement string):

void SqlExecuteCommand(string InsertSQL)
{
    try
    {
        using (SqlConnection _connSqlCe = new SqlConnection("Conn String to SQL DB"))
        {
            _connSql.Open();
            using (SqlCommand _commandSqlCe = new SqlCommand(CommandSQL, _connSql))
            {
                _commandSql.CommandType = CommandType.Text;
                _commandSql.ExecuteNonQuery();
            }
        }
    }
    catch { throw; }
}

The general format of SQL INSERT query string is shown below:

INSERT INTO YourTable (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

You can further extend this solution by adding parameters to the SQL String/Command in order to protect against possibility of SQL injection (see the following example):

INSERT INTO YourTable (column1,column2,column3,...)
VALUES (@param1,@param2,@param3,...);

_commandSql.Parameters.Add("@param1","abc");
_commandSql.Parameters.Add("@param2","def");
_commandSql.Parameters.Add("@param3","ghijklm");

You can also use the alternative syntax for SQL Parameters, like for e.g.:

_commandSql.Parameters.Add("@param1", SqlDbType.NChar).Value = "abc";
_commandSql.Parameters.Add("@param2", SqlDbType.NChar).Value = "def";
_commandSql.Parameters.Add("@param3", SqlDbType.NVarChar).Value = "ghijklm";

Pertinent to your particular question, it should be like:

"INSERT INTO xxMyDatabasexx (ParticipantID, Verifier, Notes, [Date]) VALUES  (@participantID, @userVerify, @notes, @dt)"

 _commandSql.Parameters.Add("@ParticipantID",SqlDbType.NChar).Value= participantID;
 _commandSql.Parameters.Add("@userVerify",SqlDbType.NChar).Value= userVerify ;
 _commandSql.Parameters.Add("@notes",SqlDbType.NVChar).Value= properRow ;
 _commandSql.Parameters.Add("@dt",SqlDbType.DateTime).Value= DateTime.Now;

Note: in case ParticipantID is the IDENTITY (i.e. Autonumber) Column, then do not include it in INSERT statement.

Hope this may help.

Antonín Lejsek · Accepted Answer · 2016-03-15 01:49:29Z

It seems to me that You are a bit lost. The way adapters are meant to work is

fill table from database via adapter (or take empty table)
bind table to GUI or manually transfer the information to GUI
change/add/delete data in table via binding or manually
update changes (inserts/updates/deletes) into database via adapter

The changes in table are automatically traced, so the adapter knows, what should be updated/inserted/deleted and use appropriate commands.

If You use adapter just as a holder for command You can ExecuteNonQuery with arbitrary parameters, You pass the whole concept and do not need adapter at all (see @AlexBells answer).

Apart from this, are You really going to write all that plumbing code by hand? Life is too short. If I were You, I would look for some ORM. You get simple CRUDs or concurrency checking with no effort.

Collectives™ on Stack Overflow

Insert Records into SQL Server Database using C# Data Objects

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related