general my sql

public class SQLCommandGeneral
{
#region Huynh Van Si General command

void New()
{
}
string ReturnValueParameterName = "RETURN_VALUE";
///****************************************************************
/// Class : GenerateCommand
/// Author : HUYNH V. Si
/// Created Date :
/// ***************************************************************
///

///This class is used to generate command.
///

public MySqlCommand GenerateCommand(MySqlConnection Connection, MethodInfo Method, Object[] Values)
{
MySqlCommand command = new MySqlCommand();
command.Connection = Connection;
command.CommandText = Method.Name;
command.CommandType = CommandType.StoredProcedure;
ParameterInfo[] methodParameters = Method.GetParameters();
int index = 0;
foreach (ParameterInfo paramInfo in methodParameters)
{
MySqlParameter mySqlPara = new MySqlParameter();
mySqlPara.ParameterName = paramInfo.Name;
//mySqlPara.ParameterName = mySqlPara.ParameterName;
mySqlPara.ParameterName = "?" + mySqlPara.ParameterName;
//mySqlPara.ParameterName = "@" + mySqlPara.ParameterName;
//mySqlPara.ParameterName = mySqlPara.ParameterName;
mySqlPara.Value = Values[index];
command.Parameters.Add(mySqlPara);
index++;
}
command.Parameters.Add(ReturnValueParameterName, MySqlDbType.Int64).Direction = ParameterDirection.ReturnValue;
return command;
}
#endregion
}

convert reader mysql

public MySqlCommand SQLCommand(string commandString)
{
//ConnectData connect = new ConnectData();
connect.Close_con();
connect.open_Con();
MySqlConnection myConnect = connect.NewConnection;
MySqlCommand cmdMySQL = myConnect.CreateCommand();
cmdMySQL.CommandText = commandString;
//connect.Close_con();
return cmdMySQL;
}
public DataTable ConvertDataReaderToDataTable(MySqlDataReader reader)
{

DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount;
int intCounter;
for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));

objDataTable.BeginLoadData();
Object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
connect.Close_con();
return objDataTable;
}
public DataSet ConvertDataReaderToDataSet(MySqlDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table

DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();

if (schemaTable != null)
{
// A query returning records was executed

for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}

dataSet.Tables.Add(dataTable);

// Fill the data table we just created

while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();

for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);

dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned

DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
connect.Close_con();
return dataSet;
}

call produrce mysql 2

public void InsertEmployee(string connect, string spName, int id, string fisrtName, string lastName, string midName, string mail, string code,DateTime date)
{
try
{
OpenConnection(connect);
MySqlCommand cmd = new MySqlCommand(spName, ObjCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("pid", id));
cmd.Parameters.Add(new MySqlParameter("pfirstname", fisrtName));
cmd.Parameters.Add(new MySqlParameter("plastname", lastName));
cmd.Parameters.Add(new MySqlParameter("pmidname", midName));
cmd.Parameters.Add(new MySqlParameter("pemail", mail));
cmd.Parameters.Add(new MySqlParameter("pcode", code));
cmd.Parameters.Add(new MySqlParameter("pdate", date));
cmd.ExecuteNonQuery();
Close_con();
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.ToString());
}
}

public DataSet sp_get_employee()
{
open_Con();
SQLCommandGeneral s = new SQLCommandGeneral();
MySqlCommand myCommand = s.GenerateCommand(ObjCon, (MethodInfo)MethodBase.GetCurrentMethod(), new Object[] { });
MySqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataProcess dprocess = new DataProcess();
return dprocess.ConvertDataReaderToDataSet(result);
}

call produrce mysql

public DataTable CallStoreProcedure(string spName, bool ishptDB)
{
try
{
OpenConnection(ishptDB);
MySqlCommand cmd = new MySqlCommand(spName, ObjCon);
cmd.CommandType = CommandType.StoredProcedure;
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataProcess dProcess = new DataProcess();
DataTable tb = dProcess.ConvertDataReaderToDataTable(dr);
Close_con();
return tb;
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.ToString());
return null;
}
}

public void UpdateEmployee(string connect,string spName,int id,string fisrtName,string lastName,string midName,string mail,string code)
{
try
{
OpenConnection(connect);
MySqlCommand cmd = new MySqlCommand(spName, ObjCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("pid", id));
cmd.Parameters.Add(new MySqlParameter("pfirstname", fisrtName));
cmd.Parameters.Add(new MySqlParameter("plastname", lastName));
cmd.Parameters.Add(new MySqlParameter("pmidname", midName));
cmd.Parameters.Add(new MySqlParameter("pemail", mail));
cmd.Parameters.Add(new MySqlParameter("pcode", code));
cmd.ExecuteNonQuery();
Close_con();
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.ToString());
}
}

connect mysql 2

private string GetConnectionString(bool isphtDB)
{
try
{
string conString = "";
if (!isphtDB)
{
conString = ConfigurationSettings.AppSettings["HRConnectionString"];
}
else
{
conString = ConfigurationSettings.AppSettings["PHTConnectionString"];
}
return conString;
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.ToString());
return ConString;
}
}
public void OpenConnection(bool isphtDB)
{
try
{
ObjCon = new MySqlConnection(GetConnectionString(isphtDB));
ObjCon.Open();
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.ToString());
}

}
public void OpenConnection(string conString)
{
try
{
ObjCon = new MySqlConnection(conString);
ObjCon.Open();
}
catch (Exception e)
{
System.Diagnostics.Debug.Write(e.ToString());
}
}

connect mysql

public MySqlConnection NewConnection
{
get { return ObjCon; }
}
public void open_Con()
{
if (ObjCon.State == ConnectionState.Closed || ObjCon.State == ConnectionState.Broken)
{
ObjCon = new MySqlConnection(ConString);
ObjCon.Open();
}
}

public void Close_con()
{
try
{
ObjCon.Close();
ObjCon.Dispose();
}
catch
{
ObjCon.Close();
ObjCon.Dispose();
}
}

config

public ConnectData()
{
//
// TODO: Add constructor logic here
//
}
// string connection
public MySqlConnection ObjCon = new MySqlConnection();
private string ConString = ConfigurationSettings.AppSettings["PHTConnectionString"];

Home

Decoration Tips

XML

Create XML

DateTime

array? string ? split functions? C#