What is the book about and who's it for?

This book is for developers looking for code samples and step-by-step instructions for building SQL Server 2000 solutions with Visual Basic .NET. The book focuses on the integration of SQL Server 2000 with .NET technologies tapped via Visual Basic .NET. The .NET Framework content is at a professional level, but it isn’t just for techies. This book doesn’t assume any prior knowledge of the .NET Framework. I include coverage of the many special features that tie Visual Basic .NET and SQL Server 2000 closely to one another.

Getting Started with Visual Basic .NET for SQL Server 2000

My goal in this chapter is to equip you conceptually for the rest of the book. Therefore, this chapter includes material that acquaints you with application development techniques and topics for SQL Server 2000 and Visual Basic .NET. The discussion of the samples in this chapter generally aims to convey broad approaches instead of how to run the sample. All the remaining chapters except for Chapter 8, another conceptual chapter, have samples with instructions aimed at professional developers.

Tables and Data Types

This chapter targets the design and programming of SQL Server tables with T-SQL (Transact Structured Query Language). SQL Server database administrators and developers use T-SQL for programming database administration and data access. This chapter begins with an exploration of SQL Server data types. This chapter's code samples introduce you to programming techniques for SQL Server tables. By understanding how to script database objects, such as tables, you can readily duplicate those database objects across multiple servers.

Programming Data Access with T-SQL

This chapter presents T-SQL programming techniques for data access. You can use these techniques in many environments -- in Query Analyzer, encapsulated within views, in stored procedures and user-defined functions -- and in Visual Basic .NET. When you finish working through this chapter, you should possess a foundation for extracting precisely the data you need from a SQL Server database for any application.

Programming Views and Stored Procedures

The preceding chapter introduced you to programming data access with T-SQL. This chapter builds on and goes beyond the introduction in two explicit ways: First it introduces views by describing their uses with various types of row sources. Second it introduces you to stored procedures by reviewing their uses and the statements for creating and altering them, and by focusing on the use of parameters and local variables that are often found in stored procedures.

Programming User-Defined Functions and Triggers

This chapter completes the book’s review of database objects that facilitate the reuse of T-SQL code. The beginning of the chapter introduces user-defined functions (UDFs). Your applications can apply UDFs as if they were built-in functions. The chapter explores the different kinds of UDFs that you can create and illustrates scenarios for developing and applying them. The last part of the chapter deals with triggers. Visual Basic developers are likely to find it useful to think of triggers as event procedures for tables and views. This chapter’s coverage of triggers starts with an overview of the key concepts for designing and applying triggers and concludes with a series of four samples that demonstrate the kinds of uses to which you can put triggers. The main purpose of the samples is to highlight syntax conventions for different types of triggers and illustrate broad design issues. The T-SQL inside a trigger can reference other database objects. For example, the last trigger sample references a UDF defined earlier in the chapter.

SQL Server 2000 XML Functionality

You will gain from this chapter an overall understanding of XML functionality in SQL Server with an emphasis on access to that functionality via T-SQL, XML schemas and templates, and hypertext transport protocol (HTTP). Chapter 12 will refocus on XML so that you can build on the understanding presented here while you learn how to tap the XML capabilities in SQL Server with Visual Basic .NET and related technologies, such as ADO.NET. With XML, developers can build incredibly powerful solutions for retrieving and maintaining data over Web connections. As the word gets out about how easy it is to create these solutions, you will become an evangelist for using XML with SQL Server.

SQL Server 2000 Security

In these times, all information technology professionals, including database developers, have a pressing need to protect their systems. There is no magic pill you can take to inoculate your systems against any kind of security attack that ever did, or will, exist. Securing systems is a matter of learning your applications and judiciously applying the security measures appropriate for your computing environment. This chapter exposes you to the security features available with Microsoft SQL Server 2000.

The scripts in this chapter differ from those of preceding chapters in that the login for database connections varies between scripts. By using different logins, the samples enable you to evaluate the effects of different types of logins as well as understand how to create those logins.

Overview of the .NET Framework

This chapter attempts to familiarize you with the architecture of the .NET Framework and related technologies, including ASP.NET and XML Web services. See Chapter 1 for introductory material on Visual Basic .NET and ADO.NET. My goal in this chapter isn't to empower you as a programmer with these technologies. Instead, I aim to show how the technologies complement one another. In the process, I feel you will develop an appreciation of why it is important for you to adopt the .NET Framework and start programming it with Visual Basic .NET. You will learn about such concepts as ADO.NET, ASP.NET, and XML Web services. A separate chapter targets each of these concepts. The chapters drill down into specific details with code samples that demonstrate important techniques.

Creating Windows Applications

This chapter covers creating solutions with Windows applications and managing the Windows Forms in those applications with Visual Basic .NET code. The focus of the chapter is on intermediate to advanced topics, such as creating and using classes, inheritance, event programming, and handling run-time errors with structured exception handling. For each topic covered, I identify what’s new for the topic with Visual Basic .NET. In some cases, such as inheritance and structured exception handling, the emphasis is wholly on what’s new because Visual Basic .NET introduces these capabilities to Visual Basic programmers.

Programming Windows Solutions with ADO.NET

The chapter has five major sections.

The chapter begins with a brief overview of ADO.NET design issues. This section drills down into the data set object model. This material will help you to programmatically coordinate data set objects with SQL Server database objects.

Next the chapter presents programming samples for making a connection to a SQL Server database. This presentation also demonstrates how to secure access to your SQL Server databases. (See Chapter 7 for more on SQL Server security.)

Coverage moves from making a connection to forward-only, read-only data access. Several samples reveal the flexibility you can achieve with this form of data access for displaying data. In addition, you learn how to dynamically configure the source that a DataReader object contains at run time.

The next section introduces how to display data set objects with Windows Forms. It covers how to programmatically bind Windows Forms controls -- such as text boxes, combo boxes, and data grids -- to data set objects. You’ll also learn how to display parent-child data relationships so that users can control the display of child data by manipulating a control for the parent data.

The chapter concludes with a section that demonstrates how to update, insert, and delete rows in a SQL Server database from a Windows form.

Programming ASP.NET Solutions

This chapter introduces Visual Basic developers to building Web solutions with ASP.NET. I start the chapter with an overview of conceptual and hands-on design issues that can make you more productive when you get around to writing code. Next I introduce code development techniques with ASP.NET even while I continue to highlight special Web issues, such as dynamically adapting to the browser on which your solution runs. Communicating between Web pages is different than with traditional Windows applications because the HTTP protocol used in nearly all Web applications is stateless. That is, the protocol by itself doesn’t convey anything about the history of visits to a Web server by a browser in a session. An entire section goes into clarifying session management and offering a mix of remedies optimized for different situations. Perhaps this chapter’s most important section discusses and demonstrates how to use SQL Server databases in ASP.NET solutions. You will learn about classic data issues, such as browsing a data source, creating parent-child forms, and performing update, insert, and delete operations. The final section highlights how to graphically and programmatically manage the validity of data on Web pages in ASP.NET.

Managing XML with Visual Basic .NET

After overviews of the Web releases and the XML technologies in the .NET Framework, this chapter presents a series of samples in four sections. The first set of samples targets creating XML documents with SQL queries and annotated XML schemas. The second set of samples expands the functionality available with the first set of samples by imparting the ability to generate XML documents based on run-time input. In addition, this second section demonstrates the use of XPath queries to return XML documents and reveals alternative means to produce equivalent results with SQL queries. The third section particularly examines the interplay between ADO.NET data sets and XML documents. It also examines ways of performing advanced XPath queries. A couple of samples in this section demonstrate how to process XML documents with hierarchical data sets as well as how to manage changes to a remote SQL Server database. The chapter’s closing section drills down on how to use XSLT to prepare Web pages with HTML tables based on XML documents that you create with Visual Basic .NET from a SQL Server data source.

Creating Solutions with XML Web Services

After presenting an overview of core Web services concepts, this chapter presents a series of samples in a hands-on style. The idea is to acquaint you with the basics of building XML Web services. The chapter conveys step-by-step instructions and code samples for creating solutions with Web services. The chapter’s content extends and complements the information on Web services that you can find in the Visual Studio .NET documentation and the SQL Server 2000 Web Services Toolkit support materials. Separate sections drill down on creating Web service and client applications using contrasting approaches. For example, you can build both the Web service and client applications with Visual Studio .NET. Alternatively, other sections show how to build a Web service with the Web Services Toolkit and the client application with Visual Studio .NET. The sample presentations describe how to build the solution folders and mention especially important files and procedures for each solution.