实例介绍
【实例简介】
基于SQL语句新建，添加，查找，等操作
【实例截图】
【核心代码】
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using ADOX;
using System.Collections;
using System.IO;
namespace Access数据库
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
#region //access调用方法
//创建mdb
public bool CreateMDBDataBase(string mdbPath)
{
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";");
cat = null;
return true;
}
catch { return false; }
}
// 新建mdb的表
//mdbHead是一个ArrayList，存储的是table表中的具体列名。
public static bool CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)
{
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
string sAccessConnection
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath;
ADODB.Connection cn = new ADODB.Connection();
cn.Open(sAccessConnection, null, null, -1);
cat.ActiveConnection = cn;
//新建一个表
ADOX.TableClass tbl = new ADOX.TableClass();
tbl.ParentCatalog = cat;
tbl.Name = tableName;
int size = mdbHead.Count;
for (int i = 0; i < size; i )
{
//增加一个文本字段
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
col2.ParentCatalog = cat;
col2.Name = mdbHead[i].ToString();//列的名称
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);
}
cat.Tables.Append(tbl); //这句把表加入数据库(非常重要)
tbl = null;
cat = null;
cn.Close();
return true;
}
catch { return false; }
}
//读取mdb文件内数据表个数
public string[] GetShemaTableName(string mdbPath)
{
try
{
//获取数据表
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath;
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();
DataTable shemaTable = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, new object[]{ null, null, null, "TABLE" });
int n = shemaTable.Rows.Count;
string[] strTable = new string[n];
int m = shemaTable.Columns.IndexOf("TABLE_NAME");
for (int i = 0; i < n; i )
{
DataRow m_DataRow = shemaTable.Rows[i];
strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString();
}
return strTable;
}
catch (OleDbException ex)
{
MessageBox.Show("指定的限制集无效:\n" ex.Message);
return null;
}
}
//读取表内列个数
public string[] GetShemaColName(string mdbPath,string tableName)
{
try
{
//获取数据表
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath;
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();
DataTable shemaTable = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
//foreach (DataRow dr in shemaTable.Rows)
//{
// string s=dr["TABLE_NAME"].ToString();
//}
//获取表内列数
int n = shemaTable.Rows.Count;
string[] strTable = new string[n];
int m = shemaTable.Columns.IndexOf("COLUMN_NAME");
for (int i = 0; i < n; i )
{
DataRow m_DataRow = shemaTable.Rows[i];
strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString();
}
return strTable;
}
catch (OleDbException ex)
{
MessageBox.Show("指定的限制集无效:\n" ex.Message);
return null;
}
}
// 读取mdb数据
public static DataTable ReadAllData(string tableName, string mdbPath, ref bool success)
{
DataTable dt = new DataTable();
try
{
DataRow dr;
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommand odCommand = odcConnection.CreateCommand();
//3、输入查询语句
odCommand.CommandText = "select * from " tableName;
//建立读取
OleDbDataReader odrReader = odCommand.ExecuteReader();
//查询并显示数据
int size = odrReader.FieldCount;
for (int i = 0; i < size; i )
{
DataColumn dc;
dc = new DataColumn(odrReader.GetName(i));
dt.Columns.Add(dc);
}
while (odrReader.Read())
{
dr = dt.NewRow();
for (int i = 0; i < size; i )
{
dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
}
dt.Rows.Add(dr);
}
//关闭连接
odrReader.Close();
odcConnection.Close();
success = true;
return dt;
}
catch
{
success = false;
return dt;
}
}
// 读取mdb数据 (按列)
public static DataTable ReadDataByColumns(string mdbPath, string tableName, string[] columns, ref bool success)
{
DataTable dt = new DataTable();
try
{
DataRow dr;
//1、建立连接
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
//建立SQL查询
OleDbCommand odCommand = odcConnection.CreateCommand();
//3、输入查询语句
string strColumn = "";
for (int i = 0; i < columns.Length; i )
{
strColumn = columns[i].ToString() ",";
}
strColumn = strColumn.TrimEnd(',');
odCommand.CommandText = "select " strColumn " from " tableName;
//建立读取
OleDbDataReader odrReader = odCommand.ExecuteReader();
//查询并显示数据
int size = odrReader.FieldCount;
for (int i = 0; i < size; i )
{
DataColumn dc;
dc = new DataColumn(odrReader.GetName(i));
dt.Columns.Add(dc);
}
while (odrReader.Read())
{
dr = dt.NewRow();
for (int i = 0; i < size; i )
{
dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
}
dt.Rows.Add(dr);
}
//关闭连接
odrReader.Close();
odcConnection.Close();
success = true;
return dt;
}
catch
{
success = false;
return dt;
}
}
//普通的节点
public struct Node
{
private string nodeType;
public string NodeType//表的字段名
{
set { nodeType = value; }
get { return nodeType; }
}
private string nodeValue;
public string NodeValue//具体的值
{
set { nodeValue = value; }
get { return nodeValue; }
}
}
//照片节点
public struct PictureNode
{
private string nodeType;
public string NodeType//照片的列名
{
set { nodeType = value; }
get { return nodeType; }
}
private byte[] nodeValue;
public byte[] NodeValue//照片的值，注意类型
{
set { nodeValue = value; }
get { return nodeValue; }
}
}
/* //另外的写法有错误
//插入数据
public static bool InsertRow(string mdbPath, string tableName, ArrayList insertArray,
PictureNode pictureNode, ref string errinfo)
{
try
{
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
string str_col = "";
int size_col = insertArray.Count;
for (int i = 0; i < size_col; i )
{
Node vipNode = new Node();
vipNode = (Node)insertArray[i];
str_col = vipNode.NodeType ",";
}
str_col = str_col.TrimEnd(',');
int size_row = insertArray.Count;
string str_row = "";
for (int i = 0; i < size_row; i )
{
Node vipNode = new Node();
vipNode = (Node)insertArray[i];
string v = vipNode.NodeValue.ToString();
v = DealString(v);
if (v == "")
{
str_row = "null" ',';
}
else
{
str_row = "'" v "'" ',';
}
}
str_row = str_row.TrimEnd(',');
if (pictureNode != null && pictureNode.NodeValue != null)
{
str_col = ',' pictureNode.NodeType;
str_row = ",@Image";
}
string sql = "insert into " tableName @" (" str_col ") values" @"(" str_row ")";
OleDbCommand odCommand = new OleDbCommand(sql, odcConnection);
if (pictureNode != null && pictureNode.NodeValue != null)
{
odCommand.Parameters.Add("@Image", OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue;
}
odCommand.ExecuteNonQuery();
odcConnection.Close();
return true;
}
catch (Exception err)
{
errinfo = err.Message;
return false;
}
}
//更新一行数据
public static bool UpdateRow(string mdbPath, string tableName,
Node keyNode, ArrayList insertArray, PictureNode pictureNode, ref string errinfo)
{
try
{
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
int size = insertArray.Count;
string str = "";
for (int i = 0; i < size; i )
{
Node node = new Node();
node = (Node)insertArray[i];
string v = node.NodeValue.ToString();
v = DealString(v);
str = node.NodeType " = ";
if (v == "")
{
str = "null" ',';
}
else
{
str = "'" v "'" ',';
}
}
str = str.TrimEnd(',');
if (pictureNode.NodeValue != null)
{
str = ',' pictureNode.NodeType;
str = " = @Image";
}
string sql = "update " tableName " set " str
" where " keyNode.NodeType " = " "'" keyNode.NodeValue "'";
OleDbCommand odCommand = new OleDbCommand(sql, odcConnection);
if (pictureNode.NodeValue != null)
{
odCommand.Parameters.Add("@Image", OleDbType.VarBinary, pictureNode.NodeValue.Length).Value = pictureNode.NodeValue;
}
odCommand.ExecuteNonQuery();
odcConnection.Close();
return true;
}
catch (Exception err)
{
errinfo = err.Message;
return false;
}
}
*/
//插入图片数据
public static bool InsertPictureToMDB(string mdbPath, string tableName, Node keyNode,PictureNode pictureNode, ref string errinfo)
{
try
{
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
string sql = @"update " tableName " set " pictureNode.NodeType "="
"@Image where " keyNode.NodeType "=" "'" keyNode.NodeValue "'";
OleDbCommand comm = new OleDbCommand(sql, odcConnection);
byte[] pic = pictureNode.NodeValue;
comm.Parameters.Add("@Image", OleDbType.VarBinary, pic.Length).Value = pic;
comm.ExecuteNonQuery();
odcConnection.Close();
return true;
}
catch (Exception err)
{
errinfo = err.Message;
return false;
}
}
//从mdb中获得照片
public static byte[] GetImageFromMDB(string tableName,string pictureCol, Node keyNode)
{
try
{
string sql = "Select " pictureCol " From " tableName
" member Where " keyNode.NodeType " = "
"'" keyNode.NodeValue "'";
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
OleDbCommand comm = new OleDbCommand(sql, odcConnection);
OleDbDataReader sdr = comm.ExecuteReader();
sdr.Read();
byte[] pic = (byte[])sdr[0];
sdr.Close();
odcConnection.Close();
return pic;
}
catch
{
return null;
}
}
//修改mdb的一条数据
public static bool UpdateMDBNode(string mdbPath, string tableName, Node keyNode, Node saveNode, ref string errinfo)
{
try
{
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
string sql = @"update " tableName " set " saveNode.NodeType " = '" saveNode.NodeValue
"' where " keyNode.NodeType " = " "'" keyNode.NodeValue "'";
OleDbCommand comm = new OleDbCommand(sql, odcConnection);
comm.ExecuteNonQuery();
odcConnection.Close();
return true;
}
catch (Exception err)
{
errinfo = err.Message;
return false;
}
}
//插入数据
public static bool InsertRow(string mdbPath, string tableName, string[] colArray, string[] dataArray, ref string errinfo)
{
try
{
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
string col="";
string data="";
for (int i = 0; i < colArray.Length; i )
{
if (i==colArray.Length-1)
{
col = col colArray[i];
}
else
{
col = col colArray[i] ",";
}
}
for (int i = 0; i < colArray.Length; i )
{
if (i == colArray.Length - 1)
{
data = data "'" dataArray[i] "'";
}
else
{
data = data "'" dataArray[i] "'" ",";
}
}
string sql = "insert into " tableName " (" col ") values (" data ")";
OleDbCommand comm = new OleDbCommand(sql, odcConnection);
comm.ExecuteNonQuery();
odcConnection.Close();
return true;
}
catch
{
return false;
}
}
//删除数据
public static bool DeleteData(string mdbPath, string tableName, Node keyNode, ref string errinfo)
{
try
{
//1、建立连接
string strConn
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" mdbPath ";Jet OLEDB:Database Password=haoren";
OleDbConnection odcConnection = new OleDbConnection(strConn);
//2、打开连接
odcConnection.Open();
string col=keyNode.NodeType;
string value=keyNode.NodeValue;
string sql = "delete from " tableName " where " col "='" value "'";
//string sql = "delete from " tableName;
OleDbCommand comm = new OleDbCommand(sql, odcConnection);
comm.ExecuteNonQuery();
odcConnection.Close();
return true;
}
catch
{
return false;
}
}
#endregion
# region
static string mdbPath ="";
static string tableName = "";
static string[] strCol=new string[4];
private void button2_Click(object sender, EventArgs e)
{
//选择创建路径
saveFileDialog1.InitialDirectory = Environment.CurrentDirectory "\\..\\..\\..\\";
saveFileDialog1.ShowDialog();
textBox1.Text = saveFileDialog1.FileName;
mdbPath = textBox1.Text;
CreateMDBDataBase(mdbPath);
}
private void button3_Click(object sender, EventArgs e)
{
tableName = textBox3.Text;
int col =Convert .ToInt32( numericUpDown1.Value);
ArrayList a = new ArrayList(col);
for (int i = 0; i < col; i )
{
a.Add(strCol[i]);
}
CreateMDBTable(mdbPath, tableName, a);
}
private void button4_Click(object sender, EventArgs e)
{
tableName = comboBox2.Text;
bool bo1=false;
DataTable dataTable=new DataTable();
dataTable = ReadAllData(tableName, mdbPath, ref bo1);
dataGridView1.DataSource = dataTable;
}
private void button5_Click(object sender, EventArgs e)
{
tableName = comboBox2.Text;
DataTable dataTable=new DataTable();
bool bo1=false;
string[] columns={comboBox1.Text};
dataTable= ReadDataByColumns( mdbPath, tableName, columns, ref bo1);
dataGridView1.DataSource = dataTable;
}
private void button6_Click(object sender, EventArgs e)
{
string bol = "";
Node a=new Node();
Node b = new Node();
a.NodeType=textBox8.Text;
a.NodeValue=textBox9.Text;
b.NodeType = textBox8.Text;
b.NodeValue=textBox10.Text;
UpdateMDBNode(mdbPath, tableName, a, b, ref bol);
}
private void button7_Click(object sender, EventArgs e)
{
string bol = "";
string[] colArray = GetShemaColName(mdbPath, tableName);
string[] dataArray = { textBox5.Text, textBox6.Text, textBox7.Text };
InsertRow(mdbPath, tableName, colArray,dataArray, ref bol);
}
private void button9_Click(object sender, EventArgs e)
{
string bol = "";
Node a = new Node();
a.NodeType = textBox11.Text;
a.NodeValue = textBox12.Text;
DeleteData (mdbPath, tableName, a, ref bol);
}
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.InitialDirectory = Environment.CurrentDirectory "\\..\\..\\..\\";
openFileDialog1.ShowDialog();
textBox1.Text = openFileDialog1.FileName;
mdbPath = textBox1.Text;
string[] strA = GetShemaTableName(mdbPath);
textBox2.Text = strA.Length.ToString();
for (int i = 0; i < strA.Length; i )
{
comboBox2.Items.Add(strA[i ]);
}
}
private void textBox4_Leave(object sender, EventArgs e)
{
int col = Convert.ToInt32(numericUpDown2.Value);
strCol[col] = textBox4.Text;
}
private void comboBox1_Enter(object sender, EventArgs e)
{
if (comboBox1.Text=="")
{
//MessageBox.Show("");
string[] strA = GetShemaColName(mdbPath, tableName);
for (int i = 0; i < strA.Length; i )
{
comboBox1.Items.Add(strA[i]);
}
}
}
private void comboBox2_Leave(object sender, EventArgs e)
{
tableName = comboBox2.Text;
}
#endregion
private void button8_Click(object sender, EventArgs e)
{
Node keyNode = new Node();
PictureNode pictureNode = new PictureNode();
string errinfo = "";
FileStream myStream = new FileStream(openPicture.FileName, FileMode.Open, FileAccess.Read);
byte[] imagebytes = new byte[myStream.Length];//fs.Length文件流的长度，用字节表示
BinaryReader br = new BinaryReader(myStream);//二进制文件读取器
imagebytes = br.ReadBytes(Convert.ToInt32(myStream.Length));//从当前流中将count个字节读入字节数组中
myStream.Close();
keyNode.NodeType = textBox15.Text;
keyNode.NodeValue = textBox16.Text;
pictureNode.NodeType = textBox13.Text;
pictureNode.NodeValue = imagebytes;
InsertPictureToMDB(mdbPath, tableName, keyNode, pictureNode,ref errinfo);
}
OpenFileDialog openPicture = new OpenFileDialog();
private void textBox14_Enter(object sender, EventArgs e)
{
openPicture.Filter = "Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.* ";
openPicture.ShowDialog();
textBox14.Text = openPicture.FileName;
//openPicture.OpenFile();
}
private void button10_Click(object sender, EventArgs e)
{
Node keyNode = new Node();
keyNode.NodeType = textBox15.Text;
keyNode.NodeValue = textBox16.Text;
string pictureCol = textBox13.Text;
byte[] pictureByt= GetImageFromMDB(tableName, pictureCol, keyNode);
MemoryStream stream = new MemoryStream(pictureByt, true);
stream.Write(pictureByt, 0, pictureByt.Length);
pictureBox1.Image = new Bitmap(stream); stream.Close();
}
}
}
好例子网口号：伸出你的我的手 — 分享！
网友评论
小贴士
感谢您为本站写下的评论，您的评论对其它用户来说具有重要的参考价值，所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字，对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙，所以请不要反馈意义不大的重复字符，也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明，或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦，又没人会搭理，于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享，对搜索内容的合法性不具有预见性、识别性、控制性，仅供学习研究，请务必在下载后24小时内给予删除，不得用于其他任何用途，否则后果自负。基于互联网的特殊性，平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查；无论平台是否已进行审查，用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场，基于网友分享，根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定，若资源存在侵权或相关问题请联系本站客服人员，点此联系我们。关于更多版权及免责申明参见 版权及免责申明
支持(0) 盖楼(回复)