NEWS

[C#] Chia sẽ tool POCO tạo class object từ table Sqlserver

[C#] Chia sẽ tool POCO tạo class object từ table Sqlserver
Đăng bởi: Thảo meo - Lượt xem: 1125 15:57:15, 26/11/2020C#   In bài viết

Xin chào các bạn, bài viết hôm nay mình chia sẻ đến các bạn tools tạo class object từ table Sqlserver trong lập trình C#, Winform.

[C#] Create class object from table sqlserver

Trong lập trình ứng dụng, nếu bạn nào đang sử dụng Entity Framework để phát triển cho dự án của mình thì trong Entity đã có sẵn chức năng Migrate data to model object.

Tuy nhiên, nếu các nào đang sử dụng Dapper hoặc muốn tạo một class Object nhanh chóng từ Table Sqlserver, các bạn có thể downlaod tool bên dưới về để sử dụng.

Hoặc các bạn có thể tùy chỉnh lại theo nhu cầu sử dụng của mình.

Trong demo ứng phần mềm, mình có sử dụng công cụ CodeEditor để hiển thị code: Công cụ đó là ScintillaNET, các bạn có thể từ khóa này tìm trên github để tham khảo nhé.

Dưới đây là giao diện demo ứng:

POCOTemplate_demo

Để sử dụng, các bạn nhập các thông tin kết nối database vào, xong chọn table cần tạo class để sử dụng.

Video demo ứng dụng:

Source code C# tạo class object từ sqlserver:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using ScintillaNET;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace POCOTemplate
{
    public partial class Form1 : Form
    {
        public Server srv;
        public string databaseName;
        public Form1()
        {
            InitializeComponent();
        }

        public static string GetNetDataType(string sqlDataTypeName)
        {

            switch (sqlDataTypeName.ToLower())
            {
                case "bigint":
                    return "Int64";
                case "binary":
                    return "Byte[]";
                case "bit":
                    return "bool";
                case "char":
                    return "char";
                case "cursor":
                    return string.Empty;
                case "datetime":
                    return "DateTime";
                case "decimal":
                    return "Decimal";
                case "float":
                    return "Double";
                case "int":
                    return "int";
                case "money":
                    return "Decimal";
                case "nchar":
                    return "string";
                case "numeric":
                    return "Decimal";
                case "nvarchar":
                    return "string";
                case "real":
                    return "single";
                case "smallint":
                    return "Int16";
                case "text":
                    return "string";
                case "tinyint":
                    return "Byte";
                case "varbinary":
                    return "Byte[]";
                case "xml":
                    return "string";
                case "varchar":
                    return "string";
                case "smalldatetime":
                    return "DateTime";
                case "image":
                    return "byte[]";

                default:
                    return string.Empty;
            }



        }
        public static Color IntToColor(int rgb)
        {
            return Color.FromArgb(255, (byte)(rgb >> 16), (byte)(rgb >> 8), (byte)rgb);
        }
        public void InitSettingCodeEditor()
        {
            rtbResult.StyleResetDefault();
            rtbResult.Styles[Style.Default].Font = "Consolas";
            rtbResult.Styles[Style.Default].Size = 10;
            rtbResult.Styles[Style.Default].BackColor = IntToColor(0x212121);
            rtbResult.Styles[Style.Default].ForeColor = IntToColor(0xFFFFFF);
            rtbResult.StyleClearAll();

            // Configure the CPP (C#) lexer styles
            rtbResult.Styles[Style.Cpp.Identifier].ForeColor = IntToColor(0xD0DAE2);
            rtbResult.Styles[Style.Cpp.Comment].ForeColor = IntToColor(0xBD758B);
            rtbResult.Styles[Style.Cpp.CommentLine].ForeColor = IntToColor(0x40BF57);
            rtbResult.Styles[Style.Cpp.CommentDoc].ForeColor = IntToColor(0x2FAE35);
            rtbResult.Styles[Style.Cpp.Number].ForeColor = IntToColor(0xFFFF00);
            rtbResult.Styles[Style.Cpp.String].ForeColor = IntToColor(0xFFFF00);
            rtbResult.Styles[Style.Cpp.Character].ForeColor = IntToColor(0xE95454);
            rtbResult.Styles[Style.Cpp.Preprocessor].ForeColor = IntToColor(0x8AAFEE);
            rtbResult.Styles[Style.Cpp.Operator].ForeColor = IntToColor(0xE0E0E0);
            rtbResult.Styles[Style.Cpp.Regex].ForeColor = IntToColor(0xff00ff);
            rtbResult.Styles[Style.Cpp.CommentLineDoc].ForeColor = IntToColor(0x77A7DB);
            rtbResult.Styles[Style.Cpp.Word].ForeColor = IntToColor(0x48A8EE);
            rtbResult.Styles[Style.Cpp.Word2].ForeColor = IntToColor(0xF98906);
            rtbResult.Styles[Style.Cpp.CommentDocKeyword].ForeColor = IntToColor(0xB3D991);
            rtbResult.Styles[Style.Cpp.CommentDocKeywordError].ForeColor = IntToColor(0xFF0000);
            rtbResult.Styles[Style.Cpp.GlobalClass].ForeColor = IntToColor(0x48A8EE);

            rtbResult.Lexer = Lexer.Cpp;

            rtbResult.SetKeywords(0, "class extends implements import interface new case do while else if for in switch throw get set function var try catch finally while with default break continue delete return each const namespace package include use is as instanceof typeof author copy default deprecated eventType example exampleText exception haxe inheritDoc internal link mtasc mxmlc param private return see serial serialData serialField since throws usage version langversion playerversion productversion dynamic private public partial static intrinsic internal native override protected AS3 final super this arguments null Infinity NaN undefined true false abstract as base bool break by byte case catch char checked class const continue decimal default delegate do double descending explicit event extern else enum false finally fixed float for foreach from goto group if implicit in int interface internal into is lock long new null namespace object operator out override orderby params private protected public readonly ref return switch struct sbyte sealed short sizeof stackalloc static string select this throw true try typeof uint ulong unchecked unsafe ushort using var virtual volatile void while where yield");
            rtbResult.SetKeywords(1, "void Null ArgumentError arguments Array Boolean Class Date DefinitionError Error EvalError Function int Math Namespace Number Object RangeError ReferenceError RegExp SecurityError String SyntaxError TypeError uint XML XMLList Boolean Byte Char DateTime Decimal Double Int16 Int32 Int64 IntPtr SByte Single UInt16 UInt32 UInt64 UIntPtr Void Path File System Windows Forms ScintillaNET");
        }
    

        #region Numbers, Bookmarks, Code Folding

        /// <summary>
        /// the background color of the text area
        /// </summary>
        private const int BACK_COLOR = 0x2A211C;

        /// <summary>
        /// default text color of the text area
        /// </summary>
        private const int FORE_COLOR = 0xB7B7B7;

        /// <summary>
        /// change this to whatever margin you want the line numbers to show in
        /// </summary>
        private const int NUMBER_MARGIN = 1;

        /// <summary>
        /// change this to whatever margin you want the bookmarks/breakpoints to show in
        /// </summary>
        private const int BOOKMARK_MARGIN = 2;
        private const int BOOKMARK_MARKER = 2;

        /// <summary>
        /// change this to whatever margin you want the code folding tree (+/-) to show in
        /// </summary>
        private const int FOLDING_MARGIN = 3;

        /// <summary>
        /// set this true to show circular buttons for code folding (the [+] and [-] buttons on the margin)
        /// </summary>
        private const bool CODEFOLDING_CIRCULAR = true;

        private void InitNumberMargin()
        {

            rtbResult.Styles[Style.LineNumber].BackColor = IntToColor(BACK_COLOR);
            rtbResult.Styles[Style.LineNumber].ForeColor = IntToColor(FORE_COLOR);
            rtbResult.Styles[Style.IndentGuide].ForeColor = IntToColor(FORE_COLOR);
            rtbResult.Styles[Style.IndentGuide].BackColor = IntToColor(BACK_COLOR);

            var nums = rtbResult.Margins[NUMBER_MARGIN];
            nums.Width = 30;
            nums.Type = MarginType.Number;
            nums.Sensitive = true;
            nums.Mask = 0;

            rtbResult.MarginClick += rtbResult_MarginClick;
        }

        private void InitBookmarkMargin()
        {

            //rtbResult.SetFoldMarginColor(true, IntToColor(BACK_COLOR));

            var margin = rtbResult.Margins[BOOKMARK_MARGIN];
            margin.Width = 20;
            margin.Sensitive = true;
            margin.Type = MarginType.Symbol;
            margin.Mask = (1 << BOOKMARK_MARKER);
            //margin.Cursor = MarginCursor.Arrow;

            var marker = rtbResult.Markers[BOOKMARK_MARKER];
            marker.Symbol = MarkerSymbol.Circle;
            marker.SetBackColor(IntToColor(0xFF003B));
            marker.SetForeColor(IntToColor(0x000000));
            marker.SetAlpha(100);

        }

        private void InitCodeFolding()
        {

            rtbResult.SetFoldMarginColor(true, IntToColor(BACK_COLOR));
            rtbResult.SetFoldMarginHighlightColor(true, IntToColor(BACK_COLOR));

            // Enable code folding
            rtbResult.SetProperty("fold", "1");
            rtbResult.SetProperty("fold.compact", "1");

            // Configure a margin to display folding symbols
            rtbResult.Margins[FOLDING_MARGIN].Type = MarginType.Symbol;
            rtbResult.Margins[FOLDING_MARGIN].Mask = Marker.MaskFolders;
            rtbResult.Margins[FOLDING_MARGIN].Sensitive = true;
            rtbResult.Margins[FOLDING_MARGIN].Width = 20;

            // Set colors for all folding markers
            for (int i = 25; i <= 31; i++)
            {
                rtbResult.Markers[i].SetForeColor(IntToColor(BACK_COLOR)); // styles for [+] and [-]
                rtbResult.Markers[i].SetBackColor(IntToColor(FORE_COLOR)); // styles for [+] and [-]
            }

            // Configure folding markers with respective symbols
            rtbResult.Markers[Marker.Folder].Symbol = CODEFOLDING_CIRCULAR ? MarkerSymbol.CirclePlus : MarkerSymbol.BoxPlus;
            rtbResult.Markers[Marker.FolderOpen].Symbol = CODEFOLDING_CIRCULAR ? MarkerSymbol.CircleMinus : MarkerSymbol.BoxMinus;
            rtbResult.Markers[Marker.FolderEnd].Symbol = CODEFOLDING_CIRCULAR ? MarkerSymbol.CirclePlusConnected : MarkerSymbol.BoxPlusConnected;
            rtbResult.Markers[Marker.FolderMidTail].Symbol = MarkerSymbol.TCorner;
            rtbResult.Markers[Marker.FolderOpenMid].Symbol = CODEFOLDING_CIRCULAR ? MarkerSymbol.CircleMinusConnected : MarkerSymbol.BoxMinusConnected;
            rtbResult.Markers[Marker.FolderSub].Symbol = MarkerSymbol.VLine;
            rtbResult.Markers[Marker.FolderTail].Symbol = MarkerSymbol.LCorner;

            // Enable automatic folding
            rtbResult.AutomaticFold = (AutomaticFold.Show | AutomaticFold.Click | AutomaticFold.Change);

        }

        private void rtbResult_MarginClick(object sender, MarginClickEventArgs e)
        {
            if (e.Margin == BOOKMARK_MARGIN)
            {
                // Do we have a marker for this line?
                const uint mask = (1 << BOOKMARK_MARKER);
                var line = rtbResult.Lines[rtbResult.LineFromPosition(e.Position)];
                if ((line.MarkerGet() & mask) > 0)
                {
                    // Remove existing bookmark
                    line.MarkerDelete(BOOKMARK_MARKER);
                }
                else
                {
                    // Add bookmark
                    line.MarkerAdd(BOOKMARK_MARKER);
                }
            }
        }
        private void InitColors()
        {

            rtbResult.SetSelectionBackColor(true, IntToColor(0x114D9C));

        }
        private void InitSyntaxColoring()
        {

            // Configure the default style
            rtbResult.StyleResetDefault();
            rtbResult.Styles[Style.Default].Font = "Consolas";
            rtbResult.Styles[Style.Default].Size = 10;
            rtbResult.Styles[Style.Default].BackColor = IntToColor(0x212121);
            rtbResult.Styles[Style.Default].ForeColor = IntToColor(0xFFFFFF);
            rtbResult.StyleClearAll();

            // Configure the CPP (C#) lexer styles
            rtbResult.Styles[Style.Cpp.Identifier].ForeColor = IntToColor(0xD0DAE2);
            rtbResult.Styles[Style.Cpp.Comment].ForeColor = IntToColor(0xBD758B);
            rtbResult.Styles[Style.Cpp.CommentLine].ForeColor = IntToColor(0x40BF57);
            rtbResult.Styles[Style.Cpp.CommentDoc].ForeColor = IntToColor(0x2FAE35);
            rtbResult.Styles[Style.Cpp.Number].ForeColor = IntToColor(0xFFFF00);
            rtbResult.Styles[Style.Cpp.String].ForeColor = IntToColor(0xFFFF00);
            rtbResult.Styles[Style.Cpp.Character].ForeColor = IntToColor(0xE95454);
            rtbResult.Styles[Style.Cpp.Preprocessor].ForeColor = IntToColor(0x8AAFEE);
            rtbResult.Styles[Style.Cpp.Operator].ForeColor = IntToColor(0xE0E0E0);
            rtbResult.Styles[Style.Cpp.Regex].ForeColor = IntToColor(0xff00ff);
            rtbResult.Styles[Style.Cpp.CommentLineDoc].ForeColor = IntToColor(0x77A7DB);
            rtbResult.Styles[Style.Cpp.Word].ForeColor = IntToColor(0x48A8EE);
            rtbResult.Styles[Style.Cpp.Word2].ForeColor = IntToColor(0xF98906);
            rtbResult.Styles[Style.Cpp.CommentDocKeyword].ForeColor = IntToColor(0xB3D991);
            rtbResult.Styles[Style.Cpp.CommentDocKeywordError].ForeColor = IntToColor(0xFF0000);
            rtbResult.Styles[Style.Cpp.GlobalClass].ForeColor = IntToColor(0x48A8EE);

            rtbResult.Lexer = Lexer.Cpp;

            rtbResult.SetKeywords(0, "class extends implements import interface new case do while else if for in switch throw get set function var try catch finally while with default break continue delete return each const namespace package include use is as instanceof typeof author copy default deprecated eventType example exampleText exception haxe inheritDoc internal link mtasc mxmlc param private return see serial serialData serialField since throws usage version langversion playerversion productversion dynamic private public partial static intrinsic internal native override protected AS3 final super this arguments null Infinity NaN undefined true false abstract as base bool break by byte case catch char checked class const continue decimal default delegate do double descending explicit event extern else enum false finally fixed float for foreach from goto group if implicit in int interface internal into is lock long new null namespace object operator out override orderby params private protected public readonly ref return switch struct sbyte sealed short sizeof stackalloc static string select this throw true try typeof uint ulong unchecked unsafe ushort using var virtual volatile void while where yield");
            rtbResult.SetKeywords(1, "void Null ArgumentError arguments Array Boolean Class Date DefinitionError Error EvalError Function int Math Namespace Number Object RangeError ReferenceError RegExp SecurityError String SyntaxError TypeError uint XML XMLList Boolean Byte Char DateTime Decimal Double Int16 Int32 Int64 IntPtr SByte Single UInt16 UInt32 UInt64 UIntPtr Void Path File System Windows Forms ScintillaNET");

        }
        #endregion
        private void Form1_Load(object sender, EventArgs e)
        {
            rtbResult.WrapMode = WrapMode.None;
            rtbResult.IndentationGuides = IndentView.LookBoth;

            // STYLING
            InitColors();
            InitSyntaxColoring();

            // NUMBER MARGIN
            InitNumberMargin();

            // BOOKMARK MARGIN
            InitBookmarkMargin();

            // CODE FOLDING MARGIN
            InitCodeFolding();
        }

        private void btnGen_Click(object sender, EventArgs e)
        {
            string resultClass = "";
            resultClass += $"namespace { txtNameSpace.Text }" + Environment.NewLine;
            resultClass += "{" + Environment.NewLine;
            resultClass += $"   public class { txtClassName.Text }" + Environment.NewLine;
            resultClass += "   {" + Environment.NewLine;
            foreach (Table table in srv.Databases[databaseName].Tables)
            {
                if (table.Name.Equals(cb_TableDatabase.Text))
                {
                    foreach (Column col in table.Columns)
                    {
                        resultClass += $"        public {GetNetDataType(col.DataType.Name)} {col.Name} {{ get; set; }}" + Environment.NewLine;
                    }
                    break;
                }
              
            }
            resultClass += "   }" + Environment.NewLine;
            resultClass += "}";
            rtbResult.Text = resultClass;
            
            
        }

        private void cb_TableDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtClassName.Text = cb_TableDatabase.Text;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Clipboard.SetText(rtbResult.Text);
        }
       
        private void rtbResult_TextChanged(object sender, EventArgs e)
        {
          
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            databaseName = txt_nameDatabase.Text;
            var svrConnection = new ServerConnection();
            svrConnection.ConnectionString = $@"Server={txt_ip.Text};Database={databaseName};User Id={txtUsername.Text};Password = {txtPassword.Text};";
            svrConnection.Connect();
            srv = new Server(svrConnection);
            var listTables = new List<string>();
            foreach (Table table in srv.Databases[databaseName].Tables)
            {
                listTables.Add(table.Name);
            }
            cb_TableDatabase.DataSource = listTables;
            cb_TableDatabase.SelectedIndex = 0;
        }
    }
}


Thanks for watching!

DOWNLOAD SOURCE

 
Tags: map database to object c#table2object c#

THÔNG TIN TÁC GIẢ

THẢO LUẬN BÀI VIẾT

BÀI VIẾT LIÊN QUAN

[C#] Chia sẽ tool POCO tạo class object từ table Sqlserver
Đăng bởi: Thảo meo - Lượt xem: 1125 15:57:15, 26/11/2020C#   In bài viết

CÁC BÀI CÙNG CHỦ ĐỀ

.