Use DataAdapter Update Table that doesn't have primary keys

If the Table has primary key, We just need to use the SqlCommandBuilder, and DataAdpter Update() method:

Here we create a table in sql server:

CREATE TABLE [dbo].[TestTable](
[First Name] [nvarchar](50) NOT NULL,
[Last Name] [nchar](50) NULL,
[Score] [int] NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[First Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And let's insert 2 records in the table:
Insert into TestTable Values ('JayNair', 'SomeLastName' 99);
Insert into TestTable Values ('Bala', 'AnotherLastName',99);

Notice the table has Primary key, so update the table is very simple, the sampe code is like following:

using System;
using System.Data;
using System.Data.SqlClient;

string constr = @"Data Source=myserver;Initial Catalog=mydb;User Id=jiezhu;Password=mypass;";

SqlConnection con = new SqlConnection(constr);

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "Select * from TestTable";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet("MyDS");

SqlCommandBuilder brd = new SqlCommandBuilder(da);

da.Fill(ds, "MyTable");

ds.Tables["MyTable"].Rows[0]["Score"] = 95;

ds.Tables["MyTable"].Rows.Add("Jie", "Zhu", 88);

da.Update(ds, "MyTable");

Notice here, set PK at FirstName is not reasonable. Many times, we need to update a table which doesn't have primary key. But for DataAdpter, UpdateCommand, InsertCommand properties, they use PK to do update and Insert. So in order to manipulate the table which doesn't have primary key, we need to define the UpdateCommand and InsertCommand ourselves:

Let's remove PK in our table:

CREATE TABLE [dbo].[TestTable](
[First Name] [nvarchar](50) NOT NULL,
[Last Name] [nchar](50) NULL,
[Score] [int] NULL
) ON [PRIMARY]

And in our C# code, the complete code is like following:

using System;
using System.Data;
using System.Data.SqlClient;

string constr = @"Data Source=myserver;Initial Catalog=mydb;User Id=jiezhu;Password=mypass;";

SqlConnection con = new SqlConnection(constr);

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "Select * from TestTable";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet("MyDS");

SqlCommandBuilder brd = new SqlCommandBuilder(da);

da.Fill(ds, "MyTable");



ds.Tables["MyTable"].Rows[0]["Score"] = 95;

ds.Tables["MyTable"].Rows.Add("Jie", "Zhu", 88);

da.UpdateCommand = new SqlCommand("UPDATE TestTable SET [First Name] = @FirstName, [Last Name] = @LastName, Score=@Score " +
"WHERE [First Name] = @FirstName and [Last Name] = @LastName", con);
da.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.NChar, 50, "First Name");
da.UpdateCommand.Parameters.Add("@LastName", SqlDbType.NChar, 50, "Last Name");
da.UpdateCommand.Parameters.Add("@Score", SqlDbType.Int, 4, "Score");

da.InsertCommand = new SqlCommand("INSERT INTO TestTable ([First Name], [Last Name], Score) " +
"VALUES (@FirstName, @LastName, @Score)", con);
da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.NChar, 50, "First Name");
da.InsertCommand.Parameters.Add("@LastName", SqlDbType.NChar, 50, "Last Name");
da.InsertCommand.Parameters.Add("@Score", SqlDbType.Int, 4, "Score");

da.Update(ds, "MyTable");