Home > C# and dot NET > SqlDataAdapter Example

SqlDataAdapter Example

November 16, 2014 Leave a comment Go to comments

SqlDataAdapter menyediakan cara untuk komunikasi antara DataSet dan SQL Database. Berikut ini example dari SqlDataAdapter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace ConsoleApp
{
class Program
{
private static SqlConnection connection;
private static SqlDataReader dataReader = null;
static void Main(string[] args)
{
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["AdoTraining"];
string connectString = setting.ConnectionString;
connection = new SqlConnection(connectString);

insertAdapter();
updateAdapter();
deleteAdapter();
selectAdapter();
}
private static void TestConnection()
{
connection.Open();
Console.WriteLine("Test Connection Success\n");
connection.Close();
}
private static void selectAdapter()
{
SqlCommand selectCommand = new SqlCommand("select * from Employee", connection);
selectCommand.CommandType = System.Data.CommandType.Text;

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;

DataSet employeeDataSet = new DataSet();
adapter.Fill(employeeDataSet, "Employee");

foreach (DataRow employee in employeeDataSet.Tables["Employee"].Rows)
{
Console.WriteLine("{0}: {1}: {2} : {3}", employee["Id"].ToString(), employee["FirstName"].ToString(), employee["LastName"].ToString(), employee["Email"].ToString());
}

}

private static void insertAdapter()
{
SqlDataAdapter adapter = new SqlDataAdapter("select * from Employee", connection);

DataSet employeeDataSet = new DataSet();
adapter.Fill(employeeDataSet, "Employees");

DataRow employee = employeeDataSet.Tables["Employees"].NewRow();
employee["ID"] = 4;
employee["FirstName"] = "Ismail";
employee["Email"] = "IsmailH@rocket.mail.com";
employee["LastName"] = "Hanif";

employeeDataSet.Tables["Employees"].Rows.Add(employee);
SqlCommandBuilder cmdb = new SqlCommandBuilder(adapter);

adapter.Update(employeeDataSet, "Employees");

}

private static void updateAdapter()
{
SqlCommand command = new SqlCommand();
command.CommandText = "update Employee set Email= @Email where Id = @EmployeeId";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;

command.Parameters.Add("@Email", SqlDbType.NVarChar).Value = "HanifaIsmail@gmail.com";
command.Parameters.Add("@EmployeeId", SqlDbType.Int).Value = 4;

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = command;

connection.Open();
adapter.UpdateCommand.ExecuteNonQuery();
connection.Close();

}

private static void deleteAdapter()
{
SqlCommand command = new SqlCommand();
command.CommandText = "delete from Employee where Id> @deleteId";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;

command.Parameters.Add("@deleteId", SqlDbType.Int).Value = 3;

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.DeleteCommand = command;

connection.Open();
adapter.DeleteCommand.ExecuteNonQuery();
connection.Close();

}

}
}

Print Screen

Print Screen


Semoga bermanfaat.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: