Shan Haider Shan Haider - 3 months ago 16
C# Question

How to synchronize DataTables with database

I'm creating a small wpf project which involves users registration. In this project I'm using dataAdapter to load data from database to datatables.

con = new SqlConnection(connectionString);

string query = "select * from Users;";
cmd = new SqlCommand(query, con);
da = new SqlDataAdapter();

da.SelectCommand = cmd;

users = new DataTable();
con.Open();
da.Fill(users);


Database have 6 columns Id which is auto increment, username, password, name and two int type columns with default value to Zero. Here is database schema.

CREATE TABLE [dbo].[Users] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[username] NVARCHAR (20) NOT NULL,
[password] NVARCHAR (20) NOT NULL,
[name] NVARCHAR (50) NULL,
[attempted] INT DEFAULT ((0)) NULL,
[correct] INT DEFAULT ((0)) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
UNIQUE NONCLUSTERED ([username] ASC)
);


Whenever I insert new user into datatable I only provide username, password and name remaining field should be filled automatically but datatable only save provided fields and remaining are left blank and when I call

da.update(users);


it update data in database and remaining fields are filled automatically butt datatable still contain those fields as empty columns.

Image without user Id

But I need user Id to update or delete user. Problem is how can I sync datatable with database for updated fields so I could use them for further operations. Thanks in Advance for your assistance.

Update:
Insert query and parameters are given below

query = "insert into Users (username, password, name)values(@u, @p, @n)";
SqlCommand insertcmd = new SqlCommand(query, con);
insertcmd.Parameters.Add(new SqlParameter("u", SqlDbType.NVarChar, 20, "username"));
insertcmd.Parameters.Add(new SqlParameter("p", SqlDbType.NVarChar, 20, "Password"));
insertcmd.Parameters.Add(new SqlParameter("n", SqlDbType.NVarChar, 50, "name"));

da.InsertCommand = insertcmd;

Answer

I used following parameterized insert query to update Id field in dataTable on the basis of Id generated by database.

query = "insert into Users (username, password, name)values(@u, @p, @n); set @Id = SCOPE_IDENTITY()";
insertcmd = new SqlCommand(query, con);
insertcmd.Parameters.Add(new SqlParameter("u", SqlDbType.NVarChar, 20, "username"));
insertcmd.Parameters.Add(new SqlParameter("p", SqlDbType.NVarChar, 20, "Password"));
insertcmd.Parameters.Add(new SqlParameter("n", SqlDbType.NVarChar, 50, "name"));

SqlParameter Id = new SqlParameter("Id", SqlDbType.Int, 0, "Id");
Id.Direction = ParameterDirection.Output;

insertcmd.Parameters.Add(Id);

da.InsertCommand = insertcmd;

Complete explanation given here with stored procedure.