• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3533
  • Last Modified:

running sql commands using c# .net

what is the best way to use c# .net application to run a bunch of sql commands. i would like sql command be outside the code in a flat file.
0
jgordin
Asked:
jgordin
  • 3
1 Solution
 
DhaestCommented:
Execute multiple SQL statements using a SqlCommand object

using System;
using System.Data;
using System.Data.SqlClient;

class ExecuteMultipleSQL
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
      "INSERT INTO Employee (ID, FirstName) " +
      "VALUES (11, 'Jason');" +
      "SELECT ID, FirstName " +
      "FROM Employee " +
      "WHERE ID = 11;" +
      "UPDATE Employee " +
      "SET FirstName = 'Jason 2' " +
      "WHERE ID = 11;" +
      "SELECT ID, FirstName " +
      "FROM Employee " +
      "WHERE ID = 11;" +
      "DELETE FROM Employee " +
      "WHERE ID = 11;";

    mySqlConnection.Open();

    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    do
    {
      while (mySqlDataReader.Read())
      {
        Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
        Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
      }
      Console.WriteLine(""); // visually split the results
    } while (mySqlDataReader.NextResult());

    mySqlDataReader.Close();
    mySqlConnection.Close();
  }
0
 
DhaestCommented:
If you don't want to retrieve nothing, then you can replace this:
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    do
    {
      while (mySqlDataReader.Read())
      {
        Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
        Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
      }
      Console.WriteLine(""); // visually split the results
    } while (mySqlDataReader.NextResult());


by mySqlCommand.executeNonQuery()
0
 
jgordinAuthor Commented:
thanks. is it possible to keep sql command in a separate file?
0
 
DhaestCommented:
Off course, you can easily load them into a string ...

Public Function GetFileContents(ByVal FullPath As String) As String

        Dim strContents As String
        Dim objReader As StreamReader
        Try

            objReader = New StreamReader(FullPath)
            strContents = objReader.ReadToEnd()
            objReader.Close()
            Return strContents
        Catch Ex As Exception
            throw ex
        End Try
    End Function
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now