Click here to Skip to main content
11,485,334 members (77,913 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL
I have this stored procedure which is working fine in Management Studio,extracts the name of all those database mdf files present in physical path. If a file name exists in master files, it returns 1 and if not it returns 0.This is fine.
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[GetExistingDatabasePath]    Script Date: 06/19/2014 07:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetExistingDatabasePath]
@enteredDatabaseName varchar(50)
 
AS
BEGIN
IF EXISTS(
SELECT name, physical_name AS current_file_location FROM sys.master_files 
where name=@enteredDatabaseName)
RETURN 1
ELSE
RETURN 0
 
END
Now, corresponding to the sp , i have following c# code.
string connectionString = "uid=sa;password=login@234;server=BNGTSIDTPC0738;database=master";
SqlConnection connection = new SqlConnection(connectionString);
 

//stored procedure to check if the database with this name exist or not in physical drive
SqlCommand cmd = new SqlCommand("GetExistingDatabasePath", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//local variable to store data entered by user in textbox
string userSideDatabaseName;
 
//assigning the database entered in textbox to enter the variable 

Console.WriteLine("Enter the database name");
userSideDatabaseName = Console.ReadLine();
 
//passing the this text box data to stored procedure parameter
SqlParameter param1 = cmd.Parameters.Add("@enteredDatabaseName", SqlDbType.VarChar, 50);
param1.Value = userSideDatabaseName;
bool IsRowExisting = false;
connection.Open();
//this will return true if there is existence of Database with same name in Physical path
IsRowExisting = Convert.ToBoolean(cmd.ExecuteNonQuery());
connection.Close();
if (IsRowExisting == true)
{
 
Console.WriteLine(IsRowExisting);
}
Problem is when the code is executed then it returns true for all cases whether the file exists or not.
Posted 18-Jun-14 17:50pm
Edited 18-Jun-14 18:26pm
DamithSL61.2K
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Stored Procedures in SQL Server can only RETURN integers. If you need to return anything other than a single integer, then you should use one of these methods (some explained by the previous answers):

Use a SELECT in your procedure

Use an OUTPUT Parameter

Use a User Defined Function instead

Update your Stored procedure and code as follows:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[GetExistingDatabasePath] Script Date: 06/19/2014 07:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetExistingDatabasePath]
@enteredDatabaseName varchar(50)
@Result BIT OUTPUT
 
AS
BEGIN
IF EXISTS(
SELECT name, physical_name AS current_file_location FROM sys.master_files 
where name=@enteredDatabaseName)
SET @Result = 1
ELSE
SET @Result = 0
 
END


Now, corresponding to the sp , i have following c# code.
string connectionString = "uid=sa;password=login@234;server=BNGTSIDTPC0738;database=master";
SqlConnection connection = new SqlConnection(connectionString);
 
 
//stored procedure to check if the database with this name exist or not in physical drive
SqlCommand cmd = new SqlCommand("GetExistingDatabasePath", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//local variable to store data entered by user in textbox
string userSideDatabaseName;
 
//assigning the database entered in textbox to enter the variable 
 
Console.WriteLine("Enter the database name");
userSideDatabaseName = Console.ReadLine();
 
//passing the this text box data to stored procedure parameter
SqlParameter param1 = cmd.Parameters.Add("@enteredDatabaseName", SqlDbType.VarChar, 50);
   SqlParameter param2 = cmd.Parameters.Add("@Result", SqlDbType.Bit);
    param2.Direction = ParameterDirection.Output; 
param1.Value = userSideDatabaseName;
bool IsRowExisting = false;
connection.Open();
//this will return true if there is existence of Database with same name in Physical path
//IsRowExisting = Convert.ToBoolean(cmd.ExecuteNonQuery());
IsRowExisting = Convert.ToBoolean(param2.Value);
connection.Close();
if (IsRowExisting == true)
{
 
Console.WriteLine("Already exists!");
}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 380
1 Maciej Los 365
2 Mehdi Gholam 160
3 Raje_ 145
4 Frankie-C 134
0 Sergey Alexandrovich Kryukov 8,819
1 OriginalGriff 7,982
2 Sascha Lefèvre 3,199
3 Maciej Los 2,841
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 18 Jun 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100