c#操作MySQL数据库-Null值的插入及自增主键的插入时获取
注明
本段摘自我的文章:https://blog.csdn.net/qq_39024280/article/details/117113736?spm=1001.2014.3001.5501
因为时常用到所以独立出来方便直接搜索查询
null值的插入
/// <summary>
/// 插入数据(带null)
/// </summary>
public void InsertItWithNull(int bookId, String chapterName, String chapterContent, int chapterIndex)
{
String now = DateTime.Now.ToString("yyyy-MM-dd");
string searchStr = "Insert into chapters value(@id, @bookId, @chapterName, @chapterContent,@now, @chapterIndex)";
MySqlCommand cmd = new MySqlCommand(searchStr, conn);
cmd.Parameters.AddWithValue("@id", DBNull.Value); // 注意c#的null和数据库的null不同, 如果赋值null必须使用DBNull.Value
cmd.Parameters.AddWithValue("@bookId", bookId);
cmd.Parameters.AddWithValue("@chapterName", chapterName);
cmd.Parameters.AddWithValue("@chapterContent", chapterContent);
cmd.Parameters.AddWithValue("@now", now);
cmd.Parameters.AddWithValue("@chapterIndex", chapterIndex);
cmd.ExecuteNonQuery();
}
自增主键的插入时获取
/// <summary>
/// 插入数据(插入带自增主键的数据,并返回自增数据id)
/// </summary>
public long InsertItReturnId(int bookId, String chapterName, String chapterContent, int chapterIndex)
{
String now = DateTime.Now.ToString("yyyy-MM-dd");
string searchStr = "Insert into chapters(book_id, chapter_name, chapter_content,update_date, chapter_index) " +
"value(@bookId, @chapterName, @chapterContent,@now, @chapterIndex)";
MySqlCommand cmd = new MySqlCommand(searchStr, conn);
// 这里存在一个id参数是自增的,如果插入时不定义,系统会自动使其自增填充
cmd.Parameters.AddWithValue("@bookId", bookId);
cmd.Parameters.AddWithValue("@chapterName", chapterName);
cmd.Parameters.AddWithValue("@chapterContent", chapterContent);
cmd.Parameters.AddWithValue("@now", now);
cmd.Parameters.AddWithValue("@chapterIndex", chapterIndex);
cmd.ExecuteNonQuery();
// 这个返回值就是自增值结果
return cmd.LastInsertedId;
}