Solved

SQL in C#

Posted on 2003-11-27
22
664 Views
Last Modified: 2011-09-20
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
Comment
Question by:pikapi
  • 12
  • 6
  • 3
  • +1
22 Comments
 
LVL 10

Expert Comment

by:smegghead
ID: 9834717
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
 

Author Comment

by:pikapi
ID: 9834808
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
 
LVL 10

Expert Comment

by:smegghead
ID: 9836840
If the databases exist on the same SQL server, then it's easy...

select * from OtherDatabase.dbo.TheTable
0
 

Expert Comment

by:atomicchip
ID: 9838327
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
 
LVL 10

Expert Comment

by:Duy Pham
ID: 9845167
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
 
LVL 10

Expert Comment

by:Duy Pham
ID: 9845186
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
 

Author Comment

by:pikapi
ID: 9847026
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
 

Author Comment

by:pikapi
ID: 9847032
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
 

Author Comment

by:pikapi
ID: 9847033
PS. if it helps at all... I'm using MSSQLServer that's available free on the Microsoft website
0
 
LVL 10

Expert Comment

by:smegghead
ID: 9847051
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
 

Author Comment

by:pikapi
ID: 9847071
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:pikapi
ID: 9847082
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
 
LVL 10

Expert Comment

by:smegghead
ID: 9847101
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
 

Author Comment

by:pikapi
ID: 9847190
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
 
LVL 10

Accepted Solution

by:
smegghead earned 250 total points
ID: 9847219
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
 

Author Comment

by:pikapi
ID: 9847323
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
 

Author Comment

by:pikapi
ID: 9847740
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
 
LVL 10

Expert Comment

by:smegghead
ID: 9849541
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
 

Author Comment

by:pikapi
ID: 9853617
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
 

Author Comment

by:pikapi
ID: 9853655
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
 

Author Comment

by:pikapi
ID: 9853668
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
 
LVL 10

Expert Comment

by:Duy Pham
ID: 9855978
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now