c#与数据库进行连接
程序员文章站
2024-03-17 20:04:10
...
先要在vs和mysql-front上创建表格，例如：
再创建读写类和帮助数据连接的类。注意：要到引用里添加个MySQL.data引用，不然不能使用
public class mysqlReadWriteClass
{
public string conStr;//连接字符串的路径
private static MySql.Data.MySqlClient.MySqlConnection DbConn = null;
/// <summary>
/// 根据传入数据库的绝对路径，执行构造函数并初始化连接字符串。
/// </summary>
/// <param name="str">传入数据库的绝对路径</param>
public mysqlReadWriteClass(string str)
{
if (DbConn != null)
return;
conStr = "Database=" + str + "; User ID=root;Password=xxxxx; Host=localhost; Port= 3306;Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0;Charset=utf8";
DbConn = new MySqlConnection(conStr);//定义新的数据连接控件并初始化
}
public mysqlReadWriteClass()
{
if (DbConn != null)
return;
string str = Application.StartupPath;
conStr = "Database=tempdatabase; User ID=root;Password=xxxxx; Host=localhost; Port= 3306;Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0;Charset=utf8";
DbConn = new MySqlConnection(conStr);//定义新的数据连接控件并初始化
}
/// <summary>
/// 根据传入的表格名称读出所有的记录
/// </summary>
/// <param name="tableName">传入表格的名称</param>
/// <returns>返回表格</returns>
public void readData(string tableName, ref DataGridView dgv)
{
DataTable dt = new DataTable();
dt = getTable("select * from " + tableName);
dgv.DataSource = dt;
}
/// <summary>
/// 根据传入的sql字符串读出表中的记录
/// </summary>
/// <param name="strSql">传入读取的sql字符串</param>
/// <returns>返回表格</returns>
public DataTable getTable(string strSql)
{
DataTable dd = new DataTable();//创建一个数据集dd
MySqlDataAdapter adapter = new MySqlDataAdapter(strSql, DbConn);//定义并初始化数据适配器
try
{
DbConn.Open();//打开连接
adapter.Fill(dd); //将数据适配器中的数据填充到数据集dd中
}
catch (Exception ee)
{
DbConn.Close();//关闭连接
adapter.Dispose();
MessageBox.Show(ee.Message);
return null;
}
DbConn.Close();//关闭连接
return dd;
}
/// <summary>
/// 根据传入的sql字符串读出表中的记录
/// </summary>
/// <param name="strSql">传入读取的sql字符串</param>
/// <returns>返回表格</returns>
public string getValue(string strSql)
{
string retstr = "";
DataTable dt = new DataTable();//创建一个数据集dd
dt = getTable(strSql);
if (dt.Rows.Count > 0)
{
retstr = dt.Rows[0][0].ToString();
}
return retstr;
}
/// <summary>
/// 根据传入的sql语句执行
/// </summary>
/// <param name="strSql">传入的要执行的sql语句</param>
public string execSql(string strSql)
{
MySqlCommand comm = new MySqlCommand(strSql, DbConn);//定义并初始化命令对象
try //容错功能
{
DbConn.Open();//打开连接
comm.ExecuteNonQuery();//执行命令
}
catch (Exception ee)
{
DbConn.Close();//关闭连接
comm.Dispose();
MessageBox.Show(ee.Message);
return ee.Message;
}
DbConn.Close();//关闭连接
return "OK";
}
}
public class DbHelperMySQL
{
//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
//public static string connectionString = "User ID=admin;Database=ronghetongxin_case; Password=1; Host=www.zly123.cn; Port= 3306;Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0;Charset=utf8";//PubConstant.ConnectionString; ConfigurationManager.AppSettings["ConnectionString"]
public static string connectionString = "User ID=root;Database=tempdatabase; Password=xxxxx; Host=localhost; Port= 3306;Protocol=TCP; Compress=false; Pooling=true; Min Pool Size=0;Max Pool Size=100; Connection Lifetime=0;Charset=utf8 ";//PubConstant.ConnectionString; ConfigurationManager.AppSettings["ConnectionString"]
internal MySqlConnection connection = null;
public static int createconnectnum = 0;
//public static string connectionString = PubConstant.ConnectionString;
public DbHelperMySQL()
{
if (connection == null)
connection = new MySqlConnection(connectionString);
}
#region 执行简单SQL语句
public int ExecuteSql(string SQLString)
{
int retnum = 0;
try
{
//Log.Debug("1__ExecuteSql2___", SQLString);
connection.Open();
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
cmd.CommandTimeout = 50;
retnum = cmd.ExecuteNonQuery();
cmd.Dispose();
connection.Close(); //ljx 2017-8-3
return retnum;
}
catch (Exception e)
{
connection.Close(); //ljx 2017-8-3
WriteLogFile(e.Message + e.StackTrace + SQLString + " -->> " + connectionString, "DbHelperMySQL", "ExecuteSql", "Db_001", "", "", "");
//throw e;
}
return retnum;//将执行结果返回。
}
public int ExecuteSqlByTime(string SQLString, int Times)
{
connection.Open();
//using (MySqlConnection connection = new MySqlConnection(connectionString))
//{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
connection.Close();
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
connection.Close();
//throw e;
WriteLogFile(ex.Message + ex.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "DbHelperMySQL", "ExecuteSqlByTime", "Db_002", "", "", "");
return 0;
}
}
//}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, string content)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
connection.Close(); //ljx 2017-8-3
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close(); //ljx 2017-8-3
WriteLogFile(e.Message + e.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "DbHelperMySQL", " ExecuteSql..content ", "Db_006", "", "", "");
return 0;
}
finally
{
cmd.Dispose();
// connection.Close(); //ljx 2017-8-3
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public object ExecuteSqlGet(string SQLString, string content)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
cmd.CommandTimeout = 50;
cmd.Parameters.AddWithValue("@content", Encoding.UTF8.GetBytes(content));//model.报文内容;
//cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
connection.Close(); //ljx 2017-8-3
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close(); //ljx 2017-8-3
//Log.Debug("error sqlstr : ",e.Message + e.StackTrace + " -->> " + SQLString + " -->> " + connectionString);
//WriteLogFile(e.Message + e.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "ExecuteSqlGet", "ExecuteSqlByTime", "Db_002", "", "", "");
}
finally
{
cmd.Dispose();
// connection.Close(); //ljx 2017-8-3
}
return null;
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(strSQL, connection);
MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
connection.Close(); //ljx 2017-8-3
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close(); //ljx 2017-8-3
WriteLogFile(e.Message + e.StackTrace + " -->> " + strSQL + " -->> " + connectionString, "", "ExecuteSqlGet", "Db_002", "", "", "");
return 0;
}
finally
{
cmd.Dispose();
// connection.Close(); //ljx 2017-8-3
}
}
}
public DataTable GetDataTable(string SQLString)
{
return Query(SQLString).Tables[0];
}
private DataSet Query(string SQLString)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
//MySqlCommand cmd = new MySqlCommand();
{
MySqlDataAdapter command;
DataSet ds = new DataSet();
try
{
connection.Open();
command = new MySqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = 50;
command.Fill(ds, "ds");
command.Dispose();
//cmd.Dispose();
connection.Close(); //ljx 2017-8-3
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
connection.Close(); //ljx 2017-8-3
WriteLogFile(ex.Message + ex.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "DbHelperMySQL", "Query", "Db_003", "", "", "");
return null;
}
return ds;
}
}
public DataSet Query(string SQLString, int Times)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = Times;
command.Fill(ds, "ds");
connection.Close(); //ljx 2017-8-3
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
connection.Close();
WriteLogFile(ex.Message + ex.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "DbHelperMySQL", "Query", "Db_004", "", "", "");
}
return ds;
}
}
public string getvalue(string SQLString)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string str = "";
DataSet ds = new DataSet();
try
{
connection.Open();
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
connection.Close(); //ljx 2017-8-3
}
catch (Exception ex)
{
connection.Close();
WriteLogFile(ex.Message + ex.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "DbHelperMySQL", "getvalue ", "Db_005", "", "", "");
return str;
}
if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
str = ds.Tables[0].Rows[0][0].ToString();
return str;
}
}
#endregion
#region 执行带参数的SQL语句
private int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
{
{
try
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
connection.Open();
int rows = cmd.ExecuteNonQuery();
connection.Close(); //ljx 2017-8-3
cmd.Parameters.Clear();
cmd.Dispose();
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
//cmd.Dispose();
// connection.Close(); //ljx 2017-8-3
WriteLogFile(e.Message + e.StackTrace + " -->> " + SQLString + " -->> " + connectionString, "DbHelperMySQL", "ExecuteSql..cmdParms ", "Db_005", "", "", "");
return 0;
}
}
}
/// <summary>
/// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>MySqlDataReader</returns>
public MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
{
//MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand();
try
{
connection.Open();
PrepareCommand(ref cmd, connection, null, SQLString, cmdParms);
MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
connection.Close(); //ljx 2017-8-3
return myReader;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();
WriteLogFile(e.Message + e.StackTrace + " -->> " + SQLString + " -->> " + connection.ConnectionString, "DbHelperMySQL", "ExecuteReader ", "Db_005", "", "", "");
return null;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
{
//using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(ref cmd, connection, null, SQLString, cmdParms);
cmd.CommandTimeout = 30;
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
connection.Open();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
connection.Close(); //ljx 2017-8-3
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
connection.Close();
WriteLogFile(ex.Message + ex.StackTrace + " -->> " + SQLString + " -->> " + connection.ConnectionString, "DbHelperMySQL", "Query.. cmdParms ", "Db_005", "", "", "");
return null;
}
return ds;
}
}
}
private void PrepareCommand(ref MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (MySqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
#endregion
private static void WriteLogFile(string contetnstr, string LogType, string OperateType, string ExceptionNo, string UserCount, string UserType, string UserNo)
{
try
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(System.Windows.Forms.Application.StartupPath + "\\Logs\\Log.txt", true, Encoding.GetEncoding("gb2312"));
string WriteStr = "";
WriteStr = DateTime.Now.ToString() + ":\r\n";
WriteStr += LogType + "," + OperateType + "," + ExceptionNo + "," + UserCount + "," + UserType + "," + UserNo + "\r\n";
WriteStr += contetnstr + "\r\n";
sw.WriteLine(WriteStr);
sw.Close();
}
catch (Exception ex)
{
}
}
}
这是窗体里各个控件的代码
public partial class MySQLRW1 : Form
{
public MySQLRW1()
{
InitializeComponent();
}
mysqlReadWriteClass me = new mysqlReadWriteClass();
private void read_Click(object sender, EventArgs e)
{
string strsql = "select * from information";//读取数据的SQL语句
dataGridView1.DataSource = me.getTable(strsql);//调用读数据的函数进行读，并把读取的表返回，并绑定到数据控件上。
//me.readData("employee", ref dataGridView1);
}
private void add_Click(object sender, EventArgs e)
{
string strsql = "insert into information(ID,姓名,性别,年龄,住址,入学时间) values('" + ID.Text + "'";
strsql = strsql + ",'" + 姓名.Text + "'";
strsql = strsql + ",'" + 性别.Text + "'";
strsql = strsql + ",'" + 年龄.Text + "'";
strsql = strsql + ",'" + 住址.Text + "'";
strsql = strsql + ",'" + 入学时间.Text + "'";
strsql = strsql + ")";
me.execSql(strsql);
read_Click(sender, e);
}
private void modify_Click(object sender, EventArgs e)
{
string strsql = "update information set ID='" + ID.Text + "'";
strsql = strsql + ",姓名='" + 姓名.Text + "'";
strsql = strsql + ",性别='" + 性别.Text + "'";
strsql = strsql + ",年龄='" + 年龄.Text + "'";
strsql = strsql + ",住址='" + 住址.Text + "'";
strsql = strsql + ",入学时间='" + 入学时间.Text + "'";
strsql = strsql + " where ID='" + ID.Text + "'";
me.execSql(strsql);
read_Click(sender, e);
}
private void del_Click(object sender, EventArgs e)
{
string strsql = "delete from information where ID='" + ID.Text + "'";
me.execSql(strsql);
read_Click(sender, e);
}
private void button1_Click(object sender, EventArgs e)
{
this.Close();
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
ID.Text = dataGridView1.Rows[e.RowIndex].Cells["ID"].Value.ToString();
姓名.Text = dataGridView1.Rows[e.RowIndex].Cells["姓名"].Value.ToString();
性别.Text = dataGridView1.Rows[e.RowIndex].Cells["性别"].Value.ToString();
年龄.Text = dataGridView1.Rows[e.RowIndex].Cells["年龄"].Value.ToString();
住址.Text = dataGridView1.Rows[e.RowIndex].Cells["住址"].Value.ToString();
入学时间.Text = dataGridView1.Rows[e.RowIndex].Cells["入学时间"].Value.ToString();
}
}
private void MySQLRW1_Load(object sender, EventArgs e)
{
read_Click(sender, e);
}
}
上一篇： 协方差矩阵的python实现