通过Connector / NET与MySQL进行C#

我正在用C#(4.0 Framework)开发CMS应用程序,它连接到远程服务器上的MySQL 数据库(5.0.95)
通过MysqL Connector(6.5.4).

我在执行查询时遇到问题.

例如
我的连接字符串：

"Server=" + Options.DbServer + ";Database="+ Options.Database +";Uid=" + Options.DbUser + ";Pwd=" + Options.DbPassword + ";CharSet=utf8; Connect Timeout=30;";

我有静态类来管理数据库相关的东西,我有私人成员_connection.

private static MysqLConnection _connection;
public static MysqLConnection Connection
{ 
    get
    {
        if (_connection.State != ConnectionState.Open)
            _connection.Open();

        return _connection;
    } 
    set { _connection = value; }
}

这是初始化连接的方法：

public static bool Init(string cs)
{
    _connection = new MysqLConnection(cs);
    MysqLCommand command = new MysqLCommand("SET NAMES utf8",Connection);
    command.ExecuteNonQuery();
    return true;
}

这是我得到异常的方法：

public static bool InsertRecord(MysqLCommand command)
{
    command.Connection = Connection;
    if(command.ExecuteNonQuery() > 0)
        return true;

    return false;
}

command.ExecuteNonQuery()抛出异常：在命令执行期间遇到致命错误.

这是堆栈跟踪……

MysqL.Data.MysqLClient.MysqLException was unhandled
  Message=Fatal error encountered during command execution.
  Source=MysqL.Data
  ErrorCode=-2147467259
  Number=0
  StackTrace:
       at MysqL.Data.MysqLClient.MysqLCommand.ExecuteReader(CommandBehavior behavior)
       at MysqL.Data.MysqLClient.MysqLCommand.ExecuteNonQuery()
       at CMS.Database.InsertRecord(MysqLCommand command) in C:\_myStuff\VS2010\CMS\CMS\Database.cs:line 95
       at CMS.frmAddItem.btnDo_Click(Object sender,EventArgs e) in C:\_myStuff\VS2010\CMS\CMS\frmAddItem.cs:line 138
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m,MouseButtons button,Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,Int32 msg,IntPtr wparam,IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID,Int32 reason,Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason,ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason,ApplicationContext context)
       at System.Windows.Forms.Application.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at CMS.frmMain.btnNovi_Click(Object sender,EventArgs e) in C:\_myStuff\VS2010\CMS\CMS\frmMain.cs:line 381
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m,ApplicationContext context)
       at System.Windows.Forms.Application.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at CMS.frmLogin.DoLogin() in C:\_myStuff\VS2010\CMS\CMS\frmLogin.cs:line 55
       at CMS.frmLogin.button2_Click(Object sender,EventArgs e) in C:\_myStuff\VS2010\CMS\CMS\frmLogin.cs:line 31
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m,ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at CMS.Program.Main() in C:\_myStuff\VS2010\CMS\CMS\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly,String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile,Evidence assemblySecurity,String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext,ContextCallback callback,Object state,Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext,Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: MysqL.Data.MysqLClient.MysqLException
       Message=Fatal error encountered attempting to read the resultset.
       Source=MysqL.Data
       ErrorCode=-2147467259
       Number=0
       StackTrace:
            at MysqL.Data.MysqLClient.MysqLDataReader.NextResult()
            at MysqL.Data.MysqLClient.MysqLCommand.ExecuteReader(CommandBehavior behavior)
       InnerException: MysqL.Data.MysqLClient.MysqLException
            Message=Reading from the stream has Failed.
            Source=MysqL.Data
            ErrorCode=-2147467259
            Number=0
            StackTrace:
                 at MysqL.Data.MysqLClient.MysqLStream.LoadPacket()
                 at MysqL.Data.MysqLClient.MysqLStream.ReadPacket()
                 at MysqL.Data.MysqLClient.NativeDriver.GetResult(Int32& affectedRow,Int32& insertedId)
                 at MysqL.Data.MysqLClient.Driver.GetResult(Int32 statementId,Int32& affectedRows,Int32& insertedId)
                 at MysqL.Data.MysqLClient.Driver.NextResult(Int32 statementId,Boolean force)
                 at MysqL.Data.MysqLClient.MysqLDataReader.NextResult()
            InnerException: System.IO.EndOfStreamException
                 Message=Attempted to read past the end of the stream.
                 Source=MysqL.Data
                 StackTrace:
                      at MysqL.Data.MysqLClient.MysqLStream.ReadFully(Stream stream,Byte[] buffer,Int32 offset,Int32 count)
                      at MysqL.Data.MysqLClient.MysqLStream.LoadPacket()
                 InnerException:

有什么建议？

最佳答案

我注意到你打开了连接,但是当你完成它们时你不会关闭它们.我更喜欢在需要时打开连接的方法,而不是在它们尚未打开时打开连接.它们可能是陈旧的.

缓存连接字符串但不缓存连接本身.

public static string ConnectionString {get;set;}

public static bool InsertRecord(sql)
{
    bool success = false;
    using (var con = new Connection(ConnectionString)){
        var command = new sqlCommand(sql,con);
        success = (command.ExecuteNonQuery() > 0);
    }
    return success;
}

当不再需要资源时,应释放资源.

通过Connector / NET与MySQL进行C#

猜你在找的MySQL相关文章