public List <Personer> GetPersonerWithDataSet(out string errormsg)
{
SqlConnection dbConnection = new SqlConnection();
dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=FiskDB_Labb3;Integrated Security=True";
String sqlstring = "SELECT * FROM Personer";
SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);
SqlDataAdapter myAdapter = new SqlDataAdapter(dbCommand);
DataSet myDS = new DataSet();
List <Personer> PersonLista = new List <Personer>();
try
{
dbConnection.Open();
myAdapter.Fill(myDS, "myFisk");
int count = 0;
int i = 0;
count = myDS.Tables["myFisk"].Rows.Count;
if (count > 0)
{
while (i < count)
{
Personer pd = new Personer();
pd.Persnr = Convert.ToInt64(myDS.Tables["myFisk"].Rows[i]["Persnr"]);
pd.Namn = myDS.Tables["myFisk"].Rows[i]["Namn"].ToString();
pd.Telnr = myDS.Tables["myFisk"].Rows[i]["Telnr"].ToString();
i++;
PersonLista.Add(pd);
}
errormsg = "";
return(PersonLista);
}
else
{
errormsg = "Det hämtas ingen Person!";
return(null);
}
}
catch (Exception e)
{
errormsg = e.Message;
return(null);
}
finally
{
dbConnection.Close();
}
}
public int UpdatePersoner(Personer p1, int Pers_id, out string errormsg)
{
SqlConnection dbConnection = new SqlConnection();
dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=FiskDB_Labb3;Integrated Security=True";
string sqlstring = "UPDATE Personer SET Persnr = @persnr, Namn = @namn, Telnr = @telnr WHERE Persnr = @id";
SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);
dbCommand.Parameters.Add("persnr", SqlDbType.NVarChar, 50).Value = p1.Persnr;
dbCommand.Parameters.Add("namn", SqlDbType.NVarChar, 50).Value = p1.Namn;
dbCommand.Parameters.Add("telnr", SqlDbType.NVarChar, 50).Value = p1.Telnr;
dbCommand.Parameters.Add("id", SqlDbType.Int).Value = Pers_id;
try
{
dbConnection.Open();
int i = 0;
i = dbCommand.ExecuteNonQuery();
if (i == 1)
{
errormsg = "";
}
else
{
errormsg = "Gick ej att uppdatera person";
}
return(i);
}
catch (Exception e)
{
errormsg = e.Message;
return(0);
}
finally
{
dbConnection.Close();
}
}
public int InsertPerson(Personer p1, out string errormsg)
{
SqlConnection dbConnection = new SqlConnection();
dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=FiskDB_Labb3;Integrated Security=True";
string sqlstring = "INSERT INTO Personer (Persnr, Namn, Telnr) VALUES (@persnr, @namn, @telnr)";
SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);
dbCommand.Parameters.Add("persnr", SqlDbType.BigInt).Value = p1.Persnr;
dbCommand.Parameters.Add("namn", SqlDbType.NVarChar, 50).Value = p1.Namn;
dbCommand.Parameters.Add("telnr", SqlDbType.NVarChar, 15).Value = p1.Telnr;
try
{
dbConnection.Open();
int i = 0;
i = dbCommand.ExecuteNonQuery();
if (i == 1)
{
errormsg = "";
}
else
{
errormsg = "Det skapas ej en person i databasen";
}
return(i);
}
catch (Exception e)
{
errormsg = e.Message;
return(0);
}
finally
{
dbConnection.Close();
}
}
