- [C#] Hướng dẫn lưu tất cả hình ảnh từ File Excel vào thư mục window
- [DATABASE] Hướng dẫn import và export hình ảnh image từ Sqlserver
- [DATABASE] Hướng dẫn sử dụng Hàm ASCII trong sqlserver
- [C#] Hướng dẫn fix lỗi Visual Studio 2022 not Support Target Net Framework 4.5.2
- [C#] Giới thiệu thư viện Sunny UI thiết kế giao diện trên Winform
- [DATABASE] Hướng dẫn thêm và cập nhật Extended Property Column trong Table Sqlserver
- [DEVEXPRESS] Hướng dẫn sử dụng Vertical Gridview để hiển thị thông tin sản phẩm
- [C#] Hướng dẫn sử dụng Json Schema để Validate chuỗi string có phải json
- [C#] Hướng dẫn sử dụng công cụ Clean Code trên Visual Studio
- [C#] Hướng dẫn Drag and Drop File vào RichTextBox
- [C#] Hướng dẫn tạo hiệu ứng Karaoke Text Effect Winform
- [C#] Sử dụng thư viện ZedGraph vẽ biểu đồ Line, Bar, Pie trên Winform
- [DATABASE] Hướng dẫn sort sắp xếp địa chỉ IP trên sqlserver sử dụng hàm PARSENAME
- [C#] Theo dõi sử kiện process Start hay Stop trên Winform
- [ASP.NET] Chia sẻ source code chụp hình sử dụng camera trên website
- [C#] Chạy ứng dụng trên Virtual Desktop (màn hình ảo) Winform
- [C#] Mã hóa và giải mã Data Protection API trên winform
- [C#] Hướng dẫn tạo Gradient Background trên Winform
- [DATABASE] Hướng dẫn convert Epoch to DateTime trong sqlserver
- [DATABASE] Hướng dẫn sử dụng STRING_AGG và CONCAT_WS trong sqlserver 2017
[C#] Chia sẽ tool POCO tạo class object từ table Sqlserver
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:
Để 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!