CodeHub

In this article,I will show you CRUD operations using mvc scaffolding with linq to sql.

---Table Script---

create table Employee

(

   id int primary key identity(1,1),

   name varchar(50),

   salary decimal(18,2)

)

Here must add package in project solution :

   System.Data.Linq

---Model Class---

[Table]

public class Employee

{

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]

    public int id { get; set; }

    [Column]

    public string name { get; set; }

    [Column]

    public decimal salary { get; set; }

}

---employee controller--

DataContext dc = new DataContext("Data Source=.;Initial Catalog=TESTDB;Integrated Security=True");

public ActionResult Index()

{

    IEnumerable<Employee> emp_data = dc.GetTable<Employee>();

    return View(emp_data);

}

public ActionResult Create()

{

    return View();

}

[HttpPost]

public ActionResult Create(Employee emp)

{

    dc.GetTable<Employee>().InsertOnSubmit(emp);

    dc.SubmitChanges();

    return RedirectToAction("Index");

}

[HttpGet]

public ActionResult Edit(int id)

{

    Employee emp = dc.GetTable<Employee>().Single(x => x.id == id);

    return View(emp);

}

[HttpPost]

public ActionResult Edit(Employee empobj)

{

    Employee emp = dc.GetTable<Employee>().Single(x => x.id == empobj.id);

    UpdateModel(emp);

    dc.SubmitChanges();

    return RedirectToAction("Index");

}

[HttpGet]

public ActionResult Delete(int id)

{

    Employee emp = dc.GetTable<Employee>().Single(x => x.id == id);

    return View(emp);

}

[HttpPost]

public ActionResult Delete(Employee empobj)

{

    Employee emp = dc.GetTable<Employee>().Single(x => x.id == empobj.id);

    dc.GetTable<Employee>().DeleteOnSubmit(emp);

    dc.SubmitChanges();

    return RedirectToAction("Index");

}

Note: Accoding to action method you can add scaffolding templet on view.

<router-outlet></router-outlet>

                        

         #region QR Code Generation 

        public ActionResult GetQRCodeImage(string qrcode)

        {

            QRCodeGenerator qrGenerator = new QRCodeGenerator();

            QRCodeData qrCodeData = qrGenerator.CreateQrCode(qrcode,

            QRCodeGenerator.ECCLevel.Q);

            QRCode qrCode = new QRCode(qrCodeData);

            Bitmap qrCodeImage = qrCode.GetGraphic(20);

            ImageConverter converter = new ImageConverter();

            byte[] img = (byte[])converter.ConvertTo(qrCodeImage, typeof(byte[]));

            return new FileStreamResult(new System.IO.MemoryStream(img), "image/jpeg");

        }

        #endregion

        public ActionResult GetBarcodeImage(string brcode)

        {

            Image myimg = Code128Rendering.MakeBarcodeImage(brcode, int.Parse("2"), true);

            ImageConverter converter = new ImageConverter();

            byte[] img = (byte[])converter.ConvertTo(myimg, typeof(byte[]));

            return new FileStreamResult(new System.IO.MemoryStream(img), "image/jpeg");

        }

        #endregion

 

script type="text/javascript">

select top 1 * from (select top 2 * from emp order by salary desc) as temp_table order by salary 

CREATE TABLE [dbo].[Employee](

[EmployeeID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

[Position] [varchar](50) NULL,

[Office] [varchar](50) NULL,

[Age] [int] NULL,

[Salary] [int] NULL,

)

Index.html

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'double'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
            then '?'
            else ''
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId
set @Result = @Result  + '
}'
print @Result