Cacher is the code snippet organizer for pro developers

//insert字符及图片
OpenFileDialog OpenDialog = new OpenFileDialog();
//文件路径控件
if (OpenDialog.ShowDialog == Windows.Forms.DialogResult.OK) {
	byte[] da;

	da = My.Computer.FileSystem.ReadAllBytes(OpenDialog.FileName);
	Mycom = new OleDbCommand();
	Mycom.CommandText = "insert into User_name(Nam,bz,Jltm,img) values(@Nam,@bz,@Jltm,@img)";
	Mycom.Parameters.Add("@Nam", OleDbType.VarChar).Value = TextBox1.Text.Trim;
	Mycom.Parameters.Add("@bz", OleDbType.VarChar).Value = TextBox2.Text.TrimMycom.Parameters.Add("@Jltm", OleDbType.VarChar).Value == TextBox3.Text.Trim;
	Mycom.Parameters.Add("@img", OleDbType.Binary).Value = da;

	Mycom.Connection = Mycon;
	if (Mycom.ExecuteNonQuery > 0) {
		MsgBox("数据+图片添加成功");
	}
	Mycom.Dispose();
}

'Datatable绑定更新
Dim MdataAdapter As New OleDbDataAdapter()
Dim CB As OleDbCommandBuilder
Dim Mytable As DataTable

MdataAdapter = New OleDbDataAdapter("Select * from Pr", Mcon)
CB = New OleDbCommandBuilder(MdataAdapter)
MdataAdapter.Fill(Mytable)
DataGridView1.DataSource = Mytable

Private Sub SaveData()
  Try
      Dim MsgInt As Integer = MsgBox("请确认是否更新数据?", MsgBoxStyle.Exclamation + MsgBoxStyle.OkCancel, "更新提示")
      If MsgInt = 1 Then
          Dim tempDT As DataTable
          tempDT =Mytable
          If tempDT Is Nothing Then
              MsgBox("数据未修改")
              Exit Sub
          End If
          MdataAdapter.UpdateCommand = CB.GetUpdateCommand
          MdataAdapter.InsertCommand = CB.GetInsertCommand
          MdataAdapter.DeleteCommand = CB.GetDeleteCommand
          'End If
          MdataAdapter.Update(SqlTab)
          MsgBox("数据已更新", MsgBoxStyle.Exclamation + MsgBoxStyle.OkCancel, "提示")
      Else
          Exit Sub
      End If
  Catch ex As Exception
      MsgBox(ex.Message.ToString)
  End Try
End Sub

'单行数据更新 
'出错列 子查询返回的值不止一个。当子查询跟随在 =、!=、
'当对一个执行一次性更新多条记录的操作时，如果此表上还有更新触发器则可能引发此错误。
Dim da As SqlDataAdapter
Dim dt As New DataTable
Dim dr As DataRow
Dim sqlcmd As String

sqlcmd = "select * from patient where p_no='" & Trim(TextBox1.Text) & "'"
da = New SqlDataAdapter(sqlcmd, ModuleCommon.conn)
da.Fill(dt)
'添加  dr = dt.NewRow()'
dr = dt.Rows(0)
dr.BeginEdit()
'添加   da.InsertCommand = New SqlCommandBuilder(da).GetInsertCommand()
da.UpdateCommand = New SqlCommandBuilder(da).GetUpdateCommand()
dr("p_no") = TextBox1.Text
dr("p_name") = TextBox2.Text
dr("p_sex") = ComboBox1.Text
dr("p_address") = TextBox4.Text
' 添加  dt.Rows.Add(dr)
dr.EndEdit()
da.Update(dt.GetChanges())
Call 病人管理.WinLoad()'刷新界面'

//方法一:系统时间的格式化,如果直接是NOW可以不格式化录入
Mydate = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss");
//方法二:日期控件值格式化录入.ToString("yyyy-MM-dd HH:mm") 推荐
Mycom.CommandText = string.Format("insert into Pr(tzdh,ypbh,wtdate) values('{0}','{1}','{2}'", TextBox1.Text, TextBox2.Text, this.DateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm"));
//方法三:先格式化控件显示的文件,然后用日期控件文件插入ACCESS,至于##还是''包夹参数都是允许的
DateTimePicker1.Format = DateTimePickerFormat.Custom;
//设置自定义模式
DateTimePicker1.CustomFormat = "yyyy-MM-dd HH:mm";
Mycom.CommandText = string.Format("insert into Pr(tzdh,ypbh,wtdate) values('{0}','{1}',#{2}#,", TextBox1.Text, TextBox2.Text, this.DateTimePicker1.Text);

//DataTable备份还原数据(XML方式)
//OutXML(Me.DataGridView1, Me.DataDataSet.Pr)
//选中项导出XML
private void OutXML(DataGridView MyGrid, DataTable MyDt)
{
	try {
		DataTable BakDt = new DataTable();
		//无法序列化DataTable。数据表名称未设置用下面注释方法
		//dt = new DataTable { TableName = "TableNames"}; 
		BakDt = MyDt.Clone;
		foreach (DataGridViewRow SelRow in MyGrid.SelectedRows) {
			//Debug.Print(SelRow.Cells(0).RowIndex)
			BakDt.Rows.Add(MyDt.Rows(SelRow.Cells(0).RowIndex).ItemArray);
		}
		if (this.FolderBrowserDialog1.ShowDialog == Windows.Forms.DialogResult.OK) {
			BakDt.WriteXml(this.FolderBrowserDialog1.SelectedPath.ToString + "\\RowBak" + Now.Date.ToString("yyyyMMdd") + ".Dat");
			MessageBox.Show("选中数据行,已成功备份!", "备份", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
		}
	} catch (Exception ex) {
		MsgBox(ex.Message.ToString, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "错误");
	}

}

//InPXML(Me.DataDataSet.Pr)
//从XML读取到数据表
private void InPXML(DataTable MyDt)
{


	try {
		static_InPXML_Rowindex = this.DataDataSet.Pr.Compute("Max(ID编号)", "");
		DataTable BakDt = new DataTable();
		BakDt = MyDt.Clone;
		//克隆构架

		if (this.OpenFileDialog1.ShowDialog == Windows.Forms.DialogResult.OK) {
			//利用排序过程改写XML中的ID编号列值不与当前表重复
			SortXml(OpenFileDialog1.FileName.ToString, "ID编号", static_InPXML_Rowindex);
			BakDt.ReadXml(OpenFileDialog1.FileName.ToString);
			MyDt.Merge(BakDt);
			//合并
		}

	} catch (Exception ex) {
		MessageBox.Show(ex.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
	}
}

//排序过程
//XML遍历ID并改写
private void SortXml(string Path, string ID, int IDval = 0)
{
	XDocument doc = XDocument.Load(Path);
	int A = IDval;

	//'倒序()
	//For Each quer As XElement In (From people In doc.Descendants("ID编号"))
	//    A -= 1
	//    quer.Value = A
	//    Debug.Print(quer)
	//Next

	//顺序()
	foreach (XElement quer in doc.Descendants(ID)) {
		A += 1;
		quer.Value = A;
		Debug.Print(quer);
	}
	doc.Save(Path);
}

//存储过程参数式执行
public static void DbExePa(SqlConnection Con, string Param1, string Param2, string Param3, string Param4)
{
	try {
		if (Con.State == 1) {
			Con.Close();
		}
		Con.Open();
		SqlCommand Mycom = new SqlCommand();
		Mycom.Connection = Con;
		Mycom.CommandType = CommandType.StoredProcedure;
		Mycom.CommandText = "Proc_User";
		//过程名
		Mycom.Parameters.Add("@names", SqlDbType.VarChar, 80).Value = Param1;
		//传入参数1值
		if (!Param2 == null) {
			Mycom.Parameters.Add("@name", SqlDbType.VarChar, 80).Value = Param2;
		}
		if (!Param3 == null) {
			Mycom.Parameters.Add("@pwd", SqlDbType.VarChar, 80).Value = Param3;
		}
		if (!Param4 == null) {
			Mycom.Parameters.Add("@a", SqlDbType.VarChar, 80).Value = Param4;
		}
		//执行
		if (Mycom.ExecuteNonQuery > 0) {
			MsgBox("添加成功");
		}

	} catch (Exception ex) {
		MessageBox.Show(ex.ToString);
	}
	
//Parameters参数方式添加图片
OpenFileDialog MyOpenDialog = new OpenFileDialog();
//'文件路径控件
if (MyOpenDialog.ShowDialog == Windows.Forms.DialogResult.OK) {
	byte[] Mybyte;
	Mybyte = My.Computer.FileSystem.ReadAllBytes(MyOpenDialog.FileName);

	Mycom.CommandText = "insert into User_name(Nam,bz,Jltm,img) values(@Nam,@bz,@Jltm,@img)";
	Mycom.Parameters.Add("@Nam", OleDbType.VarChar).Value = TextBox1.Text.Trim;
	Mycom.Parameters.Add("@bz", OleDbType.VarChar).Value = TextBox2.Text.Trim;
	Mycom.Parameters.Add("@Jltm", OleDbType.VarChar).Value = TextBox3.Text.Trim;
	Mycom.Parameters.Add("@img", OleDbType.Binary).Value = Mybyte;

	Mycom.Connection = Mycon;
	if (Mycom.ExecuteNonQuery > 0) {
		MsgBox("数据+图片添加成功");
	}
	Mycom.Dispose();
}

//通过ExecuteReader来获取图片并将其显示到Picturebox控件
Mycom.CommandText = "select img from bytes where id =" + Convert.ToInt32(TextBox1.Text);
//注意选择编号
Mycom.Connection = Mycon;
OleDb.OleDbDataReader MySdr;
MySdr = Mycom.ExecuteReader;
if (MySdr.Read) {
	byte[] MyByte;
	MyByte = MySdr(0);
	PictureBox1.Image = Bitmap.FromStream(new IO.MemoryStream(Mybyte));
}
MySdr.Close();
Mycom.Dispose();

'txt读取到二维数组
Public Shared Function TxtToArr(ByVal path As String) As String(,)
    Dim file As New StreamReader(path)
    '读取文件获取行数
    Dim s As String = " "
    Dim Rows As Integer = 0
    While (s.Length > 0)
        s = file.ReadLine()
        If s = Nothing Then Exit While
        Rows += 1
    End While
    file.Close()

    '循环读取表格到二维数组
    file = New StreamReader(path, System.Text.Encoding.Default)
    Dim dt(Rows, 42) As String
    s = " "
    Rows = 0
    While (s.Length > 0)
        s = file.ReadLine()
        If s = Nothing Then Exit While
        For i As Integer = 0 To s.Split(",").Count - 2
            'If s.Split(",")(i) = Nothing Then Exit For
            If s.Split(",")(i) = Nothing Then
                dt(Rows, i) = ""
            Else
                dt(Rows, i) = s.Split(",")(i)
            End If
        Next
        Rows += 1
    End While

    file.Close()
    Return dt
End Function
    
‘使用
      Dim arr(,) As String = ReadTxToDt(Filename)
        ‘GetUpperBound(0)获取第一维上限
        For i As Integer = 1 To arr.GetUpperBound(0) - 1
            SqlHelper.Run(String.Format( _
          "insert into 测试参数(规格型号,额定电压,ACDC,线圈电流上限,F6开距下限,F6开距上限,F7开距下限,F7开距上限,F8开距下限,F8开距上限,F9开距下限,F9开距上限,L1开距下限,L1开距上限,L2开距下限,L2开距上限,L3开距下限,L3开距上限,L4开距下限,L4开距上限,开距同步差下限,开距同步差上限,F6超程下限,F6超程上限,F7超程下限,F7超程上限,F8超程下限,F8超程上限,F9超程下限,F9超程上限,L1超程下限,L1超程上限,L2超程下限,L2超程上限,L3超程下限,L3超程上限,L4超程下限,L4超程上限,额定频率,触头类型,吸合电压起始值,释放电压起始值) " _
           & "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}','{41}')", arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5), arr(i, 6), arr(i, 7), arr(i, 8), arr(i, 9), arr(i, 10), arr(i, 11), arr(i, 12), arr(i, 13), arr(i, 14), arr(i, 15), arr(i, 16), arr(i, 17), arr(i, 18), arr(i, 19), arr(i, 20), arr(i, 21), arr(i, 22), arr(i, 23), arr(i, 24), arr(i, 25), arr(i, 26), arr(i, 27), arr(i, 28), arr(i, 29), arr(i, 30), arr(i, 31), arr(i, 32), arr(i, 33), arr(i, 34), arr(i, 35), arr(i, 36), arr(i, 37), arr(i, 38), arr(i, 39), arr(i, 40), arr(i, 41), arr(i, 42)))
        Next

'连接Excel[Imports System.Data.OleDb '必须的引用]
Dim MyCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelName.xls;Extended Properties='Excel 8.0;IMEX=1';")
'打开ＯＦＦＣＥ２００７的方式  
'Dim myConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=2.xlsx;Extended Properties='Excel 12.0;IMEX=1';") 
MyCon.Open()
Dim MyAdapter As New OleDbDataAdapter("SELECT * FROM [SheetName$]", MyCon)
Dim MyDs As New DataSet
MyAdapter.Fill(MyDs, "ExcelInfo")
$DataGridView$.DataSource = MyDs.Tables("ExcelInfo")
MyCon.Close()
'IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。
'当我们设置IMEX＝1时将强制混合数据转换为文本，但仅仅这种设置并不可靠，IMEX＝1只确保在某列前8行数据
'至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例
'如某列前8行数据全为纯数字，那么它仍然以数字类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。
'另一个改进的措施是IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几
'条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”
'下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题，即使我们把IMEX设为“1”， TypeGuessRows设得再大，
'例如1000，假设数据表有1001行，某列前1000行全为纯数字，该列的第1001行又是一个文本，ISAM驱动的这种机制还是让这列的数据变成空

'连接到ACCESS数据库
Imports System.Data.OleDb
Mcon.Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$AccessFileName$;Persist Security Info=False"
' 加密方式数据库打开语句("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=true;Data Source=$AccessFileName$;;Jet OLEDB:Database Password=****1**")
Mcon.Con = New OleDb.OleDbConnection(Mcon.Str)
Mcon.Con.Open()

'连接到Mysql数据库
Imports MySql.Data.MySqlClient
'请先安装MySQL Connector Net 6.9.9,然后在项目->Net中添加引用MySql.Data
Dim Mycon As MySqlConnection
Dim Mycom As MySqlCommand
Mycon = New MySqlConnection("Data Source=127.0.0.1;User=root;Password=****1**;Database=Mydata")
Mycon.Open()

'连接到SqlServer
Imports System.Data.SqlClient
Dim Mycon As New SqlConnection("uid=sa;pwd=****1**;database=hjerp;server=(local)")
Mycon.Open()

'读取app.config中数据库连接,注意2个引用
Mycon.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings.Item(1).ToString()
Mycon.Open()