Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 I would really like some help

Author  Topic 

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 04:59:19
I have posted some queries but perhaps they are questions which are to trivial to pay attention to, nevertheless I am totally stuck after having spent a lot of time arriving at code for:
creating a simple database
creating a simple table
writing to that simple table
reading from that simple table
The error I am finally getting is that there is no data in the table to read.
I would be infinitely happy if someone would look at my code and correct where I have slipped up.
I don't expect to be far off but need a wee bit of coaching to reach the finish line.
Hopefully someone who has the knowledge will be willing to help.

Asher.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-22 : 06:28:13
where's the code? at least post us part you're having issues with.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-22 : 06:33:05
Can you post the entire code if it is not too long (or a simplified version) that someone can copy and paste to Visual Studio and build and run? Also post the version of SQL Server you are using and the version of Visual Studio.

If your code is in C# I will try to run it and see if I can figure out what might be wrong. I am much less familiar with VB, so I may not be able to help, but someone else on the forum hopefully would.

It is not that people on the forum do not want to help. Everyone who responds to questions here is a volunteer doing it on their own time, so sometimes questions fall through the cracks. Very often, when a question is very long, and is posed in such a manner that it takes a long time to even understand what is being asked or to construct the code that is being used, people tend to not respond.

So think about what you would have to do to reconstruct the problem if you were reading your own post, had to copy that simulate the problem on a brand new machine, where you did not have access to the environment where you are regularly working. Post accordingly.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 06:42:52
Sorry please see next post.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 06:46:05
Sorry something went wrong

I would be incredibly grateful for the correction of this code. I expect that I am not far off. I spent a lot of time on it and cannot get to the bottom of the error(s). Many thanks in advance!


CREATE DATABASE
----------------------------------------------------------------------

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace FilesAndDatabase
{
public sealed class CreateDatabase
{
public CreateDatabase()
{
if (!Directory.Exists(@"c:\NewDatabase\"))
{
Directory.CreateDirectory(@"c:\NewDatabase\");
string filename = @"c:\NewDatabase\NewTest2.mdf";

string databaseName = System.IO.Path.GetFileNameWithoutExtension(filename);

string connection_string =
"Data Source=.\\Sqlexpress;Initial Catalog=tempdb;" +
"Integrated Security=true;User Instance=True;";

string command_string1 =
"CREATE DATABASE " + databaseName +
" ON PRIMARY (NAME=" + databaseName +
", FILENAME='" + filename + "')";

string command_string2 =
"EXEC sp_detach_db '" + databaseName + "', 'true'";

using (SqlConnection connection = new SqlConnection(connection_string))
{
connection.Open();

SqlCommand command1 = connection.CreateCommand();
command1.CommandText = command_string1;
command1.ExecuteNonQuery();

SqlCommand command2 = connection.CreateCommand();
command2.CommandText = command_string2;
command2.ExecuteNonQuery();

connection.Close();
}
}
}
}
}



CREATE TABLE
----------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;

namespace FilesAndDatabase
{
public sealed class CustomerCreateTable
{
public CustomerCreateTable()
{
try
{
if (Directory.Exists(@"c:\NewDatabase\"))
{
string connection_string = @"Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=C:\NewDatabase\NewTest2.mdf;User Instance=true;";
SqlConnection connection = new SqlConnection(connection_string);
connection.Open();

SqlCommand command = new SqlCommand();
command.CommandText =
"CREATE TABLE Customers2"
+ "(name char(35),"
+ "company char(35),"
+ "street char(35),"
+ "pobox char(35),"
+ "city char(35),"
+ "telephone char(35),"
+ "email char(35),"
+ "customer_key char(35) NOT NULL,"
+ "PRIMARY KEY(customer_key))";
command.Connection = connection;
command.ExecuteNonQuery();

connection.Close();
new TableAvailabilityWrite();
}
}
catch (Exception x)
{
new Warning("Table creation: " + x.Message);
}
}
}
}




WRITE TO TABLE
----------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;

namespace FilesAndDatabase
{
public sealed class CustomerWrite2
{
public CustomerWrite2()
{
string name = "1";
string company = "2";
string street = "3";
string pobox = "4";
string city = "5";
string telephone = "6";
string email = "7";
string customer_key = "abc";

try
{
using (SqlConnection my_connection = new SqlConnection(@"Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=C:\NewDatabase\NewTest2.mdf;User Instance=true;"))
{
my_connection.Open();

SqlCommand cmnd = new SqlCommand();
cmnd.CommandText = "INSERT INTO Customers2(name, company, street, pobox, city, telephone, email, customer_key)VALUES(@Name, @company, @street, @pobox, @city, @telephone, @email, @customer_key)";
cmnd.Connection = my_connection;

SqlParameter par1 = new SqlParameter();
par1.ParameterName = "name";
par1.Value = @name; cmnd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter();
par2.ParameterName = "company";
par2.Value = @company; cmnd.Parameters.Add(par2);
SqlParameter par3 = new SqlParameter();
par3.ParameterName = "street";
par3.Value = @street; cmnd.Parameters.Add(par3);
SqlParameter par4 = new SqlParameter();
par4.ParameterName = "pobox";
par4.Value = @pobox; cmnd.Parameters.Add(par4);
SqlParameter par5 = new SqlParameter();
par5.ParameterName = "city";
par5.Value = @city; cmnd.Parameters.Add(par5);
SqlParameter par6 = new SqlParameter();
par6.ParameterName = "telephone";
par6.Value = @telephone; cmnd.Parameters.Add(par6);
SqlParameter par7 = new SqlParameter();
par7.ParameterName = "email";
par7.Value = @email; cmnd.Parameters.Add(par7);
SqlParameter par8 = new SqlParameter();
par8.ParameterName = "customer_key";
par8.Value = @customer_key; cmnd.Parameters.Add(par8);
cmnd.ExecuteNonQuery();

my_connection.Close();
}
}
catch (SqlException err)
{
new Warning("Writing : " + err.Message);
}
}
}
}


READ FROM TABLE
----------------------------------------------------------------------
(warning is a routine that will show the result on screen)

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Collections;

namespace FilesAndDatabase
{
public sealed class CustomerRead2
{
public CustomerRead2()
{
try
{
SqlConnection cnctn = new SqlConnection(@"Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=C:\NewDatabase\NewTest2.mdf;User Instance=true;");
cnctn.Open();
string cmndstrng = "SELECT name FROM Customers2 WHERE (customer_key LIKE @Find)";
SqlCommand cmnd = new SqlCommand(cmndstrng);
cmnd.Connection = cnctn;
cmnd.Parameters.Add(new SqlParameter("@Find",System.Data.SqlDbType.Char,35,"customer_key"));
cmnd.Parameters["@Find"].Value = "abc";
SqlDataReader rdr = cmnd.ExecuteReader();
new Warning(rdr["name"].ToString());
}
catch (SqlException x)
{
new Warning("Reading : " + x.Message);
}
}

}
}
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-22 : 06:52:31
any specific reason why you're doing all these from .net code? these are quite straightforward while doing using t-sql script.you just need to create a t-sql batch as

CREATE DATABASE...

GO

CREATE TABLE...
GO

...
INSERT INTO TABLE...
GO

SELECT name FROM Customers2 WHERE customer_key LIKE @Find
...

One thing i didnt understand is why are you detaching the db after you create it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 07:08:07
I develop in Visual Studio 2005, Sql server 2005 and in C#. Up to now I have only used regular files but would like to extend my knowledge to database handling. I am writing an application in C#. It is all done except for the database. I have written the database code I am showing in this forum in a way so I can test it. (I still need an update routine, but expect to be able to handle that once the code in question is running) Once the code is OK I will use it as an example to finish the application.

>One thing i didnt understand is why are you detaching the db after you create it?

I hadn't a clue that I was doing that If you are referring to connection.open ... connection.close, I guess I have transferred that behavior from normal file handling, where you interact with the data for as little time as possible, in particular in a multi-user environment for which I normally program. Also I prefer to write bits and pieces of code that are functionally specific, that I can reuse, so I don't have to invent wheels more than once.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-22 : 09:08:46
To expand on what Visakh was suggesting:

First download and install SQL Express from here, if you have not already done so:
http://www.microsoft.com/en-us/download/details.aspx?id=29062
Be sure to download the Express with Advanced services or Express with Tools (ENU\x64\SQLEXPRADV_x64_ENU.exe or ENU\x64\SQLEXPRWT_x64_ENU.exe if you are on 64 bit).

Now, open up SQL Management Studio, click New Query Window and experiment with it. Here is some code to create a database, create a table, insert data, select from it etc. Once you get familiar with doing all this from SQL Server Management studio, developing a C# program to connect to it and insert or retrieve data is a short step from there. But, it is important that you first get the concept of the database, how data is stored etc.
----------------------------------------------------------------------------------------------
CREATE DATABASE MyTestDatabase;

-- Lot of options available - see here http://msdn.microsoft.com/en-us/library/ms176061.aspx
-- You can also create a database from Object Explorer - right click the Databases node in
-- SSMS.

-- Once you create a database, it is there, forever. You don't need to drop it and recreate
-- it every time. Think of it as a storage space, something like a file (cabinet). So this should be
-- a very infrequent operation, perhaps part of your set up procedures.


----------------------------------------------------------------------------------------------
-- create a table
CREATE TABLE dbo.MyTestTable(id INT PRIMARY KEY CLUSTERED, val VARCHAR(32));

-- Lot of options available - see here http://msdn.microsoft.com/en-us/library/ms174979.aspx
-- You can also create a table from Object Explorer - right click on the Tables node under
-- the database you created.

-- Once you create a table, it is there, forever. You don't need to drop it and recreate
-- it every time. Think of it as a storage space, something like a file placed in a file
-- cabinet (the database). So this should be a very infrequent operation, perhaps part
-- of your set up procedures.

----------------------------------------------------------------------------------------------
-- store some values in to the table
INSERT INTO dbo.MyTestTable (id, val) VALUES (1,'Abcde');
INSERT INTO dbo.MyTestTable (id, val) VALUES (2,'Xyz123');

-- Most of your operations against a database would be reading values from a table, inserting
-- data into a table, updating the data etc.

----------------------------------------------------------------------------------------------
-- read data from the table
SELECT id,val FROM dbo.MyTestTable;

----------------------------------------------------------------------------------------------
-- Discard the database
DROP DATABASE MyTestDatabase;
-- Dropping the database should be a very RARE operation. Database is your store for your
-- data, so you don't drop it - if you do, you cannot access the data or any of the work you did.

----------------------------------------------------------------------------------------------

Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 09:40:02
Hi Sunitabeck,

I am very impressed by your reply. Many many thanks.
I have printed out the text and will study it.
Thank you very much!

Asher.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 10:18:11
I am going to profit greatly by doing the valuable exercises and so forth that you suggest, for which I am extremely grateful. Starting to read your text though, I think I should have explained that in the application, which is to be sold, the database will only be created once. The database, having been created, will set up a flag and stop it from being created a second time. The same goes for the different tables that the program requires. I do have Sql Server Express and Manager on board, and am working with Sqlserver via Visual Studio, they are integrated as you will know. I am aware of the fact that in Server Manager I can set up a database, create tables and so forth, but the application is to be sold and I cannot expect someone who buys the product to do things via Sql server manager. He or she will be faced by a simple user-friendly interface (which is all done). But, again, I am certainly going to profit by doing the exercises you suggest.


Finally, I am a C#.Net programmer (having gone through C, C++ to C#), and although this may sound utterly piggish - I do really want to stay within the domain of C#.Net.

So in spite of my sincere appreciation and gratitude for your response, I do still very much would like to know what is wrong with the code that I have posted, because it is with this code - after it has been debugged - that I must finish the product that I am developing.

The database creation code that I posted is ok I believe, so is the code to create a table, the error I would have thought is somewhere in the code to write and read.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-22 : 12:39:15
Rather than creating a single application to address all three tasks -
1. initial installation,
2. normal usage, and
3. uninstalling if the user chooses to -
I would think that separating these into three would be more manageable.

So you would have
1. a deployment package which installs the database (perhaps via SQL scripts) and the .Net application (perhaps using click-once or some other deployment technology).
2. The .Net application would simply be doing the DML operations (update/delete/insert data).
3. Then you would have an uninstall operation that would uninstall both the database and the .net application.

If you follow that approach, to develop the application:
1. Create the database manually in SSMS.
2. Develop the queries for insert, update, delete etc. in SSMS
3. Wrap the queries into stored procedures (in SSMS)
4. Develop the C# application that will call the stored procedures as required (in Visual Studio). You will see lot of examples of how to do that if you google (see here for example: http://support.microsoft.com/kb/310070)

Deployment stage:
I am not familiar with those, but there are commercial (or even freeware) utilities that will help you package the software including database installations.

Even if your requirement is to be able to install the database from a single application, separating them into logical groups and getting the DML part working correctly first would be the way to go, in my opinion.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-22 : 13:51:05
What you suggest is very interesting and certainly worth investigating, however, the support I was seeking concerned the code that I posted, and finding the bug in that code, which, in the mean time, I have done. Thank you for responding.


Go to Top of Page
   

- Advertisement -