get free code

using System.Data.SqlClient;

SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString =
"Data Source=(local);" +
"Initial Catalog=myDatabaseName;" +
"Integrated Security=SSPI";
//Or
// "Server=(local);" +
// "Database=myDatabaseName;" +
// "Trusted_Connection=Yes";
oSQLConn.Open();
...
oSQLConn.Close();

For SQL Server ODBC Driver
' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUsername;" & _
"Pwd=MyPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

Structured Query Language
Structured Query Language, which is better known as SQL (ES-Que-EL) is a computer language for database, used to retrieve, store, update, delete and to create database.

Brief History
In 1970, SQL was developed by Donald Messerly and Reymond Boyce who were working in IBM. Later in 1986 it was formally standardized by ANSI. Now SQL 2008 is being used in various development centers of Software around the world. This is the seventh of SQL Language series.

At Present
Now a days all available relational database management systems like MS SQL, MSDE, DB@, My SQL, Access, MS SQL Server etc use SQL as their standard for the creation of the database. So, it became very essential to learn the basics of the SQL. Here I’m providing the various SQL commands and good tutorials for the persons who have zeal to learn the language.


Fundamentals
The language SQL is used to performing different operation to manipulate date of the database like creating, selecting, inserting, dropping, deleting.. To run the each command a different command is used.

For example: If we want to select a “Last_Name” from our database “Login” whose “id” is “14”. Then our command would be

SELECT Last_Name FROM Login WHERE id=14;

In the above command what should be learn?

“SELECT” is the clause [keyword] used to select the name, this keyword is used to select the particular record name.

“FROM” clause [keyword] is used to refer the database “Login”. Here the name of the database is “Login”. The FROM clause [keyword] is to refer or indicate the database and from where the data will be accessed.

WHERE Clause [keyword] is used to refer the condition to select a particular condition from the database.

One more thing is to learn i.e. SQL is not a case sensitive. It means we can use and run the following commands as well.

Select last_name from login where id=14;

“ ; ” (Semicolon) is not mandatory, is optional. But it is a good programming practice to use a semicolon at the end of the command line.


1. Create Database

To create database there is a simple syntax is available and that will create a database.

Syntax

CREATE DATABASE database_name;

Example:

CREATE DATABASE sql_basic;

Or

Create database sql_basic;


2. Create
In database management system, a table is very important which refer to be a set of data of a particular database which must have columns and rows. The Keyword CREATE is used to create a table.

Syntax:

CREATE TABLE table_name
("column_01" "data_type", "column_02" "data_type", "column_03" "data_type") ;

Here in the above syntax the
“column_01”, “column_02”, “column_03” are the name of columns of the table table_name and
“data_type” refers that what type of data should be.

Example:

CREATE TABLE Login (
ID int NULL,
First_Name varchar (50),
Last_Name varchar (50),
Email varchar (50),
Gender varchar (50),
Martial_Status varchar (50),
Date_of_Birth datetime NULL ,
Password varchar (50),
Contact_Number varchar (50),
Company varchar (50),
Address varchar (100),
Salary float NULL,
City varchar (50),
State varchar (50),
Country varchar (50)
);

The above code will create a table of name “Login” in which there are 15 “columns” and their “data_types” are available with “size”.
Few points are to take a look:

* ID is taken as int and null.
* Names and other coulnms are taken as “varchar” and their sizes are as per their requirements.
* There is a function datetime has been used to get the time and date, there is no data_type is associated with the fuctions
* Salary has been taken as “float”.

Here we need to know what the data types:
Data Type refers that what kind of data should be like int, or char, or varchar.
Here few common data types are given below which can be used in this section.

# Char : 0 to 255 with 1 byte storage
# Varchar : 0 to 255
[Both are character data types of either fixed length or variable length]

# DateTime : January 1, 1753 to December 31, 9999

# Float : Depends upon the value of n
1-24 (n-value) 7 digits (precision) of 4 bytes
25-53(n-value) 15 digits(precision) of 8 bytes

# Number : Number value as per the columns are available in the table.



3. Insert
In database management system data are inserted into the table using the keyword INSERT. An statement with the keyword INSERT INTO will insert the data into the record.
There are two ways to insert the data into the table.


Syntax:

INSERT INTO table_name
VALUES (value_01, value_02, value_03,....)

or

INSERT INTO table_name
(column_01, column_02, column_03,...)
VALUES (value_01, value_02, value_03,....)


Here in the above syntax the INSERT INTO is the keyword followed by columns names of the table (column_01, column_02, column_03,..), VALUES is the keyword refers that following data is to be inserted in the table and value_01, value_02, value_03 are the value for their respective columns.

Example :

INSERT INTO Login
VALUES ('Kabir’, ‘Roy, 'Belapur', ‘coolreyan@gmail.com’, ‘M’, ‘Single’, getdate(), ’16564646’, ‘9823070730’, ‘MicroTech’, ‘Mahape’, ‘7000’, Navi Mumbai, ‘Maharastra’, ‘India’)

OR

INSERT INTO Login
(First_Name, Last_Name, Location,
Email, Gender, Martial_status, Date_of_birth,
Password, Contact_Number, Company_Name,
Address, Salary, City, Country)
VALUES ('Kabir’, ‘Roy, 'Belapur', ‘coolreyan@gmail.com’, ‘M’, ‘Single’, getdate(), ’16564646’, ‘9823070730’, ‘MicroTech’, ‘Mahape’, ‘7000’, Navi Mumbai, ‘Maharastra’, ‘India’)

Here in the above code “Login” is the table name and the value are in the statement are same as per the syntax. This is a very simple statement and used keywords are in a simple format.
Only one there is a point to note down. i.e. getdate() fuction is written in the value of Date_of_birth field. This function will automatically insert the current date and time in the database.
This function will be explained later in next article.

### Practice more with CREATE TABLE and INSERT INTO keywords.


4. UPDATE

In the database management system when a data is inserted , created and
manipulated then sometimes we need to update them as the time changes,
records are always for change.
In SQL we use a keyword UPDATE to update the database.

SYNTAX:

UPDATE table _name
SET column_01=value, column_02=value, column_03=value,...
WHERE My_column_01=My_value_01;

Here UPDATE keyword is followed by table_name, the the keyword SET is to change the values of their respective columns and WHERE keyword is indicating that a specific keyword to address the statement. This keyword can be the column_name of the database whose date is to update.

Example:

UPDATE Login
SET Salary=6100, Address=Andheri
WHERE id=15;

Here in this example the fields are to update, Salary and Address, and WHERE denotes that whose id is 15, that data is to update.

4. DELETE DATA

To remove a data from a database DELETE keyword [command] is used.

DELETE FROM “table _name”

Or

DELETE FROM “table_name”
WHERE My_Column = ‘My_Value’
[ Condition ];

Here in the above syntax there are as written DELETE is the command [Keyword] is to delete the entire table of the database.

Where in the second statement the condiation is same here but a condition is available. The condition States that should be true.

Example:

DELETE FROM Login;

OR

DELETE FROM Login
WHERE last_name=’Roy’;

Here the above command will remove the entire table “Login”
Whereas the nest Command will delete only one data whose “Last_name” is ‘Roy’. So, one shouldn’t skip the line starting with WHERE otherwise the entire table can be deleted.

DELETE command is very easy and simple to execute.

< asp:GridView ID="gdview" runat="server" ShowFooter="true" AutoGenerateColumns="False" DataKeyNames="CategoryID" OnRowCancelingEdit="gdview_RowCancelingEdit" OnRowDeleting="gdview_RowDeleting" OnRowEditing="gdview_RowEditing" OnRowUpdating="gdview_RowUpdating" Width="100%" AllowPaging="True" PageSize="5" OnPageIndexChanging="gdview_PageIndexChanging" OnRowDataBound="gdview_RowDataBound" >
< Columns >
< asp:BoundField HeaderText="Category Name" DataField="CategoryName" SortExpression="CategoryName" >
< ItemStyle Height="20px" Width="150px" />
< /asp:BoundField >

< asp:CommandField ShowEditButton="True" >
< ItemStyle Width="100px" />

<asp:TemplateField>
< ItemTemplate >
< asp:LinkButton ID="lnkdel" runat="server" Text="Delete" CommandName="Delete" >


<ItemStyle Width="100px" />





< /asp:GridView>



protected void Page_Load(object sender, EventArgs e)
{

if (!Page.IsPostBack)
{
bindgrid();
total = 0;

}

}

public void bindgrid()
{
SqlConnection conn = new SqlConnection("Data Source='localhost';Initial Catalog='Northwind';Integrated Security=SSPI;Persist Security Info=False ");
SqlCommand cmd = new SqlCommand("select CategoryName,CategoryID from Categories ", conn);

SqlDataAdapter da = new SqlDataAdapter("", conn);
da.SelectCommand = new SqlCommand("select CategoryName,CategoryID from Categories", conn);
DataSet ds = new DataSet();
da.Fill(ds, "data");
gdview.DataSource = ds.Tables[0].DefaultView;
gdview.DataBind();


}
protected void gdview_RowEditing(object sender, GridViewEditEventArgs e)
{

gdview.EditIndex = e.NewEditIndex;

bindgrid();

}
protected void gdview_RowUpdating(object sender, GridViewUpdateEventArgs e)
{



int catid = int.Parse(gdview.DataKeys[e.RowIndex].Value.ToString());
string strcatname=((TextBox)gdview.Rows[e.RowIndex].Cells[0].Controls[0]).Text;
SqlConnection conn = new SqlConnection("Data Source='localhost';Initial Catalog='Northwind';Integrated Security=SSPI;Persist Security Info=False ");
SqlDataAdapter da = new SqlDataAdapter("", conn);
conn.Open();
da.UpdateCommand = new SqlCommand("update Categories set CategoryName='" + strcatname + "' where CategoryID=" + catid, conn);
da.UpdateCommand.ExecuteNonQuery();
conn.Close();
gdview.EditIndex = -1;
bindgrid();



}
protected void gdview_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gdview.EditIndex = -1;
bindgrid();
}
protected void gdview_RowDeleting(object sender, GridViewDeleteEventArgs e)
{

int catid = int.Parse(gdview.DataKeys[0].Value.ToString());
SqlConnection conn = new SqlConnection("Data Source='localhost';Initial Catalog='Northwind';Integrated Security=SSPI;Persist Security Info=False ");
SqlDataAdapter da = new SqlDataAdapter("", conn);
conn.Open();
da.DeleteCommand = new SqlCommand("delete from Categories where CategoryID="+catid, conn);
da.DeleteCommand.ExecuteNonQuery();
conn.Close();
bindgrid();
}

This article explains how to create login page along with sign up page.
1. To start with open VWD ,click on the File>New>Web Site from main menu.
2. Select ASP.NET Web Site from template, select language from drop-down menu.
3. Click the browse folder and select folder where you want to create website content.
4. Click Ok
VWD creates new website for you.
This contains Default.aspx, Default.aspx.vb or Default.aspx.cs ,web.config files and empty App_Data Folder.
5. Double-click the Default.aspx in Solution Explorer, this will open Default.aspx in document window
6.Switch to design view.
7. Drag LoginStatus control from Login section of Toolbox and drop it on the content area of Default.aspx
8. Then drag a Login control and drop it above the LoginStatus control, so that it appears above the LoginStatus component.
9. Right-Click the Login Component in document window and select Properties from menu.
10. In Properties grid select CreateUserText and add some text like “Please Sign Up ”
11.Right-click the website path in bold in the Solution Explorer and choose new Item, new template will appear choose Web Form under the heading Visual Studio installed templates .
12. Name it as SignUpPage,make sure Place code in separte file .
13. Click ok.
14.You will see new file SignUpPage.aspx in Solution explorer.
15. Switch to deisgn view of Default.aspx, open Properties panel for Login control, add SignUpPage.aspx Url in CreateUserUrl field, this will add link to SignUpPage in CreateUserText .
16. Create one new page ,name it as HomePage.aspx , open HomePage.aspx and switch to design mode write some text like Welcome to Home Page after login.
17. Click the Default.aspx in document window tab , switch to design view , right-click Login control and open Properties Grid add HomePage.aspx in the DesignationPageUrl text filed, this will redirect to HomePage after successful login.
18. Open SignUpPage by double-clicking it in the Solution explorer.
19. Switch to Design View ,drag the CreateUserWizard from Login section of Toolbox and drop it to the content area of the page.
20.Right-Click the CreateUserWizard and open Properties grid ,add Url of HomePage.aspx in the ContinueDesignationPageurl TextField.
21. Click Cltr+F5 ,you will see Default.aspx page with login control
22. Enter some username and password and click Login button, it will give error message:
Your login attempt was not successful. Please try again.
23. Click on Please Sign Up link to create new user account in SignUpPage
Note: Password should be more than six characters and should contain at least one non-alphanumeric.
24.Fill all the text fields and click Create user button ,you will see message:
Complete
Your account has been successfully created.

26. Click Continue button you will be redirected to HomePage.aspx

27. Click refresh button on the tool bar of Solution Explorer ,you will see ASPNETDB.MDF database is create automatically in the App_Data folder which will keep track/record of the user names, passwords and other entries of the SignUpPage.

Great! You can now create login pages without writing lengthy, clumsy codes.

Sample source code is attached as a Zip file.

create table SampleImageTable (sampleimage image)

create procedure UploadImage(@imgsamgetimage as image)
as
insert into SampleImageTable (sampleimage) values (@imgsamgetimage)
Private Sub btnseltfile_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles

btnseltfile.Click
OpenFileDialog1.ShowDialog()
txtImg.Text = OpenFileDialog1.FileName
End Sub


rivate Sub btnUpload_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnUpload.Click
Try
If Trim(txtImg.Text) = "" Then
MsgBox("Please select a image.")
Exit Sub
End If
Dim fs As New FileStream(Trim(txtImg.Text), FileMode.Open)
Dim Data() As Byte = New [Byte](fs.Length) {}
fs.Read(Data, 0, fs.Length)

Dim con As New System.Data.SqlClient.SqlConnection("data source=mt5;initial
catalog=master;
user id=sa;password=mms")
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand("UploadImage")
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@imgsamgetimage", Data)
cmd.ExecuteNonQuery()
con.Close()
fs.Close()
Catch ex As System.Data.SqlClient.SqlException
MsgBox(ex.Message)
End Try
End Sub



Private Sub btnDownload_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnDownload.Click

Dim con As New System.Data.SqlClient.SqlConnection("data source=mt5;initial
catalog=master;
user id=sa;password=mms")
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand("select * from SampleImageTable")
cmd.Connection = con
cmd.CommandType = CommandType.Text
Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
Dim bits As Byte() = CType(ds.Tables(0).Rows(0).Item(0), Byte())
Dim memorybits As New MemoryStream(bits)
Dim bitmap As New Bitmap(memorybits)
PictureBox1.Image = bitmap
End Sub

using System.Collections.Generic;
private string GetSearchPacketReqId(string pathofbasefolder, string filename)
{
try
{
string strFileName = "";
string strReqId = filename + ".xml";
Stack directoryStack = new Stack();
directoryStack.Push(baseDir);

while (directoryStack.Count > 0)
{
string currentDir = directoryStack.Pop();

foreach (string fileNamePath in Directory.GetFiles(currentDir, "*.*"))
{

StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(fileNamePath);
strBuilder.Remove(0, currentDir.Length + 1);
if (strReqId == strBuilder.ToString())
{
//XML file name that you search
strFileName = fileNamePath;
return strFileName;
}
}

foreach (string directoryName in Directory.GetDirectories(currentDir))
{
directoryStack.Push(directoryName);
}
}
return strFileName;
}
catch (Exception ex)
{
throw ex;
}
}

Use the below online tool to convert VB.NET code snippets to C#. We have another online feature available to convert C# to


Imports System
Module Module1

Public Sub Main
Console.WriteLine("Hello World.")
End Sub

End Module

ID="Xaml1"
runat="server"
Source="~/ClientBin/MySilverlightApp.xap"
InitParameters="City=Houston,State=Texas,Country=USA"
Width="300"
Height="300" />

private void Application_Startup(object sender, StartupEventArgs e)
{
IDictionary parameters = e.InitParams;

this.RootVisual = new Page1();

Use the below online tool to convert C# code snippets to VB.NET. We have another online feature available to convert VB.NET


using System;

public class MyClass
{
static void Main(string[] args)
{
Console.WriteLine("Hello !");
}
}

The following code shows how to use Model Popup Extender


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxtoolkit" %>
<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Namespace="System.Web.UI" TagPrefix="asp" %>

<%-- --%>

OnClick="showModalPopupServerOperatorButton_Click" />
TargetControlID="hiddenTargetControlForModalPopup" PopupControlID="programmaticPopup"
BackgroundCssClass="modalBackground" DropShadow="True" RepositionMode="RepositionOnWindowScroll">




<%-- --%>

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void showModalPopupServerOperatorButton_Click(object sender, EventArgs e)
{
TextBox1.Text = TextBox2.Text;
this.programmaticModalPopup.Show();
}
protected void hideModalPopupViaServer_Click(object sender, EventArgs e)
{
this.programmaticModalPopup.Hide();
}
protected void Button1_Click(object sender, EventArgs e)
{
Label1.Text = TextBox1.Text;
}

}

function GetData()
{
var xmlHttp = getNewHTTPObject();
var url = "frmAsnc.aspx"; xmlHttp.onreadystatechange = StateChanged;
xmlHttp.open('GET', url, true);
xmlHttp.send(null);
return false;
}
function getNewHTTPObject()
{

if (window.XMLHttpRequest)
{ // Mozilla, Safari,...
xmlHttp = new XMLHttpRequest();
}
else if (window.ActiveXObject)
{ // IE
xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
}
return xmlHttp;
}

function StateChanged()
{
if(xmlHttp.readyState==4)
{
var result = xmlHttp.responseText;
document.getElementById('TextBox1').value = result;
}

}

When using Ajax Update panels on a page , simple javascript functions do not work as these scripts are not in the "ScriptManagers pool", to execute javascripts like for eg an alert do the following:

ScriptManager.RegisterStartupScript(this,typeof(Page), "alertFn", "alert('test');", true);

You can use the following code sample to pop up a message box inside an update panel.


ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "clientScript", "alert("Saved Successfully!")", true);

You can use the following code sample to pop up a message box inside an update panel.


ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "clientScript", "alert("Saved Successfully!")", true);

PHP and JavaScript code used in "Login" section

4. Login

The status was:

Enter your login details you added on the previous page and
test the login.

The success or failure will be shown above.

bgcolor="#FFFFFF" cellspacing="1" cellpadding="0"> Username: VALUE SIZE="8" MAXLENGTH="16" tabindex="1"> href="javascript:alert('The username must be between 4 and 16 characters long.')">Help Password: name="password" size="8" tabindex="2" maxlength="8"> href="javascript:alert('The password must be between 4 and 8 characters long.')">Help NAME="FormsButton2" VALUE="Test Login" ONCLICK="validateForm()" tabindex="3" style="font-family: Verdana; font-size: 8pt"> href="javascript:alert('Click to save the details')">Help

Now move on to the final section

 

Click here to view the PHP and JavaScript code
used for this page.

 

// version 1.1
if (isset($password)) // if a the password is set then the form has been submitted on login.php page
{
include("connect2db.php");

$username = $HTTP_POST_VARS['username'];
$password = $HTTP_POST_VARS['password'];

$qstr = "SELECT * from members where username ='$username' and password ='$password'";

$result = mysql_query($qstr);
// check login info is correct
if (mysql_num_rows($result)) echo "**Successful Login**";
else echo "**Failed Login**";
mysql_close();
}
else echo "**No login attempted**";
?>

Using PHP and MySQL to provide an image library


// Connect to database

$errmsg = "";
if (! @mysql_connect("localhost","trainee","abc123")) {
$errmsg = "Cannot connect to database";
}
@mysql_select_db("test");

// First run ONLY - need to create table by uncommenting this
// Or with silent @ we can let it fail every sunsequent time ;-)

$q = <<create table pix (
pid int primary key not null auto_increment,
title text,
imgdata longblob)
CREATE;
@mysql_query($q);

// Insert any new image into database

if ($_REQUEST[completed] == 1) {
// Need to add - check for large upload. Otherwise the code
// will just duplicate old file ;-)
// ALSO - note that latest.img must be public write and in a
// live appliaction should be in another (safe!) directory.
move_uploaded_file($_FILES['imagefile']['tmp_name'],"latest.img");
$instr = fopen("latest.img","rb");
$image = addslashes(fread($instr,filesize("latest.img")));
if (strlen($instr) < 149000) {
mysql_query ("insert into pix (title, imgdata) values (\"".
$_REQUEST[whatsit].
"\", \"".
$image.
"\")");
} else {
$errmsg = "Too large!";
}
}

// Find out about latest image

$gotten = @mysql_query("select * from pix order by pid desc limit 1");
if ($row = @mysql_fetch_assoc($gotten)) {
$title = htmlspecialchars($row[title]);
$bytes = $row[imgdata];
} else {
$errmsg = "There is no image in the database yet";
$title = "no database image available";
// Put up a picture of our training centre
$instr = fopen("../wellimg/ctco.jpg","rb");
$bytes = fread($instr,filesize("../wellimg/ctco.jpg"));
}

// If this is the image request, send out the image

if ($_REQUEST[gim] == 1) {
header("Content-type: image/jpeg");
print $bytes;
exit ();
}
?>

Here's the latest picture

---

Please upload a new picture and title

Please choose an image to upload:

Please enter the title of that picture:

then:

---

By Graham Ellis - graham@wellho.net




Note that this is a fully working example that you can try out on our server using the link above. For security reasons, we have changed the logins above but it works exactly as it's displayed above on our test systems. As you'll appreciate, various measures are taken with the online example (and those measures may change from time to time) to ensure the security and acceptability of content posting and this security may include changes that prevent posting and / or monitor your activity. See our privacy and copyright statement that's available as a link in the footer of this page.