DataBase Connection wioth Mysql with C#

install first MySql.web.

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using MySql.Data.MySqlClient;

using System.Linq;

using System.Web;

namespace AjayApi.Models

{

public class DbConnectionMysql

{

//string cs = @"Data Source=xxx.xxx.xx.xx; Network Library=DBMSSOCN;Initial Catalog=xxxxx;user id=x;password=xxxxx";

string cs = @"Server=localhost; Database=ajay;Uid=root;Pwd=;SslMode = none";

//string cs1 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

public DataSet GetMultipalTable(string sql)

{

DataTable dt;

DataSet dset = new DataSet(); //Creating instance of DataSet

try

{

using (MySqlConnection con = new MySqlConnection(cs))

{

con.Open();

MySqlDataAdapter da = new MySqlDataAdapter(sql, con);

//da.Fill(dset, "student_detail"); // Filling the DataSet with the records returned by SQL statemetns written in sqldataadapter

da.Fill(dset); // Filling the DataSet with the records returned by SQL statemetns written in sqldataadapter

//dataGridView1.DataSource = dset.Tables["student_detail"]; // Binding the datagridview

//dt = new DataTable();

//da.Fill(dt);

}

}

catch (Exception ex)

{

dset = null;

}

return dset;

}

public DataTable GetTable(string sql)

{

DataTable dt;

try

{

using (MySqlConnection con = new MySqlConnection(cs))

{

con.Open();

MySqlDataAdapter da = new MySqlDataAdapter(sql, con);

dt = new DataTable();

da.Fill(dt);

}

}

catch (Exception ex)

{

dt = null;

}

return dt;

}

public MySqlDataReader GetSqlDateReader(string sql)

{

MySqlDataReader dr;

try

{

MySqlConnection con = new MySqlConnection(cs);

con.Open();

MySqlCommand cmd = new MySqlCommand(sql, con);

dr = cmd.ExecuteReader();

}

catch (Exception ex)

{

dr = null;

}

return dr;

}

public int CURDtData(string sql)

{

int result = 0;

try

{

using (MySqlConnection con = new MySqlConnection(cs))

{

con.Open();

MySqlCommand cmd = new MySqlCommand(sql, con);

result = cmd.ExecuteNonQuery();

con.Close();

}

}

catch (Exception ex) { }

return result;

}

public int insertData(string sql)

{

return CURDtData(sql);

}

public int updateData(string sql)

{

return CURDtData(sql);

}

public int DeleteData(string sql)

{

return CURDtData(sql);

}

//-----------------------------------

public DataTable GetTable(string sql, List<SqlParameter> prm)

{

DataTable dt;

try

{

using (MySqlConnection con = new MySqlConnection(cs))

{

con.Open();

using (MySqlDataAdapter da = new MySqlDataAdapter(sql, con))

{

da.SelectCommand.Parameters.AddRange(prm.ToArray());

dt = new DataTable();

da.Fill(dt);

da.SelectCommand.Parameters.Clear();

}

con.Close();

}

}

catch (Exception ex) { dt = null; }

return dt;

}

public MySqlDataReader GetSqlDateReader(string sql, List<MySqlParameter> prm)

{

MySqlDataReader dr;

try

{

MySqlConnection con = new MySqlConnection(cs);

con.Open();

using (MySqlCommand cmd = new MySqlCommand(sql, con))

{

cmd.Parameters.AddRange(prm.ToArray<MySqlParameter>());

dr = cmd.ExecuteReader();

cmd.Parameters.Clear();

}

con.Close();

}

catch (Exception ex) { dr = null; }

return dr;

}

public int CURDtData(string sql, List<MySqlParameter> prm)

{

int result = 0;

try

{

using (MySqlConnection con = new MySqlConnection(cs))

{

con.Open();

using (MySqlCommand cmd = new MySqlCommand(sql, con))

{

cmd.Parameters.AddRange(prm.ToArray<MySqlParameter>());

result = cmd.ExecuteNonQuery();

return result;

cmd.Parameters.Clear();

}

con.Close();

}

}

catch (Exception ex) { return 0; }

}

public int InsertData(string sql, List<MySqlParameter> prm) { return CURDtData(sql, prm); }

public int UpdateData(string sql, List<MySqlParameter> prm) { return CURDtData(sql, prm); }

public int DeleteData(string sql, List<MySqlParameter> prm) { return CURDtData(sql, prm); }

}

}