If I wish to add some information to my SQL Server database, must I开发者_JAVA技巧 do it through a DataSet
and a DataAdapter
?
The idea is that if my database has 1-2 million entries, isn't my memory going to be occupied unnecessary with the 1-2 mil rows in the DataSet
considering that I want to add only one row? Is there an alternative ?
If you're only inserting a row, that needn't fetch anything into the DataSet
/DataAdapter
. You add the row, submit the changes, and the relevant INSERT command will be executed.
You could always create a plain old ADO.NET parametrized SqlCommand
holding a simple SQL INSERT statement, and provide parameters, and load the data that way (nothing needs to be loaded, doesn't matter how many rows you already have - it will just work):
string insertStmt = "INSERT INTO dbo.YourTable(col1, col2, ...., colN) " +
"VALUES(@Value1, @Value2, ...., @ValueN)";
using(SqlConnection _con = new SqlConnection(-your-connection-string-here))
using(SqlCommand _cmdInsert = new SqlCommand(insertStmt, _con))
{
// define the parameters for your query
_cmdInsert.Parameters.Add("@Value1", SqlDbType.Int);
.......
// set the values
_cmdInsert.Parameters["@Value1"].Value = 4711;
.....
_con.Open();
int rowsInserted = _cmdInsert.ExecuteNonQuery();
_con.Close();
}
If you have multiple rows to insert, you could loop over e.g. a list of objects, set the values for our _cmdInsert
for each object, and execute the _cmdInsert.ExecuteNonQuery()
for each row.
Of course, if you use something like an ORM (NHibernate, Linq-to-SQL, Entity Framework), that work might get infinitely easier - just insert new objects into your collection and save them - the ORM will deal with all the nitty-gritty details (and basically do this code I showed above and execute it - more or less).
精彩评论