不再是学生了，成了社畜了，公司主要技术栈是C#
大一时候学C#学的很迷糊，总要重新学一下
入职已经20天了，也开始上手简单增删改查了
记录了一些C#相关的东西，只是还没有系统整理
控件命名规范
string constr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.20.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=abac)));User ID=abac;Password=abac";
OracleConnection con = new OracleConnection(constr);//连接到数据库
this.con.Open();
ConnectionState 枚举
Dispose 和 Close
Close用于关闭一个连接，而Dispose方法不仅关闭一个连接，而且还清理连接所占用的资源。当调用Close方法关闭连接后，还可以再调用Open方法打开连接，而如果使用Dispose方法关闭连接，就不能再调用Open方法打开连接，必须重新初始化连接再打开。
OracleCommand command = new OracleCommand("insert into " +
"ABXRTEST02(SYSID,STRING1,NUMBER1,DT1) values ('" +
sysid_textbox.Text + "','" + string1_textbox.Text +
"','" + number1_textbox.Text + "',to_date('" +
dt1_date.Text + "','yyyy-mm-dd'))", this.con);
command.ExecuteNonQuery();
查询数据并填充至DataTable
string strQuery = @"select SYSID,STRING1,NUMBER1,DT1 from ABXRTEST02 where SYSID='" + SYSID +
"' and STRING1='" + STRING1 + "' and NUMBER1='" + NUMBER1 + "'";
OracleDataAdapter adapter = new OracleDataAdapter(strQuery, this.con);
DataTable dtResult = new DataTable();
adapter.Fill(dtResult);
adapter.Dispose();
相当于存放于内存的小型数据库，可以包含数据表、数据列、数据行、视图、约束关系等信息
Merge(DataSet ds, bool Pc, MissingSchemaAction msa)方法
ds：被合并的数据和架构
pc：要保留当前DataSet中的更改则为true，否则为false
msa：MissingSchemaAction枚举值之一
Copy()方法，返回一个DataSet的副本
数据库连接单例
using System;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Collections.Generic;
namespace Demo_7_4 {
public class DB {
static Lazy<IFreeSql> oracleLazy = new Lazy<IFreeSql>(() => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Oracle, "user id=abac;password=abac;data source=//10.168.8.1:1521/oradbsgl;Pooling=true;Max Pool Size=2")
.UseAutoSyncStructure(true)
.UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd => Trace.WriteLine(cmd.CommandText))
.Build());
public static IFreeSql oracle => oracleLazy.Value;
}
}
增
fsql.Insert(new ABXRTEST02 {
STRING1 = txtString1.Text,
NUMBER1 = int.Parse(txtNumber1.Text),
DT1 = dtpickerDt1.Value
}).ExecuteAffrows();
删
fsql.Delete<ABXRTEST02>()
.Where(b => b.SYSID == this.curIdx)
.ExecuteAffrows();
改
fsql.Update<ABXRTEST02>()
.Set(b => b.STRING1, txtString1.Text)
.Set(b => b.NUMBER1, int.Parse(txtNumber1.Text))
.Set(b => b.DT1, dtpickerDt1.Value)
.Where(b => b.SYSID == this.SYSID)
.ExecuteAffrows();
查
fsql.Select<ABXRTEST02>()
.Where(a => a.SYSID == this.SYSID)
.ToList();
fsql.Select<ABXRTEST02>().ToList();
首先是数据表结构如下
界面如下
DT1 对应的 DateTimePicker 的 Format 属性改为 Short
STRING1 的数据列为nvarchar2(20)，故其对应TextBox应设置最大长度（MaxLength）为20
SYSID和NUMBER1对应的TextBox应只能输入数字，故为其KeyPress事件添加方法如下
private void number1_textbox_KeyPress(object sender, KeyPressEventArgs e) {
if (!(Char.IsNumber(e.KeyChar)) && e.KeyChar != (char)8) { e.Handled = true; }
}
数据库连接代码
OracleConnection con;
string constr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.168.8.109)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=abac)));User ID=abac;Password=abac";
private void Form1_Load(object sender, EventArgs e) {
this.con = new OracleConnection(constr);//连接到数据库
this.con.Open();
}
获取数据列表
private void getList() {
string cmdText = @"select SYSID,STRING1,NUMBER1,DT1 from ABXRTEST02 ";
OracleDataAdapter adapter = new OracleDataAdapter(cmdText, constr);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "QueryResult");
adapter.Dispose();
DataTable dt = dataSet.Tables[0];
this.dataGridView1.Columns["SYSID"].DataPropertyName = "SYSID";
this.dataGridView1.Columns["STRING1"].DataPropertyName = "STRING1";
this.dataGridView1.Columns["NUMBER1"].DataPropertyName = "NUMBER1";
this.dataGridView1.Columns["DT1"].DataPropertyName = "DT1";
this.dataGridView1.DataSource = dt;
}
添加数据时要注意，SYSID不能重复
try {
OracleCommand command = new OracleCommand("insert into " +
"ABXRTEST02(SYSID,STRING1,NUMBER1,DT1) values ('" +
sysid_textbox.Text + "','" + string1_textbox.Text +
"','" + number1_textbox.Text + "',to_date('" +
dt1_date.Text + "','yyyy-mm-dd'))", this.con);
command.ExecuteNonQuery();
command.Dispose();
MessageBox.Show("添加成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
} catch (Exception ex) {
MessageBox.Show("添加失败，SYSID重复！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
Form1.cs
using System.Data;
using System.Drawing;
using Oracle.ManagedDataAccess.Client;//oracle数据提供程序
namespace Demo_7_4 {
public partial class Form1 : Form {
OracleConnection con;
string constr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.168.6.29)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=abac)));User ID=abac;Password=abac";
string flag = "";
public Form1() {
InitializeComponent();
}
/// <summary>
/// 添加
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e) {
this.sysid_textbox.Text = "";
this.string1_textbox.Text = "";
this.number1_textbox.Text = "";
this.dt1_date.Text = "";
this.sysid_textbox.Enabled = true;
this.string1_textbox.Enabled = true;
this.number1_textbox.Enabled = true;
this.dt1_date.Enabled = true;
this.button3.Enabled = false;
this.button2.Enabled = false;
this.add_button.Enabled = false;
this.button1.Enabled = true;
this.flag = "add";
}
/// <summary>
/// 修改
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e) {
this.sysid_textbox.Enabled = false;
this.string1_textbox.Enabled = true;
this.number1_textbox.Enabled = true;
this.dt1_date.Enabled = true;
this.button1.Enabled = true;
this.button3.Enabled = false;
this.button2.Enabled = false;
this.add_button.Enabled = true;
this.flag = "edit";
}
/// <summary>
/// 删除
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e) {
if ((MessageBox.Show("是否真的要删除用户" + this.sysid_textbox.Text + "吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)).Equals(DialogResult.Yes)) {
String strQuery = "delete from ABXRTEST02 where SYSID='" + sysid_textbox.Text + "'";
OracleCommand command = new OracleCommand(strQuery, this.con);
int num = command.ExecuteNonQuery();
command.Dispose();
MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
getList();
this.sysid_textbox.Text = "";
this.string1_textbox.Text = "";
this.number1_textbox.Text = "";
this.dt1_date.Text = "";
this.sysid_textbox.Enabled = false;
this.string1_textbox.Enabled = false;
this.number1_textbox.Enabled = false;
this.button3.Enabled = false;
this.button2.Enabled = false;
this.button1.Enabled = false;
this.dt1_date.Enabled = false;
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) {
DataTable dt = (DataTable)this.dataGridView1.DataSource;
if (e.RowIndex > -1) {
string SYSID = dt.Rows[e.RowIndex]["SYSID"].ToString();
string STRING1 = dt.Rows[e.RowIndex]["STRING1"].ToString();
string NUMBER1 = dt.Rows[e.RowIndex]["NUMBER1"].ToString();
string DT1 = dt.Rows[e.RowIndex]["DT1"].ToString();
string strQuery = @"select SYSID,STRING1,NUMBER1,DT1 from ABXRTEST02
where SYSID='" + SYSID + "' and STRING1='" + STRING1 + "' and NUMBER1='" + NUMBER1 +
"'";
OracleDataAdapter adapter = new OracleDataAdapter(strQuery, this.constr);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "QueryResult");
adapter.Dispose();
DataTable dtResult = dataSet.Tables[0];
this.sysid_textbox.Text = dtResult.Rows[0]["SYSID"].ToString();
this.string1_textbox.Text = dtResult.Rows[0]["STRING1"].ToString();
this.number1_textbox.Text = dtResult.Rows[0]["NUMBER1"].ToString();
this.dt1_date.Text = dtResult.Rows[0]["DT1"].ToString();
this.button2.Enabled = true;
this.button1.Enabled = false;
this.add_button.Enabled = true;
this.button3.Enabled = true;
}
}
private void Form1_Load(object sender, EventArgs e) {
this.sysid_textbox.Enabled = false;
this.string1_textbox.Enabled = false;
this.number1_textbox.Enabled = false;
this.button3.Enabled = false;
this.button2.Enabled = false;
this.button1.Enabled = false;
this.dt1_date.Enabled = false;
this.con = new OracleConnection(constr);//连接到数据库
this.con.Open();
getList();
}
private void getList() {
string cmdText = @"select SYSID,STRING1,NUMBER1,DT1 from ABXRTEST02 ";
OracleDataAdapter adapter = new OracleDataAdapter(cmdText, constr);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "QueryResult");
adapter.Dispose();
DataTable dt = dataSet.Tables[0];
this.dataGridView1.Columns["SYSID"].DataPropertyName = "SYSID";
this.dataGridView1.Columns["STRING1"].DataPropertyName = "STRING1";
this.dataGridView1.Columns["NUMBER1"].DataPropertyName = "NUMBER1";
this.dataGridView1.Columns["DT1"].DataPropertyName = "DT1";
this.dataGridView1.DataSource = dt;
}
private void button1_Click_1(object sender, EventArgs e) {
if (this.string1_textbox.Text == "") {
MessageBox.Show("STRING1不允许为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.string1_textbox.Focus();
return;
} else if (this.number1_textbox.Text == "") {
MessageBox.Show("NUMBER1不允许为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.number1_textbox.Focus();
return;
} else if (this.dt1_date.Text == "") {
MessageBox.Show("DT1不允许为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.dt1_date.Focus();
return;
}
if (this.flag == "add") {
try {
OracleCommand command = new OracleCommand("insert into " +
"ABXRTEST02(SYSID,STRING1,NUMBER1,DT1) values ('" + sysid_textbox.Text +
"','" + string1_textbox.Text + "','"
+ number1_textbox.Text + "',to_date('"
+ dt1_date.Text + "','yyyy-mm-dd'))", this.con);
command.ExecuteNonQuery();
command.Dispose();
MessageBox.Show("添加成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
} catch (Exception ex) {
MessageBox.Show("添加失败，SYSID重复！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
} else if (this.flag == "edit") {
OracleCommand command = new OracleCommand("update ABXRTEST02 set " +
"STRING1='" + string1_textbox.Text + "', " +
"NUMBER1='" + number1_textbox.Text + "', " +
"DT1=to_date('" + dt1_date.Text + "','yyyy-mm-dd') where SYSID='" + sysid_textbox.Text + "'", this.con);
command.ExecuteNonQuery();
command.Dispose();
MessageBox.Show("修改成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
getList();
this.button1.Enabled = false;
this.add_button.Enabled = true;
this.sysid_textbox.Enabled = false;
this.string1_textbox.Enabled = false;
this.number1_textbox.Enabled = false;
this.dt1_date.Enabled = false;
this.sysid_textbox.Text = "";
this.string1_textbox.Text = "";
this.number1_textbox.Text = "";
this.dt1_date.Text = "";
}
private void number1_textbox_KeyPress(object sender, KeyPressEventArgs e) {
if (!(Char.IsNumber(e.KeyChar)) && e.KeyChar != (char)8) { e.Handled = true; }
}
~Form1() {
this.con.Close();
}
}
}
Form1.Designer.cs
namespace Demo_7_4 {
partial class Form1 {
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing) {
if (disposing && (components != null)) {
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent() {
components = new System.ComponentModel.Container();
bindingSource1 = new BindingSource(components);
label1 = new Label();
label2 = new Label();
label3 = new Label();
label4 = new Label();
dataGridView1 = new DataGridView();
SYSID = new DataGridViewTextBoxColumn();
STRING1 = new DataGridViewTextBoxColumn();
NUMBER1 = new DataGridViewTextBoxColumn();
DT1 = new DataGridViewTextBoxColumn();
add_button = new Button();
button2 = new Button();
sysid_textbox = new TextBox();
string1_textbox = new TextBox();
number1_textbox = new TextBox();
dt1_date = new DateTimePicker();
button3 = new Button();
button1 = new Button();
((System.ComponentModel.ISupportInitialize)bindingSource1).BeginInit();
((System.ComponentModel.ISupportInitialize)dataGridView1).BeginInit();
SuspendLayout();
//
// label1
//
label1.AutoSize = true;
label1.Location = new Point(25, 42);
label1.Name = "label1";
label1.Size = new Size(51, 20);
label1.TabIndex = 0;
label1.Text = "SYSID";
//
// label2
//
label2.AutoSize = true;
label2.Location = new Point(698, 42);
label2.Name = "label2";
label2.Size = new Size(38, 20);
label2.TabIndex = 1;
label2.Text = "DT1";
//
// label3
//
label3.AutoSize = true;
label3.Location = new Point(465, 42);
label3.Name = "label3";
label3.Size = new Size(83, 20);
label3.TabIndex = 2;
label3.Text = "NUMBER1";
//
// label4
//
label4.AutoSize = true;
label4.Location = new Point(230, 42);
label4.Name = "label4";
label4.Size = new Size(73, 20);
label4.TabIndex = 3;
label4.Text = "STRING1";
//
// dataGridView1
//
dataGridView1.AccessibleRole = AccessibleRole.MenuPopup;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
dataGridView1.Columns.AddRange(new DataGridViewColumn[] { SYSID, STRING1, NUMBER1, DT1 });
dataGridView1.Location = new Point(25, 215);
dataGridView1.Name = "dataGridView1";
dataGridView1.ReadOnly = true;
dataGridView1.RowHeadersWidth = 51;
dataGridView1.RowTemplate.Height = 29;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.Size = new Size(910, 342);
dataGridView1.TabIndex = 4;
dataGridView1.CellContentClick += dataGridView1_CellContentClick;
//
// SYSID
//
SYSID.HeaderText = "SYSID";
SYSID.MinimumWidth = 6;
SYSID.Name = "SYSID";
SYSID.ReadOnly = true;
SYSID.Width = 125;
//
// STRING1
//
STRING1.HeaderText = "STRING1";
STRING1.MinimumWidth = 6;
STRING1.Name = "STRING1";
STRING1.ReadOnly = true;
STRING1.Width = 125;
//
// NUMBER1
//
NUMBER1.HeaderText = "NUMBER1";
NUMBER1.MinimumWidth = 6;
NUMBER1.Name = "NUMBER1";
NUMBER1.ReadOnly = true;
NUMBER1.Width = 125;
//
// DT1
//
DT1.HeaderText = "DT1";
DT1.MinimumWidth = 6;
DT1.Name = "DT1";
DT1.ReadOnly = true;
DT1.Width = 200;
//
// add_button
//
add_button.Location = new Point(178, 134);
add_button.Name = "add_button";
add_button.Size = new Size(125, 49);
add_button.TabIndex = 5;
add_button.Text = "添加";
add_button.UseVisualStyleBackColor = true;
add_button.Click += button1_Click;
//
// button2
//
button2.Location = new Point(369, 134);
button2.Name = "button2";
button2.Size = new Size(135, 49);
button2.TabIndex = 6;
button2.Text = "修改";
button2.UseVisualStyleBackColor = true;
button2.Click += button2_Click;
//
// sysid_textbox
//
sysid_textbox.Location = new Point(99, 39);
sysid_textbox.Name = "sysid_textbox";
sysid_textbox.Size = new Size(125, 27);
sysid_textbox.TabIndex = 7;
sysid_textbox.KeyPress += number1_textbox_KeyPress;
//
// string1_textbox
//
string1_textbox.Location = new Point(309, 39);
string1_textbox.MaxLength = 20;
string1_textbox.Name = "string1_textbox";
string1_textbox.Size = new Size(125, 27);
string1_textbox.TabIndex = 8;
//
// number1_textbox
//
number1_textbox.Location = new Point(554, 39);
number1_textbox.Name = "number1_textbox";
number1_textbox.Size = new Size(125, 27);
number1_textbox.TabIndex = 9;
number1_textbox.KeyPress += number1_textbox_KeyPress;
//
// dt1_date
//
dt1_date.Format = DateTimePickerFormat.Short;
dt1_date.Location = new Point(763, 37);
dt1_date.Name = "dt1_date";
dt1_date.Size = new Size(172, 27);
dt1_date.TabIndex = 11;
//
// button3
//
button3.Location = new Point(568, 134);
button3.Name = "button3";
button3.Size = new Size(135, 49);
button3.TabIndex = 12;
button3.Text = "删除";
button3.UseVisualStyleBackColor = true;
button3.Click += button3_Click;
//
// button1
//
button1.Location = new Point(763, 134);
button1.Name = "button1";
button1.Size = new Size(135, 49);
button1.TabIndex = 13;
button1.Text = "确定";
button1.UseVisualStyleBackColor = true;
button1.Click += button1_Click_1;
//
// Form1
//
AutoScaleDimensions = new SizeF(9F, 20F);
AutoScaleMode = AutoScaleMode.Font;
ClientSize = new Size(965, 569);
Controls.Add(button1);
Controls.Add(button3);
Controls.Add(dt1_date);
Controls.Add(number1_textbox);
Controls.Add(string1_textbox);
Controls.Add(sysid_textbox);
Controls.Add(button2);
Controls.Add(add_button);
Controls.Add(dataGridView1);
Controls.Add(label4);
Controls.Add(label3);
Controls.Add(label2);
Controls.Add(label1);
Name = "Form1";
Text = "Form1";
Load += Form1_Load;
((System.ComponentModel.ISupportInitialize)bindingSource1).EndInit();
((System.ComponentModel.ISupportInitialize)dataGridView1).EndInit();
ResumeLayout(false);
PerformLayout();
}
#endregion
private BindingSource bindingSource1;
private Label label1;
private Label label2;
private Label label3;
private Label label4;
private DataGridView dataGridView1;
private Button add_button;
private Button button2;
private TextBox sysid_textbox;
private TextBox string1_textbox;
private TextBox number1_textbox;
private DateTimePicker dt1_date;
private Button button3;
private DataGridViewTextBoxColumn SYSID;
private DataGridViewTextBoxColumn STRING1;
private DataGridViewTextBoxColumn NUMBER1;
private DataGridViewTextBoxColumn DT1;
private Button button1;
}
}