//查询 public static DataSet select(string tableName,string whereStr) { string sql = "select * from "+tableName+whereStr; try { return DbHelp.ExecSql(sql); } catch (Exception e1) { return null; } } //删除单条 public static int delete (string tableName,string whereStr) { string sql = "delete from "+tableName+whereStr; try { return DbHelp.ExecSql(sql); } catch (Exception e1) { return 0; } } //删除多条 public static string deltes(DataTable dt) { int i = 0; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) //遍历行 { string tableName = dt.TableName; string pk = dt.Columns[0].ColumnName.ToString(); //获取第一列名(主键列) string pkValue = row[0].ToString(); //获取第一行的(主键值) string sql="delete from "+tableNmae+" where "+pk+" = '"+pkValue+"'"; try { i = i + DbHelp.ExecSql(sql); } catch (Exception e1) { return e1.Massage.ToString()+sql; } } return "删除"+i.ToString()+"条信息"; } else { return "删除0行"; } } //遍历dataTable新增
public static string add(DataTable dt) { int k = 0; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) //遍历行 { string tableName = dt.TableName; string pk = dt.Columns[0].ColumnName.ToString(); //获取第一列名(主键列) string pkValue = row[0].ToString(); //获取第一行的(主键值) string sql = "insert into " + tableName + "("; string sql1 = pk; string sql2 = "'" + pkValue + "'"; for (int i = 1; i < dt.Columns.Count; i++) //从1开始,因为第一列为主键值 { sql1 = sql1 + "," + dt.Columns[i].ColumnName.ToString(); //拼列明 sql2 = sql2 + ",'" + row[i].ToString() + "'"; //拼值 //sql1 = sql1 + dt.Columns[i].ColumnName + "= '" + row[i].ToString() + "',";//列名+列值 } sql = sql + sql1 + ") values(" + sql2 + ")"; try { DB.DataHelper dtHelper = new DB.DataHelper(); k = k + dtHelper.ExecuteSql(sql); } catch (Exception e1) { return e1.Message.ToString() + sql; } } return "新增" + k.ToString() + "条信息"; } return "新增0条信息"; }
//遍历dataTable修改
public static string upd(DataTable dt) { int k = 0; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) //遍历行 { string tableName = dt.TableName; string pk = dt.Columns[0].ColumnName.ToString(); //获取第一列名(主键列) string pkValue = row[0].ToString(); //获取第一行的(主键值) string sql = "update " + tableName + " set "; string sql1 = ""; for (int i = 1; i < dt.Columns.Count; i++) //从1开始,因为第一列为主键值 { sql1 = sql1 + dt.Columns[i].ColumnName + "= '" + row[i].ToString() + "',";//列名+列值 }
sql = sql + sql1.Substring(0, sql1.Length - 1) + " where " + pk + " = '" + pkValue + "'"; try { DB.DataHelper dtHelper = new DB.DataHelper(); k = k + dtHelper.ExecuteSql(sql); } catch (Exception e1) { return e1.Message.ToString() + sql; } } return "修改" + k.ToString() + "条信息"; } return "修改0条信息"; }