How to Create Only One function for all database operations in Asp.net - MVC

Video Tutorial:

Watch here

Download Full Source Code:

Download here

Introduction:

 Here I will explain how to create only one function for all database operation in Asp.net MVC. It reduce your time to for creating separate function for creating individual function for each operations.

Description:

In previous articles I will explain How to bind Country, State, City Dropdown list in mvc using ajax,

How to show database values using Dataset in table with Edit and delete operation using Asp.net MVC,  How to bind dropdown in mvc using ajax, How to login with Facebook in asp.net mvc, How to upload multiple image or files in asp.net mvc with Source Code. ,Retrieve data from database in asp.net web api,How to login with gmail in Asp.net MVC  with relevant example

So now we start.

Step1:  In first step we will create table and stored procedure in database

CREATE TABLE [dbo].[tbl_registration] (

    [Sr_no]    INT            IDENTITY (1, 1) NOT NULL,

    [Email]    NVARCHAR (100) NULL,

    [Password] NVARCHAR (MAX) NULL,

    [Name]     VARCHAR (MAX)  NULL,

    [Address]  NVARCHAR (MAX) NULL,

    [City]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED ([Sr_no] ASC)

);

Create proc Sp_register

 @Email    NVARCHAR (100) ,

    @Password NVARCHAR (MAX) ,

    @Name     VARCHAR (MAX)  ,

    @Address  NVARCHAR (MAX) ,

    @City     NVARCHAR (MAX)

     as

     begin

     insert into tbl_registration values(

      @Email     ,

    @Password  ,

    @Name      ,

    @Address   ,

    @City     

     )

     end

Step2: In this step we create store procedure for retrieve total parameter from given stored procedure

CREATE proc Sp_getProcParam

@Proc_name nvarchar(50)

as

begin

select Parameter_Name from information_schema.parameters

where specific_name=@Proc_name

end

Step3: Create New folder for Database_Access_Layer and add class operation.cs class and write fallowing code

  public class operation

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        public void dml(SqlCommand com,SqlConnection con)

        {

            con.Open();

            com.ExecuteNonQuery();

            con.Close();

        }

        public DataSet Getrecord(SqlCommand com)

        {

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

        // For All DML operations

        public void dmlvalue(string proc,dynamic[] col)

        {

            SqlCommand com = new SqlCommand("Sp_getProcParam", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Proc_name", proc);

            DataSet ds = Getrecord(com);

            int i = 0;

            SqlCommand com1 = new SqlCommand(proc, con);

            com1.CommandType = CommandType.StoredProcedure;

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                com1.Parameters.AddWithValue(dr[0].ToString(), col[i]);

                i++;

            }

            dml(com1, con);

        }

        // For get record from database

        public DataSet Getvalue(string proc, dynamic[] col)

        {

            SqlCommand com = new SqlCommand("Sp_getProcParam", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Proc_name", proc);

            DataSet ds = Getrecord(com);

            int i = 0;

            SqlCommand com1 = new SqlCommand(proc, con);

            com1.CommandType = CommandType.StoredProcedure;

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                com1.Parameters.AddWithValue(dr[0].ToString(), col[i]);

                i++;

            }

            DataSet ds2 = Getrecord(com1);

            return ds2;

        }

    }

Step4: Write down fallowing html under Index.cshtml View

@{

    Layout = null;

}

<!DOCTYPE html>

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

</head>

<body>

    <div>

        <form method="post">

            <table>

                <tr>

                    <td><input placeholder="Email" name="email" /></td>

                </tr>

                <tr>

                    <td><input placeholder="Password" name="Password" /></td>

                </tr>

                <tr>

                    <td><input placeholder="Name" name="Name" /></td>

                </tr>

                <tr>

                    <td><input placeholder="Address" name="Address" /></td>

                </tr>

                <tr>

                    <td><input placeholder="City" name="City" /></td>

                </tr>

                <tr>

                    <td><input type="submit" value="Submit" /></td>

                </tr>

            </table>

        </form>

    </div>

    @{

        if (TempData["msg"] != null)

        {

            <script type="text/javascript">

                alert('@TempData["msg"]');

            </script>

        }

    }

</body>

</html>

Step5: Go to HomeController.cs class and write fallowing code for accessing data and send stored procedure name and parameters

public class HomeController : Controller

    {

        //

        // GET: /Home/

        operation dbop = new operation();

        dynamic[] col;

        public ActionResult Index()

        {

            return View();

        }

        [HttpPost]

        public ActionResult Index(FormCollection fc)

        {

            col = new dynamic[] { fc["email"], fc["Password"], fc["Name"], fc["Address"], fc["City"] };

            dbop.dmlvalue("Sp_register", col);

            TempData["msg"] = "Submitted Succssfully";

            return View();

        }

    }

Step5: Now run project and you inserted your data in database

Thanks…