• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

SQL in C#

I'm trying to write a C# program that will automatically enter or delete data from certain tables.  I'm working with multiple tables, and when one table is updated, or data has been entered into that table, then an insert or delete command is run.  

The problem is: how do I determine which table is updated (or which table Ishould be making insert or delete commands in relation to) I know how to write SQL statements in C#, but how do I use C# to figure out which table is the one the command(s) should relate to?
0
pikapi
Asked:
pikapi
  • 12
  • 6
  • 3
  • +1
1 Solution
 
smeggheadCommented:
This might be triggers, although I'm not entirely sure what you are trying to acheive.

For example.

create trigger [KeepTableUpdated] on [MyTableWhichHasBeenChanged]
      for Delete,Insert,Update
as
insert into [Another Table] (id,details) values (1,'hello')


0
 
pikapiAuthor Commented:
Mmmm I thought about using triggers, but I'm not sure if it would work - what I neglected to mention was that I'm writing data from a table in one database (say database1) to a corresponding table in another database (say database2) - can triggers still be used here? And how to I make a connection to two databases at the same time to write data across?
0
 
smeggheadCommented:
If the databases exist on the same SQL server, then it's easy...

select * from OtherDatabase.dbo.TheTable
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
atomicchipCommented:
Even if the database exists on another server, it's still pretty much as easy as in smegghead's comment:

Add a linked server (if you are using SQL 2000 it's under Server>Security>Linked Servers). The SQL query looks the same as above. For example, say you added a linked server named sql2. Your query would then look like:

SELECT * FROM sql2.[objectOwner].[TableName]
0
 
Duy PhamFreelance IT ConsultantCommented:
If you want to do it with only some SQL statements, it's easy. You have known the syntax for of those SQL statements, so you will find it easy to determine which tables will be changed.
For example :

with INSERT statement, after INSERT is the INTO and after INTO is a table name
       INSERT INTO Employees
       VALUES(....)

DELETE <Tablename> WHERE <Conditions>
UPDATE <Tablename> SET <Updated fields and values> WHERE <Conditions>.

So if you have the query string, it is easy to recognize the table will be updated.
In case you do not know the query string, TRIGGER is the only solution.

Try it.
0
 
Duy PhamFreelance IT ConsultantCommented:
Doing with TRIGGER, you should create a HasChanged Table to store the history of tables changed, and remember to refresh it always. But you can also to find a way to auto information. I've done it in VC++ but I don't know how to do with C#. In VC++ auto information way is complicated and has many work you must do.

Good luck !
And if you find a way to do it, please tell me ! Thanks !
0
 
pikapiAuthor Commented:
I tried using triggers as suggested.... but now my program just freezes... using this code:

string source = "server = (PIBDJP178);" +
      "uid = ; pwd = fish;" + // what is the database's user name and password?
                                                "database = local";
0
 
pikapiAuthor Commented:
oops accidently submitted before i finished typing...

...as far as i know the server doesn't have a uid, but does have a pwd... i also use:

SqlConnection conn = new SqlConnection (source);
conn.Open();

to start a connection, i'm relatively sure that this is the part which is freezing the program... can anyone help me at all?
0
 
pikapiAuthor Commented:
PS. if it helps at all... I'm using MSSQLServer that's available free on the Microsoft website
0
 
smeggheadCommented:
it may be the case that your trigger creates a circular update to your table, that could be the cause of your freeze.

Can you post the rest of your procedure.

Smg.
0
 
pikapiAuthor Commented:
string source = "server = (TNKDEQ047\\NetSDK);" +
                                                // "uid = ?; pwd = fish;" +
                                                "pwd = fish;" +
                                                "database = local";
                        /*
                        // are these tables in seperate databases or the same databases?
                        string select_op =      "CREATE TRIGGER op_trig_pin" +
                              "ON op_temp" +
                              "FOR INSERT" +
                              "AS" +
                              "INSERT INTO operation (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘end_date’, ‘activity_code’, ‘cruise_id’, ‘cooperating_boat_no’, ‘fishing_method_code’, ‘operation_latitude’, ‘operation_longitude’, ‘position_precision’, ‘fishing_start_time’, ‘fishing_end_time’, ‘fishing_time’, ‘effort’, ‘effort_precision’, ‘gear_code’, ‘net_mesh’, ‘net_length’, ‘pot_nos’, ‘pot_lifts’, ‘line_nos’, ‘boat_nos’, ‘crew_nos’, ‘fishing_depth_avg’, ‘no_of_operations’, ‘skipper_id’, ‘port_code’, ‘fishing_ground’, ‘search_grid’, ‘start_grid’, ‘end_grid’, ‘log_type’, ‘log_no’, ‘page_no’, ‘file_id’, ‘retained_wt’, ‘uploaded_flag’, ‘verified_flag’, ‘edited_flag’, ‘position_error’, ‘catch_error’, ‘effort_error’, ‘other_error’, ‘added_time’, ‘added_by’, ‘changed_time’, ‘changed_by’)" +
                              "SELECT (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘end_date’, ‘activity_code’, ‘cruise_id’, ‘cooperating_boat_no’, ‘fishing_method_code’, ‘operation_latitude’, ‘operation_longitude’, ‘position_precision’, ‘fishing_start_time’, ‘fishing_end_time’, ‘fishing_time’, ‘effort’, ‘effort_precision’, ‘gear_code’, ‘net_mesh’, ‘net_length’, ‘pot_nos’, ‘pot_lifts’, ‘line_nos’, ‘boat_nos’, ‘crew_nos’, ‘fishing_depth_avg’, ‘no_of_operations’, ‘skipper_id’, ‘port_code’, ‘fishing_ground’, ‘search_grid’, ‘start_grid’, ‘end_grid’, ‘log_type’, ‘log_no’, ‘page_no’, ‘file_id’, ‘retained_wt’, ‘uploaded_flag’, ‘verified_flag’, ‘edited_flag’, ‘position_error’, ‘catch_error’, ‘effort_error’, ‘other_error’, ‘added_time’, ‘added_by’, ‘changed_time’, ‘changed_by’)" +
                              "FROM inserted";
                        string select_op_spe =      "CREATE TRIGGER op_spe_trig_pin" +
                              "ON op_spe_temp" +
                              "FOR INSERT" +
                              "AS" +
                              "INSERT INTO operation_species (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘species_code’, ‘species_grade’, ‘catch_nos’, ‘released_nos’, ‘catch_wt’, ‘released_wt’, ‘weight_type_code’, ‘weight_factor’, ‘target_flag’, ‘commercial_catch_flag’)" +
                              "SELECT (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘species_code’, ‘species_grade’, ‘catch_nos’, ‘released_nos’, ‘catch_wt’, ‘released_wt’, ‘weight_type_code’, ‘weight_factor’, ‘target_flag’, ‘commercial_catch_flag’)" +
                              "FROM inserted";
                        string select_log_reg = "CREATE TRIGGER log_reg_trig_pin" +
                              "ON log_reg_temp" +
                              "FOR INSERT" +
                              "AS" +
                              "INSERT INTO log_registration (‘fishery_code’, ‘boat_record_no’, ‘first_date’, ‘last_date’, ‘log_type’, ‘log_no’, ‘first_page_no’, ‘last_page_no’, ‘file_id’, ‘received_time’, ‘received_by’, ‘operations_entered’, ‘operations_verified’, ‘operations_completed’, ‘log_status_flag’, ‘receipt_notice_id’, ‘receipt_issue_date’)" +
                              "SELECT (‘fishery_code’, ‘boat_record_no’, ‘first_date’, ‘last_date’, ‘log_type’, ‘log_no’, ‘first_page_no’, ‘last_page_no’, ‘file_id’, ‘received_time’, ‘received_by’, ‘operations_entered’, ‘operations_verified’, ‘operations_completed’, ‘log_status_flag’, ‘receipt_notice_id’, ‘receipt_issue_date’)" +
                              "FROM inserted";
                        // not sure if this will work... may end up deleting before the program has a chance to get the data
                        string delete_op =      "DELETE *" +
                              "FROM inserted";
                        // on the other hand, this might be deleting at the wrong times
                        string delete_op_spe =      "DELETE *" +
                              "FROM op_spe_temp";
                        string delete_log_reg = "DELETE *" +
                              "FROM log_reg_temp";
                        string record_op =      "CREATE TRIGGER record_op" +
                                                      "ON op_temp" +
                                                      "FOR DELETE" +
                                                      "AS" +
                                                      "INSERT INTO op_audit (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘end_date’, ‘activity_code’, ‘cruise_id’, ‘cooperating_boat_no’, ‘fishing_method_code’, ‘operation_latitude’, ‘operation_longitude’, ‘position_precision’, ‘fishing_start_time’, ‘fishing_end_time’, ‘fishing_time’, ‘effort’, ‘effort_precision’, ‘gear_code’, ‘net_mesh’, ‘net_length’, ‘pot_nos’, ‘pot_lifts’, ‘line_nos’, ‘boat_nos’, ‘crew_nos’, ‘fishing_depth_avg’, ‘no_of_operations’, ‘skipper_id’, ‘port_code’, ‘fishing_ground’, ‘search_grid’, ‘start_grid’, ‘end_grid’, ‘log_type’, ‘log_no’, ‘page_no’, ‘file_id’, ‘retained_wt’, ‘uploaded_flag’, ‘verified_flag’, ‘edited_flag’, ‘position_error’, ‘catch_error’, ‘effort_error’, ‘other_error’, ‘added_time’, ‘added_by’, ‘changed_time’, ‘changed_by’)" +
                                                      "SELECT (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘end_date’, ‘activity_code’, ‘cruise_id’, ‘cooperating_boat_no’, ‘fishing_method_code’, ‘operation_latitude’, ‘operation_longitude’, ‘position_precision’, ‘fishing_start_time’, ‘fishing_end_time’, ‘fishing_time’, ‘effort’, ‘effort_precision’, ‘gear_code’, ‘net_mesh’, ‘net_length’, ‘pot_nos’, ‘pot_lifts’, ‘line_nos’, ‘boat_nos’, ‘crew_nos’, ‘fishing_depth_avg’, ‘no_of_operations’, ‘skipper_id’, ‘port_code’, ‘fishing_ground’, ‘search_grid’, ‘start_grid’, ‘end_grid’, ‘log_type’, ‘log_no’, ‘page_no’, ‘file_id’, ‘retained_wt’, ‘uploaded_flag’, ‘verified_flag’, ‘edited_flag’, ‘position_error’, ‘catch_error’, ‘effort_error’, ‘other_error’, ‘added_time’, ‘added_by’, ‘changed_time’, ‘changed_by’)" +
                                                      "FROM deleted";
                        string record_op_spe =  "CREATE TRIGGER recod_op_spe" +
                                                            "ON op_spe_temp" +
                                                            "FOR DELETE" +
                                                            "AS" +
                                                            "INSERT INTO op_spe_audit (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘species_code’, ‘species_grade’, ‘catch_nos’, ‘released_nos’, ‘catch_wt’, ‘released_wt’, ‘weight_type_code’, ‘weight_factor’, ‘target_flag’, ‘commercial_catch_flag’)" +
                                                            "SELECT (‘fishery_code’, ‘boat_record_no’, ‘operation_date’, ‘operation_no’, ‘species_code’, ‘species_grade’, ‘catch_nos’, ‘released_nos’, ‘catch_wt’, ‘released_wt’, ‘weight_type_code’, ‘weight_factor’, ‘target_flag’, ‘commercial_catch_flag’)" +
                                                            "FROM deleted";
                        string record_log_reg = "CREATE TRIGGER record_log_reg" +
                                                            "ON log_reg_temp" +
                                                            "FOR DELETE" +
                                                            "AS" +
                                                            "INSERT INTO log_reg_audit (‘fishery_code’, ‘boat_record_no’, ‘first_date’, ‘last_date’, ‘log_type’, ‘log_no’, ‘first_page_no’, ‘last_page_no’, ‘file_id’, ‘received_time’, ‘received_by’, ‘operations_entered’, ‘operations_verified’, ‘operations_completed’, ‘log_status_flag’, ‘receipt_notice_id’, ‘receipt_issue_date’)" +
                                                            "SELECT (‘fishery_code’, ‘boat_record_no’, ‘first_date’, ‘last_date’, ‘log_type’, ‘log_no’, ‘first_page_no’, ‘last_page_no’, ‘file_id’, ‘received_time’, ‘received_by’, ‘operations_entered’, ‘operations_verified’, ‘operations_completed’, ‘log_status_flag’, ‘receipt_notice_id’, ‘receipt_issue_date’)" +
                                                            "FROM deleted";
                        */
                        // entire connection code is buggy - creating program freeze:
                        // from here to the line "conn.Close()"
                        // make the connection as per given specifications
                        SqlConnection conn = new SqlConnection (source);
                        MessageBox.Show ("blah~");
                        conn.Open();
                        
                        /*
                        // execute commands
                        SqlCommand cmd_op = new SqlCommand (select_op, conn);
                        cmd_op.ExecuteNonQuery();
                        SqlCommand cmd_op_del = new SqlCommand (delete_op, conn);
                        cmd_op_del.ExecuteNonQuery();
                        SqlCommand cmd_op_audit = new SqlCommand (record_op, conn);
                        cmd_op_audit.ExecuteNonQuery();

                        SqlCommand cmd_op_spe = new SqlCommand (select_op_spe, conn);
                        cmd_op_spe.ExecuteNonQuery();
                        SqlCommand cmd_op_spe_del = new SqlCommand (delete_op_spe, conn);
                        cmd_op_spe_del.ExecuteNonQuery();
                        SqlCommand cmd_op_spe_audit = new SqlCommand (record_op_spe, conn);
                        cmd_op_spe_audit.ExecuteNonQuery();
               
                        SqlCommand cmd_log_reg = new SqlCommand (select_log_reg, conn);
                        cmd_log_reg.ExecuteNonQuery();
                        SqlCommand cmd_log_reg_del = new SqlCommand (delete_log_reg, conn);
                        cmd_log_reg_del.ExecuteNonQuery();
                        SqlCommand cmd_log_reg_audit = new SqlCommand (record_log_reg, conn);
                        cmd_log_reg_audit.ExecuteNonQuery();
                        */
                        //conn.Close();
0
 
pikapiAuthor Commented:
erk... that's longer than i expected... but that's what i've got... it's all under a button so it starts after the button is clicked... i've narrowed it down to the line "conn.open()" as where it freezes i think (from commenting out everything, and the messagebox before conn.open() pops up just before the program goes into freeze mode)

some of the comments can probably be ignored, i just use them as reminders later...
0
 
smeggheadCommented:
can you confirm that it is getting past the connection line, as it's not worth looking at the rest if that doesn't work.

Do you have visual studio?? can you not step through the code ??

Smg.
0
 
pikapiAuthor Commented:
i can't figure out how to step into that section... stepping into it goes through all the form code and then hits "Application.Run(new Form1());" and just starts the program... how can i step thru button code? Aside from that I'm 99% sure it's the connection code that's causing problems, becasue the triggers have already been tested in something else.  And i have no idea what's wrong with the connection code tho... (thanks for all ur help btw smg!!)
0
 
smeggheadCommented:
can you connect to the database using query analyser?? or using any other tool ? it may be a problem with your installation. Have you tried shutting down your machine and restarting?

PS. off to bed now, so won't be replying for a bit
0
 
pikapiAuthor Commented:
ahh we figured it out :) by changing the uid and pwd section with Integrated Security = SSPI; and then it works!! :) Thanks for all ur help!!
0
 
pikapiAuthor Commented:
mmm now that the server connects, i find that mysql command may have been wrong... the program now breaks at the sql query... can anyone tell me what's wrong with this:
string select_test = "CREATE TRIGGER testing_trig" +
"ON test_temp" +
"FOR INSERT" +
"AS" +
"INSERT INTO temp ('code', 'name', 'grade')" +
"SELECT ('code', 'name', 'grade')" +
"FROM inserted";

SqlCommand cmd_test = new SqlCommand (select_test, conn);
cmd_test.ExecuteNonQuery();

am i meant to use ...ExecuteNonQuery() for triggers?? Or is there something wrong with the SQL I'm using to create the trigger?
0
 
smeggheadCommented:
Are you re-creating the trigger every time you run ?? a trigger is a like a stored procedure, it is stored against the database, and will exist and be active until you drop it.

executenonquery should be fine.

It is definetely worth putting a try/catch statement around your execute statement.. i.e.

try
{
        cmd_test.ExecuteNonQuery();
}
catch (SqlException se)
{
        MessageBox.Show(se.Message);
}

this gives you more accurate info about the error encountered.

HTH
Smg.
0
 
pikapiAuthor Commented:
does this mean that I should create and then drop everytime i use that trigger? is a trigger the best way to do it?
0
 
pikapiAuthor Commented:
string select_test = "CREATE TRIGGER test_trig" +
"ON test" +
"AFTER INSERT" +
"AS" +
"INSERT INTO test_temp (code, name, grade)" +
"SELECT code, name, grade" +
"FROM inserted";

The error reads: "Line1: Incorrect syntax near 'testAFTER'"?!?!?!?!?
0
 
pikapiAuthor Commented:
i'm also wondering if a trigger is really feasible... some of these tables i use are fairly large so i think it thinks for a quite a while before finishing... (maybe i should make this a new question too?)
0
 
Duy PhamFreelance IT ConsultantCommented:
You forgot to add space between connected texts. Try to do like this ( add a space before each new line-text ).

string select_test = "CREATE TRIGGER test_trig" +
" ON test" +
" AFTER INSERT" +
" AS" +
" INSERT INTO test_temp (code, name, grade)" +
" SELECT code, name, grade" +
" FROM inserted";
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now