Tag Archives: tutorial

C# DataGridView Insert/Update/Delete

Posted on by
Reply

A nice tutorial here..

“public partial class DataTrialForm : Form
{
private String connectionString = null;
private SqlConnection sqlConnection = null;
private SqlDataAdapter sqlDataAdapter = null;
private SqlCommandBuilder sqlCommandBuilder = null;
private DataTable dataTable = null;
private BindingSource bindingSource = null;
private String selectQueryString = null;

public DataTrialForm()
{
InitializeComponent();”

“private void DataTraiForm_Load(object sender, EventArgs e)
{
connectionString = ConfigurationManager.AppSettings["connectionString"];
sqlConnection = new SqlConnection(connectionString);
selectQueryString = “SELECT * FROM t_Bill”;

sqlConnection.Open();

sqlDataAdapter = new SqlDataAdapter(selectQueryString, sqlConnection);
sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;

dataGridViewTrial.DataSource = bindingSource;

// if you want to hide Identity column
dataGridViewTrial.Columns[0].Visible = false;
}”

SQL index options

Posted on by
Reply

Here is a brief look at some of the common options for SQL table index.

 create table customer (
            id integer not null auto_increment primary key ,
            //other column headers here ,
            (index)name
);

What can we say about this insert statement? Here are a few things that are top of my head ,

  • It has two index , id and name.
  • id , has following properties , it is an integer , it cannot be null , and it is increased itself automatically.

Well then you might ask what is an SQL index and what does it do? Basically , it functions very much similar to indexes in libraries. It allows locating information with the database much faster just like the books arranged by alphabetical indexes in libraries. If the library is well indexed by alphabet , there is no need to search the whole library for a book , instead you would only need to know the first letter of the book and then go to the section containing it. It makes the process much faster than searching every rows for it.

Pls keep in mind that when you design a database , its best to keep the index numbering away from the information and within your control. What do I mean is that suppose you are designing a table for books , with author’s information and date published and so on. What would the index for such a table be? It is tempted to say ISBN but what if ISBN changes? It has , from 10 to 13 digits. You can’t control it and you may have no choice but to modify your whole database due to change in index from a table. So instead of ISBN , you might simple put 1 , 2 , 3 4 and so on as the index for the book table. It means that whatever the changes to the ISBN in future , there is no need to make much changes since every SQL , every PHP or Java codes will be referencing the internal index numbers and not ISBN.

Hope it clears up indexes in SQL databases as much as I have myself. See ya next time!

Original SQL database example

Posted on by
1

*This sample would no longer be in used anymore. Pls reference to new database.* If you would like to delete a database – the correct term is “drop” a database – here is how


drop database anime;

Simple heh?

——————————————————————————————————————

We are going to build a sample database with a table and some data to be a reference for all the SQL codes we will be using on the site. Lets begin!

The following has been tested on MySQL server version 5.5 and assumed to be correct on all other SQL Databases. If you are using another database and having some error pls leave a comment so we could update the code.


 CREATE DATABASE anime;
 Query OK, 1 row affected (0.00 sec) 

 mysql> GRANT ALL PRIVILEGES ON anime.* TO 'slamdunk'@'localhost' IDENTIFIED BY 'slamdunk';
 Query OK, 0 rows affected (0.05 sec) 

 mysql> FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec) mysql>

Lets go through it line by line shall we? Lets start with the MySQL user password when you start the commandline. Out of the package , it is blank , so just enter. It will do well for home users and developers but definitely for for production servers or any server that is facing the internet.

Next line is about creating the database which follows the syntax of

CREATE DATABASE DATABASE_NAME

ending with “;”. It is to signify that the SQL command has ended. If you comes from C/C++/JAVA background , it should be familiar to you. Also pls take note that some words are capitalized and some are not. Those that are capitalized are reserved words in SQL , same as include , int , printf in other programming languages. You could , if you want , type them in lower-case too , MySQL doesn’t care. Other SQL Servers might so pls check your respective SQL documentation.

Moving on , this line about privileges or permissions , basically who can do what. This follows the syntax of ,

 GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO 'USER_NAME'@'HOST_NAME' IDENTIFIED BY 'USER_PASSWORD';

It is not so straight forward as the first line but simple put , I am giving all privileges , such as create,read,update,delete and so on , on this database and all its tables to this user from this particular host , you can leave this out , authenticated by that password. Phew! Clear now?

Lastly , it is about flushing privileges. Here the quote from the MySQL website itself about this

“Reloads the privileges from the grant tables in the mysql database. On Unix, this also occurs if the server receives a SIGHUP signal.

The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES. ”
http://dev.mysql.com/doc/refman/5.0/en/flush.html

Here is a sample table.

Sample Database Table:

id Name Address Email Sex
1 Monkey D. Luffy Amazon Lily monkey@eastblue Male
2 Uzumaki Naruto Konohagakure naruto@Konohagakure Male
3 Rukia Kuchiki Soul Society rukia@soul.society Female

So how do we enter these into the MySQL Database? First , we need to tell the database what are the datatype of the each column.

  • id = integer
  • name = varchar(30)
  • address = varchar(60)
  • email = varchar(30)
  • sex = varchar(6)

Note that the difference between char and varchar is that when you define a type as char(6) , it is fixed to 6 characters.

Before creating our first table , be sure to select which database to create the table first! This can be done by ,

USE DATABASE_NAME;

or if you have been following along the example , 

USE anime;

Here is the SQL code to create the table.

 CREATE TABLE anime (id INTEGER PRIMARY KEY ,name VARCHAR(30) ,address VARCHAR(60) ,email VARCHAR(30) ,sex VARCHAR(6) );

To enter the information into the table ,

 INSERT INTO anime (id, name, address , email,sex) VALUES (1,'Monkey D. Luffy' ,'Amazon Lily','monkey@eastblue','Male');
 INSERT INTO anime (id, name, address , email,sex) VALUES (2,'Uzumaki Naruto' ,'Konohagakure',' naruto@Konohagakure','Male');
 INSERT INTO anime (id, name, address , email,sex) VALUES (3,'Rukia Kuchiki' ,'Soul Society','rukia@soul.society','Female');

This should have the trick. If you are unable to do for some reason or another , pls kindly leave a comment.

When you are done , try these two commands and see what you get.


DESC anime;
SELECT * FROM anime;

That should give you some ideas of querying. Thanks for following us all the way and hope you had fun!