commandinfo mysql dbhelper_通用DBHelper类 | 学步园

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

using System.Reflection;

namespace ClassLibrary1

{

public class DBhelper

{

private static SqlConnection conn = null;

///

/// 返回数据库连接字符串的方法

///

/// 返回数据库连接字符串

private SqlConnection Conn()

{

//string connString = ConfigurationManager.AppSettings[""].ToString();

string connString = ConfigurationManager.ConnectionStrings["sql"].ToString();

if (conn == null)

{

if (connString == null)

{

connString = string.Format(@"server = 192.168.0.100/sa ; uid = HeartFly ; pwd = HeartFly ; database = HeartFly");

}

conn = new SqlConnection(connString);

conn.Open();

}

else if (conn.State == System.Data.ConnectionState.Closed)

{

conn.Open();

}

else if (conn.State == System.Data.ConnectionState.Broken)

{

conn.Close();

conn.Open();

}

return conn;

}

#region 执行SQL，返回受影响的行数

///

/// 执行SQL，返回受影响的行数

/// 适用于单条数据的添加、删除、修改

///

///

///

///

public static int ExecuteCommand(string sql, params SqlParameter[] p)

{

using (SqlCommand comm = new SqlCommand(sql, Conn()))

{

if(p != null)

{

comm.Parameters.AddRange(p);

}

return comm.ExecuteNonQuery();

}

}

#endregion

#region 执行SQL，返回受影响的行数

///

/// 执行SQL，返回受影响的行数

/// 适用于单条数据的添加、删除、修改

///

///

///

public static int ExecuteCommand(string sql)

{

using (SqlCommand comm = new SqlCommand(sql, Conn()))

{

return comm.ExecuteNonQuery();

}

}

#endregion

#region 执行查询，返回第一行的第一列

///

/// 执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他列或行

///

///

///

///

public static int GetScalar(string sql, params SqlParameter[] p)

{

using (SqlCommand comm = new SqlCommand(sql,Conn()))

{

if (p != null)

{

comm.Parameters.AddRange(p);

}

return  Convert.ToInt32(comm.ExecuteScalar());

}

}

#endregion

#region 执行查询，返回第一行的第一列

///

/// 执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他列或行

///

///

///

public static int GetScalar(string sql)

{

using (SqlCommand comm = new SqlCommand(sql, Conn()))

{

return Convert.ToInt32(comm.ExecuteScalar());

}

}

#endregion

#region 查询返回泛型集合

///

/// 返回泛型集合

///

/// 集合的类型

/// T-SQL语句

///

public static IList SelectIn(string sql)

{

using (SqlDataAdapter sda = new SqlDataAdapter(sql, Conn()))

{

try

{

DataSet ds = new DataSet();

sda.Fill(ds);

return DataSetToIList(ds.Tables[0]);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

return null;

}

finally

{

conn.Close();

}

}

}

#endregion

#region 将DataSet转换成IList集合

private static IList DataSetToIList(DataTable dt)

{

if (dt == null)

{

return null;

}

if (dt.Rows.Count < 0)

{

return null;

}

IList list = new List();

for (int i = 0; i < dt.Rows.Count; i++)

{

T _t = Activator.CreateInstance();

PropertyInfo[] propertys = _t.GetType().GetProperties();

foreach (PropertyInfo item in propertys)

{

for (int j = 0; j < dt.Columns.Count; j++)

{

if (item.Name.ToLower().Equals(dt.Columns[j].ColumnName.ToLower()))

{

if (dt.Rows[i][j] != DBNull.Value)

{

item.SetValue(_t, dt.Rows[i][j], null);

}

else

{

item.SetValue(_t, null, null);

}

break;

}

}

}

list.Add(_t);

}

return list;

}

#endregion

#region

///

/// 将一个数据表转换成一个JSON字符串

///

/// 需要转换的表。

///

public  string DataTableToJson(DataTable source)

{

if (source.Rows.Count == 0)

return "";

StringBuilder sb = new StringBuilder("[");

foreach (DataRow row in source.Rows)

{

sb.Append("[");

for (int i = 0; i < source.Columns.Count; i++)

{

sb.Append('"' + row[i].ToString() + "/",");

}

sb.Remove(sb.Length - 1, 1);

sb.Append("],");

}

sb.Remove(sb.Length - 1, 1);

sb.Append("]");

return sb.ToString();

}

///

/// DataTable转换为json，以{"属性":"值"}的形式

///

/// 数据表

/// JSON字符串

public  string CreateJsonParameters(DataTable dt)

{

StringBuilder JsonString = new StringBuilder();

if (dt != null && dt.Rows.Count > 0)

{

JsonString.Append("{ ");

JsonString.Append("/"TableInfo/":[ ");

for (int i = 0; i < dt.Rows.Count; i++)

{

JsonString.Append("{ ");

for (int j = 0; j < dt.Columns.Count; j++)

{

if (j < dt.Columns.Count - 1)

{

JsonString.Append("/"" + dt.Columns[j].ColumnName.ToString() + "/":" + "/"" + dt.Rows[i][j].ToString() + "/",");

}

else if (j == dt.Columns.Count - 1)

{

JsonString.Append("/"" + dt.Columns[j].ColumnName.ToString() + "/":" + "/"" + dt.Rows[i][j].ToString() + "/"");

}

}

if (i == dt.Rows.Count - 1)

{

JsonString.Append("} ");

}

else

{

JsonString.Append("}, ");

}

}

JsonString.Append("]}");

return JsonString.ToString();

}

else

{

return null;

}

}

#endregion

#region 将 list集合转换成json字符串

///

/// 将 list集合转换成json字符串

///此方法比较繁琐，我们直接应用System.Web.Script.Serialization命名空间，

///然后调用JavaScriptSerializer类的Serialize方法，此方法可以将object类型的数据转换为json类型

///

///

///

///

public string CreateListToJson(List list)

{

StringBuilder sbString = new StringBuilder();

sbString.Append("[{");

if (list == null || list.Count <= 0)

{

return "";

}

else

{

foreach (T item in list)

{

PropertyInfo[] process = item.GetType().GetProperties();

foreach (PropertyInfo temp in process)

{

sbString.Append("/"" + temp.Name.ToString() + ":/"" + temp.GetValue(item, null) + "/",");

}

sbString.Remove(sbString.Length - 1, 1);

sbString.Append("},");

}

sbString.Remove(sbString.Length - 1, 1);

sbString.Append("]");

return sbString.ToString();

}

}

#endregion

#region

///

/// 将list集合转换为xml

///

/// 任意类型

/// 任意类型的集合

public static void ListToXml(List list)

{

if (list == null || list.Count <= 0)

{

return ;

}

else

{

XmlDocument xdoc = new XmlDocument();

XmlDeclaration xd = xdoc.CreateXmlDeclaration("1.0", "GBK", null);

xdoc.AppendChild(xd);

XmlElement fele = xdoc.CreateElement("ListToXml"); //根节点

for (int i = 0; i < list.Count; i++)

{

PropertyInfo[] property = list[i].GetType().GetProperties();

XmlElement zele = xdoc.CreateElement("Model-"+ (i+1));//集合中的对象

for (int j = 0; j < property.Length; j++)

{

XmlElement sele = xdoc.CreateElement(property[j].Name.ToString());

sele.InnerText = property[j].GetValue(list[i], null).ToString();

//sele.SetAttribute(property[j].Name, property[j].GetValue(list[i], null).ToString());

zele.AppendChild(sele);

}

fele.AppendChild(zele);

}

xdoc.AppendChild(fele);

xdoc.Save(AppDomain.CurrentDomain.BaseDirectory + "/ListToXml.xml");

}

}

#endregion

#region 同时执行多条sql语句

///

/// 同时执行多条sql语句

/// 可同时进行数据的添加、删除、修改等操作

///

/// sql语句集合

/// 都执行则返回ture，否则返回false

public bool method(IList sqllist)

{

using (SqlCommand comm = new SqlCommand())

{

comm.Connection = conn;

SqlTransaction st = null;

try

{

st = Conn().BeginTransaction();

comm.Transaction = st;

foreach (string sql in sqllist)

{

comm.CommandText = sql;

comm.ExecuteNonQuery();

}

st.Commit();

return true;

}

catch (Exception)

{

st.Rollback();

return false;

}

finally

{

conn.Close();

}

}

}

#endregion

}

}