sql or access to vb/c# /??

Huwebes, Pebrero 23, 2012

update del

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Database_Lecture
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}

Form1 _owner;
string f2Snumber;
public Form2(string snumber,Form1 owner)
{
InitializeComponent();
f2Snumber = snumber;
lblStudentNumber.Text = f2Snumber;
_owner = owner;
this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(this.Form2_FormClosing);
}

private void btnSave_Click(object sender, EventArgs e)
{
clsDatabaseManager obj = new clsDatabaseManager();
string queryUpdateRecord = "Update tbl_student_record " +
@" Set [First Name] = '" + txtFirstName.Text + "', [Last Name] = '" + txtLastName.Text + "', " +
@" [Middle Name] = '" + txtMiddleName.Text + "', Age = '" + txtAge.Text + "', Course = '" + cboCourse.Text + "', " +
@" Address = '" + txtAddress.Text + "'" +
@" Where [Student Number] like '" + lblStudentNumber.Text + "'";

obj.execute(queryUpdateRecord);
this.DialogResult = DialogResult.OK;
}

private void btnDelete_Click(object sender, EventArgs e)
{
clsDatabaseManager obj = new clsDatabaseManager();
string queryDeleteRecord = "Delete from tbl_student_record where [Student Number] like '" + lblStudentNumber.Text + "'";
obj.execute(queryDeleteRecord);
this.DialogResult = DialogResult.OK;
}

private void Form2_Load(object sender, EventArgs e)
{
clsDatabaseManager obj = new clsDatabaseManager();
string queryCourse = "Select * from tbl_course";
string queryGetRecordOfSelectedStudentNumber = "Select * from tbl_student_record where [Student Number] like '" + lblStudentNumber.Text + "'";
obj.fillComboBox(cboCourse, queryCourse, ComboBoxStyle.DropDownList);
obj.retrieveData(queryGetRecordOfSelectedStudentNumber,txtFirstName,txtMiddleName ,txtLastName ,txtAge,txtAddress ,cboCourse );

}

private void Form2_FormClosing(object sender, FormClosingEventArgs e)
{
_owner.PerformRefresh();
}

}
}

main form

final lib

Library Management System requirements

Let us define the requirements for our Library Management System (LMS):

Requirements
• This software will allow members to register
• Only registered members will be allowed to lend an item from the system
• User can add items (Books, CD etc) to the system
• System will allow searching for items in the system based on Author name, book name, user name etc

Non requirements
• System will not enforce any security. Anybody who have access to the computer will be able to access this software and perform any operations. There will be no login/password required to access this software.
• System will not generate any alerts if a member is not returning any items
• System will not maintain any inventory.
• System doesn't keep track of damaged items. Users have to manually replace the damaged items
with new ones.
• System will not handle data security and backup. Users have depend on some external or manual backup mechanism to take data backup whenever required.

Design phase

After completing the requirement study and documentation, it is time to design the software based on the requirements. It is easy to start coding without any design and you may end up developing a product which your customers like. But it may not be easy to maintain. Customers may keep changing their mind. Only after seeing the product, they may say 'we want this to behave in a different way'. And it is very hard to say 'NO' to a customer.

First step is identifying all the features you are going to implement in the software. The
requirements document is a good guide in identifying the features. According to the requirements specification, we need to develop the following features in the system:
• Registration
• Item Management
• Lending
• Search for Books
• Search for Members

Registration

This feature allows to add/edit/delete members in the system. We should be able to store
at least the basic information like Name, Address, Email etc of the member. You can add more attributes like phone number, homepage, date of registration etc.

Database structure:

Table Name : Users
Table Schema
Id : Number

Name : String (50)

Address : String (100)

Email : String (50)

DateOfRegistration : DateTime

We have defined 4 fields in the table above. You can add more fields.

Item Management

Before we let a user to lend a book from the library, we need to keep the list of items available in the library. We will develop a feature to add/edit/delete items in the library.

An item (Book, CD etc) in the library can have the following properties:

1. Name
2. Author
3. Total number of books (we may have more than one copy of the same book)

Let us define database schema for this:

Id : Number (Autonumber)
Name : String
Count : Number

You may add more fields like Publisher Name, Book Category etc.

Lending

This feature includes the following:
• Allow a member to take a book from library
• Return a book to the library

We need the following fields:
• Id : Autonumber
• BookId : Number - This is the ID of the book
• UserId : Number - Id of the member who lend the book
• DateOfLend : DateTime
• DateOfReturn : DateTime- this field will filled only at the time of return

This is an important feature and bit more complex than other features we have discussed above. When a user take the book, we will store the Id of the book and Id of the member in the above table. Note that we are not storing the name of the book and name of user. We are just storing the Ids.

It is important to save the exact Book Id and member Id when lending the book. It is a good idea to make those two fields readonly so that user don't need to type them (they might make mistakes if they type the id number). You can provide a feature to search for the books and member. User can search and find the book and member. Once they find in the search screen, user can just select it from the list. When they select a book or member from the search results, you can programmatically populate the Book Id and Member Id fields.

The search buttons for Book Id and member Id should open another window where user can search for Books and Members and select one.When selected in the search window, the selected Id should be populated in this form.

Search for Books

User should be able to search for books by Book Name, author name etc. The results can be displayed using a datagrid. user must be able to select a record from the search results.

This form will be called from other screens like Lending screen etc. User can search by Book name of author name. The search results will be displayed in the datagrid below. From the datagrid, user can select a record and press the 'select' button. When a record is 'selected', this form should be closed and the selected Books Id should be populated in the appropriate field in the calling form (like lending form).

class

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

using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Database_Lecture
{
class clsDatabaseManager
{
private OleDbConnection conn;
private OleDbCommand cmd;
private OleDbDataAdapter da;
private OleDbDataReader dr;

#region initializeComponent
public clsDatabaseManager()
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=C:\Users\alvin\Documents\UE Files\PROG3 (C#)\db_student.mdb";
try
{
conn = new OleDbConnection(connectionString);
cmd = new OleDbCommand();
da = new OleDbDataAdapter(cmd);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
#endregion

#region fillComboBox
public void fillComboBox(ComboBox cBox, string query, ComboBoxStyle DropDownStyle)
{
cBox.Items.Clear();
cBox.DropDownStyle = DropDownStyle;
cBox.Text = "";

try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
dr = cmd.ExecuteReader();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}

while (dr.Read())
{
cBox.Items.Add(dr[0]);
}

conn.Close();

try
{
cBox.Text = cBox.Items[0].ToString();
}
catch (Exception e)
{

}
}
#endregion

#region fillListView
public void fillListView(ListView lstView, string query, Boolean gridLines, View view, int fullRowSelect)
{
lstView.View = view;
lstView.GridLines = gridLines;
if (fullRowSelect <= 0)
{
lstView.FullRowSelect = false;
}
else
{
lstView.FullRowSelect = true;
}

lstView.Items.Clear();
lstView.Columns.Clear();

DataSet ds = new DataSet();

try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
da.Fill(ds, "Aw");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}

DataTable dt = new DataTable();
dt = ds.Tables["Aw"];

ListViewItem obj;

for (int x = 0; x < dt.Columns.Count; x++)
{
lstView.Columns.Add(dt.Columns[x].ColumnName);
}

for (int x = 0; x < dt.Rows.Count; x++)
{
obj = lstView.Items.Add(dt.Rows[x][0].ToString());
for (int y = 1; y < dt.Columns.Count; y++)
obj.SubItems.Add(dt.Rows[x][y].ToString());
}

for (int x = 0; x < dt.Columns.Count; x++)
lstView.AutoResizeColumn(x, ColumnHeaderAutoResizeStyle.HeaderSize);

conn.Close();
}
#endregion

#region execute
public void execute(string query)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}
conn.Close();
}
#endregion

#region getSingleValue
public string getSingleValue(string query)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
dr = cmd.ExecuteReader();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return "";
}

string value = "";
if (dr.Read())
{
value = dr[0].ToString();
}
else
{
value = "";
}

conn.Close();
return value;
}
#endregion

#region retrieveData
public void retrieveData(string query, TextBox txtFName, TextBox txtMName, TextBox txtLName, TextBox txtAge, TextBox txtAddress, ComboBox cboCourse)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
dr = cmd.ExecuteReader();

}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}
while (dr.Read())
{
txtFName.Text = dr["First Name"].ToString();
txtMName.Text = dr["Middle Name"].ToString();
txtLName.Text = dr["Last Name"].ToString();
txtAge.Text = dr["Age"].ToString();
cboCourse.Text = dr["Course"].ToString();
txtAddress.Text = dr["Address"].ToString();
}
dr.Close();
conn.Close();
}
#endregion
}
}
val126

Posts: 21
Joined: Wed Sep 28, 2011 3:32 pm

add

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Database_Lecture
{
public partial class frmAddRecord : Form
{
public frmAddRecord()
{
InitializeComponent();
}

Form1 _owner;
//string f2Snumber;
public frmAddRecord(Form1 owner)
{
InitializeComponent();
//f2Snumber = snumber;
// lblStudentNumber.Text = f2Snumber;
_owner = owner;
this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(this.frmAddRecord_FormClosing);
}

private void frmAddRecord_Load(object sender, EventArgs e)
{
initializeValues();
}

private void initializeValues()
{

string query = "Select * from tbl_student_record";
string queryCourse = "Select * from tbl_course";
string queryCountRecord = "SELECT MAX([Student Number]) FROM tbl_student_record";
clsDatabaseManager obj = new clsDatabaseManager();
obj.fillComboBox(cboCourse, queryCourse, ComboBoxStyle.DropDownList);

DateTime dt = DateTime.Now;

string strLast3Digits = obj.getSingleValue(queryCountRecord);
int max_rec_num = 0;
if (obj.getSingleValue(query).Equals(""))
{
lblStudentNumber.Text = dt.ToString("yyyyMMdd") + "-" + String.Format("{0:000}", (max_rec_num + 1)).ToString();
}
else
{
max_rec_num = Convert.ToInt32(strLast3Digits.Substring(strLast3Digits.Length - 3, 3));
lblStudentNumber.Text = dt.ToString("yyyyMMdd") + "-" + String.Format("{0:000}", (max_rec_num + 1)).ToString();
}

}

private void btnAdd_Click(object sender, EventArgs e)
{
clsDatabaseManager addRecord = new clsDatabaseManager();
string query = " Insert into tbl_student_record " +
@" values ('" + lblStudentNumber.Text + "'," +
@" '" + txtFirstName.Text + "', " +
@" '" + txtLastName.Text + "'," +
@" '" + txtMiddleName.Text + "', " +
@" " + int.Parse(txtAge.Text) + "," +
@" '" + cboCourse.Text + "'," +
@" '" + txtAddress.Text + "')";
addRecord.execute(query);
MessageBox.Show ("New Record was created!!!");
this.DialogResult = DialogResult.OK;
}

private void frmAddRecord_FormClosing(object sender, FormClosingEventArgs e)
{
_owner.PerformRefresh();
}
}
}

Introduction

9-1. Connect to a Database
Problem
You need to open a connection to a database.
Solution
Create a connection object appropriate to the type of database to which you need to connect. All
connection objects implement the System.Data.IDbConnection interface. Configure the connection
object by setting its ConnectionString property. Open the connection by calling the connection object’s
Open method.
How It Works
The first step in database access is to open a connection to the database. The IDbConnection interface
represents a database connection, and each data provider includes a unique implementation. Here is
the list of IDbConnection implementations for the five standard data providers:
• System.Data.Odbc.OdbcConnection
• System.Data.OleDb.OleDbConnection
• System.Data.OracleClient.OracleConnection
• System.Data.SqlServerCe.SqlCeConnection
• System.Data.SqlClient.SqlConnection
You configure a connection object using a connection string. A connection string is a set of
semicolon-separated name/value pairs. You can supply a connection string either as a constructor
argument or by setting a connection object’s ConnectionString property before opening the connection.
Each connection class implementation requires that you provide different information in the
connection string. Refer to the ConnectionString property documentation for each implementation to
see the values you can specify. Possible settings include the following:
CHAPTER 9 ■ DATABASE ACCESS
426
• The name of the target database server
• The name of the database to open initially
• Connection timeout values
• Connection-pooling behavior (see recipe 9-2)
• Authentication mechanisms to use when connecting to secured databases,
including provision of a username and password if needed
Once configured, call the connection object’s Open method to open the connection to the database.
You can then use the connection object to execute commands against the data source (discussed in
recipe 9-3). The properties of a connection object also allow you to retrieve information about the state
of a connection and the settings used to open the connection. When you’re finished with a connection,
you should always call its Close method to free the underlying database connection and system
resources. IDbConnection extends System.IDisposable, meaning that each connection class implements
the Dispose method. Dispose automatically calls Close, making the using statement a very clean and
efficient way of using connection objects in your code.
The Code
The following example demonstrates how to use both the SqlConnection and OleDbConnection classes to
open a connection to a Microsoft SQL Server Express database running on the local machine that uses
integrated Windows security:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace Apress.VisualCSharpRecipes.Chapter09
{
class Recipe09_01
{
public static void SqlConnectionExample()
{
// Create an empty SqlConnection object.
using (SqlConnection con = new SqlConnection())
{
// Configure the SqlConnection object's connection string.
con.ConnectionString =
@"Data Source=.\sqlexpress;" + // local SQL Server instance
"Database=Northwind;" + // the sample Northwind DB
"Integrated Security=SSPI"; // integrated Windows security
// Open the database connection.
con.Open();
CHAPTER 9 ■ DATABASE ACCESS
427
// Display information about the connection.
if (con.State == ConnectionState.Open)
{
Console.WriteLine("SqlConnection Information:");
Console.WriteLine(" Connection State = " + con.State);
Console.WriteLine(" Connection String = " +
con.ConnectionString);
Console.WriteLine(" Database Source = " + con.DataSource);
Console.WriteLine(" Database = " + con.Database);
Console.WriteLine(" Server Version = " + con.ServerVersion);
Console.WriteLine(" Workstation Id = " + con.WorkstationId);
Console.WriteLine(" Timeout = " + con.ConnectionTimeout);
Console.WriteLine(" Packet Size = " + con.PacketSize);
}
else
{
Console.WriteLine("SqlConnection failed to open.");
Console.WriteLine(" Connection State = " + con.State);
}
// At the end of the using block Dispose() calls Close().
}
}
public static void OleDbConnectionExample()
{
// Create an empty OleDbConnection object.
using (OleDbConnection con = new OleDbConnection())
{
// Configure the OleDbConnection object's connection string.
con.ConnectionString =
"Provider=SQLOLEDB;" + // OLE DB Provider for SQL Server
@"Data Source=.\sqlexpress;" + // local SQL Server instance
"Initial Catalog=Northwind;" + // the sample Northwind DB
"Integrated Security=SSPI"; // integrated Windows security
// Open the database connection.
con.Open();
// Display information about the connection.
if (con.State == ConnectionState.Open)
{
Console.WriteLine("OleDbConnection Information:");
Console.WriteLine(" Connection State = " + con.State);
Console.WriteLine(" Connection String = " +
con.ConnectionString);
Console.WriteLine(" Database Source = " + con.DataSource);
Console.WriteLine(" Database = " + con.Database);
Console.WriteLine(" Server Version = " + con.ServerVersion);
Console.WriteLine(" Timeout = " + con.ConnectionTimeout);
}
CHAPTER 9 ■ DATABASE ACCESS
428
else
{
Console.WriteLine("OleDbConnection failed to open.");
Console.WriteLine(" Connection State = " + con.State);
}
// At the end of the using block Dispose() calls Close().
}
}
public static void Main()
{
// Open connection using SqlConnection.
SqlConnectionExample();
Console.WriteLine(Environment.NewLine);
// Open connection using OleDbConnection.
OleDbConnectionExample();
// Wait to continue.
Console.WriteLine(Environment.NewLine);
Console.WriteLine("Main method complete. Press Enter.");
Console.ReadLine();
}
}
}