Glad that i could make a new post again in my blog :D
So, the topic is, The next episode of my database project (see the first post, including my introduction). I will make my database is accessible in application. I have made it with my visual studio 2013 with window form application in c# syntax. Well, i have only made the application for editing the master song table, which is consist of:
(
songID char(5) primary key not null,
songTitle varchar(100) not null,
songArtist varchar(100) not null,
srecordingCompany varchar(100) not null,
songPrice float not null,
)
So, the user could easily input the data with my app :) not only inputting the data, it could update, delete and also show the data that is stored in database with that app.
here is the syntax for my application!
1. Utilities (using class): This code is for connecting the application to the database.
//Utilities
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace hezkii
{
class Utilities
{
public static SqlConnection ConnectToSql(string integratedSecurity, string dataSource,
string initialCatalog, string userID, string password)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"integrated security=" + integratedSecurity + ";" +
"data source=" + dataSource + ";" +
"persist security info=false;" +
"initial catalog=" + initialCatalog + ";" +
"User ID=" + userID + ";" +
"Password=" + password;
//"User ID=user;Password=password";
try
{
conn.Open();
MessageBox.Show("Connected to Database successfully!", "Database connect status:", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source, note: " + ex, "Database connect status:", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
finally
{
//conn.Close();
}
return conn;
}
}
}
2. Songs: the main syntax that cold insert, update, delete, and show the detail of the data in the application and it is all connected to the database
//songs
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace hezkii
{
public partial class song : Form
{
private static SqlConnection conn = null;
public song()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");
if (textBox1.Text != "") // if not empty
{
SqlCommand addSite =
new
SqlCommand("INSERT INTO MasSong (songID, songTitle, songArtist, srecordingCompany, songPrice) VALUES (@songID, @songTitle, @songArtist, @srecordingCompany, @songPrice)", conn);
addSite.Parameters.AddWithValue("@songID", textBox1.Text);
addSite.Parameters.AddWithValue("@songTitle", textBox2.Text);
addSite.Parameters.AddWithValue("@songArtist", textBox3.Text);
addSite.Parameters.AddWithValue("@srecordingCompany", textBox4.Text);
addSite.Parameters.AddWithValue("@songPrice", textBox5.Text);
addSite.ExecuteNonQuery();
MessageBox.Show("Data is successfully saved!");
}
else
{
MessageBox.Show("Empty field is not allowed!");
}
}
private void button2_Click(object sender, EventArgs e)
{
conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");
if (textBox1.Text != "") // if not empty
{
SqlCommand addSite =
new SqlCommand("UPDATE MasSong SET songID = @songID, songTitle = @songTitle, songArtist = @songArtist, srecordingCompany = @srecordingCompany, songPrice = @songPrice WHERE songID = @songID", conn);
addSite.Parameters.AddWithValue("@songID", textBox1.Text);
addSite.Parameters.AddWithValue("@songTitle", textBox2.Text);
addSite.Parameters.AddWithValue("@songArtist", textBox3.Text);
addSite.Parameters.AddWithValue("@srecordingCompany", textBox4.Text);
addSite.Parameters.AddWithValue("@songPrice", textBox5.Text);
addSite.ExecuteNonQuery();
MessageBox.Show("Data is successfully saved!");
}
else
{
MessageBox.Show("Empty field is not allowed!");
}
}
private void button3_Click(object sender, EventArgs e)
{
conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");
if (textBox1.Text != "") // if not empty
{
SqlCommand addSite =
new SqlCommand("DELETE FROM MasSong WHERE SongID = @SongID", conn);
addSite.Parameters.AddWithValue("@songID", textBox1.Text);
addSite.Parameters.AddWithValue("@songTitle", textBox2.Text);
addSite.Parameters.AddWithValue("@songArtist", textBox3.Text);
addSite.Parameters.AddWithValue("@srecordingCompany", textBox4.Text);
addSite.Parameters.AddWithValue("@songPrice", textBox5.Text);
addSite.ExecuteNonQuery();
MessageBox.Show("Data is successfully saved!");
}
else
{
MessageBox.Show("Empty field is not allowed!");
}
}
private void button4_Click(object sender, EventArgs e)
{
showdetail b = new showdetail();
b.Show();
}
}
}
//Form1
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace hezkii
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void songToolStripMenuItem_Click(object sender, EventArgs e)
{
song a = new song();
a.Show();
}
}
}
3. The showdetail: the syntax that required to show the stored data in the showdetail box
//Showdetail
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace hezkii
{
public partial class showdetail : Form
{
private static SqlConnection conn = null;
public showdetail()
{
InitializeComponent();
}
private void fill_my_grid()
{
conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");
//SET SOME PROPERTIES OF DATAGRID:
dataGridView1.AllowUserToAddRows = false;
dataGridView1.ReadOnly = true;
//CREATING HEADER IN GRID:
dataGridView1.ColumnCount = 5;
dataGridView1.Columns[0].Name = "songID";
dataGridView1.Columns[1].Name = "songTitle";
dataGridView1.Columns[2].Name = "songArtist";
dataGridView1.Columns[3].Name = "srecordingCompany";
dataGridView1.Columns[4].Name = "songPrice";
//FILL DATAGRID WITH BARANG
using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM MasSong", conn))
{
DataTable t = new DataTable();
a.Fill(t);
//prepare rows empty space as much as data
dataGridView1.RowCount = t.Rows.Count;
//FILL ALL DATA BARANG INTO GRID
for (int row = 0; row < t.Rows.Count; row++)
{
dataGridView1[0, row].Value = t.Rows[row]["songID"];
dataGridView1[1, row].Value = t.Rows[row]["songTitle"];
dataGridView1[2, row].Value = t.Rows[row]["songArtist"];
dataGridView1[3, row].Value = t.Rows[row]["srecordingCompany"];
dataGridView1[4, row].Value = t.Rows[row]["songPrice"];
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void showdetail_Load(object sender, EventArgs e)
{
fill_my_grid();
}
}
}
That is all of my syntax that i have used. You can free to copy and paste the syntax.
Well again, i made my video also just like last time. I made it with a help of my friends. Thanks guys, hope you have a good life :D
Well, This syntax and the video is honorably dedicated to my lecturer in the Introduction to Database System subject, Mr. Samuel Mahatmaputera and to my teaching assistant in the Introduction to Database System subject, Ms. Sharon Jesica. Thanks for teaching me about the query language of database. This knowledge is very precious for me.
Thanks for reading my post and thanks for having a time to see my video! Goodbye.
(You can contact me from the comment if you have a question based on the tutorial)
Manual link
Application on iTunes database: https://www.youtube.com/watch?v=B-zBYU53hrw&feature=youtu.be
Tidak ada komentar:
Posting Komentar