by
61 8
1
5
4,732
13
Top 1% !
Popular
Famous
Specified
Refined
Popularity: 1753rd place
Created
Modified Sep 28, 2013
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