How to connect mysql to DevExpress ASPxScheduler without SqlDataSource

I have an ASP.net project that I am looking at and they want to use MySQL. I'm used to SQL Server, but using mySQL shouldn't be a problem.

Usually the control should bind to a SqlDataSource, but is not available in MySQL (from other posts on this site).

What's the best way to connect MySQL and DevExpress ASPxScheduler so you can create appointments?

+1


source to share


2 answers


Why not ObjectDataSource and write data layer? Or use LLBLGen, I think it works great with MySQL. One of the caveats I've seen is that the MySQL ODBC and ADO drivers have metadata issues.



+3


source


I ended up using the objectdatasource and ObjectCreated method and wrote a datalayer to insert records into the mysql database. I included my code just to the point that someone needs some help with some logic.



protected void appointmentsDataSource_ObjectCreated(object sender, ObjectDataSourceEventArgs e)
    {
        e.ObjectInstance = new CustomEventDataSource(GetCustomEvents());
    }

  public void InsertAppointment()
    {

        //need to reformat the dates
        string tempStartDate;
        string tempStartMinutes;

        if (appointmentobject.Start.Minute.ToString().Length == 1)
        {
            tempStartMinutes = "0" + appointmentobject.Start.Minute.ToString();
        }
        else
        {
            tempStartMinutes = appointmentobject.Start.Minute.ToString();
        }

        tempStartDate = AppointmentObject.Start.Year + "-"
            + AppointmentObject.Start.Month + "-"
            + appointmentobject.Start.Day + " "
            + appointmentobject.Start.Hour + ":"
            + tempStartMinutes;

        string tempEndDate;
        string tempEndMinutes;

        if (appointmentobject.End.Minute.ToString().Length == 1)
        {
            tempEndMinutes = "0" + appointmentobject.End.Minute.ToString();
        }
        else
        {
            tempEndMinutes = appointmentobject.End.Minute.ToString();
        }

        tempEndDate = AppointmentObject.End.Year + "-"
            + AppointmentObject.End.Month + "-"
            + appointmentobject.End.Day + " "
            + appointmentobject.End.Hour + ":"
            + tempEndMinutes;

        //TODO Add CustomField : Need to add to this Insert Statement

        //Change the appointment subject
        string NewSubject = AppointmentObject.CustomFields["fldFirstName"]
            + ", " + AppointmentObject.CustomFields["fldLastName"]
            + ", " + AppointmentObject.CustomFields["fldClassID"]
            + ", " + AppointmentObject.CustomFields["fldPhoneNumberDay"];

        string mySQLQueryString = @"INSERT INTO appointment (StartDate,EndDate,Subject,Status,Description,label,location,Type,FirstName,
            LastName,PhoneNumberDay,PhoneNumberEvening,DriversLicenseNumber,Email,RentalCar,Payment,ConfirmationNumber,
            PermitNumber,ClassID,CreateDate,CreateUser,NoticeToReport) 
            VALUES('" + tempStartDate + "','"
            + tempEndDate + "', '"
            //+ AppointmentObject.Subject + "',"
            + NewSubject + "',"
            + AppointmentObject.StatusId + ",'"
            + AppointmentObject.Description + "',"
            + AppointmentObject.LabelId + ", '"
            + AppointmentObject.Location + "',"
            + "0, '" //type
            + AppointmentObject.CustomFields["fldFirstName"] + "','" 
            + AppointmentObject.CustomFields["fldLastName"] + "','"
            + AppointmentObject.CustomFields["fldPhoneNumberDay"] + "','"
            + AppointmentObject.CustomFields["fldPhoneNumberEvening"] + "','"
            + AppointmentObject.CustomFields["fldDriversLicenseNumber"] + "','"
            + AppointmentObject.CustomFields["fldEmail"] + "',"
            + AppointmentObject.CustomFields["fldRentalCar"] + ","
            + AppointmentObject.CustomFields["fldPayment"] + ",'"
            + AppointmentObject.CustomFields["fldConfirmationNumber"] + "','"
            + AppointmentObject.CustomFields["fldPermitNumber"] + "',"
            + AppointmentObject.CustomFields["fldClassID"] + ", '"
            //ignore create date for now.
            //+ AppointmentObject.CustomFields["fldCreateDate"] + "', '"
            + "2009-01-01 12:00', '"
            + AppointmentObject.CustomFields["fldCreateUser"] + "', "
            + AppointmentObject.CustomFields["fldNoticeToReport"] + ")";

        MySqlConnections test = new MySqlConnections();
        test.InsertRow(mySQLQueryString);

    }

public class MySqlConnections
{
    private static string DriverConnectionString = "Database=driverexam;Data Source=localhost;User Id=ART;Password=art01";

    public DataSet SelectRows(DataSet dataset, string query, string tablename)
    {
        MySqlConnection conn = new MySqlConnection(DriverConnectionString);

        MySqlDataAdapter adapter = new MySqlDataAdapter();
        adapter.SelectCommand = new MySqlCommand(query, conn);
        adapter.Fill(dataset, tablename);

        conn.Close();
        return dataset;
    }

    public bool InsertRow(string query)
    {
     //   MySqlConnection conn = new MySqlConnection(DriverConnectionString);

        MySqlConnection conn = new MySqlConnection();
        MySqlCommand cmd = new MySqlCommand();

        conn.ConnectionString = DriverConnectionString;

        try
        {
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
            conn.Close();
            Console.WriteLine("Success Occurred ");
        } //end of try
        catch(Exception ex)
        {
            Console.WriteLine("Error Occurred - " + ex.Message);
        }

        return true;
    }
}

      

+1


source







All Articles