using System; using System.Text; using System.Data; using System.Data.OleDb; using System.Windows.Forms; using System.Drawing; namespace Skyiv.Util.Oledb { class OleDbDlg : Form { Label lblTable; TextBox tbxConn; TextBox tbxSql; TextBox tbxMsg; Button btnSubmit; CheckBox chkStru; DataGrid dgOut; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=yes\";Data Source=Test.xls"; public OleDbDlg() { SuspendLayout();
btnSubmit = new Button(); btnSubmit.Text = "执行"; btnSubmit.Location = new Point(10, 420); btnSubmit.Size = new Size(60, 24); btnSubmit.Click += new EventHandler(Submit_Click); btnSubmit.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left); chkStru = new CheckBox(); chkStru.Text = "结构"; chkStru.Location = new Point(80, 420); chkStru.Size = new Size(60, 24); chkStru.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left); lblTable = new Label(); lblTable.Text = "数据源"; lblTable.Location = new Point(12, 460); lblTable.Size = new Size(70, 24); lblTable.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left); tbxConn = new TextBox(); tbxConn.Text = strConn; tbxConn.Location = new Point(83, 456); tbxConn.Size = new Size(626, 20); tbxConn.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right); tbxSql = new TextBox(); tbxSql.Text = "SELECT *\r\nFROM [Sheet1$]\r\n"; tbxSql.Location = new Point(10, 10); tbxSql.Size = new Size(240, 200); tbxSql.Multiline = true; tbxSql.ScrollBars = ScrollBars.Both; tbxSql.AcceptsReturn = true; tbxSql.WordWrap = true; tbxSql.Anchor = (AnchorStyles.Top | AnchorStyles.Left); tbxMsg = new TextBox(); tbxMsg.Location = new Point(10, 220); tbxMsg.Size = new Size(240, 190); tbxMsg.Multiline = true; tbxMsg.ScrollBars = ScrollBars.Both; tbxMsg.AcceptsReturn = true; tbxMsg.WordWrap = true; tbxMsg.Anchor = (AnchorStyles.Top | AnchorStyles.Bottom | AnchorStyles.Left); dgOut = new DataGrid(); dgOut.Location = new Point(260, 10); dgOut.Size = new Size(450, 436); dgOut.CaptionVisible = false; dgOut.ReadOnly = true; dgOut.Anchor = (AnchorStyles.Top | AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right); Controls.AddRange(new Control[]{btnSubmit, chkStru, lblTable, tbxSql, tbxMsg, tbxConn, dgOut}); Text = "数据库查询(OLEDB)"; ClientSize = new Size(720, 490); WindowState = FormWindowState.Maximized; ResumeLayout(false); }
void DisplayError(Exception ex) { StringBuilder sb = new StringBuilder(); while (ex != null) { sb.Append("> "); sb.Append(ex.GetType()); sb.Append(Environment.NewLine); OleDbException e = ex as OleDbException; if (e != null) { for (int i = 0; i < e.Errors.Count; i++) sb.AppendFormat( "Index: {1}{0}Message: {2}{0}NativeError: {3}{0}Source: {4}{0}SQLState: {5}{0}", Environment.NewLine, i, e.Errors[i].Message, e.Errors[i].NativeError, e.Errors[i].Source, e.Errors[i].SQLState ); } else sb.Append(ex.Message); sb.Append(Environment.NewLine); ex = ex.InnerException; } tbxMsg.Text = sb.ToString(); } void Submit_Click(object sender, EventArgs e) { btnSubmit.Enabled = false; string sql = tbxSql.Text.Trim(); if (sql.Length == 0) return; try { int rows = -2; string strType = "查询"; using (OleDbConnection conn = new OleDbConnection(tbxConn.Text)) { conn.Open(); OleDbCommand comm = new OleDbCommand(sql, conn); if (!isQuery(sql)) { strType = "非查询"; rows = comm.ExecuteNonQuery(); } else if (chkStru.Checked) { strType = "表结构"; dgOut.DataSource = RunQueryTableStruct(comm); } else dgOut.DataSource = RunQueryTableData(comm); } tbxMsg.Text = "运行 SQL 语句完毕(" + strType + ")"; if (rows >= 0) tbxMsg.Text = "受影响的行数: " + rows.ToString("N0"); } catch (Exception ex) { DisplayError(ex); } btnSubmit.Enabled = true; }
string GetBriefType(object obj) { string s = (obj as Type).ToString(); if (string.CompareOrdinal(s, 0, "System.", 0, 7) == 0) s = s.Substring(7); return s; } static void Main() { Application.Run(new OleDbDlg()); } } }
要做个强大一点的,还是自己写一个类似richtextBox的控件才是王道,带提示,带高亮关键字等
你这样的话,只能执行一条命令,如果多条或者是使用了 use Testselect * from tab1这样的话处理就会很麻烦,我的意思是能不能够有执行一段SQL命令的方式,像SQL查询分析器一样,而且会返回一个DataSet对象,再返回一个输出参数代表返回的消息.这样就算有更新或查询就能够得到返回消息
上面一个文本框richtextBox,下面一个表格,通过执行richtextBox里面的SQL语句
在下面反馈信息
只是为了在应用方便
数据库小工具(C#)
using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Drawing;
namespace Skyiv.Util.Oledb
{
class OleDbDlg : Form
{
Label lblTable;
TextBox tbxConn;
TextBox tbxSql;
TextBox tbxMsg;
Button btnSubmit;
CheckBox chkStru;
DataGrid dgOut;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=yes\";Data Source=Test.xls";
public OleDbDlg()
{
SuspendLayout();
btnSubmit = new Button();
btnSubmit.Text = "执行";
btnSubmit.Location = new Point(10, 420);
btnSubmit.Size = new Size(60, 24);
btnSubmit.Click += new EventHandler(Submit_Click);
btnSubmit.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
chkStru = new CheckBox();
chkStru.Text = "结构";
chkStru.Location = new Point(80, 420);
chkStru.Size = new Size(60, 24);
chkStru.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
lblTable = new Label();
lblTable.Text = "数据源";
lblTable.Location = new Point(12, 460);
lblTable.Size = new Size(70, 24);
lblTable.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
tbxConn = new TextBox();
tbxConn.Text = strConn;
tbxConn.Location = new Point(83, 456);
tbxConn.Size = new Size(626, 20);
tbxConn.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right);
tbxSql = new TextBox();
tbxSql.Text = "SELECT *\r\nFROM [Sheet1$]\r\n";
tbxSql.Location = new Point(10, 10);
tbxSql.Size = new Size(240, 200);
tbxSql.Multiline = true;
tbxSql.ScrollBars = ScrollBars.Both;
tbxSql.AcceptsReturn = true;
tbxSql.WordWrap = true;
tbxSql.Anchor = (AnchorStyles.Top | AnchorStyles.Left);
tbxMsg = new TextBox();
tbxMsg.Location = new Point(10, 220);
tbxMsg.Size = new Size(240, 190);
tbxMsg.Multiline = true;
tbxMsg.ScrollBars = ScrollBars.Both;
tbxMsg.AcceptsReturn = true;
tbxMsg.WordWrap = true;
tbxMsg.Anchor = (AnchorStyles.Top | AnchorStyles.Bottom | AnchorStyles.Left);
dgOut = new DataGrid();
dgOut.Location = new Point(260, 10);
dgOut.Size = new Size(450, 436);
dgOut.CaptionVisible = false;
dgOut.ReadOnly = true;
dgOut.Anchor = (AnchorStyles.Top | AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right);
Controls.AddRange(new Control[]{btnSubmit, chkStru, lblTable, tbxSql, tbxMsg, tbxConn, dgOut});
Text = "数据库查询(OLEDB)";
ClientSize = new Size(720, 490);
WindowState = FormWindowState.Maximized;
ResumeLayout(false);
}
void DisplayError(Exception ex)
{
StringBuilder sb = new StringBuilder();
while (ex != null)
{
sb.Append("> ");
sb.Append(ex.GetType());
sb.Append(Environment.NewLine);
OleDbException e = ex as OleDbException;
if (e != null)
{
for (int i = 0; i < e.Errors.Count; i++) sb.AppendFormat(
"Index: {1}{0}Message: {2}{0}NativeError: {3}{0}Source: {4}{0}SQLState: {5}{0}", Environment.NewLine,
i, e.Errors[i].Message, e.Errors[i].NativeError, e.Errors[i].Source, e.Errors[i].SQLState
);
}
else sb.Append(ex.Message);
sb.Append(Environment.NewLine);
ex = ex.InnerException;
}
tbxMsg.Text = sb.ToString();
}
void Submit_Click(object sender, EventArgs e)
{
btnSubmit.Enabled = false;
string sql = tbxSql.Text.Trim();
if (sql.Length == 0) return;
try
{
int rows = -2;
string strType = "查询";
using (OleDbConnection conn = new OleDbConnection(tbxConn.Text))
{
conn.Open();
OleDbCommand comm = new OleDbCommand(sql, conn);
if (!isQuery(sql))
{
strType = "非查询";
rows = comm.ExecuteNonQuery();
}
else if (chkStru.Checked)
{
strType = "表结构";
dgOut.DataSource = RunQueryTableStruct(comm);
}
else dgOut.DataSource = RunQueryTableData(comm);
}
tbxMsg.Text = "运行 SQL 语句完毕(" + strType + ")";
if (rows >= 0) tbxMsg.Text = "受影响的行数: " + rows.ToString("N0");
}
catch (Exception ex)
{
DisplayError(ex);
}
btnSubmit.Enabled = true;
}
bool isQuery(string sql)
{
return sql.Substring(0, 6).ToUpper() == "SELECT";
}
private DataView RunQueryTableData(OleDbCommand comm)
{
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].DefaultView;
}
private DataView RunQueryTableStruct(OleDbCommand comm)
{
DataTable dt = new DataTable();
dt.Columns.Add("#", typeof(int));
dt.Columns.Add("字段名", typeof(string));
dt.Columns.Add("数据类型", typeof(string));
dt.Columns.Add("源数据类型", typeof(string));
dt.Columns.Add("大小", typeof(string));
dt.Columns.Add("备注", typeof(string));
using (OleDbDataReader r = comm.ExecuteReader(CommandBehavior.KeyInfo))
{
DataTable dt0 = r.GetSchemaTable();
//return dt0.DefaultView;
foreach (DataRow dr0 in dt0.Rows)
{
DataRow dr = dt.NewRow();
dr[0] = (int)dr0["ColumnOrdinal"];
dr[1] = (string)dr0["ColumnName"];
dr[2] = GetBriefType(dr0["DataType"]);
dr[3] = ((OleDbType)dr0["ProviderType"]).ToString();
dr[4] = string.Format(
"({0},{1}) {2}", (short)dr0["NumericPrecision"], (short)dr0["NumericScale"], (int)dr0["ColumnSize"]
);
dr[5] = string.Format(
"{0}{1}{2}{3}{4}{5}{6}",
(bool)dr0["AllowDBNull" ] ? "AllowDBNull " : "",
(bool)dr0["IsKey" ] ? "Key " : "",
(bool)dr0["IsAutoIncrement"] ? "AutoIncrement " : "",
(bool)dr0["IsUnique" ] ? "Unique " : "",
(bool)dr0["IsRowVersion" ] ? "RowVersion " : "",
(bool)dr0["IsLong" ] ? "Long " : "",
(bool)dr0["IsReadOnly" ] ? "ReadOnly " : ""
);
dt.Rows.Add(dr);
}
}
return dt.DefaultView;
}
string GetBriefType(object obj)
{
string s = (obj as Type).ToString();
if (string.CompareOrdinal(s, 0, "System.", 0, 7) == 0) s = s.Substring(7);
return s;
}
static void Main()
{
Application.Run(new OleDbDlg());
}
}
}
use Testselect * from tab1这样的话处理就会很麻烦,我的意思是能不能够有执行一段SQL命令的方式,像SQL查询分析器一样,而且会返回一个DataSet对象,再返回一个输出参数代表返回的消息.这样就算有更新或查询就能够得到返回消息