259 lines
8.5 KiB
C#
259 lines
8.5 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using System.Windows.Forms;
|
|||
|
|
using System.Runtime.InteropServices;
|
|||
|
|
|
|||
|
|
namespace AGVSystem
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数据库操作
|
|||
|
|
/// </summary>
|
|||
|
|
public class ClsDBConn_sql
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取CPU运行的时间
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
[DllImport("kernel32")]
|
|||
|
|
static extern uint GetTickCount();
|
|||
|
|
|
|||
|
|
private System.Data.DataSet dt = new System.Data.DataSet();
|
|||
|
|
private SqlConnection sqlconn = new SqlConnection(Cls.Param.connectStr);
|
|||
|
|
private SqlDataAdapter sqladapter = null;
|
|||
|
|
private SqlCommandBuilder sqlcommandb = null;
|
|||
|
|
private SqlCommandBuilder sqlcommandb_ds = null;
|
|||
|
|
private BindingSource bindsource = new BindingSource();
|
|||
|
|
private SqlCommand command = null;
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 关闭连接
|
|||
|
|
/// </summary>
|
|||
|
|
public void ConnClosed()
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (sqlconn != null)
|
|||
|
|
{
|
|||
|
|
if (sqlconn.State == ConnectionState.Open)
|
|||
|
|
{
|
|||
|
|
sqlconn.Close();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 连接数据库返回dataset
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="str"> SQL语句</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public System.Data.DataSet connDt(string str)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (sqlconn.State != ConnectionState.Open)
|
|||
|
|
sqlconn.Open();
|
|||
|
|
sqladapter = new SqlDataAdapter(str, sqlconn);
|
|||
|
|
sqlcommandb = new SqlCommandBuilder(sqladapter);
|
|||
|
|
if (dt.Tables.Count > 0)
|
|||
|
|
dt.Tables.Clear();
|
|||
|
|
sqladapter.Fill(dt);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("SQL：" + str + " , \r\n" + err.Message + " \r\n FunctionName：connDt", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 返回可更新的dataset
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="str"> SQL语句</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public System.Data.DataSet dtEdit(string str)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
sqladapter = new SqlDataAdapter(str, sqlconn);
|
|||
|
|
sqlcommandb_ds = new SqlCommandBuilder(sqladapter);
|
|||
|
|
if (dt.Tables.Count > 0)
|
|||
|
|
dt.Tables.Clear();
|
|||
|
|
sqladapter.Fill(dt);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("SQL：" + str + " , \r\n" + err.Message + " \r\n FunctionName：dtEdit", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 更新Dataset数据到数据库
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="ds"></param>
|
|||
|
|
/// <param name="adapter"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public bool dtCommit(System.Data.DataSet ds )
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
//Int64 t1 = GetTickCount();
|
|||
|
|
int DS = ds.Tables[0].Rows.Count;
|
|||
|
|
|
|||
|
|
sqladapter.Update(ds);
|
|||
|
|
//Int64 t2 = GetTickCount() - t1;
|
|||
|
|
//if (t2 > 2)
|
|||
|
|
// MessageBox.Show("update time:"+t2.ToString());
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
//MessageBox.Show(err.Message+"EventName：dtCommit");
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数据库连接到datagridview
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="str">sql语句</param>
|
|||
|
|
/// <param name="dataGridView">datagridview控件名称</param>
|
|||
|
|
public bool connDGV(string str, DataGridView dgv)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (sqlconn.State == ConnectionState.Open) sqlconn.Close();
|
|||
|
|
sqladapter = new SqlDataAdapter(str, sqlconn);
|
|||
|
|
if (dt.Tables.Count > 0)
|
|||
|
|
dt.Tables.Clear();
|
|||
|
|
sqladapter.Fill(dt);
|
|||
|
|
if (dt.Tables.Count > 0)
|
|||
|
|
dgv.DataSource = dt.Tables[0].Copy();
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("SQL：" + str + " , \r\n" + err.Message + " \r\n FunctionName：connDGV", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 数据库连接到datagridview，并可通过datagridview修改数据库
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="str">sql语句</param>
|
|||
|
|
/// <param name="dataGridView">datagridview控件名称</param>
|
|||
|
|
public bool connDgvEdit(string str, DataGridView dgv)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (sqlconn.State == ConnectionState.Open) sqlconn.Close();
|
|||
|
|
sqladapter = new SqlDataAdapter(str, sqlconn);
|
|||
|
|
if (dt.Tables.Count > 0)
|
|||
|
|
dt.Tables.Clear();
|
|||
|
|
sqladapter.Fill(dt);
|
|||
|
|
sqlcommandb = new SqlCommandBuilder(sqladapter);
|
|||
|
|
bindsource.DataSource = dt.Tables[0];
|
|||
|
|
dgv.DataSource = dt.Tables[0];
|
|||
|
|
dgv.DataSource = bindsource;
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("SQL：" + str + " , \r\n" + err.Message + " \r\n FunctionName：connDGVEdit", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 提交datagridview的数据
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="dataGridView">datagridview控件名称</param>
|
|||
|
|
public bool DgvCommit(DataGridView dgv)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
dgv.EndEdit();
|
|||
|
|
bindsource.EndEdit();
|
|||
|
|
sqladapter.Update(dt.Tables[0]);
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show(err.Message + " \r\n FunctionName：DgvCommit", "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 直接执行SQL语句
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="sqlstr"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public string Command(string sqlstr)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
int count = 0;
|
|||
|
|
if (sqlconn.State != ConnectionState.Open) sqlconn.Open();
|
|||
|
|
command = new SqlCommand(sqlstr, sqlconn);
|
|||
|
|
count = command.ExecuteNonQuery();
|
|||
|
|
//sqlconn.Close();
|
|||
|
|
return count.ToString();
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
return "SQL：" + sqlstr + " , \r\n" + err.Message + " \r\n FunctionName：Command";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public bool DtCommand(string Sqlstr, string[,] insertValue)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (sqlconn.State == ConnectionState.Open) sqlconn.Close();
|
|||
|
|
sqladapter = new SqlDataAdapter(Sqlstr, sqlconn);
|
|||
|
|
if (dt.Tables.Count > 0)
|
|||
|
|
dt.Tables.Clear();
|
|||
|
|
sqlcommandb = new SqlCommandBuilder(sqladapter);
|
|||
|
|
sqladapter.Fill(dt);
|
|||
|
|
for (int rw = 0; rw < insertValue.GetLength(0); rw++)
|
|||
|
|
{
|
|||
|
|
System.Data.DataRow drow = dt.Tables[0].NewRow();
|
|||
|
|
for (int cl = 0; cl < insertValue.GetLength(1); cl++)
|
|||
|
|
{
|
|||
|
|
drow[cl] = insertValue[rw, cl];
|
|||
|
|
}
|
|||
|
|
dt.Tables[0].Rows.Add(drow);
|
|||
|
|
}
|
|||
|
|
sqladapter.Update(dt);
|
|||
|
|
sqlconn.Close();
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show(err.Message, "错误！", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public void gg()
|
|||
|
|
{
|
|||
|
|
bindsource.AddNew();
|
|||
|
|
bindsource.MoveLast();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|