特にinsertした後などPHPだと
mysql_insert_id()
を使うと楽だったのだが、
.netではそのような便利な関数が用意されていない。
今まではそのままselectしていたが、
mysql自体に直近のオートインクリメント値を取得する関数があるのを発見したのでメモ。
C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings("hoge"); DbProviderFactory factory = DbProviderFactories.GetFactory(setting.ProviderName); using (DbConnection db = factory.CreateConnection) { db.ConnectionString = setting.ConnectionString; DbCommand comm = factory.CreateCommand; comm.CommandText = "INSERT INTO poster (title,body) VALUES (?,?)"; comm.Connection = db; db.Open(); comm.ExecuteNonQuery(); //ここでクエリを投げる DbCommand comm2 = factory.CreateCommand; comm2.CommandText = "SELECT LAST_INSERT_ID()"; comm2.Connection = db; int pid = ""; DbDataReader reader = comm2.ExecuteReader; if (reader.Read) { //格納 pid = reader.GetValue(0); } } |
VB.NET
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Dim setting As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("hoge") Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(setting.ProviderName) Using db As DbConnection = factory.CreateConnection db.ConnectionString = setting.ConnectionString Dim comm As DbCommand = factory.CreateCommand comm.CommandText = "INSERT INTO poster (title,body) VALUES (?,?)" comm.Connection = db db.Open() comm.ExecuteNonQuery() 'ここでクエリを投げる Dim comm2 As DbCommand = factory.CreateCommand comm2.CommandText = "SELECT LAST_INSERT_ID()" comm2.Connection = db Dim pid As Integer = "" Dim reader As DbDataReader = comm2.ExecuteReader If reader.Read Then '格納 pid = reader.GetValue(0) End If End Using |
