Access Tips: Working Out a Person's Age. When storing personal data, it is normal practice to record a person's date of birth rather than their age. This is for the simple reason that the date of birth never changes, but the age will be different next year. If you need to show someone's age you should ask Access to calculate it for you. Calculating someone's age from their birthday is not quite as simple as it might at first seem!
Of course, you take the current year and subtract it from the year in which they were born. This will give you an accurate age in years - providing they have had their birthday this year. So, to be absolutely certain you need to decide if the day and month of their birthday has passed yet. If it has you subtract their birth year from the current year. Fortunately, there are a couple of easier ways. To Calculate Approximate Age Use this expression... To Calculate Accurate Age Use this expression... It makes use of the fact that Access uses serial numbers to store dates Applying the Technique. C# - implementing a progress bar to show work being done. How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET. Writing Managed Stored Procedures using C# Introduction With the integration of the CLR with SQL Server 2005, we can create database objects using modern object-oriented languages like VB.NET and C#.
In fact, for pure non-data access code like computation, string parsing logic etc., we should use .NET for writing the SQL server objects. It is also advisable to use managed code for writing the store procedure. Also for accessing webservices, exploring OOP's programming for better reusability and read external files, it is good to used managed store procedure. This article is trying to explain the simple and required steps that are require starting the creation of Manage Stored Procedure using C# and using them.
The Project We will create a Visual Studio 2005 database project for the managed stored procedure. Creating the Database project: Open Microsoft Visual Studio 2005 and create a SQL Server Project. File->New->Project->Database Adding a database reference: Now it will ask for a database reference. Adding a Stored Procedure: Walkthrough: Creating a Stored Procedure in Managed Code. You can now write stored procedures for SQL Server 2005 databases in managed code by using .NET Framework languages such as Visual Basic, C#, and C++. Stored procedures written in managed code are called CLR stored procedures.
You can create SQL stored procedures by adding Stored Procedure items to SQL Server projects. After successfully deploying to a SQL Server, stored procedures created in managed code are called and executed like any other stored procedures. Tasks illustrated in this walkthrough include the following: Creating a new Windows Application project.Creating a stored procedure in managed code.Deploying the stored procedure to a SQL Server 2005 database.Creating a script to test the stored procedure on the database.Querying data in the database to confirm that the stored procedure executes correctly. In order to complete this walkthrough, you need: To create the new SQL Server project To connect to the AdventureWorks sample database To create the SQL Server stored procedure Tasks. C# - NHibernate and DTOs.
Scott Hanselman - Extending NerdDinner: Exploring Different Database Options. Home - NHibernate Forge. Inserting and Retrieving images from SQL Server Database using C# Advertisements I think storing images in a database can save a developer loads of time and can ease his life while dealing with visuals, either creating a local application or a web application. Think of storing your images of a certain web application in a database instead in a directory exits somewhere in a cloud which one-a bit headache and second-completely transparent with respects to its location. So, here we are, I am going to create here a local application through which we can insert and retrieve an image using c# from a sql server 2005 database. First I created a database named TestImage in which I created a table called test_table holding two columns id_image(datatype: nvarchar[50]) and pic(datatype: image). Second create a new project in visual studio, select windows application, language c# and name it as TestImage.
So, now right click on your form and select view code. Here declare a global string a data adapter and a dataset using System.IO; using System.Data.SqlClient; try. SQL Bulk Copy with C#.Net. Introduction Programmers usually need to transfer production data for testing or analyzing. The simplest way to copy lots of data from any resources to SQL Server is BulkCopying. .NET Framework 2.0 contains a class in ADO.NET "System.Data.SqlClient" namespace: SqlBulkCopy.
The bulk copy operation usually has two separated phases. In the first phase you get the source data. The source could be various data platforms such as Access, Excel, SQL.. You must get the source data in your code wrapping it in a DataTable, or any DataReader class which implements IDataReader. After that, in the second phase, you must connect the target SQL Database and perform the bulk copy operation. The bulk copy operation in .Net is a very fast way to copy large amount of data somewhere to SQL Server. Solution walkthrough While you are programming for bulk copy, first open a connection for the source data. Then we are retrieving data from the source with SqlCommand and SqlDataReader classes. OK. That's all. Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet. Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects.
Here's a few handy things to help you out with converting your projects. Check in now and then as this short list will eventually grow as more things come up. Converting NULL values Access: NZ(Value, ValueToReturnIfNull) T-SQL: COALESCE(Value, ValueToReturnIfNull) -- or -- ISNULL(Value, ValueToReturnIfNull) Checking for NULLs Access: WHERE Value IS NULL -- or -- WHERE ISNULL(Value) (note the difference from T-SQL's ISNULL) T-SQL: WHERE Value IS NULL String Segments Access: MID(StringVal, StartPos, [length] ) (length is optional) T-SQL: SUBSTRING(StringVal, StartPos, length ) (length is required!) Finding a String within a String Access: SELECT INSTR(start, StringToSearch, StringToFind) T-SQL: SELECT CHARINDEX(start, StringToSearch, StringToFind) Reverse a String Access: SELECT STRREVERSE(StringVal) T-SQL: SELECT REVERSE(StringVal) Modulo Operator.
Comparing .NET XML Serializers: Part One. Download source - 43.74 KB Introduction I was working on a large .NET application (.NET 3.5 SP1), and we began to argue about which XML serializer is better for saving application state: user preferences, open windows, queries, and the like. The power of serializers and quality of resulting XML is best investigated on actual samples. I have built a small engine that runs each serializer on a set of samples, captures resulting XML or exception, and saves them in an XML output file.
The results of this work are below. Upcoming Parts This part contains mostly pure facts and very little analysis. XML Serializers Used Starting with .NET 3.0, there are at least two other serializers available for general use besides XmlSerializer: * In .NET 3.5 SP1, Microsoft made a significant change in the behavior of DataContractSerializer. Qualities Researched We were interested primarily in these things: Serialization Samples Brief Analysis of the Serialization Samples XML Serializer Good Bad XAML Serializer History. XML Serializable Generic Dictionary - Paul Welter's Weblog. XML Serializable Generic Dictionary For some reason, the generic Dictionary in .net 2.0 is not XML serializable.
The following code snippet is a xml serializable generic dictionary. The dictionary is serialzable by implementing the IXmlSerializable interface. using System; using System.Collections.Generic; using System.Text; using System.Xml.Serialization; [XmlRoot("dictionary")] public class SerializableDictionary<TKey, TValue> : Dictionary<TKey, TValue>, IXmlSerializable #region IXmlSerializable Members public System.Xml.Schema.XmlSchema GetSchema() return null; public void ReadXml(System.Xml.XmlReader reader) XmlSerializer keySerializer = new XmlSerializer(typeof(TKey)); XmlSerializer valueSerializer = new XmlSerializer(typeof(TValue)); bool wasEmpty = reader.IsEmptyElement; reader.Read(); if (wasEmpty) return; while (reader.NodeType ! Reader.ReadStartElement("item"); reader.ReadStartElement("key"); TKey key = (TKey)keySerializer.Deserialize(reader); reader.ReadEndElement(); this.Add(key, value); #endregion.
Databases supported by NHibernate - JBoss Community. NHibernate is primarily tested on Microsoft SQL Server 2000. It is also known to work on these databases: Microsoft SQL Server 2005/2000 SQL Server 2005 and 2000 are the primary databases used by the developers of NHibernate. Configuration example: <? Xml version="1.0" ? <? For SQL Server 2000, change the dialect to NHibernate.Dialect.MsSql2000Dialect. Issues SQL Server sometimes ignores columns specified in ORDER BY clause of a query if they are not included in the SELECT clause. From Person p order by p.Company.Name Oracle Oracle 9i and 10g are supported, both using Microsoft driver (System.Data.OracleClient) and using Oracle driver (Oracle.Data.OracleClient).
Microsoft's driver does not handle long character strings correctly. Oracle cannot handle empty strings (""), you should use null instead. Microsoft Access Microsoft Access has its own dialect and driver (contributed by Lukas Krejci). They are currently in a separated library: NHibernate.JetDriver.dll (in NHibernateContrib package). <? <? <? <? Updating Data to a Database. This topic illustrates how to update data in a database using a DataSet. It is important to remember that you can also insert, update, and delete data in a database directly using a SqlCommand. Understanding the concepts covered in Populate a DataSet from a Database will help you understand the current topic. Some of the topics covered in Populate a DataSet from a Database include retrieving data from a database and into a DataSet, and how the DataSet is separate and distinct from the database. Once the DataSet is loaded, you can modify the data, and the DataSet will track the changes.
The DataSet can be considered an in-memory cache of data retrieved from a database. The DataSet consists of a collection of tables, relationships, and constraints. Note that the DataTable must return a DataRow through the NewRow method. You can change data in a DataRow by accessing the DataRow. MyDataSet.Tables("Customers").Rows(0)("ContactName")="Peach" Use the Delete method to remove the Row. C# Station: ADO.NET Tutorial Lesson 06 - Adding Parameters to Commands. By Joe Mayo, 9/5/04 This lesson shows you how to use parameters in your commands.2 Here are the objectives of this lesson: Understand what a parameter is. Be informed about the benefits of using parameters. Learn how to create a parameter. Learn how to assign parameters to commands. Introduction When working with data, you'll often want to filter results based on some criteria. As you know, the SQL query assigned to a SqlCommand object is simply a string. // don't ever do this SqlCommand cmd = new SqlCommand( "select * from Customers where city = '" + inputCity + "'"; Don't ever build a query this way!
Instead of dynamically building a string, as shown in the bad example above, use parameters. Using parameterized queries is a three step process: Construct the SqlCommand command string with parameters. The following sections take you step-by-step through this process. preparing a SqlCommand Object for Parameters Declaring a SqlParameter Object Each parameter in a SQL statement must be defined. C# Station: ADO.NET Tutorial Lesson 07 - Using Stored Procedures. By Joe Mayo, 9/12/04 This lesson shows how to use stored procedures in your data access code. Here are the objectives of this lesson: Learn how to modify the SqlCommand object to use a stored procedure. Understand how to use parameters with stored procedures.
Introduction A stored procedures is a pre-defined, reusable routine that is stored in a database. Executing a Stored Procedure In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures. // 1. create a command object identifying // the stored procedure SqlCommand cmd = new SqlCommand( "Ten Most Expensive Products", conn); // 2. set the command object so it knows // to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; While declaring the SqlCommand object above, the first parameter is set to "Ten Most Expensive Products". Sending Parameters to Stored Procedures Using parameters for stored procedures is the same as using parameters for query string commands. Summary. Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0 - ScottGu's Blog. June 22nd 2006 Update: We've now published a whole series of new data tutorials based on this origional post.
You can read all about it here. One of my goals over the next few weeks is to publish a number of quick tutorial postings that walkthrough implementing common data binding design patterns using ASP.NET 2.0 (master details, filtering, sorting, paging, 2-way data-binding, editing, insertion, deletion, hierarchical data browsing, hierarchical drill-down, optimistic concurrency, etc, etc).
To help keep these samples shorter, and to help link them together, I’m going to use a common DAL (data access layer) implementation across the samples that is based on SQL Server’s Northwind sample database. To build the DAL layer I decided to use the new DataSet Designer that is built-into Visual Web Developer (which you can download for free) as well as VS 2005, and which provides an easy way to create and encapsulate data access components within an application. SuppliersTest2.aspx: FROM Products.