Working with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET

Large objects come in many flavors. The LOBs can be broadly classified as Character Large Objects (CLOBs) or Binary Large Objects (BLOBs). Luckily, SQL server provides different data types to handle these different flavors of data. Have a look at the following table that lists various SQL Server data types that are used in conjunction with LOBs.

The Text, NText, and Image data types come from the previous versions of SQL Server. It is recommended that you use the newer varchar(MAX), nvarchar(MAX), and varbinary(MAX) data types instead. Varbinary(MAX) is the data type that allows you to deal with large binary data; therefore, the discussion will focus on using varbinary(MAX) data in .NET applications.

Storing BLOBs in SQL Server

Binary data comes in variety of flavors. Word documents, PDF invoices, Photos, product images, raw data are all examples of BLOB data. When storing BLOBs as a part of your .NET application, you have two choices:

  1. Store BLOBs as disk files
  2. Store BLOBs directly inside a SQL Server database

Both the choices have some pros and cons. The first approach is good if:

  1. You have few hosting resources in terms of SQL Server space.
  2. The BLOB data is being used by third parties. For example, you might have images that are being created by a graphics designer.
  3. You want to use a file system-based editor to manipulate the binary data. For example, you might be using Microsoft Office Suite to edit Word and Excel files.

The biggest drawback of storing binary data as a physical file is that your database easily can lose track of the binary data. Say, for example, you have a column that stores URLs of product images. If you move your application from one server to another, you must manually ensure that physical files representing product images are shipped along with the rest of the application. Also, any deletions to the database records also must be played on the physical image files.

Storing BLOBs directly inside SQL server provides the following advantages:

  1. Because BLOBs and related data are always together, there is no possibility of mismatch.
  2. BLOBs are transferred from one server to another along with the usual data transfer process. There is no need for any manual efforts to keep them in sync.
  3. BLOB data is also backed up along with routine SQL Server data.
  4. Applications, such as Content management Systems and Photo Albums, are tightly dependent on the BLOB data they use. Such applications are greatly benefited by storing BLOBs directly in the database.

In summary, depending on the application being developed, you should decide whether storing BLOB data in SQL Server is the way to go. That said, generally CMS systems, photo albums, slide show applications, document management systems, and so forth are good candidates for storing BLOBs inside SQL Server.

sursa: http://www.developer.com/net/asp/article.php/3761486/Working-with-Binary-Large-Objects-BLOBs-Using-SQL-Server-and-ADONET.htm

What is method overloading?

Method overloading allows us to write different version of the same method in a class or derived class. Compiler automatically select the most appropriate method based on the parameter supplied. 

public class MultiplyNumbers{
    public int Multiply(int a, int b){
        return a * b;
    }

    public int Multiply(int a, int b, int c){
        return a*b*c;
    }    
}

To call the above method, you can use following code.
MultiplyNumbers mn = new MultiplyNumbers();
int number = mn.Multiply(2, 3)
// result = 6
int number1 = mn.Multiply(2, 3, 4)
// result = 24

You can’t have a overload method with same number parameters but different return type. In order to create overload method, the return type must be the same and parameter type must be different or different in numbers.

What is a tuple

Also known as a “Record”, or a “Row”.  The word “tuple” is used because it can refer to a row returned as part of a result set as well as a record in a table.  Strictly speaking, a row returned from most queries is not a record, as that row does not exist in permanent storage anywhere …. it is created by the query.  Hence, “tuple”.

Class vs. struct

Structs differ from classes in several important ways:

  • Structs are value types.
  • All struct types implicitly inherit from the class System.ValueType.
  • Assignment to a variable of a struct type creates a copy of the value being assigned.
  • The default value of a struct is the value produced by setting all value type fields to their default value and all reference type fields to null.
  • Boxing and unboxing operations are used to convert between a struct type and object.
  • The meaning of this is different for structs.
  • Instance field declarations for a struct are not permitted to include variable initializers.
  • A struct is not permitted to declare a parameterless instance constructor.
  • A struct is not permitted to declare a destructor.

Sursa: http://msdn.microsoft.com/en-us/library/aa664471(VS.71).aspx

How to get a list of SQL Server tables and their row counts

The most straightforward method for determining the number of rows in a table, is to use the following:
SELECT COUNT(*) FROM tablename
You can also use the system stored procedure, sp_spaceused, to determine other information, such as data and index size:
EXEC sp_spaceusedtablename

To get an *approximate* count for all tables, you can use the following:
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = ‘U
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC

The sysindexes table is usually a little bit inaccurate, because it is not updated constantly. It will also include the ‘dtproperties’ table, which is one of those hybrid tables that falls neither under the ‘system’ nor ‘user’ category. It does not appear in Enterprise Manager’s “Tables” view if you choose to hide system objects, but it shows up above.
In any case, it is generally not recommended to query against the system objects directly, so please only use the above for rough, ad-hoc guesstimates

Undocumented methods

Please don’t rely on these methods, or use them in production code. Undocumented stored procedures may change or be disabled in a future release, or even a service pack / hotfix; or, they could disappear altogether.
The following creates your own diagnostic page to give you a quick overview of how many rows are in each table in a specific database. It uses my favorite of the undocumented, do-not-use-in-production system stored procedures, sp_MSForEachTable
CREATE PROCEDURE dbo.listTableRowCounts
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL VARCHAR(255)
SET @SQL = ‘DBCC UPDATEUSAGE (‘ + DB_NAME() + ‘)
EXEC(@SQL)

CREATE TABLE #foo
(
tablename VARCHAR(255),
rc INT
)

INSERT #foo
EXEC sp_msForEachTable
‘SELECT PARSENAME(”?”, 1),
COUNT(*) FROM ?

SELECT tablename, rc
FROM #foo
ORDER BY rc DESC

DROP TABLE #foo
END
(The only reason a #temp table is used here is because we want the results ordered by largest row counts first. If the order can be arbitrary, you can just run the EXEC by itself.)

Replicating ‘Taskpad / Table Info’ view

Several people have asked how to mimic what taskpad view in Enterprise Manager does for Table Info, without having to scroll or search to find tables, and without listing all of the (largely superfluous and mostly built-in) index names. This view shows all the tables, rowcounts, reserved size and index size. Here is a stored procedure that does it one better… it essentially fires an sp_spaceused (which includes data and free space, in addition to reserved and index size). Now, before you use it, please exercise caution. This relies on system tables, and the undocumented sp_msForEachTable. Its behavior may change between versions and service packs, so don’t rely on it for production code.
CREATE PROCEDURE dbo.allTables_SpaceUsed
AS
BEGIN
SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

CREATE TABLE #t
(
id INT,
TableName VARCHAR(32),
NRows INT,
Reserved FLOAT,
TableSize FLOAT,
IndexSize FLOAT,
FreeSpace FLOAT
)

INSERT #t EXEC sp_msForEachTable ‘SELECT
OBJECT_ID(PARSENAME(”?”,1)),
PARSENAME(”?”,1),
COUNT(*),0,0,0,0 FROM ?

DECLARE @low INT

SELECT @low = [low] FROM master.dbo.spt_values
WHERE number = 1
AND type = ‘E

UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM
(SELECT id, r = SUM(si.reserved), i = SUM(si.used)
FROM sysindexes si
WHERE si.indid IN (0, 1, 255)
GROUP BY id) x
WHERE x.id = #t.id

UPDATE #t SET TableSize = (SELECT SUM(si.dpages)
FROM sysindexes si
WHERE si.indid < 2
AND si.id = #t.id)

UPDATE #t SET TableSize = TableSize +
(SELECT COALESCE(SUM(used), 0)
FROM sysindexes si
WHERE si.indid = 255
AND si.id = #t.id)

UPDATE #t SET FreeSpace = Reserved – IndexSize

UPDATE #t SET IndexSize = IndexSize – TableSize

SELECT
tablename,
nrows,
Reserved = LTRIM(STR(
reserved * @low / 1024.,15,0) +
‘ ‘ + ‘KB’),
DataSize = LTRIM(STR(
tablesize * @low / 1024.,15,0) +
‘ ‘ + ‘KB’),
IndexSize = LTRIM(STR(
indexSize * @low / 1024.,15,0) +
‘ ‘ + ‘KB’),
FreeSpace = LTRIM(STR(
freeSpace * @low / 1024.,15,0) +
‘ ‘ + ‘KB’)
FROM #t
ORDER BY 1

DROP TABLE #t
END

Source: http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

BACKUP and RESTORE SQL DataBase across Network

BACKUP DATABASE databaseName TO DISK = ‘\\serverHostName\folder\backupName.bak’  WITH INIT

RESTORE DATABASE [databaseName]
FROM DISK = N‘\\serverHostName\folder\backupName.bak’
WITH FILE = 1,
MOVE N‘mdfSourceFileName’ TO N‘C:\folder\databaseName.mdf’,
MOVE N‘ldfSourceFileName’ TO N‘C:\folder\databaseName.ldf’,
NOUNLOAD, STATS = 10

more info http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

http://msdn.microsoft.com/en-us/library/ms179313.aspx