<add name=”connection” connectionString=”Data Source=***.***.*.*;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=SA**Uaser;Password=******” providerName=”System.Data.SqlClient”/>

Advertisements

DbHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace TestNameSpace
{
internal class DbHelper : IDisposable
{
public DbHelper()
{
OpenConnection();
}

#region PRIVATE MEMBERS

private SqlConnection _connection;

///

/// Gets Connection String from web configuration file
///

private static string ConnectionString
{
get { return ConfigurationManager.ConnectionStrings[“connection”].ConnectionString; }
}

#endregion

#region METHODS

///

/// Establishes connection with sql server
///

private void OpenConnection()
{
try
{
// create a new instance of sqlconnection class.
_connection = new SqlConnection(ConnectionString);

// if connection is open the close it and again open it.This approach is followed
// only for safety or ignoring the status any existing connection.
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
_connection.Open();
}
catch
{
throw new Exception(“An error occured while communicating to sql server database.”);
}
}

///

/// Closes existing sql server connection from database
///

private void CloseConnection()
{
try
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
}
finally
{
_connection.Dispose();
}
}

///

/// Executes any Create , Update or Delete query and in lieu returns the total
/// number of rows affected by the query
///

/// sql statement as string whether it is sp or inline query.
/// command type
///
public int ExecuteNonQuery(string commandText, CommandType commandType)
{
int result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
result = command.ExecuteNonQuery();
}
return result;
}

///

/// Executes any Create , Update or Delete query and in lieu returns the total
/// number of rows affected by the query
///

/// sql statement as string
/// type of command whether it is sp or inline query
/// sql parameter
///
public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter parameter)
{
int result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
result = command.ExecuteNonQuery();
command.Parameters.Clear();

}
return result;
}

///

/// Executes any Create , Update or Delete query and in lieu returns the total
/// number of rows affected by the query
///

/// sql statement as string
/// type of command whether it is sp or inline query
/// array of sql parameter
///
public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters)
{
int result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();

}
return result;
}

///

/// Executes any Create , Update, Delete or even Select query and in lieu returns the value
/// of first row and first column
///

/// sql statement as string whether it is sp or inline query.
/// command type
///
public object ExecuteScalar(string commandText, CommandType commandType)
{
object result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
result = command.ExecuteScalar();

}
return result;
}

///

/// Executes any Create , Update, Delete or even Select query and in lieu returns the value
/// of first row and first column
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// sql parameter
///
public object ExecuteScalar(string commandText, CommandType commandType, SqlParameter parameter)
{
object result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
result = command.ExecuteScalar();
command.Parameters.Clear();

}
return result;
}

///

/// Executes any Create , Update, Delete or even Select query and in lieu returns the value
/// of first row and first column
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// array of sql parameter
///
public object ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] parameters)
{
object result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
result = command.ExecuteScalar();
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the table of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// Datatable
public DataTable ExecuteSelect(string commandText, CommandType commandType)
{
var result = new DataTable();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
using (SqlDataReader reader = command.ExecuteReader())
{
result.Load(reader);
}
}
return result;
}

///

/// Executes Select query and in lieu returns the table of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// sql parameter
/// datatable
public DataTable ExecuteSelect(string commandText, CommandType commandType, SqlParameter parameter)
{
var result = new DataTable();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
using (SqlDataReader reader = command.ExecuteReader())
{
result.Load(reader);
}
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the table of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// array of sql parameter
/// Datatable
public DataTable ExecuteSelect(string commandText, CommandType commandType, SqlParameter[] parameters)
{
var result = new DataTable();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
using (SqlDataReader reader = command.ExecuteReader())
{
result.Load(reader);
}
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the set of tables of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// Datatset
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType)
{
var result = new DataSet();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
}
return result;
}

///

/// Executes Select query and in lieu returns the set of tables of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// sql parameter
/// dataset
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType, SqlParameter parameter)
{
var result = new DataSet();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the set of tables of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// array of sql parameter
/// Dataset
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType, SqlParameter[] parameters)
{
var result = new DataSet();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
command.Parameters.Clear();

}
return result;
}

#endregion

// Implement IDisposable.
// Do not make this method virtual.
// A derived class should not be able to override this method.

#region IDisposable Members

public void Dispose()
{
CloseConnection();

// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize(this);
}

#endregion
}
}

MVC Pagination

****Model***

using System;
using System.Collections.Generic;

namespace PaginationExample
{
public class IndexViewModel
{
public IEnumerable Items { get; set; }
public Pager Pager { get; set; }
}

public class Pager
{
public Pager(int totalItems, int? page, int pageSize = 10)
{
// calculate total, start and end pages
var totalPages = (int)Math.Ceiling((decimal)totalItems / (decimal)pageSize);
var currentPage = page != null ? (int)page : 1;
var startPage = currentPage – 5;
var endPage = currentPage + 4;
if (startPage totalPages)
{
endPage = totalPages;
if (endPage > 10)
{
startPage = endPage – 9;
}
}

TotalItems = totalItems;
CurrentPage = currentPage;
PageSize = pageSize;
TotalPages = totalPages;
StartPage = startPage;
EndPage = endPage;
}

public int TotalItems { get; private set; }
public int CurrentPage { get; private set; }
public int PageSize { get; private set; }
public int TotalPages { get; private set; }
public int StartPage { get; private set; }
public int EndPage { get; private set; }
}
}

*** view ***
@model PaginationExample.IndexViewModel

ASP.NET MVC – Pagination Example

ASP.NET MVC – Pagination Example

    @foreach (var item in Model.Items)
    {

  • @item
  • }


@if (Model.Pager.EndPage > 1)
{

    @if (Model.Pager.CurrentPage > 1)
    {

  • First
  • Previous
  • }

    @for (var page = Model.Pager.StartPage; page <= Model.Pager.EndPage; page++)
    {

  • @page
  • }

    @if (Model.Pager.CurrentPage < Model.Pager.TotalPages)
    {

  • Next
  • Last
  • }

}


*** Controller ***
using System;
using System.Web.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace PaginationExample
{
public class HomeController : Controller
{
[HttpGet]
public ActionResult Index(int? page)
{
var dummyItems = Enumerable.Range(1, 150).Select(x => “Item ” + x);
var pager = new Pager(dummyItems.Count(), page);

var viewModel = new IndexViewModel
{
Items = dummyItems.Skip((pager.CurrentPage – 1) * pager.PageSize).Take(pager.PageSize),
Pager = pager
};

return View(viewModel);
}
}
}

Copy From::
https://dotnetfiddle.net/Widget/eOe3tv

https://dotnetfiddle.net/eOe3tv

http://pramadha.com
http://shoessale.in

Code first database migration

Automated Migration:

Entity framework 4.3 has introduced Automated Migration so that you don’t have to process database migration manually in the code file, for each change you make in your domain classes. You just need to run a command in Package Manger Console to accomplish this.

Let’s see how you can use the automated migration.

As you know, you don’t have a database when you start writing your Code-First application. For example, we start writing an application with Student and Course entity classes. Before running the application, which does not have its database created yet, you have to enable automated migration by running the ‘enable-migrations’ command in Package Manager Console, as shown below:

First, open the package manager console from Tools → Library Package Manager → Package Manager Console and then run “enable-migrations –EnableAutomaticMigration:$true” command (make sure that the default project is the project where your context class is)

automated migration in code first

Once the command runs successfully, it creates an internal sealed Configuration class in the Migration folder in your project:

automated migration in code first

If you open this and see the class shown below, then you will find AutomaticMigrationsEnabled = true in the constructor.

     
    internal sealed class Configuration : DbMigrationsConfiguration<SchoolDataLayer.SchoolDBContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(SchoolDataLayer.SchoolDBContext context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }

You also need to set the database initializer in the context class with the new DB initialization strategy MigrateDatabaseToLatestVersion, as shown below:

public class SchoolDBContext: DbContext 
{
    public SchoolDBContext(): base("SchoolDBConnectionString") 
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<SchoolDBContext, SchoolDataLayer.Migrations.Configuration>("SchoolDBConnectionString"));
            
    }

    public DbSet<Student> Students { get; set; }
    public DbSet<Course> Courses { get; set; }
        
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        base.OnModelCreating(modelBuilder);
    }

}

As you can see in the code shown above, we have passed the Configuration class name, which was created by command, along with the context class name.

Now you are set for automated migration. It will automatically take care of migration, when you change the model. Run the application and look at the created database:

automated migration in code first

You will find that it has created one system table __MigrationHistory along with other tables. This is where automated migration maintains the history of database changes.

Now let’s add a Standard entity class. Run the application again and you will see that it has automatically created a Standard table.

Wait a minute, this works if you add a new entity class or remove an entity class, but what about adding or removing properties from the entity class? To try that, let’s remove the Description property from Standard class and run the application.

Oops…. an error message will appear:

automated migration in code first

This is because you will lose data in description column, if you remove it from the Standard class. So to handle this kind of scenario, you have to set AutomaticMigrationDataLossAllowed = true in the configuration class constructor, along with AutomaticMigrationsEnabled = true.

Note: You can find more information about parameters that we can pass to the enable-migrations command using the “get-help enable-migrations” command. For more detailed help use “get-help enable-migrations –detailed”

Thus, migration can be handled automatically.

c# Ajax calender with validation of date format

protected void Btn_Click(object sender, EventArgs e)
{
lblSSMIMessage.Text=””;
string myDateString = txtSendSmsToBusInchargeDate.Text;
string myDateFormat = “MMMM yyyy”;
DateTime outputDateTime;
if (DateTime.TryParseExact(myDateString, myDateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out outputDateTime))
{

lblSSMIMessage.Text = outputDateTime.ToString(“MMMM yyyy”);
}
else
{
lblSSMIMessage.Text = “Select valid month.”;
return;
}
}