Database

david boon wrote:

how to write a stored procedure for filling a datagrid

CREATE PROCEDURE GetEmployees
( 
  @startOfLast varchar(128)
)
AS
BEGIN
  SELECT EmployeeID, LastName, FirstName, Department
    FROM MyEmployeesTable
   WHERE LastName LIKE @startOfLast + '%'
END

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

public class SqlServerUsers
{
  private static SqlConnection GetConnection()
  {
    // get a database connection; substitute with your
    // own connectionString, or calls to get the connection
    // information from web.config    
    string sCon = "Server=(local);Database=master;Trusted_Connection=True;";
    SqlConnection con = new SqlConnection(sCon);
    con.Open();    
    return con;
  }
  
  
  public static DataSet GetLoggedInUsers()
  {
    // pick the level of detail you want
    string sql = "SELECT hostname, loginame, cmd, program_name " 
               + "  FROM  master.dbo.sysprocesses "
               + " WHERE hostname != ''";
               
    // or something like this:
    // string sql = "SELECT DISTINCT hostname, loginame " 
    //            + "  FROM  master.dbo.sysprocesses "
    //            + " WHERE hostname != ''";
    
    using (SqlConnection con = GetConnection())
    {
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = con;
      cmd.CommandText = sql;
      
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds);
      
      return ds;      
    }
                   
  }
  
}

Diego F. wrote:

I have a database with many tables, but other person is updating it, so there are tables that have changes: new fields, new relationships...

I have the backup with the new version and I have to update my database, but I don't want to lose the tables information. Is it possible to do that automatically? There are many tables to do it manually.

thedom2 wrote:

one of the propellor heads to figure it out

IF NOT EXISTS(SELECT *
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME='SorDetail'
              AND SCHEMA_NAME = 'dbo'
              AND COLUMN_NAME = @Column)
BEGIN
    -- Column does not exist.
    RETURN 0;
END

IF NOT EXISTS(SELECT * 
              FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME='MON_AppMsgPubStatistics'
              AND SCHEMA_NAME = 'dbo'
              AND COLUMN_NAME = @Column)
BEGIN
    -- Column does not exist.
    RETURN 0;
END