{"id":424,"date":"2012-02-26T19:28:44","date_gmt":"2012-02-26T19:28:44","guid":{"rendered":"http:\/\/devnot.wordpress.com\/?p=424"},"modified":"2012-02-26T19:28:44","modified_gmt":"2012-02-26T19:28:44","slug":"executing-a-sql-stored-procedure-and-read-the-result","status":"publish","type":"post","link":"https:\/\/thomas-jansen.eu\/?p=424","title":{"rendered":"Executing a SQL stored procedure and read the result"},"content":{"rendered":"<p>Self explanatory snippet<\/p>\n<pre>\nSqlConnection db = new SqlConnection(Properties.Settings.Default.ConnectionString);\n\ntry\n{\n   db.Open();\n\n   SqlCommand cmd = new SqlCommand(\"storedProcedureName\", db);\n   cmd.CommandType = CommandType.StoredProcedure;\n   cmd.Parameters.Add(new SqlParameter(\"@parm1\", SqlDbType.Int, 4));\n   cmd.Parameters.Add(new SqlParameter(\"@parm2\", SqlDbType.Text));\n   cmd.Parameters[\"@parm1\"].Value = 1234;\n   cmd.Parameters[\"@parm2\"].Value = \"ABC\";\n\n   SqlDataReader dataReader = cmd.ExecuteReader();\n\n   DataTable storedTable = new DataTable();\n   storedTable.Load(dataReader);\n                \n   foreach (DataRow row in storedTable.Rows)\n   {\n      \/\/Here row contains the retrieved data.\n      int test = Convert.ToInt32(row[0]);\n   }\n   storedTable.Dispose();\n   dataReader.Dispose();\n   cmd.Dispose();\n          \n   db.Close(); \n   db.Dispose(); \n   db = null;\n}\ncatch (Exception e)\n{\n   System.Windows.Forms.MessageBox.Show(e.Message, \"DB error\", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);      \n}\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Self explanatory snippet SqlConnection db = new SqlConnection(Properties.Settings.Default.ConnectionString); try { db.Open(); SqlCommand cmd = new SqlCommand(&#8220;storedProcedureName&#8221;, db); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter(&#8220;@parm1&#8221;, SqlDbType.Int, 4)); cmd.Parameters.Add(new SqlParameter(&#8220;@parm2&#8221;, SqlDbType.Text)); cmd.Parameters[&#8220;@parm1&#8221;].Value = 1234; cmd.Parameters[&#8220;@parm2&#8221;].Value = &#8220;ABC&#8221;; SqlDataReader dataReader = cmd.ExecuteReader(); DataTable storedTable = new DataTable(); storedTable.Load(dataReader); foreach (DataRow row in storedTable.Rows) { \/\/Here row contains the retrieved data. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-424","post","type-post","status-publish","format-standard","hentry","category-c"],"_links":{"self":[{"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=\/wp\/v2\/posts\/424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=424"}],"version-history":[{"count":0,"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=\/wp\/v2\/posts\/424\/revisions"}],"wp:attachment":[{"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thomas-jansen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}