分類彙整: Web資料庫
ASP.NET SQLite Sample 上課範例程式碼
void showdata()
{
//建立資料表
SQLiteConnection conn = new SQLiteConnection("Data source=mydb.sqlite");
// Open
conn.Open();
//要下任何命令先取得該連結的執行命令物件
SQLiteCommand cmd = conn.CreateCommand();
// 建立資料表
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM address_book";
// 執行查詢塞入 sqlite_datareader
SQLiteDataReader sqlite_datareader = cmd.ExecuteReader();
GridView1.DataSource = sqlite_datareader;
GridView1.DataBind();
conn.Close();
}
void executeSQL(string sql)
{
SQLiteConnection conn = new SQLiteConnection("Data source=mydb.sqlite");
conn.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
conn.Close();
}
protected void GridView1_RowEditing(Object sender, GridViewEditEventArgs e)
{
//Set the edit index.
GridView1.EditIndex = e.NewEditIndex;
//Bind data to the GridView control.
showdata();
}
protected void GridView1_OnRowCancelingEdit(Object sender, GridViewCancelEditEventArgs e)
{
Response.Write("cancel");
GridView1.EditIndex = -1;
//Bind data to the GridView control.
showdata();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//Update the values.
GridViewRow row = GridView1.Rows[e.RowIndex];
Label1.Text += ((TextBox)(row.Cells[2].Controls[0])).Text+"<br>";
Label1.Text += ((TextBox)(row.Cells[3].Controls[0])).Text +"<br>";
Label1.Text += ((TextBox)(row.Cells[4].Controls[0])).Text +"<br>";
//更新的SQL語法
string updatesql = "UPDATE address_book SET name='" + ((TextBox)(row.Cells[2].Controls[0])).Text + "', phone='" + ((TextBox)(row.Cells[3].Controls[0])).Text + "',address='" + ((TextBox)(row.Cells[4].Controls[0])).Text + "' where sid=" + ((TextBox)(row.Cells[1].Controls[0])).Text;
executeSQL(updatesql);
//Reset the edit index.
GridView1.EditIndex = -1;
//Bind data to the GridView control.
showdata();
}
ASP.NET SQLite Sample
//建立資料表
SQLiteConnection conn = new SQLiteConnection("Data source=mydb.sqlite");
//Open
conn.Open();
//要下任何命令先取得該連結的執行命令物件
SQLiteCommand cmd = conn.CreateCommand();
// 建立資料表
string sql = "CREATE TABLE IF NOT EXISTS 'address_book' (sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , name VARCHAR, phone VARCHAR, address VARCHAR,creation_date DATETIME DEFAULT CURRENT_TIMESTAMP)";
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
// 輸入參數
cmd.Parameters.Add(cmd.CreateParameter());
cmd.Parameters.Add(cmd.CreateParameter());
cmd.Parameters.Add(cmd.CreateParameter());
cmd.CommandText = "INSERT INTO 'address_book' ('name', 'phone', 'address' ) VALUES( ?, ?, ?)";
cmd.Parameters[0].Value = TextBox1.Text;
cmd.Parameters[1].Value = TextBox2.Text;
cmd.Parameters[2].Value = TextBox3.Text;
cmd.ExecuteNonQuery();
// 查詢資料表
cmd.CommandText = "SELECT * FROM address_book";
// 執行查詢塞入 sqlite_datareader
SQLiteDataReader sqlite_datareader = cmd.ExecuteReader();
GridView1.DataSource = sqlite_datareader;
GridView1.DataBind();
conn.Close();
2013 11/16 Web資料庫上課範例
SQL 語法練習
1.列出第11~20筆星巴克
2.列出營業時間 07:00~21:00 的店家
3.台北市的店家數量總數為
4.隨機找出10筆星巴克
http://140.112.31.82/websql/starbucks.xlsx
ASP.NET 連結 Mysql
//建立與mysql連線
MySqlConnection conn = new MySqlConnection("server=localhost;user id=帳號; password=密碼;database=資料庫名稱");
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string sql = "CREATE TABLE IF NOT EXISTS address_book (sid INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR( 20 ) NOT NULL , phone VARCHAR( 20 ) NOT NULL , address VARCHAR( 20 ) NOT NULL) ENGINE = MYISAM ;";
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO address_book (name, phone, address ) VALUES('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "')";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM address_book";
MySqlDataReader mysql_datareader = cmd.ExecuteReader();
GridView1.DataSource = mysql_datareader;
GridView1.DataBind();
conn.Close();
update.php
<form action="" method="post" name="form1"><br>
Input address book data:<br>
<input name="name" type="text" value="
<?php
if($_POST['submit']=='Update') {
echo $_POST['name'];
}
else {
echo $_GET['name'];
}
?>
"><br>
<input name="phone" type="text" value="
<?php
if($_POST['submit']=='Update') {
echo $_POST['phone'];
}
else {
echo $_GET['phone'];
}
?>
"><br>
<input name="address" type="text" value="
<?php
if($_POST['submit']=='Update') {
echo $_POST['address'];
}
else {
echo $_GET['address'];
}
?>
"><br>
<input name="submit" type="submit" value="Update"><br>
</form>
<?php
//建立資料連線物件
$pdo = new PDO("sqlite:mydb.sqlite");
$pdo->exec("CREATE TABLE address_book (
sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
name VARCHAR,
phone VARCHAR,
address VARCHAR,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP)");
if( isset($_POST['submit']) && $_POST['submit']=='Update' ) {
// 準備新增資料的語法
$name = $_POST["name"];
$phone = $_POST["phone"];
$address = $_POST["address"];
$pdo->exec("UPDATE 'address_book' SET name='$name', phone='$phone', address='$address' where sid=$_GET[sid]");
}
if( $_GET['op']=='del' ) {
// 準備刪除資料的語法
$pdo->exec("DELETE FROM 'address_book' WHERE
SID=$_GET[sid]");
}
$pdoStatement = $pdo->query("SELECT * FROM address_book");
/*
while($row = $pdoStatement->fetch() ) {
print_r( $row );
}
*/
makeTable($pdoStatement);
function makeTable($pdoStatement)
{
echo $_POST["sql"]."<br>";
echo "<table border='1'>";
//設定Title
echo "<tr><td>sid</td>";
echo "<td>name</td>";
echo "<td>phone</td>";
echo "<td>address</td>";
echo "<td></td></tr>";
//讀取內容
while($row = $pdoStatement->fetch(PDO::FETCH_ASSOC) ) {
echo "<tr><td>$row[sid]</td>";
echo "<td>$row[name]</td>";
echo "<td>$row[phone]</td>";
echo "<td>$row[address]</td>";
echo "<td><a href=add.php?op=del&sid=$row[sid]>del</a></td></tr>";
}
echo "</table>";
}
?>
add.php
<form action="" method="post" name="form1"><br>
Input address book data:<br>
<input name="name" type="text" value="shinder"><br>
<input name="phone" type="text" value="0918981520"><br>
<input name="address" type="text" value="Taipei"><br>
<input name="submit" type="submit" value="Add"><br>
</form>
<?php
//建立資料連線物件
$pdo = new PDO("sqlite:mydb.sqlite");
$pdo->exec("CREATE TABLE address_book (
sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
name VARCHAR,
phone VARCHAR,
address VARCHAR,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP)");
if( isset($_POST['submit']) && $_POST['submit']=='Add' ) {
// 準備新增資料的語法
$name = $_POST["name"];
$phone = $_POST["phone"];
$address = $_POST["address"];
$pdo->exec("INSERT INTO 'address_book' ('name', 'phone', 'address' )
VALUES( '$name', '$phone', '$address')");
}
if( $_GET['op']=='del' ) {
// 準備刪除資料的語法
$pdo->exec("DELETE FROM 'address_book' WHERE
SID=$_GET[sid]");
}
if( $_GET['op']=='sort_sid' ) {
$pdoStatement = $pdo->query("SELECT * FROM address_book order by sid desc");
}
else if( $_GET['op']=='sort_name' ) {
$pdoStatement = $pdo->query("SELECT * FROM address_book order by name desc");
}
else {
$pdoStatement = $pdo->query("SELECT * FROM address_book");
}
makeTable($pdoStatement);
function makeTable($temp)
{
echo "<table border='1'>";
//設定Title
echo "<tr><td><a href=add.php?op=sort_sid>sid</a></td>";
echo "<td><a href=add.php?op=sort_name>name</a></td>";
echo "<td>phone</td>";
echo "<td>address</td>";
echo "<td></td>";
echo "<td></td></tr>";
//讀取內容
while($row = $temp->fetch() ) {
echo "<tr><td>$row[sid]</td>";
echo "<td>$row[name]</td>";
echo "<td>$row[phone]</td>";
echo "<td>$row[address]</td>";
echo "<td><a href=add.php?op=del&sid=$row[sid]>del</a></td>";
echo "<td><a href=update.php?op=update&sid=$row[sid]&name=$row[name]&phone=$row[phone]&address=$row[address]>update</a></td></tr>";
}
echo "</table>";
}
?>
ASP.NET 連接 SQLite 範例
//建立資料表
SQLiteConnection conn = new SQLiteConnection("Data source=mydb.sqlite");
// Open
conn.Open();
//要下任何命令先取得該連結的執行命令物件
SQLiteCommand cmd = conn.CreateCommand();
// 建立資料表
string sql = "CREATE TABLE IF NOT EXISTS 'address_book' (sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , name VARCHAR, phone VARCHAR, address VARCHAR,creation_date DATETIME DEFAULT CURRENT_TIMESTAMP)";
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO 'address_book' ('name', 'phone', 'address' ) VALUES('" + TextBox1.Text +"', '" + TextBox2.Text + "', '" + TextBox3.Text + "')";
cmd.ExecuteNonQuery();
// 查詢資料表
cmd.CommandText = "SELECT * FROM address_book";
// 執行查詢塞入 sqlite_datareader
SQLiteDataReader sqlite_datareader = cmd.ExecuteReader();
GridView1.DataSource = sqlite_datareader;
GridView1.DataBind();
conn.Close();
php+mysql 連接範例 add_mysql.php
<form action="" method="post" name="form1"><br>
Input address book data:<br>
name:<input name="name" type="text" value=""><br>
phone:<input name="phone" type="text" value=""><br>
address:<input name="address" type="text" value=""><br>
<input name="submit" type="submit" value="add"><br>
<?php
mysql_connect('localhost','root','student') or die("connect error!");
mysql_select_db('phone') or die("db error");
mysql_query("SET NAMES 'utf8'");
mysql_query("CREATE TABLE `address_book` (
`sid` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`phone` VARCHAR( 20 ) NOT NULL ,
`address` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM ;");
if(count($_POST)>0) {
// 準備更新資料的語法
$name = $_POST["name"];
$phone = $_POST["phone"];
$address = $_POST["address"];
mysql_query("INSERT INTO address_book (name, phone, address )
VALUES( '$name', '$phone', '$address')");
}
$result = mysql_query("SELECT * FROM address_book");
makeTable($result);
function makeTable($result)
{
echo "<table border='1'>";
//設定Title
echo "<tr><td>sid</td>";
echo "<td>name</td>";
echo "<td>phone</td>";
echo "<td>address</td>";
echo "<td></td>";
echo "<td></td></tr>";
//讀取內容
while($row = mysql_fetch_assoc($result)) {
echo "<tr><td>$row[sid]</td>";
echo "<td>$row[name]</td>";
echo "<td>$row[phone]</td>";
echo "<td>$row[address]</td>";
echo "<td><a href=add_mysql.php?op=del&sid=$row[sid]>del</a></td>";
echo "<td><a href=update.php?sid=$row[sid]&name=$row[name]&phone=$row[phone]&address=$row[address]>update</a></td></tr>";
}
echo "</table>";
}
?>