文档章节

C# 操作MySQL

vga
 vga
发布于 2014/08/29 13:55
字数 1056
阅读 1435
收藏 7
using System;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Web.UI.WebControls;
using System.Web;

public class DBQuery
{
    public DBQuery()
 {
  //
  // TODO: 在此处添加构造函数逻辑
  //
       
 }
 
    public static void SetDataView(GridView dg)
    {
        if (GFDB.SysInfo.GridViewPage == 0)
        {
            GFDB.SysInfo.GridViewPage = int.Parse(ConfigurationManager.AppSettings["GridViewPage"].ToString());
        }
        if (GFDB.SysInfo.GridViewPage > 0)
        {
            dg.PageSize = GFDB.SysInfo.GridViewPage;// 15;//每页显示15条记录
        }
        dg.ShowHeaderWhenEmpty = false;
        dg.RowDataBound += new GridViewRowEventHandler(SmartGridView_RowDataBound);
    }
    public static void SetDataView(GridView dg, bool ARowDataBound, bool AShowHeaderWhenEmpty)
    {
        if (GFDB.SysInfo.GridViewPage == 0)
        {
            GFDB.SysInfo.GridViewPage = int.Parse(ConfigurationManager.AppSettings["GridViewPage"].ToString());
        }
        if (GFDB.SysInfo.GridViewPage > 0)
        {
            dg.PageSize = GFDB.SysInfo.GridViewPage;// 15;//每页显示15条记录
        }
        dg.ShowHeaderWhenEmpty = AShowHeaderWhenEmpty;
        if (ARowDataBound)
        {
            dg.RowDataBound += new GridViewRowEventHandler(SmartGridView_RowDataBound);
        }
    }
    public static void SmartGridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "currentcolor = this.style.backgroundColor;this.style.backgroundColor='#cccccc'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor = currentcolor");
            e.Row.Attributes["style"] = "Cursor:hand;height:28px;";
        }
    }
       #region EXECUTE DATASET
    //    DBConnectionPool
        /// <summary>
        /// This method returns the data in dataset form. 
        /// </summary>
        /// <param name="cmdType">Command type</param>
        /// <param name="cmdText">Command text</param>
        /// <returns>Data in the form of Dataset.</returns>
        /// <summary>
        public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText)
        {
            DataSet dsData = new DataSet();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
            MySqlCommand myCommand = new MySqlCommand();
            try
            {
                try
                {
                    myCommand.Connection = DBConnectionPool.getPool().getConnection();
                    myCommand.CommandType = cmdType;
                    myCommand.CommandText =  CommandText; //"set names gbk;" +
                    myDataAdapter.SelectCommand = myCommand;
                    myDataAdapter.Fill(dsData);
                    return dsData;
                }
                catch (MySql.Data.MySqlClient.MySqlException Ex)
                {
                    ExceptLog.SetExcept(Ex);
                    return null;
                    //throw new Exception(Ex.Message);
                    
                }
            }
            finally
            {
                DBConnectionPool.getPool().closeConnection(myCommand.Connection);
            }
            
        }
        /// <summary>
        /// This method returns the data in dataset form. 
        /// </summary>
        /// <param name="cmdType">Command type</param>
        /// <param name="cmdText">Command text</param>
        /// <param name="mysqlParams">MySqlParameters</param>
        /// <returns>Data in the form of Dataset.</returns>
        /// public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)
        public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)
        {
             
            DataSet dsData = new DataSet();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
            MySqlCommand myCommand = new MySqlCommand();
            try
            {
                try
                {
                    myCommand.Connection = DBConnectionPool.getPool().getConnection();
                    myCommand.CommandType = cmdType;
                    myCommand.CommandText = CommandText;
                    
                    for (int i = 0; i < mysqlParams.Length; i++)
                    {
                        myCommand.Parameters.Add(mysqlParams[i]);
                    }
                    myDataAdapter.SelectCommand = myCommand;
                    myDataAdapter.Fill(dsData);
                    return dsData;
                }
                catch (Exception Ex)
                {
                    ExceptLog.SetExcept(Ex);
                    return null;
                    //throw Ex;
                }
            }
            finally
            {
                DBConnectionPool.getPool().closeConnection(myCommand.Connection);
            }
        }

        public static bool ExecuteSQLParams(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)
        {
            DataSet dsData = new DataSet();
            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
            MySqlCommand myCommand = new MySqlCommand();
            try
            {
                try
                {
                    myCommand.Connection = DBConnectionPool.getPool().getConnection();
                    myCommand.CommandType = cmdType;
                    myCommand.CommandText = CommandText;
                    for (int i = 0; i < mysqlParams.Length; i++)
                    {
                        myCommand.Parameters.Add(mysqlParams[i]);
                    }
                    myDataAdapter.SelectCommand = myCommand;
                    myCommand.ExecuteNonQuery();
                    //myDataAdapter.Fill(dsData);
                    return true;
                }
                catch (Exception Ex)
                {
                    ExceptLog.SetExcept(Ex);
                    return false;
                    //throw Ex;
                }
            }
            finally
            {
                DBConnectionPool.getPool().closeConnection(myCommand.Connection);
            }
        }
    public static MySqlDataReader ExecuteDataReader(string CommandText)
        {
            MySqlCommand myCommand = new MySqlCommand();
            MySqlDataReader myReader;
            try
            {
                try
                {
                    myCommand.Connection = DBConnectionPool.getPool().getConnection();
                    myCommand.CommandType = CommandType.Text;
                    myCommand.CommandText = CommandText;
                    myReader = myCommand.ExecuteReader();
                    return myReader;
                }
                catch (Exception Ex)
                {
                    ExceptLog.SetExcept(Ex);
                    return null;
                    //throw Ex;
                }
            }
            finally
            {
                DBConnectionPool.getPool().closeConnection(myCommand.Connection);
            }
        }
    public static DataTable ExecuteDataTable(string CommandText)
    {
        MySqlCommand myCommand = new MySqlCommand();
        
        MySqlDataReader myReader;
        try
        {
            try
            {
                myCommand.Connection = DBConnectionPool.getPool().getConnection();
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = CommandText;//"set names gbk;" +
                myReader = myCommand.ExecuteReader();
                DataTable Table = new DataTable("myDataTable");
                Table.Load(myReader);
                return Table;
            }
            catch (Exception Ex)
            {
                ExceptLog.SetExcept(Ex);
                return null; 
            }
        }
        finally
        {
            
            DBConnectionPool.getPool().closeConnection(myCommand.Connection);
        }
    }
    public static DataTable ExecuteSqlReDataTable(string CommandText)
    {
        MySqlCommand myCommand = new MySqlCommand();
        MySqlDataReader myReader;
        try
        {
            try
            {
                myCommand.Connection = DBConnectionPool.getPool().getConnection();
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = CommandText;//"set names gbk;" +
                myReader = myCommand.ExecuteReader();
                DataTable Table = new DataTable("myDataTable");
                Table.Load(myReader);
                return Table;
            }
            catch (Exception Ex)
            {
                throw Ex;
                return null;
            }
        }
        finally
        {
            DBConnectionPool.getPool().closeConnection(myCommand.Connection);
        }
    }
    public static string GetParamValueMemo(string AParamName)
    {
 
        string strSQL = "select * from `sys_parameter_memo` where name ='" + AParamName + "'";
        DataTable dt = DBQuery.ExecuteDataTable(strSQL);
        if (dt.Rows.Count == 0)
        {
            return "";
        }
        string str = dt.Rows[0]["pvalue"].ToString().Replace("\r\n", "\n");
       
        string[] slist = str.Split(new char[] {'\n'});
        str = "";
        for (int i = 0; i < slist.Length - 1;i++ )
        {
            if (slist[i].Trim().IndexOf("#") != 0)
            {
                str = str + slist[i];
            }
        }
        return str;
    }
    /// <summary>
    /// 执行一条SQL返回是否成功
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="CommandText"></param>
    /// <returns>真假</returns>
    public static bool ExecuteSql(CommandType cmdType, string CommandText,ref string ReError)
    {
        if ("".Equals(CommandText)) 
        {
            ReError = "SQL语为空!";
            return false;
        }
        MySqlCommand myCommand = new MySqlCommand();
        try
        {
            try
            {
                myCommand.Connection = DBConnectionPool.getPool().getConnection();
                myCommand.CommandType = cmdType;
                myCommand.CommandText = CommandText;
                myCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception Ex)
            {
                ReError = Ex.Message.ToString().Replace("'","`");
                ExceptLog.SetExcept(Ex);
                return false;
            }
        }
        finally
        {
            DBConnectionPool.getPool().closeConnection(myCommand.Connection);
        }
    }
    public static string GetParam(string PName)
    {
        try
        {
            DataTable dt = ExecuteDataTable("select Name,Pvalue from `sys_parameter_list` where Name='" + PName + "';");
            if ((dt == null) || (dt.Rows.Count == 0))
            {
                return "";
            }
            return dt.Rows[0]["Pvalue"].ToString();
        }
        catch (Exception Ex)
        {
            return "";
        }
    }
    public static bool ExecuteSql( string CommandText, ref string ReError)
    {
        if ("".Equals(CommandText))
        {
            ReError = "SQL语为空!";
            return false;
        }
        MySqlCommand myCommand = new MySqlCommand();
        try
        {
            try
            {
                myCommand.Connection = DBConnectionPool.getPool().getConnection();
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = CommandText;
                myCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception Ex)
            {
                ReError = Ex.Message.ToString().Replace("'", "`");
                ExceptLog.SetExcept(Ex);
                return false;
            }
        }
        finally
        {
            DBConnectionPool.getPool().closeConnection(myCommand.Connection);
        }
    }
    public static bool ExecuteSql(string CommandText)
    {
        if ("".Equals(CommandText))
        {
            return false;
        }
        MySqlCommand myCommand = new MySqlCommand();
        try
        {
            try
            {
                myCommand.Connection = DBConnectionPool.getPool().getConnection();
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = CommandText;
                myCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception Ex)
            {
                ExceptLog.SetExcept(Ex);
                return false;
            }
        }
        finally
        {
            DBConnectionPool.getPool().closeConnection(myCommand.Connection);
        }
    }
        #endregion
    public static void ExportExcel(string ExecSQL, string FileName, HttpResponse resp)
    {
        try
        {
            string tmp = "";
            int fc = 0;
            string sFileName = FileName.ToLower();
              if (sFileName == "")
            {
                sFileName = System.DateTime.Today.ToString("yyyyMMdd");
            }
            if (sFileName.IndexOf(".xls")<0){
                sFileName = sFileName + ".xls";
            }
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
     
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(sFileName).ToString());
            //下面就列出常用的一些格式:
            //1) 文本:vnd.ms-excel.numberformat:@
            //2) 日期:vnd.ms-excel.numberformat:yyyy/mm/dd
            //3) 数字:vnd.ms-excel.numberformat:#,##0.00
            //4) 货币:vnd.ms-excel.numberformat:¥#,##0.00
            //5) 百分比:vnd.ms-excel.numberformat: #0.00%
            resp.ContentType = "vnd.ms-excel.numberformat:@";
            //定义表对象与行对像,同时用DataSet对其值进行初始化 
            //DataTable dt = DBQuery.ExecuteDataSet(CommandType.Text, ExecSQL).Tables[0];
            DataTable dt = DBQuery.ExecuteDataTable(ExecSQL);
            //取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符 
            fc = dt.Columns.Count;
            for (int i = 0; i < fc; i++)
            {
                tmp = tmp + "" + dt.Columns[i].Caption.Replace("&nbsp;", "") + "\t";
            }
            resp.Write(tmp + "\n");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tmp = "";
                for (int j = 0; j < fc; j++)
                {
                    tmp = tmp + "" + dt.Rows[i].ItemArray[j] + "\t";

                }
                resp.Write(tmp + "\n");
            }
            //强制释放无用资源  
            GC.Collect();
            //写缓冲区中的数据到HTTP头文件中 
            resp.End();
            resp.Flush();
        }
        catch (Exception Ex)
        {
            ExceptLog.SetExcept(Ex);
            throw Ex;
        }
       
    }

        //使用连接后的连接获取方法
//获取连接时,就不用创建连接直接从池中获取数据
        //string strsql = "select * from Chapter";
//SqlDataAdapter sqldap = new SqlDataAdapter(strsql, DBConnectionPool.getPool().getConnection());
        //DataSet set = new DataSet();
        //sqldap.Fill(set);
        //GridView1.DataSource = set.Tables[0].DefaultView;
        //GridView1.DataBind();
}

© 著作权归作者所有

vga

vga

粉丝 23
博文 371
码字总数 26854
作品 0
佳木斯
私信 提问
加载中

评论(2)

vga
vga 博主
protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = connStr;

try
{
con.Open();
Label1.Text = "连接 Mysql 成功!";

string sql = "select * from test";
da = new MySqlDataAdapter(sql, con);
DataSet mydataset = new DataSet();
da.Fill(mydataset, "test");
BulletedList1.DataSource = mydataset.Tables["test"];
BulletedList1.DataTextField = "Name";
BulletedList1.DataValueField = "link";
BulletedList1.DataBind();
}
catch
{
Label1.Text = "连接 Mysql 失败!";
}
}
vga
vga 博主
C# BulletedList 绑定 Mysql
C# BulletedList 绑定 Mysql

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;

public partial class page1 : System.Web.UI.Page
{
string connStr = String.Format(
"server={0};user id={1};password={2};database={3};port={4};pooling=false;charset=utf8",
"192.168.1.3", "root", "1234567", "test", 3306);

public MySqlConnection con = new MySqlConnection();
public MySqlDataAdapter da;
public MySqlCommandBuilder cb;
public MySqlCommand cmd;
public MySqlDataReader dr;

protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = connStr;

try
{
con.Open();
Label1.Text = "连接 Mysql 成功!";

string sql = "select * from test";
da = new MySqlDataAdapter(sql, con);
DataSet mydataset = new Data
C#中简单操作Mysql数据库

以C#访问MySQL数据库,执行简单的CRUD。 MySql.Data.dll是C#操作MySQL的驱动文件,是C#连接MySQL必要插件,使C#语言更简洁的操作MySQL数据库。可以用NuGet命令安装 PM>Install-Package MySql...

咸鱼翻身①
04/30
0
0
在C#中用MySql.Data.MySqlClient连接MySql

在C#中连接MySql数据库其实是件很简单的事情,但对于刚开始学习C#的朋友来说,问题却是不小,主要原因是相对于ACCESS和MSSql 来说,MySql方面的教程文章实在太少,我也是自己摸索好好半天才搞...

老朱教授
2018/01/10
0
0
c#开发大全、系列文章、精品教程

全栈工程师开发手册 (作者:栾鹏) c#系列教程: c#实现ajax通信:向后台发送JSON字符串,接收响应字符串,并转换为对象 c#文件夹常用操作,属性设置,遍历、压缩 c#获取网页源代码的5种方式...

luanpeng825485697
2017/10/03
0
0
C#7.0连接MySQL8.0数据库的小笔记

  1、要连接MySql数据库必须首先下载MySql官方的连接.net的文件,文件下载地址为https://dev.mysql.com/downloads/connector/net/6.6.html#downloads ,下载平台选择.Net&Mono,下载ZIP版。...

Angerxzer
2018/07/30
0
0
.NET Core 使用Dapper 操作MySQL

原文出处:LineZero .NET Core 使用Dapper 操作MySQL 数据库, .NET Core 使用Dapper。目前官方没有出.NET Core MySQL 驱动,但是已经有第三方进行改动封装出.NET Core MySQL Connector 预览...

LineZero
2016/06/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

nginx学习笔记

中间件位于客户机/ 服务器的操作系统之上,管理计算机资源和网络通讯。 是连接两个独立应用程序或独立系统的软件。 web请求通过中间件可以直接调用操作系统,也可以经过中间件把请求分发到多...

码农实战
今天
5
0
Spring Security 实战干货:玩转自定义登录

1. 前言 前面的关于 Spring Security 相关的文章只是一个预热。为了接下来更好的实战,如果你错过了请从 Spring Security 实战系列 开始。安全访问的第一步就是认证(Authentication),认证...

码农小胖哥
今天
11
0
JAVA 实现雪花算法生成唯一订单号工具类

import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import java.util.Calendar;/** * Default distributed primary key generator. * * <p> * Use snowflake......

huangkejie
昨天
12
0
PhotoShop 色调:RGB/CMYK 颜色模式

一·、 RGB : 三原色:红绿蓝 1.通道:通道中的红绿蓝通道分别对应的是红绿蓝三种原色(RGB)的显示范围 1.差值模式能模拟三种原色叠加之后的效果 2.添加-颜色曲线:调整图像RGB颜色----R色增强...

东方墨天
昨天
11
1
将博客搬至CSDN

将博客搬至CSDN

算法与编程之美
昨天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部