Snip2Code is shutting down.
It has been quite a ride, since 2013 when we launched our first prototype: thanks to the effort of you guys we collected more than 3 million snippets!
We are very proud to help all our users to be more efficient in their jobs, and to be the central point to share programming knowledge for everyone.
Our basic service is free, so we always survived on our own resources to give you Snip2Code.
Unfortunately, we are no more in the financial position to sustain this effort, and therefore we are announcing here our permanent shut down,
which will take place on August 1st, 2020.
Please save your private snippets using our backup function in the settings, here.
IF YOU WANT TO SAVE SNIP2CODE, PLEASE CONSIDER DOING A DONATION!
This will allow us to pay for the servers and the infrastructure. If you want to donate, Contact Us!
by
61 8
1
4
4,824
13
Top 1% !
Famous
Specified
Refined
Popularity: 1753rd place
This snippet is Public
No tags for this snippet yet.
.NETFramework4.0
Architecturex86_64
ClassNameMySqlException
DBMSMS SQL Server
Extension.cs
LanguageCSharp
LicenseMIT_X11
MethodNameNeedsRetry
OSWindows
OutputTypebool
Windows VersionWindows7

Execute SQL Server Stored Procedure in C#

This is the most effective and generic way to invoke a SP from C#. It can retry to invoke it other times, if needed, depending on the logic placed in MySqlException.NeedsRetry() method.
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/11264/Execute-SQL-Server-Stored-Procedure-in-C?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
static private bool ExecNonQuerySP(this string spName, SqlParameter[] sqlParams, string outputParamName, out object outputValue, int timeout = -1) { //System.Diagnostics.Debug.WriteLine(string.Format("invoking {0}", spName)); bool res = false; outputValue = null; using (SqlConnection dbConnection = Connect()) { SqlCommand sqlCmd = CreateStoredProc(dbConnection, spName); if (sqlParams != null) { sqlCmd.Parameters.AddRange(sqlParams); } if (timeout > 0) sqlCmd.CommandTimeout = timeout; int retries = 0; while (retries < 2) { try { dbConnection.Open(); sqlCmd.ExecuteNonQuery(); if (!string.IsNullOrEmpty(outputParamName)) outputValue = sqlCmd.Parameters[outputParamName].Value; if ((outputValue == null) || (outputValue.Equals(DBNull.Value))) outputValue = null; res = true; break; } catch (SqlException queryErr) { MySqlException lastExc = new MySqlException(queryErr); //retry another time, if needed: if (lastExc.NeedsRetry()) { log.Error(spName + ":" + s_lastExc); retries++; } else { log.Error(spName + ":" + s_lastExc); res = false; break; } } catch (Exception genericErr) { log.Error(spName + ":" + genericErr); res = false; break; } finally { if (dbConnection != null) dbConnection.Close(); } } sqlCmd.Parameters.Clear(); } return res; } static public SqlCommand CreateStoredProc(SqlConnection connection, string name) { SqlCommand newSPCmd = new SqlCommand(); newSPCmd.CommandType = CommandType.StoredProcedure; newSPCmd.Connection = connection; newSPCmd.CommandText = name; newSPCmd.CommandTimeout = 90; return newSPCmd; } public class MySqlException : Exception { private SqlException m_sqlExc; public MySqlException(SqlException exc) : base(exc.Message, exc) { m_sqlExc = exc; } public int ErrorCode { get { return m_sqlExc.ErrorCode; } } public override string Message { get { return m_sqlExc.Message; } } public override string StackTrace { get { return m_sqlExc.StackTrace; } } public SqlErrorCollection Errors { get { return m_sqlExc.Errors; } } /// <summary> /// Returns true if the SQL command that produced this exception needs to be /// re-executed another time. /// </summary> /// <returns></returns> public bool NeedsRetry() { if (m_sqlExc.Message.Contains("Timeout expired") || m_sqlExc.Message.Contains("Thread was being aborted") || m_sqlExc.Message.Contains("Rerun the transaction.")) return true; return false; } }
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus