Posts

Showing posts with the label databases

C# execute a stored procedure using SQL Server

Below we have some code that opens a connection to a database using a connection string then executes a stored procedure. This obviously needs to be placed in some method or class. You will need to add your own connection string and parameter values. try { using( SqlConnection connection = new SqlConnection("some connection string" ) ) { using( SqlCommand command = new SqlCommand( "stored procedure name", connection ) ) { command.CommandType = CommandType.StoredProcedure; // this line needs to be added for every parameter the SP expects command.Parameters.AddWithValue( "parametere value", paramValue ); connection.Open(); // based on the what the SP does, call the execute non query, execute scalar, or execute reader method command.ExecuteNonQuery(); } } } cat...

C# using a transaction with ODBC

Let's say your writing some data to the database and you need to make it transactional.  You know where you need to rollback multiple statements or use multiple statements in one go.  Normally you would do this in the database itself.  But I have found a few times where you have to do this in .net.  My latest was using mySQL to get the new index after I did an insert.  I found the SQL to do so would work fine in a mySQL command prompt or in the phpMyAdmin SQL pane but their .net ODBC driver simply wouldn't allow it.  Epic .net fail mySQL I guess your love for PHP has clouded your vision.  So you end up needing to wrap up multiple SQL calls in a transaction to accomplish this.  I have also had to do this it SQLSERVER but not on a scale like this.  But regardless this is how you can use a .net transaction with multiple SQL statements. You need to create an OdbcConnection.  Then an OdbcCommand and OdbcTransaction.  Set the command an...