c#, .net and Sql server tutorials: 2019

1. Create Mvc project

Webconfig

<configuration>
<connectionStrings configSource="ConnectionStrings.config" />
<appSettings>

ConnectionStrings.config

<?xml version="1.0"?>
<connectionStrings>
<add name="SelfCore" connectionString="Server=VS-BALAMURUGANG;Database=Test;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

</connectionStrings>

AccountViewModel
-- Add contract reference
namespace Self.Portal.Models
{
public class AccountViewModel
{
public List<Account> RegisterList { get; set; }
}

}

Controller
--add using Self.Contracts;
--using Self.Portal.Models;
--using Self.provider;

---------
public AccountProvider _accountProvider = new AccountProvider();
[HttpGet]
public ActionResult AddRegister()
{
//AccountViewModel _accountViewModel = new AccountViewModel();
//List<Account> account = new List<Account>();
//account = _accountProvider.Register();
//_accountViewModel.RegisterList = account;
return View();
}

[HttpPost]
public ActionResult AddRegister(FormCollection form)
{
string Name = Convert.ToString(form["txtName"]);
string City = Convert.ToString(form["txtCity"]);
string Address = Convert.ToString(form["txtAddress"]);

AccountViewModel _accountViewModel = new AccountViewModel();
List<Account> account = new List<Account>();
account = _accountProvider.Register(Name, City,Address);
_accountViewModel.RegisterList = account;
return View(_accountViewModel);
}

public ActionResult ViewRegister()
{
AccountViewModel _accountViewModel = new AccountViewModel();
List<Account> account = new List<Account>();
account = _accountProvider.ViewRegister();
_accountViewModel.RegisterList = account;
return View(_accountViewModel);
}
--------

AddRegister.cshtml
--------------------------

@{
ViewBag.Title = "AddRegister";
}

<h2>AddRegister</h2>
@using (Html.BeginForm("AddRegister", "Account", FormMethod.Post, new { enctype = "multipart/form-data", @id = "formAddRegister" }))
{
<div class="page-wrapper">
<div class="page-body">
<div class=".form-control">
<div class="card">
<div class="card-header">
<h5 class="">Add Banner</h5>
</div>
<div class="card-block">
<div class="row">
<div class="col-md-12 text-right m-b-15">
<a href="/Account/EditRegister" class="btn btn-primary btn-round">Edit Employee</a>
</div>
<div class="col-md-12" style="margin-top:20px;">

@*<div class="form-group row">
<div class="col-sm-12">
<div class="alert alert-success border-success">
@ViewBag.Message
</div>
<div class="alert alert-danger border-danger">
@ViewBag.ErrorMessage
</div>
@*</div>
</div>*@
<input id="BannerId" name="BannerId" type="hidden" value="0" />
<input id="rbStatus" name="rbStatus" type="hidden" value="A" />
<input id="hdnImagepath" name="hdnImagepath" type="hidden" />
<div id="resultdiv">

</div>
<div class="form-group row">
<div class="col-sm-3">
<label for="lblBannerName" class="block">Name *</label>
</div>
<div class="col-sm-9">
<input id="txtBannerName" name="txtName" type="text" class="form-control" placeholder="" maxlength="50" required value="">
<span class="messages"></span>
</div>
</div>
<div class="form-group row">
<div class="col-sm-3">
<label for="lblBannerName" class="block">City *</label>
</div>
<div class="col-sm-9">
<input id="txtBannerName" name="txtCity" type="text" class="form-control" placeholder="" maxlength="50" required value="">
<span class="messages"></span>
</div>
</div>
<div class="form-group row">
<div class="col-sm-3">
<label for="lblBannerName" class="block">Address *</label>
</div>
<div class="col-sm-9">
<input id="txtBannerName" name="txtAddress" type="text" class="form-control" placeholder="" maxlength="50" required value="">
<span class="messages"></span>
</div>
</div>
<div class="text-center">
<button type="submit" class="btn btn-primary btn-round" id="Save">Save</button>
<a id="ancReset" href="/Banner/AddBanner" class="btn btn-primary btn-round">Reset</a>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
}

-----------
ViewRegister.cshtml
--------------------------

@{
ViewBag.Title = "ViewRegister";
}

<h2>ViewRegister</h2>

<div>
@if (Model.RegisterList.Count > 0)
{
<table class="table table-styling table-hover table-striped contribution-table table-responsive">
<thead>
<tr>
<td>S.No</td>
<td>Title</td>
<td>Training Type</td>
<td>Category</td>
<td>Author</td>
<td>Display Image</td>
<td>Start Date</td>
<td>End Date</td>
<td>IsEnable</td>
<td>Priority</td>
<td style="width:200px;text-align:center;">Action</td>
</tr>
</thead>
<tbody>
@{
int itr = 0;
foreach (var listItem in Model.RegisterList)
{
<tr>
<td>@(itr + 1)</td>
<td>
<label class="TaskName">@listItem.Name</label>
<input type="hidden" class="TaskIDHidden" value="@listItem.Name" />
</td>
</tr>
itr = itr + 1;
}
}
</tbody>
</table>
}
else
{
<div class="alert alert-danger background-warning">
No Records Found.
</div>
}
</div>
--------------------------------

CREATE TABLE [dbo].[tblEmployee1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Address] [varchar](50) NULL
---------------------

CREATE procedure [dbo].[PROC_AddEmployee]
(
@Name varchar (50),
@City varchar (50),
@Address varchar (50)
)
as
begin
Insert into tblEmployee1 values(@Name,@City,@Address)
End

--------------------------
CREATE procedure [dbo].[PROC_GetRegister1]

as
begin
Select * from tblEmployee1
End

-------------------------------
-------------------------------
ProjectName.Contracts

Account.cs

public class Account
{
public string Name { get; set; }
public string City { get; set; }
public string Address { get; set; }

}
-----------------------------

ProjectName.Provider

AccountProvider.cs

public class AccountProvider
{
AccountRepository accountRepository = new AccountRepository();
public List<Account> Register()
{
//List<Account> accounts = accountRepository.GetRegisterList();
//return accounts;
return accountRepository.GetRegisterList();
}
public List<Account> Register(string Name,string City,string Address)
{
//List<Account> accounts = accountRepository.GetRegisterList();
//return accounts;
return accountRepository.GetRegisterList(Name, City, Address);
}
public List<Account> ViewRegister()
{
return accountRepository.ViewRegisterList();
}

}
------------------------------
ProjectName.Repository

AccountRepository

public class AccountRepository:BaseRepository
{
public List<Account> GetRegisterList()
{
//Account _accounts = null;
List<Account> _accountsList = new List<Account>();
//string getTrainingType = AdminResource.GetFutureTrainingById;
var dbCommand = SelfCore.GetStoredProcCommand("PROC_GetRegister");
SelfCore.AddInParameter(dbCommand, "@Name", DbType.String, "Bala");
using (var dataReader = SelfCore.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
_accountsList.Add(new Account
{
Name = GetStringValue(dataReader, "Name")

});
}
}
return _accountsList;
}

public List<Account> GetRegisterList(string Name,string City,string Address)
{
List<Account> _accountsList = new List<Account>();
var dbCommand = SelfCore.GetStoredProcCommand("PROC_GetRegister");
SelfCore.AddInParameter(dbCommand, "@Name", DbType.String, Name);
SelfCore.AddInParameter(dbCommand, "@City", DbType.String, City);
SelfCore.AddInParameter(dbCommand, "@Address", DbType.String, Address);
using (var dataReader = SelfCore.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
_accountsList.Add(new Account
{
Name = GetStringValue(dataReader, "Name"),
City = GetStringValue(dataReader, "City"),
Address = GetStringValue(dataReader, "Address")
});
}
}
return _accountsList;
}

public List<Account> ViewRegisterList()
{
List<Account> _accountsList = new List<Account>();
var dbCommand = SelfCore.GetStoredProcCommand("PROC_GetRegister1");
using (var dataReader = SelfCore.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
_accountsList.Add(new Account
{
Name = GetStringValue(dataReader, "Name"),
City = GetStringValue(dataReader, "City")
});
}
}
return _accountsList;
}

}

---------------------------
BaseRepository

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Self.Repository
{
public class BaseRepository
{
static Database _SelfCore;

public static Database SelfCore
{
get
{
return _SelfCore;
}
}

/// <summary>
/// Initializes a new instance of the <see cref="BaseRepository"/> class.
/// </summary>
public BaseRepository()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
Database core = factory.Create(_Database.SelfCore);
_SelfCore = core;
}

public struct _Database
{
public const string SelfCore = "SelfCore";
}

/// <summary>
/// Gets the integer value.
/// </summary>
/// <param name="dataReader">The data reader.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
protected Int32 GetIntegerValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

Int32 valueToReturn = default(Int32);

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (Int32)value;
}

return valueToReturn;
}

/// <summary>
/// Gets the long value.
/// </summary>
/// <param name="dataReader">The data reader.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
protected Int64 GetLongValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

Int64 valueToReturn = default(Int64);

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (Int64)value;
}

return valueToReturn;
}

/// <summary>
/// Gets the string value.
/// </summary>
/// <param name="dataReader">The data reader.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
protected string GetStringValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

string valueToReturn = null;

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = value.ToString();
}

return valueToReturn;

}

/// <summary>
/// Gets the char value.
/// </summary>
/// <param name="dataReader">The data reader.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
protected char GetCharValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

char valueToReturn = default(char);

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = Convert.ToChar(value);
}

return valueToReturn;

}

/// <summary>
/// Gets the date value.
/// </summary>
/// <param name="dataReader">The data reader.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
protected DateTime? GetDateValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

DateTime? valueToReturn = null;

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (DateTime?)value;
}

return valueToReturn;

}

/// <summary>
/// To return the Int64 value
/// </summary>
/// <param name="dataReader"></param>
/// <param name="columnName"></param>
/// <returns></returns>
protected Guid GetGuidValue(IDataReader dataReader, string columnName)
{

//Retreive the column from the data reader
object value = dataReader[columnName];
Guid valueToReturn = new Guid();
//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (Guid)value;
}
return valueToReturn;
}

/// <summary>
/// Get the Bool value from Small int
/// </summary>
/// <param name="dataReader">The data reader.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns>It returns the Boolvalue</returns>
protected bool GetSmallIntBoolValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];
bool valueToReturn = false;

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
if ((Int16?)value == 1) { valueToReturn = true; }
}
return valueToReturn;
}

protected bool GetBoolValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];
bool valueToReturn = false;

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (bool)value;
}
return valueToReturn;
}

protected double GetDoubleValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

double valueToReturn = default(double);

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (double)value;
}

return valueToReturn;
}

protected decimal GetDecimalValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

decimal valueToReturn = default(decimal);

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (decimal)value;
}

return valueToReturn;
}
protected TimeSpan GetTimeSpanValue(IDataReader dataReader, string columnName)
{
//Retreive the column from the data reader
object value = dataReader[columnName];

TimeSpan valueToReturn = default(TimeSpan);

//If the retrieved value is not null then cast it to the correct type
if (!(value is DBNull))
{
valueToReturn = (TimeSpan)value;
}

return valueToReturn;
}

/// <summary>
/// GetCurrentCulture
/// </summary>
/// <returns></returns>
protected string GetCurrentCulture()
{
return Thread.CurrentThread.CurrentUICulture.ToString();
}
}

public static class DataRecordExtensions
{
public static bool HasColumn(this IDataRecord dr, string columnName)
{
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
return true;
}
return false;
}
}
}

Base Contract

namespace GCE.Contracts.Shared
{
public class BaseContract
{
/// <summary>
/// Get or Set CreatedBy.
/// </summary>
public string CreatedByName { get; set; }

/// <summary>
/// Get or Set CreatedBy.
/// </summary>
public long CreatedBy { get; set; }

/// <summary>
/// Get or Set CreatedDate.
/// </summary>
public DateTime CreatedDateTime { get; set; }

public string UpdatedByName { get; set; }

/// <summary>
/// Get or Set UpdatedBy.
/// </summary>
public long UpdatedBy { get; set; }

/// <summary>
/// Get or Set UpdatedDate.
/// </summary>
public DateTime? UpdatedDateTime { get; set; }

/// <summary>
/// Get or Set Status.
/// </summary>
public char Status { get; set; }
}

}

Without BaseRepository

public List<Account> GetRegisterList(string Name, string City, string Address)
{
//Account _accounts = null;
List<Account> _accountsList = new List<Account>();
var constring = ConfigurationManager.ConnectionStrings["SelfCore"].ToString();
SqlConnection con = new SqlConnection(constring);
con.Open();
SqlCommand cmd = new SqlCommand("PROC_GetRegister", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Name", Name));
cmd.Parameters.Add(new SqlParameter("@City", City));
cmd.Parameters.Add(new SqlParameter("@Address", Address));
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
Account AM = new Account();
AM.Name = dr.GetString(1);
AM.Address = dr.GetString(2);
AM.City = dr.GetString(3);
// -- AM.Description = dr.GetGuid(4);
// -- AM.ImageUrl = dr.GetString(5);
// -- AM.Latitude = dr.GetDecimal(6);
// -- AM.Longitude = dr.GetDecimal(7);
//-- AM.Location = dr.GetString(8);
// -- AM.ArticleURL = dr.GetString(9);
_accountsList.Add(AM);
}
}
catch (Exception ex)
{

}
con.Close();
return _accountsList;
}

c#, .net and Sql server tutorials

Wednesday 13 March 2019

Not fired method when using interface