I create a SqlDataAdapter
after a fill it to Dataset
. My question is that after insert I want to get the IDENTITY
value for primary column. For example a give buttonedit1 editvalue is Id
(this is my primary column) after insert I want to get Identity value in ButtonEdit1 text.
Can I make this unless use SQL command like Select @IDentity
thanks.
public void Form1_Load(object sender,EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(@"ConString");
con.Open();
adap = new SqlDataAdapter("select Id,Caption from SKMenu where ID=-1",con);
adap.Fill(ds, "Table");
bs.DataSource = ds.Tables["Table"];
buttonEdit1.DataBindings.Add("Text", bs, "Id");
buttonEdit2.DataBindings.Add("Text", bs, "Caption");
bs.AddNew();
}
private void button1_Click(object sender, EventArgs e)
{
SqlCommandBuilder cv = new SqlCommandBuilder(adap);
bs.EndEdit();
adap.Update(ds.Tables["Table"]);
}
public static bool CreateEntity(object entity, out long id)
{
bool created = false;
long newid = -1;
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection(Provider.Connection()))
{
string sqlcreate = "select * from {0} where id = -1;";
conn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(String.Format(sqlcreate, entity.GetType().UnderlyingSystemType.Name), conn))
{
using (SqlCommandBuilder build = new SqlCommandBuilder(da))
{
using (DataSet ds = new DataSet())
{
da.Fill(ds);
DataRow dr = ds.Tables[0].NewRow();
ClassProperties.Update(entity, dr);
da.InsertCommand = build.GetInsertCommand();
da.InsertCommand.CommandText += ";SELECT SCOPE_IDENTITY()";
da.InsertCommand.Parameters.Clear();
for (int i = 1; i < dr.ItemArray.Length; i++)
{
da.InsertCommand.Parameters.AddWithValue("@p" + i, dr.ItemArray[i]);
}
var result = da.InsertCommand.ExecuteScalar();
if (result != null)
{
created = true;
newid = Convert.ToInt64(result);
}
}
}
}
}
id = newid;
return created;
}