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.

ColumnNameDataTypeDataValue
Idbigint IDENTITY(1,1)not null
Titlebigintnot null
FullNamevarchar(200)null
JobTitlevarchar(50)null
ContactNovarchar(15)not null
AltContactNovarchar(15)null
Addressvarchar(500)null
CountryIdintnull
StateIdintnull
Cityvarchar(100)null
Zipcodevarchar(10)null
RoleIdintnull
Emailvarchar(200)not null
Passwordvarchar(max)not null
CompanyIdbigintnot null
BranchIdintnull
CreatedByintnull
CreatedDatedatatimenot null
UpdateByintnull
UpdatedDatedatetimenull

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:

  1. $("#Upload").click(function() {
  2. var formData = new FormData();
  3. var totalFiles = document.getElementById("FileUpload1").files.length;
  4. for (var i = 0; i < totalFiles; i++) {
  5. var file = document.getElementById("FileUpload1").files[i];
  6. formData.append('tableName', $("#tableName").val());
  7. formData.append("FileUpload1", file);
  8. }
  9. $.ajax({
  10. type: "POST",
  11. url: '/Home/ImportExcel',
  12. data: formData,
  13. dataType: 'json',
  14. contentType: false,
  15. processData: false,
  16. success: function(response) {
  17. alert("Congratulation your table is created with name" + $("#tableName").val() + " please check your database.");
  18. },
  19. error: function(xhr, textStatus, error) {
  20. console.log(xhr.statusText);
  21. console.log(textStatus);
  22. console.log(error);
  23. }
  24. });
  25. });

C# code for reading Excel File.

  1. [HttpPost] public ActionResult Importexcel(string tableName) {
  2. if (Request.Files["FileUpload1"].ContentLength > 0) {
  3. string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
  4. string connString = "";
  5. string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);
  6. if (!Directory.Exists(path1)) {
  7. Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));
  8. }
  9. if (System.IO.File.Exists(path1)) {
  10. System.IO.File.Delete(path1);
  11. }
  12. Request.Files["FileUpload1"].SaveAs(path1);
  13. if (extension.Trim() == ".xlsx") {
  14. connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
  15. DataTable dt = ConvertXSLXtoDataTable(path1, connString);
  16. ViewBag.Data = dt;
  17. CreateDBTable(dt, tableName);
  18. } else {
  19. ViewBag.Error = "Please Upload File in .xlsx format";
  20. }
  21. return Json("Success", JsonRequestBehavior.AllowGet);
  22. } else return Json("Please upload File", JsonRequestBehavior.AllowGet);
  23. }
  24. private DataTable ConvertXSLXtoDataTable(string strFilePath, string connString) {
  25. OleDbConnection oledbConn = new OleDbConnection(connString);
  26. DataTable dt = new DataTable();
  27. DataTable dtMain = new DataTable();
  28. DataSet ds = new DataSet();
  29. try {
  30. oledbConn.Open();
  31. using(DataTable Sheets = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) {
  32. for (int i = 0; i < Sheets.Rows.Count; i++) {
  33. string worksheets = Sheets.Rows[i]["TABLE_NAME"].ToString();
  34. OleDbCommand cmd = new OleDbCommand(String.Format("SELECT * FROM [{0}]", worksheets), oledbConn);
  35. OleDbDataAdapter oleda = new OleDbDataAdapter();
  36. oleda.SelectCommand = cmd;
  37. oleda.Fill(ds);
  38. }
  39. dt = ds.Tables[0];
  40. for (int i = 0; i < 3; i++) {
  41. dtMain.Columns.Add(dt.Columns[i]);
  42. }
  43. }
  44. } finally {
  45. oledbConn.Close();
  46. }
  47. dt.Columns.RemoveAt(3);
  48. return dt;
  49. }

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.

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

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

  1. create PROCEDURE [dbo].[CreateTableSP]
  2. @tableName varchar(100) = null,
  3. @ExcelTable ExcelData readonly,
  4. @TableScrpt varchar(5000) out
  5. AS
  6. BEGIN
  7. SET NOCOUNT ON
  8. DECLARE @ColumnName varchar(100)
  9. DECLARE @ColumnType NVARCHAR(MAX)
  10. DECLARE @ColumnNull varchar(100)
  11. IF EXISTS(
  12. SELECT
  13. *
  14. FROM
  15. INFORMATION_SCHEMA.TABLES
  16. WHERE
  17. TABLE_NAME = @tableName
  18. )
  19. begin
  20. exec ('drop table '+@tableName)
  21. end
  22. declare @SqLQ varchar(max)='CREATE TABLE [dbo].'+@tableName+'('
  23. DECLARE EMP_CURSOR CURSOR
  24. LOCAL FORWARD_ONLY FOR
  25. SELECT * FROM @ExcelTable
  26. OPEN EMP_CURSOR
  27. FETCH NEXT FROM EMP_CURSOR INTO @ColumnName ,@ColumnType,@ColumnNull
  28. WHILE @@FETCH_STATUS = 0
  29. BEGIN
  30. set @SqLQ = @SqLQ+' '+ @ColumnName+' '+@ColumnType+' '+ @ColumnNull+', '
  31. FETCH NEXT FROM EMP_CURSOR INTO @ColumnName ,@ColumnType,@ColumnNull
  32. END
  33. CLOSE EMP_CURSOR
  34. DEALLOCATE EMP_CURSOR
  35. set @SqLQ = @SqLQ + '
  36. CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED
  37. (
  38. [Id] ASC
  39. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  40. ) ON [PRIMARY]'
  41. exec (@SqLQ)
  42. select @SqLQ
  43. set @TableScrpt = @SqLQ
  44. 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.
  1. 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 '
  2. DECLARE @ColListPara VARCHAR(MAX) = ''
  3. DECLARE @ColList VARCHAR(MAX) = ''
  4. SET @SqlInsertQ = 'Create PROCEDURE ' + @insertSp + '('
  5. 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 + ', '
  6. SET @ColListPara = @ColListPara + ' @' + @ColumnName + ', '
  7. if (@ColumnType = 'varchar'
  8. OR @ColumnType = 'nvarchar'
  9. OR @ColumnType = 'char'
  10. OR @ColumnType = 'nchar') BEGIN SET @ColLength = CASE when(@ColLength = -1) THEN '(Max)'
  11. ELSE ' (' + @ColLength + ' )'
  12. END END ELSE BEGIN SET @ColLength = ''
  13. END SET @SqlInsertQ = @SqlInsertQ + ' @' + @ColumnName + ' ' + @ColumnType + ' ' + @ColLength + ' = null, '
  14. if (@ColumnName < > 'Id') BEGIN SET @SqlUpdateQ = @SqlUpdateQ + @ColumnName + ' =' + ' @' + @ColumnName + ', '
  15. 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'
  16. SET @SqlInsertQ = @SqlInsertQ + ')'
  17. SET @SqlInsertQ = @SqlInsertQ + 'AS BEGIN if(@Id =0) begin INSERT INTO [dbo].[' + @TableName + '] (' + @ColList + ') VALUES ( ' + @ColListPara + ' ) END else begin ' + @SqlUpdateQ + ' End end'
  18. 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.
  1. Create PROCEDURE[dbo]. [CREATEMODEL](@TableName SYSNAME, @CLASSNAME VARCHAR(500), @TableScrpt varchar(8000) out) AS BEGIN DECLARE @Result VARCHAR(MAX) SET @Result = 'public class ' + @CLASSNAME + ' {'
  2. SELECT @Result = @Result + ' public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }'
  3. FROM(SELECT REPLACE(col.NAME, ' ', '_') ColumnName, column_id ColumnId, CASE typ.NAME WHEN 'bigint'
  4. THEN 'long'
  5. WHEN 'binary'
  6. THEN 'byte[]'
  7. WHEN 'bit'
  8. THEN 'bool'
  9. WHEN 'char'
  10. THEN 'string'
  11. WHEN 'date'
  12. THEN 'DateTime'
  13. WHEN 'datetime'
  14. THEN 'DateTime'
  15. WHEN 'datetime2'
  16. then 'DateTime'
  17. WHEN 'datetimeoffset'
  18. THEN 'DateTimeOffset'
  19. WHEN 'decimal'
  20. THEN 'decimal'
  21. WHEN 'float'
  22. THEN 'float'
  23. WHEN 'image'
  24. THEN 'byte[]'
  25. WHEN 'int'
  26. THEN 'int'
  27. WHEN 'money'
  28. THEN 'decimal'
  29. WHEN 'nchar'
  30. THEN 'char'
  31. WHEN 'ntext'
  32. THEN 'string'
  33. WHEN 'numeric'
  34. THEN 'decimal'
  35. WHEN 'nvarchar'
  36. THEN 'string'
  37. WHEN 'real'
  38. THEN 'double'
  39. WHEN 'smalldatetime'
  40. THEN 'DateTime'
  41. WHEN 'smallint'
  42. THEN 'short'
  43. WHEN 'smallmoney'
  44. THEN 'decimal'
  45. WHEN 'text'
  46. THEN 'string'
  47. WHEN 'time'
  48. THEN 'TimeSpan'
  49. WHEN 'timestamp'
  50. THEN 'DateTime'
  51. WHEN 'tinyint'
  52. THEN 'byte'
  53. WHEN 'uniqueidentifier'
  54. THEN 'Guid'
  55. WHEN 'varbinary'
  56. THEN 'byte[]'
  57. WHEN 'varchar'
  58. THEN 'string'
  59. 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 '?'
  60. ELSE ''
  61. 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 + ' }'
  62. set @TableScrpt = @Result END

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