Asp.net C# connection with mySQL

.NET
1

Hello everyone,

what I would like to do is to connect with C# (in a *.aspx file of course) to a mySQL database

I have configured my own server with ASP.net 2.0 and I have installed the myODBC 3.51 driver from mysql.com

somehow I can’t find the solution to this simple connection! All I want is to connect to the database and execute some simple queries on it via a *.aspx webpage.

can somebody please help me?

2

Here are a couple of examples:

http://www.vbdotnetheaven.com/Code/Jun2003/2097.asp

http://www.codeproject.com/database/connectionstrings.asp#MySQL

MB

3

Hello,

thanks for the fast response, but unfortunately I need C# to fix the connection and NOT VB or VB.net

so please, anyone???

4

is a great source of connection string information. Note that you should be using a native .NET provider for mySQL. Links available on the mySQL home site.

5

it’s not about the connectionstring. I can make a connection from an asp page with VB. but I need a working example in C# that I can copy & paste.

I’m using my own server with asp.net 2.0 with C# and I use the myODBC driver to connect to the mySQL database.

what I don’t need is an Atlas example or simular like that. I would like to have full control over my data and just want to know how I can use my connectionstring in C#.

I need an *.aspx file with C# able to connect via an object where I can get my data with.

I need to make a connection in C# something like the VB example below:


Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "driver={mysql ODBC 3.51 driver}; server=ipOfMyServer; port=3306; database=test; uid=myUserID; pwd=myPassWord; OPTION=17923")

strSQL = "SELECT * FROM myTable WHERE ID=10"
			
oRecordset.Open strSQL, adoCon

While not oRecordset.EOF
	id = oRecordset("ID")
	record1 = oRecordset("record1")
	record2 = oRecordset("record2")
	record3 = oRecordset("record3")			
	oRecordset.MoveNext
Wend

oRecordset.Close

Set oRecordset = Nothing
Set adoCon = Nothing
6

Here is the beauty of the .NET dataproviders–the code needed to access MySql will be nearly identical to the code for accessing, say, MsSql. You just need to change the types to use the right database specific providers.

EG:


List<Foo> foos=new List<Foo>();
DbConnection conn=new SqlConnection(cString);
DbCommand cmd=conn.CreateCommand();
cmd.CommandText="SELECT * FROM Foo";
conn.Open();
IDataReader reader=cmd.ExecReader();
Foo f;
while (reader.Read())
{
     f=new Foo();
     f.Id=reader.IsDbNull(0) ?? 0 : reader.GetInt32(0);
     f.Name=reader.IsDbNull(1) ?? string.Empty : reader.GetString(1);
     foos.Add(f);
}
conn.Close();
MyDataBoundControl.DataSource=foos;
MyDataBoundControl.DataBind();

Would work exactly the same if you were using MsSql, Oracle, Postgres, Access or anything else that fully implemented the ADO.NET dataprovider pattern. To make it work with MySql, you would just need to change the DbConnection conn=new SqlConnection() to DbConnection conn=new MySqlConnection() [or whatever they call the connection class].

ADO actually worked the same way, the code you posted was DB agnostic save the adoCon.Open statement.

7

I appreciate your effort. I don’t quite understand some parts of the code. Can you give me a more specific/complete code that I can use by copy & paste? I have tried to connect via myODBC, OleDB and I’ve used sqlcommand/oledbcommand but I am fed of trying any more.

Can you please give me the exact code? Am I missing some important things like a project file with a web.config for example? It would be better for me to understand if I have the complete source file that I can paste into my webfolder and run on my server.

Thanks anyway.

8

<%@ Page Language=“C#” AutoEventWireup=“False”
EnableSessionState=“False” EnableViewState=“False” %>
<%@ Import Namespace=“System.Data” %>
<%@ Import Namespace=“System.Data.Odbc” %>

<script runat=“server”>
private const string ConnStr = “Driver={MySQL ODBC 3.51 Driver};” +
“Server=mssql.yourdomain.com;Database=dbname;uid=your-username;pwd=your-password;option=3”;

protected override void OnInit(EventArgs e)
{
base.OnInit(e);

using(OdbcConnection con = new OdbcConnection(ConnStr))
using(OdbcCommand cmd = new OdbcCommand("SELECT * FROM test_table", con))
{
  con.Open();
  dgrAllNames.DataSource = cmd.ExecuteReader(
    CommandBehavior.CloseConnection |
    CommandBehavior.SingleResult);
  dgrAllNames.DataBind();
}

}
</script>

<html>
<head>
<title>Displaying Records from MySQL test table</title>
<style>
body { font: 100% Verdana; }
</style>
</head>
<body>

<p align=“center”>All records in the test table:</p>

<asp:DataGrid ID=“dgrAllNames” HorizontalAlign=“Center”
CellPadding=“3” Runat=“server” />

</body>
</html>

Give me a shout if you dont understand the codes.

9

it works (after a slight reconfiguration in my web.config) so you are the greatest! I’ve achieved a similar gridview in VB.net but I couldn’t get it work with C#.

Now the only thing that I would prefer is something else than a datagrid. Because now I have less control over my data (I think…).

but thanks to you I’m so close now to my goal!!!

If anyone knows a way without a datagrid then please let me know!

All I want is to save the records into variables so I can use them to create my own HTML layout.

10

Is it also possible to use a datagrid and pass the data to some variables?

Something like this?


<asp:TemplateColumn>
    <ItemTemplate>
        <TABLE>
            <TR>
                <TD>ID:</TD>
                <TD><%# DataBinder.Eval(Container.DataItem, "id") %></TD>
            </TR>
            <TR>
                <TD>Naam:</TD>
                <TD><%# DataBinder.Eval(Container.DataItem, "naam") %></TD>
            </TR>
            <TR>
                <TD>moederID:</TD>
                <TD><%# DataBinder.Eval(Container.DataItem, "vaderID" ) %></TD>
            </TR>
            <TR>
                <TD>vaderID:</TD>
                <TD><%# DataBinder.Eval(Container.DataItem, "moederID") %></TD>
            </TR>
          </TABLE>
     </ItemTemplate>
</asp:TemplateColumn>

please? anyone? I need to get my data into variables so I can put them in my own HTML structure.

11

You sound like you are very new to ASP.NET and C# in general. I would really go work through some of the tutorials at http://www.asp.net/learn/default.aspx?tabid=63 before trying to build custom stuff.

12

I need a solution now please…

13

"Give a man a fish, he eats for a day. Teach a man to fish, he eats for life . . . "

PS: To start fishing, what you really want is to use a SqlDataSource control combined with a gridview and possibly a details view. Look at the tutorials at the above link, they will explain it better than some copied and pasted code snippet ever will.

14

Please don’t lecture me. I have seen your link before as many other sites.

I will use your advice and look for a solution with the Formview and Detailsview.

But to be honest, I just want a solution, that’s all. And if you can’t give me one then please don’t bother me again.

15

There is no need for that! pfft… I would suggest READING the link or other sites, and then you would be able to get the solution yourself.

If it’s a collection of data, I would recommend staying with the DataGrid (or GridView as Wyatt suggests) for displaying the data. If you want more control over the appearence, I would suggest looking at the Repeater webcontrol too

16

I gave you one. Several in fact. What I am not going to do is sit here and write your code for you. Well, unless you hire me to write your code.

Its pretty clear to me you have no desire to learn anything, but rather copy and paste your way out of whatever jam you got yourself into. So I shall grant your request and I will not be bothering with you again.

17

Thank you dhtmlgod. Finally someone who understands the situation. Thanks to you I have my problem solved.

18