怎么做赌钱网站代理,杭州seo排名优化外包,wordpress搜索词结果按文章标题,it培训课程目标#xff1a; 用Excel写数据库的表的定义书#xff0c;用该工具生成SQL#xff0c;在客户端执行#xff0c;把表结构导入数据库#xff0c;生成真正的表 Github代码下载 目录 0.完成下面开发环境的准备1 操作系统Win11 专业版 21H22 oracle 19.33 Visual Studio Commun… 目标 用Excel写数据库的表的定义书用该工具生成SQL在客户端执行把表结构导入数据库生成真正的表 Github代码下载 目录 0.完成下面开发环境的准备1 操作系统Win11 专业版 21H22 oracle 19.33 Visual Studio Community 20224 数据库表定义书参照样式 1 C#工程准备1.1 新建工程1.2 Form1添加控件 2 添加代码2.1 button1_Click事件2.2 button2_Click事件2.3 button3_Click事件2.4 添加ColumnObject类2.5 添加共通方法目的是实现“读入Excel到DataSet中” 3 画面启动测试结果3.1 生成如下的SQL文件3.2 SQL文件T_PLAYER_PICTURES.SQL内容如下3.3 最终生成的表结构 0.完成下面开发环境的准备
1 操作系统Win11 专业版 21H2
2 oracle 19.3
下载和安装手顺https://blog.csdn.net/u011159350/article/details/125432921
3 Visual Studio Community 2022
下载地址https://visualstudio.microsoft.com/zh-hans/vs/community/ 下载后双击选择下面两项并安装
4 数据库表定义书参照样式 1 C#工程准备
1.1 新建工程 创建完工程后如果显示0项目Form1.cs的设计窗口不能显示只显示代码可能是Donet的环境变量出问题了可以尝试下面的方案解决 VS2022中解决方案显示0项目 | 找不到指定的SDK“Microsoft.NET.Sdk”
1.2 Form1添加控件
添加Label控件 label1.Text Oracle数据库定义书文件路径 label2.Text SQL文件生成的文件夹路径 追加textBox1 追加textBox2 button1.Text 选择 button2.Text 选择 button3.Text 运行生成SQL 调整一下Form1窗口大小 Form1的FormBorderStyle FormBorderStyle.FixedToolWindow Form1的StartPosition FormStartPosition.CenterScreen;
2 添加代码
2.1 button1_Click事件
双击button1添加Click事件的内容 private void button1_Click(object sender, EventArgs e){OpenFileDialog openFileDialog new OpenFileDialog();openFileDialog.Title 请选择文件;openFileDialog.Filter Excel文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*;openFileDialog.FilterIndex 1;openFileDialog.Multiselect false;if (openFileDialog.ShowDialog() DialogResult.OK){textBox1.Text openFileDialog.FileName;// 假设你的TextBox控件名为textBox1textBox1.SelectionStart textBox1.Text.Length;textBox1.SelectionLength 0; // 设置选择长度为0表示没有文字被选中textBox1.Focus(); // 设置焦点到textBox1}}2.2 button2_Click事件
双击button2添加Click事件的内容 private void button2_Click(object sender, EventArgs e){FolderBrowserDialog folderBrowserDialog new FolderBrowserDialog();folderBrowserDialog.Description 请选择文件夹;if (folderBrowserDialog.ShowDialog() DialogResult.OK){textBox2.Text folderBrowserDialog.SelectedPath;// 假设你的TextBox控件名为textBox1textBox2.SelectionStart textBox2.Text.Length;textBox2.SelectionLength 0; // 设置选择长度为0表示没有文字被选中textBox2.Focus(); // 设置焦点到textBox1}}2.3 button3_Click事件
双击button3添加Click事件的内容 private void button3_Click(object sender, EventArgs e){try{string talbedesignbookpath textBox1.Text;string sqlpath textBox2.Text;DirectoryInfo directoryInfo new DirectoryInfo(sqlpath);if (!directoryInfo.Exists){directoryInfo.Create();}DataSet ds this.ReadExcelToDataSet(talbedesignbookpath);foreach (DataTable dt in ds.Tables){if (dt.TableName.Contains($_xlnm#Print_Area)){string tableid dt.Rows[1][2].ToString();string tablename dt.Rows[2][2].ToString();FileInfo file new FileInfo(sqlpath \\ tableid .SQL);using (StreamWriter sw file.CreateText()){sw.WriteLine(/*);sw.WriteLine(-- 作成表的中文名 tablename);sw.WriteLine(-- 作成表的英文名 tableid);sw.WriteLine(-- 作成组织无);sw.WriteLine(-- 作成日期 DateTime.Now.ToString(yyyy/MM/dd));sw.WriteLine(-- 作成者烟图黛螺);sw.WriteLine(-- 修改历史 DateTime.Now.ToString(yyyy/MM/dd) 烟图黛螺 新建文件);sw.WriteLine(*/);sw.WriteLine();sw.WriteLine(------- 永久删除表不进入回收站 ----------);sw.WriteLine(DROP TABLE tableid PURGE;);sw.WriteLine(/);sw.WriteLine();sw.WriteLine(------- 创建表 ----------);sw.WriteLine(CREATE TABLE tableid ();int maxcolumnidlen 0;ListColumnObject ColumnObjectList new ListColumnObject();for (int i 0; i dt.Rows.Count; i){if (dt.Rows[i][3] null || dt.Rows[i][3].ToString().Trim().Length 0){continue;}if (i 4){ColumnObject columnObject new ColumnObject();// 列汉字名if (dt.Rows[i][2] ! null dt.Rows[i][2].ToString().Trim().Length 0){string columnname dt.Rows[i][2].ToString();columnObject.Columnname columnname;}// 列英文名if (dt.Rows[i][3] ! null dt.Rows[i][3].ToString().Trim().Length 0){string columnid dt.Rows[i][3].ToString();columnObject.Columnid columnid;if (columnid.Length maxcolumnidlen){maxcolumnidlen columnid.Length;}}// 主键if (dt.Rows[i][4] ! null dt.Rows[i][4].ToString().Trim().Length 0){string columnkey dt.Rows[i][4].ToString();columnObject.Columnkey columnkey;}// 是否非空if (dt.Rows[i][5] ! null dt.Rows[i][5].ToString().Trim().Length 0){string columnnullable dt.Rows[i][5].ToString();columnObject.Columnnullable columnnullable;}// 类型if (dt.Rows[i][6] ! null dt.Rows[i][6].ToString().Trim().Length 0){string columntype dt.Rows[i][6].ToString();columnObject.Columntype columntype;}// 整数位if (dt.Rows[i][7] ! null dt.Rows[i][7].ToString().Trim().Length 0){string columninteger dt.Rows[i][7].ToString();columnObject.Columninteger columninteger;}// 小数位if (dt.Rows[i][8] ! null dt.Rows[i][8].ToString().Trim().Length 0){string columnxiaoshu dt.Rows[i][8].ToString();columnObject.Columnxiaoshu columnxiaoshu;}// 默认值if (dt.Rows[i][9] ! null dt.Rows[i][9].ToString().Trim().Length 0){string columndefault dt.Rows[i][9].ToString();columnObject.Columndefault columndefault;}// 约束条件if (dt.Rows[i][10] ! null dt.Rows[i][10].ToString().Trim().Length 0){string columncheck dt.Rows[i][10].ToString();columnObject.Columncheck columncheck;}ColumnObjectList.Add(columnObject);}}// 创建表int index 0;string line string.Empty;foreach (ColumnObject co in ColumnObjectList){line string.Empty;if (co.Columnid null || co.Columnid.Length 0){continue;}if (index 0){line line co.Columnid.PadRight(maxcolumnidlen 5);}else{line line , co.Columnid.PadRight(maxcolumnidlen 5);}if (co.Columntype VARCHAR2){line line VARCHAR2( co.Columninteger CHAR);}else if (co.Columntype NUMBER){if (co.Columnxiaoshu null || co.Columnxiaoshu.Length 0){line line NUMBER( co.Columninteger ,0);}else{line line NUMBER( co.Columninteger , co.Columnxiaoshu );}}else if (co.Columntype DATE){line line DATE;}if (co.Columndefault ! null co.Columndefault.Length 0){line line DEFAULT co.Columndefault;}if (co.Columnnullable ! null co.Columnnullable.Length 0){line line NOT NULL;}sw.WriteLine(line);index index 1;}if (index 1){sw.WriteLine(););sw.WriteLine(/);sw.WriteLine();}// 添加触发器sw.WriteLine(------- 添加触发器 ----------);sw.WriteLine(CREATE OR REPLACE TRIGGER TRG_ tableid BEFORE INSERT OR UPDATE ON tableid FOR EACH ROW);sw.WriteLine(DECLARE);sw.WriteLine( LV_USER VARCHAR2(64 CHAR););sw.WriteLine( LV_CLIENTID VARCHAR2(64 CHAR););sw.WriteLine( LV_PROGRAMNAME VARCHAR2(50 CHAR););sw.WriteLine( LV_TERMINALINAL VARCHAR2(50 CHAR););sw.WriteLine(BEGIN);sw.WriteLine( LV_USER : SYS_CONTEXT(USERENV, SESSION_USER););sw.WriteLine( LV_CLIENTID: SYS_CONTEXT(USERENV, CLIENT_INFO););sw.WriteLine( LV_PROGRAMNAME: SYS_CONTEXT(USERENV, CLIENT_PROGRAM_NAME););sw.WriteLine( LV_TERMINALINAL: SYS_CONTEXT(USERENV, TERMINAL););sw.WriteLine();sw.WriteLine( IF NOT LV_CLIENTID IS NULL);sw.WriteLine( AND LENGTH(LV_CLIENTID) 0);sw.WriteLine( THEN);sw.WriteLine( IF INSTR(LV_CLIENTID, ,) 0);sw.WriteLine( THEN);sw.WriteLine( LV_USER : SUBSTR(LV_CLIENTID, 1, INSTR(LV_CLIENTID, ,) - 1););sw.WriteLine( LV_PROGRAMNAME: SUBSTR(LV_CLIENTID, INSTR(LV_CLIENTID, ,) 1););sw.WriteLine();sw.WriteLine( IF INSTR(LV_PROGRAMNAME, ,) 0);sw.WriteLine( THEN);sw.WriteLine( LV_TERMINALINAL : SUBSTR(LV_PROGRAMNAME, INSTR(LV_PROGRAMNAME, ,) 1););sw.WriteLine( LV_PROGRAMNAME: SUBSTR(LV_PROGRAMNAME, 1, INSTR(LV_PROGRAMNAME, ,) - 1););sw.WriteLine( END IF;);sw.WriteLine( ELSE);sw.WriteLine( LV_USER : LV_CLIENTID;);sw.WriteLine( END IF;);sw.WriteLine( ELSE);sw.WriteLine( LV_USER : LV_USER || , || LV_PROGRAMNAME;);sw.WriteLine( END IF;);sw.WriteLine();sw.WriteLine( IF INSERTING);sw.WriteLine( THEN);sw.WriteLine( : new.INSTID : LV_USER;);sw.WriteLine( :new.INSTDT : sysdate;);sw.WriteLine( :new.INSTTERM : LV_TERMINALINAL;);sw.WriteLine( :new.INSTPRGNM : LV_PROGRAMNAME;);sw.WriteLine( END IF;);sw.WriteLine();sw.WriteLine( IF INSERTING OR UPDATING);sw.WriteLine( THEN);sw.WriteLine( : new.UPDTID : LV_USER;);sw.WriteLine( :new.UPDTDT : sysdate;);sw.WriteLine( :new.UPDTTERM : LV_TERMINALINAL;);sw.WriteLine( :new.UPDTPRGNM : LV_PROGRAMNAME;);sw.WriteLine( END IF;);sw.WriteLine(END;);sw.WriteLine(/);sw.WriteLine();// 添加主键索引sw.WriteLine(-------添加主键索引----------);index 0;line ALTER TABLE tableid ADD CONSTRAINT PK_ tableid PRIMARY KEY (;foreach (ColumnObject co in ColumnObjectList){if (co.Columnkey ! null co.Columnkey.Length 0){if (index 0){line line co.Columnid;}else{line line , co.Columnid;}index index 1;}}line line );;if (index 1){sw.WriteLine(line);sw.WriteLine(/);sw.WriteLine();}// 添加约束条件sw.WriteLine(------- 添加约束条件 ----------);foreach (ColumnObject co in ColumnObjectList){if (co.Columncheck ! null co.Columncheck.Length 0){line ALTER TABLE tableid ADD CONSTRAINT CHECK_ tableid _ co.Columnid _1 CHECK( co.Columncheck );;sw.WriteLine(line);sw.WriteLine(/);}}sw.WriteLine();// 添加表和列名的注释sw.WriteLine(------- 添加表和列名的注释 ----------);line COMMENT ON TABLE tableid IS tablename ;;sw.WriteLine(line);sw.WriteLine(/);foreach (ColumnObject co in ColumnObjectList){line COMMENT ON COLUMN tableid . co.Columnid IS co.Columnname ;;sw.WriteLine(line);sw.WriteLine(/);}}}}MessageBox.Show(sqlpath Environment.NewLine SQL文件生成完了);}catch (Exception ex){MessageBox.Show(ex.ToString());}}2.4 添加ColumnObject类
在namespace WindowsFormsApp2下添加如下的代码 public class ColumnObject{// 列汉字名private string columnname;// 列英文名private string columnid;// 主键private string columnkey;// 是否非空private string columnnullable;// 类型private string columntype;// 整数位private string columninteger;// 小数位private string columnxiaoshu;// 默认值private string columndefault;// 约束条件private string columncheck;public string Columnname { get columnname; set columnname value; }public string Columnid { get columnid; set columnid value; }public string Columnkey { get columnkey; set columnkey value; }public string Columnnullable { get columnnullable; set columnnullable value; }public string Columntype { get columntype; set columntype value; }public string Columninteger { get columninteger; set columninteger value; }public string Columnxiaoshu { get columnxiaoshu; set columnxiaoshu value; }public string Columndefault { get columndefault; set columndefault value; }public string Columncheck { get columncheck; set columncheck value; }}2.5 添加共通方法目的是实现“读入Excel到DataSet中”
在Form1下边添加下面的方法和button1_Click同一层 private DataSet ReadExcelToDataSet(string path){//连接字符串/* 备注添加 IMEX1 表示将所有列当做字符串读取实际应该不是这样系统默认会查看前8行如果有字符串则该列会识别为字符串列。如果前8行都是数字则还是会识别为数字列日期也一样如果你觉得8行不够或者太多了则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows如果此值为0则会根据所有行来判断使用什么类型通常不建议这麽做除非你的数据量确实比较少*/string connstring ProviderMicrosoft.ACE.OLEDB.12.0;Data Source path ;Extended PropertiesExcel 8.0;IMEX1;;using (OleDbConnection conn new OleDbConnection(connstring)){conn.Open();DataTable sheetsName conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, Table });//存放所有的sheetDataSet set new DataSet();for (int i 0; i sheetsName.Rows.Count; i){string sheetName sheetsName.Rows[i][2].ToString();string sql string.Format(SELECT * FROM [{0}], sheetName);OleDbDataAdapter ada new OleDbDataAdapter(sql, connstring);ada.Fill(set);set.Tables[i].TableName sheetName;}return set;}}搜索Excel选择“Microsoft.Office.Interop.Excel”并安装 搜索“oledb”选择并安装“System.Data.OleDb” 然后Form.cs的using部分修改为如下内容
using Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.ApplicationServices;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Diagnostics.Metrics;
using System.Drawing;
using DataTable System.Data.DataTable;3 画面启动测试结果 3.1 生成如下的SQL文件 3.2 SQL文件T_PLAYER_PICTURES.SQL内容如下
/*
-- 作成表的中文名选手定妆照表
-- 作成表的英文名T_PLAYLER_PICTURES
-- 作成组织无
-- 作成日期2024/02/15
-- 作成者烟图黛螺
-- 修改历史2024/02/15 烟图黛螺 新建文件
*/------- 永久删除表不进入回收站 ----------
DROP TABLE T_PLAYLER_PICTURES PURGE;
/------- 创建表 ----------
CREATE TABLE T_PLAYLER_PICTURES (INSTID VARCHAR2(64 CHAR) NOT NULL,INSTDT DATE NOT NULL,INSTTERM VARCHAR2(50 CHAR) NOT NULL,INSTPRGNM VARCHAR2(50 CHAR) NOT NULL,UPDTID VARCHAR2(64 CHAR) NOT NULL,UPDTDT DATE NOT NULL,UPDTTERM VARCHAR2(50 CHAR) NOT NULL,UPDTPRGNM VARCHAR2(50 CHAR) NOT NULL,PICTURENO VARCHAR2(20 CHAR) NOT NULL,NUM NUMBER(3,0) DEFAULT 1 NOT NULL,MEMBER_ID VARCHAR2(5 CHAR) NOT NULL,PICURL VARCHAR2(100 CHAR) NOT NULL,USEKBN VARCHAR2(1 CHAR) DEFAULT 1 NOT NULL
);
/------- 添加触发器 ----------
CREATE OR REPLACE TRIGGER TRG_T_PLAYLER_PICTURES BEFORE INSERT OR UPDATE ON T_PLAYLER_PICTURES FOR EACH ROW
DECLARELV_USER VARCHAR2(64 CHAR);LV_CLIENTID VARCHAR2(64 CHAR);LV_PROGRAMNAME VARCHAR2(50 CHAR);LV_TERMINALINAL VARCHAR2(50 CHAR);
BEGINLV_USER : SYS_CONTEXT(USERENV, SESSION_USER);LV_CLIENTID: SYS_CONTEXT(USERENV, CLIENT_INFO);LV_PROGRAMNAME: SYS_CONTEXT(USERENV, CLIENT_PROGRAM_NAME);LV_TERMINALINAL: SYS_CONTEXT(USERENV, TERMINAL);IF NOT LV_CLIENTID IS NULLAND LENGTH(LV_CLIENTID) 0THENIF INSTR(LV_CLIENTID, ,) 0THENLV_USER : SUBSTR(LV_CLIENTID, 1, INSTR(LV_CLIENTID, ,) - 1);LV_PROGRAMNAME: SUBSTR(LV_CLIENTID, INSTR(LV_CLIENTID, ,) 1);IF INSTR(LV_PROGRAMNAME, ,) 0THENLV_TERMINALINAL : SUBSTR(LV_PROGRAMNAME, INSTR(LV_PROGRAMNAME, ,) 1);LV_PROGRAMNAME: SUBSTR(LV_PROGRAMNAME, 1, INSTR(LV_PROGRAMNAME, ,) - 1);END IF;ELSELV_USER : LV_CLIENTID;END IF;ELSELV_USER : LV_USER || , || LV_PROGRAMNAME;END IF;IF INSERTINGTHEN: new.INSTID : LV_USER;:new.INSTDT : sysdate;:new.INSTTERM : LV_TERMINALINAL;:new.INSTPRGNM : LV_PROGRAMNAME;END IF;IF INSERTING OR UPDATINGTHEN: new.UPDTID : LV_USER;:new.UPDTDT : sysdate;:new.UPDTTERM : LV_TERMINALINAL;:new.UPDTPRGNM : LV_PROGRAMNAME;END IF;
END;
/-------添加主键索引----------
ALTER TABLE T_PLAYLER_PICTURES ADD CONSTRAINT PK_T_PLAYLER_PICTURES PRIMARY KEY (PICTURENO);
/------- 添加约束条件 ----------
ALTER TABLE T_PLAYLER_PICTURES ADD CONSTRAINT CHECK_T_PLAYLER_PICTURES_NUM_1 CHECK(NUM 1);
/
ALTER TABLE T_PLAYLER_PICTURES ADD CONSTRAINT CHECK_T_PLAYLER_PICTURES_USEKBN_1 CHECK(USEKBN IN (0,1));
/------- 添加表和列名的注释 ----------
COMMENT ON TABLE T_PLAYLER_PICTURES IS 选手定妆照表;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.INSTID IS 登陆者名;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.INSTDT IS 登陆时间;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.INSTTERM IS 登陆客户端名;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.INSTPRGNM IS 登陆程序名;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.UPDTID IS 更新者名;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.UPDTDT IS 更新时间;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.UPDTTERM IS 更新客户端名;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.UPDTPRGNM IS 更新程序名;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.PICTURENO IS 定妆照编号;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.NUM IS 连番;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.MEMBER_ID IS 人员ID;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.PICURL IS 选手照图片地址;
/
COMMENT ON COLUMN T_PLAYLER_PICTURES.USEKBN IS 背景作用;
/
3.3 最终生成的表结构
把上面生成的SQL复制到SQL Developer中执行SQL在数据库中创建表 执行成功后得到下面的表 前8列由触发器自动登录和更新数据第9列以后才是用户登录的数据 到这里就完成了。
