如何在sqlcommand中使用默认值

我在microsoft newsgroup发的原文：
In Sql Server, I can use syntax to insert a record with default value:
insert table1(a,b,c)values('A','B',default)

But in .Net, if I want to use default value in Adapter.InsertCommand,for
example,

SqlAdapObj.InsertCommand.Parameters.Add(New
SqlClient.SqlParameter("@testField"))
SqlAdapObj.InsertCommand.Parameters("@testField").Value=default

There is no "default" in C# language, so it says error occurred,what const
variable should I use to do so

相关回复：

*************************************************************************

ADO.NET does not know how to handle DEFAULT syntax on its own, but you can
handle this issue by creating a custom SqlCommand that uses the DEFAULT
keyword to load the default value defined for a column. In this case you
would not define the third column (as you have done)--so you only need the
first two parameters defined.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.

**************************************************************************

To support default values you need to add custom code to the table mapper
(<TableName>Collection) class. Usually you will copy the standard insert
method from the <TableName>Collection_Base class and slightly modify it to
support defaults.


Let's say the following standard Insert method and the 'Country' field has a default value:


public virtual void Insert(UserRow value)
{
    string sqlStr = "INSERT INTO [dbo].[User] (" +
        "[ID], " +
        "[Name], " +
        "[Country]" +
        ") VALUES (" +
        _db.CreateSqlParameterName("ID") + ", " +
        _db.CreateSqlParameterName("Name") + ", " +
        _db.CreateSqlParameterName("Country") + ")";
    IDbCommand cmd = _db.CreateCommand(sqlStr);
    AddParameter(cmd, "ID", value.ID);
    AddParameter(cmd, "Name", value.Name);
    AddParameter(cmd, "Country", value.Country);
    cmd.ExecuteNonQuery();
}


To create a method that inserts data with the default Contry value, perform the following steps:
- Copy the Insert methods from <TableName>Collection_Base to <TableName>Collection
- Rename the new method to InsertWithDefaultCountry
- Remove all 'Country' related code from the new method


public virtual void InsertWithDefaultCountry(UserRow value)
{
    string sqlStr = "INSERT INTO [dbo].[User] (" +
        "[ID], " +
        "[Name] " +
        ") VALUES (" +
        _db.CreateSqlParameterName("ID") + ", " +
        _db.CreateSqlParameterName("Name") + ")";
    IDbCommand cmd = _db.CreateCommand(sqlStr);
    AddParameter(cmd, "ID", value.ID);
    AddParameter(cmd, "Name", value.Name);
    cmd.ExecuteNonQuery();
}


The code above inserts a new record with the default 'Country' value, however it does not update the Country property in the value object. If you want to update the value object, you can utilize the RapTier generated GetByPrimaryKey method.


public virtual void InsertWithDefaultCountry(UserRow value)
{
    string sqlStr = "INSERT INTO [dbo].[User] (" +
        "[ID], " +
        "[Name] " +
        ") VALUES (" +
        _db.CreateSqlParameterName("ID") + ", " +
        _db.CreateSqlParameterName("Name") + ")";
    IDbCommand cmd = _db.CreateCommand(sqlStr);
    AddParameter(cmd, "ID", value.ID);
    AddParameter(cmd, "Name", value.Name);
    cmd.ExecuteNonQuery();

    // Update the Country property
    UserRow newValue = GetByPrimaryKey(value.ID);
    value.Country = newValue.Country;
}

so the conclusion is that you have to skip the column name in a parameter if you want it to be updated with default values

--
Vijay Sachan
MCP - ASP.NET