如题 下面是代码,可以插入新的记录。但是无法删除。
public class Messages : IDisposable
{
//
// The DataSetCommand object
//
private SqlDataAdapter dsCommand; private SqlCommand selectcommand;
private SqlCommand insertcommand;
private SqlCommand deletecommand;
private SqlCommand updatecommand;
private const string MESSAGEID = "@MessageId";
private const string SENDERID = "@SenderId";
private const string RECEIVERID = "@ReceiverId";
private const string TITLE = "@Title";
private const string CONTENT = "@Content";
private const string STATE = "@State";
private const string TYPE = "@Type";
public Messages()
{
//
// Create the adapter
//
dsCommand = new SqlDataAdapter();
dsCommand.TableMappings.Add("Table", Userinfo.USERS_TABLE);
} public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true); // as a service to those who might inherit from us
}
/// <summary>
/// BuildGetMessageByUserNumIdCommands: Initialize the parameterized Select command for the DataAdapter
/// </summary>
/// <returns></returns>
private SqlCommand GetMessageByUserNumIdcommand()
{
if ( selectcommand == null )
{
//
// Construct the command since we don't have it already
//
selectcommand = new SqlCommand("GetMessageByUserNumId",new SqlConnection (AgencyConfiguration.ConnectionString));
selectcommand.CommandType = CommandType.StoredProcedure;
selectcommand.Parameters.Add(new SqlParameter(SENDERID, SqlDbType.Int));
}
return selectcommand;
} private SqlCommand GetInsertMessageCommand()
{
if ( insertcommand == null )
{
insertcommand = new SqlCommand("InsertMessage",new SqlConnection("server=(local);Integrated Security=SSPI;database=agency"));
insertcommand.CommandType = CommandType.StoredProcedure;
SqlParameterCollection sqlParams = insertcommand.Parameters;
sqlParams.Add(new SqlParameter(SENDERID, SqlDbType.Int));
sqlParams.Add(new SqlParameter(RECEIVERID, SqlDbType.Int));
sqlParams.Add(new SqlParameter(TITLE, SqlDbType.VarChar,50));
sqlParams.Add(new SqlParameter(CONTENT, SqlDbType.NText));
sqlParams.Add(new SqlParameter(STATE, SqlDbType.Bit));
sqlParams.Add(new SqlParameter(TYPE, SqlDbType.TinyInt));
// Define the parameter mappings from the data table in the
// dataset.
//
sqlParams[SENDERID].SourceColumn = Messageinfo.SENDERID_FIELD;
sqlParams[RECEIVERID].SourceColumn =Messageinfo.RECEIVEID_FIELD;
sqlParams[CONTENT].SourceColumn = Messageinfo.CONTENT_FIELD;
sqlParams[TITLE].SourceColumn = Messageinfo.TITLE_FIELD;
sqlParams[STATE].SourceColumn = Messageinfo.STATE_FIELD;
sqlParams[TYPE].SourceColumn = Messageinfo.TYPE_FIELD;
}
return insertcommand;
}
/// <summary>
/// Sub DeleteAllMessageByUsercommand: Initialize the parameterized Delete command for the DataAdapter
/// </summary>
/// <returns></returns>
private SqlCommand GetDeleteAllMessageByUsercommand()
{
if ( deletecommand == null )
{
//
// Construct the command since we don't have it already
//
deletecommand = new SqlCommand("DeleteAllMessageByUser",new SqlConnection ("server=(local);Integrated Security=SSPI;database=agency"));
deletecommand.CommandType = CommandType.StoredProcedure; SqlParameterCollection sqlParams = deletecommand.Parameters; sqlParams.Add(new SqlParameter(SENDERID, SqlDbType.Int)); sqlParams[SENDERID].SourceVersion = DataRowVersion.Original;
sqlParams[SENDERID].SourceColumn = Messageinfo.SENDERID_FIELD;
}
return deletecommand;
} public bool InsertMessage(Messageinfo data)
{
if ( dsCommand == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
dsCommand.InsertCommand = GetInsertMessageCommand();
dsCommand.Update(data, Messageinfo.MESSAGE_TABLE);
//
// Check for table errors to see if the update failed.
//
if ( data.HasErrors )
{
data.Tables[Messageinfo.MESSAGE_TABLE].GetErrors()[0].ClearErrors();
return false;
}
else
{
data.AcceptChanges();
return true;
}
}
public bool DeleteAllMessageByUser(Messageinfo message)
{
if ( dsCommand == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
//
// Get the command and update the database
//
//dsCommand.SelectCommand = GetMessageByUserNumIdcommand();
dsCommand.DeleteCommand = GetDeleteAllMessageByUsercommand();
//dsCommand.DeleteCommand.Parameters[SENDERID] = message.Tables[Messageinfo.MESSAGE_TABLE].Columns[Messageinfo.SENDERID_FIELD];
dsCommand.Update(message, Messageinfo.MESSAGE_TABLE);
//
// Check for table errors to see if the update failed.
//
if ( message.HasErrors )
{
message.Tables[Messageinfo.MESSAGE_TABLE].GetErrors()[0].ClearErrors();
return false;
}
else
{
message.AcceptChanges();
return true;
}
} }
}调用删除的方式后 提示错误
未处理的“System.InvalidOperationException”类型的异常出现在 system.data.dll 中。其他信息: 当传递具有新行的 DataRow 集合时,更新要求有效的 InsertCommand。
public class Messages : IDisposable
{
//
// The DataSetCommand object
//
private SqlDataAdapter dsCommand; private SqlCommand selectcommand;
private SqlCommand insertcommand;
private SqlCommand deletecommand;
private SqlCommand updatecommand;
private const string MESSAGEID = "@MessageId";
private const string SENDERID = "@SenderId";
private const string RECEIVERID = "@ReceiverId";
private const string TITLE = "@Title";
private const string CONTENT = "@Content";
private const string STATE = "@State";
private const string TYPE = "@Type";
public Messages()
{
//
// Create the adapter
//
dsCommand = new SqlDataAdapter();
dsCommand.TableMappings.Add("Table", Userinfo.USERS_TABLE);
} public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true); // as a service to those who might inherit from us
}
/// <summary>
/// BuildGetMessageByUserNumIdCommands: Initialize the parameterized Select command for the DataAdapter
/// </summary>
/// <returns></returns>
private SqlCommand GetMessageByUserNumIdcommand()
{
if ( selectcommand == null )
{
//
// Construct the command since we don't have it already
//
selectcommand = new SqlCommand("GetMessageByUserNumId",new SqlConnection (AgencyConfiguration.ConnectionString));
selectcommand.CommandType = CommandType.StoredProcedure;
selectcommand.Parameters.Add(new SqlParameter(SENDERID, SqlDbType.Int));
}
return selectcommand;
} private SqlCommand GetInsertMessageCommand()
{
if ( insertcommand == null )
{
insertcommand = new SqlCommand("InsertMessage",new SqlConnection("server=(local);Integrated Security=SSPI;database=agency"));
insertcommand.CommandType = CommandType.StoredProcedure;
SqlParameterCollection sqlParams = insertcommand.Parameters;
sqlParams.Add(new SqlParameter(SENDERID, SqlDbType.Int));
sqlParams.Add(new SqlParameter(RECEIVERID, SqlDbType.Int));
sqlParams.Add(new SqlParameter(TITLE, SqlDbType.VarChar,50));
sqlParams.Add(new SqlParameter(CONTENT, SqlDbType.NText));
sqlParams.Add(new SqlParameter(STATE, SqlDbType.Bit));
sqlParams.Add(new SqlParameter(TYPE, SqlDbType.TinyInt));
// Define the parameter mappings from the data table in the
// dataset.
//
sqlParams[SENDERID].SourceColumn = Messageinfo.SENDERID_FIELD;
sqlParams[RECEIVERID].SourceColumn =Messageinfo.RECEIVEID_FIELD;
sqlParams[CONTENT].SourceColumn = Messageinfo.CONTENT_FIELD;
sqlParams[TITLE].SourceColumn = Messageinfo.TITLE_FIELD;
sqlParams[STATE].SourceColumn = Messageinfo.STATE_FIELD;
sqlParams[TYPE].SourceColumn = Messageinfo.TYPE_FIELD;
}
return insertcommand;
}
/// <summary>
/// Sub DeleteAllMessageByUsercommand: Initialize the parameterized Delete command for the DataAdapter
/// </summary>
/// <returns></returns>
private SqlCommand GetDeleteAllMessageByUsercommand()
{
if ( deletecommand == null )
{
//
// Construct the command since we don't have it already
//
deletecommand = new SqlCommand("DeleteAllMessageByUser",new SqlConnection ("server=(local);Integrated Security=SSPI;database=agency"));
deletecommand.CommandType = CommandType.StoredProcedure; SqlParameterCollection sqlParams = deletecommand.Parameters; sqlParams.Add(new SqlParameter(SENDERID, SqlDbType.Int)); sqlParams[SENDERID].SourceVersion = DataRowVersion.Original;
sqlParams[SENDERID].SourceColumn = Messageinfo.SENDERID_FIELD;
}
return deletecommand;
} public bool InsertMessage(Messageinfo data)
{
if ( dsCommand == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
dsCommand.InsertCommand = GetInsertMessageCommand();
dsCommand.Update(data, Messageinfo.MESSAGE_TABLE);
//
// Check for table errors to see if the update failed.
//
if ( data.HasErrors )
{
data.Tables[Messageinfo.MESSAGE_TABLE].GetErrors()[0].ClearErrors();
return false;
}
else
{
data.AcceptChanges();
return true;
}
}
public bool DeleteAllMessageByUser(Messageinfo message)
{
if ( dsCommand == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
//
// Get the command and update the database
//
//dsCommand.SelectCommand = GetMessageByUserNumIdcommand();
dsCommand.DeleteCommand = GetDeleteAllMessageByUsercommand();
//dsCommand.DeleteCommand.Parameters[SENDERID] = message.Tables[Messageinfo.MESSAGE_TABLE].Columns[Messageinfo.SENDERID_FIELD];
dsCommand.Update(message, Messageinfo.MESSAGE_TABLE);
//
// Check for table errors to see if the update failed.
//
if ( message.HasErrors )
{
message.Tables[Messageinfo.MESSAGE_TABLE].GetErrors()[0].ClearErrors();
return false;
}
else
{
message.AcceptChanges();
return true;
}
} }
}调用删除的方式后 提示错误
未处理的“System.InvalidOperationException”类型的异常出现在 system.data.dll 中。其他信息: 当传递具有新行的 DataRow 集合时,更新要求有效的 InsertCommand。
不如通过适配器来自动生成这些代码。
rowstate(行状态)是不是deleted
Adapter的Insert/Update/DeletedCommand 之间要有联系么?
出去一下 等下回来看
http://community.csdn.net/Expert/topic/3902/3902309.xml?temp=6.228274E-02