/// <summary>
/// 增、删、改操作数据库的方法（存储过程）
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="VariableName">参数集合</param>
/// <param name="Values">值集合</param>
/// <returns>返回操作状态（true成功，false失败）</returns>
public bool ExecProcedure(string ProcedureName, string[] VariableName, object[] Values)
{
bool Bool = true;
try
{
MySqlCommand cmd = new MySqlCommand(ProcedureName, conn);
for (int i = 0; i < VariableName.Length; i++)
{
cmd.Parameters.Add("?" + VariableName[i], Values[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
Open();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
/// <summary>
/// 查询操作数据库的方法（存储过程）
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="VariableName">参数集合</param>
/// <param name="Values">值集合</param>
/// <returns>返回DataTable的集合</returns>
public DataTable ExecProcedureSelect(string ProcedureName, string[] VariableName, object[] Values)
{
DataTable dt = new DataTable();
try
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
SqlCommand cmd = new SqlCommand(ProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < VariableName.Length; i++)
{
cmd.Parameters.AddWithValue("@" + VariableName[i], Values[i]);
}
da.SelectCommand = cmd;
da.Fill(dt);
da.Dispose();
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
return(dt);
}
public bool Delete_W(object obj, string Where)
{
string TableName;
Type t = obj.GetType();
TableName = t.Name;
bool Bool = true;
try
{
MySqlCommand cmd = new MySqlCommand("delete from " + TableName + " " + Where, conn);
Open();
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
Close();
}
return(Bool);
}
/// <summary>
/// 得到自增列的信息
/// </summary>
/// <param name="TableName">表名</param>
/// <returns></returns>
public DataTable GetIncrementColumn(string TableName)
{
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("select so.name Table_name,sc.name Column_name,");
sb.Append("ident_current(so.name) curr_value,");
sb.Append("ident_incr(so.name)incr_value,"); //增量
sb.Append("ident_seed(so.name) seed_value "); //种子
sb.Append("from sysobjects so inner join syscolumns sc ");
sb.Append("on so.id=sc.id and columnproperty(sc.id,sc.name,'IsIdentity')=1 where upper(so.name)=upper('" + TableName + "')");
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlDataAdapter da = new SqlDataAdapter(sb.ToString(), connection))
{
da.Fill(dt);
da.Dispose();
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
return(dt);
}
public bool Insert(string TableName, string[] Fields, object[] Values)
{
bool Bool = true;
StringBuilder sb_Str = new StringBuilder();
StringBuilder sb_Field = new StringBuilder();
StringBuilder sb_Sql = new StringBuilder();
try
{
for (int i = 0; i < Fields.Length; i++)
{
sb_Field.Append(Fields[i]);
sb_Field.Append(",");
sb_Str.Append("@");
sb_Str.Append(Fields[i]);
sb_Str.Append(",");
}
sb_Field.Remove(sb_Field.Length - 1, 1);
sb_Str.Remove(sb_Str.Length - 1, 1);
sb_Sql.Append("insert into ");
sb_Sql.Append(TableName);
sb_Sql.Append("(");
sb_Sql.Append(sb_Field);
sb_Sql.Append(") values(");
sb_Sql.Append(sb_Str);
sb_Sql.Append(")");
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sb_Sql.ToString(), connection))
{
for (int i = 0; i < Fields.Length; i++)
{
cmd.Parameters.Add("@" + Fields[i], Values[i]);
}
try
{
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
public bool Open()
{
ReadXml();
conn = new SqlConnection();
//conn.ConnectionString = "server=SQLOLEDB.1;Persist Security Info=True;User ID=" + username + ";pwd=" + password + ";Initial Catalog=" + catalog + ";Data Source=" + server;
conn.ConnectionString = @"server=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=YY_DB;Data Source=.";
conn.Open();
DataBase_State = "数据库打开成功！[" + DateTime.Now.ToString() + "]\r\n";
if (conn.State == ConnectionState.Closed)
{
try
{
conn.Open();
DataBase_State = "数据库打开成功！[" + DateTime.Now.ToString() + "]\r\n";
return(true);
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
DataBase_State = "数据库打开失败！[" + DateTime.Now.ToString() + "]\r\n";
return(false);
}
}
return(true);
}
public DataTable Select(string TableName, string[] Fields, string Where)
{
DataTable dt = new DataTable();
StringBuilder sb_Sql = new StringBuilder();
StringBuilder sb_field = new StringBuilder();
for (int i = 0; i < Fields.Length; i++)
{
sb_field.Append(Fields[i]);
sb_field.Append(",");
}
sb_field.Remove(sb_field.Length - 1, 1);
sb_Sql.Append("select ");
sb_Sql.Append(sb_field.ToString());
sb_Sql.Append(" from ");
sb_Sql.Append(TableName);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
try
{
using (SqlDataAdapter da = new SqlDataAdapter(sb_Sql.ToString(), conn))
{
da.Fill(dt);
da.Dispose();
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
return(dt);
}
/// <summary>
/// 删除操作数据库的方法
/// </summary>
/// <param name="sqlDel">删除操作的SQL语句</param>
/// <returns>返回操作状态（true成功，false失败）</returns>
public bool Delete(string sqlDel)
{
bool Bool = true;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sqlDel, connection))
{
try
{
//Open();
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
//Close();
}
catch (Exception ex)
{
connection.Close();
SystemError.SystemLog(ex.Message);
Bool = false;
}
}
}
return(Bool);
}
public static IList <T> Select(string TableName, string[] Fields, string Where)
{
DataTable dt = new DataTable();
StringBuilder sb_Sql = new StringBuilder();
StringBuilder sb_field = new StringBuilder();
for (int i = 0; i < Fields.Length; i++)
{
sb_field.Append(Fields[i]);
sb_field.Append(",");
}
sb_field.Remove(sb_field.Length - 1, 1);
sb_Sql.Append("select ");
sb_Sql.Append(sb_field.ToString());
sb_Sql.Append(" from ");
sb_Sql.Append(TableName);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sb_Sql.ToString(), new MySqlConnection()); //MySqlData.conn
da.Fill(dt);
da.Dispose();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
return(ModelConvertHelper <T> .ConvertToModel(dt));
}
public bool Delete(object obj, string KeyField)
{
string TableName;
string Where = "";
Type t = obj.GetType();
TableName = t.Name;
MemberInfo[] memberInfot = t.GetMembers();
ArrayList Fields = new ArrayList();
ArrayList Values = new ArrayList();
PropertyInfo propertyInfo;
object Tempobj;
foreach (MemberInfo var in memberInfot)
{
if (var.MemberType == MemberTypes.Property)
{
propertyInfo = t.GetProperty(var.Name);
Fields.Add(propertyInfo.Name);
Tempobj = propertyInfo.GetValue(obj, null);
if (Tempobj == null)
{
Tempobj = DBNull.Value;
}
Values.Add(Tempobj);
if (KeyField == propertyInfo.Name)
{
Where = " where " + KeyField + "='" + Tempobj + "'";
}
}
}
bool Bool = true;
try
{
MySqlCommand cmd = new MySqlCommand("delete from " + TableName + " " + Where, conn);
Open();
cmd.ExecuteNonQuery();
Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
Close();
}
return(Bool);
}
public bool Update(string TableName, string[] Fields, object[] newValues, string Where)
{
bool Bool = true;
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append("Update ");
sb_Sql.Append(TableName);
sb_Sql.Append(" set ");
for (int i = 0; i < Fields.Length; i++)
{
sb_Sql.Append(Fields[i]);
sb_Sql.Append("=:");
sb_Sql.Append(Fields[i]);
sb_Sql.Append(",");
}
sb_Sql.Remove(sb_Sql.Length - 1, 1);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(sb_Sql.ToString(), connection))
{
SystemError.SystemLog(sb_Sql.ToString());
try
{
StringBuilder sb_Param = new StringBuilder();
for (int i = 0; i < Fields.Length; i++)
{
cmd.Parameters.Add(":" + Fields[i], newValues[i]);
}
//Open();
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
//cmd.Parameters.Clear();
//Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message + "更新");
connection.Close();
Bool = false;
}
}
}
return(Bool);
}
public void Close()
{
if (conn.State == ConnectionState.Open)
{
try
{
conn.Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
}
}
}
//返回第一行第一列（一般用于自增列）需要转换成int型
public object Insert_ReturnVal(string TableName, string[] Fields, object[] Values)
{
object obj = null;
StringBuilder sb_Str = new StringBuilder();
StringBuilder sb_Field = new StringBuilder();
StringBuilder sb_Sql = new StringBuilder();
try
{
for (int i = 0; i < Fields.Length; i++)
{
sb_Field.Append(Fields[i]);
sb_Field.Append(",");
sb_Str.Append("@");
sb_Str.Append(Fields[i]);
sb_Str.Append(",");
}
sb_Field.Remove(sb_Field.Length - 1, 1);
sb_Str.Remove(sb_Str.Length - 1, 1);
sb_Sql.Append("insert into ");
sb_Sql.Append(TableName);
sb_Sql.Append("(");
sb_Sql.Append(sb_Field);
sb_Sql.Append(") values(");
sb_Sql.Append(sb_Str);
sb_Sql.Append(")");
SqlCommand cmd = new SqlCommand(sb_Sql.ToString(), conn);
for (int i = 0; i < Fields.Length; i++)
{
cmd.Parameters.Add("@" + Fields[i], Values[i]);
}
//Open();
obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
//Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
}
return(obj);
}
/// <summary>
/// 查询操作数据库的方法（存储过程）
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <returns>返回DataTable的集合</returns>
public DataTable ExecProcedureSelect(string ProcedureName)
{
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter(ProcedureName, conn);
da.Fill(dt);
da.Dispose();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
return(dt);
}
public IList <T> Select <T>(string TableName, string[] Fields, string Where) where T : new()
{
DataTable dt = new DataTable();
StringBuilder sb_Sql = new StringBuilder();
StringBuilder sb_field = new StringBuilder();
for (int i = 0; i < Fields.Length; i++)
{
sb_field.Append(Fields[i]);
sb_field.Append(",");
}
sb_field.Remove(sb_field.Length - 1, 1);
sb_Sql.Append("select ");
sb_Sql.Append(sb_field.ToString());
sb_Sql.Append(" from ");
sb_Sql.Append(TableName);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlDataAdapter da = new SqlDataAdapter(sb_Sql.ToString(), connection))
{
da.Fill(dt);
da.Dispose();
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
IList <T> List = null;
if (dt != null)
{
List = SqlModelConvertHelper <T> .ConvertToModel(dt);
}
return(List);
}
/// <summary>
/// 删除操作数据库的方法
/// </summary>
/// <param name="sqlDel">删除操作的SQL语句</param>
/// <returns>返回操作状态（true成功，false失败）</returns>
public bool Delete(string sqlDel)
{
bool Bool = true;
try
{
SqlCommand cmd = new SqlCommand(sqlDel, conn);
//Open();
cmd.ExecuteNonQuery();
//Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
public DataTable Select(string sql)
{
DataTable dt = new DataTable();
try
{
OracleDataAdapter da = new OracleDataAdapter(sql, conn);
da.Fill(dt);
da.Dispose();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
return(dt);
}
public bool Open()
{
DataBase_State = "数据库打开成功！[" + DateTime.Now.ToString() + "]\r\n";
if (conn.State == ConnectionState.Closed)
{
try
{
conn.Open();
DataBase_State = "数据库打开成功！[" + DateTime.Now.ToString() + "]\r\n";
return(true);
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
DataBase_State = "数据库打开失败！[" + DateTime.Now.ToString() + "]\r\n";
return(false);
}
}
return(true);
}
public bool Open(string server, string catalog, string username, string password)
{
connectionString = "server=SQLOLEDB.1;Persist Security Info=True;User ID=" + username + ";pwd=" + password + ";Initial Catalog=" + catalog + ";Data Source=" + server + ";MultipleActiveResultSets=true";
conn.ConnectionString = connectionString;
if (conn.State == ConnectionState.Closed)
{
try
{
conn.Open();
DataBase_State = "数据库打开成功！[" + DateTime.Now.ToString() + "]\r\n";
return(true);
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
DataBase_State = "数据库打开失败！[" + DateTime.Now.ToString() + "]\r\n";
return(false);
}
}
return(true);
}
public bool Update(string TableName, string[] Fields, object[] newValues, string Where)
{
bool Bool = true;
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append("Update ");
sb_Sql.Append(TableName);
sb_Sql.Append(" set ");
for (int i = 0; i < Fields.Length; i++)
{
sb_Sql.Append(Fields[i]);
sb_Sql.Append("=?");
sb_Sql.Append(Fields[i]);
sb_Sql.Append(",");
}
sb_Sql.Remove(sb_Sql.Length - 1, 1);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
try
{
MySqlCommand cmd = new MySqlCommand(sb_Sql.ToString(), conn);
StringBuilder sb_Param = new StringBuilder();
for (int i = 0; i < Fields.Length; i++)
{
cmd.Parameters.Add("?" + Fields[i], newValues[i]);
}
Open();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
Close();
}
return(Bool);
}
public bool Insert <T>(string TableName, T model) where T : new()
{
DataTable dt = new DataTable();
bool Bool = true;
StringBuilder sb_Str = new StringBuilder();
StringBuilder sb_Field = new StringBuilder();
StringBuilder sb_Sql = new StringBuilder();
System.Reflection.PropertyInfo[] entityPropertites = typeof(T).GetProperties();
try
{
foreach (var item in entityPropertites)
{
sb_Field.Append(item.Name);
sb_Field.Append(",");
sb_Str.Append(":");
sb_Str.Append(item.Name);
sb_Str.Append(",");
}
sb_Field.Remove(sb_Field.Length - 1, 1);
sb_Str.Remove(sb_Str.Length - 1, 1);
sb_Sql.Append("insert into ");
sb_Sql.Append(TableName);
sb_Sql.Append("(");
sb_Sql.Append(sb_Field);
sb_Sql.Append(") values(");
sb_Sql.Append(sb_Str);
sb_Sql.Append(")");
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(sb_Sql.ToString(), connection))
{
foreach (var item in entityPropertites)
{
if (item.GetValue(model, null) == null)
{
cmd.Parameters.Add(":" + item.Name, DBNull.Value);
}
else
{
if (item.PropertyType.Name == "Boolean")
{
if (item.GetValue(model, null).ToString().ToUpper() == "TRUE")
{
cmd.Parameters.Add(":" + item.Name, OracleDbType.Decimal).Value = 1;
}
else
{
cmd.Parameters.Add(":" + item.Name, OracleDbType.Decimal).Value = 0;
}
}
else
{
cmd.Parameters.Add(":" + item.Name, item.GetValue(model, null));
}
}
}
try
{
connection.Open();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
connection.Close();
}
catch (Exception e)
{
connection.Close();
throw e;
}
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
public bool Update <T>(string TableName, T model, string Where) where T : new()
{
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("select so.name Table_name,sc.name Column_name,");
sb.Append("ident_current(so.name) curr_value,");
sb.Append("ident_incr(so.name)incr_value,"); //增量
sb.Append("ident_seed(so.name) seed_value "); //种子
sb.Append("from sysobjects so inner join syscolumns sc ");
sb.Append("on so.id=sc.id and columnproperty(sc.id,sc.name,'IsIdentity')=1 where upper(so.name)=upper('" + TableName + "')");
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlDataAdapter da = new SqlDataAdapter(sb.ToString(), connection))
{
da.Fill(dt);
da.Dispose();
}
}
bool Bool = true;
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append("Update ");
sb_Sql.Append(TableName);
sb_Sql.Append(" set ");
System.Reflection.PropertyInfo[] entityPropertites = typeof(T).GetProperties();
try
{
bool b = false;
foreach (var item in entityPropertites)
{
b = false;
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["column_name"].ToString() == item.Name)
{
b = true; break;
}
}
if (!b)
{
sb_Sql.Append(item.Name);
sb_Sql.Append("=@");
sb_Sql.Append(item.Name);
sb_Sql.Append(",");
}
}
}
sb_Sql.Remove(sb_Sql.Length - 1, 1);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sb_Sql.ToString(), connection))
{
try
{
foreach (var item in entityPropertites)
{
if (item.GetValue(model, null) == null)
{
cmd.Parameters.AddWithValue("@" + item.Name, DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@" + item.Name, item.GetValue(model, null));
}
}
//Open();
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
//cmd.Parameters.Clear();
//Close();
}
catch (Exception e)
{
connection.Close();
throw e;
}
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
public int Insert_1(string TableName, string[] Fields, object[] Values)
{
int Bool = 1;
StringBuilder sb_Str = new StringBuilder();
StringBuilder sb_Field = new StringBuilder();
StringBuilder sb_Sql = new StringBuilder();
try
{
for (int i = 0; i < Fields.Length; i++)
{
sb_Field.Append(Fields[i]);
sb_Field.Append(",");
sb_Str.Append("@");
sb_Str.Append(Fields[i]);
sb_Str.Append(",");
}
sb_Field.Remove(sb_Field.Length - 1, 1);
sb_Str.Remove(sb_Str.Length - 1, 1);
sb_Sql.Append("insert into ");
sb_Sql.Append(TableName);
sb_Sql.Append("(");
sb_Sql.Append(sb_Field);
sb_Sql.Append(") values(");
sb_Sql.Append(sb_Str);
sb_Sql.Append(")");
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sb_Sql.ToString(), connection))
{
for (int i = 0; i < Fields.Length; i++)
{
if (Values[i] == null)
{
cmd.Parameters.AddWithValue("@" + Fields[i], DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@" + Fields[i], Values[i]);
}
}
try
{
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
catch (System.Data.SqlClient.SqlException ee)
{
SystemError.SystemLog(ee.Message);
Bool = 0;
if (ee.Number == 2627)
{
Bool = 2;
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = 0;
}
return(Bool);
}
public int Insert_1(string TableName, string[] Fields, object[] Values)
{
int Bool = 1;
StringBuilder sb_Str = new StringBuilder();
StringBuilder sb_Field = new StringBuilder();
StringBuilder sb_Sql = new StringBuilder();
try
{
for (int i = 0; i < Fields.Length; i++)
{
sb_Field.Append(Fields[i]);
sb_Field.Append(",");
sb_Str.Append(":");
sb_Str.Append(Fields[i]);
sb_Str.Append(",");
}
sb_Field.Remove(sb_Field.Length - 1, 1);
sb_Str.Remove(sb_Str.Length - 1, 1);
sb_Sql.Append("insert into ");
sb_Sql.Append(TableName);
sb_Sql.Append("(");
sb_Sql.Append(sb_Field);
sb_Sql.Append(") values(");
sb_Sql.Append(sb_Str);
sb_Sql.Append(")");
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(sb_Sql.ToString(), connection))
{
for (int i = 0; i < Fields.Length; i++)
{
cmd.Parameters.Add(":" + Fields[i], Values[i]);
}
try
{
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
catch (Oracle.ManagedDataAccess.Client.OracleException e)
{
SystemError.SystemLog(e.Message + "执行插入操作");
connection.Close();
throw e;
}
}
}
}
catch (Oracle.ManagedDataAccess.Client.OracleException ee)
{
SystemError.SystemLog(ee.Message + "主键");
Bool = 0;
if (ee.ErrorCode == -2147467259)
{
Bool = 2;
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message + "插入");
Bool = 0;
}
return(Bool);
}
public bool Update_W(object obj, string Where)
{
string TableName;
Type t = obj.GetType();
TableName = t.Name;
MemberInfo[] memberInfot = t.GetMembers();
ArrayList Fields = new ArrayList();
ArrayList Values = new ArrayList();
PropertyInfo propertyInfo;
object Tempobj;
foreach (MemberInfo var in memberInfot)
{
if (var.MemberType == MemberTypes.Property)
{
propertyInfo = t.GetProperty(var.Name);
Fields.Add(propertyInfo.Name);
Tempobj = propertyInfo.GetValue(obj, null);
if (Tempobj == null)
{
Tempobj = DBNull.Value;
}
Values.Add(Tempobj);
}
}
#region //实体类中自增列去掉
StringBuilder sb_Str_IsIdentity = new StringBuilder();
sb_Str_IsIdentity.Append("Select Field=a.name ");
sb_Str_IsIdentity.Append("FROM syscolumns a ");
sb_Str_IsIdentity.Append("inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
sb_Str_IsIdentity.Append("where d.name='" + TableName + "' and COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 ");
DataTable dt = new DataTable();
new MySqlDataAdapter(sb_Str_IsIdentity.ToString(), conn).Fill(dt);
if (dt.Rows.Count > 0)
{
int Index = Fields.IndexOf(dt.Rows[0]["Field"].ToString());
Fields.Remove(dt.Rows[0]["Field"].ToString());
Values.Remove(Values[Index]);
}
#endregion
bool Bool = true;
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append("Update ");
sb_Sql.Append(TableName);
sb_Sql.Append(" set ");
for (int i = 0; i < Fields.Count; i++)
{
sb_Sql.Append(Fields[i]);
sb_Sql.Append("=?");
sb_Sql.Append(Fields[i]);
sb_Sql.Append(",");
}
sb_Sql.Remove(sb_Sql.Length - 1, 1);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
try
{
MySqlCommand cmd = new MySqlCommand(sb_Sql.ToString(), conn);
StringBuilder sb_Param = new StringBuilder();
for (int i = 0; i < Fields.Count; i++)
{
cmd.Parameters.Add("?" + Fields[i], Values[i]);
}
Open();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Close();
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
public bool Update <T>(string TableName, T model, string Where) where T : new()
{
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append(" show columns from " + TableName);
sb.Append(" where Extra='auto_increment'");
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter da = new MySqlDataAdapter(sb.ToString(), connection))
{
da.Fill(dt);
da.Dispose();
}
}
bool Bool = true;
StringBuilder sb_Sql = new StringBuilder();
sb_Sql.Append("Update ");
sb_Sql.Append(TableName);
sb_Sql.Append(" set ");
System.Reflection.PropertyInfo[] entityPropertites = typeof(T).GetProperties();
try
{
bool b = false;
foreach (var item in entityPropertites)
{
b = false;
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["Field"].ToString() == item.Name)
{
b = true; break;
}
}
if (!b)
{
sb_Sql.Append(item.Name);
sb_Sql.Append("=?");
sb_Sql.Append(item.Name);
sb_Sql.Append(",");
}
}
}
sb_Sql.Remove(sb_Sql.Length - 1, 1);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(sb_Sql.ToString(), connection))
{
try
{
foreach (var item in entityPropertites)
{
if (item.GetValue(model, null) == null)
{
cmd.Parameters.Add("?" + item.Name, DBNull.Value);
}
else
{
cmd.Parameters.Add("?" + item.Name, item.GetValue(model, null));
}
}
//Open();
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
//cmd.Parameters.Clear();
//Close();
}
catch (Exception e)
{
connection.Close();
throw e;
}
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
Bool = false;
}
return(Bool);
}
