Stored Prosedure Kullanılarak Insert İşlemini Gerçekleştirme
Merhaba arkadaşlar, bu makalemizde Stored Procedure kullanarak Employees tablosuna personel kayıt işlemini gerçekleştireceğiz.
Stored Procedure
ALTER PROCEDURE dbo.insertEmployee
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate datetime,
@City varchar(50),
@Country varchar(50),
@id int output
AS
/* SET NOCOUNT ON */
BEGIN
SET NOCOUNT ON;
INSERT INTO Employees (FirstName, LastName, BirthDate, City, Country)
VALUES(@FirstName, @LastName, @BirthDate, @City, @Country)
SET @id=SCOPE_IDENTITY()
RETURN @id
END
aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnInsert_Click(object sender, EventArgs e)
{
string str = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\northwind.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
SqlConnection baglan = new SqlConnection(str);
SqlCommand komut = new SqlCommand();
komut.CommandType = CommandType.StoredProcedure;
komut.CommandText = "insertEmployee";
komut.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtAd.Text.Trim();
komut.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtSoyad.Text.Trim();
komut.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtTarih.Text.Trim();
komut.Parameters.Add("@City", SqlDbType.VarChar).Value = txtSehir.Text.Trim();
komut.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtUlke.Text.Trim();
komut.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
komut.Connection = baglan;
try
{
baglan.Open();
komut.ExecuteNonQuery();
string id = komut.Parameters["@id"].Value.ToString();
Label6.Text = "Personel kayıtı başarılı olarak kaydedildi. Personel Kayıt No= " + id;
}
catch (Exception ex)
{
throw ex;
}
finally
{
baglan.Close();
baglan.Dispose();
}
}
}
aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Ad:"></asp:Label>
<asp:TextBox ID="txtAd" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="Soyad:"></asp:Label>
<asp:TextBox ID="txtSoyad" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label3" runat="server" Text="D.Tarih:"></asp:Label>
<asp:TextBox ID="txtTarih" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label4" runat="server" Text="Şehir:"></asp:Label>
<asp:TextBox ID="txtSehir" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label5" runat="server" Text="Ülke:"></asp:Label>
<asp:TextBox ID="txtUlke" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label6" runat="server" Text="Label"></asp:Label>
<br />
<asp:Button ID="btnInsert" runat="server" onclick="btnInsert_Click"
Text="Kayıt Ekle" />
</div>
</form>
</body>
</html>
Bir makalenin daha sonuna geldik. Bir sonraki makalede görüşmek dileğiyle. Hoşçakalın. Bahadır ŞAHİN