Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mysql with asp.net connection

Posted on 2010-01-04
9
Medium Priority
?
523 Views
Last Modified: 2012-05-08
i need connection from asp.net to mysql database...
pls send me the detailed answer...............
0
Comment
Question by:sunda2010
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:ICaldwell
ID: 26177545
Not sure if you want VB or C#....
Dim sConString As String = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; 
DATABASE=Your_Mysql_DB; UID=mysql_username; PASSWORD=mysql_password; OPTION=3"
Dim oConnection as ODBCConnection = new ODBCConnection(sConString)
Dim sSQL as String = "SELECT * FROM Table1"
Dim oDataAdapter as ODBCDataAdapter = New ODBCDataAdapter(sSQL, oConnection)
Dim oDataSet as DataSet = new DataSet()
oDataAdapter.Fill(oDataSet)
oDataGrid.DataSource = oDataSet
oDataGrid.DataBind()

Open in new window

0
 

Author Comment

by:sunda2010
ID: 26177581
i need in c#... pls send it clearly....
0
 
LVL 3

Accepted Solution

by:
K4U earned 252 total points
ID: 26177596
using System;
using System.Data;
using ByteFX.Data.MySqlClient;

   class MySqlConnect
   {
      static void Main()
      {
         string connString = @"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false";
         MySqlConnection conn = new MySqlConnection(connString);

         try
         {
            conn.Open();
            Console.WriteLine("Connection opened.");

            Console.WriteLine("Connection Properties:");  
            Console.WriteLine("\tConnection String: {0}",conn.ConnectionString);    
            Console.WriteLine("\tDatabase: {0}",conn.Database);  
            Console.WriteLine("\tServerVersion: {0}",
               conn.ServerVersion);
            Console.WriteLine(
               "\tState: {0}",
               conn.State);
         }
         catch (MySqlException e)
         {
            Console.WriteLine("Error: " + e);  
         }
         finally
         {
            conn.Close();
            Console.WriteLine("Connection closed.");
         }
      }
   }
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 11

Expert Comment

by:ICaldwell
ID: 26177614
Tried to make it as simple as I could...
using System.Data;
using System.Data.Odbc;

string ConnectionString = @"driver={MySQL ODBC 3.51 Driver};server=localhost;database=books;uid=band;pwd=letmein;";
string CommandText = "select * from Publisher";
    
OdbcConnection myConnection = new OdbcConnection(ConnectionString);
OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
    
myConnection.Open();
    
DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataBind();
    
// close the connection
myConnection.Close();

Open in new window

0
 
LVL 11

Expert Comment

by:ICaldwell
ID: 26177617
obviously the using would go at the top of your file and the part between would go in your function.... I hope this is clear and easy to understand...
0
 

Author Comment

by:sunda2010
ID: 26178009
i found some Run time error in this i need with odbc driver5.1 connectivity........... help for me soon....
0
 
LVL 11

Expert Comment

by:ICaldwell
ID: 26180465
Do you have the ODBC driver installed for MySQL?

http://dev.mysql.com/downloads/connector/odbc/
0
 

Assisted Solution

by:manitou2215
manitou2215 earned 248 total points
ID: 26180711
First download the ado.net connector for MySql:
http://dev.mysql.com/downloads/connector/net/

Include a reference to MySql.Data in your Visual Studio Project.

Add the connection string to your web.config:
<connectionStrings>
    <add name="connString"
connectionString="Server=ServerAddress;Database=DbName;Uid=UserId;Pwd=Password;port=3306;pooling=true;"
         providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Declare the following in your code file:
using System.Configuration;
using MySql.Data.MySqlClient;

Now you are ready to start using it in your code.  You can search connector /net on www.mysql.com for more information.

Here is an example to help get you started.

string dbConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(connString);
MySqlDataReader reader = null;

// sample data
int Id = 1;
string myInt = "";
int myString = "";


try
{
    myConnection.Open();

    MySqlCommand myCommand = new MySqlCommand();
    myCommand.Connection = myConnection;
    myCommand.CommandType = CommandType.Text;

    MySqlParameter[] myParameter = new MySqlParameter[1];
    myParameter[0] = new MySqlParameter("@myId", MySqlDbType.Int32);
    myParameter[0].Direction = ParameterDirection.Input;
    myParameter[0].Value = Id;

    //add parameters to the command
    myCommand.Parameters.Add(myParameter[0]);

    //execute sql statement to get values from the database
    myCommand.CommandText = "SELECT * FROM MyTable WHERE ID=@myId";
    reader = myCommand.ExecuteReader();

    //get values from the database
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            // There would only be one row if Id is the primary key, else
            // this will loop through each record returned by the DB.

            // Also, if any of these values are null, an exception is throw,
            // you can add a try/catch around them if needed.

            myInt = Convert.ToInt32(reader["DbIntField"]);
            myString = Convert.ToString(reader["DbStringField"]);
        }
    }
    else
    {
        throw new Exception("No record found with ID: " + Id);
    }
    reader.Close();
}
catch (Exception e)
{
    reader.Close();
    myConnection.Close();
    throw e;
}

myConnection.Close();
0
 

Expert Comment

by:manitou2215
ID: 26180742
sorry, i just noticed a typo in the code above.  the correction:

string dbConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
MySqlConnection myConnection = new MySqlConnection(dbConnectionString);
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question