Reading Excel From C# using OLEDB Connection

 First, You need to add below name spaces to read Excel file in OLEDB

using System.Data.OleDb;

using System.Configuration;

using System.Data.SqlClient;

And we need establish a connection to the Database and OLEDB. Add below connection code in web.config file

<connectionStrings>

   <add name="AD_Phase2" connectionString="Data Source=localhost;Database=DatabaseName;User Id=sa;Password=Password" providerName="System.Data.SqlClient"/>

    <add name="ExcelConnection" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=NO'\"/>

    <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO'"/>

    <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO'"/>

  </connectionStrings>

Below is the code for read the Excel data and fill into datatable

//Read the Excel file and fill in datatable .

        public DataTable GetDataTable(string strSheetName)

        {

            try

            {

                string strComand;

                if (strSheetName.IndexOf("|") > 0)

                {

                    /* if Range is provided.*/

                    string SheetName = strSheetName.Substring(0, strSheetName.IndexOf("|"));

                    string Range = strSheetName.Substring(strSheetName.IndexOf("|") + 1);

                    strComand = "select * from [" + SheetName + "$" + Range + "]";

                }

                else

                {

                    //= Images_Path + "" + SaveStudentDetails.FileName;

                    strComand = "select * from [Sheet1$]";

                }

                //HttpPostedFile StudentPhotoFile = SaveStudentDetails.PostedFile;

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FinalPath + ";Extended Properties='Excel 8.0;HDR=NO;'";

                OleDbConnection connection = new OleDbConnection(excelConnectionString);

                OleDbDataAdapter daAdapter = new OleDbDataAdapter(strComand, connection);

                DataTable dt = new DataTable("Datatable");

                DataColumn dc = new DataColumn();

                DataRow dr = dt.NewRow();

                dt.Rows.Add(dr);

                daAdapter.Fill(dt);

                return dt;

            }

            catch (Exception ex)

            {

                throw new Exception("Select location" + ex.Message);

            }

        }

Solid Principles in C#

The SOLID Design Principles and Design Patterns play an important role in achieving all of the above key points.

The SOLID Design Principles are the design principles that help us to solve most of the software design problems. These design principles provide us with multiple ways to move the tightly coupled code between the software components which makes the software designs more understandable, flexible, and maintainable. 

S stands for the Single Responsibility Principle which is also known as SRP.

O stands for the Open-Closed Principle which is also known as OSP.

L stands for the Liskov Substitution Principle which is also known as LSP.

I stand for the Interface Segregation Principle which is also known as ISP.

D stands for Dependency Inversion Principle which is also known as DIP.

Why we need Partial Views in ASP.NET MVC Application?

When we need a common part of the user interface at multiple pages in a web application then we develop a partial view, hence the partial view is a regular view that can be used multiple times in an application and has the file extension .cshtml.

Sometimes we also use a partial view to divide a web page into small parts such as header, footer, and menu on Layout. Other examples are comments in blogging site, shipping and billing address in the invoice in e-commerce site, etc. 

If you are coming from asp.net web-forms background, then you can realize that partial views in MVC are similar to user controls in asp.net web forms. 

use of "New" keyword

Using a new keyword for re-implementing the methods in the child class is optional and if used will give information to hiding.

Generics example

namespace Generics

{

    public class MainClass

    {

        private static void Main()

        {

            //bool IsEqual = ClsCalculator.AreEqual<int>(10, 20);

            //bool IsEqual = ClsCalculator.AreEqual<string>("ABC", "ABC");

            bool IsEqual = ClsCalculator.AreEqual<double>(10.5, 20.5);

            if (IsEqual)

            {

                Console.WriteLine("Both are Equal");

            }

            else

            {

                Console.WriteLine("Both are Not Equal");

            }

            Console.ReadKey();

        }

    }

    public class ClsCalculator

    {

        public static bool AreEqual<T>(T value1, T value2)

        {

            return value1.Equals(value2);

        }

    }

}

What is IEnumerable in C#?

 IEnumerable in C# is an interface that defines one method, GetEnumerator which returns an IEnumerator interface. This allows read-only access to a collection then a collection that implements IEnumerable can be used with a for-each statement.

Is Vs as C#?

 The difference between is and as operators are as follows: The is operator is used to check if the run-time type of an object is compatible with the given type or not whereas as operator is used to perform conversion between compatible reference types or Nullable types.

What is difference between generic and non generic in C#?

A Generic collection is a class that provides type safety without having to derive from a base collection type and implement type-specific members. The key difference between Generic and Non-generic Collection in C# is that a Generic Collection is strongly typed while a Non-Generic Collection is not strongly typed.

What is generic list in C#?

In c#, List is a generic type of collection so it will allow storing only strongly typed objects i.e. elements of the same data type, and the size of the list will vary dynamically based on our application requirements like adding or removing elements from the list.

Can we store different types in an array in C#?

In c# we use an object[] array to store different types of data in each element location. You can use an object[] (an object array), but it would be more flexible to use List<object>. It satisfies your requirement that any kind of object can be added to it, and like an array, it can be accessed through a numeric index.

List vs array in C#

Arrays are strongly typed which means it can store only specific type of items or elements. Arraylist are not strongly typed. Array cannot accept null. ArrayList can accepts null.

What are circular references in C#?

 A circular reference occurs when two or more interdependent resources cause lock condition. This makes the resource unusable.

To handle the problem of circular references in C#, you should use garbage collection. It detects and collects circular references. The garbage collector begins with local and static and it marks each object that can be reached through their children.

Through this, you can handle the issues with circular references.

Let’s say the following classes are in a circular reference. Here both of them depends on each other 

public class Hyd {

   Wgl Two;

}

public class Wgl {

   Hyd one;

}

to solve this issue using interfaces

public interface ourInterface {

}

public class Hyd{

   ourInterface Two;

}

public class Wgl: myInterface {

   Hyd one;

}

What is IDisposable in C#?

IDisposable is an interface that contains a single method, Dispose of (), for releasing unmanaged resources, like files, streams, database connections, and so on.

Can we have 2 main methods in C#?

In a C# application, we can have multiple Main methods but only one of them with valid Main signature can be treated as Startup or Entry Point of the application.

Why main method is static in C#?

The Main method states what the class does when executed and instantiates other objects and variables. The main method is static since it is available to run when the C# program starts. It is the entry point of the program and runs without even creating an instance of the class.

What is the static keyword in C#?

In C# terms, “static” means “relating to the type itself, rather than an instance of the type”. You access a static member using the type name instead of a reference or a value, e.g. Guid. NewGuid(). In addition to methods and variables, you can also declare a class to be static (since C# 2.0).

Can we declare constructor as private?

Yes, we can declare a constructor as private. 

If we declare a constructor as private we are not able to create an object of a class. We can use this private constructor in the Singleton Design Pattern.

Can abstract class have constructor?

Yes, Abstract Classes can have constructors!

An abstract class can have a constructor though it cannot be instantiated. But the constructor defined in an abstract class can be used for the instantiation of a concrete class of this abstract class.

Can you have a static method in a non static class?

You can define one or more static methods in a non-static class. Static methods can be called without creating an object. You cannot call static methods using an object of the non-static class.

What are the differences between REST and SOAP?

 

1. SOAP stands for Simple Object Access Protocol whereas REST stands for Representational State Transfer.
2. The SOAP is an XML based protocol whereas REST is not a protocol but it is an architectural pattern i.e. resource-based architecture.                         
3. SOAP has specifications for both stateless and state-full implementation whereas REST is completely stateless.
4. SOAP enforces message format as XML whereas REST does not enforce message format as XML or JSON.
5. The SOAP message consists of an envelope which includes SOAP headers and body to store the actual information we want to send whereas REST uses the HTTP build-in headers (with a variety of media-types) to store the information and uses the HTTP GET, POST, PUT and DELETE  methods to perform CRUD operations.
6. SOAP uses interfaces and named operations to expose the service whereas to expose resources (service) REST uses URI and methods like (GET, PUT, POST, DELETE).
7. 
   
8. SOAP Performance is slow as compared to REST.

.net core and .net framework difference

Developers use the. NET framework to create Windows desktop applications and server-based applications. This includes ASP.NET web applications. NET Core is used to create server applications that run on Windows, Linux, and Mac.

SingleorDefault vs Firstordefault example

Single

It returns a single specific element from a collection of elements if the element match found. An exception is thrown, if none or more than one match found for that element in the collection.

SingleOrDefault

It returns a single specific element from a collection of elements if the element match found. An exception is thrown if more than one match found for that element in the collection. The default value is returned, if no match is found for that element in the collection.

First

It returns the first specific element from a collection of elements if one or more than one match found for that element. An exception is thrown, if no match is found for that element in the collection.

FirstOrDefault

It returns the first specific element from a collection of elements if one or more than one match found for that element. The default value is returned, if no match is found for that element in the collection.

When to use Interface?

 If your child classes should implement a certain group of methods/functionalities but each of the child classes is free to provide its own implementation then use interfaces.

When to use Abstract Classes in C#?

 When we have a requirement where our base class should provide the default implementation of certain methods whereas other methods should be open to being overridden by child classes use abstract classes.

How can you detect the client's browser name?

 You can use the navigator. appName and navigator. userAgent properties.

C# Destructor Syntax

 class User

    {

        // Destructor

        ~User()

        {

            // your code

        }

    }

visual studio component model cache clear

 %localappdata%\Microsoft\VisualStudio\ -- Goto 12.0 or particular visual studio 14.0 delete component cache model inside folders. 

What is the difference between struct and class in c#

 Difference between Structs and Classes 

Structs are value type whereas Classes are reference type. 

Structs are stored on the stack whereas Classes are stored on the heap. 

When you copy struct into another struct, a new copy of that struct gets created modified of one struct won't affect the value of the other struct.

Class can create a subclass that will inherit parent's properties and methods, whereas Structure does not support the inheritance. 

A class has all members private by default. 

A struct is a class where members are public by default. 

 Boxing and unboxing operations are used to convert between a struct type and object.

What are the return types in MVC?

Various Return Types From MVC Controller

System.Web.Mvc.ContentResult.

System.Web.Mvc.EmptyResult.

System.Web.Mvc.FileResult.

System.Web.Mvc.HttpStatusCodeResult.

System.Web.Mvc.JavaScriptResult.

System.Web.Mvc.JsonResult.

System.Web.Mvc.RedirectResult.

System.Web.Mvc.RedirectToRouteResult.

Why do we use MVC pattern?

 MVC is an acronym for Model, View, and Controller. It's a product development architecture. With the emerge of MVC approach, it helps you create applications that separate the different aspects of the application (input logic, business logic, and UI logic) while providing a loose coupling between these elements.

Is MVC a framework?

The Model-View-Controller (MVC) is an architectural pattern that separates an application into three main logical components: the model, the view, and the controller. MVC is one of the most frequently used industry-standard web development frameworks to create scalable and extensible projects.

Is MVC is a design pattern?

Model–view–controller (usually known as MVC) is a software design pattern commonly used for developing user interfaces that divide the related program logic into three interconnected elements.

What is design pattern with example?

 Design patterns provide a standard terminology and are specific to particular scenario. For example, a singleton design pattern signifies use of single object so all developers familiar with single design pattern will make use of single object and they can tell each other that program is following a singleton pattern.

What is meant by design pattern?

 In software engineering, a design pattern is a general repeatable solution to a commonly occurring problem in software design. A design pattern isn't a finished design that can be transformed directly into code. It is a description or template for how to solve a problem that can be used in many different situations.

What is difference between Dispose and Finalize in C#?

 The Finalize method is used to perform cleanup operations on unmanaged resources held by the current object before the object is destroyed. The method is protected and therefore is accessible only through this class or through a derived class.

Can abstract class be sealed in C#?

 When a class is declared sealed, it cannot be inherited, abstract classes cannot be declared sealed. To prevent being overridden, use the sealed in C#. When you use sealed modifiers in C# on a method, then the method loses its capabilities of overriding.

Why do we use keyword in C#?

 The using keyword has three major uses: The using statement defines a scope at the end of which an object will be disposed. The using directive creates an alias for a namespace or import types defined in other namespaces. The using static directive imports the members of a single class.

Can abstract class have constructor in C#?

 Yes, an abstract class can have a constructor. In general, a class constructor is used to initialize fields. Along the same lines, an abstract class constructor is used to initialize fields of the abstract class.

What is the use of virtual keyword in C#?

The virtual keyword is used to modify a method, property, indexer, or event declared in the base class and allow it to be overridden in the derived class. The override keyword is used to extend or modify a virtual/abstract method, property, indexer, or event of base class into derived class. 

Why abstraction is used in C#?

Abstraction allows making relevant information visible and encapsulation enables a programmer to implement the desired level of abstraction. Abstraction can be achieved using abstract classes in C#. C# allows you to create abstract classes that are used to provide a partial class implementation of an interface.

how to call web api method in client application c#

HttpClient class provides a base class for sending/receiving the HTTP requests/responses from a URL.

Example.

In this example, I have created a console application.

To call Web API methods from the console Application, the first step is to install the required packages, using the NuGet Package Manager.

Is it necessary to override a virtual method in C#?

C# virtual keyword is used to create virtual methods in C#. A virtual method is a method that can be redefined in derived classes. When a method is declared as a virtual method in a base class and that method has the same definition in a derived class then there is no need to override it in the derived class.a

error occurred while configuring nuget package manager

Goto Package console manager and download or restore 

And enable the NuGet console manager.

and build 

Why is abstraction needed?

Why abstraction is really important. A summary is one of the key elements of good software design. It helps to round out behavior. When developing with a high level of abstraction, you communicate behavior and execute less.

Change or remove windows saved passwords

1. Press Windows + R and type explorer shell:::{1206F5F1-0569-412C-8FEC-3204630DFB70} in the Run dialog box, hit Enter key to open Credential Manager snap-in.

2. Choose the credentials you want to remove and click on down arrow icon next to it.

3. Finally, click Remove.

This site can’t be reached error

Open a Command prompt and run these commands

           ipconfig /release
           ipconfig /all
           ipconfig /flushdns
           ipconfig /renew

How to search particular date records in sql sqerver

select * from tableName where CONVERT(varchar,[Timestamp],3) = '17/03/20' //here timestamp your table column name and data type datetime.

How do I create a SQL table under a different schema

how to create a schema and how to apply schema.
CREATE SCHEMA SchemaName;

or go to expand the database and goto security folder then expand and create the schema.
 it will create under the schema.

- here if we create any database it will create under dbo.
we want to create under our created schema
create a table and add primary key and auto-increment
then right-click on properties and change schema. that's it njoy...

newtonsoft.json 11.0.2' package requires nuget client version '2.12' or above

Solution: 

Update your NuGet Package Manager extension. Go toTools->Extensions and Updates and selectUpdates->Visual Studio Gallery. Update the NuGet Package Manager extension. Then try to install the package after Visual Studio gets restarted.

Verbatim string c#

string path = @"AppData\Example1"; 

C# supports two forms of string literals: regular string literals and verbatim string literals. A verbatim string literal consists of an @ character followed by a double-quote character, zero or more characters, and a closing double-quote character. A simple example is @"asp net MVC".

using itextsharp form fill issue with checkbox in C#

First of all open pdf escape website. upload your pdf to pdf escape and click upload.

After upload selects the particular checkbox and right-clicks and unlocks form field.

Again click the checkbox and right-click you will get the "object properties".

Click "object properties".

Next, You will get pop like "form field properties".

Here you will get "Export Value" - for example, Yes or 1 or 0

In your code use, this "export value" you will get output. 

datatable filter rows or where condition in datatable in c#

DataRow[] dr = dtDepaet.Select("Id= " + deptId); // where clase in datatable. here id is a column name 
string name = dr[0]["Name"].ToString(); 

How to clear previous search history in the immediate window

 type >cls in immediate window and Enter.

based on number get alphabets in c#

public string GetAlphabets(int number)

{

   string strAlpha = "";

   try

   {

     strAlpha = ((char)number).ToString();

   }

   catch (Exception e)

   {

     string message = e.Message;

     throw;

   }

   return strAlpha;

}

An item with the same key has already been added

"exceptionType": "System.ArgumentException",
  "message": "An item with the same key has already been added.",  "exceptionType": "System.ArgumentException",
  "message": "An item with the same key has already been added.",

Solution: In your model or .cs file same keys
Ex: public class Student
{
 public int Sid {set; get;};
 public string Name {get; set;}
 public sting Sid {set; get;} //Same Name - here we will get above exception. if we chnage to another name this issue will resolve.

}

stop confirm resubmission in page refresh time

<script>
if ( window.history.replaceState ) {
  window.history.replaceState( null, null, window.location.href );
}
</script>

Create hidden or private folder in windows

open notepad and paste below code and save .bat extension. password place changes your password. 

open your saved file. Here automatically create one folder like private. 

if you want to hide that folder open you're saved file it will open one command prompt type here "Y".

again if you want to open the private folder open you're saved file it will open one command prompt type here your password.

Quote: cls

@ECHO OFF

title Folder Private

if EXIST "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" goto UNLOCK

if NOT EXIST Private goto MDENTER PASSWORD TO OPEN

:CONFIRM

echo -----------------------------------------------------------

echo ================== Choulla Naresh ==================

echo -----------------------------------------------------------

echo Are you sure you want to lock the folder(Y/N)

echo Press (Y) for Yes and Press (N) for No.

echo -----------------------------------------------------------

set/p "cho=>"

if %cho%==Y goto LOCK

if %cho%==y goto LOCK

if %cho%==n goto END

if %cho%==N goto END

echo Invalid choice.

goto CONFIRM

:LOCK

ren Private "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"

attrib +h +s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"

echo Folder locked

goto End

:UNLOCK

echo -----------------------------------------------------------

echo ================== Choulla Naresh ==================

echo -----------------------------------------------------------

echo Enter password to unlock folder

set/p "pass=>"

if NOT %pass%== YOUR PASSWORD goto FAIL

attrib -h -s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"

ren "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" Private

echo Folder Unlocked successfully

goto End

:FAIL

echo Invalid password

goto end

:MDENTER PASSWORD TO OPEN

md Private

echo Private created successfully

goto End

:End

how to add project to GitHub repository

Open GitHub and create a repository
Next, execute a command in Gitbash
next, take update using GitHub repository URL
paste ur project and commit

Example : URL: https://github.com/C-Naresh/aspdotnet.git

Execute this command in GitBash

echo "# aspdotnet" >> README.md
git init
git add README.md
git commit -m "first commit"
git remote add origin https://github.com/C-Naresh/aspdotnet.git
git push -u origin master

Now take update using this url https://github.com/C-Naresh/aspdotnet.git

Add your existing project inside folder and commit that's it... 

send confirmation email after registration and forgot password in Asp.Net MVC

find out EmailService : IIdentityMessageService

and add these two methods---

public async Task SendAsync(IdentityMessage message)
   {
      await configSendGridasync(message);
   }

   // Use NuGet to install SendGrid (Basic C# client lib)
   private async Task configSendGridasync(IdentityMessage message)
   {
      var myMessage = new SendGridMessage();
      myMessage.AddTo(message.Destination);
      myMessage.From = new System.Net.Mail.MailAddress(
                          "Joe@contoso.com", "Joe S.");
      myMessage.Subject = message.Subject;
      myMessage.Text = message.Body;
      myMessage.Html = message.Body;

      var credentials = new NetworkCredential(
                 ConfigurationManager.AppSettings["mailAccount"],
                 ConfigurationManager.AppSettings["mailPassword"]
                 );

      // Create a Web transport for sending email.
      var transportWeb = new Web(credentials);

      // Send the email.
      if (transportWeb != null)
      {
         await transportWeb.DeliverAsync(myMessage);
      }
      else
      {
         Trace.TraceError("Failed to create Web transport.");
         await Task.FromResult(0);
      }
   }
------------------------------
add keys in web.config

<add key="mailAccount" value="xyz" />
      <add key="mailPassword" value="password" />
-----------------------
find out public async Task<ActionResult> Register(RegisterViewModel model)
Add
await SignInManager.SignInAsync(user, isPersistent:false, rememberBrowser:false);
After
string code = await UserManager.GenerateEmailConfirmationTokenAsync(user.Id);
            var callbackUrl = Url.Action("ConfirmEmail", "Account",
               new { userId = user.Id, code = code }, protocol: Request.Url.Scheme);
            await UserManager.SendEmailAsync(user.Id,
               "Confirm your account", "Please confirm your account by clicking <a href=\""
               + callbackUrl + "\">here</a>");
-------------------------
find out public async Task<ActionResult> Register(RegisterViewModel model)
comment await SignInManager.SignInAsync(user, isPersistent:false, rememberBrowser:false);

and comment return RedirectToAction("Index", "Home");
add
ViewBag.Message = "Check your email and confirm your account, you must be confirmed "
                         + "before you can log in.";

         return View("Info");
         //return RedirectToAction("Index", "Home");
=================
create info page shared folder inside
----------
Add attribute in contact page
[Authorize]
public ActionResult Contact()

--------------
findout public async Task<ActionResult> Login(LoginViewModel model, string returnUrl)
add
if (!ModelState.IsValid)
    {
        return View(model);
    }
after
// Require the user to have a confirmed email before they can log on.
    var user = await UserManager.FindByNameAsync(model.Email);
    if (user != null)
    {
       if (!await UserManager.IsEmailConfirmedAsync(user.Id))
       {
          ViewBag.errorMessage = "You must have a confirmed email to log on.";
          return View("Error");
       }
    }
---------------
Add error.cshtml inside

@{
   if (String.IsNullOrEmpty(ViewBag.errorMessage))
   {
      <h2 class="text-danger">An error occurred while processing your request.</h2>
   }
   else
   {
      <h2 class="text-danger">@ViewBag.errorMessage</h2>
   }
}
------------
find out public async Task<ActionResult> ForgotPassword(ForgotPasswordViewModel model)
Add
if (user == null || !(await UserManager.IsEmailConfirmedAsync(user.Id)))
        {
            // Don't reveal that the user does not exist or is not confirmed
            return View("ForgotPasswordConfirmation");
        }
after
string code = await UserManager.GeneratePasswordResetTokenAsync(user.Id);
        var callbackUrl = Url.Action("ResetPassword", "Account", new { userId = user.Id, code = code }, protocol: Request.Url.Scheme);
        await UserManager.SendEmailAsync(user.Id, "Reset Password", "Please reset your password by clicking <a href=\"" + callbackUrl + "\">here</a>");
        return RedirectToAction("ForgotPasswordConfirmation", "Account");

-------------------
uncomment Login.cshtml
inside
forget password link
-------
Add method
private async Task<string> SendEmailConfirmationTokenAsync(string userID, string subject)
{
   string code = await UserManager.GenerateEmailConfirmationTokenAsync(userID);
   var callbackUrl = Url.Action("ConfirmEmail", "Account",
      new { userId = userID, code = code }, protocol: Request.Url.Scheme);
   await UserManager.SendEmailAsync(userID, subject,
      "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>");

   return callbackUrl;
}