对数据库的操作其实可以很简单
DataTable dt = new CustomDataTable("MyTable1");
DataRow dr = DT.GetNewRow(true);
dr[0] = "sadfas";
DT.UpData();
GridBind();
就这样轻而易举的对数据进行操作
/// <summary>
/// 自定义DataTable,封装对数据库的操作,只处理select 语句
/// </summary>
public class CustomDataTable:DataTable
{
private SQLServer sqlServer;
SqlDataAdapter sqlDataAdapter;
//private CustomDataTable(){}
private void Init(string SQLString)
{
sqlServer = SQLServer.Instance();
sqlDataAdapter = new SqlDataAdapter(SQLString,sqlServer.Connection);
SqlCommandBuilder custCB = new SqlCommandBuilder(sqlDataAdapter);
sqlServer.Connection.Open();
try
{
sqlDataAdapter.FillSchema(this,SchemaType.Source);
sqlDataAdapter.Fill(this);
}
finally
{
sqlServer.Connection.Close();
}
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="SQLString">Select 语句或表名</param>
public CustomDataTable(string SQLString)
{
string SQL = SQLString.Trim().ToUpper();
if(SQL.StartsWith("SELECT"))
{
Init(SQL);
}
else
{
Init("SELECT * FROM "+SQL);
this.TableName = TableName;
}
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Condition">条件</param>
public CustomDataTable(string TableName,string Condition)
{
string SQL = "SELECT * FROM " + TableName ;
if(Condition != null && Condition != "" && Condition.Trim() != "")
SQL += " WHERE " + Condition;
Init(SQL);
this.TableName = TableName;
}
/// <summary>
/// 提交
/// </summary>
public void UpData()
{
try
{
sqlServer.Connection.Open();
sqlServer.BeginTransaction();
sqlDataAdapter.SelectCommand.Transaction = sqlServer.SqlTran;
sqlDataAdapter.Update(this);
sqlServer.CommitTransaction();
}
catch
{
sqlServer.RollbackTransaction();
throw;
}
finally
{
sqlServer.Connection.Close();
}
}
/// <summary>
/// 取新行
/// </summary>
/// <param name="FlagAdd">标志位,True表示新行加入到表中,False反之</param>
/// <returns>新行的引用</returns>
public DataRow GetNewRow(bool FlagAdd)
{
DataRow dr = this.NewRow();
if(FlagAdd)
this.Rows.Add(dr);
return dr;
}
/// <summary>
/// 添加新行
/// </summary>
/// <param name="dr">新行</param>
public void AddNewRow(DataRow dr)
{
this.Rows.Add(dr);
}
/// <summary>
/// 获取一行
/// </summary>
/// <param name="Index">索引</param>
/// <returns>行的引用</returns>
public DataRow GetRow(int Index)
{
return this.Rows[Index];
}
/// <summary>
/// 获取行集
/// </summary>
/// <param name="Condition">条件</param>
/// <returns>行集</returns>
public DataRow[] GetRows(string Condition)
{
return this.Select(Condition,null,DataViewRowState.CurrentRows);
}
/// <summary>
/// 删除一行
/// </summary>
/// <param name="Index"></param>
public void DeleteRow(int Index)
{
this.Rows[Index].Delete();
}
/// <summary>
/// 删除行集
/// </summary>
/// <param name="Condition">条件</param>
public void DeleteRows(string Condition)
{
foreach(DataRow dr in GetRows(Condition))
dr.Delete();
}
/// <summary>
/// 删除表中所有记录
/// </summary>
public void Delete()
{
foreach(DataRow dr in this.Select(null,null,DataViewRowState.CurrentRows))
dr.Delete();
}
/// <summary>
/// 设置主键
/// </summary>
/// <param name="Keys">键</param>
public void SetPrimaryKey(params string[] Keys)
{
DataColumn [] dc = new DataColumn[Keys.Length];
for(int i=0;i<Keys.Length;i++)
{
dc[i] = this.Columns[Keys[i]];
}
this.PrimaryKey = dc;
}
}