Oct 3, 2011

MY SQL PART 1


Setting up the Database

CREATE DATABASE versedb;

CREATE TABLE `verse` (
`verse_id` int(10) unsigned NOT NULL auto_increment,
`verse_text` varchar(1024) NOT NULL default '',
`verse_ref` varchar(50) NOT NULL default '',
PRIMARY KEY  (`verse_id`)
)


INSERT INTO `verse` (`verse_text`,`verse_ref`) VALUES
('And God is able to make all grace abound to you, so that in all things
at all times, having all that you need, you will abound in every good work.',
'2 Corinthians 9:8'),
('Whatever you do, work at it with all your heart, as working for the
Lord, not for men, since you know that you will receive an inheritance from
the Lord as a reward. It is the Lord Christ you are serving.',
'Colossians 3:23-24');

Connecting to MySQL from ASP.NET

// Connection string for a typical local MySQL installation
string cnnString = "Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=MySecretPassword";

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

// Bind the DataSet
// ... Place your databinding code here ... 

Hello World…

<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<html>
<head>
<script runat="server" type="text/javascript">
void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindVerses();
}
}

private void BindVerses()
{
try
{
// Connection string for a typical local MySQL installation
string cnnString = "Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=mySecretPassword";

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

// Bind the DataSet
VerseRepeater.DataSource = ds;
VerseRepeater.DataBind();
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
}
</script>

<title></title>    
<style type="text/css">
body { font-family:Verdana, Arial, Sans-Serif; font-size:small; }
h1, h2, h3, h4, h5 { font-family:Trebuchet MS, Verdana, Arial, Sans-Serif; }
input, textarea { font-family: Verdana, Arial, Sans-Serif; font-size:small; }
.Error { font-weight:bold; color:#c00; }
.VerseList { width:400px; }
.VerseHeader {
padding:5px;
border:1px solid #999;
background-color:#ddd;
font-weight:bold;
}
.VerseText {
font-family: Trebuchet MS, Verdana, Arial;
font-size: small;
padding:5px 5px 0px 5px;
border-left:1px solid #999;
border-right: 1px solid #999;
}
.VerseRef {
font-family: Arial, sans-serif;
font-style:italic;
font-size: x-small;
text-align:right;
padding:5px 15px 0px 5px;
border-left:1px solid #999;
border-right: 1px solid #999;
border-bottom:1px solid #ccc;
}
.VerseFooter {
border-left:1px solid #999;
border-right: 1px solid #999;
border-bottom:1px solid #999;
}
</style>
</head>
<body>

<form runat="server" method="post" id="Form1">
<p><asp:label cssclass="Error" enableviewstate="False" runat="server" id="lblError"></asp:label></p>
<div class="VerseList">
<asp:repeater runat="server" id="VerseRepeater">
<headertemplate>
<div class="VerseHeader">Verse List</div>
</headertemplate>

<itemtemplate>
<div class="VerseText"><%# DataBinder.Eval(Container.DataItem, "verse_text") %></div>
<div class="VerseRef">--
<%# DataBinder.Eval(Container.DataItem, "verse_ref") %>
</div>
</itemtemplate>
<footertemplate>
<div class="VerseFooter"><img width="1" height="2" alt="" src="images/pixel.gif" /></div>
</footertemplate>
</asp:repeater>

</div>
</form>
</body>
</html>

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib