Database

RugbyLeague wrote:

I realise it is best to do it SQL but in this instance (and others) I would like to do it in C# fo reasons which aren't relevant to the question.

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn; // Assumes already open connection
cmd.CommandText = "UPDATE MyTable "+
                  "SET SomeColumn = @SomeValue "+
                  "WHERE PrimaryKey = @id";
cmd.Parameters.Add("@SomeValue", updatedValue);
cmd.Parameters.Add("@id", reader.GetInt32(0)); // Assumes the first column in 
                                               // the reader is the primary key.
cmd.ExecuteNonQuery();

Table: Contact         [Contact ID] [Name]
Row  : 1               1            user

Table: Account         [Account ID] [Name]
Row  : 1               1            file1
Row  : 2               2            file2
Row  : 3               3            file3
Row  : 4               4            file4
Row  : 5               5            file5
Row  : 6               6            file6
Row  : 7               7            file7

Table: Contact Account [Contact ID] [Account ID]
Row  : 1               1            4
Row  : 2               1            7

Table: Contact Account [Contact ID] [Account ID]
Row  : 1               1            2
Row  : 2               1            2

CREATE PROCEDURE usp_ContactAccount_Delete
( @contactID int
, @accountID int
)
AS
DELETE FROM [Contact Account ]
WHERE [Contact ID] = @contactID
AND [Account ID] = @accountID

CREATE PROCEDURE usp_ContactAccount_Insert
( @contactID int
, @accountID int
)
AS
INSERT INTO [Contact Account ]
( [Contact ID]
, [Account ID]
)
VALUES
( @contactID
, @accountID
)

declare @col1 varchar(10), @col2 date, @col3 int
declare c1 cursor for
   select * from MyExcelNames
open c1
while (@@fetch_status = 0) begin
   fetch c1 into @col1, @col2, @col3
   if (@@fetch_status = 0) begin
      exec MyNamesProcedure @col1, @col2, @col3
   end
end
close c1

SELECT * FROM SomeTable WHERE SomeColumn IS NULL

IF EXISTS(SELECT * FROM Test
  WHERE [User] = @User AND [Out] IS NULL)
BEGIN

    UPDATE Test
    SET [Out] = @TimeValue
    WHERE [User] = @User
    AND [Out] IS NULL

END
ELSE
BEGIN

    INSERT INTO Test ([Date], [User], [In])
    VALUES (GETDATE(), @User, @TimeValue)

END