Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Help Inserting Multiple Files With The Same Primary Key C#

c# c# sql server combobox

  • Please log in to reply
11 replies to this topic

#1 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic
  • Learning:C++, C#, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 28 June 2012 - 09:17 AM

private void button2_Click(object sender, EventArgs e)
{
//Field Declarations
DateTime report_year = dateTimePicker1.Value;
string firstname = textBox1.Text;
string lastname = textBox2.Text;
string property**le = textBox3.Text;
string business**le = textBox4.Text;
string address = textBox5.Text;
string district = comboBox1.Text;

//Floorplan File
string floorplanpath1 = opendlg.FileName;
string fpname = Path.GetFileName(floorplanpath1);
FileStream fpstream = new FileStream(fpname, FileMode.Open, FileAccess.Read);
BinaryReader fpreader = new BinaryReader(fpstream);
FileInfo fpfi = new FileInfo(fpname);
long fpsize = fpfi.Length;
byte[] floorplan = System.IO.File.ReadAllBytes(fpname);


//Reportsubmitted File
string reportsubmittedpath1 = opendlg.FileName;
string rsname = Path.GetFileName(reportsubmittedpath1);
FileStream rsstream = new FileStream(rsname, FileMode.Open, FileAccess.Read);
BinaryReader rsreader = new BinaryReader(rsstream);
FileInfo rsfi = new FileInfo(rsname);
long rssize = rsfi.Length;
byte[] reportsubmitted = System.IO.File.ReadAllBytes(rsname);


//Request File
string requestpath1 = opendlg.FileName;
string rname = Path.GetFileName(requestpath1);
FileStream rstream = new FileStream(rname, FileMode.Open, FileAccess.Read);
BinaryReader rreader = new BinaryReader(rstream);
FileInfo rfi = new FileInfo(rname);
long rsize = rfi.Length;
byte[] request = System.IO.File.ReadAllBytes(rname);

DateTime review = dateTimePicker2.Value;

//Establish Database Connection
try
{
SqlConnection connection = new SqlConnection(@"Data Source=localhost;Initial Catalog=fireprevention;User ID=sa;P**word=pussyopac");
const string query = "insert into [reports] ([report_year], [firstname], [lastname], [property**le], [business**le], [address], [district], [floorplan], [reportsubmitted], [request], [review]) values (@reports, @firstname, @lastname, @property**le, @business**le, @address, @district, @floorplan, @reportsubmitted, @request, @review)";
SqlCommand command = new SqlCommand(query, connection);

command.Parameters.AddWithValue("@report_year", report_year);
command.Parameters.AddWithValue("@firstname", firstname);
command.Parameters.AddWithValue("@lastname", lastname);
command.Parameters.AddWithValue("@property**le", property**le);
command.Parameters.AddWithValue("@business**le", business**le);
command.Parameters.AddWithValue("@address", address);
command.Parameters.AddWithValue("@district", district);
command.Parameters.AddWithValue("@floorplan", floorplan);
command.Parameters.AddWithValue("@reportsubmitted", reportsubmitted);
command.Parameters.AddWithValue("@request", request);
command.Parameters.AddWithValue("@review", review);

const string queryfp = "insert into [floorplan] ([fpsize], [floorplan], [fpname]) values (@fpsize, @floorplan, @fpname)";
command.Parameters.AddWithValue("@fpsize", fpsize);
command.Parameters.AddWithValue("@fpplan", floorplan);
command.Parameters.AddWithValue("@fpname", fpname);
SqlCommand command1 = new SqlCommand(queryfp, connection);

const string queryrs = "insert into [reportsubmitted] ([rssize], [reportsubmitted], [rsname]) values (@rssize, @reportsubmitted, @rsname)";
command.Parameters.AddWithValue("@rssize", rssize);
command.Parameters.AddWithValue("@rreportsubmitted", reportsubmitted);
command.Parameters.AddWithValue("@rsname", rsname);
SqlCommand command2 = new SqlCommand(queryrs, connection);

const string queryr = "insert into [request] ([rsize], [request], [rname]) values (@rsize, @request, @rname)";
command.Parameters.AddWithValue("@rsize", rsize);
command.Parameters.AddWithValue("@request", request);
command.Parameters.AddWithValue("@rname", rname);
SqlCommand command3 = new SqlCommand(queryr, connection);


command.Connection.Open();
command1.Connection.Open();
command2.Connection.Open();
command3.Connection.Open();
command.ExecuteNonQuery();
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
command3.ExecuteNonQuery();
fpreader.Close();
fpstream.Close();
rsreader.Close();
rsstream.Close();
rreader.Close();
rstream.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

//dateTimePicker1.Enabled = false;
//textBox1.Enabled = false;
//textBox2.Enabled = false;
//textBox3.Enabled = false;
//textBox4.Enabled = false;
//textBox5.Enabled = false;
//comboBox1.Enabled = false;
//dateTimePicker2.Enabled = false;
//button2.Enabled = false;
//button3.Enabled = false;
//button4.Enabled = false;
//button5.Enabled = false;
//button6.Enabled = false;
}
}

i am new to programming in c# so bear with me please as my source code is all jumbled up..
i have 4 databases. reports - floorplan - reportsubmitted - request.
My issue is i want to knwo whwen i upload the report details ialso want to upload multiple binary files with that same report..
  • 0

#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 28 June 2012 - 09:54 AM

you might do a files table, for the binary files, in the likes of : id, id_report, file

you set id as autoincrement iden**y, and you insert there the files with the reportid, an the file contents

That way you will have no file quan**y limitations

is that what you wanted to do ?
  • 0

#3 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic
  • Learning:C++, C#, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 28 June 2012 - 10:05 AM

you might do a files table, for the binary files, in the likes of : id, id_report, file

you set id as autoincrement iden**y, and you insert there the files with the reportid, an the file contents

That way you will have no file quan**y limitations

is that what you wanted to do ?

yes i have created the tables but how do structure the query so that the reports form along with the 3 binary files are submitted .thanx
  • 0

#4 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 28 June 2012 - 10:45 AM

oh, that will depend on which database you are using,

you are gonna need a binary reader for the blob fields ( the binary files )

so, the query is pretty much as a normal one when you retrieve the data with a join clause in between both tables, and you can use a datareader, and what you will do with the binary fields is pretty much what you did to save them, get the buffersize, **ociate a streamreader or a binaryreader to the field and write it to ... memory, disk or as an http stream if what you want is to present it on web page.

so, where are you standing with this ? anything make me know, am here to help
  • 0

#5 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 28 June 2012 - 10:50 AM

Can you explain what problems you are having? The **le says "Help Inserting Multiple Files With The Same Primary Key C#". I don't see any primary keys.
  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#6 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic
  • Learning:C++, C#, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 28 June 2012 - 10:58 AM

Can you explain what problems you are having? The **le says "Help Inserting Multiple Files With The Same Primary Key C#". I don't see any primary keys.

i have attached a picture of my database diagram maybe this will give you a clearer picture of what i am hoping to achieve

Attached Thumbnails

  • diagram.JPG

  • 0

#7 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 29 June 2012 - 01:54 AM

1. that's not 4 databases...that is one database with 4 tables...big difference
2. you didn't show/tell us what kind of database did you use (mySQL, MSSQL,...)...that is important if you want to learn their query language
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#8 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic
  • Learning:C++, C#, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 29 June 2012 - 01:57 AM

1. that's not 4 databases...that is one database with 4 tables...big difference
2. you didn't show/tell us what kind of database did you use (mySQL, MSSQL,...)...that is important if you want to learn their query language

its sql server 2012 sorry its a one to many relationship. a report can have many floorplan, reportsubmitted and requests. i just need help in writing the correct code to insert the report with a floorplan, reportsubmited and request, and later the ability to edit that report either to add additional files or delete already stored files.

thanx for the response
  • 0

#9 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 29 June 2012 - 02:36 AM

i have never worked with 2012, but you are using MSSQL (Microsoft SQL)...you have many good sites for learning MS SQL, one of them is w3schools which is very great site...learn the query language first (SELECT; INSERT; CREATE,...) and then you only have to learn few lines of code in C# to make your query work but first learn the query language
  • 0

Microsoft Student Partner, Microsoft Certified Professional


#10 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 29 June 2012 - 04:51 AM

I think your first issue would be learning database design. For instance, what happens if a report has been submitted more than once? Also, what happens if you need to find out when a request was made, and when a report was submitted. There are a lot of holes, but there are many parts where you are referencing the wrong way. Also, when you are doing foreign keys, it make sense to suffix the field with "ID" or "_ID", like floorplan_ID, so that you know that it is a reference to another table and the datatype, just by the name.
  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#11 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic
  • Learning:C++, C#, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 29 June 2012 - 04:53 AM

I think your first issue would be learning database design. For instance, what happens if a report has been submitted more than once? Also, what happens if you need to find out when a request was made, and when a report was submitted. There are a lot of holes, but there are many parts where you are referencing the wrong way. Also, when you are doing foreign keys, it make sense to suffix the field with "ID" or "_ID", like floorplan_ID, so that you know that it is a reference to another table and the datatype, just by the name.

thanx for the advice in the process of redesigning the database
  • 0

#12 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic
  • Learning:C++, C#, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 29 June 2012 - 05:17 AM

i have re-designed the database and i think i got it right...

Attached Thumbnails

  • Capture.JPG

  • 0





Also tagged with one or more of these keywords: c#, c# sql server, combobox

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download