public void UpdateDataTable(DataTable dataTable)
{
if(dataTable == null) return;
if(dataTable.Rows.Count<1) return;
//DALQueryBuilder qBuilder = null;
ArrayList dataParams = new ArrayList();
ArrayList keyParams = new ArrayList();
ArrayList keyCols = new ArrayList();
ArrayList cols = new ArrayList();
foreach(DataColumn col in dataTable.PrimaryKey)
{
keyCols.Add(col.ColumnName);
}
foreach(DataColumn col in dataTable.Columns)
{
cols.Add(col.ColumnName);
}
foreach(DataRow row in dataTable.Rows)
{
if(row.RowState == DataRowState.Unchanged)
{
continue;
}
this.keyFieldParameters.Clear();
this.dataFieldParameters.Clear();
dataParams.Clear();
keyParams.Clear();
foreach(string colname in cols)
{
object value = row[colname];
if (value.GetType().IsEnum)
{
try
{
value = Convert.ToInt32(value);
}
catch
{
value = 0;
}
}
if (value.GetType().IsEnum)
{
value = Convert.ToInt32(value);
}
ParamItem item;
this.GetParameterName(colname,out item);
item.Value = value;
DALParameter param = new DALParameter(item.paramName, value);
dataParams.Add(item);
if(dataTable.Columns[colname].DataType.Name == "DateTime")
{
param.Type = "DateTime";
param.Size = 0;
}
if (value.ToString().Length==0)
{
param.Size = 1;
param.Value = "";
}
this.dataFieldParameters.Add(param);
}
foreach(string colname in keyCols)
{
object value = row[colname];
if(row.RowState == DataRowState.Modified)
{
value = row[colname, DataRowVersion.Original];
}
if (value == null || value.ToString().Length==0)
{
throw new Exception("关键字段" + colname + "有空值!");
}
if (value.GetType().IsEnum)
{
value = Convert.ToInt32(value);
}
ParamItem item;
this.GetParameterName(colname,out item);
if(item.placeName.Trim().Length>1)
{
item.placeName += "_w";
item.paramName += "_w";
}
item.Value = value;
DALParameter param = new DALParameter(item.paramName, value);
keyParams.Add(item);
if(dataTable.Columns[colname].DataType.Name == "DateTime")
{
param.Type = "DateTime";
param.Size = 0;
}
this.keyFieldParameters.Add(param);
}
string sql = "";
if (row.RowState == DataRowState.Added)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into " + dataTable.TableName + "(");
StringBuilder vb = new StringBuilder();
vb.Append(" values(");
foreach(ParamItem item in dataParams)
{
sb.Append(item.colName);
sb.Append(", ");
vb.Append(item.placeName);
vb.Append(", ");
}
// foreach(ParamItem item in keyParams)
// {
// sb.Append(item.colName);
// sb.Append(", ");
// vb.Append(item.placeName);
// vb.Append(", ");
// }
sb.Remove(sb.Length - 2, 2);
vb.Remove(vb.Length - 2, 2);
sb.Append(")");
vb.Append(")");
sql = sb.ToString() + vb.ToString();
}
if(row.RowState == DataRowState.Modified)
{
StringBuilder sb = new StringBuilder();
sb.Append("update " + dataTable.TableName + " set ");
StringBuilder vb = new StringBuilder();
vb.Append(" where ");
StringBuilder tmpsb = new StringBuilder();
tmpsb.Append("update " + dataTable.TableName + " set ");
StringBuilder tmpvb = new StringBuilder();
tmpvb.Append(" where ");
foreach(ParamItem item in dataParams)
{
sb.Append(item.colName);
sb.Append("=");
sb.Append(item.placeName);
sb.Append(", ");
tmpsb.Append(item.colName);
tmpsb.Append("=");
tmpsb.Append(item.Value.ToString());
tmpsb.Append(", ");
}
foreach(ParamItem item in keyParams)
{
vb.Append(item.colName);
vb.Append("=");
vb.Append(item.placeName);
vb.Append(" and ");
tmpvb.Append(item.colName);
tmpvb.Append("=");
tmpvb.Append(item.Value.ToString());
tmpvb.Append(" and ");
}
sb.Remove(sb.Length - 2, 2);
vb.Remove(vb.Length - 4, 4);
sql = sb.ToString() + vb.ToString();
//MessageBox.Show(tmpsb.ToString() + tmpvb.ToString());
}
if(sql.Length<1) continue;
// MessageBox.Show(sql);
IDataCommand cmd = this._dataSource.GetTxtCommand(sql);
foreach(DALParameter p in this.dataFieldParameters)
{
cmd.AddParameter(p.Name, p.Type, p.Size, p.Direction, p.Value);
}
if(row.RowState == DataRowState.Modified)
{
foreach(DALParameter p in this.keyFieldParameters)
{
cmd.AddParameter(p.Name, p.Type, p.Size, p.Direction, p.Value);
}
}
cmd.ExecuteNonQuery();
}
}
public void UpdateObjectStoredProcedure(object o, DataTableAttribute dataTable)
{
DALParameter keyParameter = null;
PropertyInfo keyProperty = null;
PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
ClearParameters();
for (int i=0; i < properties.Length; i++)
{
BaseFieldAttribute[] fields = (BaseFieldAttribute[])properties[i].GetCustomAttributes(typeof(BaseFieldAttribute), true);
if (fields.Length > 0)
{
object value = properties[i].GetValue(o, null);
if (value.GetType().IsEnum)
{
value = Convert.ToInt32(value);
}
DALParameter param = new DALParameter("@" + fields[0].ColumnName, value);
DataFieldAttribute dataField = fields[0] as DataFieldAttribute;
if (dataField != null)
{
param.Type = dataField.Type;
if (dataField.Size != 0)
{
param.Size = dataField.Size;
}
}
else if (fields[0] is KeyFieldAttribute)
{
keyProperty = properties[i];
keyParameter = param;
param.Direction = ParameterDirection.InputOutput;
}
AddParameter(param);
}
}
if (keyProperty == null || keyParameter == null)
{
throw new ArgumentException("The object " + o + " doesn't has a KeyField attribute");
}
//ExecSP_NonQuery(dataTable.UpdateStoredProcedure);
IDataCommand cmd = this._dataSource.GetSpCommand(dataTable.UpdateStoredProcedure);
cmd.ExecuteNonQuery();
keyProperty.SetValue(o, Convert.ChangeType(keyParameter.Value, keyProperty.PropertyType), null);
}
public void DeleteDataTable(DataTable dataTable)
{
if(dataTable == null) return;
if(dataTable.Rows.Count<1) return;
//DALQueryBuilder qBuilder = null;
ArrayList keyParams = new ArrayList();
ArrayList keyCols = new ArrayList();
foreach(DataColumn col in dataTable.PrimaryKey)
{
keyCols.Add(col.ColumnName);
}
foreach(DataRow row in dataTable.Rows)
{
this.keyFieldParameters.Clear();
keyParams.Clear();
foreach(string colname in keyCols)
{
object value = row[colname];
if (value == null) continue;
if (value.GetType().IsEnum)
{
value = Convert.ToInt32(value);
}
if (value.ToString().Length==0) continue;
ParamItem item;
this.GetParameterName(colname,out item);
DALParameter param = new DALParameter(item.paramName, value);
keyParams.Add(item);
//param.Type = value.GetType().Name;
//MessageBox.Show("param.Name=" + param.Name + ",param.Value=" + param.Value.ToString());
this.keyFieldParameters.Add(param);
}
string sql = "";
StringBuilder sb = new StringBuilder();
sb.Append("delete from " + dataTable.TableName);
StringBuilder vb = new StringBuilder();
vb.Append(" where ");
foreach(ParamItem item in keyParams)
{
vb.Append(item.colName);
vb.Append("=");
vb.Append(item.placeName);
vb.Append(" and ");
}
vb.Remove(vb.Length - 4, 4);
sql = sb.ToString() + vb.ToString();
if(sql.Length<1) continue;
//MessageBox.Show(sql);
IDataCommand cmd = this._dataSource.GetTxtCommand(sql);
//string s="";
foreach(DALParameter p in this.keyFieldParameters)
{
//MessageBox.Show("p.Name=" + p.Name + ",p.Value=" + p.Value.ToString() + ",p.Type=" + p.Type + ",p.Direction=" + p.Direction.ToString());
cmd.AddParameter(p.Name, p.Type, p.Size, p.Direction, p.Value);
//s += "," + p.Name + "=" + p.Value;
}
//MessageBox.Show(s);
cmd.ExecuteNonQuery();
}
}
public void UpdateObjectSql(object o, DataTableAttribute dataTable)
{
//DALQueryBuilder qBuilder = null;
ArrayList dataParams = new ArrayList();
ArrayList keyParams = new ArrayList();
this.keyFieldParameters.Clear();
this.dataFieldParameters.Clear();
PropertyInfo dbaction = o.GetType().GetProperty("dbaction");
string action = dbaction.GetValue(o, null).ToString();
PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int i=0; i < properties.Length; i++)
{
DataFieldAttribute[] fields = (DataFieldAttribute[])properties[i].GetCustomAttributes(typeof(DataFieldAttribute), true);
if (fields.Length > 0)
{
object value = properties[i].GetValue(o, null);
if (value == null) continue;
if (value.GetType().IsEnum)
{
value = Convert.ToInt32(value);
}
// if (value.ToString().Length==0) continue;
ParamItem item;
this.GetParameterName(fields[0].ColumnName,out item);
DALParameter param = new DALParameter(item.paramName, value);
dataParams.Add(item);
param.Type = fields[0].Type;
if (fields[0].Size != 0)
{
param.Size = fields[0].Size;
}
this.dataFieldParameters.Add(param);
}
}
try
{
for (int i=0; i < properties.Length; i++)
{
KeyFieldAttribute[] fields = (KeyFieldAttribute[])properties[i].GetCustomAttributes(typeof(KeyFieldAttribute), true);
if (fields.Length > 0)
{
object value = properties[i].GetValue(o, null);
if (value == null)
{
throw new Exception("Primary key字段" + fields[0].ColumnName + "不能为空值！！！");
}
if (value.GetType().IsEnum)
{
value = Convert.ToInt32(value);
}
ParamItem item;
this.GetParameterName(fields[0].ColumnName,out item);
DALParameter param = new DALParameter(item.paramName, value);
keyParams.Add(item);
param.Type = fields[0].Type;
if (fields[0].Size != 0)
{
param.Size = fields[0].Size;
}
this.keyFieldParameters.Add(param);
}
}
}
catch(Exception ex)
{
throw new Exception("3213213" + ex.Message);
}
string sql = "";
if (action == "insert")
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into " + dataTable.TableName + "(");
StringBuilder vb = new StringBuilder();
vb.Append(" values(");
foreach(ParamItem item in dataParams)
{
sb.Append(item.colName);
sb.Append(", ");
vb.Append(item.placeName);
vb.Append(", ");
}
foreach(ParamItem item in keyParams)
{
sb.Append(item.colName);
sb.Append(", ");
vb.Append(item.placeName);
vb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
vb.Remove(vb.Length - 2, 2);
sb.Append(")");
vb.Append(")");
sql = sb.ToString() + vb.ToString();
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append("update " + dataTable.TableName + " set ");
StringBuilder vb = new StringBuilder();
vb.Append(" where ");
foreach(ParamItem item in dataParams)
{
sb.Append(item.colName);
sb.Append("=");
sb.Append(item.placeName);
sb.Append(", ");
}
foreach(ParamItem item in keyParams)
{
vb.Append(item.colName);
vb.Append("=");
vb.Append(item.placeName);
vb.Append(" and ");
}
sb.Remove(sb.Length - 2, 2);
vb.Remove(vb.Length - 4, 4);
sql = sb.ToString() + vb.ToString();
}
IDataCommand cmd = this._dataSource.GetTxtCommand(sql);
//MessageBox.Show(sql);
foreach(DALParameter p in this.dataFieldParameters)
{
// MessageBox.Show(p.Name+","+p.Type+","+p.Value.ToString());
cmd.AddParameter(p.Name, p.Type, p.Size, p.Direction, p.Value);
}
foreach(DALParameter p in this.keyFieldParameters)
{
cmd.AddParameter(p.Name, p.Type, p.Size, p.Direction, p.Value);
}
// MessageBox.Show(sql);
cmd.ExecuteNonQuery();
}
public void AddParameter(DALParameter param)
{
Parameters.Add(param);
}
