Sql Class for executing SQL Query | C# Online Compiler

Sql Class for executing SQL Query by Pratik Bhoir

using System;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;

namespace HelloWorldMvcApp
{
	public class SampleViewModel
	{
		[Required]
		[MinLength(10)]
		[MaxLength(100)]
		[Display(Name = "Ask Magic 8 Ball any question:")]
		public string Question { get; set; }

		//See here for list of answers
		public string Answer { get; set; }
	}
	
	
	public class Sql
	{
		public static DataSet ExecuteStoredProcedure(string storedProcedureName, IEnumerable<SqlParameter> parameters)
		{
			//FMSEntities context = new FMSEntities();
			//var dfds = (SqlConnection)context.Database.Connection;
			var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; //context.Database.Connection.ConnectionString;//((EntityConnection)context.Connection).StoreConnection.ConnectionString;
			var ds = new DataSet();
			using (var conn = new SqlConnection(connectionString))
			{
				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = storedProcedureName;
					cmd.CommandType = CommandType.StoredProcedure;
					foreach (var parameter in parameters)
					{
						cmd.Parameters.Add(parameter);
					}
	
					using (var adapter = new SqlDataAdapter(cmd))
					{
						adapter.Fill(ds);
					}
				}
			}
	
			return ds;
		}
	}
	
	
}

Model

​x
 
using System;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
​
namespace HelloWorldMvcApp
{
    public class SampleViewModel
    {
        [Required]
        [MinLength(10)]
        [MaxLength(100)]
        [Display(Name = "Ask Magic 8 Ball any question:")]
        public string Question { get; set; }
​
        //See here for list of answers
        public string Answer { get; set; }
    }
    
    
    public class Sql
    {
        public static DataSet ExecuteStoredProcedure(string storedProcedureName, IEnumerable<SqlParameter> parameters)
        {
            //FMSEntities context = new FMSEntities();
            //var dfds = (SqlConnection)context.Database.Connection;
            var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; //context.Database.Connection.ConnectionString;//((EntityConnection)context.Connection).StoreConnection.ConnectionString;
            var ds = new DataSet();
            using (var conn = new SqlConnection(connectionString))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = storedProcedureName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
    
                    using (var adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }
            }
    
            return ds;
        }
    }
    
    
}

@model HelloWorldMvcApp.SampleViewModel
@{
	Layout = null;
}

<!DOCTYPE html>
<!-- template from http://getbootstrap.com/getting-started -->

<html lang="en">
	<head>
		<meta charset="utf-8">
		<meta http-equiv="X-UA-Compatible" content="IE=edge">
		<meta name="viewport" content="width=device-width, initial-scale=1">
		<title>Bootstrap 101 Template</title>

		<!-- CSS Includes -->
		<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
		
		<style type="text/css">

			.field-validation-error {
				color: #ff0000;
			}

		</style>
	</head>
	
	<body>
		<div class="container">
			<div class="col-md-6 col-md-offset-3">
				<h1>Hello Stranger</h1>
	
				@using (Html.BeginForm())
				{
					<div class="form-group">
						@Html.LabelFor(m => m.Question)
						@Html.TextBoxFor(model => model.Question, new {@class="form-control"}) 
						@Html.ValidationMessageFor(model => model.Question)
					</div>
				
					<button type="button" class="btn btn-success submit">Ask</button>
				}
	
				<br/><br/>
				<div class="alert alert-warning fade">
					<img src="//dl.dropboxusercontent.com/s/lq0mgxtxtc4uj1e/morph.jpg?dl=1&token_hash=AAGm5lEcLzicmV_-T4h6Hc_3iBvhKVerZlOjvGP_vjpoJQ" /><br/><br/>
					<strong><span class="alert-content"></span></strong>
				</div>
			</div>
		</div>

		<!-- JS includes -->
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
		<script src="//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
	
		<script src="//ajax.aspnetcdn.com/ajax/jquery.validate/1.11.1/jquery.validate.min.js"></script>
		<script src="//ajax.aspnetcdn.com/ajax/mvc/4.0/jquery.validate.unobtrusive.min.js"></script>
		
		<script type="text/javascript">
		
			function openAlert(txt) {
				$('.alert-content').text(txt);
				$('.alert').addClass('in');
			}
		
			function closeAlert() {
				$('.alert').removeClass('in');
			}
		
			$(function(){
				var answer = '@Model.Answer';
		
				if(answer && answer != '') 
					openAlert(answer);
		
				$('#Question').change(closeAlert);
				$('#Question').keyup(closeAlert);
		
				$('.submit').click(function(){
					if($('form').valid()) {
					
						$.ajax({
							url: '@Url.RouteUrl(new{ action="GetAnswer", controller="Home"})',
							data: {Answer: '', Question: $('#Question').val()},
								type: 'POST',
								dataType: 'json',
								contentType: "application/json; charset=utf-8",
								success: function(resp) {
								openAlert(resp);
						}});
					}
					else {
						closeAlert();
					}
				});
			
			});

		</script>
	</body>
</html>

View

 
@model HelloWorldMvcApp.SampleViewModel
@{
    Layout = null;
}
​
<!DOCTYPE html>
<!-- template from http://getbootstrap.com/getting-started -->
​
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>Bootstrap 101 Template</title>
​
        <!-- CSS Includes -->
        <link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
        
        <style type="text/css">
​
            .field-validation-error {
                color: #ff0000;
            }
​
        </style>
    </head>
    
    <body>
        <div class="container">
            <div class="col-md-6 col-md-offset-3">
                <h1>Hello Stranger</h1>
    
                @using (Html.BeginForm())
                {
                    <div class="form-group">
                        @Html.LabelFor(m => m.Question)
                        @Html.TextBoxFor(model => model.Question, new {@class="form-control"}) 
                        @Html.ValidationMessageFor(model => model.Question)
                    </div>
                
                    <button type="button" class="btn btn-success submit">Ask</button>
                }
    
                <br/><br/>
                <div class="alert alert-warning fade">
                    <img src="//dl.dropboxusercontent.com/s/lq0mgxtxtc4uj1e/morph.jpg?dl=1&token_hash=AAGm5lEcLzicmV_-T4h6Hc_3iBvhKVerZlOjvGP_vjpoJQ" /><br/><br/>
                    <strong><span class="alert-content"></span></strong>
                </div>
            </div>
        </div>
​
        <!-- JS includes -->
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
        <script src="//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
    
        <script src="//ajax.aspnetcdn.com/ajax/jquery.validate/1.11.1/jquery.validate.min.js"></script>
        <script src="//ajax.aspnetcdn.com/ajax/mvc/4.0/jquery.validate.unobtrusive.min.js"></script>
        
        <script type="text/javascript">
        
            function openAlert(txt) {
                $('.alert-content').text(txt);
                $('.alert').addClass('in');
            }
        
            function closeAlert() {
                $('.alert').removeClass('in');
            }
        
            $(function(){
                var answer = '@Model.Answer';
        
                if(answer && answer != '') 
                    openAlert(answer);
        
                $('#Question').change(closeAlert);
                $('#Question').keyup(closeAlert);
        
                $('.submit').click(function(){
                    if($('form').valid()) {
                    
                        $.ajax({
                            url: '@Url.RouteUrl(new{ action="GetAnswer", controller="Home"})',
                            data: {Answer: '', Question: $('#Question').val()},
                                type: 'POST',
                                dataType: 'json',
                                contentType: "application/json; charset=utf-8",
                                success: function(resp) {
                                openAlert(resp);
                        }});
                    }
                    else {
                        closeAlert();
                    }
                });
            
            });
​
        </script>
    </body>
</html>
​

using System;
using System.Web.Mvc;
using System.Collections.Generic;

namespace HelloWorldMvcApp
{
	public class HomeController : Controller
	{
		[HttpGet]
		public ActionResult Index()
		{
			return View(new SampleViewModel());
		}


		[HttpPost]
		public JsonResult GetAnswer(string question)
		{				
			int index = _rnd.Next(_db.Count);
			var answer = _db[index];
			return Json(answer);
		}

		private static Random _rnd = new Random();
		
		private static List<string> _db = new List<string> { "Yes", "No", "Definitely, yes", "I don't know", "Looks like, yes"} ;
	}
}

Controller

 
using System;
using System.Web.Mvc;
using System.Collections.Generic;
​
namespace HelloWorldMvcApp
{
    public class HomeController : Controller
    {
        [HttpGet]
        public ActionResult Index()
        {
            return View(new SampleViewModel());
        }
​
​
        [HttpPost]
        public JsonResult GetAnswer(string question)
        {               
            int index = _rnd.Next(_db.Count);
            var answer = _db[index];
            return Json(answer);
        }
​
        private static Random _rnd = new Random();
        
        private static List<string> _db = new List<string> { "Yes", "No", "Definitely, yes", "I don't know", "Looks like, yes"} ;
    }
}

View in Full Screen

Out of memory.
Command terminated by signal 6

Last Run:	11:20:47 pm
Compile:	0.009s
Execute:	0.41s
Memory:	64.97Mb
CPU:	0.419s

View IL Code