Assuming only basic knowledge of C#, Beginning C# 5.0 Databases teaches all the fundamentals of database technology and database programming readers need to quickly become highly proficient database users and application developers.
A comprehensive tutorial on both SQL Server 2012 and Visual Studio 2012, Beginning C# 5.0 Databases explains and demonstrates various techniques and ways to build a database application using T–SQL and C#. Full of practical, detailed examples, it’s been fully revised and updated for Winforms using C# 5.0 and Visual Studio 2012 and offers the most complete, detailed, and gentle introduction to database technology for all C# programmers at any level of experience.
- Comprehensively and concisely explains fundamental database concepts and database programming techniques
- Rich in step-by-step working examples of both T–SQL and C# programs
- Covers all the features ADO.NET most database programming ever requires
What you’ll learn
- How relational databases work and how to use them
- How to create database and table objects using SQL Server 2012
- How to write SQL queries to modify data in SQL Tables
- How to write SQL queries to retrieve data using various querying techniques, including JOINS
- How to write stored procedures in T–SQL and call them from C# 5.0 programs
- How C# 5.0 uses various techniques to access databases
- How to build Windows form based applications
- How to handle runtime anomalies i.e. Exceptions
- How to program with each individual object of ADO .NET
- How to use XML in database applications
- How to use LINQ to simplify C# database programming
- How to use ADO .NET Entity Framework
- How to use SQL CLR to create stored procedures using C#
Who this book is for
Beginning C# 5.0 Databases is for every C# programmer. Database programming requires relatively little knowledge of C# but a lot of knowledge about relational database concepts, database language SQL and ADO.NET. This book assumes no prior database experience and teaches you, always through hands–on examples, how to create and use relational databases with SQL Server 2012 and how to access them with C# 5.0 using ADO.NET. Almost every application needs to access a database, and this book teaches all the fundamentals you need and may ever need to develop professional database applications.
Table of Contents
- Getting and Understanding Your Tools
- Understanding Relational Databases
- Creating Database and Tables
- Manipulating Database Data
- Querying Database
- Using Stored Procedures
- Using XML
- Understanding Transactions
- Building Windows Form Applications
- Introduction to ADO .NET
- Handling Exceptions
- Making Connections
- Executing ADO.NET Commands to Retrieve Data
- Using Data Readers
- Using Data Sets and Data Adapters
- Using Data Controls with ASP.NET Applications
- Working with Text and Binary Data
- Using LINQ
- Using ADO.NET Entity Framework
- Using the CLR in SQL Server
Contents at a Glance......Page 3
Contents......Page 413
About the Author......Page 425
About the Technical Reviewer......Page 426
Acknowledgments......Page 427
Conventions......Page 5
Contacting the Author......Page 6
Getting and Understanding Your Tools......Page 8
Installing SQL Server 2012 Express......Page 9
Installing the AdventureWorks Database......Page 10
Attaching the AdventureWorks Sample Database......Page 11
Summary......Page 19
What Is a Database?......Page 20
Benefits of Using a Relational Database Management System......Page 21
Server Databases......Page 22
The Database Life Cycle......Page 23
Mapping Cardinalities......Page 24
Understanding Keys......Page 26
Entity Integrity......Page 27
Normalization Concepts......Page 28
Summary......Page 29
Types of SQL Server Databases......Page 30
The Architecture of a SQL Server Database......Page 32
Creating a Database in a Simple Way......Page 33
Exploring Database Properties......Page 36
Creating Database with Your Own Settings......Page 37
Understanding Table Fundamentals......Page 39
Creating a Table in SQL Server......Page 40
Adding an IDENTITY Column in a Table......Page 41
Summary......Page 44
Try It: Inserting a New Row......Page 46
How It Works......Page 47
Inserting Multiple Rows Through a Single INSERT Statement......Page 48
Updating Data......Page 50
How It Works......Page 51
Deleting Data......Page 52
Summary......Page 53
Retrieving Data......Page 54
How It Works......Page 56
Using the WHERE Clause......Page 57
Using Comparison Operators......Page 59
Sorting Data......Page 60
Try It: Writing an Enhanced Query......Page 61
Try It: Using the GROUP BY Clause......Page 62
How It Works......Page 63
Try It: Using the Percent (%) Character......Page 64
Try It: Using the Underscore (_) Character......Page 65
Try It: Using the Square Bracket ([]) Characters......Page 66
Try It: Using the Square Bracket and Caret ([^ ]) Characters......Page 67
Aggregate Functions......Page 68
How It Works......Page 69
Try It: Using the COUNT Function......Page 70
Try It: Using T-SQL Date and Time Functions......Page 71
How It Works......Page 72
Try It: Using the IN Operator......Page 73
Try It: Using the NOT IN Operator......Page 74
How It Works......Page 75
Try It: Using the BETWEEN Operator......Page 76
Try It: Using the NOT BETWEEN Operator......Page 77
How It Works......Page 78
How It Works......Page 79
Inner Joins......Page 80
Table Aliasing......Page 81
Outer Joins......Page 83
Summary......Page 86
Creating Stored Procedures......Page 87
Try It: Working with Stored Procedures in SQL Server......Page 88
How It Works......Page 89
Try It: Creating a Stored Procedure with an Input Parameter......Page 90
Try It: Creating a Stored Procedure with an Output Parameter......Page 91
How It Works......Page 93
Try It: Modifying Your Trivial Stored Procedure......Page 94
Displaying the Definition of Stored Procedures......Page 96
How It Works......Page 97
Try It: Renaming a Stored Procedure......Page 98
How It Works......Page 99
Try It: Deleting a Stored Procedure......Page 100
Summary......Page 101
Defining XML......Page 102
Benefits of Storing Data As XML......Page 103
Understanding XML Documents......Page 104
Understanding the XML Declaration......Page 105
Try It: Using FOR XML RAW (Attribute-centric)......Page 106
Try It: Using FOR XML RAW (Element-centric)......Page 107
Try It: Renaming the row Element......Page 108
How It Works......Page 109
Try It: Using FOR XML AUTO......Page 110
Observations About XML AUTO Formatting......Page 111
Try It: Creating a Table to Store XML......Page 112
Try It: Storing and Retrieving XML Documents......Page 113
Summary......Page 114
What Is a Transaction?......Page 115
Understanding ACID Properties......Page 116
Transaction Design......Page 117
T-SQL Statements Allowed in a Transaction......Page 118
Local Transactions in SQL Server 2012......Page 119
Guidelines to Code Efficient Transactions......Page 120
Coding Transactions in T-SQL......Page 121
Summary......Page 133
Understanding Windows Forms......Page 135
Position of Controls......Page 136
Working with Windows Forms......Page 138
Understanding the Design and Code Views......Page 140
Sorting Properties in the Properties Window......Page 141
Categorized View......Page 142
Setting the Properties of Solutions, Projects, and Windows Forms......Page 144
Working with Controls......Page 145
Dock Property......Page 150
Anchor Property......Page 151
Adding a New Form to the Project......Page 154
Implementing an MDI Form......Page 160
Summary......Page 168
Understanding ADO.NET......Page 169
Moving from ADO to ADO.NET......Page 170
ADO.NET and the .NET Base Class Library......Page 171
Understanding ADO.NET Architecture......Page 173
Understanding .NET Data Providers......Page 175
Understanding the SQL Server Data Provider......Page 176
Understanding the OLE DB Data Provider......Page 177
Understanding the ODBC Data Provider......Page 178
Data Providers Are APIs......Page 179
Summary......Page 180
The System.Exception Class......Page 181
Try It: Creating a File-Handling Application......Page 182
Try It: Causing an Exception to Occur and Observing the Behavior......Page 187
Exploring the Type, Message, and StackTrace Properties of an Exception......Page 190
Try It: Adding Exception-Handling Statements......Page 192
How It Works......Page 193
Summary......Page 194
Introducing the Data Provider Connection Classes......Page 195
Try It: Using SqlConnection......Page 196
How It Works......Page 198
Debugging Connections to SQL Server......Page 200
Security and Passwords in SqlConnection......Page 201
Connection String Parameters for SqlConnection......Page 202
Connection Pooling......Page 203
Try It: Displaying Connection Information......Page 204
How It Works......Page 206
Try It: Connecting to SQL Server with the OLE DB Data Provider......Page 208
Summary......Page 210
Assigning Text to a Command......Page 211
Try It: Using the ExecuteScalar Method......Page 215
Executing Commands with Multiple Results......Page 219
Executing Nonquery Statements......Page 222
Creating a Stored Procedure to Perform a Delete Operation......Page 230
Summary......Page 237
Understanding Data Readers in General......Page 238
Using Ordinal Indexers......Page 243
Using Column Name Indexers......Page 247
Using Typed Accessor Methods......Page 248
Getting Data About Data......Page 255
Getting Data About Tables......Page 261
Using Multiple Result Sets with a Data Reader......Page 265
Summary......Page 270
Understanding the Object Model......Page 272
A Brief Introduction to Data Sets......Page 273
A Brief Introduction to Data Adapters......Page 274
A Brief Introduction to Data Tables, Data Columns, and Data Rows......Page 275
Working with Data Sets and Data Adapters......Page 276
Filtering and Sorting in a Data Set......Page 280
Comparing FilterSort to PopDataSet......Page 286
Using Data Views......Page 287
Modifying Data in a Dataset......Page 291
Propagating Changes to a Data Source......Page 295
InsertCommand Property......Page 296
Command Builders......Page 299
Using Data Sets and XML......Page 303
Understanding Typed and Untyped Data Sets......Page 308
Summary......Page 309
Understanding Web Functionality......Page 310
Understanding ASP.NET and Web Pages......Page 311
Understanding the Visual Studio 2012 Web Site Types......Page 312
Understanding the Layout of an ASP.NET Web Site......Page 315
Understanding the Web UI of ASP.NET Web Apps......Page 317
Using the Repeater Control......Page 321
Summary......Page 326
Understanding SQL Server Text and Binary Data Types......Page 327
Storing Images in a Database......Page 328
Retrieving Images from a Database......Page 334
Working with Text Data......Page 340
Retrieving Data from Text Columns......Page 345
Summary......Page 350
Introduction to LINQ......Page 351
Architecture of LINQ......Page 353
LINQ Project Structure......Page 354
Using LINQ to Objects......Page 355
Using LINQ to SQL......Page 358
Using LINQ to XML......Page 364
Summary......Page 368
Understanding ADO.NET Entity Framework 5.0......Page 369
Working with the Entity Data Model......Page 370
Summary......Page 387
Introducing SQL CLR......Page 388
Enabling SQL CLR Integration......Page 389
Try It: Creating a SQL CLR Stored Procedure Using C#......Page 391
How It Works......Page 395
Try It: Deploying SQL CLR C# Stored Procedure in SQL Server......Page 396
Try It: Executing the SQL CLR Stored Procedure......Page 398
Summary......Page 400
A, B......Page 401
D......Page 402
F, G......Page 404
M, N......Page 405
R......Page 406
S......Page 407
T......Page 408
X, Y, Z......Page 409
