public class SqlHelpe
{
/// <summary>
/// 建立mysql数据库链接
/// </summary>
/// <returns></returns>
public static MySqlConnection getMySqlCon()
{
// string conn = "Data Source=localhost;User ID=root;Password=123;DataBase=googlesearch;Charset=gbk;";
string conn = "Data Source=192.168.10.80;User ID=admin;Password=admin123;DataBase=googlesearch;Charset=gbk;";
MySqlConnection con = new MySqlConnection(conn);
con.Open();
return con;
}
/// <summary>
/// 建立执行命令语句对象
/// </summary>
/// <param name="sql"></param>
/// <param name="mysql"></param>
/// <returns></returns>
public static MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)
{
MySqlCommand cmd = new MySqlCommand(sql, mysql);
return cmd;
}
public static int ExecuteNonQuery(string sql, MySqlParameter[] pars)
{
MySqlConnection conn = getMySqlCon();
MySqlCommand comm = new MySqlCommand(sql, conn);
if (pars != null)
{
foreach (MySqlParameter p in pars)
comm.Parameters.AddWithValue(p.ParameterName, p.Value);
}
int i = comm.ExecuteNonQuery();
conn.Close();
return i;
}
public static object ExecuteScalar(string sql, MySqlParameter[] pars)
{
MySqlConnection conn = getMySqlCon();
MySqlCommand comm = new MySqlCommand(sql, conn);
if (pars != null)
{
foreach (MySqlParameter p in pars)
comm.Parameters.AddWithValue(p.ParameterName, p.Value);
}
object o = comm.ExecuteScalar();
conn.Close();
return o;
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="sqlString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string sql)
{
using (MySqlConnection conn = getMySqlCon())
{
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
{
try
{
//conn.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
conn.Close();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose(); conn.Close();
}
}
}
}
/// <summary>
/// 查询并获得结果集并遍历
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getResultset(MySqlCommand mySqlCommand)
{
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
Console.WriteLine("编号:" + reader.GetInt32(0) + "|姓名:" + reader.GetString(1) + "|年龄:" + reader.GetInt32(2) + "|学历:" + reader.GetString(3));
}
}
}
catch (Exception)
{
Console.WriteLine("查询失败了!");
}
finally
{
reader.Close();
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getInsert(MySqlCommand mySqlCommand)
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
String message = ex.Message;
Console.WriteLine("插入数据失败了!" + message);
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getUpdate(MySqlCommand mySqlCommand)
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
String message = ex.Message;
Console.WriteLine("修改数据失败了!" + message);
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="mySqlCommand"></param>
public static void getDel(MySqlCommand mySqlCommand)
{
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
String message = ex.Message;
Console.WriteLine("删除数据失败了!" + message);
}
}
}
public static object InsertKSOthers(string Kname, string Kvicinity, string Ktype, string Kformatted_phone_number, string Kformatted_address, string Klat, string Klng, string Kurl, string Kicon, string Kreference, string Kid, string Kinternational_phone_number,
string Kwebsite)
{
string sql = @"insert into KSOthers(Kname,Kvicinity,Ktype,Kformatted_phone_number,Kformatted_address,Klat,Klng,Kurl,Kicon,Kreference,Kid,Kinternational_phone_number,Kwebsite)
values(?Kname,?Kvicinity,?Ktype,?Kformatted_phone_number,?Kformatted_address,?Klat,?Klng,?Kurl,?Kicon,?Kreference,?Kid,?Kinternational_phone_number,?Kwebsite)";
MySqlParameter[] pars = new MySqlParameter[]
{
new MySqlParameter("Kname",Kname),
new MySqlParameter("Kvicinity",Kvicinity),
new MySqlParameter("Ktype",Ktype),
new MySqlParameter("Kformatted_phone_number",Kformatted_phone_number),
new MySqlParameter("Kformatted_address",Kformatted_address),
new MySqlParameter("Klat",Klat),
new MySqlParameter("Klng",Klng),
new MySqlParameter("Kurl",Kurl),
new MySqlParameter("Kicon",Kicon),
new MySqlParameter("Kreference",Kreference),
new MySqlParameter("Kid",Kid),
new MySqlParameter("Kinternational_phone_number",Kinternational_phone_number),
new MySqlParameter("Kwebsite",Kwebsite)
};
//返回受影响行数
//return DAL.ExecuteNonQuery(sql, pars);
return SqlHelpe.ExecuteNonQuery(sql, pars);
}