提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
新手学习数据库操作
一、使用步骤
1.引入库
引入MySql.Data.MySqlClient命名空间
代码如下(示例):using MySql.Data.MySqlClient;
2.代码
代码如下
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
namespace MySQLForm
{
internal class SQLBase
{
private MySqlCommand MyCMD = null;
private MySqlConnection MyConn = null;
private MySqlDataReader MyDataReader = null;
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="connstr"> </param>
public SQLBase(string connstr)
{
MyConn = new MySqlConnection(connstr);
}
//public long MyProperty
//{
// get { return MyCMD.ExecuteNonQuery(); }
// }
/// <summary>
/// 检查数据库连接状态
/// </summary>
/// <returns> </returns>
public bool CheckConnectionStatus()
{
bool result = false;
try
{
MyConn.Open();
if (MyConn.State == System.Data.ConnectionState.Open)
{
result = true;
}
}
catch
{
result = false;
}
finally { MyConn.Close(); }
return result;
}
/// <summary>
/// 增删改 方法一样
/// </summary>
/// <param name="sql"> </param>
/// <returns> </returns>
public int commonExecute(string sql)
{
int res = -1;
try
{
if (MyConn.State == System.Data.ConnectionState.Open)
{
MyConn.Close();
}
MyConn.Open();//必须打开数据库才可以进行相应操作
MyCMD = new MySqlCommand(sql, MyConn);
res = MyCMD.ExecuteNonQuery();//成功返回相应数字
}
catch (Exception)
{
}
MyConn.Close();
return res;
}
public bool ExecuteSqlTran(List<string> SQLStringList)
{
bool flag = false;
if (MyConn.State == ConnectionState.Open)
{
MyConn.Close();
}
MyConn.Open();
MySqlCommand cmd = MyConn.CreateCommand();
MySqlTransaction transaction = MyConn.BeginTransaction();
cmd.Transaction = transaction;
try
{
foreach (string sqlStr in SQLStringList)
{
if (sqlStr.Trim() != "")
{
cmd.CommandText = sqlStr;
cmd.ExecuteNonQuery();
}
}
transaction.Commit();//提交事务
flag = true;
}
catch (Exception)
{
transaction.Rollback();
flag = false;
}
finally { MyConn.Close(); }
return flag;
}
/// <summary>
/// 获取dataset数据集
/// </summary>
/// <param name="sql"> </param>
/// <param name="table"> </param>
/// <returns> </returns>
public DataSet GetDataSet(string sql, string table)
{
if (MyConn.State == ConnectionState.Open)
{
MyConn.Close();
}
MyConn.Open();
MyCMD = new MySqlCommand(sql, MyConn);
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(MyCMD);
adapter.Fill(ds, table);
MyConn.Close();
return ds;
}
/// <summary>
/// 查询方法
/// </summary>
/// <param name="sql"> </param>
/// <returns> </returns>
public DataTable query(string sql)
{
DataTable dt = new DataTable();
if (MyConn.State == ConnectionState.Open)
{
MyConn.Close();
}
MyConn.Open();
MyCMD = new MySqlCommand(sql, MyConn);
using (MyDataReader = MyCMD.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(MyDataReader);
}
return dt;
}
}
}