Transcript CSharpDatabase

C# programming with database
Basic guideline
First step
 Install SQL Server 2008/2010 (Professional edition if
possible)
 Install Visual Studio 2008/2010
 you can begin to do!!!
http://www.sqlserverclub.com/essentialguides/how-to-installsql-server-2008-step-by-step-guide.aspx
SQL Server
 After installation,
ADO.NET
 Includes number of classes that can be used to retrieve,
manipulate, and update data in databases
 Can work with databases in a disconnect manner
 Database table(s) can be retrieved to a temporary file
 To retrieve data first, you must connect to the database
 ADO.NET uses a feature called data providers to connect,
execute commands, and retrieve results from a database
7
Data Providers
 Microsoft SQL Server
 Applications using SQL Server 7.0 or later
 Oracle
 Applications using Oracle data sources
 Object Linking and Embedding Database (OLE DB)
 Applications that use Microsoft Access databases
 Open Database Connectivity (ODBC)
 Applications supported by earlier versions of Visual Studio
8
Data Providers (continued)
 Classes are encapsulated into a different namespace by
provider
 Four core classes make up each data provider namespace
 Connection
 Command
 DataReader or DataSet
 DataAdapter
9
Data Providers (continued)
10
C# programming language
 Go to Settings.settings
C# programming language
 Create a new class to work with Database, for example
DatabaseHelper class
 In this class, create the connection such as:
// declare the class level connection variable
SqlConnection conn;
// init with the above connection string in the
constructure of the class
conn = new
SqlConnection(global::DoAnTinHocProject.Properties
.Settings.Default.DoAnConnectionString);
conn.Open();
C# programming language
public DataSet SelectData(string SQLCommand)
{ // hàm mẫu để thực thi các câu lệnh select, trả về dữ liệu được chứa trong dataset
DataSet ds = new DataSet();
try
{
if (sqlconn.State != ConnectionState.Open) sqlconn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlAdp = new SqlDataAdapter();
sqlComm.Connection = sqlconn;
sqlComm.CommandText = SQLCommand;
sqlComm.CommandType = CommandType.Text;
sqlAdp.SelectCommand = sqlComm;
sqlComm.ExecuteNonQuery();
sqlAdp.Fill(ds);
return ds;
}
catch (Exception)
}
{ return null; }
C# programming language
Public int ExecuteCommand(string SQLCommand)
{ // hàm mẫu thực thi các câu lệnh insert, update, delete)
try
{
if (sqlconn.State != ConnectionState.Open) sqlconn.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlconn;
sqlComm.CommandText = SQLCommand;
sqlComm.CommandType = CommandType.Text;
return sqlComm.ExecuteNonQuery();
}
catch (Exception)
{
return -1;
}
}
C# programming language
 The example of using the above functions
- To display data, use the DataGridView Control
C# programming language
//Load data on datagridview control, transfer the
SELECT command into the function SelectData
DatabaseHelper h = new DatabaseHelper();
dataGridView1.DataSource = h.SelectData("Select *
from Workdays ").Tables[0];
C# programming language
// To insert data, transfer the Insert statement to the
function ExecuteCommand
DatabaseHelper h = new DatabaseHelper();
h.ExecuteCommand(“Insert into Workdays (ID, Day) values
(1, ‘Monday’)”);
// Update, Delete is similar with this