Database

giddy_guitarist wrote:

Or is there something on MSDN that would show me how to just get data from an sqlserver DB via C# code!. There are just some dumb wizard/ databinding examples.

// Set up
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = queryOrStoredProcedureName;
cmd.CommandType = CommandType.StoredProcedure; // Only if the cmd is a stored procedure
cmd.Parameters.Add(parameter details); // If you need to add parameters. Repeat as required
 
// Aquire connection
conn.Open();
 
// Query - choose one only.
SqlDataReader reader = cmd.ExecuteReader(); // if you expect a result
cmd.ExecuteNonQuery(); // if you don't expect a result
object result = cmd.ExecuteScalar(); // if you only want the value of the first column of the first row only.
 
// If you have a data reader, then iterate over the reader if necessary
while(reader.Read())
{
    // Get the data from the reader as required.
}
 
// Release the connection
conn.Close();

public static void FillListBox(string query, ListBox theListbox)<br />
        {<br />
            using (SqlConnection conn = new SqlConnection(<br />
 @"Data Source=.\SQLEXPRESS;AttachDbFilename=|path|\Experiment.mdf;Integrated Security=True;User Instance=True"))<br />
            {<br />
                SqlCommand cmd = new SqlCommand(query, conn);<br />
                conn.Open();<br />
                SqlDataReader reader = cmd.ExecuteReader();<br />
                theListbox.Items.Clear();<br />
                while (reader.Read())<br />
                {<br />
                    theListbox.Items.Add(reader[0]);<br />
                }<br />
                reader.Close();<br />
                cmd.Dispose();<br />
                conn.Close();<br />
            }<br />
        }

private void ExecEdit(string query)<br />
        {<br />
            using (SqlConnection conn = new SqlConnection(<br />
 @"Data Source=.\SQLEXPRESS;AttachDbFilename=|path|\Experiment.mdf;Integrated Security=True;User Instance=True"))<br />
            {<br />
                //code goes here<br />
                SqlCommand cmd = new SqlCommand(query, conn);<br />
                conn.Open();<br />
                cmd.ExecuteNonQuery();<br />
                cmd.Dispose();<br />
                conn.Close();<br />
<br />
            }<br />
        }

giddy_guitarist wrote:

1. Theres a good load because sometimes i need data often and this queries the DB and causes some slightly noticeable lag. Thats why i had the Dataset in mind. 50% of the time , i dont need *live* data. I realized i cant have a method that returns an SqlDataReader without a global open connection.So how do i cache raw?

giddy_guitarist wrote:

2.I have a PersID column in the persons table.Should i let it be an autonumber or Generate some hash because customers once removed are stored in an archive table.New and archived customers should'nt have same IDs , i noticed that even if a delete a customer with ID=2 , the next customer has an ID:3 . But i'm curious about where this is stored? what if i wanted to purge the DB of all customers so ID will start from 1 again?

giddy_guitarist wrote:

3. This is slightly embarrasing but , the thing i have to install on my clients pc is the file on this page thats 53 mb right?

giddy_guitarist wrote:

I know i need this to automate the intall correct?:

giddy_guitarist wrote:

A business operation ends up manipulating data. Like if a something is added to the customers bill , BillingService adds it to the Customers Dues , this is just copying the amount into the Dues table , so this class is somewhat a mix between a data and business entity. Or should i have a seperate class under the dataServies namespace just to put an amount under a customers due?

class Person
{
    public void ChangeAddrss(string address1, string address2, string city, string postcode)
    {
        this.address1 = address1;
        this.address2 = address2;
        this.ciy = city;
        this.postcode = postcode;
        Dal.UpdatePersonAddress(this.personId, address1, address2, city, postcode);
    }
}

giddy_guitarist wrote:

1. When i create a new sql db via VS i get a bunch of xsc , xss , xsd files and a Dataset?[like myDBDataSet] What are all these files?

giddy_guitarist wrote:

2. Why does an application setting class have to be created. The only sepecial property i see is the connection string.

giddy_guitarist wrote:

DO i have to use the above class to change the conn string , my string will be a little complex because the application works over a network and it has to query the network class for the right path to the DB.