Networked Application

Question

zachattack05 70 Posting Pro in Training

14 Years Ago

OK before I fumble around any more and end up needing to change everything again...I was wondering if someone would be kind enough to have a discussion with me about a networked solution.

Right now what I have is an Access database with forms that manage the data...this is great, except deploying such an application is difficult and, in my opinion, unprofessional looking.

I would like to transfer that solution to a C# solution. Here's my main concern:

I need to know how networked applications work (not technically, but with C#)...I know that I need to use an .mdf file (service based database) to achieve this...but before I start working on this any further I was hoping to get the answers to these questions:

1) The MS SQL Server Express is required to access the data in .mdf files, does the server only need to be installed on the "host" or "server" computer on the network or does it need to be installed on all client computers as well?

2) When a solution uses .mdf files to hold data, how do client computers connect to it? Does it need to be shared (\\server\data\myfile.mdf), does it need to be on a network drive (S:\data\myfile.mdf) or does the client computer need to connect to it using the standard SQL connection (by connecting to IP address ###.###.###.###:PORT#)? Or can it be any of those?

3) Does the MS SQL Server Express edition need to be configured in any special way? Are remote connections enabled for those who are connecting from different workstations etc...?

The reason why I ask this is because I created a simple "test" application that used .mdf files. I tried it in "debug" mode and everything was fine, then I decided to try "publishing" it and installing it and testing it to see how it would look installed...it wouldn't work. The solution started but crashed when the "get data" button was pressed...the button is supposed to query the database and get the data needed, if it can't find anything it is supposed to put a message in the box saying the database is empty...It simply failed. But worked in the debugger...I don't get it...

2 Contributors
11 Replies
397 Views
5 Days Discussion Span
Latest Post 14 Years Ago Latest Post by zachattack05

sknake 1,622 Senior Poster

14 Years Ago

>>1) The MS SQL Server Express is required to access the data in .mdf files, does the server only need to be installed on the "host" or "server" computer on the network or does it need to be installed on all client computers as well?

You should install the SQL Server on a stable machine and have all of the clients point to that machine. You don't ever need to deal with the raw data files of the SQL Server directly unless you're moving/remounting databases or using a compact edition. For your purposes you should ignore them.

>>2) When a solution uses .mdf files to hold data, how do client computers connect to it? Does it need to be shared (\\server\data\myfile.mdf), does it need to be on a network drive (S:\data\myfile.mdf) or does the client computer need to connect to it using the standard SQL connection (by connecting to IP address ###.###.###.###ORT#)? Or can it be any of those?

You connect to it using named pipes over the LAN. In a domain environment you can use windows authentication with the domain control so you don't even have to set up users in the database -- it will pull credentials from active directory for you. Its real easy!. Here is a code snippet of how you would connect to a database and run a query:

/// <summary>
    /// Concatenates the query string
    /// </summary>
    /// <param name="Sql"></param>
    /// <returns></returns>
    private static string GetText(List<string> Sql)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < Sql.Count; i1++)
        sb.AppendLine(Sql[i1]);
      return sb.ToString();
    }

    /// <summary>
    /// Builds a connection string
    /// </summary>
    /// <param name="server"></param>
    /// <param name="database"></param>
    /// <returns></returns>
    internal static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    /// <summary>
    /// Gets data after today using logic to determine what today is
    /// </summary>
    /// <returns></returns>
    internal static DataTable GetTable()
    {
      DataTable result = default(DataTable);

      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table");
      Sql.Add("Create Table #Table");
      Sql.Add("(");
      Sql.Add("  [Date] DateTime,");
      Sql.Add("  EventName varchar(100)");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-3, 'Event 1')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-2, 'Event 2')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-1, 'Event 3')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate(), 'Event 4')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+1, 'Event 5')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+2, 'Event 6')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+3, 'Event 7')");
      Sql.Add("");
      Sql.Add("Select *");
      Sql.Add("From #Table");
      Sql.Add("Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)");
      string query = GetText(Sql);
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Scott");
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }

3) Does the MS SQL Server Express edition need to be configured in any special way? Are remote connections enabled for those who are connecting from different workstations etc...?

Not "special" per se but by default it does not enable network connections making it useless for the most part. You can use the GUI wizard when installing MSSQL and it will ask you to "enable remote connections" and you will want to select "yes".

Edited 14 Years Ago by sknake because: n/a

sknake 1,622 Senior Poster

14 Years Ago

>>I'm not using the compact edition, but the "Express" edition, so it isn't the "full" version of the software. If I use the "publish" option in C# when I am finished with my application, will it include with it a distributable copy of the SQL Express server or should I instruct my users to download and set-up that software separately? When you write software like this, do you progrmatically mount the database files to the server? Or is that something you have to do manually?

The deployment projects in Visual Studio won't distribute SQL server for you with the installer (it can be done, but not easily). You can make a deployment project to install SQL Server or include the installer for sql server in a zip file with your software and have an application or batch file that runs it with the proper parameters. Here are the parameters you will probably want for this type of installation (SQL2005. I haven't messed around with the 2008 installer yet):

setup.exe /qb ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,Client_Components,Connectivity,SQL_SSMSEE INSTANCENAME=InstanceName SAVESYSDB=1 SQLBROWSERAUTOSTART=1 SQLAUTOSTART=1 AGTAUTOSTART=1 SECURITYMODE=SQL SAPWD=password DISABLENETWORKPROTOCOLS=0 ERRORREPORTING=1 SQMREPORTING=0 ADDUSERASADMIN=1 INSTALLSQLDIR="%ProgramFiles%\Microsoft SQL Server\"

I have a database setup & update assembly that I ship with my applications. The database is created when they run the .msi installer but nothing is populated (the database setup can fail for a lot of situation that can be easily corrected. However when installing if my installer encounters an error it rolls back the installation and uninstalls the product. Sure I could just ignore errors for that part of the installation, but I decided it would be better handled outside of the MSI engine) . When any of the applications connect to the sql server they test to see if certain tables exist. If the tables don't exist they are prompted if they want to setup the database. The prompt is useful so in case they select the wrong DB you don't create a ton of tables in the wrong db. One of the tables is "Install_Upgrade" and it contains a list of upgrades by #, a description, and execution date. It checks the schema in code versus the upgrade table and runs updates accordingly.

>>So, I would need to set-up users on the database for a network with no domain service? This is starting to seem quite complicated...Would I need different versions of the client software, one for domain networks and one without?
You would need to set up users but you don't need two different applications. I include a SQL Connection Form where it allows the user to input a server, database, asks to use windows or sql authentication, and lets them provide a user/pass if sql authentication is selected. You still access data the same way it just changes your connection string. Here are examples of the two types of connection strings I mentioned:

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    public static string BuildSqlNativeConnStr(string server, string database, string username, string password)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};", 
        server, 
        database,
        username,
        password);
    }

You could also set up a single user account when you create the database and hardcode the user/pass in your application and allow the user to select "application default credentials" so you don't have to bother with permissions as much but you need to support every authentication type. Sometimes people have their own SQL Server farm and want to mount the database on an existing server and use their corporate authentication.

Another caveat with permissions is by default some permissions aren't granted that may be required for an application to function. If you create a stored procedure in MSSQL with the "sa" account in the "dbo" schema then a normal user cannot execute the stored procedure unless you grant execute rights to that user. A lot of people who deploy their own sql server run their application under "sa" so they don't have to deal with it.

Another way permissions can be an issue is with upgrading. If someone without dbo rights for the database runs a new version of the application and trips the database update mechanism then the application could issue a CREATE TABLE statement that will fail if they do not have sufficient rights. So when you setup your database be sure you have dbo access.

It is quite complicated but once you get the code done for one application you can reuse it in your other applications and in my opinion there is no better alternative. SQL Server is awesome.

>>Is this something that can be enabled programmatically? Or if the MSSQLExpress installer is included with my installer, is there a way to use command lines switches at install time to make sure those options are enabled?

Notice the DISABLENETWORKPROTOCOLS=0 in the command line parameters I posted above for the SQL2005 installer.

>>Honestly this seems to be more than what I expected...Not that I am turned off by it, I just don't get how I can have so many pieces of software on my computer that frequently interact with data that is saved and I never have to install or configure any kind of SQL server or anything special...Is there a different way to write networked solutions?

Your best bet is to learn sql server and deploy it. If you something like access and share the database on a common network path you will have concurrency issues with multiple users reading and writing from the database, plus the performance will be horrible. If you can your own solution and handle TCP traffic and databasing then you have to worry about adjusting windows firewall, creating some kind of database files, be able to recover if the computer abruptly shuts down while writing database records, etc. Stick with MSSQL.

I attached a screenshot of my SQL connection form to give you an idea.

My personal recommendation for doing database setups and updates is to write your own assembly. I have tried just about every product on the market for database diffs, schema versioning, etc etc and they all have their strengths and weaknesses. The solution I rolled isn't nearly as feature rich as other products but they are features I don't need and it does exactly what I need it to do. Unless you enjoy telephone tech support calls you need to make your updater extremely fault-tolerant. I have used about 5 different approaches and am finally happy with the latest way I decided to do it. Here is my stub upgrade class, an attribute used for the upgrades, and the delegates for user prompts:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.ComponentModel;
using XXX.Extensions;

namespace XXX.CustomActions
{
  internal abstract class UpgradeRecord
  {
    /// <summary>
    /// These queries are executed before the upgrade. If any query returns a
    /// scalar value of 1 then the query is skipped.
    /// </summary>
    /// <returns></returns>
    internal virtual string[] GetSkipQueries()
    {
      return new string[0];
    }
    internal abstract string[] GetUpgradeQueries();
    public int GetUpgradeId()
    {
      AttributeCollection attrs = TypeDescriptor.GetAttributes(this);
      foreach (Attribute at in attrs)
      {
        UpgradeId up = (at as UpgradeId);
        if (up != null)
        {
          return up.ID;
        }
      }
      throw new InvalidOperationException("Upgrade id not defined");
    }

    /// <summary>
    /// Returns a skip query for a stored procedure
    /// </summary>
    /// <param name="ProcedureName">Use format: dbo.name</param>
    /// <returns></returns>
    protected string GetSkipQuery_SPROC(string ProcedureName)
    {
      return GetObjectIdQuery(ProcedureName, "P");
    }
    /// <summary>
    /// Returns a skip query for a table
    /// </summary>
    /// <param name="TableName">Use format: dbo.name</param>
    /// <returns></returns>
    protected string GetSkipQuery_Table(string TableName)
    {
      return GetObjectIdQuery(TableName, "U");
    }
    /// <summary>
    /// Returns a skip query for a scalar function
    /// </summary>
    /// <param name="FunctionName">Use format: dbo.name</param>
    /// <returns></returns>
    protected string GetSkipQuery_ScalarFunction(string FunctionName)
    {
      return GetObjectIdQuery(FunctionName, "FN");
    }
    private string GetObjectIdQuery(string Name, string ObjType)
    {
      return string.Format("IF OBJECT_ID('{0}', '{1}') IS NULL Select Cast(0 as bit) As Result ELSE Select Cast(1 as bit) As Result", Name, ObjType);
    }

    protected string GetColumnAddQuery(string Table, string Column, string DataType)
    {
      if (string.IsNullOrEmpty(Table)) throw new ArgumentNullException("Table");
      if (string.IsNullOrEmpty(Column)) throw new ArgumentNullException("Column");
      if (string.IsNullOrEmpty(DataType)) throw new ArgumentNullException("DataType");
      //
      Table = Table.Replace("[", string.Empty).Replace("]", string.Empty).Trim();
      Column = Column.Replace("[", string.Empty).Replace("]", string.Empty).Trim();
      DataType = DataType.Replace("[", string.Empty).Replace("]", string.Empty).Trim();
      //
      List<string> Sql = new List<string>();
      Sql.Add("IF NOT EXISTS ");
      Sql.Add("(");
      Sql.Add("  SELECT *");
      Sql.Add("  FROM INFORMATION_SCHEMA.COLUMNS");
      Sql.Add(string.Format("  WHERE TABLE_NAME=N'{0}' AND COLUMN_NAME = N'{1}'", Table, Column));
      Sql.Add(")");
      Sql.Add("BEGIN");
      Sql.Add(string.Format("  ALTER TABLE {0} ADD {1} {2}", Table, Column, DataType).Trim());
      Sql.Add("END");
      return Sql.GetText();
    }

    /// <summary>
    /// Get index drop query
    /// </summary>
    /// <param name="Table">Format: dbo.TableName</param>
    /// <param name="IndexName">Format: IX_Index_1</param>
    /// <returns></returns>
    protected string GetIndexDropQuery(string Table, string IndexName)
    {
      if (string.IsNullOrEmpty(Table)) throw new ArgumentNullException("Table");
      if (string.IsNullOrEmpty(IndexName)) throw new ArgumentNullException("IndexName");

      List<string> Sql = new List<string>();
      Sql.Add(string.Format("IF EXISTS(Select * From sys.indexes Where [object_id] = OBJECT_ID('{0}', 'U') and Name = '{1}')", Table, IndexName));
      Sql.Add("BEGIN");
      Sql.Add(string.Format("  DROP INDEX {0}.{1}", Table, IndexName));
      Sql.Add("END");
      return Sql.GetText();
    }
  }

  [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
  internal sealed class UpgradeId : Attribute
  {
    private int _upgradeId;
    private string _description;

    public int ID { get { return _upgradeId; } }
    public string Description { get { return _description; } }

    public int SMGR_ID { get; set; }
    public int XXX_ID { get; set; }

    private UpgradeId()
    {
    }
    public UpgradeId(int UpgradeId, string Description)
      : this()
    {
      this._upgradeId = UpgradeId;
      this._description = Description;
    }
  }

  public class UpgradeDictRecord
  {
    private int _upgradeId;
    private string _description;
    private Type _type;
    private int _smgr_id;
    private int _XXX_id;

    public int UpgradeID { get { return _upgradeId; } }
    public string Description { get { return _description; } }
    public Type UpgradeType { get { return _type; } }
    public int SMGR_ID { get { return _smgr_id; } }
    public int XXX_ID { get { return _XXX_id; } }

    private UpgradeDictRecord()
    {
      _smgr_id = -1;
    }
    public UpgradeDictRecord(int UpgradeId, string Description, int smgr_id, int XXX_id, Type type)
      : this()
    {
      this._upgradeId = UpgradeId;
      this._description = Description;
      this._type = type;
      this._smgr_id = smgr_id;
      this._XXX_id = XXX_id;
    }
  }
  /* -------------------------------------------------------------------- */
  public delegate void NonServerManagerDatabaseDetected(object sender, NonServerManagerDatabaseDetectedEventArgs e);
  public class NonServerManagerDatabaseDetectedEventArgs : EventArgs
  {
    private string _dbName;
    public bool SetupInNewDatabase { get; set; }
    public string DatabaseName { get { return _dbName; } }
    private NonServerManagerDatabaseDetectedEventArgs()
      : base()
    {
      this.SetupInNewDatabase = false;
    }
    public NonServerManagerDatabaseDetectedEventArgs(string DatabaseName)
      : this()
    {
      this._dbName = DatabaseName;
    }
  }
  /* -------------------------------------------------------------------- */
  public delegate void DatabaseUpgradeMessage(object sender, DatabaseUpgradeMessageEventArgs e);
  public class DatabaseUpgradeMessageEventArgs : EventArgs
  {
    private string _message;
    public string Message { get { return _message; } }
    public DatabaseUpgradeMessageEventArgs()
      : base()
    {
    }
    public DatabaseUpgradeMessageEventArgs(string Message)
      : this()
    {
      this._message = Message;
    }
  }
  /* -------------------------------------------------------------------- */
  public delegate void InvalidDatabaseSelected(object sender, InvalidDatabaseSelectedEventArgs e);
  public class InvalidDatabaseSelectedEventArgs : EventArgs
  {
    private string _dbName;
    public string DatabaseName { get { return _dbName; } }
    private InvalidDatabaseSelectedEventArgs()
      : base()
    {
    }
    public InvalidDatabaseSelectedEventArgs(string DatabaseName)
      : this()
    {
      this._dbName = DatabaseName;
    }
  }
  /* -------------------------------------------------------------------- */
}

Here is an implementation of the stub class. This actually creates the table used to track database versioning. The attribute defined in the code above is used below to assign an upgrade id and description for creating the record in the SQL server:

[UpgradeId(1, "Create Table: dbo.Install_Upgrade")]
  internal class Upgrade_1 : UpgradeRecord
  {
    internal override string[] GetUpgradeQueries()
    {
      return new string[] { GetUpgrade() };
    }
    internal override string[] GetSkipQueries()
    {
      return new string[] { GetSkipQuery_Table(@"dbo.Install_Upgrade") };
    }

    private string GetUpgrade()
    {
      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('dbo.Install_Upgrade', 'U') IS NULL");
      Sql.Add("BEGIN");
      Sql.Add("");
      Sql.Add("CREATE TABLE dbo.Install_Upgrade");
      Sql.Add("(");
      Sql.Add("  UpgradeId int PRIMARY KEY NOT NULL,");
      Sql.Add("  [Description] varchar(500) NOT NULL,");
      Sql.Add("  ExecuteDate DateTime,");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("END");
      return Sql.GetText();
    }
  }

After your app connects to the SQL server you can check the upgrade table:

private void PopulateDictionary()
    {
      Type[] types = System.Reflection.Assembly.GetAssembly(typeof(DBUpgrade)).GetTypes();
      foreach (Type t in types)
      {
        if (t.IsSubclassOf(typeof(UpgradeRecord)))
        {
          int id;
          string desc;
          int smgr_id, XXX_id;
          GetUpgradeId(t, out id, out desc, out smgr_id, out XXX_id);
          dict.Add(id, new UpgradeDictRecord(id, desc, smgr_id, XXX_id, t));
        }
      }
    }
    /* -------------------------------------------------------------------- */
    private static void GetUpgradeId(Type t, out int id, out string desc, out int smgr_id, out int XXX_id)
    {
      AttributeCollection attrs = TypeDescriptor.GetAttributes(t);
      foreach (Attribute at in attrs)
      {
        UpgradeId up = (at as UpgradeId);
        if (up != null)
        {
          id = up.ID;
          desc = up.Description;
          smgr_id = up.SMGR_ID;
          XXX_id = up.XXX_ID;

          //Force the class naming convention of Upgrade_1 = upgrade# defined in the attribute
          string[] typeName = t.Name.Split(new char[] { '_' });
          int idFromTypeName;
          if (!int.TryParse(typeName[typeName.Length - 1], out idFromTypeName) || (idFromTypeName != id))
          {
#if (DEBUG)
            if (System.Diagnostics.Debugger.IsAttached)
              System.Diagnostics.Debugger.Break();
#endif
            throw new Exception("Class upgrade id does not match attribute value. Please contact support."); //Never should happen
          }

          return;
        }
      }
      throw new InvalidOperationException("Upgrade id not defined"); //Never should happen
    }

I hope this is enough information for you to think about for a few minutes :)
Post back if you have any questions but I strongly urge you to bite the bullet and get MSSQL deployments working. Also do your homework in figuring out the best way for you to setup the database. It is very difficult to change your deployment logic after you begin shipping your application.

Edited 14 Years Ago by sknake because: n/a

ddanbe commented: Deep database knowledge! +6

DdoubleD commented: MVP response, again! +1

sknake 1,622 Senior Poster

14 Years Ago

>>PS - How do you get those cool looking Mac style buttons on your forms?

I use developer express controls. The entire application is skinnable. Forms, buttons, text boxes, grids, etc. Its a little pricey but I think its' worth it.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

zachattack05 70 Posting Pro in Training · Answer 1 · 2009-12-18T07:33:01+00:00

>>1) The MS SQL Server Express is required to access the data in .mdf files, does the server only need to be installed on the "host" or "server" computer on the network or does it need to be installed on all client computers as well?
You should install the SQL Server on a stable machine and have all of the clients point to that machine. You don't ever need to deal with the raw data files of the SQL Server directly unless you're moving/remounting databases or using a compact edition. For your purposes you should ignore them.

I'm not using the compact edition, but the "Express" edition, so it isn't the "full" version of the software. If I use the "publish" option in C# when I am finished with my application, will it include with it a distributable copy of the SQL Express server or should I instruct my users to download and set-up that software separately? When you write software like this, do you progrmatically mount the database files to the server? Or is that something you have to do manually?

>>2) When a solution uses .mdf files to hold data, how do client computers connect to it? Does it need to be shared (\\server\data\myfile.mdf), does it need to be on a network drive (S:\data\myfile.mdf) or does the client computer need to connect to it using the standard SQL connection (by connecting to IP address ###.###.###.###ORT#)? Or can it be any of those?
You connect to it using named pipes over the LAN. In a domain environment you can use windows authentication with the domain control so you don't even have to set up users in the database -- it will pull credentials from active directory for you. Its real easy!. Here is a code snippet of how you would connect to a database and run a query:

So, I would need to set-up users on the database for a network with no domain service? This is starting to seem quite complicated...Would I need different versions of the client software, one for domain networks and one without?

3) Does the MS SQL Server Express edition need to be configured in any special way? Are remote connections enabled for those who are connecting from different workstations etc...?
Not "special" per se but by default it does not enable network connections making it useless for the most part. You can use the GUI wizard when installing MSSQL and it will ask you to "enable remote connections" and you will want to select "yes".

Is this something that can be enabled programmatically? Or if the MSSQLExpress installer is included with my installer, is there a way to use command lines switches at install time to make sure those options are enabled?

Honestly this seems to be more than what I expected...Not that I am turned off by it, I just don't get how I can have so many pieces of software on my computer that frequently interact with data that is saved and I never have to install or configure any kind of SQL server or anything special...Is there a different way to write networked solutions?

zachattack05 70 Posting Pro in Training · Answer 2 · 2009-12-18T23:06:27+00:00

Sknake,

Let me chew that over for a bit and see if I can wrap my brain around it...you are correct though, the Access project is functioning, but does have issues...that's why I'd like to port it over to C#...I've toyed with the idea of possibly trying c++ instead, but I'm starting to think, at this point, it doesn't really matter...C# should be sufficient for it...besides from what I hear C++ is really no better.

Anyway, let me think about this stuff for a bit...I'm sure I'll have some other questions but I don't know...I might need to just start working on it and if I run into some issues ask then. I'm a pretty picky guy though, I like to know exactly how things work before I mess with it to seriously...maybe that's a bad thing for a noobie programmer to be...

anyway, I'll read your post over a few times and see if anything comes up...thanks again for all of your help and patience!

PS - How do you get those cool looking Mac style buttons on your forms? :P

zachattack05 70 Posting Pro in Training · Answer 3 · 2009-12-21T22:48:58+00:00

Okay, I do have a couple of initial questions after chewing on this for a while...

1) In regards to the application installation and the database being created... If I have only 1 "version" of the application (can be either a client or a server based on how they set the software up), when the msi package runs, it would try to create a blank database on the local system, but the SQL server won't be installed, will that cause the install to fail or can I have it simply ignore the error? Also, what happens if it is a re-install? The database already exists and the user is trying to reinstall the software...are there ways to access how msi packages handle these or would it be better to write my own installer package and do all of this stuff by hand? I hate not having control of how things are handled...the unknown to me makes me nervous.

2) Regarding users on the SQL Server...can users be added programmatically with C# or does the server administrator need to do that through a different interface? Since the server already has an admin account by default...what in the world is the username and password for it considering that I was never asked to set it? I'm assuming "SAPWD=password" is where I would set the admin password and the user name is probably "admin" or something when I install it through a command line right?

3) Injection attacks...is this something that I need to be worried about since I would be using SQL?

sknake 1,622 Senior Poster Featured Poster · Answer 4 · 2009-12-22T00:10:19+00:00

>>1) In regards to the application installation and the database being created... If I have only 1 "version" of the application (can be either a client or a server based on how they set the software up), when the msi package runs, it would try to create a blank database on the local system, but the SQL server won't be installed, will that cause the install to fail or can I have it simply ignore the error? Also, what happens if it is a re-install? The database already exists and the user is trying to reinstall the software...are there ways to access how msi packages handle these or would it be better to write my own installer package and do all of this stuff by hand? I hate not having control of how things are handled...the unknown to me makes me nervous.

Don't add SQL-Server as a launch condition or prerequisit in order to install your software. In the install dialogs add an optional step for installing SQL server, and add an optional step for configuring the database. You don't want to end up making your installer require SQL server for every installation because you only need one SQL instance per network. This will leave /some/ thinking to the person doing the install but it is unavoidable. Keep all of your database setup logic outside of the MSI. It will never work the way you want and it will always be problematic.

>>2) Regarding users on the SQL Server...can users be added programmatically with C# or does the server administrator need to do that through a different interface? Since the server already has an admin account by default...what in the world is the username and password for it considering that I was never asked to set it? I'm assuming "SAPWD=password" is where I would set the admin password and the user name is probably "admin" or something when I install it through a command line right?

The admin account for SQL is "sa" and the password is the one defined with SAPWD=xxxx from the initial installation. You can add users programatically in SQL. It would look something like this:

USE [ServManIS]
GO
CREATE USER [testuser] FOR LOGIN [testuser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [ServManIS]
GO
 ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [testuser]
GO
USE [ServManIS]
GO
EXEC sp_addrolemember N'db_accessadmin', N'testuser'
GO
USE [ServManIS]
GO
EXEC sp_addrolemember N'db_ddladmin', N'testuser'
GO
USE [ServManIS]
GO
EXEC sp_addrolemember N'db_datareader', N'testuser'
GO
USE [ServManIS]
GO
EXEC sp_addrolemember N'db_owner', N'testuser'
GO
USE [ServManIS]
GO
EXEC sp_addrolemember N'db_backupoperator', N'testuser'
GO
USE [ServManIS]
GO
EXEC sp_addrolemember N'db_datawriter', N'testuser'
GO
GRANT EXECUTE ON dbo.SensorImport6 to [testuser]

>>3) Injection attacks...is this something that I need to be worried about since I would be using SQL?

Only if you don't use parameterized SQL. Reference the following URLs:
Daniweb search
http://www.daniweb.com/forums/post1083454.html#post1083454

That should get you started. Never find yourself in the position of adding user-input directly in to a queries' command text.

zachattack05 70 Posting Pro in Training · Answer 5 · 2009-12-22T02:13:39+00:00

Don't add SQL-Server as a launch condition or prerequisit in order to install your software. In the install dialogs add an optional step for installing SQL server, and add an optional step for configuring the database. You don't want to end up making your installer require SQL server for every installation because you only need one SQL instance per network. This will leave /some/ thinking to the person doing the install but it is unavoidable. Keep all of your database setup logic outside of the MSI. It will never work the way you want and it will always be problematic.

Add a dialog? When I "publish" the application I don't seem to have any way to add any dialogs...I'm using express edition of C#...figured I shouldn't invest in the full studio until I'm sure I want to do this. See the attachment, that's the screen I see when I publish.

The admin account for SQL is "sa" and the password is the one defined with SAPWD=xxxx from the initial installation. You can add users programatically in SQL. It would look something like this:

How would I execute that in C#? Using a ExecuteNonQuery() command?

Only if you don't use parameterized SQL. Reference the following URLs:
Daniweb search
http://www.daniweb.com/forums/post1083454.html#post1083454
That should get you started. Never find yourself in the position of adding user-input directly in to a queries' command text.

Excellent! That is a great way to handle that! You learn something new every day! :)

zachattack05 70 Posting Pro in Training · Answer 6 · 2009-12-22T03:08:55+00:00

zachattack05 70 Posting Pro in Training

14 Years Ago

See the attachment

Helps if I attach it. :)

sknake 1,622 Senior Poster Featured Poster · Answer 7 · 2009-12-22T08:05:50+00:00

>>Add a dialog? When I "publish" the application I don't seem to have any way to add any dialogs...I'm using express edition of C#...figured I shouldn't invest in the full studio until I'm sure I want to do this. See the attachment, that's the screen I see when I publish.

Is this a web app or a windows form app?

>>How would I execute that in C#? Using a ExecuteNonQuery() command?

No that is a parameter to the command-line installation of SQL server. See the setup.exe ? That is run from a command prompt in the same directory as the installer.

zachattack05 70 Posting Pro in Training · Answer 8 · 2009-12-22T08:14:06+00:00

>>Add a dialog? When I "publish" the application I don't seem to have any way to add any dialogs...I'm using express edition of C#...figured I shouldn't invest in the full studio until I'm sure I want to do this. See the attachment, that's the screen I see when I publish.
Is this a web app or a windows form app?
>>How would I execute that in C#? Using a ExecuteNonQuery() command?
No that is a parameter to the command-line installation of SQL server. See the setup.exe ? That is run from a command prompt in the same directory as the installer.

This is a windows form app.

I'll think about the user thing later...