Generate SQL Table, Store Procedure and C# Class Via Uploading Excel

I am explaining how we can create Table, Store procedure , and C# class via uploading excel and it can save developer’s time.

During the preparation of the project database structure, we can generally create a rough Database structure in Excel and finalize its structure. After finalizing the Database structure we can create tables and stored procedures manually in the database, but that takes a lot of time and developers can get frustrated.

Here, I am explaining how we can create a table, Store Procedure, and C# class via uploading Excel, and how it can save time.

Let’s start with practical examples for understanding how can we create a table, Store Procedure, and C# class.

Below is an example of how we generally create Database design in Excel.

ColumnName	DataType	DataValue
Id	bigint IDENTITY(1,1)	not null
Title	bigint	not null
FullName	varchar(200)	null
JobTitle	varchar(50)	null
ContactNo	varchar(15)	not null
AltContactNo	varchar(15)	null
Address	varchar(500)	null
CountryId	int	null
StateId	int	null
City	varchar(100)	null
Zipcode	varchar(10)	null
RoleId	int	null
Email	varchar(200)	not null
Password	varchar(max)	not null
CompanyId	bigint	not null
BranchId	int	null
CreatedBy	int	null
CreatedDate	datatime	not null
UpdateBy	int	null
UpdatedDate	datetime	null

Here, we have 3 columns in Excel like (ColumnName, DataType, DataValue) that contain the database table structure.

Step 1

Create an MVC Application that can read Excel data. Below is code to read data from Excel.

Ajax call for uploading Excel file:

$("#Upload").click(function() {
    var formData = new FormData();
    var totalFiles = document.getElementById("FileUpload1").files.length;
    for (var i = 0; i < totalFiles; i++) {
        var file = document.getElementById("FileUpload1").files[i];
        formData.append('tableName', $("#tableName").val());
        formData.append("FileUpload1", file);
    }
    $.ajax({
        type: "POST",
        url: '/Home/ImportExcel',
        data: formData,
        dataType: 'json',
        contentType: false,
        processData: false,
        success: function(response) {
            alert("Congratulation your table is created with name" + $("#tableName").val() + " please check your database.");
        },
        error: function(xhr, textStatus, error) {
            console.log(xhr.statusText);
            console.log(textStatus);
            console.log(error);
        }
    });
});

C# code for reading Excel File.

[HttpPost] public ActionResult Importexcel(string tableName) {
    if (Request.Files["FileUpload1"].ContentLength > 0) {
        string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
        string connString = "";
        string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);
        if (!Directory.Exists(path1)) {
            Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));
        }
        if (System.IO.File.Exists(path1)) {
            System.IO.File.Delete(path1);
        }
        Request.Files["FileUpload1"].SaveAs(path1);
        if (extension.Trim() == ".xlsx") {
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            DataTable dt = ConvertXSLXtoDataTable(path1, connString);
            ViewBag.Data = dt;
            CreateDBTable(dt, tableName);
        } else {
            ViewBag.Error = "Please Upload File in .xlsx format";
        }
        return Json("Success", JsonRequestBehavior.AllowGet);
    } else return Json("Please upload File", JsonRequestBehavior.AllowGet);
}
private DataTable ConvertXSLXtoDataTable(string strFilePath, string connString) {
    OleDbConnection oledbConn = new OleDbConnection(connString);
    DataTable dt = new DataTable();
    DataTable dtMain = new DataTable();
    DataSet ds = new DataSet();
    try {
        oledbConn.Open();
        using(DataTable Sheets = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) {
            for (int i = 0; i < Sheets.Rows.Count; i++) {
                string worksheets = Sheets.Rows[i]["TABLE_NAME"].ToString();
                OleDbCommand cmd = new OleDbCommand(String.Format("SELECT * FROM [{0}]", worksheets), oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                oleda.Fill(ds);
            }
            dt = ds.Tables[0];
            for (int i = 0; i < 3; i++) {
                dtMain.Columns.Add(dt.Columns[i]);
            }
        }
    } finally {
        oledbConn.Close();
    }
    dt.Columns.RemoveAt(3);
    return dt;
}

Step 2

Now, call database to create the table. I am using dapper to call the database from my MVC Application (you can use any other mechanism like entity frame work or Ado.net). Below is the code for how to call Store Procedure from MVC using dapper.

I’ll explain how dapper works in my next blog.

public Func < DbConnection > ConnectionFactory = () => new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString);
private void CreateDBTable(DataTable dt, string tableName) {
        using(var connection = ConnectionFactory()) {
                dt.Columns[0].ColumnName = "ColumnName";
                dt.Columns[1].ColumnName = "ColumnType";
                dt.Columns[2].ColumnName = "ColumnNull";
                var queryParameters = new DynamicParameters();
                queryParameters.Add("@tableNamebleName); queryParameters.Add("
                    @ExcelTable, DbType.Object);
                var result = connection.Query < dynamic > ("CreateTableSPameters, commandType: CommandType.StoredProcedure);
}
}

Below is Store Procedure that can read C# DataTable via UserDefinedTableType and create a table in the database.

create PROCEDURE [dbo].[CreateTableSP]
@tableName varchar(100) = null,
@ExcelTable ExcelData readonly,
@TableScrpt varchar(5000) out
AS
BEGIN
SET NOCOUNT ON
DECLARE @ColumnName varchar(100)
DECLARE @ColumnType NVARCHAR(MAX)
DECLARE @ColumnNull varchar(100)
IF EXISTS(
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = @tableName
)
begin
exec ('drop table '+@tableName)
end
declare @SqLQ varchar(max)='CREATE TABLE [dbo].'+@tableName+'('
DECLARE EMP_CURSOR CURSOR
LOCAL FORWARD_ONLY FOR
SELECT * FROM @ExcelTable
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR INTO @ColumnName ,@ColumnType,@ColumnNull
WHILE @@FETCH_STATUS = 0
BEGIN
set @SqLQ = @SqLQ+' '+	@ColumnName+' '+@ColumnType+' '+ @ColumnNull+', '
FETCH NEXT FROM EMP_CURSOR INTO @ColumnName ,@ColumnType,@ColumnNull
END
CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR
set @SqLQ = @SqLQ + '
CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
exec (@SqLQ)
select @SqLQ
set @TableScrpt = @SqLQ
END

Step 3

Now, call the database to create Store Procedure for inserting updates via passing table name in which you need to perform insert and update and pass the Store Procedure name. Here I read a table from the schema and create a Store Procedure to insert and update a table.

Create PROCEDURE[dbo]. [CREATESP](@TableName SYSNAME, @insertSp VARCHAR(500)) AS BEGIN DECLARE @ColumnName varchar(100) DECLARE @ColumnType NVARCHAR(MAX), @ColLength nvarchar(MAX) DECLARE @ColumnNull varchar(100) DECLARE @SqlInsertQ VARCHAR(MAX) DECLARE @SqlUpdateQ VARCHAR(MAX) = 'Update ' + @TableName + ' set '
DECLARE @ColListPara VARCHAR(MAX) = ''
DECLARE @ColList VARCHAR(MAX) = ''
SET @SqlInsertQ = 'Create PROCEDURE ' + @insertSp + '('
DECLARE tbl_CURSOR CURSOR LOCAL FORWARD_ONLY FOR SELECT col.Name AS col, typ.Name AS TYPE, col.max_length AS ColumnLength 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) OPEN tbl_CURSOR FETCH NEXT FROM tbl_CURSOR INTO @ColumnName, @ColumnType, @ColLength WHILE @ @FETCH_STATUS = 0 BEGIN SET @ColList = @ColList + ' ' + @ColumnName + ', '
SET @ColListPara = @ColListPara + ' @' + @ColumnName + ', '
if (@ColumnType = 'varchar'
    OR @ColumnType = 'nvarchar'
    OR @ColumnType = 'char'
    OR @ColumnType = 'nchar') BEGIN SET @ColLength = CASE when(@ColLength = -1) THEN '(Max)'
ELSE ' (' + @ColLength + ' )'
END END ELSE BEGIN SET @ColLength = ''
END SET @SqlInsertQ = @SqlInsertQ + ' @' + @ColumnName + ' ' + @ColumnType + ' ' + @ColLength + ' = null, '
if (@ColumnName < > 'Id') BEGIN SET @SqlUpdateQ = @SqlUpdateQ + @ColumnName + ' =' + ' @' + @ColumnName + ', '
END FETCH NEXT FROM tbl_CURSOR INTO @ColumnName, @ColumnType, @ColLength END CLOSE tbl_CURSOR DEALLOCATE tbl_CURSOR SET @ColList = (SELECT reverse(stuff(reverse(@ColList), 1, 2, '')) AS Ids) SET @ColListPara = (SELECT reverse(stuff(reverse(@ColListPara), 1, 2, '')) AS Ids) SET @SqlInsertQ = (SELECT reverse(stuff(reverse(@SqlInsertQ), 1, 2, '')) AS Ids) SET @SqlUpdateQ = (SELECT reverse(stuff(reverse(@SqlUpdateQ), 1, 2, '')) AS Ids) SET @SqlUpdateQ = @SqlUpdateQ + ' where Id = @Id'
SET @SqlInsertQ = @SqlInsertQ + ')'
SET @SqlInsertQ = @SqlInsertQ + 'AS BEGIN if(@Id =0) begin INSERT INTO [dbo].[' + @TableName + '] (' + @ColList + ') VALUES ( ' + @ColListPara + ' ) END else begin ' + @SqlUpdateQ + ' End end'
EXEC(@SqlInsertQ) print(@SqlInsertQ) END

Step 4

Now, call the database to create Store Procedure to generate C# Class via passing Table Name and  Class Name. Below Store Procedure,  read the table schema and return C# Class Model file accordingly. 

Create PROCEDURE[dbo]. [CREATEMODEL](@TableName SYSNAME, @CLASSNAME VARCHAR(500), @TableScrpt varchar(8000) out) AS BEGIN DECLARE @Result VARCHAR(MAX) SET @Result = 'public class ' + @CLASSNAME + ' {'
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 'float'
    WHEN 'image'
    THEN 'byte[]'
    WHEN 'int'
    THEN 'int'
    WHEN 'money'
    THEN 'decimal'
    WHEN 'nchar'
    THEN 'char'
    WHEN 'ntext'
    THEN 'string'
    WHEN 'numeric'
    THEN 'decimal'
    WHEN 'nvarchar'
    THEN 'string'
    WHEN 'real'
    THEN 'double'
    WHEN 'smalldatetime'
    THEN 'DateTime'
    WHEN 'smallint'
    THEN 'short'
    WHEN 'smallmoney'
    THEN 'decimal'
    WHEN 'text'
    THEN 'string'
    WHEN 'time'
    THEN 'TimeSpan'
    WHEN 'timestamp'
    THEN 'DateTime'
    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 + ' }'
set @TableScrpt = @Result END

Let me know if this is useful or not and please give me your suggestions as well.