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

Question

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?

Kamran Khan · Accepted Answer · 2010-11-04 10:13:56Z

1

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!");
}

answered Nov 4, 2010 at 10:13

Kamran Khan

9,9842 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neil Knight · Accepted Answer · 2010-11-04 10:12:28Z

0

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();

answered Nov 4, 2010 at 10:12

Neil Knight

48.8k26 gold badges136 silver badges193 bronze badges

Comments

Oliver · Accepted Answer · 2010-11-04 11:32:47Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related