C# and SQL Server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello,

Below is my Stored Procedure and Code.

Using this i would like to load the values in the database to my Form Controls "Datasource" property.

But i could not be able to retrieve any values.

My Code:

C#

_conn = new SqlConnection(ConnectionString);
            var command = new SqlCommand("LoadAppointment", _conn) { CommandType = CommandType.StoredProcedure };
            _conn.Open();
            var dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            var dt = new DataTable();
            dt.Load(dr);

My Stored Procedure:

SQL

USE [Scheduler]
GO
/****** Object:  StoredProcedure [dbo].[LoadAppointment]    Script Date: 03/04/2013 14:44:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LoadAppointment]

AS

SELECT * FROM [Scheduler].[dbo].[tblAppointments]

Note: I am not supposed to use a Dataset.

Posted 3-Mar-13 22:31pm

ArunAmalraj

Updated 3-Mar-13 22:36pm

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

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

RajeshRaushan · Answer 1 · 2013-03-03T22:45:00

Solution 1

You need to provide the schema for data table.
In you code you have simply created the table - but that does not have any columns added to that.

Please go through this link - It will help you achieving this.

DataTable.Load Method

Posted 3-Mar-13 22:45pm

RajeshRaushan

willington.d · Answer 2 · 2013-03-03T22:45:00

I have used SqlDataReader instead of using SqlDataAdapter or DataSet.

C#

DataTable dtTable = new DataTable();
            SqlConnection sqlCon = new SqlConnection(@"Server=WILLINGTON\SQLEXPRESS;Database=TestDB;Trusted_Connection=True;");
            SqlCommand sqlCmd = new SqlCommand("SELECT * FROM USERS", sqlCon);

            if (sqlCon.State != ConnectionState.Open)
                sqlCon.Open();

            SqlDataReader dr = sqlCmd.ExecuteReader();
            dtTable.Load(dr);

            if (sqlCon.State != ConnectionState.Open)
                sqlCon.Close();

C# and SQL Server 2005

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

C# and SQL Server 2005

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us