ASP.NET connecting to MySQL

[Holokai]

Hey guys, I'm attempting to connect to a MySQL database via ASP.NET, and I'm finding the code to do so very... cumbersome.

Here's what I'm doing now -

private const string connStr = "Driver={MySQL ODBC 3.51 Driver};"+
			"Server=www.bob.com;Database=test;uid=bob;pwd=bobrules;option=3";
		protected System.Web.UI.WebControls.Label Label1;
		private DateTime dt = new DateTime();
		private void Page_Load(object sender, System.EventArgs e)
		{
			dt = System.DateTime.Now;
			Label1.Text = dt.ToString("yyyy:MM:dd hh:mm:ss");
			using(OdbcConnection con = new OdbcConnection(connStr))
			using(OdbcCommand cmd = new OdbcCommand("INSERT INTO entries VALUES('0',"
				+"'"+dt.ToString("yyyy:MM:dd hh:mm:ss")+"', 'testing', 'title test', 'Chris')", con))
			{
				con.Open();
				cmd.ExecuteReader();
			}
		}

It's kinda big just for an insert I could do with one line in PHP. Is there an easier way to do this? I can just create a DB API I guess, wouldn't take that long. But I hope I am just missing something.

Thanks in advance,

Chris

[Holokai]

Well, scratch that. I can't figure this thing out. It's been almost a month of on again off again research / attempts and I can't get ASP.NET to work with MySQL. If anyone has any experience with this, please let me know how you do it.

Thanks much,
Chris

[echoSwe]

Hello Holokai

I don't often discuss ASP.Net in this forum, mostly because I find the help I get the pretty bad... There are many nice ways of getting your code a bit compressed. That thing about one line is really not relevant since then you have fewer customization options. In order to get that into one line in C# you could create a data access layer and an insert function that takes your strings as parameters, but then in your business layer you could simply call the method, e.g.

private objDAL dal = new objDAL();
if(dal.Insert("string one", [...], "string n")) lblAnswer.Text = "Update successful";

but then in your data access layer you'd have to instantiate the connection and the command. I'll paste a function from an image gallery into the code sketch below:

namespace h { 
  class h.e {
static int concurrentCons;
//and here are the rest of the variables and the constructor
public dal() {
//constructor
}
    public static List<Photo> GetPhotos(int AlbumID) {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString)) {
            using (SqlCommand command = new SqlCommand("GetPhotos", connection)) {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));
                bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));
                command.Parameters.Add(new SqlParameter("@IsPublic", filter));
                connection.Open();
                List<Photo> list = new List<Photo>();
                using (SqlDataReader reader = command.ExecuteReader()) {
                    while (reader.Read()) {
                        Photo temp = new Photo(
                            (int)reader["PhotoID"],
                            (int)reader["AlbumID"],
                            (string)reader["Caption"]);
                        list.Add(temp);
                    }
                }
                return list;
            }
        }


    }
} }

Alright, that's the structure of it. A few buts... (haha...) This code isn't for what you wanted. It's for getting, but it's the same paradigm. It all depends on how scalable you want to do it and how good you want your application architecture to be... If you don't care for stored procedures or don't know how to use them, just write the SQL string in your command - then insert the strings via parameters. This takes care of SQL injections while you're at it... Then execute it.

But if you do it like this you'll be able to call it from any codebehind (presentation layer) you want and you can do it with only one line. The line corresponding to this get function would be

//get album
try {
List<Photo> album = GetPhotos(Request.QueryString["id"]);
}
catch { //couldn't be done }

[echoSwe]

And also, if you're after ease of use, use the DataSet instead of the DataReader.

I'm personally using MySQL and ASP.Net and I've also built other people's system using it... So it does work

[Holokai]

echoSwe - Thanks for the examples. I haven't had a chance to look into things here for a while, I've actually been up on a mountain excavating a pad for a large home. Soooo... I've been busy. But I'm hoping to get a chance to get back to some coding here soon, and I'm sure I'll have questions for you

Thanks!

- Chris

[Holokai]

Oi... This ASP.NET and MySQL stuff is hard as heck. One wonders why it isn't as streamlined as PHP and MySQL is. That should be the standard, or maybe I'm just missing something.

[echoSwe]

Well, you could write this;

        DataSet ds = new DataSet();
        OdbcDataAdapter oDa = new OdbcDataAdapter("query", new OdbcConnection("DSN"));
        oDa.Fill(ds);

which would give the same bloated exerience as you have in MySQL , but also the possibility for creating data relations within the dataset and cache it (contrary to a recordset) which is live)

[Holokai]

Well, I have the database part down, finally. I hate working on a project here and there. I'm lucky when I Get 5 minutes straight to work on this. Thanks for the help, echoSwe. By the way, I browsed through your websites and stuff, very nice.

- Chris

[echoSwe]

Hi and thanks Holokai,

Know what it's like, go to school, but I'm trying to update my web site at the same time - writing my own blog engine in ASP.Net which proves to be a real challenge when it comes to the back-end. Unfortunately it's not finished yet, so the homepage hasn't been updated for a long time as you can see.

Did this yesterday though (I'm better at front-end)

Good luck.

[Holokai]

Damn impressive! I've always been better at the back end. My major in College was software engineering. I consider myself to be design-challenged. I usually browse the web for a few days, book marking 5 or 6 sites I like, then creating a mish mosh of what I liked from all of them. It never looks good

I've written three blog engines now, all in PHP. For some reason, each site re-design I decided to just start over. This time I thought I'd pick up C#. It's definitely interesting. My next problem I have to tackle is dealing with Microsoft's gridlayout crap. Obviously It needs to be replaced with CSS.