SqlCommandBuilder在insert的时候会把sqlserver中有默认值的字段置为null,而我想要的是当没维护那些字段时,取sql中的默认值。如:tCustomer表 中有个 fCreateDate字段,其默认值为 getdate()
在用下面代码进行插入数据的时候,fCreateDate字段的值为null。
public test()
{
//获取数据
datatable dt=myBll.getdatetable("select * from tCustomer");
//(在winform的网格中新增了几行)
//保存数据
myBll.SaveDataTable("select * from tCustomer", dt, myTran)
}public DataTable GetDataTable(string sql)
{
//SqlCommand mycm = conn.CreateCommand();
//mycm.CommandText = sql;
DbCommand mycm = CreateCommand(sql, null);
DataSet myds = new DataSet();
myds.CaseSensitive = false; //不区分大小写
DbDataAdapter myda = new SqlDataAdapter();
myda.MissingSchemaAction = MissingSchemaAction.AddWithKey; //可以通过DataTable获得表的PrimaryKey
try
{
myda.SelectCommand = mycm;
myda.Fill(myds, "Table1");
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\n" + sql);
}
finally
{
mycm.Dispose();
myds.Dispose();
myda.Dispose();
}
return myds.Tables[0];
}public void SaveDataTable(string sql, DataTable dt, DbTransaction tran)
{
CheckConnectionIf();
//SqlCommand cmd = new SqlCommand(sql, this._connection);
DbCommand cmd = this.CreateCommand(sql, null); if (tran != null) cmd.Transaction = (DbTransaction)tran;
DbDataAdapter da = new SqlDataAdapter((SqlCommand)cmd);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey; //可以通过DataTable获得表的PrimaryKey
DbCommandBuilder cb = new SqlCommandBuilder((SqlDataAdapter)da);
cb.ConflictOption = ConflictOption.OverwriteChanges; //
cb.SetAllValues = false; //
da.InsertCommand = cb.GetInsertCommand();
try
{
da.Update(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//dt.AcceptChanges();
//return dt;
}
在用下面代码进行插入数据的时候,fCreateDate字段的值为null。
public test()
{
//获取数据
datatable dt=myBll.getdatetable("select * from tCustomer");
//(在winform的网格中新增了几行)
//保存数据
myBll.SaveDataTable("select * from tCustomer", dt, myTran)
}public DataTable GetDataTable(string sql)
{
//SqlCommand mycm = conn.CreateCommand();
//mycm.CommandText = sql;
DbCommand mycm = CreateCommand(sql, null);
DataSet myds = new DataSet();
myds.CaseSensitive = false; //不区分大小写
DbDataAdapter myda = new SqlDataAdapter();
myda.MissingSchemaAction = MissingSchemaAction.AddWithKey; //可以通过DataTable获得表的PrimaryKey
try
{
myda.SelectCommand = mycm;
myda.Fill(myds, "Table1");
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\n" + sql);
}
finally
{
mycm.Dispose();
myds.Dispose();
myda.Dispose();
}
return myds.Tables[0];
}public void SaveDataTable(string sql, DataTable dt, DbTransaction tran)
{
CheckConnectionIf();
//SqlCommand cmd = new SqlCommand(sql, this._connection);
DbCommand cmd = this.CreateCommand(sql, null); if (tran != null) cmd.Transaction = (DbTransaction)tran;
DbDataAdapter da = new SqlDataAdapter((SqlCommand)cmd);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey; //可以通过DataTable获得表的PrimaryKey
DbCommandBuilder cb = new SqlCommandBuilder((SqlDataAdapter)da);
cb.ConflictOption = ConflictOption.OverwriteChanges; //
cb.SetAllValues = false; //
da.InsertCommand = cb.GetInsertCommand();
try
{
da.Update(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//dt.AcceptChanges();
//return dt;
}
一个sql语句不写入fCreateDate字段 另一个包含fCreateDate字段
from sysobjects a
inner join syscolumns b on b.id=a.id
inner join syscomments c on c.id=b.cdefault
where a.name='BOM'
/*
BOM SO ('')
BOM SF ('')
BOM USG (1)
BOM RT (1)
BOM ST (1)
BOM ID (newid())
BOM CrDt (getdate())
*/
select SO='', SF='', USG=1, RT=1, ST=1, ID=newid(), CrDt=getdate()
/*
1 1 1 9DF5E475-7723-4ADA-9ECD-C7575A33C1B7 2010-06-30 20:42:09.653 */
>>shou dong cha ru..
我现在就是这样操作的,但系统大了很难维护的,一不小心就漏了一(两)个字段没填。>>写两个SaveDataTable方法
>>一个sql语句不写入fCreateDate字段 另一个包含fCreateDate字段
这样做太麻烦了,同时也会出现上面的现象。我想实现完全自动化,让自己可以抬头走路,不用老是担心脚下突如其来的拌脚石。
我的数据库结构是经常变的,不想搞级联的一连串修改。用commandbuilder产生的update语句可以只包含改变了的字段,且where条件也是仅主键,这个做得很人性化;
自动产生的delete语句也很听话;
现在就差insert语句了,我想只insert修改过的字段值,没修改的(或设成的null的)字段用sql默认值充之。
--获取数据库中的默认值,返回一行数据
alter proc P_GetDefaultValue(
@table nvarchar(1000)
) as
--默认值
declare @sql nvarchar(1000) set @sql='select '
select @sql=@sql+b.name+'='+c.text+',' --, c.text, b.*
from sysobjects a
inner join syscolumns b on b.id=a.id
inner join syscomments c on c.id=b.cdefault
where a.name=@table
set @sql=@sql+'0 as _'
exec(@sql)
--exec P_GetDefaultValue 'BOM'
GO
exec P_GetDefaultValue 'BOM'
/* 返回结果
SO SF USG RT ST ID CrDt _
---- ---- ----------- ----------- ----------- ------------------------------------ ------------------------------------------------------ -----------
1 1 1 512BCF04-29BD-4F90-9FAD-2BF4D5CFE787 2010-07-01 16:34:27.077 0
*/
//aDataTable.TableNewRow事件
protected virtual void ftDtlNewRow(DataRow row)
{
//(判断条件,获得最大序号) dtlBll.InitNewRow(row);
row["CD"] = this.ftHdrKey;
row["SEQ"] = nMaxSeq + 1;
row["CrUsr"] = ftWinApp.ftLoginInfo.ftUserId;
}//
public override DataRow InitNewRow(DataRow row)
{
SysBll aBll = new SysBll(this.dal);
DataRow defRow = aBll.GetDefaultValue("BOM"); //抓这个表的默认值
ftCommon.FillDataRow(defRow, row); //如果有匹配字段,则替换之 //附加,此内容可以后续填
row["CD"] = ""; //主键1默认值(现在在数据库中把主键也建了默认值,这两行可以移掉了)
row["SEQ"] = 0; //主键2默认值 return row;
}