Archive
C# Saving any File to database SQL Server
Since it’s not mentioned what database you mean I’m assuming SQL Server. Below solution works for both 2005 and 2008.
You have to create table with VARBINARY(MAX) as one of the columns. In my example I’ve created Table Raporty with column RaportPlik being VARBINARY(MAX) column.
Method to put file into database from drive:
public static void databaseFilePut(string varFilePath) {
byte[] file;
using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read)) {
using (var reader = new BinaryReader(stream)) {
file = reader.ReadBytes((int) stream.Length);
}
}
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
sqlWrite.ExecuteNonQuery();
}
}This method is to get file from database and save it on drive:
public static void databaseFileRead(string varID, string varPathToNewLocation) {
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
sqlQuery.Parameters.AddWithValue("@varID", varID);
using (var sqlQueryResult = sqlQuery.ExecuteReader())
if (sqlQueryResult != null) {
sqlQueryResult.Read();
var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write))
fs.Write(blob, 0, blob.Length);
}
}
}This method is to get file from database and put it as MemoryStream:
public static MemoryStream databaseFileRead(string varID) {
MemoryStream memoryStream = new MemoryStream();
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
sqlQuery.Parameters.AddWithValue("@varID", varID);
using (var sqlQueryResult = sqlQuery.ExecuteReader())
if (sqlQueryResult != null) {
sqlQueryResult.Read();
var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
//using (var fs = new MemoryStream(memoryStream, FileMode.Create, FileAccess.Write)) {
memoryStream.Write(blob, 0, blob.Length);
//}
}
}
return memoryStream;
}This method is to put MemoryStream into database:
public static int databaseFilePut(MemoryStream fileToPut) {
int varID = 0;
byte[] file = fileToPut.ToArray();
const string preparedCommand = @"
INSERT INTO [dbo].[Raporty]
([RaportPlik])
VALUES
(@File)
SELECT [RaportID] FROM [dbo].[Raporty]
WHERE [RaportID] = SCOPE_IDENTITY()
";
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
using (var sqlWriteQuery = sqlWrite.ExecuteReader())
while (sqlWriteQuery != null && sqlWriteQuery.Read()) {
varID = sqlWriteQuery["RaportID"] is int ? (int) sqlWriteQuery["RaportID"] : 0;
}
}
return varID;
}
source : http://stackoverflow.com/questions/2579373/saving-any-file-to-in-the-database-just-convert-it-to-a-byte-arrayFunction Table and CURSOR SQL SERVER
CREATE FUNCTION [dbo].[TB_BBOOKS]
(
@ReportDate smalldatetime,@acc_type varchar(30)
)
RETURNS @RETURN TABLE
(
CATEGORY varchar(30),AUD float,CHF float,EUR float,GBP float,HKD float,
IDR float,JPY float,SGD float,THB float,USD float
)
AS
BEGIN
DECLARE @PORTFOLIO_ID int
DECLARE @SUM_AMOUNT float
DECLARE @CURRENCY_ID char(3)
DECLARE @AUD float
DECLARE @CHF float
DECLARE @EUR float
DECLARE @GBP float
DECLARE @HKD float
DECLARE @IDR float
DECLARE @JPY float
DECLARE @SGD float
DECLARE @THB float
DECLARE @USD float
DECLARE ACursor CURSOR FOR
SELECT PORTFOLIO_ID FROM TYPE_ACOUNT WHERE TYPE_ACOUNT=@acc_type
OPEN ACursor
— Perform the first fetch.
FETCH NEXT FROM ACursor INTO @PORTFOLIO_ID
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AUD = 0
SET @CHF = 0
SET @EUR = 0
SET @GBP = 0
SET @HKD = 0
SET @IDR = 0
SET @JPY = 0
SET @SGD = 0
SET @THB = 0
SET @USD = 0
DECLARE ACursor2 CURSOR FOR
SELECT CURRENCY_ID FROM CURRENCY ORDER BY CURRENCY_ID
OPEN ACursor2
FETCH NEXT FROM ACursor2 INTO @CURRENCY_ID
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PORTFOLIO_ID=26
BEGIN
SELECT @SUM_AMOUNT=(-1*sum(ABS(AMOUNT))) FROM MM_DEALS_BBOOKS WHERE PORTFOLIO_ID in (13,15,26)
AND CASH_FLOW_DATE>@ReportDate and REPORT_DATE<=@ReportDate
AND CURRENCY_ID=@CURRENCY_ID
END ELSE BEGIN
SELECT @SUM_AMOUNT=sum(AMOUNT) FROM MM_DEALS_BBOOKS WHERE PORTFOLIO_ID=@PORTFOLIO_ID
AND CASH_FLOW_DATE>@ReportDate and REPORT_DATE<=@ReportDate
AND CURRENCY_ID=@CURRENCY_ID
END
IF @CURRENCY_ID=’AUD’
BEGIN
SET @AUD = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’CHF’
BEGIN
SET @CHF = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’EUR’
BEGIN
SET @EUR = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’GBP’
BEGIN
SET @GBP = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’HKD’
BEGIN
SET @HKD = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’IDR’
BEGIN
SET @IDR = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’JPY’
BEGIN
SET @JPY = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’SGD’
BEGIN
SET @SGD = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’THB’
BEGIN
SET @THB = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’USD’
BEGIN
SET @USD = @SUM_AMOUNT
END
FETCH NEXT FROM ACursor2 INTO @CURRENCY_ID
END
CLOSE ACursor2
DEALLOCATE ACursor2
INSERT @Return values (dbo.getPORTFOLIONAME(@PORTFOLIO_ID),@AUD ,@CHF ,@EUR ,@GBP,@HKD ,
@IDR ,@JPY ,@SGD ,@THB ,@USD)
— Perform the first fetch.
FETCH NEXT FROM ACursor INTO @PORTFOLIO_ID
END
CLOSE ACursor
DEALLOCATE ACursor
RETURN
END
String Manipulation On Query SQL Server
SELECT LEFT(LastName, 3) AS FirstThreeLettersOfLastName FROM Employees
Results:
FirstThreeLettersOfLastName --------------------------- Buc Cal Dav Dod Ful Kin Lev Pea Suy
Similarly, the RIGHT function lets you retrieve the portion of the string starting from the right. The following example retrieves the first two characters from the employees’ last names, starting from the right:
SELECT RIGHT(LastName, 2) AS LastTwoLettersOfLastName FROM Employees
Results:
LastTwoLettersOfLastName ------------------------ an an io th er ng ng ck ma
Notice that the RIGHT and LEFT functions don’t check for blank characters. In other words, if your string contains a couple of leading blanks, the LEFT(string_variable, 2) will return you two blank spaces, which might not be exactly what you want. If your data needs to be left-aligned, you can use the LTRIM function, which removes the leading blanks. For instance, the following UPDATE statement will left-align (remove any number of leading blanks) the last names:
UPDATE Employees SET LastName = LTRIM(LastName)
Similarly, if your data is padded with spaces, and you don’t want to see spaces in your output, you can use the RTRIM function. For instance, suppose you have a variable that’s 20 characters long, but the last two characters are blank. The following queries show what happens when you run the RIGHT function on such a variable before and after removing the trailing blanks:
DECLARE @string_var VARCHAR(20) SELECT @string_var = 'my string variable ' SELECT RIGHT(@string_var, 2) AS BeforeRemovingTrailingSpaces SELECT RIGHT(RTRIM(@string_var), 2) AS AfterRemovingTrailingSpaces
Results:
BeforeRemovingTrailingSpaces ---------------------------- AfterRemovingTrailingSpaces --------------------------- le
At times, you might want to retrieve part of the string that does not necessarily start at the first character from the left or right. In such cases, the SUBSTRING function is your friend. It retrieves the portion starting at the specified character and brings back the number of characters specified; the syntax is SUBSTRING(string_variable, starting_character_number, number_of_characters_to_return). The following example will retrieve four characters from the employees’ last names, starting at the third character:
SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM Employees
Results:
PortionOfLastName ----------------- chan llah voli dswo ller ng verl acoc yama
Notice that the SUBSTRING function finds the starting character by counting from the left. In other words, if you run SUBSTRING(LastName, 3, 4) against the last name of “Buchanan”, you start on the third character from the left—”c”.
What if you want to start from the right side, you ask? Fortunately, there is a string function called REVERSE that gives you a mirror image of the given string. Check out the mirror image of Northwind employees’ last names:
SELECT REVERSE(LastName) AS MirrorImage FROM Employees
Results:
MirrorImage -------------------- nanahcuB nahallaC oilovaD htrowsdoD relluF gniK gnilreveL kcocaeP amayuS
This way, if you want to use the SUBSTRING function starting from the right, you can use the combination of the REVERSE and SUBSTRING functions, as follows:
SELECT SUBSTRING(REVERSE(LastName), 3, 4) AS PortionOfLastNameMirrorImage FROM Employees
Results:
PortionOfLastNameMirrorImage ---------------------------- nahc hall lova rows lluF iK ilre ocae ayuS
Similarly, if you want to see a mirror image of the portion, you can use REVERSE to reverse the result of the SUBSTRING, as follows:
SELECT REVERSE(SUBSTRING(LastName, 3, 4)) AS MirrorImageOfPortion FROM Employees
Results:
MirrorImageOfPortion -------------------- nahc hall ilov owsd rell gn lrev coca amay
You often need to find an occurrence of a particular character or number of characters inside a string. For example, you might want to find a position of a comma inside last names if they contain a last name and a suffix, separated by a comma.
The following example shows how this can be achieved using the CHARINDEX function:
DECLARE @string_var VARCHAR(20) SELECT @string_var = 'Brown, Jr. ' SELECT CHARINDEX( ',', @string_var) AS comma_position
Results:
comma_position -------------- 6
The PATINDEX function is very similar to CHARINDEX in the way it works—it also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX, and it searches for a pattern. If you use a % wildcard with CHARINDEX, you won’t find anything unless your data contains percent signs. If you’re searching for a pattern at the end of the string expression, you only have to use the % wildcard at the beginning of the pattern to be found, as in PATINDEX ('%pattern_to_find', string_expression).
An example of using PATINDEX is provided in the following code:
DECLARE @companyName VARCHAR(20), @pattern_position INT
SELECT @CompanyName = 'Green & Waldorf'
SELECT @pattern_position = PATINDEX('%Wal%', @CompanyName)
SELECT @pattern_position
Result:
----------- 9
Occasionally, you might need to replace some characters inside a string. For instance, suppose you’re designing a report of employee titles, and you want to use the 'Customer Service' phrase instead of 'Sales' in titles. However, other reports still need to show the regular titles. No need to worry—the REPLACE function is here to help, as the following example demonstrates:
SELECT REPLACE(Title, 'Sales', 'Customer Service') AS ManipulatedTitle, Title FROM Employees
Results:
| ManipulatedTitle | Title |
| Customer Service Representative | Sales Representative |
| Vice President, Customer Service | Vice President, Sales |
| Customer Service Representative | Sales Representative |
| Customer Service Representative | Sales Representative |
| Customer Service Manager | Sales Manager |
| Customer Service Representative | Sales Representative |
| Customer Service Representative | Sales Representative |
| Inside Customer Service Coordinator | Inside Sales Coordinator |
| Customer Service Representative | Sales Representative |
This example was relatively simple because you knew exactly what sequence of characters you wanted to replace. What if you only know the position of the characters? Suppose that you have some clients who contain ampersands (&) in their names, and your reporting tool cannot handle special characters such as ampersands. The STUFF function can help you replace such special characters with their equivalent expressions.
You saw how to find the position of a specific character or number of characters using CHARINDEX. Now, you can apply that knowledge and use the STUFF function to replace characters based on their position.
The following example determines the position of the offending character (&) in the string variable and then replaces it with 'AND':
DECLARE @CompanyName VARCHAR(20), @amp_position INT SELECT @CompanyName = 'Green & Waldorf' SET @amp_position = CHARINDEX( '&', @CompanyName) SELECT @CompanyName = STUFF(@CompanyName, @amp_position, 1, 'AND') SELECT @CompanyName AS CompanyName
Results:
CompanyName -------------------- Green AND Waldorf
Another common need is finding the length of the character string or some portions thereof. For instance, you might have a need to replace leading spaces with zeros in some character columns. The number of zeros you need depends on how many spaces each column contains, which can vary from one row to the next. To find out how many leading spaces you have, you can use the LEN function, as the following example demonstrates:
DECLARE @AlphaCode VARCHAR(10) SELECT @AlphaCode = ' AB03543' SELECT LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode)) AS NumberOfLeadingSpaces
Results:
NumberOfLeadingSpaces --------------------- 2
Next, to replace the leading spaces, you can use the combination of the REPLACE and REPLICATE functions. You’ve already seen the REPLACE function in action. The REPLICATE function simply prints a character or a number of characters as many times as you specify, as follows:
SELECT REPLICATE('MyCoolString', 5)
Result:
------------------------------------------------------------ MyCoolStringMyCoolStringMyCoolStringMyCoolStringMyCoolString
To replace the leading spaces with zeros, you simply replicate the '0' string times the number of leading spaces in your column:
DECLARE @AlphaCode VARCHAR(10)
SELECT @AlphaCode = ' AB03543'
SELECT @AlphaCode = REPLICATE('0', LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode)))
+ LTRIM(@AlphaCode)
SELECT @AlphaCode AS NewAlphaCode
Result:
NewAlphaCode ------------ 00AB03543
Notice that unlike Visual Basic and some other programming languages, string concatenation in Transact-SQL is accomplished with a plus (+) sign rather than an ampersand (&).
Another specific string function you might want to be aware of is SPACE, which works exactly like REPLICATE, except it takes a single parameter. The parameter specifies how many spaces you want printed:
SELECT SPACE(12) AS Spaces
Result:
Spaces ------------
For reporting purposes, you also might have to change the case of your output. This is a simple task using the UPPER and LOWER functions. For example, the following query will return the employees’ last and first names in mixed case:
SELECT UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, (LEN(FirstName) - 1))) + ' ' + UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, (LEN(LastName) - 1))) AS FullName FROM Employees
Results:
FullName --------------------------------- Nancy Davolio Andrew Fuller Janet Leverling Margaret Peacock Steven Buchanan Michael Suyama Robert King Laura Callahan Anne Dodsworth
In some cases, you might wish to see the ASCII representation of your characters. You’ll use the ASCII function more often when comparing characters without knowing whether they’re in upper- or lowercase. Keep in mind that uppercase and lowercase letters translate into different ASCII values, as the following example shows:
SELECT ASCII('W') AS UpperCase, ASCII('w') AS LowerCase
Results:
UpperCase LowerCase ----------- ----------- 87 119
The UNICODE function works just like ASCII, except it accepts the unicode character value as input. This can be useful if you’re working with international character sets.
Another useful function is CHAR. Although it’s difficult to think of a business example when you need to see some weird characters on the report, it’s often necessary to append a carriage return, line feed, or both to your output. In such cases, you can effectively use the CHAR function, as follows:
SELECT 'My Output' + CHAR(10) + CHAR(13) + 'AnotherOutput'
Results:
------------------------ My Output AnotherOutput
The NCHAR function works exactly like CHAR, except it returns the unicode character.
The QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally, it’s a bad idea to use reserved words, special characters, and spaces inside your object names. However, if you’re working with tables imported from data sources other than SQL Server, or if you inherit a database from you predecessor colleagues, you might not have a choice. The QUOTENAME function is actually very simple—it appends square brackets to the beginning and end of the string expression, and therefore makes such an expression a valid SQL Server identifier. The following example creates a temporary table with the column name that contains spaces:
CREATE TABLE #temp (id_column INT NULL)
DECLARE @column_name VARCHAR(50), @sql_string VARCHAR(200)
SELECT @column_name = QUOTENAME('invalid column name')
SELECT @sql_string = 'ALTER TABLE #temp ADD ' + @column_name
+ 'VARCHAR(50)'
EXEC (@sql_string)
SELECT * FROM #temp
Results:
id_column invalid column name ----------- --------------------------------------------------
The STR function can be considered as a special case of the CAST or CONVERT functions, both of which let you convert the variable from one data type into another compatible datatype. As the name implies, the STR function converts an integer (or a decimal) value into a string. The nice part about the STR function is that it lets you specify the length of the string variable returned, as well as how many decimal points to include in the string variable. For instance, the following example converts a decimal value into a string expression and rounds one decimal place:
SELECT STR(1.2546, 6, 3)
Result:
------ 1.255
The other two string functions available in SQL Server are SOUNDEX and DIFFERENCE, which happen to be rather similar. I’ve also found them to be extremely difficult to use. SOUNDEX provides a numeric representation of the string, and is supposed to help you determine whether two strings sound alike. DIFFERENCE, on the other hand, will provide you with a degree of similarity (or lack thereof) between two character expressions. If the SOUNDEX values are the same for the two strings passed to the DIFFERENCE function, the degree of similarity is the highest: 4. Otherwise, the DIFFERENCE function will return 3, 2, 1, or 0. The DIFFERENCE function can be used when you wish to find all customers with a name that sounds similar to a known value, as in the following example:
SELECT ContactName FROM customers WHERE DIFFERENCE (ContactName, 'ana') > 2
Results:
ContactName ------------------------------ Ana Trujillo Antonio Moreno Hanna Moos Janine Labrune Ann Devon Aria Cruz Lino Rodriguez Annette Roulet John Steel Jaime Yorres Jean Fresnière Simon Crowther Rene Phillips
Triger Sql Server – Delete Related data on others Table
Triger bisa digunakan untuk menghapus data pada tabel yang lain tanpa lewat program aplikasi.
contaoh Triger :
CREATE TRIGGER MyTriger ON MRM_DATA
FOR DELETE
AS
DECLARE @intRowCount int
SELECT @intRowCount = @@RowCount
IF @intRowCount > 0
BEGIN
DELETE MRM_IR_RATES
WHERE DATA_ID IN (SELECT DATA_ID FROM deleted)
DELETE MRM_GOV_ZERO_RATES
WHERE DATA_ID IN (SELECT DATA_ID FROM deleted)
DELETE MRM_FXO_VOLS
WHERE DATA_ID IN (SELECT DATA_ID FROM deleted)
DELETE MRM_FX_RATES
WHERE DATA_ID IN (SELECT DATA_ID FROM deleted)
DELETE MRM_FIS_PRICES
WHERE DATA_ID IN (SELECT DATA_ID FROM deleted)
DELETE MRM_CORP_ZERO_RATES
WHERE DATA_ID IN (SELECT DATA_ID FROM deleted)
END
GO
Resed a SQL Server Identity Column
Kita bisa menggunakankan Query Berikut :
DBCC CHECKIDENT
(
‘TABLE_NAME’ , RESEED , 1
)
Bay Notes 