CODEWITHSUNDEEP
c#.netdatatable
Rehan Haider
Rehan Haider

Reputation: 1061

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;

Upvotes: 0

Views: 2044

Answers (3)

Rehan Haider
Rehan Haider

Reputation: 1061

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.

Upvotes: 0

user5993035
user5993035

Reputation:

After the insert command do thes to get the ID:

select SCOPE_IDENTITY()
int newId = Convert.ToInt32( cmd.ExecuteScalar());

Then add the newId to the right place

This is an example:

 SqlCommand cmd = new SqlCommand("Insert into Products (ProductName, UnitPrice) VALUES (@ProductName, @Price); select SCOPE_IDENTITY()", conn); 
int newId = Convert.ToInt32( cmd.ExecuteScalar());

Upvotes: 1

Fernando Gutierrez
Fernando Gutierrez

Reputation: 439

If you are using a stored procedure, make sure to declare the id parameter as an output parameter.

create procedure dbo.SaveUser
(
    @id int = null out,
   ...
)
as
begin

     --insert goes here

     set @id = scope_identity()
end

Upvotes: 1

Related Questions