Home > C# and dot NET > SqlCommand Object

SqlCommand Object

SqlCommand digunakan untuk interaksi dengan database . Sebagai contoh, kita dapat melakukan operasi Select, Insert, Update, dan Delete  data dalam tabel database.

Connection string detail :

<connectionStrings>
<add name="AdoTraining" connectionString="Persist Security Info=False;User ID=*****;Password=*****;Initial     Catalog=AdoTraining;Server=localhost"/>
</connectionStrings>

Example:


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);

CommandDeleteData();

Console.WriteLine("-------------");
Console.WriteLine("Insert Data");
CommandInsertData();

Console.WriteLine("-------------");
Console.WriteLine("Select Data");
CommandSelectData();

Console.WriteLine("-------------");
Console.WriteLine("Update Data");
CommandUpdateData();
CommandSelectData();

Console.WriteLine("-------------");
Console.WriteLine("Delete Data");
CommandDeleteData();

}
private static void TestConnection()
{
connection.Open();
Console.WriteLine("Test Connection Success\n");
connection.Close();
}

private static void CommandInsertData()
{
try
{
connection.Open();

string sqlInsert = "insert into Employee(Id, FirstName, LastName, Email) values(@Id, @FirstName, @LastName, @Email)";
SqlCommand insertCommand = new SqlCommand();
insertCommand.Connection = connection;
insertCommand.CommandText = sqlInsert;

insertCommand.Parameters.Add("@Id",SqlDbType.Int).Value = 1;
insertCommand.Parameters.Add("@FirstName",SqlDbType.NVarChar).Value="Kukuh";
insertCommand.Parameters.Add("@LastName",SqlDbType.NVarChar).Value="Utama";
insertCommand.Parameters.Add("@Email",SqlDbType.NVarChar).Value="kukuh2utama@gmail.com";
insertCommand.ExecuteNonQuery();
Console.WriteLine("Insert Success");

}
finally
{
if (connection != null)
{
connection.Close();
}
}

}

private static void CommandSelectData()
{
try
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("select Id, FirstName, LastName, Email from Employee");
selectCommand.Connection = connection;
dataReader = selectCommand.ExecuteReader();

while (dataReader.Read())
{
Console.WriteLine("Record: " + dataReader[0] + " " + dataReader[1] + " " + dataReader[2]);
}
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}

if (connection != null)
{
connection.Close();
}
}

}

private static void CommandUpdateData()
{
try
{
connection.Open();

string updateSql = "update Employee Set FirstName=@FirstName, LastName=@LastName, Email=@Email where Id=@Id";
SqlCommand updateCommand = new SqlCommand(updateSql);
updateCommand.Connection = connection;

updateCommand.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
updateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = "Fitria";
updateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = "Utama";
updateCommand.Parameters.Add("@Email", SqlDbType.NVarChar).Value = "fitria2utama@gmail.com";
updateCommand.ExecuteNonQuery();
Console.WriteLine("Update Success");
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}

private static void CommandDeleteData()
{
try
{
connection.Open();

string deleteSql = "delete from Employee where Id=@Id";
SqlCommand deleteCommand = new SqlCommand(deleteSql);
deleteCommand.Connection = connection;

deleteCommand.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
deleteCommand.ExecuteNonQuery();
Console.WriteLine("Delete Success");
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}

}
}

Screen Capture

SqlCommand

SqlCommand


 

Referensi : dari berbagai sumber.

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: