CODEWITHSUNDEEP
c#visual-studiot-sql
Martin
Martin

Reputation: 2349

Where/how do I write T-SQL in my C# code and get the result (Visual Studio 2008)

I am trying to write t-sql in C# (visual studio). I have this code to connect to the database:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;

namespace WindowsFormsApplication8
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection cnn;
            connetionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Xtreme\\Desktop\\CardsDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            cnn = new SqlConnection(connetionString);
            try
            {


                cnn.Open();
                MessageBox.Show("Connection Open ! ");

                cnn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }


        }
    }
}

Where/how do I write the T-SQL code and how do I get the result?

Can someone give me an simple select example in my code?

Upvotes: 2

Views: 400

Answers (3)

Oliver
Oliver

Reputation: 45109

Ardman already showed you how to execute any arbitary sql command. But what i didn't answer very well, is where to put your sql statement.

I think it is a very bad behaviour and also not very good to read if you put your statement directly into the code.

A better method (in my eyes) is the following:

  • Within your project create a new folder (maybe called Queries)
  • Right click this folder and select Add - New Item
  • In the dialog just select Textfile and give it the name about what this query will do
    • Make sure you replace the file extension from .txt to .sql
  • Just put your statement right into this file
  • In the Resource Editor add this file as a resource
  • Now you can access this sql statement within your project just by using Properties.Resources.MySqlStatement

Upvotes: 0

Kamran Khan
Kamran Khan

Reputation: 9978

You can use DataAdapter.Fill Method:

try
{
    using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM Employee", cnn))
    {
        // Use DataAdapter to fill DataTable
        DataTable t = new DataTable();
        a.Fill(t);

        // Render data onto the screen
        dataGridView1.DataSource = t; //if you want.
    }
}
catch (Exception ex)
{
    MessageBox.Show("Problem!");
}

Upvotes: 1

Neil Knight
Neil Knight

Reputation: 48597

Create a SqlCommand and set the CommandType to CommandType.Text. Then add your SQL to the CommandText property of the SqlCommand.

SqlCommand command = new SqlCommand(commandName, (SqlConnection)Connection);
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM MyTable";
IDataReader result = command.ExecuteReader();

Upvotes: 0

Related Questions